This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-4.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-4.0 by this push:
new 16b253a55d3 branch-4.0: [fix](nereids) Allocate fresh ExprId for
constants when pushing project into Union #62296 (#63025)
16b253a55d3 is described below
commit 16b253a55d3eed6c033a0e0fb023cd8314538399
Author: yujun <[email protected]>
AuthorDate: Mon May 11 15:33:09 2026 +0800
branch-4.0: [fix](nereids) Allocate fresh ExprId for constants when pushing
project into Union #62296 (#63025)
cherry pick from #62296
---------
Co-authored-by: Copilot <[email protected]>
---
.../rules/rewrite/PushProjectIntoUnion.java | 16 +-
.../rules/rewrite/PushProjectThroughUnion.java | 17 +-
.../doris/nereids/trees/expressions/Alias.java | 4 +
.../rules/rewrite/PushProjectIntoUnionTest.java | 133 ++++++++++++++++
.../rules/rewrite/PushProjectThroughUnionTest.java | 122 +++++++++++++++
.../set_operations/set_operation_exprid_reuse.out | 44 ++++++
.../set_operation_exprid_reuse.groovy | 174 +++++++++++++++++++++
7 files changed, 506 insertions(+), 4 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PushProjectIntoUnion.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PushProjectIntoUnion.java
index 78610c949a7..8b109f9dae5 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PushProjectIntoUnion.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PushProjectIntoUnion.java
@@ -26,6 +26,7 @@ import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.expressions.NamedExpression;
import org.apache.doris.nereids.trees.expressions.Slot;
import org.apache.doris.nereids.trees.expressions.SlotReference;
+import org.apache.doris.nereids.trees.expressions.StatementScopeIdGenerator;
import org.apache.doris.nereids.trees.plans.algebra.SetOperation.Qualifier;
import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
import org.apache.doris.nereids.trees.plans.logical.LogicalUnion;
@@ -67,11 +68,24 @@ public class PushProjectIntoUnion extends
OneRewriteRuleFactory {
ImmutableList.Builder<NamedExpression> newProjections =
ImmutableList.builder();
for (NamedExpression old : p.getProjects()) {
if (old instanceof SlotReference) {
+ // replaceRootMap.get(old) is the original constant
NamedExpression from
+ // constExprs (each row owns a distinct ExprId, none
equal to the new
+ // UNION output ExprId), so it can be reused as-is.
newProjections.add((NamedExpression)
FoldConstantRule.evaluate(replaceRootMap.get(old),
expressionRewriteContext));
} else {
+ // `old` must be an Alias (Nereids LogicalProject
invariant for non-Slot
+ // projections). Its ExprId equals the new UNION
output ExprId, since
+ // p.getOutput() becomes the UNION output below.
Feeding the original
+ // Alias into the rewriter would preserve that outer
ExprId on every
+ // constant row and collide with the UNION output.
Reassign a fresh
+ // ExprId on the Alias first, then run the SlotRef ->
constant rewrite
+ // and constant folding on the new Alias; this
preserves the Alias'
+ // name/qualifier/nameFromChild while breaking the
ExprId collision.
+ Alias oldAlias = (Alias) old;
+ Alias reIdAlias =
oldAlias.withExprId(StatementScopeIdGenerator.newExprId());
newProjections.add((NamedExpression)
FoldConstantRule.evaluate(
- ExpressionUtils.replaceNameExpression(old,
replaceMap), expressionRewriteContext));
+ ExpressionUtils.replace(reIdAlias,
replaceMap), expressionRewriteContext));
}
}
newConstExprs.add(newProjections.build());
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PushProjectThroughUnion.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PushProjectThroughUnion.java
index a07ec94f091..dfaef55b56c 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PushProjectThroughUnion.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PushProjectThroughUnion.java
@@ -24,6 +24,7 @@ import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.expressions.NamedExpression;
import org.apache.doris.nereids.trees.expressions.Slot;
import org.apache.doris.nereids.trees.expressions.SlotReference;
+import org.apache.doris.nereids.trees.expressions.StatementScopeIdGenerator;
import org.apache.doris.nereids.trees.plans.Plan;
import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
import org.apache.doris.nereids.trees.plans.logical.LogicalSetOperation;
@@ -80,13 +81,23 @@ public class PushProjectThroughUnion extends
OneRewriteRuleFactory {
ImmutableList.Builder<NamedExpression> newOneRowProject =
ImmutableList.builder();
for (NamedExpression outerProject : projects) {
if (outerProject instanceof Slot) {
+ // replaceMap value is the original constant
NamedExpression. Each
+ // constant row owns a distinct ExprId different from
the new UNION
+ // output ExprId, so it can be reused as-is.
newOneRowProject.add((NamedExpression)
replaceMap.getOrDefault(outerProject, outerProject));
} else {
- Expression replacedOutput = outerProject.rewriteUp(e
-> {
+ // `outerProject` is an Alias (canPushProject only
allows Slot or
+ // Alias(Cast(Slot))). Its ExprId equals the new UNION
output ExprId
+ // (newOutput below uses outerProject.toSlot()).
Reassigning a fresh
+ // ExprId on the Alias first breaks the collision; the
subsequent
+ // rewriteUp only rewrites the inner SlotReferences,
leaving the
+ // outer Alias' name/qualifier/nameFromChild intact.
+ Alias oldAlias = (Alias) outerProject;
+ Alias reIdAlias =
oldAlias.withExprId(StatementScopeIdGenerator.newExprId());
+ newOneRowProject.add((NamedExpression)
reIdAlias.rewriteUp(e -> {
Expression mappingExpr = replaceMap.get(e);
return mappingExpr == null ? e : /* remove alias
*/ mappingExpr.child(0);
- });
- newOneRowProject.add((NamedExpression) replacedOutput);
+ }));
}
}
newConstantListBuilder.add(newOneRowProject.build());
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Alias.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Alias.java
index 2ee92281c76..edb416014a6 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Alias.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Alias.java
@@ -171,6 +171,10 @@ public class Alias extends NamedExpression implements
UnaryExpression {
return new Alias(exprId, children, name, qualifier, nameFromChild);
}
+ public Alias withExprId(ExprId exprId) {
+ return new Alias(exprId, children, name, qualifier, nameFromChild);
+ }
+
public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
return visitor.visitAlias(this, context);
}
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/PushProjectIntoUnionTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/PushProjectIntoUnionTest.java
new file mode 100644
index 00000000000..42f4f513b8b
--- /dev/null
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/PushProjectIntoUnionTest.java
@@ -0,0 +1,133 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package org.apache.doris.nereids.rules.rewrite;
+
+import org.apache.doris.nereids.trees.expressions.Alias;
+import org.apache.doris.nereids.trees.expressions.ExprId;
+import org.apache.doris.nereids.trees.expressions.Multiply;
+import org.apache.doris.nereids.trees.expressions.NamedExpression;
+import org.apache.doris.nereids.trees.expressions.SlotReference;
+import org.apache.doris.nereids.trees.expressions.literal.IntegerLiteral;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.algebra.SetOperation.Qualifier;
+import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
+import org.apache.doris.nereids.trees.plans.logical.LogicalUnion;
+import org.apache.doris.nereids.types.IntegerType;
+import org.apache.doris.nereids.util.MemoTestUtils;
+import org.apache.doris.nereids.util.PlanChecker;
+
+import com.google.common.collect.ImmutableList;
+import org.junit.jupiter.api.Assertions;
+import org.junit.jupiter.api.Test;
+
+import java.util.HashSet;
+import java.util.List;
+import java.util.Set;
+
+/**
+ * Plain unit test for {@link PushProjectIntoUnion}: builds a
Project(LogicalUnion)
+ * tree directly and applies only the rule, so the assertions target the rule's
+ * output without interference from any other rewrite.
+ *
+ * <p>Regression contract: after the rule fires, every constant row must hold
+ * NamedExpressions whose ExprIds are (a) different from the new UNION output
+ * ExprIds and (b) different from the corresponding constants in any other row
+ * of the same column.
+ */
+public class PushProjectIntoUnionTest {
+
+ @Test
+ public void testConstantExprIdsDistinctFromUnionOutputAndAcrossRows() {
+ // Original UNION: outputs=[s#10], no children, three constant rows:
1, 3, NULL
+ // (already merged from OneRowRelations by an earlier rule). Each
row's Alias
+ // owns its own ExprId.
+ SlotReference unionOutput = new SlotReference(new ExprId(10), "s",
+ IntegerType.INSTANCE, true, ImmutableList.of());
+ NamedExpression row0 = new Alias(new ExprId(1), new IntegerLiteral(1),
"1");
+ NamedExpression row1 = new Alias(new ExprId(2), new IntegerLiteral(3),
"3");
+ NamedExpression row2 = new Alias(new ExprId(3), new IntegerLiteral(7),
"7");
+ LogicalUnion union = new LogicalUnion(Qualifier.ALL,
+ ImmutableList.of(unionOutput),
+ ImmutableList.of(),
+ ImmutableList.of(ImmutableList.of(row0),
ImmutableList.of(row1), ImmutableList.of(row2)),
+ false,
+ ImmutableList.of());
+
+ // Parent project: (s * 2) AS n. The Alias here owns ExprId 100, which
will
+ // become the new UNION output ExprId after the rule fires. Without
the fix,
+ // this same ExprId would be reused in every constant row.
+ Alias parentAlias = new Alias(new ExprId(100),
+ new Multiply(unionOutput, new IntegerLiteral(2)), "n");
+ LogicalProject<LogicalUnion> project = new LogicalProject<>(
+ ImmutableList.<NamedExpression>of(parentAlias), union);
+
+ Plan rewritten =
PlanChecker.from(MemoTestUtils.createConnectContext(), project)
+ .applyTopDown(new PushProjectIntoUnion())
+ .getPlan();
+
+ // After the rule fires, the project is absorbed and the root becomes
a UNION.
+ LogicalUnion newUnion = findUnion(rewritten);
+ Assertions.assertNotNull(newUnion, "expected a LogicalUnion at/under
the root after rewrite");
+
+ List<? extends NamedExpression> outputs = newUnion.getOutputs();
+ Assertions.assertEquals(1, outputs.size());
+ // The new UNION output keeps the parent project's output ExprId (100).
+ Assertions.assertEquals(parentAlias.getExprId(),
outputs.get(0).getExprId());
+
+ Set<ExprId> outputIds = new HashSet<>();
+ for (NamedExpression out : outputs) {
+ outputIds.add(out.getExprId());
+ }
+
+ List<List<NamedExpression>> consts = newUnion.getConstantExprsList();
+ Assertions.assertEquals(3, consts.size(), "expected three constant
rows");
+
+ // (a) constant ExprIds must NOT collide with any UNION output ExprId.
+ for (int row = 0; row < consts.size(); row++) {
+ for (int col = 0; col < consts.get(row).size(); col++) {
+ ExprId cid = consts.get(row).get(col).getExprId();
+ Assertions.assertFalse(outputIds.contains(cid),
+ "constant ExprId must not collide with UNION output;
row="
+ + row + ", col=" + col + ", id=" + cid);
+ }
+ }
+
+ // (b) for each column, all rows must have distinct constant ExprIds.
+ for (int col = 0; col < outputs.size(); col++) {
+ Set<ExprId> seen = new HashSet<>();
+ for (int row = 0; row < consts.size(); row++) {
+ ExprId cid = consts.get(row).get(col).getExprId();
+ Assertions.assertTrue(seen.add(cid),
+ "constant ExprId duplicated across rows for column " +
col + ": " + cid);
+ }
+ }
+ }
+
+ private LogicalUnion findUnion(Plan p) {
+ if (p instanceof LogicalUnion) {
+ return (LogicalUnion) p;
+ }
+ for (Plan c : p.children()) {
+ LogicalUnion u = findUnion(c);
+ if (u != null) {
+ return u;
+ }
+ }
+ return null;
+ }
+}
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/PushProjectThroughUnionTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/PushProjectThroughUnionTest.java
new file mode 100644
index 00000000000..328c390d52f
--- /dev/null
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/PushProjectThroughUnionTest.java
@@ -0,0 +1,122 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package org.apache.doris.nereids.rules.rewrite;
+
+import org.apache.doris.nereids.trees.expressions.Alias;
+import org.apache.doris.nereids.trees.expressions.Cast;
+import org.apache.doris.nereids.trees.expressions.ExprId;
+import org.apache.doris.nereids.trees.expressions.NamedExpression;
+import org.apache.doris.nereids.trees.expressions.SlotReference;
+import org.apache.doris.nereids.trees.expressions.literal.IntegerLiteral;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.algebra.SetOperation.Qualifier;
+import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
+import org.apache.doris.nereids.trees.plans.logical.LogicalUnion;
+import org.apache.doris.nereids.types.BigIntType;
+import org.apache.doris.nereids.types.IntegerType;
+import org.apache.doris.nereids.util.MemoTestUtils;
+import org.apache.doris.nereids.util.PlanChecker;
+
+import com.google.common.collect.ImmutableList;
+import org.junit.jupiter.api.Assertions;
+import org.junit.jupiter.api.Test;
+
+import java.util.HashSet;
+import java.util.List;
+import java.util.Set;
+
+/**
+ * Plain unit test for {@link PushProjectThroughUnion}: directly builds a
+ * Project(LogicalUnion(...)) where the union holds a non-empty
+ * {@code constantExprsList} (the mixed-union shape produced after
+ * {@code MergeOneRowRelationIntoUnion} folds a {@code LogicalOneRowRelation}
+ * into a union sibling) and asserts that after the rule rewrites the constant
+ * rows, no constant cell's ExprId collides with the new UNION output ExprId.
+ */
+public class PushProjectThroughUnionTest {
+
+ @Test
+ public void testConstantExprIdsDistinctFromUnionOutput() {
+ // Build a LogicalUnion that owns a single constant row and zero
regular
+ // children. The arity does not affect the constant-row rewrite branch
+ // we want to cover; what matters is that constantExprsList is
non-empty
+ // and the parent project carries a non-Slot expression that triggers
+ // the else branch of doPushProject.
+ SlotReference unionOutput = new SlotReference(new ExprId(10), "s",
+ IntegerType.INSTANCE, true, ImmutableList.of());
+ NamedExpression row0 = new Alias(new ExprId(1), new
IntegerLiteral(99), "s");
+ LogicalUnion union = new LogicalUnion(Qualifier.ALL,
+ ImmutableList.of(unionOutput),
+ ImmutableList.of(),
+ ImmutableList.of(ImmutableList.of(row0)),
+ false,
+ ImmutableList.of());
+
+ // Parent project: CAST(s AS BIGINT) AS n. canPushProject accepts this
+ // because the inner expression covered by the cast is a SlotReference.
+ // The Alias's ExprId (100) becomes the new UNION output ExprId via
+ // project.toSlot(). Without the fix, the constant row would also keep
+ // ExprId 100 and collide.
+ Alias parentAlias = new Alias(new ExprId(100),
+ new Cast(unionOutput, BigIntType.INSTANCE), "n");
+ LogicalProject<LogicalUnion> project = new LogicalProject<>(
+ ImmutableList.<NamedExpression>of(parentAlias), union);
+
+ Plan rewritten =
PlanChecker.from(MemoTestUtils.createConnectContext(), project)
+ .applyTopDown(new PushProjectThroughUnion())
+ .getPlan();
+
+ LogicalUnion newUnion = findUnion(rewritten);
+ Assertions.assertNotNull(newUnion, "expected a LogicalUnion at/under
the root after rewrite");
+
+ List<? extends NamedExpression> outputs = newUnion.getOutputs();
+ Assertions.assertEquals(1, outputs.size());
+ // The new UNION output reuses the parent project's output ExprId.
+ Assertions.assertEquals(parentAlias.getExprId(),
outputs.get(0).getExprId());
+
+ Set<ExprId> outputIds = new HashSet<>();
+ for (NamedExpression out : outputs) {
+ outputIds.add(out.getExprId());
+ }
+
+ List<List<NamedExpression>> consts = newUnion.getConstantExprsList();
+ Assertions.assertEquals(1, consts.size(), "expected one constant row");
+
+ for (int row = 0; row < consts.size(); row++) {
+ for (int col = 0; col < consts.get(row).size(); col++) {
+ ExprId cid = consts.get(row).get(col).getExprId();
+ Assertions.assertFalse(outputIds.contains(cid),
+ "constant ExprId must not collide with UNION output;
row="
+ + row + ", col=" + col + ", id=" + cid);
+ }
+ }
+ }
+
+ private LogicalUnion findUnion(Plan p) {
+ if (p instanceof LogicalUnion) {
+ return (LogicalUnion) p;
+ }
+ for (Plan c : p.children()) {
+ LogicalUnion u = findUnion(c);
+ if (u != null) {
+ return u;
+ }
+ }
+ return null;
+ }
+}
diff --git
a/regression-test/data/query_p0/set_operations/set_operation_exprid_reuse.out
b/regression-test/data/query_p0/set_operations/set_operation_exprid_reuse.out
new file mode 100644
index 00000000000..bf8c790057c
--- /dev/null
+++
b/regression-test/data/query_p0/set_operations/set_operation_exprid_reuse.out
@@ -0,0 +1,44 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !intersect_cte_expr --
+\N
+2
+
+-- !except_cte_expr --
+6
+
+-- !union_distinct_cte_expr --
+\N
+2
+4
+6
+
+-- !intersect_cast --
+\N
+1
+
+-- !intersect_rename --
+\N
+1
+
+-- !nested_set_ops --
+1
+2
+
+-- !intersect_multi_col --
+20 Y
+30 Z
+
+-- !intersect_nulls --
+\N
+1
+
+-- !chained_intersect --
+6
+
+-- !intersect_table --
+4 B
+6 C
+
+-- !except_table --
+2 A
+
diff --git
a/regression-test/suites/query_p0/set_operations/set_operation_exprid_reuse.groovy
b/regression-test/suites/query_p0/set_operations/set_operation_exprid_reuse.groovy
new file mode 100644
index 00000000000..97b36ca1e34
--- /dev/null
+++
b/regression-test/suites/query_p0/set_operations/set_operation_exprid_reuse.groovy
@@ -0,0 +1,174 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+// Regression test for ExprId reuse between set operation output and child
output.
+// When Nereids optimizer produces a UNION with a single regular child where
the
+// output ExprId equals the child's output ExprId, the plan translator must
translate
+// child result expressions before creating the output tuple descriptor to
avoid
+// the ExprId->SlotRef mapping being overwritten.
+suite("set_operation_exprid_reuse", "query,p0") {
+ sql """
+ SET enable_nereids_planner=true;
+ SET enable_fallback_to_original_planner=false;
+ """
+
+ // Original reproducing query from CIR-19889:
+ // CTE + INTERSECT with expression computation causes ExprId reuse
+ order_qt_intersect_cte_expr """
+ WITH
+ tbl0(`n`) AS (SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT NULL),
+ tbl1(`n`) AS (SELECT 2 UNION ALL SELECT NULL UNION ALL SELECT 1)
+ (
+ SELECT (`n` * 2) AS `n` FROM tbl0
+ INTERSECT
+ SELECT (`n` * 2) AS `n` FROM tbl1
+ )
+ """
+
+ // EXCEPT with CTE and expression
+ order_qt_except_cte_expr """
+ WITH
+ tbl0(`n`) AS (SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT NULL),
+ tbl1(`n`) AS (SELECT 2 UNION ALL SELECT NULL UNION ALL SELECT 1)
+ (
+ SELECT (`n` * 2) AS `n` FROM tbl0
+ EXCEPT
+ SELECT (`n` * 2) AS `n` FROM tbl1
+ )
+ """
+
+ // UNION DISTINCT with CTE and expression
+ order_qt_union_distinct_cte_expr """
+ WITH
+ tbl0(`n`) AS (SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT NULL),
+ tbl1(`n`) AS (SELECT 2 UNION ALL SELECT NULL UNION ALL SELECT 1)
+ (
+ SELECT (`n` * 2) AS `n` FROM tbl0
+ UNION
+ SELECT (`n` * 2) AS `n` FROM tbl1
+ )
+ """
+
+ // CAST projection through Union (PushProjectThroughUnion path)
+ order_qt_intersect_cast """
+ WITH
+ tbl0(`n`) AS (SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT NULL),
+ tbl1(`n`) AS (SELECT 2 UNION ALL SELECT NULL UNION ALL SELECT 1)
+ (
+ SELECT CAST(`n` AS BIGINT) AS `n` FROM tbl0
+ INTERSECT
+ SELECT CAST(`n` AS BIGINT) AS `n` FROM tbl1
+ )
+ """
+
+ // Plain rename projection through Union (PushProjectThroughUnion slot
path)
+ order_qt_intersect_rename """
+ WITH
+ tbl0(`n`) AS (SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT NULL),
+ tbl1(`n`) AS (SELECT 2 UNION ALL SELECT NULL UNION ALL SELECT 1)
+ (
+ SELECT `n` AS `m` FROM tbl0
+ INTERSECT
+ SELECT `n` AS `m` FROM tbl1
+ )
+ """
+
+ // Nested set operations
+ order_qt_nested_set_ops """
+ SELECT * FROM (SELECT 1 a INTERSECT SELECT 1 a) t1
+ UNION
+ SELECT * FROM (SELECT 2 a EXCEPT SELECT 3 a) t2
+ """
+
+ // INTERSECT with multiple columns and expressions
+ order_qt_intersect_multi_col """
+ WITH
+ t1(a, b) AS (SELECT 1, 'x' UNION ALL SELECT 2, 'y' UNION ALL
SELECT 3, 'z'),
+ t2(a, b) AS (SELECT 2, 'y' UNION ALL SELECT 3, 'z' UNION ALL
SELECT 4, 'w')
+ (
+ SELECT a * 10 AS a, upper(b) AS b FROM t1
+ INTERSECT
+ SELECT a * 10 AS a, upper(b) AS b FROM t2
+ )
+ """
+
+ // INTERSECT with NULL handling
+ order_qt_intersect_nulls """
+ WITH
+ t1(a) AS (SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT 1),
+ t2(a) AS (SELECT NULL UNION ALL SELECT 1 UNION ALL SELECT 2)
+ (
+ SELECT a FROM t1
+ INTERSECT
+ SELECT a FROM t2
+ )
+ """
+
+ // Multiple INTERSECT chained
+ order_qt_chained_intersect """
+ WITH
+ t1(n) AS (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3),
+ t2(n) AS (SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4),
+ t3(n) AS (SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5)
+ (
+ SELECT n * 2 AS n FROM t1
+ INTERSECT
+ SELECT n * 2 AS n FROM t2
+ INTERSECT
+ SELECT n * 2 AS n FROM t3
+ )
+ """
+
+ // INTERSECT with table data (not just constants)
+ sql "drop table if exists set_op_exprid_t1"
+ sql "drop table if exists set_op_exprid_t2"
+
+ sql """
+ CREATE TABLE set_op_exprid_t1 (
+ id INT NOT NULL,
+ val VARCHAR(50) NOT NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES ("replication_num" = "1")
+ """
+
+ sql """
+ CREATE TABLE set_op_exprid_t2 (
+ id INT NOT NULL,
+ val VARCHAR(50) NOT NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES ("replication_num" = "1")
+ """
+
+ sql "INSERT INTO set_op_exprid_t1 VALUES (1, 'a'), (2, 'b'), (3, 'c')"
+ sql "INSERT INTO set_op_exprid_t2 VALUES (2, 'b'), (3, 'c'), (4, 'd')"
+
+ order_qt_intersect_table """
+ SELECT id * 2, upper(val) FROM set_op_exprid_t1
+ INTERSECT
+ SELECT id * 2, upper(val) FROM set_op_exprid_t2
+ """
+
+ order_qt_except_table """
+ SELECT id * 2, upper(val) FROM set_op_exprid_t1
+ EXCEPT
+ SELECT id * 2, upper(val) FROM set_op_exprid_t2
+ """
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
