Changeset: 8b56a4b04198 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/8b56a4b04198
Modified Files:
sql/test/Tests/select_window_pushdown.test
Branch: Sep2022
Log Message:
Include properties info in test output.
diffs (116 lines):
diff --git a/sql/test/Tests/select_window_pushdown.test
b/sql/test/Tests/select_window_pushdown.test
--- a/sql/test/Tests/select_window_pushdown.test
+++ b/sql/test/Tests/select_window_pushdown.test
@@ -5,6 +5,7 @@ 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
@@ -32,6 +33,7 @@ project (
| ) [ ("t1"."flag" NOT NULL) = (boolean(1) "false"), ("t2"."rank") = (int(32)
"1") ]
) [ "t1"."k" NOT NULL as "t2"."k", "t1"."v" NOT NULL UNIQUE as "t2"."v",
"t1"."flag" NOT NULL as "t2"."flag", "t2"."rank" NOT NULL ]
+
# simple range filter on the partition key
query T nosort
plan SELECT *
@@ -58,6 +60,7 @@ project (
| ) [ ("t1"."flag" NOT NULL) = (boolean(1) "false"), ("t2"."rank") = (int(32)
"1") ]
) [ "t1"."k" NOT NULL as "t2"."k", "t1"."v" NOT NULL UNIQUE as "t2"."v",
"t1"."flag" NOT NULL as "t2"."flag", "t2"."rank" NOT NULL ]
+
# simple not in filter on the partition key
query T nosort
plan SELECT *
@@ -84,6 +87,7 @@ project (
| ) [ ("t1"."flag" NOT NULL) = (boolean(1) "false"), ("t2"."rank") = (int(32)
"1") ]
) [ "t1"."k" NOT NULL as "t2"."k", "t1"."v" NOT NULL UNIQUE as "t2"."v",
"t1"."flag" NOT NULL as "t2"."flag", "t2"."rank" NOT NULL ]
+
# another filter also not on the partition key, must not be pushed down
query T nosort
plan SELECT *
@@ -110,6 +114,7 @@ project (
| ) [ ("t1"."v" NOT NULL UNIQUE) = (int(32) "15"), ("t1"."flag" NOT NULL) =
(boolean(1) "false"), ("t2"."rank") = (int(32) "1") ]
) [ "t1"."k" NOT NULL as "t2"."k", "t1"."v" NOT NULL UNIQUE as "t2"."v",
"t1"."flag" NOT NULL as "t2"."flag", "t2"."rank" NOT NULL ]
+
# swapping k with v just to test for hardcoded optimizations,
# v is pushed down but not k
query T nosort
@@ -137,8 +142,36 @@ project (
| ) [ ("t1"."flag" NOT NULL) = (boolean(1) "false"), ("t2"."rank") = (int(32)
"1"), ("t1"."k" NOT NULL) = (int(32) "10") ]
) [ "t1"."k" NOT NULL as "t2"."k", "t1"."v" NOT NULL UNIQUE as "t2"."v",
"t1"."flag" NOT NULL as "t2"."flag", "t2"."rank" NOT NULL ]
+
# 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" NOT NULL, "test"."v" NOT NULL
UNIQUE ] COUNT 99
+| | | | | | ) [ "test"."k" NOT NULL as "t1"."k", "test"."v" NOT NULL UNIQUE as
"t1"."v", "sys"."mod"("test"."v" NOT NULL UNIQUE, int(32) "2") NOT NULL as
"%1"."%1", "sys"."="("%1"."%1" NOT NULL, int(32) "0") NOT NULL as "t1"."flag" ]
COUNT 99
+| | | | | ) [ "t1"."k" NOT NULL, "t1"."v" NOT NULL UNIQUE, "t1"."flag" NOT
NULL ] [ "t1"."k" ASC NOT NULL, "t1"."v" NULLS LAST NOT NULL UNIQUE ] COUNT 99
+| | | | ) [ "t1"."k" NOT NULL, "t1"."v" NOT NULL UNIQUE, "t1"."flag" NOT NULL,
"sys"."rank"("sys"."star"(), "sys"."diff"("t1"."k" NOT NULL),
"sys"."diff"("t1"."v" NOT NULL UNIQUE)) as "t2"."rank" ] COUNT 99
+| | | ) [ ("t1"."flag" NOT NULL) = (boolean(1) "false"), ("t2"."rank") =
(int(32) "1") ] COUNT 99
+| | ) [ "sys"."sql_mul"("t1"."k" NOT NULL, tinyint(4) "10") NOT NULL as
"t2"."k", "t1"."v" NOT NULL UNIQUE as "t2"."v", "t1"."flag" NOT NULL as
"t2"."flag", "t2"."rank" NOT NULL ] COUNT 99
+| ) [ ("t2"."k" NOT NULL) = (bigint(36) "10") ] COUNT 99
+) [ "t2"."k" NOT NULL, "t2"."v" NOT NULL UNIQUE, "t2"."flag" NOT NULL,
"t2"."rank" NOT NULL ] COUNT 99
+
+
# filter [partition column OR flag], cannot be safely pushed down
query T nosort
plan SELECT *
@@ -163,6 +196,7 @@ project (
| ) [ ("t2"."rank") = (int(32) "1"), (("t1"."flag" NOT NULL) = (boolean(1)
"false")) or (("t1"."k" NOT NULL) = (int(32) "10")) ]
) [ "t1"."k" NOT NULL as "t2"."k", "t1"."v" NOT NULL UNIQUE as "t2"."v",
"t1"."flag" NOT NULL as "t2"."flag", "t2"."rank" NOT NULL ]
+
# filter on k and v and both are partition columns, both filters can be pushed
down
query T nosort
plan SELECT *
@@ -189,9 +223,11 @@ project (
| ) [ ("t1"."flag" NOT NULL) = (boolean(1) "false"), ("t2"."rank") = (int(32)
"1") ]
) [ "t1"."k" NOT NULL as "t2"."k", "t1"."v" NOT NULL UNIQUE as "t2"."v",
"t1"."flag" NOT NULL as "t2"."flag", "t2"."rank" NOT NULL ]
+
statement ok
DROP TABLE Test
+
# test with string filters
# (previously e_convert were not being considered)
statement ok
@@ -225,6 +261,7 @@ project (
| ) [ ("t1"."flag" NOT NULL) = (boolean(1) "false"), ("t2"."rank") = (int(32)
"1") ]
) [ "t1"."k" NOT NULL as "t2"."k", "t1"."v" NOT NULL UNIQUE as "t2"."v",
"t1"."flag" NOT NULL as "t2"."flag", "t2"."rank" NOT NULL ]
+
# test with like-type filters
query T nosort
plan SELECT *
@@ -251,6 +288,7 @@ project (
| ) [ ("t1"."flag" NOT NULL) = (boolean(1) "false"), ("t2"."rank") = (int(32)
"1") ]
) [ "t1"."k" NOT NULL as "t2"."k", "t1"."v" NOT NULL UNIQUE as "t2"."v",
"t1"."flag" NOT NULL as "t2"."flag", "t2"."rank" NOT NULL ]
+
statement ok
DROP TABLE Test
_______________________________________________
checkin-list mailing list -- checkin-list@monetdb.org
To unsubscribe send an email to checkin-list-le...@monetdb.org
