This is an automated email from the ASF dual-hosted git repository.
starocean999 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 90791f0b19 [fix](nereids)fix bug of exists subquery with limit clause
(#24630)
90791f0b19 is described below
commit 90791f0b190bda23d972a13f93bc0d7ea0b8cd22
Author: starocean999 <[email protected]>
AuthorDate: Mon Sep 25 17:15:08 2023 +0800
[fix](nereids)fix bug of exists subquery with limit clause (#24630)
create table t1(c1 int, c2 int);
create table t2(c1 int, c2 int);
insert into t1 values (1,1);
insert into t2 values (1,1);
select * from t1 where exists (select * from t2 where t1.c1 = t2.c1 limit
0);
the result should be empty set.
---
.../nereids/rules/analysis/SubExprAnalyzer.java | 17 ++++++++-
.../nereids_syntax_p0/sub_query_correlated.out | 40 ++++++++++++++++++++++
.../nereids_syntax_p0/sub_query_correlated.groovy | 33 ++++++++++++++++++
3 files changed, 89 insertions(+), 1 deletion(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/SubExprAnalyzer.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/SubExprAnalyzer.java
index e39ebecaa0..d64a5516f5 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/SubExprAnalyzer.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/SubExprAnalyzer.java
@@ -29,6 +29,7 @@ import org.apache.doris.nereids.trees.expressions.Not;
import org.apache.doris.nereids.trees.expressions.ScalarSubquery;
import org.apache.doris.nereids.trees.expressions.Slot;
import org.apache.doris.nereids.trees.expressions.SubqueryExpr;
+import org.apache.doris.nereids.trees.expressions.literal.BooleanLiteral;
import
org.apache.doris.nereids.trees.expressions.visitor.DefaultExpressionRewriter;
import org.apache.doris.nereids.trees.plans.logical.LogicalAggregate;
import org.apache.doris.nereids.trees.plans.logical.LogicalLimit;
@@ -72,7 +73,13 @@ class SubExprAnalyzer extends
DefaultExpressionRewriter<CascadesContext> {
@Override
public Expression visitExistsSubquery(Exists exists, CascadesContext
context) {
AnalyzedResult analyzedResult = analyzeSubquery(exists);
-
+ if (analyzedResult.rootIsLimitZero()) {
+ return BooleanLiteral.of(exists.isNot());
+ }
+ if (analyzedResult.isCorrelated() &&
analyzedResult.rootIsLimitWithOffset()) {
+ throw new AnalysisException("Unsupported correlated subquery with
a LIMIT clause with offset > 0 "
+ + analyzedResult.getLogicalPlan());
+ }
return new Exists(analyzedResult.getLogicalPlan(),
analyzedResult.getCorrelatedSlots(), exists.isNot());
}
@@ -219,5 +226,13 @@ class SubExprAnalyzer extends
DefaultExpressionRewriter<CascadesContext> {
public boolean rootIsLimit() {
return logicalPlan instanceof LogicalLimit;
}
+
+ public boolean rootIsLimitWithOffset() {
+ return logicalPlan instanceof LogicalLimit && ((LogicalLimit<?>)
logicalPlan).getOffset() != 0;
+ }
+
+ public boolean rootIsLimitZero() {
+ return logicalPlan instanceof LogicalLimit && ((LogicalLimit<?>)
logicalPlan).getLimit() == 0;
+ }
}
}
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 162cdb3b95..732a72a390 100644
--- a/regression-test/data/nereids_syntax_p0/sub_query_correlated.out
+++ b/regression-test/data/nereids_syntax_p0/sub_query_correlated.out
@@ -161,6 +161,46 @@
22 3
24 4
+-- !exist_unCorrelated_limit1 --
+1 2
+1 3
+2 4
+2 5
+3 3
+3 4
+20 2
+22 3
+24 4
+
+-- !exist_corr_limit1 --
+1 2
+1 3
+2 4
+3 3
+3 4
+20 2
+22 3
+24 4
+
+-- !exist_unCorrelated_limit0 --
+
+-- !exist_corr_limit0 --
+
+-- !exist_unCorrelated_limit1_offset1 --
+1 2
+1 3
+2 4
+2 5
+3 3
+3 4
+20 2
+22 3
+24 4
+
+-- !exist_unCorrelated_limit0_offset1 --
+
+-- !exist_corr_limit0_offset1 --
+
-- !scalar_subquery1 --
-- !scalar_subquery2 --
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 c13cac2d86..482eab7a6a 100644
--- a/regression-test/suites/nereids_syntax_p0/sub_query_correlated.groovy
+++ b/regression-test/suites/nereids_syntax_p0/sub_query_correlated.groovy
@@ -240,6 +240,39 @@ suite ("sub_query_correlated") {
select * from sub_query_correlated_subquery1 where exists (select
sub_query_correlated_subquery3.k3 from sub_query_correlated_subquery3) order by
k1, k2
"""
+ qt_exist_unCorrelated_limit1 """
+ select * from sub_query_correlated_subquery1 where exists (select
sub_query_correlated_subquery3.k3 from sub_query_correlated_subquery3 where
sub_query_correlated_subquery3.v2 = 2 limit 1) order by k1, k2
+ """
+
+ qt_exist_corr_limit1 """
+ select * from sub_query_correlated_subquery1 where exists (select
sub_query_correlated_subquery3.k3 from sub_query_correlated_subquery3 where
sub_query_correlated_subquery1.k2 = sub_query_correlated_subquery3.v2 limit 1)
order by k1, k2
+ """
+
+ qt_exist_unCorrelated_limit0 """
+ select * from sub_query_correlated_subquery1 where exists (select
sub_query_correlated_subquery3.k3 from sub_query_correlated_subquery3 where
sub_query_correlated_subquery3.v2 = 2 limit 0) order by k1, k2
+ """
+
+ qt_exist_corr_limit0 """
+ select * from sub_query_correlated_subquery1 where exists (select
sub_query_correlated_subquery3.k3 from sub_query_correlated_subquery3 where
sub_query_correlated_subquery1.k2 = sub_query_correlated_subquery3.v2 limit 0)
order by k1, k2
+ """
+
+ qt_exist_unCorrelated_limit1_offset1 """
+ select * from sub_query_correlated_subquery1 where exists (select
sub_query_correlated_subquery3.k3 from sub_query_correlated_subquery3 where
sub_query_correlated_subquery3.v2 = 2 limit 1 offset 1) order by k1, k2
+ """
+
+ test {
+ sql("select * from sub_query_correlated_subquery1 where exists (select
sub_query_correlated_subquery3.k3 from sub_query_correlated_subquery3 where
sub_query_correlated_subquery1.k2 = sub_query_correlated_subquery3.v2 limit 1
offset 1) order by k1, k2")
+ exception "Unsupported correlated subquery with a LIMIT clause with
offset > 0"
+ }
+
+ qt_exist_unCorrelated_limit0_offset1 """
+ select * from sub_query_correlated_subquery1 where exists (select
sub_query_correlated_subquery3.k3 from sub_query_correlated_subquery3 where
sub_query_correlated_subquery3.v2 = 2 limit 0 offset 1) order by k1, k2
+ """
+
+ qt_exist_corr_limit0_offset1 """
+ select * from sub_query_correlated_subquery1 where exists (select
sub_query_correlated_subquery3.k3 from sub_query_correlated_subquery3 where
sub_query_correlated_subquery1.k2 = sub_query_correlated_subquery3.v2 limit 0
offset 1) order by k1, k2
+ """
+
//----------complex subqueries----------
qt_scalar_subquery1 """
select * from sub_query_correlated_subquery1
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
