This is an automated email from the ASF dual-hosted git repository. dataroaring pushed a commit to branch branch-4.0-preview in repository https://gitbox.apache.org/repos/asf/doris.git
commit dde19fe768812de0f84637998d3bfb100e9ca3f8 Author: 谢健 <[email protected]> AuthorDate: Thu Apr 18 15:47:29 2024 +0800 [fix](Nereids)fix unstable plan shape in limit_push_down case --- .../org/apache/doris/nereids/cost/CostModelV1.java | 2 +- .../limit_push_down/limit_push_down.out | 89 +++++++++------------- .../limit_push_down/order_push_down.out | 86 +++++++++++---------- .../limit_push_down/limit_push_down.groovy | 8 +- .../limit_push_down/order_push_down.groovy | 48 ++++++------ 5 files changed, 111 insertions(+), 122 deletions(-) diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/cost/CostModelV1.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/cost/CostModelV1.java index d56a9d8be2a..d469dd3b40b 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/nereids/cost/CostModelV1.java +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/cost/CostModelV1.java @@ -176,7 +176,7 @@ class CostModelV1 extends PlanVisitor<Cost, PlanContext> { double rowCount = statistics.getRowCount(); if (topN.getSortPhase().isGather()) { // Now we do more like two-phase sort, so penalise one-phase sort - rowCount *= 100; + rowCount = rowCount * 100 + 100; } return CostV1.of(context.getSessionVariable(), childRowCount, rowCount, childRowCount); } diff --git a/regression-test/data/nereids_rules_p0/limit_push_down/limit_push_down.out b/regression-test/data/nereids_rules_p0/limit_push_down/limit_push_down.out index cbf8a09c7e6..54b8a7a6b6c 100644 --- a/regression-test/data/nereids_rules_p0/limit_push_down/limit_push_down.out +++ b/regression-test/data/nereids_rules_p0/limit_push_down/limit_push_down.out @@ -274,60 +274,47 @@ PhysicalResultSink PhysicalResultSink --PhysicalLimit[GLOBAL] ----PhysicalLimit[LOCAL] -------hashAgg[GLOBAL] ---------hashAgg[LOCAL] -----------PhysicalUnion -------------PhysicalLimit[LOCAL] ---------------hashAgg[LOCAL] -----------------filter((t1.id > 100)) -------------------PhysicalOlapScan[t1] -------------PhysicalLimit[LOCAL] ---------------hashAgg[LOCAL] -----------------filter((t2.id > 100)) -------------------PhysicalOlapScan[t2] +------PhysicalUnion +--------PhysicalLimit[LOCAL] +----------filter((cast(msg as DOUBLE) > 100.0)) +------------PhysicalOlapScan[t1] +--------PhysicalLimit[LOCAL] +----------filter((t2.id > 100)) +------------PhysicalOlapScan[t2] -- !limit_union_join -- PhysicalResultSink --PhysicalLimit[GLOBAL] ----PhysicalLimit[LOCAL] -------hashAgg[GLOBAL] ---------hashAgg[LOCAL] -----------PhysicalUnion +------PhysicalUnion +--------PhysicalLimit[LOCAL] +----------hashJoin[RIGHT_OUTER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=() +------------PhysicalOlapScan[t1] ------------PhysicalLimit[LOCAL] ---------------hashAgg[GLOBAL] -----------------hashAgg[LOCAL] -------------------hashJoin[RIGHT_OUTER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=() ---------------------PhysicalOlapScan[t1] ---------------------PhysicalOlapScan[t2] +--------------PhysicalOlapScan[t2] +--------PhysicalLimit[LOCAL] +----------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t3.id = t4.id)) otherCondition=() ------------PhysicalLimit[LOCAL] ---------------hashAgg[LOCAL] -----------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t3.id = t4.id)) otherCondition=() -------------------PhysicalLimit[LOCAL] ---------------------hashAgg[LOCAL] -----------------------PhysicalOlapScan[t3] -------------------PhysicalOlapScan[t4] +--------------PhysicalOlapScan[t3] +------------PhysicalOlapScan[t4] -- !limit_union_window -- PhysicalResultSink --PhysicalLimit[GLOBAL] ----PhysicalLimit[LOCAL] -------hashAgg[GLOBAL] ---------hashAgg[LOCAL] -----------PhysicalUnion -------------PhysicalLimit[LOCAL] ---------------hashAgg[GLOBAL] -----------------hashAgg[LOCAL] -------------------PhysicalWindow ---------------------PhysicalQuickSort[MERGE_SORT] -----------------------PhysicalQuickSort[LOCAL_SORT] -------------------------PhysicalOlapScan[t1] -------------PhysicalLimit[LOCAL] ---------------hashAgg[GLOBAL] -----------------hashAgg[LOCAL] -------------------PhysicalWindow ---------------------PhysicalQuickSort[MERGE_SORT] -----------------------PhysicalQuickSort[LOCAL_SORT] -------------------------PhysicalOlapScan[t2] +------PhysicalUnion +--------PhysicalLimit[LOCAL] +----------PhysicalWindow +------------PhysicalQuickSort[MERGE_SORT] +--------------PhysicalQuickSort[LOCAL_SORT] +----------------PhysicalPartitionTopN +------------------PhysicalOlapScan[t1] +--------PhysicalLimit[LOCAL] +----------PhysicalWindow +------------PhysicalQuickSort[MERGE_SORT] +--------------PhysicalQuickSort[LOCAL_SORT] +----------------PhysicalPartitionTopN +------------------PhysicalOlapScan[t2] -- !limit_subquery_join_filter -- PhysicalResultSink @@ -354,17 +341,13 @@ PhysicalResultSink PhysicalResultSink --PhysicalLimit[GLOBAL] ----PhysicalLimit[LOCAL] -------hashAgg[GLOBAL] ---------hashAgg[LOCAL] -----------PhysicalUnion -------------PhysicalLimit[LOCAL] ---------------hashAgg[LOCAL] -----------------filter((t1.id > 100)) -------------------PhysicalOlapScan[t1] -------------PhysicalLimit[LOCAL] ---------------hashAgg[LOCAL] -----------------filter((t2.id > 100)) -------------------PhysicalOlapScan[t2] +------PhysicalUnion +--------PhysicalLimit[LOCAL] +----------filter((t1.id > 100)) +------------PhysicalOlapScan[t1] +--------PhysicalLimit[LOCAL] +----------filter((t2.id > 100)) +------------PhysicalOlapScan[t2] -- !limit_subquery_union_join -- PhysicalResultSink diff --git a/regression-test/data/nereids_rules_p0/limit_push_down/order_push_down.out b/regression-test/data/nereids_rules_p0/limit_push_down/order_push_down.out index d1ab585baa2..d1684890a5d 100644 --- a/regression-test/data/nereids_rules_p0/limit_push_down/order_push_down.out +++ b/regression-test/data/nereids_rules_p0/limit_push_down/order_push_down.out @@ -228,35 +228,31 @@ PhysicalResultSink PhysicalResultSink --PhysicalTopN[MERGE_SORT] ----PhysicalTopN[LOCAL_SORT] -------hashAgg[GLOBAL] ---------hashAgg[LOCAL] -----------PhysicalUnion +------PhysicalUnion +--------PhysicalTopN[MERGE_SORT] +----------PhysicalTopN[LOCAL_SORT] ------------PhysicalOlapScan[t1] +--------PhysicalTopN[MERGE_SORT] +----------PhysicalTopN[LOCAL_SORT] ------------PhysicalOlapScan[t2] -- !limit_outside_order_inside_set_operation -- PhysicalResultSink --PhysicalLimit[GLOBAL] ----PhysicalLimit[LOCAL] -------hashAgg[GLOBAL] ---------hashAgg[LOCAL] -----------PhysicalUnion -------------PhysicalLimit[LOCAL] ---------------hashAgg[LOCAL] -----------------PhysicalOlapScan[t1] -------------PhysicalLimit[LOCAL] ---------------hashAgg[LOCAL] -----------------PhysicalOlapScan[t2] +------PhysicalUnion +--------PhysicalLimit[LOCAL] +----------PhysicalOlapScan[t1] +--------PhysicalLimit[LOCAL] +----------PhysicalOlapScan[t2] -- !limit_inside_set_operation -- PhysicalResultSink ---hashAgg[GLOBAL] -----hashAgg[LOCAL] -------PhysicalUnion ---------PhysicalOlapScan[t1] ---------PhysicalTopN[MERGE_SORT] -----------PhysicalTopN[LOCAL_SORT] -------------PhysicalOlapScan[t2] +--PhysicalUnion +----PhysicalOlapScan[t1] +----PhysicalTopN[MERGE_SORT] +------PhysicalTopN[LOCAL_SORT] +--------PhysicalOlapScan[t2] -- !limit_offset_set_operation -- PhysicalResultSink @@ -355,11 +351,13 @@ PhysicalResultSink PhysicalResultSink --PhysicalTopN[MERGE_SORT] ----PhysicalTopN[LOCAL_SORT] -------hashAgg[GLOBAL] ---------hashAgg[LOCAL] -----------PhysicalUnion +------PhysicalUnion +--------PhysicalTopN[MERGE_SORT] +----------PhysicalTopN[LOCAL_SORT] ------------filter((t1.id > 100)) --------------PhysicalOlapScan[t1] +--------PhysicalTopN[MERGE_SORT] +----------PhysicalTopN[LOCAL_SORT] ------------filter((t2.id > 100)) --------------PhysicalOlapScan[t2] @@ -367,27 +365,33 @@ PhysicalResultSink PhysicalResultSink --PhysicalTopN[MERGE_SORT] ----PhysicalTopN[LOCAL_SORT] -------hashAgg[GLOBAL] ---------hashAgg[LOCAL] -----------PhysicalUnion +------PhysicalUnion +--------PhysicalTopN[MERGE_SORT] +----------PhysicalTopN[LOCAL_SORT] ------------hashJoin[RIGHT_OUTER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=() --------------PhysicalOlapScan[t1] --------------PhysicalOlapScan[t2] +--------PhysicalTopN[MERGE_SORT] +----------PhysicalTopN[LOCAL_SORT] ------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t3.id = t4.id)) otherCondition=() ---------------PhysicalOlapScan[t3] +--------------PhysicalTopN[MERGE_SORT] +----------------PhysicalTopN[LOCAL_SORT] +------------------PhysicalOlapScan[t3] --------------PhysicalOlapScan[t4] -- !limit_union_window -- PhysicalResultSink --PhysicalTopN[MERGE_SORT] ----PhysicalTopN[LOCAL_SORT] -------hashAgg[GLOBAL] ---------hashAgg[LOCAL] -----------PhysicalUnion +------PhysicalUnion +--------PhysicalTopN[MERGE_SORT] +----------PhysicalTopN[LOCAL_SORT] ------------PhysicalWindow --------------PhysicalQuickSort[MERGE_SORT] ----------------PhysicalQuickSort[LOCAL_SORT] ------------------PhysicalOlapScan[t1] +--------PhysicalTopN[MERGE_SORT] +----------PhysicalTopN[LOCAL_SORT] ------------PhysicalWindow --------------PhysicalQuickSort[MERGE_SORT] ----------------PhysicalQuickSort[LOCAL_SORT] @@ -417,11 +421,13 @@ PhysicalResultSink PhysicalResultSink --PhysicalTopN[MERGE_SORT] ----PhysicalTopN[LOCAL_SORT] -------hashAgg[GLOBAL] ---------hashAgg[LOCAL] -----------PhysicalUnion +------PhysicalUnion +--------PhysicalTopN[MERGE_SORT] +----------PhysicalTopN[LOCAL_SORT] ------------filter((t1.id > 100)) --------------PhysicalOlapScan[t1] +--------PhysicalTopN[MERGE_SORT] +----------PhysicalTopN[LOCAL_SORT] ------------filter((t2.id > 100)) --------------PhysicalOlapScan[t2] @@ -429,12 +435,14 @@ PhysicalResultSink PhysicalResultSink --PhysicalTopN[MERGE_SORT] ----PhysicalTopN[LOCAL_SORT] -------hashAgg[GLOBAL] ---------hashAgg[LOCAL] -----------PhysicalUnion +------PhysicalUnion +--------PhysicalTopN[MERGE_SORT] +----------PhysicalTopN[LOCAL_SORT] ------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=() --------------PhysicalOlapScan[t1] --------------PhysicalOlapScan[t2] +--------PhysicalTopN[MERGE_SORT] +----------PhysicalTopN[LOCAL_SORT] ------------hashJoin[INNER_JOIN] hashCondition=((t3.id = t4.id)) otherCondition=() --------------PhysicalOlapScan[t3] --------------PhysicalOlapScan[t4] @@ -445,11 +453,9 @@ PhysicalResultSink ----PhysicalTopN[LOCAL_SORT] ------PhysicalWindow --------PhysicalQuickSort[LOCAL_SORT] -----------hashAgg[GLOBAL] -------------hashAgg[LOCAL] ---------------PhysicalUnion -----------------PhysicalOlapScan[t1] -----------------PhysicalOlapScan[t2] +----------PhysicalUnion +------------PhysicalOlapScan[t1] +------------PhysicalOlapScan[t2] -- !limit_correlated_subquery -- PhysicalResultSink diff --git a/regression-test/suites/nereids_rules_p0/limit_push_down/limit_push_down.groovy b/regression-test/suites/nereids_rules_p0/limit_push_down/limit_push_down.groovy index 2f1ff4b4ee6..0c4654cf72a 100644 --- a/regression-test/suites/nereids_rules_p0/limit_push_down/limit_push_down.groovy +++ b/regression-test/suites/nereids_rules_p0/limit_push_down/limit_push_down.groovy @@ -91,11 +91,11 @@ suite("limit_push_down") { // `limit 1, nested subquery`: qt_limit_nested_subquery """explain shape plan SELECT * FROM (SELECT * FROM (SELECT t1.id FROM t1) AS subq1) AS subq2 LIMIT 1;""" // `limit 1, union, filter`: - qt_limit_union_filter """explain shape plan SELECT * FROM (SELECT t1.id FROM t1 WHERE t1.id > 100 UNION SELECT t2.id FROM t2 WHERE t2.id > 100) u LIMIT 1;""" + qt_limit_union_filter """explain shape plan SELECT * FROM (SELECT t1.msg FROM t1 WHERE t1.msg > 100 UNION ALL SELECT t2.id FROM t2 WHERE t2.id > 100) u LIMIT 1;""" // `limit 1, union, join`: - qt_limit_union_join """explain shape plan SELECT * FROM (SELECT t1.id FROM t1 RIGHT OUTER JOIN t2 ON t1.id = t2.id UNION SELECT t3.id FROM t3 LEFT OUTER JOIN t4 ON t3.id = t4.id) u LIMIT 1;""" + qt_limit_union_join """explain shape plan SELECT * FROM (SELECT t1.msg FROM t1 RIGHT OUTER JOIN t2 ON t1.id = t2.id UNION ALL SELECT t3.msg FROM t3 LEFT OUTER JOIN t4 ON t3.id = t4.id) u LIMIT 1;""" // `limit 1, union, window`: - qt_limit_union_window """explain shape plan SELECT * FROM (SELECT id, msg, ROW_NUMBER() OVER (ORDER BY id) AS row_num FROM t1 UNION SELECT id, msg, ROW_NUMBER() OVER (ORDER BY id) AS row_num FROM t2) u LIMIT 1;""" + qt_limit_union_window """explain shape plan SELECT * FROM (SELECT msg, ROW_NUMBER() OVER (ORDER BY id) AS row_num FROM t1 UNION ALL SELECT msg, ROW_NUMBER() OVER (ORDER BY id) AS row_num FROM t2) u LIMIT 1;""" // `limit 1, subquery, join, filter`: qt_limit_subquery_join_filter """explain shape plan SELECT * FROM (SELECT t1.id FROM t1 JOIN t2 ON t1.id = t2.id WHERE t1.id > 100) AS subq LIMIT 1;""" @@ -103,7 +103,7 @@ suite("limit_push_down") { qt_limit_subquery_join_window """explain shape plan SELECT id, msg, ROW_NUMBER() OVER (PARTITION BY subq.id ORDER BY subq.id) AS row_num FROM (SELECT t1.id, t1.msg FROM t1 left outer JOIN t2 ON t1.id = t2.id) AS subq LIMIT 1;""" // `limit 1, subquery, union, filter`: - qt_limit_subquery_union_filter """explain shape plan SELECT * FROM (SELECT t1.id FROM t1 WHERE t1.id > 100 UNION SELECT t2.id FROM t2 WHERE t2.id > 100) AS subq LIMIT 1;""" + qt_limit_subquery_union_filter """explain shape plan SELECT * FROM (SELECT t1.id FROM t1 WHERE t1.id > 100 UNION ALL SELECT t2.id FROM t2 WHERE t2.id > 100) AS subq LIMIT 1;""" // `limit 1, subquery, union, join`: qt_limit_subquery_union_join """explain shape plan SELECT * FROM (SELECT t1.id FROM t1 JOIN t2 ON t1.id = t2.id UNION SELECT t3.id FROM t3 JOIN t4 ON t3.id = t4.id) AS subq LIMIT 1;""" diff --git a/regression-test/suites/nereids_rules_p0/limit_push_down/order_push_down.groovy b/regression-test/suites/nereids_rules_p0/limit_push_down/order_push_down.groovy index e97856fbe43..d1282c96e7b 100644 --- a/regression-test/suites/nereids_rules_p0/limit_push_down/order_push_down.groovy +++ b/regression-test/suites/nereids_rules_p0/limit_push_down/order_push_down.groovy @@ -29,24 +29,24 @@ suite("order_push_down") { //`limit 1 offset 1 + sort, project`: qt_limit_offset_sort_project """ explain shape plan SELECT t1.id FROM t1 ORDER BY id LIMIT 1 OFFSET 1; """ //`limit 1 + sort, join`: - qt_limit_sort_join """ explain shape plan SELECT t1.id FROM t1 JOIN t2 ON t1.id = t2.id ORDER BY t1.id LIMIT 1; """ + qt_limit_sort_join """ explain shape plan SELECT t1.id FROM t1 JOIN t2 ON t1.id = t2.id order by t1.msg LIMIT 1; """ //`limit 1 + sort, semi join`: - qt_limit_sort_semi_join """ explain shape plan SELECT t1.id FROM t1 LEFT SEMI JOIN t2 ON t1.id = t2.id ORDER BY t1.id LIMIT 1; """ + qt_limit_sort_semi_join """ explain shape plan SELECT t1.id FROM t1 LEFT SEMI JOIN t2 ON t1.id = t2.id order by t1.msg LIMIT 1; """ // Right Semi Join with Order By qt_right_semi_join_order """ explain shape plan SELECT t2.id FROM t1 RIGHT SEMI JOIN t2 ON t1.id = t2.id ORDER BY t2.id LIMIT 1; """ // Left Anti Join with Order By - qt_left_anti_join_order """ explain shape plan SELECT t1.id FROM t1 LEFT ANTI JOIN t2 ON t1.id = t2.id ORDER BY t1.id LIMIT 1; """ + qt_left_anti_join_order """ explain shape plan SELECT t1.id FROM t1 LEFT ANTI JOIN t2 ON t1.id = t2.id order by t1.msg LIMIT 1; """ // Right Anti Join with Order By qt_right_anti_join_order """ explain shape plan SELECT t2.id FROM t1 RIGHT ANTI JOIN t2 ON t1.id = t2.id ORDER BY t2.id LIMIT 1; """ // Full Outer Join with Order By - qt_full_outer_join_order """ explain shape plan SELECT t1.id FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.id ORDER BY t1.id LIMIT 1; """ + qt_full_outer_join_order """ explain shape plan SELECT t1.id FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.id order by t1.msg LIMIT 1; """ // Left Outer Join with Order By - qt_left_outer_join_order """ explain shape plan SELECT t1.id FROM t1 LEFT OUTER JOIN t2 ON t1.id = t2.id ORDER BY t1.id LIMIT 1; """ + qt_left_outer_join_order """ explain shape plan SELECT t1.id FROM t1 LEFT OUTER JOIN t2 ON t1.id = t2.id order by t1.msg LIMIT 1; """ // Right Outer Join with Order By qt_right_outer_join_order """ explain shape plan SELECT t2.id FROM t1 RIGHT OUTER JOIN t2 ON t1.id = t2.id ORDER BY t2.id LIMIT 1; """ // Cross Join with Order By - qt_cross_join_order """ explain shape plan SELECT t1.id FROM t1 CROSS JOIN t2 ORDER BY t1.id LIMIT 1; """ - qt_limit_offset_sort_join """ explain shape plan SELECT t1.id FROM t1 JOIN t2 ON t1.id = t2.id ORDER BY t1.id LIMIT 1 OFFSET 1; """ + qt_cross_join_order """ explain shape plan SELECT t1.id FROM t1 CROSS JOIN t2 order by t1.msg LIMIT 1; """ + qt_limit_offset_sort_join """ explain shape plan SELECT t1.id FROM t1 JOIN t2 ON t1.id = t2.id order by t1.msg LIMIT 1 OFFSET 1; """ // `limit 1 + sort, agg & scalar agg and having`: qt_limit_sort_agg_having """ explain shape plan SELECT distinct id c FROM t1 ORDER BY c LIMIT 1; """ //`limit 1 offset 1, agg & scalar agg and having`: @@ -78,11 +78,11 @@ suite("order_push_down") { // `limit 1, subquery with order by`: qt_limit_subquery_all_inside """explain shape plan SELECT * FROM (SELECT t1.id FROM t1 order by id LIMIT 1) AS subq ;""" // `LIMIT` with Set Operation and `ORDER BY`: - qt_limit_set_operation """explain shape plan SELECT * FROM (SELECT t1.id FROM t1 UNION SELECT t2.id FROM t2) u ORDER BY id LIMIT 1;""" + qt_limit_set_operation """explain shape plan SELECT * FROM (SELECT t1.id FROM t1 UNION ALL SELECT t2.id FROM t2) u ORDER BY id LIMIT 1;""" // `LIMIT` with Set Operation and `ORDER BY`: - qt_limit_outside_order_inside_set_operation """explain shape plan SELECT * FROM (SELECT t1.id FROM t1 UNION SELECT t2.id FROM t2 ORDER BY id) u LIMIT 1;""" + qt_limit_outside_order_inside_set_operation """explain shape plan SELECT * FROM (SELECT t1.id FROM t1 UNION ALL SELECT t2.id FROM t2 ORDER BY id) u LIMIT 1;""" // `LIMIT` with Set Operation and `ORDER BY`: - qt_limit_inside_set_operation """explain shape plan SELECT * FROM (SELECT t1.id FROM t1 UNION SELECT t2.id FROM t2 ORDER BY id LIMIT 1) u;""" + qt_limit_inside_set_operation """explain shape plan SELECT * FROM (SELECT t1.id FROM t1 UNION ALL SELECT t2.id FROM t2 ORDER BY id LIMIT 1) u;""" // `LIMIT` with Set Operation and `OFFSET` with `ORDER BY`: qt_limit_offset_set_operation """explain shape plan SELECT * FROM (SELECT t1.id FROM t1 INTERSECT SELECT t2.id FROM t2) u ORDER BY id LIMIT 1 OFFSET 1;""" @@ -100,10 +100,10 @@ suite("order_push_down") { qt_limit_offset_filter """explain shape plan SELECT t1.id FROM t1 WHERE id = 1 ORDER BY id LIMIT 1 OFFSET 1;""" // `LIMIT` with Projection, Filter, and `ORDER BY`: - qt_limit_project_filter """explain shape plan SELECT t1.id AS c FROM t1 WHERE t1.id > 100 ORDER BY t1.id LIMIT 1;""" + qt_limit_project_filter """explain shape plan SELECT t1.id AS c FROM t1 WHERE t1.id > 100 order by t1.msg LIMIT 1;""" // `LIMIT` with Join, Filter, and `ORDER BY`: - qt_limit_join_filter """explain shape plan SELECT t1.id FROM t1 JOIN t2 ON t1.id = t2.id WHERE t1.id > 100 ORDER BY t1.id LIMIT 1;""" + qt_limit_join_filter """explain shape plan SELECT t1.id FROM t1 JOIN t2 ON t1.id = t2.id WHERE t1.id > 100 order by t1.msg LIMIT 1;""" // `LIMIT` with Subquery and `ORDER BY`: qt_limit_subquery """explain shape plan SELECT * FROM (SELECT t1.id FROM t1) AS subq ORDER BY id LIMIT 1;""" @@ -121,13 +121,13 @@ suite("order_push_down") { qt_limit_nested_subquery """explain shape plan SELECT * FROM (SELECT * FROM (SELECT t1.id FROM t1) AS subq1) AS subq2 ORDER BY id LIMIT 1;""" // `LIMIT` with Union, Filter, and `ORDER BY`: - qt_limit_union_filter """explain shape plan SELECT * FROM (SELECT t1.id FROM t1 WHERE t1.id > 100 UNION SELECT t2.id FROM t2 WHERE t2.id > 100) u ORDER BY id LIMIT 1;""" + qt_limit_union_filter """explain shape plan SELECT * FROM (SELECT t1.id FROM t1 WHERE t1.id > 100 UNION ALL SELECT t2.id FROM t2 WHERE t2.id > 100) u ORDER BY id LIMIT 1;""" // `LIMIT` with Union, Join, and `ORDER BY`: - qt_limit_union_join """explain shape plan SELECT * FROM (SELECT t1.id FROM t1 RIGHT OUTER JOIN t2 ON t1.id = t2.id UNION SELECT t3.id FROM t3 LEFT OUTER JOIN t4 ON t3.id = t4.id) u ORDER BY id LIMIT 1;""" + qt_limit_union_join """explain shape plan SELECT * FROM (SELECT t1.id FROM t1 RIGHT OUTER JOIN t2 ON t1.id = t2.id UNION ALL SELECT t3.id FROM t3 LEFT OUTER JOIN t4 ON t3.id = t4.id) u ORDER BY id LIMIT 1;""" // `LIMIT` with Union, Window function, and `ORDER BY`: - qt_limit_union_window """explain shape plan SELECT * FROM (SELECT id, msg, ROW_NUMBER() OVER (ORDER BY id) AS row_num FROM t1 UNION SELECT id, msg, ROW_NUMBER() OVER (ORDER BY id) AS row_num FROM t2) u ORDER BY id LIMIT 1;""" + qt_limit_union_window """explain shape plan SELECT * FROM (SELECT id, msg, ROW_NUMBER() OVER (ORDER BY id) AS row_num FROM t1 UNION ALL SELECT id, msg, ROW_NUMBER() OVER (ORDER BY id) AS row_num FROM t2) u ORDER BY id LIMIT 1;""" // `LIMIT` with Subquery, Join, Filter, and `ORDER BY`: qt_limit_subquery_join_filter """explain shape plan SELECT * FROM (SELECT t1.id FROM t1 JOIN t2 ON t1.id = t2.id WHERE t1.id > 100) AS subq ORDER BY id LIMIT 1;""" @@ -136,13 +136,13 @@ suite("order_push_down") { qt_limit_subqueryjoin_window """explain shape plan SELECT id, msg, ROW_NUMBER() OVER (PARTITION BY subq.id ORDER BY subq.id) AS row_num FROM (SELECT t1.id, t1.msg FROM t1 LEFT OUTER JOIN t2 ON t1.id = t2.id) AS subq ORDER BY id LIMIT 1;""" // `LIMIT` with Subquery, Union, Filter, and `ORDER BY`: - qt_limit_subquery_union_filter """explain shape plan SELECT * FROM (SELECT t1.id FROM t1 WHERE t1.id > 100 UNION SELECT t2.id FROM t2 WHERE t2.id > 100) AS subq ORDER BY id LIMIT 1;""" + qt_limit_subquery_union_filter """explain shape plan SELECT * FROM (SELECT t1.id FROM t1 WHERE t1.id > 100 UNION ALL SELECT t2.id FROM t2 WHERE t2.id > 100) AS subq ORDER BY id LIMIT 1;""" // `LIMIT` with Subquery, Union, Join, and `ORDER BY`: - qt_limit_subquery_union_join """explain shape plan SELECT * FROM (SELECT t1.id FROM t1 JOIN t2 ON t1.id = t2.id UNION SELECT t3.id FROM t3 JOIN t4 ON t3.id = t4.id) AS subq ORDER BY id LIMIT 1;""" + qt_limit_subquery_union_join """explain shape plan SELECT * FROM (SELECT t1.id FROM t1 JOIN t2 ON t1.id = t2.id UNION ALL SELECT t3.id FROM t3 JOIN t4 ON t3.id = t4.id) AS subq ORDER BY id LIMIT 1;""" // `LIMIT` with Subquery, Union, Window function, and `ORDER BY`: - qt_limit_subquery_union_window """explain shape plan SELECT id, msg, ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS row_num FROM (SELECT id, msg FROM t1 UNION SELECT id, msg FROM t2) AS subq ORDER BY id LIMIT 1;""" + qt_limit_subquery_union_window """explain shape plan SELECT id, msg, ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS row_num FROM (SELECT id, msg FROM t1 UNION ALL SELECT id, msg FROM t2) AS subq ORDER BY id LIMIT 1;""" // `LIMIT` with Correlated Subquery and `ORDER BY`: qt_limit_correlated_subquery """explain shape plan SELECT t1.id FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.id = t1.id ORDER BY id LIMIT 1);""" @@ -169,10 +169,10 @@ suite("order_push_down") { qt_limit_cte_query_window """explain shape plan WITH cte AS (SELECT id FROM t1 WHERE id < 10) SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS row_num FROM cte ORDER BY id LIMIT 1;""" // `limit 1, project, filter`: - qt_limit_project_filter """explain shape plan SELECT t1.id AS c FROM t1 WHERE t1.id > 100 ORDER BY t1.id LIMIT 1;""" + qt_limit_project_filter """explain shape plan SELECT t1.id AS c FROM t1 WHERE t1.id > 100 order by t1.msg LIMIT 1;""" // `limit 1, join, filter`: - qt_limit_join_filter """explain shape plan SELECT t1.id FROM t1 JOIN t2 ON t1.id = t2.id WHERE t1.id > 100 ORDER BY t1.id LIMIT 1;""" + qt_limit_join_filter """explain shape plan SELECT t1.id FROM t1 JOIN t2 ON t1.id = t2.id WHERE t1.id > 100 order by t1.msg LIMIT 1;""" // `limit 1, subquery, join`: qt_limit_subquery_join """explain shape plan SELECT * FROM (SELECT t1.id FROM t1 JOIN t2 ON t1.id = t2.id) AS subq ORDER BY subq.id LIMIT 1;""" @@ -187,16 +187,16 @@ suite("order_push_down") { qt_limit_subquery_distinct """explain shape plan SELECT DISTINCT subq.id FROM (SELECT id FROM t1) AS subq ORDER BY subq.id LIMIT 1;""" // `limit 1, cross join`: - qt_limit_cross_join """explain shape plan SELECT t1.id FROM t1 INNER JOIN t2 on true ORDER BY t1.id LIMIT 1;""" + qt_limit_cross_join """explain shape plan SELECT t1.id FROM t1 INNER JOIN t2 on true order by t1.msg LIMIT 1;""" // `limit 1, multiple left outer join`: - qt_limit_multiple_left_outer_join """explain shape plan SELECT t1.id FROM t1 LEFT OUTER JOIN t2 ON t1.id = t2.id LEFT OUTER JOIN t3 ON t1.id = t3.id ORDER BY t1.id LIMIT 1;""" + qt_limit_multiple_left_outer_join """explain shape plan SELECT t1.id FROM t1 LEFT OUTER JOIN t2 ON t1.id = t2.id LEFT OUTER JOIN t3 ON t1.id = t3.id order by t1.msg LIMIT 1;""" // `limit 1, multiple right outer join`: - qt_limit_multiple_right_outer_join """explain shape plan SELECT t1.id FROM t1 RIGHT OUTER JOIN t2 ON t1.id = t2.id RIGHT OUTER JOIN t3 ON t1.id = t3.id ORDER BY t1.id LIMIT 1;""" + qt_limit_multiple_right_outer_join """explain shape plan SELECT t1.id FROM t1 RIGHT OUTER JOIN t2 ON t1.id = t2.id RIGHT OUTER JOIN t3 ON t1.id = t3.id order by t1.msg LIMIT 1;""" // `limit 1, multiple full outer join`: - qt_limit_multiple_full_outerjoin """explain shape plan SELECT t1.id FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.id FULL OUTER JOIN t3 ON t1.id = t3.id ORDER BY t1.id LIMIT 1;""" + qt_limit_multiple_full_outerjoin """explain shape plan SELECT t1.id FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.id FULL OUTER JOIN t3 ON t1.id = t3.id order by t1.msg LIMIT 1;""" // `limit 1, subquery, cross join`: qt_limit_subquery_cross_join """explain shape plan SELECT * FROM (SELECT t1.id FROM t1) AS subq CROSS JOIN t2 ORDER BY subq.id LIMIT 1;""" --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
