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 31b3feb5eae [fix](Nereids)fix unstable plan shape in limit_push_down
case
31b3feb5eae is described below
commit 31b3feb5eae1285be7fb541551a9b44ff4517050
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]
