Changeset: 4f6d106502de for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/4f6d106502de
Added Files:
sql/test/Tests/select_window_pushdown.test
Modified Files:
sql/test/Tests/All
Branch: select-window-pushdown
Log Message:
Add test for select pushdown on window functions
diffs (244 lines):
diff --git a/sql/test/Tests/All b/sql/test/Tests/All
--- a/sql/test/Tests/All
+++ b/sql/test/Tests/All
@@ -122,6 +122,7 @@ constant-not-in
unicode
window_functions
+select_window_pushdown
!NOWAL?hot_snapshot
HAVE_LIBZ&!NOWAL?hot_snapshot_gz
diff --git a/sql/test/Tests/select_window_pushdown.test
b/sql/test/Tests/select_window_pushdown.test
new file mode 100644
--- /dev/null
+++ b/sql/test/Tests/select_window_pushdown.test
@@ -0,0 +1,228 @@
+# init
+statement ok
+CREATE TABLE Test (k int, v int);
+
+statement ok
+INSERT INTO Test SELECT value % 10 as k, value as v FROM generate_series(1,
100);
+
+
+# simple eq filter on the partition key, must be pushed down,
+# while the flag filter cannot be safely pushed down
+query T nosort
+plan SELECT *
+FROM (
+ SELECT *, rank() OVER (PARTITION BY k ORDER BY v DESC) AS rank
+ FROM (
+ SELECT k, v, v % 2 = 0 AS flag
+ FROM Test
+ ) t1
+) t2
+WHERE rank = 1 AND NOT flag AND k = 10;
+----
+project (
+| select (
+| | project (
+| | | project (
+| | | | project (
+| | | | | select (
+| | | | | | table("sys"."test") [ "test"."k", "test"."v" ]
+| | | | | ) [ ("test"."k") = (int(32) "10") ]
+| | | | ) [ "test"."k" as "t1"."k", "test"."v" as "t1"."v",
"sys"."mod"("test"."v", int(32) "2") as "%1"."%1", "sys"."="("%1"."%1", int(32)
"0") as "t1"."flag" ]
+| | | ) [ "t1"."k", "t1"."v", "t1"."flag" ] [ "t1"."k" ASC, "t1"."v" NULLS
LAST ]
+| | ) [ "t1"."k", "t1"."v", "t1"."flag", "sys"."rank"("sys"."star"(),
"sys"."diff"("t1"."k"), "sys"."diff"("t1"."v")) as "t2"."rank" ]
+| ) [ ("t1"."flag") = (boolean(1) "false"), ("t2"."rank") = (int(32) "1") ]
+) [ "t1"."k" as "t2"."k", "t1"."v" as "t2"."v", "t1"."flag" as "t2"."flag",
"t2"."rank" ]
+
+
+# simple range filter on the partition key
+query T nosort
+plan SELECT *
+FROM (
+ SELECT *, rank() OVER (PARTITION BY k ORDER BY v DESC) AS rank
+ FROM (
+ SELECT k, v, v % 2 = 0 AS flag
+ FROM Test
+ ) t1
+) t2
+WHERE rank = 1 AND NOT flag AND k >= 10 AND k <= 50;
+----
+project (
+| select (
+| | project (
+| | | project (
+| | | | project (
+| | | | | select (
+| | | | | | table("sys"."test") [ "test"."k", "test"."v" ]
+| | | | | ) [ (int(32) "10") <= ("test"."k") <= (int(32) "50") ]
+| | | | ) [ "test"."k" as "t1"."k", "test"."v" as "t1"."v",
"sys"."mod"("test"."v", int(32) "2") as "%1"."%1", "sys"."="("%1"."%1", int(32)
"0") as "t1"."flag" ]
+| | | ) [ "t1"."k", "t1"."v", "t1"."flag" ] [ "t1"."k" ASC, "t1"."v" NULLS
LAST ]
+| | ) [ "t1"."k", "t1"."v", "t1"."flag", "sys"."rank"("sys"."star"(),
"sys"."diff"("t1"."k"), "sys"."diff"("t1"."v")) as "t2"."rank" ]
+| ) [ ("t1"."flag") = (boolean(1) "false"), ("t2"."rank") = (int(32) "1") ]
+) [ "t1"."k" as "t2"."k", "t1"."v" as "t2"."v", "t1"."flag" as "t2"."flag",
"t2"."rank" ]
+
+
+# simple not in filter on the partition key
+query T nosort
+plan SELECT *
+FROM (
+ SELECT *, rank() OVER (PARTITION BY k ORDER BY v DESC) AS rank
+ FROM (
+ SELECT k, v, v % 2 = 0 AS flag
+ FROM Test
+ ) t1
+) t2
+WHERE rank = 1 AND NOT flag AND k NOT IN (10, 20, 30);
+----
+project (
+| select (
+| | project (
+| | | project (
+| | | | project (
+| | | | | select (
+| | | | | | table("sys"."test") [ "test"."k", "test"."v" ]
+| | | | | ) [ ("test"."k") notin (int(32) "10", int(32) "20", int(32) "30") ]
+| | | | ) [ "test"."k" as "t1"."k", "test"."v" as "t1"."v",
"sys"."mod"("test"."v", int(32) "2") as "%2"."%2", "sys"."="("%2"."%2", int(32)
"0") as "t1"."flag" ]
+| | | ) [ "t1"."k", "t1"."v", "t1"."flag" ] [ "t1"."k" ASC, "t1"."v" NULLS
LAST ]
+| | ) [ "t1"."k", "t1"."v", "t1"."flag", "sys"."rank"("sys"."star"(),
"sys"."diff"("t1"."k"), "sys"."diff"("t1"."v")) as "t2"."rank" ]
+| ) [ ("t1"."flag") = (boolean(1) "false"), ("t2"."rank") = (int(32) "1") ]
+) [ "t1"."k" as "t2"."k", "t1"."v" as "t2"."v", "t1"."flag" as "t2"."flag",
"t2"."rank" ]
+
+
+# another filter also not on the partition key, must not be pushed down
+query T nosort
+plan SELECT *
+FROM (
+ SELECT *, rank() OVER (PARTITION BY k ORDER BY v DESC) AS rank
+ FROM (
+ SELECT k, v, v % 2 = 0 AS flag
+ FROM Test
+ ) t1
+) t2
+WHERE rank = 1 AND NOT flag AND k = 10 AND v = 15;
+----
+project (
+| select (
+| | project (
+| | | project (
+| | | | project (
+| | | | | select (
+| | | | | | table("sys"."test") [ "test"."k", "test"."v" ]
+| | | | | ) [ ("test"."k") = (int(32) "10") ]
+| | | | ) [ "test"."k" as "t1"."k", "test"."v" as "t1"."v",
"sys"."mod"("test"."v", int(32) "2") as "%1"."%1", "sys"."="("%1"."%1", int(32)
"0") as "t1"."flag" ]
+| | | ) [ "t1"."k", "t1"."v", "t1"."flag" ] [ "t1"."k" ASC, "t1"."v" NULLS
LAST ]
+| | ) [ "t1"."k", "t1"."v", "t1"."flag", "sys"."rank"("sys"."star"(),
"sys"."diff"("t1"."k"), "sys"."diff"("t1"."v")) as "t2"."rank" ]
+| ) [ ("t1"."v") = (int(32) "15"), ("t1"."flag") = (boolean(1) "false"),
("t2"."rank") = (int(32) "1") ]
+) [ "t1"."k" as "t2"."k", "t1"."v" as "t2"."v", "t1"."flag" as "t2"."flag",
"t2"."rank" ]
+
+
+# swapping k with v just to test for hardcoded optimizations,
+# v is pushed down but not k
+query T nosort
+plan SELECT *
+FROM (
+ SELECT *, rank() OVER (PARTITION BY v ORDER BY v DESC) AS rank
+ FROM (
+ SELECT k, v, v % 2 = 0 AS flag
+ FROM Test
+ ) t1
+) t2
+WHERE rank = 1 AND NOT flag AND k = 10 AND v = 15;
+----
+project (
+| select (
+| | project (
+| | | project (
+| | | | project (
+| | | | | select (
+| | | | | | table("sys"."test") [ "test"."k", "test"."v" ]
+| | | | | ) [ ("test"."v") = (int(32) "15") ]
+| | | | ) [ "test"."k" as "t1"."k", "test"."v" as "t1"."v",
"sys"."mod"("test"."v", int(32) "2") as "%1"."%1", "sys"."="("%1"."%1", int(32)
"0") as "t1"."flag" ]
+| | | ) [ "t1"."k", "t1"."v", "t1"."flag" ] [ "t1"."v" NULLS LAST ]
+| | ) [ "t1"."k", "t1"."v", "t1"."flag", "sys"."rank"("sys"."star"(),
"sys"."diff"("t1"."v"), "sys"."diff"("t1"."v")) as "t2"."rank" ]
+| ) [ ("t1"."flag") = (boolean(1) "false"), ("t2"."rank") = (int(32) "1"),
("t1"."k") = (int(32) "10") ]
+) [ "t1"."k" as "t2"."k", "t1"."v" as "t2"."v", "t1"."flag" as "t2"."flag",
"t2"."rank" ]
+
+
+# performing some additional computation on the partition key,
+# filter cannot be pushed down
+plan SELECT *
+FROM (
+ SELECT k * 10 as k, v, flag, rank() OVER (PARTITION BY k ORDER BY v DESC)
AS rank
+ FROM (
+ SELECT k, v, v % 2 = 0 AS flag
+ FROM Test
+ ) t1
+) t2
+WHERE rank = 1 AND NOT flag AND k = 10;
+----
+project (
+| select (
+| | project (
+| | | select (
+| | | | project (
+| | | | | project (
+| | | | | | project (
+| | | | | | | table("sys"."test") [ "test"."k", "test"."v" ]
+| | | | | | ) [ "test"."k" as "t1"."k", "test"."v" as "t1"."v",
"sys"."mod"("test"."v", int(32) "2") as "%1"."%1", "sys"."="("%1"."%1", int(32)
"0") as "t1"."flag" ]
+| | | | | ) [ "t1"."k", "t1"."v", "t1"."flag" ] [ "t1"."k" ASC, "t1"."v" NULLS
LAST ]
+| | | | ) [ "t1"."k", "t1"."v", "t1"."flag", "sys"."rank"("sys"."star"(),
"sys"."diff"("t1"."k"), "sys"."diff"("t1"."v")) as "t2"."rank" ]
+| | | ) [ ("t1"."flag") = (boolean(1) "false"), ("t2"."rank") = (int(32) "1") ]
+| | ) [ "sys"."sql_mul"("t1"."k", tinyint(4) "10") as "t2"."k", "t1"."v" as
"t2"."v", "t1"."flag" as "t2"."flag", "t2"."rank" ]
+| ) [ ("t2"."k") = (bigint(36) "10") ]
+) [ "t2"."k", "t2"."v", "t2"."flag", "t2"."rank" ]
+
+
+# filter [partition column OR flag], cannot be safely pushed down
+query T nosort
+plan SELECT *
+FROM (
+ SELECT *, rank() OVER (PARTITION BY k ORDER BY v DESC) AS rank
+ FROM (
+ SELECT k, v, v % 2 = 0 AS flag
+ FROM Test
+ ) t1
+) t2
+WHERE rank = 1 AND (NOT flag OR k = 10);
+----
+project (
+| select (
+| | project (
+| | | project (
+| | | | project (
+| | | | | table("sys"."test") [ "test"."k", "test"."v" ]
+| | | | ) [ "test"."k" as "t1"."k", "test"."v" as "t1"."v",
"sys"."mod"("test"."v", int(32) "2") as "%1"."%1", "sys"."="("%1"."%1", int(32)
"0") as "t1"."flag" ]
+| | | ) [ "t1"."k", "t1"."v", "t1"."flag" ] [ "t1"."k" ASC, "t1"."v" NULLS
LAST ]
+| | ) [ "t1"."k", "t1"."v", "t1"."flag", "sys"."rank"("sys"."star"(),
"sys"."diff"("t1"."k"), "sys"."diff"("t1"."v")) as "t2"."rank" ]
+| ) [ ("t2"."rank") = (int(32) "1"), (("t1"."flag") = (boolean(1) "false")) or
(("t1"."k") = (int(32) "10")) ]
+) [ "t1"."k" as "t2"."k", "t1"."v" as "t2"."v", "t1"."flag" as "t2"."flag",
"t2"."rank" ]
+
+
+# filter on k and v and both are partition columns, both filters can be pushed
down
+query T nosort
+plan SELECT *
+FROM (
+ SELECT *, rank() OVER (PARTITION BY k, v ORDER BY v DESC) AS rank
+ FROM (
+ SELECT k, v, v % 2 = 0 AS flag
+ FROM Test
+ ) t1
+) t2
+WHERE rank = 1 AND NOT flag AND k <= 10 AND v IN (1, 2, 3);
+----
+project (
+| select (
+| | project (
+| | | project (
+| | | | project (
+| | | | | select (
+| | | | | | table("sys"."test") [ "test"."k", "test"."v" ]
+| | | | | ) [ ("test"."k") <= (int(32) "10"), ("test"."v") in (int(32) "1",
int(32) "2", int(32) "3") ]
+| | | | ) [ "test"."k" as "t1"."k", "test"."v" as "t1"."v",
"sys"."mod"("test"."v", int(32) "2") as "%2"."%2", "sys"."="("%2"."%2", int(32)
"0") as "t1"."flag" ]
+| | | ) [ "t1"."k", "t1"."v", "t1"."flag" ] [ "t1"."k" ASC, "t1"."v" NULLS
LAST ]
+| | ) [ "t1"."k", "t1"."v", "t1"."flag", "sys"."rank"("sys"."star"(),
"sys"."diff"("sys"."diff"("t1"."k"), "t1"."v"), "sys"."diff"("t1"."v")) as
"t2"."rank" ]
+| ) [ ("t1"."flag") = (boolean(1) "false"), ("t2"."rank") = (int(32) "1") ]
+) [ "t1"."k" as "t2"."k", "t1"."v" as "t2"."v", "t1"."flag" as "t2"."flag",
"t2"."rank" ]
+
+
+statement ok
+DROP TABLE Test
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
