This is an automated email from the ASF dual-hosted git repository. kxiao pushed a commit to branch branch-2.0 in repository https://gitbox.apache.org/repos/asf/doris.git
commit a62d45bbb7788e8af977aba30ecd995f9fe7afd2 Author: morrySnow <[email protected]> AuthorDate: Thu Jul 20 18:37:04 2023 +0800 [fix](Nereids) should not remove any limit from uncorrelated subquery (#21976) We should not remove any limit from uncorrelated subquery. For Example ```sql -- should return nothing, but return all tuple of t if we remove limit from exists SELECT * FROM t WHERE EXISTS (SELECT * FROM t limit 0); -- should return the tuple with smallest c1 in t, -- but report error if we remove limit from scalar subquery SELECT * FROM t WHERE c1 = (SELECT * FROM t ORDER BY c1 LIMIT 1); ``` --- .../rules/rewrite/EliminateLimitUnderApply.java | 8 +--- .../data/nereids_p0/subquery/test_subquery.out | 5 ++ .../nereids_p0/subquery/test_subquery.groovy | 55 +++++++++++++++------- 3 files changed, 45 insertions(+), 23 deletions(-) diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/EliminateLimitUnderApply.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/EliminateLimitUnderApply.java index 689ab065c7..bc36046cbe 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/EliminateLimitUnderApply.java +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/EliminateLimitUnderApply.java @@ -20,7 +20,6 @@ package org.apache.doris.nereids.rules.rewrite; import org.apache.doris.nereids.rules.Rule; import org.apache.doris.nereids.rules.RuleType; import org.apache.doris.nereids.trees.plans.Plan; -import org.apache.doris.nereids.trees.plans.logical.LogicalSort; import com.google.common.collect.ImmutableList; @@ -33,11 +32,8 @@ public class EliminateLimitUnderApply extends OneRewriteRuleFactory { @Override public Rule build() { return logicalApply(any(), logicalLimit()).then(apply -> { - if (!apply.isCorrelated() && apply.isIn() && (apply.right().child() instanceof LogicalSort - || (apply.right().child().children().size() > 0 - && apply.right().child().child(0) instanceof LogicalSort))) { - // must keep the limit if it's an uncorrelated in-subquery with limit on sort - // select a from t1 where a in ( select b from t2 order by xx limit yy ) + if (!apply.isCorrelated()) { + // must keep the limit if it's an uncorrelated because the return number of rows is affected by limit return null; } List<Plan> children = new ImmutableList.Builder<Plan>() diff --git a/regression-test/data/nereids_p0/subquery/test_subquery.out b/regression-test/data/nereids_p0/subquery/test_subquery.out index b9941ebf23..2e13e02930 100644 --- a/regression-test/data/nereids_p0/subquery/test_subquery.out +++ b/regression-test/data/nereids_p0/subquery/test_subquery.out @@ -15,3 +15,8 @@ -- !sql4 -- 1 +-- !uncorrelated_exists_with_limit_0 -- + +-- !uncorrelated_scalar_with_sort_and_limit -- +true 15 1992 3021 11011920 0.000 true 9999-12-12 2015-04-02T00:00 3.141592653 20.456 string12345 701411834604692317 + diff --git a/regression-test/suites/nereids_p0/subquery/test_subquery.groovy b/regression-test/suites/nereids_p0/subquery/test_subquery.groovy index ca4c3b621f..5e3e4bfb30 100644 --- a/regression-test/suites/nereids_p0/subquery/test_subquery.groovy +++ b/regression-test/suites/nereids_p0/subquery/test_subquery.groovy @@ -18,29 +18,50 @@ suite("test_subquery") { sql "SET enable_nereids_planner=true" sql "SET enable_fallback_to_original_planner=false" - qt_sql1 """ - select c1, c3, m2 from - (select c1, c3, max(c2) m2 from - (select c1, c2, c3 from - (select k3 c1, k2 c2, max(k1) c3 from nereids_test_query_db.test - group by 1, 2 order by 1 desc, 2 desc limit 5) x - ) x2 group by c1, c3 limit 10 - ) t - where c1>0 order by 2 , 1 limit 3 - """ - - qt_sql2 """ + qt_sql1 """ + select c1, c3, m2 from + (select c1, c3, max(c2) m2 from + (select c1, c2, c3 from + (select k3 c1, k2 c2, max(k1) c3 from nereids_test_query_db.test + group by 1, 2 order by 1 desc, 2 desc limit 5) x + ) x2 group by c1, c3 limit 10 + ) t + where c1>0 order by 2 , 1 limit 3 + """ + + qt_sql2 """ with base as (select k1, k2 from nereids_test_query_db.test as t where k1 in (select k1 from nereids_test_query_db.baseall where k7 = 'wangjuoo4' group by 1 having count(distinct k7) > 0)) select * from base limit 10; - """ + """ - qt_sql3 """ + qt_sql3 """ SELECT k1 FROM nereids_test_query_db.test GROUP BY k1 HAVING k1 IN (SELECT k1 FROM nereids_test_query_db.baseall WHERE k2 >= (SELECT min(k3) FROM nereids_test_query_db.bigtable WHERE k2 = baseall.k2)) order by k1; + """ + + qt_sql4 """ + select count() from (select k2, k1 from nereids_test_query_db.baseall order by k1 limit 1) a; + """ + + qt_uncorrelated_exists_with_limit_0 """ + select * from nereids_test_query_db.baseall where exists (select * from nereids_test_query_db.baseall limit 0) + """ + + // test uncorrelated scalar subquery with limit <= 1 + sql """ + select * from nereids_test_query_db.baseall where k1 = (select k1 from nereids_test_query_db.baseall limit 1) + """ + + // test uncorrelated scalar subquery with more than one return rows + test { + sql """ + select * from nereids_test_query_db.baseall where k1 = (select k1 from nereids_test_query_db.baseall limit 2) """ + exception("Expected LE 1 to be returned by expression") + } - qt_sql4 """ - select /*+SET_VAR(enable_projection=false) */ - count() from (select k2, k1 from nereids_test_query_db.baseall order by k1 limit 1) a; + // test uncorrelated scalar subquery with order by and limit + qt_uncorrelated_scalar_with_sort_and_limit """ + select * from nereids_test_query_db.baseall where k1 = (select k1 from nereids_test_query_db.baseall order by k1 desc limit 1) """ } --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
