Mercurial > hg > MonetDB

view sql/test/Tests/select_window_pushdown.test @ 86153:b226dd5edbe8 select-window-pushdown

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add select window pushdown support for 'like' filters
author nuno-faria <nunofpfaria@gmail.com>
date Mon, 25 Jul 2022 20:21:08 +0100
parents 85d2040d989d
children
line wrap: on
line source

# 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


# test with string filters
# (previously e_convert were not being considered)
statement ok
CREATE TABLE Test (k varchar(100), v int);

statement ok
INSERT INTO Test SELECT value % 10 as k, value as v FROM generate_series(1, 100);

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" ]
| | | | | ) [ (char(100)["test"."k"]) = (char(100) "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" ]


# test with like-type filters
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 like '%10%';
----
project (
| select (
| | project (
| | | project (
| | | | project (
| | | | | select (
| | | | | | table("sys"."test") [ "test"."k", "test"."v" ]
| | | | | ) [ (clob["test"."k"]) FILTER "sys"."like"(clob "%10%", clob "", boolean(1) "false") ]
| | | | ) [ "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" ]


statement ok
DROP TABLE Test