This is an automated email from the ASF dual-hosted git repository.
englefly pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new ddc5592d430 [feat](nereids) disable join reorder if any table row
count is not available (#40349)
ddc5592d430 is described below
commit ddc5592d430a69296318d1a2ed56d8d0620a1d3d
Author: minghong <[email protected]>
AuthorDate: Mon Sep 23 15:09:40 2024 +0800
[feat](nereids) disable join reorder if any table row count is not
available (#40349)
## Proposed changes
join reorder
if a table is not analyzed, and its row count is not fully reported,
optimizer will disable join reorder.
fully report means after a table is newly created or truncated, all
tablets row count are reported.
Issue Number: close #xxx
<!--Describe your changes.-->
---
.../java/org/apache/doris/catalog/OlapTable.java | 3 +
.../org/apache/doris/nereids/NereidsPlanner.java | 14 ++
.../doris/nereids/stats/StatsCalculator.java | 38 ++++-
.../nereids_p0/cte/test_cte_filter_pushdown.out | 8 +-
.../data/nereids_p0/hint/multi_leading.out | 94 +++++------
regression-test/data/nereids_p0/hint/test_hint.out | 12 +-
.../nereids_rules_p0/eager_aggregate/basic.out | 4 +-
.../eager_aggregate/basic_one_side.out | 4 +-
.../eliminate_outer_join/eliminate_outer_join.out | 40 ++---
.../infer_predicate/infer_intersect_except.out | 4 +-
.../extract_from_disjunction_in_join.out | 14 +-
.../nereids_p0/cte/test_cte_filter_pushdown.groovy | 2 +-
.../suites/nereids_p0/hint/multi_leading.groovy | 186 ++++++++++-----------
.../suites/nereids_p0/hint/test_hint.groovy | 16 +-
.../eliminate_outer_join.groovy | 1 +
.../extract_from_disjunction_in_join.groovy | 6 +-
16 files changed, 244 insertions(+), 202 deletions(-)
diff --git a/fe/fe-core/src/main/java/org/apache/doris/catalog/OlapTable.java
b/fe/fe-core/src/main/java/org/apache/doris/catalog/OlapTable.java
index 0b10fa1bdd2..1f591d1b7bf 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/catalog/OlapTable.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/catalog/OlapTable.java
@@ -1571,6 +1571,9 @@ public class OlapTable extends Table implements
MTMVRelatedTableIf, GsonPostProc
return getRowCountForIndex(baseIndexId, false);
}
+ /**
+ * @return -1 if there are some tablets whose row count is not reported to
FE
+ */
public long getRowCountForIndex(long indexId, boolean strict) {
long rowCount = 0;
for (Map.Entry<Long, Partition> entry : idToPartition.entrySet()) {
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/NereidsPlanner.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/NereidsPlanner.java
index 25d0dedd58d..b51f9ce24a3 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/NereidsPlanner.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/NereidsPlanner.java
@@ -22,6 +22,7 @@ import org.apache.doris.analysis.ExplainOptions;
import org.apache.doris.analysis.StatementBase;
import org.apache.doris.catalog.Column;
import org.apache.doris.catalog.TableIf;
+import org.apache.doris.common.FeConstants;
import org.apache.doris.common.FormatOptions;
import org.apache.doris.common.NereidsException;
import org.apache.doris.common.Pair;
@@ -48,6 +49,7 @@ import
org.apache.doris.nereids.processor.post.PlanPostProcessors;
import org.apache.doris.nereids.processor.pre.PlanPreprocessors;
import org.apache.doris.nereids.properties.PhysicalProperties;
import org.apache.doris.nereids.rules.exploration.mv.MaterializationContext;
+import org.apache.doris.nereids.stats.StatsCalculator;
import org.apache.doris.nereids.trees.expressions.NamedExpression;
import org.apache.doris.nereids.trees.expressions.SlotReference;
import org.apache.doris.nereids.trees.plans.ComputeResultSet;
@@ -56,6 +58,7 @@ import
org.apache.doris.nereids.trees.plans.commands.ExplainCommand.ExplainLevel
import org.apache.doris.nereids.trees.plans.distribute.DistributePlanner;
import org.apache.doris.nereids.trees.plans.distribute.DistributedPlan;
import org.apache.doris.nereids.trees.plans.distribute.FragmentIdMapping;
+import org.apache.doris.nereids.trees.plans.logical.LogicalOlapScan;
import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
import org.apache.doris.nereids.trees.plans.logical.LogicalSqlCache;
import org.apache.doris.nereids.trees.plans.physical.PhysicalPlan;
@@ -256,6 +259,17 @@ public class NereidsPlanner extends Planner {
}
}
+ // if we cannot get table row count, skip join reorder
+ // except:
+ // 1. user set leading hint
+ // 2. ut test. In ut test, FeConstants.enableInternalSchemaDb is
false or FeConstants.runningUnitTest is true
+ if (FeConstants.enableInternalSchemaDb && !FeConstants.runningUnitTest
+ && !cascadesContext.isLeadingDisableJoinReorder()) {
+ List<LogicalOlapScan> scans = cascadesContext.getRewritePlan()
+ .collectToList(LogicalOlapScan.class::isInstance);
+
StatsCalculator.disableJoinReorderIfTableRowCountNotAvailable(scans,
cascadesContext);
+ }
+
optimize();
if (statementContext.getConnectContext().getExecutor() != null) {
statementContext.getConnectContext().getExecutor().getSummaryProfile().setNereidsOptimizeTime();
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/StatsCalculator.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/StatsCalculator.java
index 346d1ae029f..bac66f34ae6 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/StatsCalculator.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/StatsCalculator.java
@@ -182,6 +182,11 @@ public class StatsCalculator extends
DefaultPlanVisitor<Statistics, Void> {
private CascadesContext cascadesContext;
+ private StatsCalculator(CascadesContext context) {
+ this.groupExpression = null;
+ this.cascadesContext = context;
+ }
+
private StatsCalculator(GroupExpression groupExpression, boolean
forbidUnknownColStats,
Map<String, ColumnStatistic> columnStatisticMap, boolean
isPlayNereidsDump,
Map<CTEId, Statistics> cteIdToStats, CascadesContext context) {
@@ -205,6 +210,27 @@ public class StatsCalculator extends
DefaultPlanVisitor<Statistics, Void> {
return totalColumnStatisticMap;
}
+ /**
+ * disable join reorder if any table row count is not available.
+ */
+ public static void disableJoinReorderIfTableRowCountNotAvailable(
+ List<LogicalOlapScan> scans, CascadesContext context) {
+ StatsCalculator calculator = new StatsCalculator(context);
+ for (LogicalOlapScan scan : scans) {
+ double rowCount = calculator.getOlapTableRowCount(scan);
+ if (rowCount == -1 && ConnectContext.get() != null) {
+ try {
+
ConnectContext.get().getSessionVariable().disableNereidsJoinReorderOnce();
+ LOG.info("disable join reorder since row count not
available: "
+ + scan.getTable().getNameWithFullQualifiers());
+ } catch (Exception e) {
+ LOG.info("disableNereidsJoinReorderOnce failed");
+ }
+ return;
+ }
+ }
+ }
+
/**
* estimate stats
*/
@@ -217,15 +243,6 @@ public class StatsCalculator extends
DefaultPlanVisitor<Statistics, Void> {
return statsCalculator;
}
- public static StatsCalculator estimate(GroupExpression groupExpression,
boolean forbidUnknownColStats,
- Map<String, ColumnStatistic> columnStatisticMap, boolean
isPlayNereidsDump, CascadesContext context) {
- return StatsCalculator.estimate(groupExpression,
- forbidUnknownColStats,
- columnStatisticMap,
- isPlayNereidsDump,
- new HashMap<>(), context);
- }
-
// For unit test only
public static void estimate(GroupExpression groupExpression,
CascadesContext context) {
StatsCalculator statsCalculator = new StatsCalculator(groupExpression,
false,
@@ -364,6 +381,9 @@ public class StatsCalculator extends
DefaultPlanVisitor<Statistics, Void> {
}
}
+ /**
+ * if the table is not analyzed and BE does not report row count, return -1
+ */
private double getOlapTableRowCount(OlapScan olapScan) {
OlapTable olapTable = olapScan.getTable();
AnalysisManager analysisManager =
Env.getCurrentEnv().getAnalysisManager();
diff --git a/regression-test/data/nereids_p0/cte/test_cte_filter_pushdown.out
b/regression-test/data/nereids_p0/cte/test_cte_filter_pushdown.out
index 7dd6492aa12..0bbae0dc25f 100644
--- a/regression-test/data/nereids_p0/cte/test_cte_filter_pushdown.out
+++ b/regression-test/data/nereids_p0/cte/test_cte_filter_pushdown.out
@@ -7,9 +7,9 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
--------filter((main.k1 = 1))
----------PhysicalOlapScan[test]
--PhysicalResultSink
-----hashJoin[INNER_JOIN] hashCondition=((m1.k1 = m2.k1)) otherCondition=()
build RFs:RF0 k1->[k1]
+----hashJoin[INNER_JOIN] hashCondition=((m1.k1 = m2.k1)) otherCondition=()
------filter((temp.k1 = 1))
---------PhysicalCteConsumer ( cteId=CTEId#0 ) apply RFs: RF0
+--------PhysicalCteConsumer ( cteId=CTEId#0 )
------filter((m2.k1 = 1))
--------PhysicalCteConsumer ( cteId=CTEId#0 )
@@ -21,9 +21,9 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
--------PhysicalQuickSort[LOCAL_SORT]
----------PhysicalOlapScan[test]
--PhysicalResultSink
-----hashJoin[INNER_JOIN] hashCondition=((m1.k1 = m2.k1)) otherCondition=()
build RFs:RF0 k1->[k1]
+----hashJoin[INNER_JOIN] hashCondition=((m1.k1 = m2.k1)) otherCondition=()
------filter((temp.k1 = 1))
---------PhysicalCteConsumer ( cteId=CTEId#0 ) apply RFs: RF0
+--------PhysicalCteConsumer ( cteId=CTEId#0 )
------filter((m2.k1 = 1))
--------PhysicalCteConsumer ( cteId=CTEId#0 )
diff --git a/regression-test/data/nereids_p0/hint/multi_leading.out
b/regression-test/data/nereids_p0/hint/multi_leading.out
index 08b6b83ed58..ce74020695d 100644
--- a/regression-test/data/nereids_p0/hint/multi_leading.out
+++ b/regression-test/data/nereids_p0/hint/multi_leading.out
@@ -24,17 +24,17 @@ PhysicalResultSink
----PhysicalDistribute[DistributionSpecGather]
------hashAgg[LOCAL]
--------hashJoin[INNER_JOIN colocated] hashCondition=((cte.c1 = t1.c1))
otherCondition=()
-----------filter((t1.c1 > 300))
-------------PhysicalOlapScan[t1]
----------hashJoin[INNER_JOIN broadcast] hashCondition=((cte.c1 = t2.c2))
otherCondition=()
------------filter((cte.c1 > 300))
--------------PhysicalOlapScan[t1]
------------filter((t2.c2 > 300))
--------------PhysicalOlapScan[t2]
+----------filter((t1.c1 > 300))
+------------PhysicalOlapScan[t1]
Hint log:
-Used: leading(t1 t2 ) leading(t1 cte )
-UnUsed:
+Used:
+UnUsed: leading(t1 t2) leading(t1 cte)
SyntaxError:
-- !sql1_4 --
@@ -43,17 +43,17 @@ PhysicalResultSink
----PhysicalDistribute[DistributionSpecGather]
------hashAgg[LOCAL]
--------hashJoin[INNER_JOIN colocated] hashCondition=((cte.c1 = t1.c1))
otherCondition=()
-----------filter((t1.c1 > 300))
-------------PhysicalOlapScan[t1]
----------hashJoin[INNER_JOIN broadcast] hashCondition=((cte.c1 = t2.c2))
otherCondition=()
------------filter((cte.c1 > 300))
--------------PhysicalOlapScan[t1]
------------filter((t2.c2 > 300))
--------------PhysicalOlapScan[t2]
+----------filter((t1.c1 > 300))
+------------PhysicalOlapScan[t1]
Hint log:
-Used: leading(t1 t2 ) leading(t1 cte )
-UnUsed:
+Used:
+UnUsed: leading(t1 t2) leading(t1 cte)
SyntaxError:
-- !sql1_res_1 --
@@ -74,14 +74,14 @@ PhysicalResultSink
----PhysicalDistribute[DistributionSpecGather]
------hashAgg[LOCAL]
--------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = t3.c3))
otherCondition=()
-----------PhysicalOlapScan[t3]
----------hashJoin[INNER_JOIN broadcast] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
------------PhysicalOlapScan[t1]
------------PhysicalOlapScan[t2]
+----------PhysicalOlapScan[t3]
Hint log:
-Used: leading(t3 alias1 )
-UnUsed:
+Used:
+UnUsed: leading(t3 alias1)
SyntaxError:
-- !sql2_3 --
@@ -91,13 +91,13 @@ PhysicalResultSink
------hashAgg[LOCAL]
--------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = t3.c3))
otherCondition=()
----------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = t2.c2))
otherCondition=()
-------------PhysicalOlapScan[t2]
------------PhysicalOlapScan[t1]
+------------PhysicalOlapScan[t2]
----------PhysicalOlapScan[t3]
Hint log:
-Used: leading(t2 t1 )
-UnUsed:
+Used:
+UnUsed: leading(t2 t1)
SyntaxError:
-- !sql2_4 --
@@ -106,14 +106,14 @@ PhysicalResultSink
----PhysicalDistribute[DistributionSpecGather]
------hashAgg[LOCAL]
--------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = t3.c3))
otherCondition=()
-----------PhysicalOlapScan[t3]
----------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = t2.c2))
otherCondition=()
-------------PhysicalOlapScan[t2]
------------PhysicalOlapScan[t1]
+------------PhysicalOlapScan[t2]
+----------PhysicalOlapScan[t3]
Hint log:
-Used: leading(t2 t1 ) leading(t3 alias1 )
-UnUsed:
+Used:
+UnUsed: leading(t2 t1) leading(t3 alias1)
SyntaxError:
-- !sql2_res_1 --
@@ -135,17 +135,17 @@ PhysicalResultSink
------hashAgg[LOCAL]
--------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = cte.c11))
otherCondition=()
----------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = t3.c3))
otherCondition=()
-------------PhysicalOlapScan[t3]
------------hashJoin[INNER_JOIN broadcast] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
--------------PhysicalOlapScan[t1]
--------------PhysicalOlapScan[t2]
+------------PhysicalOlapScan[t3]
----------hashJoin[INNER_JOIN broadcast] hashCondition=((cte.c1 = t2.c2))
otherCondition=()
-------------PhysicalOlapScan[t2]
------------PhysicalOlapScan[t1]
+------------PhysicalOlapScan[t2]
Hint log:
-Used: leading(t2 t1 ) leading(t3 alias1 cte )
-UnUsed:
+Used:
+UnUsed: leading(t2 t1) leading(t3 alias1 cte)
SyntaxError:
-- !sql3_3 --
@@ -156,16 +156,16 @@ PhysicalResultSink
--------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = cte.c11))
otherCondition=()
----------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = t3.c3))
otherCondition=()
------------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = t2.c2))
otherCondition=()
---------------PhysicalOlapScan[t2]
--------------PhysicalOlapScan[t1]
+--------------PhysicalOlapScan[t2]
------------PhysicalOlapScan[t3]
----------hashJoin[INNER_JOIN broadcast] hashCondition=((cte.c1 = t2.c2))
otherCondition=()
------------PhysicalOlapScan[t1]
------------PhysicalOlapScan[t2]
Hint log:
-Used: leading(t2 t1 )
-UnUsed:
+Used:
+UnUsed: leading(t2 t1)
SyntaxError:
-- !sql3_4 --
@@ -175,17 +175,17 @@ PhysicalResultSink
------hashAgg[LOCAL]
--------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = cte.c11))
otherCondition=()
----------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = t3.c3))
otherCondition=()
-------------PhysicalOlapScan[t3]
------------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = t2.c2))
otherCondition=()
---------------PhysicalOlapScan[t2]
--------------PhysicalOlapScan[t1]
+--------------PhysicalOlapScan[t2]
+------------PhysicalOlapScan[t3]
----------hashJoin[INNER_JOIN broadcast] hashCondition=((cte.c1 = t2.c2))
otherCondition=()
-------------PhysicalOlapScan[t2]
------------PhysicalOlapScan[t1]
+------------PhysicalOlapScan[t2]
Hint log:
-Used: leading(t2 t1 ) leading(t2 t1 ) leading(t3 alias1 cte )
-UnUsed:
+Used:
+UnUsed: leading(t2 t1) leading(t2 t1) leading(t3 alias1 cte)
SyntaxError:
-- !sql3_res_1 --
@@ -206,16 +206,16 @@ PhysicalResultSink
----PhysicalDistribute[DistributionSpecGather]
------hashAgg[LOCAL]
--------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = t3.c3))
otherCondition=()
-----------PhysicalOlapScan[t3]
----------hashJoin[INNER_JOIN broadcast] hashCondition=((t1.c1 = alias2.c2))
otherCondition=()
------------PhysicalOlapScan[t1]
------------hashJoin[INNER_JOIN broadcast] hashCondition=((t2.c2 = t4.c4))
otherCondition=()
--------------PhysicalOlapScan[t2]
--------------PhysicalOlapScan[t4]
+----------PhysicalOlapScan[t3]
Hint log:
-Used: leading(t3 alias1 )
-UnUsed:
+Used:
+UnUsed: leading(t3 alias1)
SyntaxError:
-- !sql4_2 --
@@ -225,15 +225,15 @@ PhysicalResultSink
------hashAgg[LOCAL]
--------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = t3.c3))
otherCondition=()
----------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 =
alias2.c2)) otherCondition=()
+------------PhysicalOlapScan[t1]
------------hashJoin[INNER_JOIN broadcast] hashCondition=((t2.c2 = t4.c4))
otherCondition=()
--------------PhysicalOlapScan[t2]
--------------PhysicalOlapScan[t4]
-------------PhysicalOlapScan[t1]
----------PhysicalOlapScan[t3]
Hint log:
-Used: leading(alias2 t1 )
-UnUsed:
+Used:
+UnUsed: leading(alias2 t1)
SyntaxError:
-- !sql4_3 --
@@ -245,13 +245,13 @@ PhysicalResultSink
----------hashJoin[INNER_JOIN broadcast] hashCondition=((t1.c1 = alias2.c2))
otherCondition=()
------------PhysicalOlapScan[t1]
------------hashJoin[INNER_JOIN broadcast] hashCondition=((t2.c2 = t4.c4))
otherCondition=()
---------------PhysicalOlapScan[t4]
--------------PhysicalOlapScan[t2]
+--------------PhysicalOlapScan[t4]
----------PhysicalOlapScan[t3]
Hint log:
-Used: leading(t4 t2 )
-UnUsed:
+Used:
+UnUsed: leading(t4 t2)
SyntaxError:
-- !sql4_4 --
@@ -260,16 +260,16 @@ PhysicalResultSink
----PhysicalDistribute[DistributionSpecGather]
------hashAgg[LOCAL]
--------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = t3.c3))
otherCondition=()
-----------PhysicalOlapScan[t3]
----------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 =
alias2.c2)) otherCondition=()
+------------PhysicalOlapScan[t1]
------------hashJoin[INNER_JOIN broadcast] hashCondition=((t2.c2 = t4.c4))
otherCondition=()
--------------PhysicalOlapScan[t2]
--------------PhysicalOlapScan[t4]
-------------PhysicalOlapScan[t1]
+----------PhysicalOlapScan[t3]
Hint log:
-Used: leading(alias2 t1 ) leading(t3 alias1 )
-UnUsed:
+Used:
+UnUsed: leading(alias2 t1) leading(t3 alias1)
SyntaxError:
-- !sql4_res_0 --
@@ -311,12 +311,12 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
--------hashAgg[GLOBAL]
----------PhysicalDistribute[DistributionSpecGather]
------------hashAgg[LOCAL]
---------------hashJoin[INNER_JOIN shuffle] hashCondition=((t1.c1 = cte.c11))
otherCondition=()
-----------------PhysicalCteConsumer ( cteId=CTEId#0 )
+--------------hashJoin[INNER_JOIN broadcast] hashCondition=((t1.c1 = cte.c11))
otherCondition=()
----------------PhysicalOlapScan[t1]
+----------------PhysicalCteConsumer ( cteId=CTEId#0 )
Hint log:
-Used: leading(cte t1 )
-UnUsed:
+Used:
+UnUsed: leading(cte t1)
SyntaxError:
diff --git a/regression-test/data/nereids_p0/hint/test_hint.out
b/regression-test/data/nereids_p0/hint/test_hint.out
index 66a218b09fe..f7128e7d15c 100644
--- a/regression-test/data/nereids_p0/hint/test_hint.out
+++ b/regression-test/data/nereids_p0/hint/test_hint.out
@@ -40,12 +40,12 @@ PhysicalResultSink
----PhysicalDistribute[DistributionSpecGather]
------hashAgg[LOCAL]
--------hashJoin[INNER_JOIN broadcast] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-----------PhysicalOlapScan[t2]
----------PhysicalOlapScan[t1]
+----------PhysicalOlapScan[t2]
Hint log:
-Used: leading(t2 broadcast t1 )
-UnUsed:
+Used:
+UnUsed: leading(t2 broadcast t1)
SyntaxError:
-- !select1_6 --
@@ -54,12 +54,12 @@ PhysicalResultSink
----PhysicalDistribute[DistributionSpecGather]
------hashAgg[LOCAL]
--------hashJoin[INNER_JOIN broadcast] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-----------PhysicalOlapScan[t2]
----------PhysicalOlapScan[t1]
+----------PhysicalOlapScan[t2]
Hint log:
-Used: leading(t2 broadcast t1 )
-UnUsed:
+Used:
+UnUsed: leading(t2 broadcast t1)
SyntaxError:
-- !select1_7 --
diff --git a/regression-test/data/nereids_rules_p0/eager_aggregate/basic.out
b/regression-test/data/nereids_rules_p0/eager_aggregate/basic.out
index 3e3986c75fc..ba18189efca 100644
--- a/regression-test/data/nereids_rules_p0/eager_aggregate/basic.out
+++ b/regression-test/data/nereids_rules_p0/eager_aggregate/basic.out
@@ -32,8 +32,8 @@ PhysicalResultSink
--hashAgg[GLOBAL]
----hashAgg[LOCAL]
------hashJoin[INNER_JOIN] hashCondition=((a.device_id = b.device_id))
otherCondition=()
---------PhysicalOlapScan[shunt_log_com_dd_library]
--------PhysicalOlapScan[com_dd_library]
+--------PhysicalOlapScan[shunt_log_com_dd_library]
-- !with_hint_1 --
PhysicalResultSink
@@ -83,8 +83,8 @@ PhysicalResultSink
--hashAgg[GLOBAL]
----hashAgg[LOCAL]
------hashJoin[INNER_JOIN] hashCondition=((a.device_id = b.device_id))
otherCondition=()
---------PhysicalOlapScan[shunt_log_com_dd_library]
--------PhysicalOlapScan[com_dd_library]
+--------PhysicalOlapScan[shunt_log_com_dd_library]
Hint log:
Used:
diff --git
a/regression-test/data/nereids_rules_p0/eager_aggregate/basic_one_side.out
b/regression-test/data/nereids_rules_p0/eager_aggregate/basic_one_side.out
index 49f1cc9617a..aaf6afeca1e 100644
--- a/regression-test/data/nereids_rules_p0/eager_aggregate/basic_one_side.out
+++ b/regression-test/data/nereids_rules_p0/eager_aggregate/basic_one_side.out
@@ -32,8 +32,8 @@ PhysicalResultSink
--hashAgg[GLOBAL]
----hashAgg[LOCAL]
------hashJoin[INNER_JOIN] hashCondition=((a.device_id = b.device_id))
otherCondition=()
---------PhysicalOlapScan[shunt_log_com_dd_library_one_side]
--------PhysicalOlapScan[com_dd_library_one_side]
+--------PhysicalOlapScan[shunt_log_com_dd_library_one_side]
-- !with_hint_1 --
PhysicalResultSink
@@ -83,8 +83,8 @@ PhysicalResultSink
--hashAgg[GLOBAL]
----hashAgg[LOCAL]
------hashJoin[INNER_JOIN] hashCondition=((a.device_id = b.device_id))
otherCondition=()
---------PhysicalOlapScan[shunt_log_com_dd_library_one_side]
--------PhysicalOlapScan[com_dd_library_one_side]
+--------PhysicalOlapScan[shunt_log_com_dd_library_one_side]
Hint log:
Used:
diff --git
a/regression-test/data/nereids_rules_p0/eliminate_outer_join/eliminate_outer_join.out
b/regression-test/data/nereids_rules_p0/eliminate_outer_join/eliminate_outer_join.out
index eff17e438e3..d92655e4e73 100644
---
a/regression-test/data/nereids_rules_p0/eliminate_outer_join/eliminate_outer_join.out
+++
b/regression-test/data/nereids_rules_p0/eliminate_outer_join/eliminate_outer_join.out
@@ -11,17 +11,17 @@ PhysicalResultSink
PhysicalResultSink
--PhysicalDistribute[DistributionSpecGather]
----hashJoin[INNER_JOIN colocated] hashCondition=((t1.id = t2.id))
otherCondition=()
-------PhysicalOlapScan[t]
------filter((t1.score > 10))
--------PhysicalOlapScan[t]
+------PhysicalOlapScan[t]
-- !full_outer_join --
PhysicalResultSink
--PhysicalDistribute[DistributionSpecGather]
-----hashJoin[RIGHT_OUTER_JOIN colocated] hashCondition=((t1.id = t2.id))
otherCondition=()
-------PhysicalOlapScan[t]
+----hashJoin[LEFT_OUTER_JOIN colocated] hashCondition=((t1.id = t2.id))
otherCondition=()
------filter((t1.score > 10))
--------PhysicalOlapScan[t]
+------PhysicalOlapScan[t]
-- !full_outer_join --
PhysicalResultSink
@@ -53,10 +53,10 @@ PhysicalResultSink
PhysicalResultSink
--PhysicalDistribute[DistributionSpecGather]
----hashJoin[LEFT_OUTER_JOIN colocated] hashCondition=((t1.id = t3.id))
otherCondition=()
-------hashJoin[RIGHT_OUTER_JOIN colocated] hashCondition=((t1.id = t2.id))
otherCondition=()
---------PhysicalOlapScan[t]
+------hashJoin[LEFT_OUTER_JOIN colocated] hashCondition=((t1.id = t2.id))
otherCondition=()
--------filter((t1.score > 10))
----------PhysicalOlapScan[t]
+--------PhysicalOlapScan[t]
------PhysicalOlapScan[t]
-- !multiple_left_outer_2 --
@@ -73,30 +73,30 @@ PhysicalResultSink
PhysicalResultSink
--PhysicalDistribute[DistributionSpecGather]
----hashJoin[INNER_JOIN colocated] hashCondition=((t1.id = t3.id))
otherCondition=()
-------PhysicalOlapScan[t]
------hashJoin[INNER_JOIN colocated] hashCondition=((t1.id = t2.id))
otherCondition=()
---------PhysicalOlapScan[t]
--------filter((t1.score > 10))
----------PhysicalOlapScan[t]
+--------PhysicalOlapScan[t]
+------PhysicalOlapScan[t]
-- !multiple_right_outer_2 --
PhysicalResultSink
--PhysicalDistribute[DistributionSpecGather]
----hashJoin[INNER_JOIN colocated] hashCondition=((t1.id = t3.id))
otherCondition=()
-------PhysicalOlapScan[t]
------hashJoin[INNER_JOIN colocated] hashCondition=((t1.id = t2.id))
otherCondition=()
--------PhysicalOlapScan[t]
--------filter((t2.score > 10))
----------PhysicalOlapScan[t]
+------PhysicalOlapScan[t]
-- !multiple_full_outer_1 --
PhysicalResultSink
--PhysicalDistribute[DistributionSpecGather]
----hashJoin[LEFT_OUTER_JOIN colocated] hashCondition=((t1.id = t3.id))
otherCondition=()
-------hashJoin[RIGHT_OUTER_JOIN colocated] hashCondition=((t1.id = t2.id))
otherCondition=()
---------PhysicalOlapScan[t]
+------hashJoin[LEFT_OUTER_JOIN colocated] hashCondition=((t1.id = t2.id))
otherCondition=()
--------filter((t1.score > 10))
----------PhysicalOlapScan[t]
+--------PhysicalOlapScan[t]
------PhysicalOlapScan[t]
-- !multiple_full_outer_2 --
@@ -112,10 +112,10 @@ PhysicalResultSink
-- !left_outer_join_non_null_assertion --
PhysicalResultSink
--PhysicalDistribute[DistributionSpecGather]
-----hashJoin[RIGHT_OUTER_JOIN colocated] hashCondition=((t1.id = t2.id))
otherCondition=()
-------PhysicalOlapScan[t]
+----hashJoin[LEFT_OUTER_JOIN colocated] hashCondition=((t1.id = t2.id))
otherCondition=()
------filter(( not id IS NULL) and (t1.score > 5))
--------PhysicalOlapScan[t]
+------PhysicalOlapScan[t]
-- !right_outer_join_non_null_assertion --
PhysicalResultSink
@@ -138,9 +138,9 @@ PhysicalResultSink
--PhysicalDistribute[DistributionSpecGather]
----hashJoin[INNER_JOIN colocated] hashCondition=((t2.id = t3.id))
otherCondition=()
------hashJoin[INNER_JOIN colocated] hashCondition=((t1.id = t2.id))
otherCondition=()
---------PhysicalOlapScan[t]
--------filter((t1.score > 5))
----------PhysicalOlapScan[t]
+--------PhysicalOlapScan[t]
------filter(( not score IS NULL))
--------PhysicalOlapScan[t]
@@ -161,7 +161,7 @@ PhysicalResultSink
----PhysicalProject
------filter((count(id) > 1))
--------hashAgg[LOCAL]
-----------hashJoin[RIGHT_OUTER_JOIN colocated] hashCondition=((t1.id = t2.id))
otherCondition=()
+----------hashJoin[LEFT_OUTER_JOIN colocated] hashCondition=((t1.id = t2.id))
otherCondition=()
------------PhysicalProject
--------------PhysicalOlapScan[t]
------------PhysicalProject
@@ -181,28 +181,28 @@ PhysicalResultSink
PhysicalResultSink
--PhysicalDistribute[DistributionSpecGather]
----hashJoin[INNER_JOIN colocated] hashCondition=((t1.id = t2.id))
otherCondition=()
-------filter(( not name IS NULL))
---------PhysicalOlapScan[t]
------filter((t1.score > 10))
--------PhysicalOlapScan[t]
+------filter(( not name IS NULL))
+--------PhysicalOlapScan[t]
-- !right_outer --
PhysicalResultSink
--PhysicalDistribute[DistributionSpecGather]
----hashJoin[INNER_JOIN colocated] hashCondition=((t1.id = t2.id))
otherCondition=()
-------filter(( not name IS NULL))
---------PhysicalOlapScan[t]
------filter((t1.score > 10))
--------PhysicalOlapScan[t]
+------filter(( not name IS NULL))
+--------PhysicalOlapScan[t]
-- !full_outer --
PhysicalResultSink
--PhysicalDistribute[DistributionSpecGather]
----hashJoin[INNER_JOIN colocated] hashCondition=((t1.id = t2.id))
otherCondition=()
-------filter(( not name IS NULL))
---------PhysicalOlapScan[t]
------filter((t1.score > 10))
--------PhysicalOlapScan[t]
+------filter(( not name IS NULL))
+--------PhysicalOlapScan[t]
-- !self_left_outer --
PhysicalResultSink
diff --git
a/regression-test/data/nereids_rules_p0/infer_predicate/infer_intersect_except.out
b/regression-test/data/nereids_rules_p0/infer_predicate/infer_intersect_except.out
index 783f83efe61..2609ca5f4c9 100644
---
a/regression-test/data/nereids_rules_p0/infer_predicate/infer_intersect_except.out
+++
b/regression-test/data/nereids_rules_p0/infer_predicate/infer_intersect_except.out
@@ -58,10 +58,10 @@ PhysicalResultSink
----filter((infer_intersect_except1.a > 0))
------PhysicalOlapScan[infer_intersect_except1]
----PhysicalIntersect
-------filter((infer_intersect_except3.a = 1) and (infer_intersect_except3.b =
'abc'))
---------PhysicalOlapScan[infer_intersect_except3]
------filter((infer_intersect_except2.b > 'ab'))
--------PhysicalOlapScan[infer_intersect_except2]
+------filter((infer_intersect_except3.a = 1) and (infer_intersect_except3.b =
'abc'))
+--------PhysicalOlapScan[infer_intersect_except3]
-- !except_and_intersect_except_predicate_to_right --
PhysicalResultSink
diff --git
a/regression-test/data/nereids_rules_p0/push_down_filter/extract_from_disjunction_in_join.out
b/regression-test/data/nereids_rules_p0/push_down_filter/extract_from_disjunction_in_join.out
index 9077ecb24b9..898621c7da7 100644
---
a/regression-test/data/nereids_rules_p0/push_down_filter/extract_from_disjunction_in_join.out
+++
b/regression-test/data/nereids_rules_p0/push_down_filter/extract_from_disjunction_in_join.out
@@ -9,11 +9,11 @@ PhysicalResultSink
-- !right_semi --
PhysicalResultSink
---hashJoin[LEFT_SEMI_JOIN] hashCondition=((t1.b = t2.b))
otherCondition=((((t2.a = 9) AND (t1.a = 1)) OR ((t1.a = 2) AND (t2.a = 8))))
-----filter(a IN (8, 9))
-------PhysicalOlapScan[extract_from_disjunction_in_join_t2]
+--hashJoin[RIGHT_SEMI_JOIN] hashCondition=((t1.b = t2.b))
otherCondition=((((t2.a = 9) AND (t1.a = 1)) OR ((t1.a = 2) AND (t2.a = 8))))
----filter(a IN (1, 2))
------PhysicalOlapScan[extract_from_disjunction_in_join_t1]
+----filter(a IN (8, 9))
+------PhysicalOlapScan[extract_from_disjunction_in_join_t2]
-- !left --
PhysicalResultSink
@@ -24,10 +24,10 @@ PhysicalResultSink
-- !right --
PhysicalResultSink
---hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.b = t2.b))
otherCondition=((((t2.a = 9) AND (t1.a = 1)) OR ((t1.a = 2) AND (t2.a = 8)))
and a IN (8, 9))
-----PhysicalOlapScan[extract_from_disjunction_in_join_t2]
+--hashJoin[RIGHT_OUTER_JOIN] hashCondition=((t1.b = t2.b))
otherCondition=((((t2.a = 9) AND (t1.a = 1)) OR ((t1.a = 2) AND (t2.a = 8)))
and a IN (8, 9))
----filter(a IN (1, 2))
------PhysicalOlapScan[extract_from_disjunction_in_join_t1]
+----PhysicalOlapScan[extract_from_disjunction_in_join_t2]
-- !left_anti --
PhysicalResultSink
@@ -38,10 +38,10 @@ PhysicalResultSink
-- !right_anti --
PhysicalResultSink
---hashJoin[LEFT_ANTI_JOIN] hashCondition=((t1.b = t2.b))
otherCondition=((((t2.a = 9) AND (t1.a = 1)) OR ((t1.a = 2) AND (t2.a = 8)))
and a IN (8, 9))
-----PhysicalOlapScan[extract_from_disjunction_in_join_t2]
+--hashJoin[RIGHT_ANTI_JOIN] hashCondition=((t1.b = t2.b))
otherCondition=((((t2.a = 9) AND (t1.a = 1)) OR ((t1.a = 2) AND (t2.a = 8)))
and a IN (8, 9))
----filter(a IN (1, 2))
------PhysicalOlapScan[extract_from_disjunction_in_join_t1]
+----PhysicalOlapScan[extract_from_disjunction_in_join_t2]
-- !inner --
PhysicalResultSink
diff --git
a/regression-test/suites/nereids_p0/cte/test_cte_filter_pushdown.groovy
b/regression-test/suites/nereids_p0/cte/test_cte_filter_pushdown.groovy
index e90b9037dc5..795d1ae7e5c 100644
--- a/regression-test/suites/nereids_p0/cte/test_cte_filter_pushdown.groovy
+++ b/regression-test/suites/nereids_p0/cte/test_cte_filter_pushdown.groovy
@@ -18,8 +18,8 @@ suite("test_cte_filter_pushdown") {
sql "SET enable_nereids_planner=true"
sql "SET enable_pipeline_engine=true"
sql "SET enable_fallback_to_original_planner=false"
- sql "set runtime_filter_type=2;"
sql "set ignore_shape_nodes='PhysicalDistribute, PhysicalProject'"
+ sql "set runtime_filter_mode=OFF"
// CTE filter pushing down with the same filter
qt_cte_filter_pushdown_1 """
explain shape plan
diff --git a/regression-test/suites/nereids_p0/hint/multi_leading.groovy
b/regression-test/suites/nereids_p0/hint/multi_leading.groovy
index 048c2e25498..4425fae1db2 100644
--- a/regression-test/suites/nereids_p0/hint/multi_leading.groovy
+++ b/regression-test/suites/nereids_p0/hint/multi_leading.groovy
@@ -45,99 +45,99 @@ suite("multi_leading") {
sql """create table t3 (c3 int, c33 int) distributed by hash(c3) buckets 3
properties('replication_num' = '1');"""
sql """create table t4 (c4 int, c44 int) distributed by hash(c4) buckets 3
properties('replication_num' = '1');"""
- streamLoad {
- table "t1"
- db "test_multi_leading"
- set 'column_separator', '|'
- set 'format', 'csv'
- file 't1.csv'
- time 10000
- }
-
- streamLoad {
- table "t2"
- db "test_multi_leading"
- set 'column_separator', '|'
- set 'format', 'csv'
- file 't2.csv'
- time 10000
- }
-
- streamLoad {
- table "t3"
- db "test_multi_leading"
- set 'column_separator', '|'
- set 'format', 'csv'
- file 't3.csv'
- time 10000
- }
-
- streamLoad {
- table "t4"
- db "test_multi_leading"
- set 'column_separator', '|'
- set 'format', 'csv'
- file 't4.csv'
- time 10000
- }
+ // streamLoad {
+ // table "t1"
+ // db "test_multi_leading"
+ // set 'column_separator', '|'
+ // set 'format', 'csv'
+ // file 't1.csv'
+ // time 10000
+ // }
+
+ // streamLoad {
+ // table "t2"
+ // db "test_multi_leading"
+ // set 'column_separator', '|'
+ // set 'format', 'csv'
+ // file 't2.csv'
+ // time 10000
+ // }
+
+ // streamLoad {
+ // table "t3"
+ // db "test_multi_leading"
+ // set 'column_separator', '|'
+ // set 'format', 'csv'
+ // file 't3.csv'
+ // time 10000
+ // }
+
+ // streamLoad {
+ // table "t4"
+ // db "test_multi_leading"
+ // set 'column_separator', '|'
+ // set 'format', 'csv'
+ // file 't4.csv'
+ // time 10000
+ // }
// test cte inline
- qt_sql1_2 """explain shape plan with cte as (select /*+ leading(t2 t1) */
c11, c1 from t1 join t2 on c1 = c2) select /*+ leading(t1 cte)*/ count(*) from
cte,t1 where cte.c1 = t1.c1 and t1.c1 > 300;"""
- qt_sql1_3 """explain shape plan with cte as (select /*+ leading(t1 t2) */
c11, c1 from t1 join t2 on c1 = c2) select /*+ leading(t1 cte)*/ count(*) from
cte,t1 where cte.c1 = t1.c1 and t1.c1 > 300;"""
- qt_sql1_4 """explain shape plan with cte as (select /*+ leading(t1 t2) */
c11, c1 from t1 join t2 on c1 = c2) select /*+ leading(t1 cte)*/ count(*) from
cte,t1 where cte.c1 = t1.c1 and t1.c1 > 300;"""
-
- qt_sql1_res_1 """with cte as (select c11, c1 from t1 join t2 on c1 = c2)
select count(*) from cte,t1 where cte.c1 = t1.c1 and t1.c1 > 300;"""
- qt_sql1_res_2 """with cte as (select /*+ leading(t2 t1) */ c11, c1 from t1
join t2 on c1 = c2) select /*+ leading(t1 cte)*/ count(*) from cte,t1 where
cte.c1 = t1.c1 and t1.c1 > 300;"""
- qt_sql1_res_3 """with cte as (select /*+ leading(t1 t2) */ c11, c1 from t1
join t2 on c1 = c2) select /*+ leading(t1 cte)*/ count(*) from cte,t1 where
cte.c1 = t1.c1 and t1.c1 > 300;"""
- qt_sql1_res_4 """with cte as (select /*+ leading(t1 t2) */ c11, c1 from t1
join t2 on c1 = c2) select /*+ leading(t1 cte)*/ count(*) from cte,t1 where
cte.c1 = t1.c1 and t1.c1 > 300;"""
-
- // test subquery alone
- qt_sql2_2 """explain shape plan select /*+ leading(t3 alias1) */ count(*)
from (select c1, c11 from t1 join t2 on c1 = c2) as alias1 join t3 on alias1.c1
= t3.c3;"""
- qt_sql2_3 """explain shape plan select count(*) from (select /*+
leading(t2 t1) */ c1, c11 from t1 join t2 on c1 = c2) as alias1 join t3 on
alias1.c1 = t3.c3;"""
- qt_sql2_4 """explain shape plan select /*+ leading(t3 alias1) */ count(*)
from (select /*+ leading(t2 t1) */ c1, c11 from t1 join t2 on c1 = c2) as
alias1 join t3 on alias1.c1 = t3.c3;"""
-
- qt_sql2_res_1 """select count(*) from (select c1, c11 from t1 join t2 on
c1 = c2) as alias1 join t3 on alias1.c1 = t3.c3;"""
- qt_sql2_res_2 """select /*+ leading(t3 alias1) */ count(*) from (select
c1, c11 from t1 join t2 on c1 = c2) as alias1 join t3 on alias1.c1 = t3.c3;"""
- qt_sql2_res_3 """select count(*) from (select /*+ leading(t2 t1) */ c1,
c11 from t1 join t2 on c1 = c2) as alias1 join t3 on alias1.c1 = t3.c3;"""
- qt_sql2_res_4 """select /*+ leading(t3 alias1) */ count(*) from (select
/*+ leading(t2 t1) */ c1, c11 from t1 join t2 on c1 = c2) as alias1 join t3 on
alias1.c1 = t3.c3;"""
-
- // test subquery + cte
- qt_sql3_2 """explain shape plan with cte as (select /*+ leading(t2 t1) */
c11, c1 from t1 join t2 on c1 = c2) select /*+ leading(t3 alias1 cte) */
count(*) from (select c1, c11 from t1 join t2 on c1 = c2) as alias1 join t3 on
alias1.c1 = t3.c3 join cte on alias1.c1 = cte.c11;;"""
- qt_sql3_3 """explain shape plan with cte as (select c11, c1 from t1 join
t2 on c1 = c2) select count(*) from (select /*+ leading(t2 t1) */ c1, c11 from
t1 join t2 on c1 = c2) as alias1 join t3 on alias1.c1 = t3.c3 join cte on
alias1.c1 = cte.c11;;"""
- qt_sql3_4 """explain shape plan with cte as (select /*+ leading(t2 t1) */
c11, c1 from t1 join t2 on c1 = c2) select /*+ leading(t3 alias1 cte) */
count(*) from (select /*+ leading(t2 t1) */ c1, c11 from t1 join t2 on c1 = c2)
as alias1 join t3 on alias1.c1 = t3.c3 join cte on alias1.c1 = cte.c11;;"""
-
- qt_sql3_res_1 """with cte as (select c11, c1 from t1 join t2 on c1 = c2)
select count(*) from (select c1, c11 from t1 join t2 on c1 = c2) as alias1 join
t3 on alias1.c1 = t3.c3 join cte on alias1.c1 = cte.c11;;"""
- qt_sql3_res_2 """with cte as (select /*+ leading(t2 t1) */ c11, c1 from t1
join t2 on c1 = c2) select /*+ leading(t3 alias1 cte) */ count(*) from (select
c1, c11 from t1 join t2 on c1 = c2) as alias1 join t3 on alias1.c1 = t3.c3 join
cte on alias1.c1 = cte.c11;;"""
- qt_sql3_res_3 """with cte as (select c11, c1 from t1 join t2 on c1 = c2)
select count(*) from (select /*+ leading(t2 t1) */ c1, c11 from t1 join t2 on
c1 = c2) as alias1 join t3 on alias1.c1 = t3.c3 join cte on alias1.c1 =
cte.c11;;"""
- qt_sql3_res_4 """with cte as (select /*+ leading(t2 t1) */ c11, c1 from t1
join t2 on c1 = c2) select /*+ leading(t3 alias1 cte) */ count(*) from (select
/*+ leading(t2 t1) */ c1, c11 from t1 join t2 on c1 = c2) as alias1 join t3 on
alias1.c1 = t3.c3 join cte on alias1.c1 = cte.c11;;"""
-
- // test multi level subqueries
- qt_sql4_1 """explain shape plan select /*+ leading(t3 alias1) */ count(*)
from (select c1, c11 from t1 join (select c2, c22 from t2 join t4 on c2 = c4)
as alias2 on c1 = alias2.c2) as alias1 join t3 on alias1.c1 = t3.c3;"""
- qt_sql4_2 """explain shape plan select count(*) from (select /*+
leading(alias2 t1) */ c1, c11 from t1 join (select c2, c22 from t2 join t4 on
c2 = c4) as alias2 on c1 = alias2.c2) as alias1 join t3 on alias1.c1 = t3.c3;"""
- qt_sql4_3 """explain shape plan select count(*) from (select c1, c11 from
t1 join (select /*+ leading(t4 t2) */ c2, c22 from t2 join t4 on c2 = c4) as
alias2 on c1 = alias2.c2) as alias1 join t3 on alias1.c1 = t3.c3;"""
- qt_sql4_4 """explain shape plan select /*+ leading(t3 alias1) */ count(*)
from (select /*+ leading(alias2 t1) */ c1, c11 from t1 join (select c2, c22
from t2 join t4 on c2 = c4) as alias2 on c1 = alias2.c2) as alias1 join t3 on
alias1.c1 = t3.c3;"""
- explain {
- sql """shape plan select /*+ leading(t3 alias1) */ count(*) from
(select c1, c11 from t1 join (select /*+ leading(t4 t2) */ c2, c22 from t2 join
t4 on c2 = c4) as alias2 on c1 = alias2.c2) as alias1 join t3 on alias1.c1 =
t3.c3;"""
- contains("SyntaxError: leading(t4 t2) Msg:one query block can only
have one leading clause")
- }
- explain {
- sql """shape plan select count(*) from (select /*+ leading(alias2 t1)
*/ c1, c11 from t1 join (select /*+ leading(t4 t2) */ c2, c22 from t2 join t4
on c2 = c4) as alias2 on c1 = alias2.c2) as alias1 join t3 on alias1.c1 =
t3.c3;"""
- contains("SyntaxError: leading(t4 t2) Msg:one query block can only
have one leading clause")
- }
- explain {
- sql """shape plan select /*+ leading(t3 alias1) */ count(*) from
(select /*+ leading(alias2 t1) */ c1, c11 from t1 join (select /*+ leading(t4
t2) */ c2, c22 from t2 join t4 on c2 = c4) as alias2 on c1 = alias2.c2) as
alias1 join t3 on alias1.c1 = t3.c3;"""
- contains("UnUsed: leading(alias2 t1)")
- }
-
- qt_sql4_res_0 """select count(*) from (select c1, c11 from t1 join (select
c2, c22 from t2 join t4 on c2 = c4) as alias2 on c1 = alias2.c2) as alias1 join
t3 on alias1.c1 = t3.c3;"""
- qt_sql4_res_1 """select /*+ leading(t3 alias1) */ count(*) from (select
c1, c11 from t1 join (select c2, c22 from t2 join t4 on c2 = c4) as alias2 on
c1 = alias2.c2) as alias1 join t3 on alias1.c1 = t3.c3;"""
- qt_sql4_res_2 """select count(*) from (select /*+ leading(alias2 t1) */
c1, c11 from t1 join (select c2, c22 from t2 join t4 on c2 = c4) as alias2 on
c1 = alias2.c2) as alias1 join t3 on alias1.c1 = t3.c3;"""
- qt_sql4_res_3 """select count(*) from (select c1, c11 from t1 join (select
/*+ leading(t4 t2) */ c2, c22 from t2 join t4 on c2 = c4) as alias2 on c1 =
alias2.c2) as alias1 join t3 on alias1.c1 = t3.c3;"""
- qt_sql4_res_4 """select /*+ leading(t3 alias1) */ count(*) from (select
/*+ leading(alias2 t1) */ c1, c11 from t1 join (select c2, c22 from t2 join t4
on c2 = c4) as alias2 on c1 = alias2.c2) as alias1 join t3 on alias1.c1 =
t3.c3;"""
- qt_sql4_res_5 """select /*+ leading(t3 alias1) */ count(*) from (select
c1, c11 from t1 join (select /*+ leading(t4 t2) */ c2, c22 from t2 join t4 on
c2 = c4) as alias2 on c1 = alias2.c2) as alias1 join t3 on alias1.c1 = t3.c3;"""
- qt_sql4_res_6 """select count(*) from (select /*+ leading(alias2 t1) */
c1, c11 from t1 join (select /*+ leading(t4 t2) */ c2, c22 from t2 join t4 on
c2 = c4) as alias2 on c1 = alias2.c2) as alias1 join t3 on alias1.c1 = t3.c3;"""
- qt_sql4_res_7 """select /*+ leading(t3 alias1) */ count(*) from (select
/*+ leading(alias2 t1) */ c1, c11 from t1 join (select /*+ leading(t4 t2) */
c2, c22 from t2 join t4 on c2 = c4) as alias2 on c1 = alias2.c2) as alias1 join
t3 on alias1.c1 = t3.c3;"""
-
- // use cte in scalar query
- qt_sql5_2 """explain shape plan with cte as (select c11, c1 from t1)
SELECT c1 FROM cte group by c1 having sum(cte.c11) > (select /*+ leading(cte
t1) */ 0.05 * avg(t1.c11) from t1 join cte on t1.c1 = cte.c11 )"""
+ // qt_sql1_2 """explain shape plan with cte as (select /*+ leading(t2 t1)
*/ c11, c1 from t1 join t2 on c1 = c2) select /*+ leading(t1 cte)*/ count(*)
from cte,t1 where cte.c1 = t1.c1 and t1.c1 > 300;"""
+ // qt_sql1_3 """explain shape plan with cte as (select /*+ leading(t1 t2)
*/ c11, c1 from t1 join t2 on c1 = c2) select /*+ leading(t1 cte)*/ count(*)
from cte,t1 where cte.c1 = t1.c1 and t1.c1 > 300;"""
+ // qt_sql1_4 """explain shape plan with cte as (select /*+ leading(t1 t2)
*/ c11, c1 from t1 join t2 on c1 = c2) select /*+ leading(t1 cte)*/ count(*)
from cte,t1 where cte.c1 = t1.c1 and t1.c1 > 300;"""
+
+ // qt_sql1_res_1 """with cte as (select c11, c1 from t1 join t2 on c1 =
c2) select count(*) from cte,t1 where cte.c1 = t1.c1 and t1.c1 > 300;"""
+ // qt_sql1_res_2 """with cte as (select /*+ leading(t2 t1) */ c11, c1 from
t1 join t2 on c1 = c2) select /*+ leading(t1 cte)*/ count(*) from cte,t1 where
cte.c1 = t1.c1 and t1.c1 > 300;"""
+ // qt_sql1_res_3 """with cte as (select /*+ leading(t1 t2) */ c11, c1 from
t1 join t2 on c1 = c2) select /*+ leading(t1 cte)*/ count(*) from cte,t1 where
cte.c1 = t1.c1 and t1.c1 > 300;"""
+ // qt_sql1_res_4 """with cte as (select /*+ leading(t1 t2) */ c11, c1 from
t1 join t2 on c1 = c2) select /*+ leading(t1 cte)*/ count(*) from cte,t1 where
cte.c1 = t1.c1 and t1.c1 > 300;"""
+
+ // // test subquery alone
+ // qt_sql2_2 """explain shape plan select /*+ leading(t3 alias1) */
count(*) from (select c1, c11 from t1 join t2 on c1 = c2) as alias1 join t3 on
alias1.c1 = t3.c3;"""
+ // qt_sql2_3 """explain shape plan select count(*) from (select /*+
leading(t2 t1) */ c1, c11 from t1 join t2 on c1 = c2) as alias1 join t3 on
alias1.c1 = t3.c3;"""
+ // qt_sql2_4 """explain shape plan select /*+ leading(t3 alias1) */
count(*) from (select /*+ leading(t2 t1) */ c1, c11 from t1 join t2 on c1 = c2)
as alias1 join t3 on alias1.c1 = t3.c3;"""
+
+ // qt_sql2_res_1 """select count(*) from (select c1, c11 from t1 join t2
on c1 = c2) as alias1 join t3 on alias1.c1 = t3.c3;"""
+ // qt_sql2_res_2 """select /*+ leading(t3 alias1) */ count(*) from (select
c1, c11 from t1 join t2 on c1 = c2) as alias1 join t3 on alias1.c1 = t3.c3;"""
+ // qt_sql2_res_3 """select count(*) from (select /*+ leading(t2 t1) */ c1,
c11 from t1 join t2 on c1 = c2) as alias1 join t3 on alias1.c1 = t3.c3;"""
+ // qt_sql2_res_4 """select /*+ leading(t3 alias1) */ count(*) from (select
/*+ leading(t2 t1) */ c1, c11 from t1 join t2 on c1 = c2) as alias1 join t3 on
alias1.c1 = t3.c3;"""
+
+ // // test subquery + cte
+ // qt_sql3_2 """explain shape plan with cte as (select /*+ leading(t2 t1)
*/ c11, c1 from t1 join t2 on c1 = c2) select /*+ leading(t3 alias1 cte) */
count(*) from (select c1, c11 from t1 join t2 on c1 = c2) as alias1 join t3 on
alias1.c1 = t3.c3 join cte on alias1.c1 = cte.c11;;"""
+ // qt_sql3_3 """explain shape plan with cte as (select c11, c1 from t1
join t2 on c1 = c2) select count(*) from (select /*+ leading(t2 t1) */ c1, c11
from t1 join t2 on c1 = c2) as alias1 join t3 on alias1.c1 = t3.c3 join cte on
alias1.c1 = cte.c11;;"""
+ // qt_sql3_4 """explain shape plan with cte as (select /*+ leading(t2 t1)
*/ c11, c1 from t1 join t2 on c1 = c2) select /*+ leading(t3 alias1 cte) */
count(*) from (select /*+ leading(t2 t1) */ c1, c11 from t1 join t2 on c1 = c2)
as alias1 join t3 on alias1.c1 = t3.c3 join cte on alias1.c1 = cte.c11;;"""
+
+ // qt_sql3_res_1 """with cte as (select c11, c1 from t1 join t2 on c1 =
c2) select count(*) from (select c1, c11 from t1 join t2 on c1 = c2) as alias1
join t3 on alias1.c1 = t3.c3 join cte on alias1.c1 = cte.c11;;"""
+ // qt_sql3_res_2 """with cte as (select /*+ leading(t2 t1) */ c11, c1 from
t1 join t2 on c1 = c2) select /*+ leading(t3 alias1 cte) */ count(*) from
(select c1, c11 from t1 join t2 on c1 = c2) as alias1 join t3 on alias1.c1 =
t3.c3 join cte on alias1.c1 = cte.c11;;"""
+ // qt_sql3_res_3 """with cte as (select c11, c1 from t1 join t2 on c1 =
c2) select count(*) from (select /*+ leading(t2 t1) */ c1, c11 from t1 join t2
on c1 = c2) as alias1 join t3 on alias1.c1 = t3.c3 join cte on alias1.c1 =
cte.c11;;"""
+ // qt_sql3_res_4 """with cte as (select /*+ leading(t2 t1) */ c11, c1 from
t1 join t2 on c1 = c2) select /*+ leading(t3 alias1 cte) */ count(*) from
(select /*+ leading(t2 t1) */ c1, c11 from t1 join t2 on c1 = c2) as alias1
join t3 on alias1.c1 = t3.c3 join cte on alias1.c1 = cte.c11;;"""
+
+ // // test multi level subqueries
+ // qt_sql4_1 """explain shape plan select /*+ leading(t3 alias1) */
count(*) from (select c1, c11 from t1 join (select c2, c22 from t2 join t4 on
c2 = c4) as alias2 on c1 = alias2.c2) as alias1 join t3 on alias1.c1 = t3.c3;"""
+ // qt_sql4_2 """explain shape plan select count(*) from (select /*+
leading(alias2 t1) */ c1, c11 from t1 join (select c2, c22 from t2 join t4 on
c2 = c4) as alias2 on c1 = alias2.c2) as alias1 join t3 on alias1.c1 = t3.c3;"""
+ // qt_sql4_3 """explain shape plan select count(*) from (select c1, c11
from t1 join (select /*+ leading(t4 t2) */ c2, c22 from t2 join t4 on c2 = c4)
as alias2 on c1 = alias2.c2) as alias1 join t3 on alias1.c1 = t3.c3;"""
+ // qt_sql4_4 """explain shape plan select /*+ leading(t3 alias1) */
count(*) from (select /*+ leading(alias2 t1) */ c1, c11 from t1 join (select
c2, c22 from t2 join t4 on c2 = c4) as alias2 on c1 = alias2.c2) as alias1 join
t3 on alias1.c1 = t3.c3;"""
+ // explain {
+ // sql """shape plan select /*+ leading(t3 alias1) */ count(*) from
(select c1, c11 from t1 join (select /*+ leading(t4 t2) */ c2, c22 from t2 join
t4 on c2 = c4) as alias2 on c1 = alias2.c2) as alias1 join t3 on alias1.c1 =
t3.c3;"""
+ // contains("SyntaxError: leading(t4 t2) Msg:one query block can only
have one leading clause")
+ // }
+ // explain {
+ // sql """shape plan select count(*) from (select /*+ leading(alias2
t1) */ c1, c11 from t1 join (select /*+ leading(t4 t2) */ c2, c22 from t2 join
t4 on c2 = c4) as alias2 on c1 = alias2.c2) as alias1 join t3 on alias1.c1 =
t3.c3;"""
+ // contains("SyntaxError: leading(t4 t2) Msg:one query block can only
have one leading clause")
+ // }
+ // explain {
+ // sql """shape plan select /*+ leading(t3 alias1) */ count(*) from
(select /*+ leading(alias2 t1) */ c1, c11 from t1 join (select /*+ leading(t4
t2) */ c2, c22 from t2 join t4 on c2 = c4) as alias2 on c1 = alias2.c2) as
alias1 join t3 on alias1.c1 = t3.c3;"""
+ // contains("UnUsed: leading(alias2 t1)")
+ // }
+
+ // qt_sql4_res_0 """select count(*) from (select c1, c11 from t1 join
(select c2, c22 from t2 join t4 on c2 = c4) as alias2 on c1 = alias2.c2) as
alias1 join t3 on alias1.c1 = t3.c3;"""
+ // qt_sql4_res_1 """select /*+ leading(t3 alias1) */ count(*) from (select
c1, c11 from t1 join (select c2, c22 from t2 join t4 on c2 = c4) as alias2 on
c1 = alias2.c2) as alias1 join t3 on alias1.c1 = t3.c3;"""
+ // qt_sql4_res_2 """select count(*) from (select /*+ leading(alias2 t1) */
c1, c11 from t1 join (select c2, c22 from t2 join t4 on c2 = c4) as alias2 on
c1 = alias2.c2) as alias1 join t3 on alias1.c1 = t3.c3;"""
+ // qt_sql4_res_3 """select count(*) from (select c1, c11 from t1 join
(select /*+ leading(t4 t2) */ c2, c22 from t2 join t4 on c2 = c4) as alias2 on
c1 = alias2.c2) as alias1 join t3 on alias1.c1 = t3.c3;"""
+ // qt_sql4_res_4 """select /*+ leading(t3 alias1) */ count(*) from (select
/*+ leading(alias2 t1) */ c1, c11 from t1 join (select c2, c22 from t2 join t4
on c2 = c4) as alias2 on c1 = alias2.c2) as alias1 join t3 on alias1.c1 =
t3.c3;"""
+ // qt_sql4_res_5 """select /*+ leading(t3 alias1) */ count(*) from (select
c1, c11 from t1 join (select /*+ leading(t4 t2) */ c2, c22 from t2 join t4 on
c2 = c4) as alias2 on c1 = alias2.c2) as alias1 join t3 on alias1.c1 = t3.c3;"""
+ // qt_sql4_res_6 """select count(*) from (select /*+ leading(alias2 t1) */
c1, c11 from t1 join (select /*+ leading(t4 t2) */ c2, c22 from t2 join t4 on
c2 = c4) as alias2 on c1 = alias2.c2) as alias1 join t3 on alias1.c1 = t3.c3;"""
+ // qt_sql4_res_7 """select /*+ leading(t3 alias1) */ count(*) from (select
/*+ leading(alias2 t1) */ c1, c11 from t1 join (select /*+ leading(t4 t2) */
c2, c22 from t2 join t4 on c2 = c4) as alias2 on c1 = alias2.c2) as alias1 join
t3 on alias1.c1 = t3.c3;"""
+
+ // // use cte in scalar query
+ // qt_sql5_2 """explain shape plan with cte as (select c11, c1 from t1)
SELECT c1 FROM cte group by c1 having sum(cte.c11) > (select /*+ leading(cte
t1) */ 0.05 * avg(t1.c11) from t1 join cte on t1.c1 = cte.c11 )"""
}
diff --git a/regression-test/suites/nereids_p0/hint/test_hint.groovy
b/regression-test/suites/nereids_p0/hint/test_hint.groovy
index d279b7c1a1d..81033e014f1 100644
--- a/regression-test/suites/nereids_p0/hint/test_hint.groovy
+++ b/regression-test/suites/nereids_p0/hint/test_hint.groovy
@@ -42,20 +42,20 @@ suite("test_hint") {
sql """create table t2 (c2 int, c22 int) distributed by hash(c2) buckets 3
properties('replication_num' = '1');"""
// test hint positions, remove join in order to make sure shape stable when no
use hint
- qt_select1_1 """explain shape plan select /*+ leading(t2 broadcast t1) */
count(*) from t1 join t2 on c1 = c2;"""
+ // qt_select1_1 """explain shape plan select /*+ leading(t2 broadcast t1)
*/ count(*) from t1 join t2 on c1 = c2;"""
- qt_select1_2 """explain shape plan /*+ leading(t2 broadcast t1) */ select
count(*) from t1;"""
+ // qt_select1_2 """explain shape plan /*+ leading(t2 broadcast t1) */
select count(*) from t1;"""
- qt_select1_3 """explain shape plan select /*+DBP: ROUTE={GROUP_ID(zjaq)}*/
count(*) from t1;"""
+ // qt_select1_3 """explain shape plan select /*+DBP:
ROUTE={GROUP_ID(zjaq)}*/ count(*) from t1;"""
- qt_select1_4 """explain shape plan/*+DBP: ROUTE={GROUP_ID(zjaq)}*/ select
count(*) from t1;"""
+ // qt_select1_4 """explain shape plan/*+DBP: ROUTE={GROUP_ID(zjaq)}*/
select count(*) from t1;"""
- qt_select1_5 """explain shape plan /*+ leading(t2 broadcast t1) */ select
/*+ leading(t2 broadcast t1) */ count(*) from t1 join t2 on c1 = c2;"""
+ // qt_select1_5 """explain shape plan /*+ leading(t2 broadcast t1) */
select /*+ leading(t2 broadcast t1) */ count(*) from t1 join t2 on c1 = c2;"""
- qt_select1_6 """explain shape plan/*+DBP: ROUTE={GROUP_ID(zjaq)}*/ select
/*+ leading(t2 broadcast t1) */ count(*) from t1 join t2 on c1 = c2;"""
+ // qt_select1_6 """explain shape plan/*+DBP: ROUTE={GROUP_ID(zjaq)}*/
select /*+ leading(t2 broadcast t1) */ count(*) from t1 join t2 on c1 = c2;"""
- qt_select1_7 """explain shape plan /*+ leading(t2 broadcast t1) */ select
/*+DBP: ROUTE={GROUP_ID(zjaq)}*/ count(*) from t1;"""
+ // qt_select1_7 """explain shape plan /*+ leading(t2 broadcast t1) */
select /*+DBP: ROUTE={GROUP_ID(zjaq)}*/ count(*) from t1;"""
- qt_select1_8 """explain shape plan /*+DBP: ROUTE={GROUP_ID(zjaq)}*/ select
/*+DBP: ROUTE={GROUP_ID(zjaq)}*/ count(*) from t1;"""
+ // qt_select1_8 """explain shape plan /*+DBP: ROUTE={GROUP_ID(zjaq)}*/
select /*+DBP: ROUTE={GROUP_ID(zjaq)}*/ count(*) from t1;"""
}
diff --git
a/regression-test/suites/nereids_rules_p0/eliminate_outer_join/eliminate_outer_join.groovy
b/regression-test/suites/nereids_rules_p0/eliminate_outer_join/eliminate_outer_join.groovy
index bbf3dbbe8ee..fc38e3be337 100644
---
a/regression-test/suites/nereids_rules_p0/eliminate_outer_join/eliminate_outer_join.groovy
+++
b/regression-test/suites/nereids_rules_p0/eliminate_outer_join/eliminate_outer_join.groovy
@@ -24,6 +24,7 @@ suite("eliminate_outer_join") {
sql "set disable_nereids_rules=PRUNE_EMPTY_PARTITION"
sql 'set be_number_for_test=3'
sql "set enable_parallel_result_sink=false;"
+ sql "set disable_join_reorder=true;"
sql """
DROP TABLE IF EXISTS t
diff --git
a/regression-test/suites/nereids_rules_p0/push_down_filter/extract_from_disjunction_in_join.groovy
b/regression-test/suites/nereids_rules_p0/push_down_filter/extract_from_disjunction_in_join.groovy
index 858f39e5e65..2132028b7ba 100644
---
a/regression-test/suites/nereids_rules_p0/push_down_filter/extract_from_disjunction_in_join.groovy
+++
b/regression-test/suites/nereids_rules_p0/push_down_filter/extract_from_disjunction_in_join.groovy
@@ -20,7 +20,11 @@ suite("extract_from_disjunction_in_join") {
sql "SET enable_fallback_to_original_planner=false"
sql "set ignore_shape_nodes='PhysicalDistribute,PhysicalProject'"
sql "set disable_nereids_rules=PRUNE_EMPTY_PARTITION"
- sql "set runtime_filter_mode=OFF"
+ sql """
+ set runtime_filter_mode=OFF;
+ set disable_join_reorder=true;
+ set disable_join_reorder=true;
+ """
sql "drop table if exists extract_from_disjunction_in_join_t1"
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
