This is an automated email from the ASF dual-hosted git repository.
morrysnow 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 dc7e5ca039 [fix](nereids) uncorrelated subquery can't get the correct
result (#12421)
dc7e5ca039 is described below
commit dc7e5ca039548c037d400c5caea3ad89d3ed2e5a
Author: zhengshiJ <32082872+zhengs...@users.noreply.github.com>
AuthorDate: Fri Sep 9 18:08:34 2022 +0800
[fix](nereids) uncorrelated subquery can't get the correct result (#12421)
When the current non-correlated subquery is executed, an error will be
reported that the corresponding column cannot be found.
The reason is that the tupleID of the child obtained in
visitPhysicalNestedLoopJoin is not consistent with the child.
The non-correlated subquery will trigger this bug because it uses crossJoin.
At the same time, sub-query regression tests for non-associative and
complex scenarios have been added
Co-authored-by: morrySnow <morrys...@126.com>
---
.../glue/translator/PhysicalPlanTranslator.java | 28 +++---
.../glue/translator/PlanTranslatorContext.java | 15 ++-
.../rules/rewrite/logical/InApplyToJoin.java | 7 +-
.../rules/rewrite/logical/ScalarApplyToJoin.java | 8 +-
.../trees/expressions/AssertNumRowsElement.java | 5 +-
.../trees/plans/logical/LogicalAssertNumRows.java | 4 +-
.../apache/doris/planner/AssertNumRowsNode.java | 6 +-
.../org/apache/doris/planner/CrossJoinNode.java | 19 ++++
.../nereids_syntax_p0/sub_query_correlated.out | 107 +++++++++++++++++++--
.../nereids_syntax_p0/sub_query_correlated.groovy | 97 ++++++++++---------
10 files changed, 205 insertions(+), 91 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/PhysicalPlanTranslator.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/PhysicalPlanTranslator.java
index fea1194fba..7219a435a5 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/PhysicalPlanTranslator.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/PhysicalPlanTranslator.java
@@ -90,6 +90,7 @@ import com.google.common.collect.Sets;
import org.apache.commons.collections.CollectionUtils;
import java.util.ArrayList;
+import java.util.Collection;
import java.util.Collections;
import java.util.HashSet;
import java.util.List;
@@ -463,18 +464,16 @@ public class PhysicalPlanTranslator extends
DefaultPlanVisitor<PlanFragment, Pla
// but BE need left child's output must be before right child's output.
// So we need to swap the output order of left and right child if
necessary.
// TODO: revert this after Nereids could ensure the output order is
correct.
- TupleDescriptor leftChildOutputTupleDesc =
leftPlanRoot.getOutputTupleDesc();
- TupleDescriptor leftTuple =
- leftChildOutputTupleDesc != null ? leftChildOutputTupleDesc :
context.getTupleDesc(leftPlanRoot);
- TupleDescriptor rightChildOutputTupleDesc =
rightPlanRoot.getOutputTupleDesc();
- TupleDescriptor rightTuple =
- rightChildOutputTupleDesc != null ? rightChildOutputTupleDesc
: context.getTupleDesc(rightPlanRoot);
+ List<TupleDescriptor> leftTuples = context.getTupleDesc(leftPlanRoot);
+ List<TupleDescriptor> rightTuples =
context.getTupleDesc(rightPlanRoot);
TupleDescriptor outputDescriptor = context.generateTupleDesc();
Map<ExprId, SlotReference> slotReferenceMap = Maps.newHashMap();
hashJoin.getOutput().stream()
.map(SlotReference.class::cast)
.forEach(s -> slotReferenceMap.put(s.getExprId(), s));
- List<Expr> srcToOutput = Stream.concat(leftTuple.getSlots().stream(),
rightTuple.getSlots().stream())
+ List<Expr> srcToOutput = Stream.concat(leftTuples.stream(),
rightTuples.stream())
+ .map(TupleDescriptor::getSlots)
+ .flatMap(Collection::stream)
.map(sd -> context.findExprId(sd.getId()))
.map(slotReferenceMap::get)
.filter(Objects::nonNull)
@@ -512,12 +511,19 @@ public class PhysicalPlanTranslator extends
DefaultPlanVisitor<PlanFragment, Pla
PlanNode leftFragmentPlanRoot = leftFragment.getPlanRoot();
PlanNode rightFragmentPlanRoot = rightFragment.getPlanRoot();
if (JoinUtils.shouldNestedLoopJoin(nestedLoopJoin)) {
- CrossJoinNode crossJoinNode =
- new CrossJoinNode(context.nextPlanNodeId(),
leftFragmentPlanRoot, rightFragmentPlanRoot, null);
+ List<TupleDescriptor> leftTuples =
context.getTupleDesc(leftFragmentPlanRoot);
+ List<TupleDescriptor> rightTuples =
context.getTupleDesc(rightFragmentPlanRoot);
+ List<TupleId> tupleIds = Stream.concat(leftTuples.stream(),
rightTuples.stream())
+ .map(TupleDescriptor::getId)
+ .collect(Collectors.toList());
+
+ CrossJoinNode crossJoinNode = new
CrossJoinNode(context.nextPlanNodeId(),
+ leftFragmentPlanRoot, rightFragmentPlanRoot, tupleIds);
rightFragment.getPlanRoot().setCompactData(false);
crossJoinNode.setChild(0, leftFragment.getPlanRoot());
connectChildFragment(crossJoinNode, 1, leftFragment,
rightFragment, context);
leftFragment.setPlanRoot(crossJoinNode);
+
return leftFragment;
} else {
throw new RuntimeException("Physical nested loop join could not
execute with equal join condition.");
@@ -650,9 +656,9 @@ public class PhysicalPlanTranslator extends
DefaultPlanVisitor<PlanFragment, Pla
PlanTranslatorContext context) {
PlanFragment inputFragment = assertNumRows.child(0).accept(this,
context);
//create assertNode
- PlanNode child = inputFragment.getPlanRoot();
AssertNumRowsNode assertNumRowsNode = new
AssertNumRowsNode(context.nextPlanNodeId(),
- child,
ExpressionTranslator.translateAssert(assertNumRows.getAssertNumRowsElement()));
+ inputFragment.getPlanRoot(),
+
ExpressionTranslator.translateAssert(assertNumRows.getAssertNumRowsElement()));
PlanFragment mergeFragment = createParentFragment(inputFragment,
DataPartition.UNPARTITIONED, context);
mergeFragment.addPlanRoot(assertNumRowsNode);
return mergeFragment;
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/PlanTranslatorContext.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/PlanTranslatorContext.java
index 24b21d5f0d..a3f442640e 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/PlanTranslatorContext.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/PlanTranslatorContext.java
@@ -39,6 +39,7 @@ import com.google.common.collect.Maps;
import java.util.List;
import java.util.Map;
import java.util.Optional;
+import java.util.stream.Collectors;
/**
* Context of physical plan.
@@ -132,15 +133,11 @@ public class PlanTranslatorContext {
return slotDescriptor;
}
- /**
- * in Nereids, all node only has one TupleDescriptor, so we can use the
first one.
- *
- * @param planNode the node to get the TupleDescriptor
- *
- * @return plan node's tuple descriptor
- */
- public TupleDescriptor getTupleDesc(PlanNode planNode) {
- return descTable.getTupleDesc(planNode.getOutputTupleIds().get(0));
+ public List<TupleDescriptor> getTupleDesc(PlanNode planNode) {
+ if (planNode.getOutputTupleDesc() != null) {
+ return Lists.newArrayList(planNode.getOutputTupleDesc());
+ }
+ return
planNode.getOutputTupleIds().stream().map(this::getTupleDesc).collect(Collectors.toList());
}
public TupleDescriptor getTupleDesc(TupleId tupleId) {
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/InApplyToJoin.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/InApplyToJoin.java
index baf459c983..6a354d0b4b 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/InApplyToJoin.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/InApplyToJoin.java
@@ -26,7 +26,6 @@ import org.apache.doris.nereids.trees.expressions.InSubquery;
import org.apache.doris.nereids.trees.plans.JoinType;
import org.apache.doris.nereids.trees.plans.logical.LogicalApply;
import org.apache.doris.nereids.trees.plans.logical.LogicalJoin;
-import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
import org.apache.doris.nereids.util.ExpressionUtils;
import com.google.common.collect.Lists;
@@ -54,15 +53,13 @@ public class InApplyToJoin extends OneRewriteRuleFactory {
apply.right().getOutput().get(0));
}
- LogicalJoin newJoin;
if (((InSubquery) apply.getSubqueryExpr()).isNot()) {
- newJoin = new LogicalJoin<>(JoinType.LEFT_ANTI_JOIN,
Lists.newArrayList(), Optional.of(predicate),
+ return new LogicalJoin<>(JoinType.LEFT_ANTI_JOIN,
Lists.newArrayList(), Optional.of(predicate),
apply.left(), apply.right());
} else {
- newJoin = new LogicalJoin<>(JoinType.LEFT_SEMI_JOIN,
Lists.newArrayList(), Optional.of(predicate),
+ return new LogicalJoin<>(JoinType.LEFT_SEMI_JOIN,
Lists.newArrayList(), Optional.of(predicate),
apply.left(), apply.right());
}
- return new LogicalProject(apply.left().getOutput(), newJoin);
}).toRule(RuleType.IN_APPLY_TO_JOIN);
}
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/ScalarApplyToJoin.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/ScalarApplyToJoin.java
index 944f9c89a8..775628cc7b 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/ScalarApplyToJoin.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/ScalarApplyToJoin.java
@@ -21,19 +21,15 @@ import org.apache.doris.nereids.rules.Rule;
import org.apache.doris.nereids.rules.RuleType;
import org.apache.doris.nereids.rules.rewrite.OneRewriteRuleFactory;
import org.apache.doris.nereids.trees.expressions.AssertNumRowsElement;
-import org.apache.doris.nereids.trees.expressions.Slot;
import org.apache.doris.nereids.trees.plans.JoinType;
import org.apache.doris.nereids.trees.plans.Plan;
import org.apache.doris.nereids.trees.plans.logical.LogicalApply;
import org.apache.doris.nereids.trees.plans.logical.LogicalAssertNumRows;
import org.apache.doris.nereids.trees.plans.logical.LogicalJoin;
import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
-import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
import com.google.common.collect.Lists;
-import java.util.List;
-
/**
* Convert scalarApply to LogicalJoin.
*
@@ -58,10 +54,8 @@ public class ScalarApplyToJoin extends OneRewriteRuleFactory
{
1, apply.getSubqueryExpr().toString(),
AssertNumRowsElement.Assertion.EQ),
(LogicalPlan) apply.right());
- LogicalJoin newJoin = new LogicalJoin<>(JoinType.CROSS_JOIN,
+ return new LogicalJoin<>(JoinType.CROSS_JOIN,
(LogicalPlan) apply.left(), assertNumRows);
- List<Slot> projects = ((LogicalPlan) apply.left()).getOutput();
- return new LogicalProject(projects, newJoin);
}
private Plan correlatedToJoin(LogicalApply apply) {
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/AssertNumRowsElement.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/AssertNumRowsElement.java
index 60ae3a2e35..391d5fdbae 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/AssertNumRowsElement.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/AssertNumRowsElement.java
@@ -73,9 +73,10 @@ public class AssertNumRowsElement extends Expression
implements LeafExpression {
@Override
public String toString() {
- return Utils.toSqlString("desiredNumOfRows: ",
+ return Utils.toSqlString("AssertNumRowsElement",
+ "desiredNumOfRows: ",
Long.toString(desiredNumOfRows),
- "assertion: " + assertion);
+ "assertion: ", assertion);
}
@Override
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalAssertNumRows.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalAssertNumRows.java
index f4063eba53..ebeaad9748 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalAssertNumRows.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/logical/LogicalAssertNumRows.java
@@ -25,6 +25,7 @@ import org.apache.doris.nereids.trees.expressions.Slot;
import org.apache.doris.nereids.trees.plans.Plan;
import org.apache.doris.nereids.trees.plans.PlanType;
import org.apache.doris.nereids.trees.plans.visitor.PlanVisitor;
+import org.apache.doris.nereids.util.Utils;
import com.google.common.base.Preconditions;
import com.google.common.collect.ImmutableList;
@@ -59,7 +60,8 @@ public class LogicalAssertNumRows<CHILD_TYPE extends Plan>
extends LogicalUnary<
@Override
public String toString() {
- return "LogicalAssertNumRows (" + assertNumRowsElement + ")";
+ return Utils.toSqlString("LogicalAssertNumRows",
+ "assertNumRowsElement", assertNumRowsElement);
}
@Override
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/planner/AssertNumRowsNode.java
b/fe/fe-core/src/main/java/org/apache/doris/planner/AssertNumRowsNode.java
index 969995e4b5..251cc48225 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/planner/AssertNumRowsNode.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/planner/AssertNumRowsNode.java
@@ -49,7 +49,11 @@ public class AssertNumRowsNode extends PlanNode {
this.subqueryString = assertNumRowsElement.getSubqueryString();
this.assertion = assertNumRowsElement.getAssertion();
this.children.add(input);
- this.tupleIds.addAll(input.getTupleIds());
+ if (input.getOutputTupleDesc() != null) {
+ this.tupleIds.add(input.getOutputTupleDesc().getId());
+ } else {
+ this.tupleIds.addAll(input.getTupleIds());
+ }
this.tblRefIds.addAll(input.getTblRefIds());
this.nullableTupleIds.addAll(input.getNullableTupleIds());
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/planner/CrossJoinNode.java
b/fe/fe-core/src/main/java/org/apache/doris/planner/CrossJoinNode.java
index ac8554c662..2e06155698 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/planner/CrossJoinNode.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/planner/CrossJoinNode.java
@@ -19,6 +19,7 @@ package org.apache.doris.planner;
import org.apache.doris.analysis.Analyzer;
import org.apache.doris.analysis.TableRef;
+import org.apache.doris.analysis.TupleId;
import org.apache.doris.common.UserException;
import org.apache.doris.statistics.StatisticalType;
import org.apache.doris.statistics.StatsRecursiveDerive;
@@ -30,6 +31,8 @@ import com.google.common.base.MoreObjects;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
+import java.util.List;
+
/**
* Cross join between left child and right child.
*/
@@ -57,6 +60,22 @@ public class CrossJoinNode extends PlanNode {
nullableTupleIds.addAll(inner.getNullableTupleIds());
}
+ /**
+ * Only for Nereids.
+ */
+ public CrossJoinNode(PlanNodeId id, PlanNode outer, PlanNode inner,
List<TupleId> tupleIds) {
+ super(id, "CROSS JOIN", StatisticalType.CROSS_JOIN_NODE);
+ this.innerRef = null;
+ this.tupleIds.addAll(tupleIds);
+ children.add(outer);
+ children.add(inner);
+
+ // Inherits all the nullable tuple from the children
+ // Mark tuples that form the "nullable" side of the outer join as
nullable.
+ nullableTupleIds.addAll(outer.getNullableTupleIds());
+ nullableTupleIds.addAll(inner.getNullableTupleIds());
+ }
+
public TableRef getInnerRef() {
return innerRef;
}
diff --git a/regression-test/data/nereids_syntax_p0/sub_query_correlated.out
b/regression-test/data/nereids_syntax_p0/sub_query_correlated.out
index 9f77c5c293..fb748d2daa 100644
--- a/regression-test/data/nereids_syntax_p0/sub_query_correlated.out
+++ b/regression-test/data/nereids_syntax_p0/sub_query_correlated.out
@@ -28,28 +28,40 @@
22 3
24 4
--- !not_in_uncorr --
+-- !in_subquery_corr --
+3 3
+
+-- !not_exist_corr --
+2 5
+
+-- !exist_corr --
1 3
1 2
+2 4
+3 4
+3 3
20 2
22 3
24 4
--- !in_subquery_corr --
-3 3
+-- !in_with_in_and_scalar --
--- !in_subquery_uncorr --
+-- !exist_and_not_exist --
+
+-- !scalar_unCorrelated --
+1 3
+1 2
2 5
2 4
3 4
3 3
--- !not_exist_corr --
-2 5
+-- !scalar_equal_to_uncorr --
--- !exist_corr --
+-- !not_scalar_unCorrelated --
1 3
1 2
+2 5
2 4
3 4
3 3
@@ -57,9 +69,16 @@
22 3
24 4
--- !in_with_in_and_scalar --
-
--- !exist_and_not_exist --
+-- !scalar_not_equal_uncorr --
+1 3
+1 2
+2 5
+2 4
+3 4
+3 3
+20 2
+22 3
+24 4
-- !in_unCorrelated --
2 5
@@ -67,6 +86,12 @@
3 4
3 3
+-- !in_subquery_uncorr --
+2 5
+2 4
+3 4
+3 3
+
-- !not_in_unCorrelated --
1 3
1 2
@@ -74,6 +99,57 @@
22 3
24 4
+-- !not_in_uncorr --
+1 3
+1 2
+20 2
+22 3
+24 4
+
+-- !exist_unCorrelated --
+1 3
+1 2
+2 5
+2 4
+3 4
+3 3
+20 2
+22 3
+24 4
+
+-- !exist_uncorr --
+1 3
+1 2
+2 5
+2 4
+3 4
+3 3
+20 2
+22 3
+24 4
+
+-- !not_exists_unCorrelated --
+1 3
+1 2
+2 5
+2 4
+3 4
+3 3
+20 2
+22 3
+24 4
+
+-- !not_exist_uncorr --
+1 3
+1 2
+2 5
+2 4
+3 4
+3 3
+20 2
+22 3
+24 4
+
-- !alias_scalar --
1 3
1 2
@@ -104,3 +180,14 @@
-- !alias_not_exist --
2 5
+-- !scalar_subquery --
+
+-- !in_subquery --
+1 abc 2 3 4
+1 abcd 3 3 4
+
+-- !exist_subquery --
+2 uvw 3 4 2
+2 uvw 3 4 2
+2 xyz 2 4 2
+
diff --git
a/regression-test/suites/nereids_syntax_p0/sub_query_correlated.groovy
b/regression-test/suites/nereids_syntax_p0/sub_query_correlated.groovy
index 2ceff3492e..5818ffa874 100644
--- a/regression-test/suites/nereids_syntax_p0/sub_query_correlated.groovy
+++ b/regression-test/suites/nereids_syntax_p0/sub_query_correlated.groovy
@@ -89,11 +89,7 @@ suite ("sub_query_correlated") {
insert into subquery4 values (5,4), (5,2), (8,3), (5,4), (6,7), (8,9)
"""
- // The query result is not necessarily correct, because there are some
problems in the current new optimizer,
- // just verify that the subquery de-nesting function can support the
following scenarios,
- // and the out file will be updated later
-
- // unstable
+ //------------------Correlated-----------------
qt_scalar_less_than_corr """
select * from subquery1 where subquery1.k1 < (select sum(subquery3.k3)
from subquery3 where subquery3.v2 = subquery1.k2) order by k1
"""
@@ -101,54 +97,26 @@ suite ("sub_query_correlated") {
qt_scalar_not_equal_corr """
select * from subquery1 where subquery1.k1 != (select
sum(subquery3.k3) from subquery3 where subquery3.v2 = subquery1.k2) order by k1
"""
-
- //qt_scalar_not_equal_uncorr """
- /*sql """
- select * from subquery1 where subquery1.k1 != (select
sum(subquery3.k3) from subquery3) order by k1
- """*/
qt_scalar_equal_to_corr """
select * from subquery1 where subquery1.k1 = (select sum(subquery3.k3)
from subquery3 where subquery3.v2 = subquery1.k2) order by k1
"""
-
- //qt_scalar_equal_to_uncorr """
- /*sql """
- select * from subquery1 where subquery1.k1 = (select sum(subquery3.k3)
from subquery3) order by k1
- """*/
qt_not_in_corr """
select * from subquery1 where subquery1.k1 not in (select subquery3.k3
from subquery3 where subquery3.v2 = subquery1.k2) order by k1
"""
-
- qt_not_in_uncorr """
- select * from subquery1 where subquery1.k1 not in (select subquery3.k3
from subquery3) order by k1
- """
qt_in_subquery_corr """
select * from subquery1 where subquery1.k1 in (select subquery3.k3
from subquery3 where subquery3.v2 = subquery1.k2) order by k1
"""
-
- qt_in_subquery_uncorr """
- select * from subquery1 where subquery1.k1 in (select subquery3.k3
from subquery3) order by k1
- """
qt_not_exist_corr """
select * from subquery1 where not exists (select subquery3.k3 from
subquery3 where subquery1.k2 = subquery3.v2) order by k1
"""
- //qt_not_exist_uncorr """
- /*sql """
- select * from subquery1 where not exists (select subquery3.k3 from
subquery3) order by k1
- """*/
-
qt_exist_corr """
select * from subquery1 where exists (select subquery3.k3 from
subquery3 where subquery1.k2 = subquery3.v2) order by k1
"""
-
- //qt_exist_uncorr """
- /*sql """
- select * from subquery1 where exists (select subquery3.k3 from
subquery3) order by k1
- """*/
qt_in_with_in_and_scalar """
select * from subquery1 where subquery1.k1 in (
@@ -163,33 +131,53 @@ suite ("sub_query_correlated") {
"""
//------------------unCorrelated-----------------
- //qt_scalar_unCorrelated
- /*sql """
+ qt_scalar_unCorrelated """
select * from subquery1 where subquery1.k1 < (select sum(subquery3.k3)
from subquery3 where subquery3.v2 = 2) order by k1
- """*/
+ """
- //qt_not_scalar_unCorrelated
- /*sql """
+ qt_scalar_equal_to_uncorr """
+ select * from subquery1 where subquery1.k1 = (select sum(subquery3.k3)
from subquery3) order by k1
+ """
+
+ qt_not_scalar_unCorrelated """
select * from subquery1 where subquery1.k1 != (select
sum(subquery3.k3) from subquery3 where subquery3.v2 = 2) order by k1
- """*/
+ """
+
+ qt_scalar_not_equal_uncorr """
+ select * from subquery1 where subquery1.k1 != (select
sum(subquery3.k3) from subquery3) order by k1
+ """
qt_in_unCorrelated """
select * from subquery1 where subquery1.k1 in (select subquery3.k3
from subquery3 where subquery3.v2 = 2) order by k1
"""
+ qt_in_subquery_uncorr """
+ select * from subquery1 where subquery1.k1 in (select subquery3.k3
from subquery3) order by k1
+ """
+
qt_not_in_unCorrelated """
select * from subquery1 where subquery1.k1 not in (select subquery3.k3
from subquery3 where subquery3.v2 = 2) order by k1
"""
- //qt_exist_unCorrelated
- /*sql """
+ qt_not_in_uncorr """
+ select * from subquery1 where subquery1.k1 not in (select subquery3.k3
from subquery3) order by k1
+ """
+
+ qt_exist_unCorrelated """
select * from subquery1 where exists (select subquery3.k3 from
subquery3 where subquery3.v2 = 2) order by k1
- """*/
+ """
+
+ qt_exist_uncorr """
+ select * from subquery1 where exists (select subquery3.k3 from
subquery3) order by k1
+ """
- //qt_not_exists_unCorrelated
- /*sql """
+ qt_not_exists_unCorrelated """
select * from subquery1 where not exists (select subquery3.k3 from
subquery3 where subquery3.v2 = 2) order by k1
- """*/
+ """
+
+ qt_not_exist_uncorr """
+ select * from subquery1 where not exists (select subquery3.k3 from
subquery3) order by k1
+ """
//----------with subquery alias----------
qt_alias_scalar """
@@ -221,4 +209,23 @@ suite ("sub_query_correlated") {
where not exists (select aa from
(select k1 as aa from subquery3 where subquery1.k2 =
subquery3.v2) subquery3) order by k1
"""
+
+ //----------complex subqueries----------
+ qt_scalar_subquery """
+ select * from subquery1
+ where k1 = (select sum(k1) from subquery3 where subquery1.k1 =
subquery3.v1 and subquery3.v2 = 2)
+ order by k1
+ """
+
+ qt_in_subquery """
+ select * from subquery3
+ where (k1 = 1 or k1 = 2 or k1 = 3) and v1 in (select k1 from
subquery1 where subquery1.k2 = subquery3.v2 and subquery1.k1 = 3)
+ order by k1
+ """
+
+ qt_exist_subquery """
+ select * from subquery3
+ where k1 = 2 and exists (select * from subquery1 where
subquery1.k1 = subquery3.v2 and subquery1.k2 = 4)
+ order by k1;
+ """
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
