Mercurial > hg > MonetDB

changeset 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 2a90add101fb
files sql/server/rel_optimizer.c sql/test/Tests/select_window_pushdown.test
diffstat 2 files changed, 37 insertions(+), 2 deletions(-) [+]
line wrap: on
line diff
--- a/sql/server/rel_optimizer.c
+++ b/sql/server/rel_optimizer.c
@@ -4709,8 +4709,16 @@ rel_push_select_down(visitor *v, sql_rel
 					if (e->type == e_cmp) {
 						/* simple comparison filter */
 						if (e->flag == cmp_gt || e->flag == cmp_gte || e->flag == cmp_lte || e->flag == cmp_lt
-							|| e->flag == cmp_equal || e->flag == cmp_notequal || e->flag == cmp_in || e->flag == cmp_notin) {
-							sql_exp* column = e->l;
+							|| e->flag == cmp_equal || e->flag == cmp_notequal || e->flag == cmp_in || e->flag == cmp_notin
+							|| (e->flag == cmp_filter && ((list*)e->l)->cnt == 1)) {
+							sql_exp* column;
+							/* the column in 'like' filters is stored inside a list */
+							if (e->flag == cmp_filter) {
+								column = ((list*)e->l)->h->data;
+							}
+							else {
+								column = e->l;
+							}
 
 							/* check if the expression matches any aggregation key, meaning we can 
 							   try to safely push it down */
--- a/sql/test/Tests/select_window_pushdown.test
+++ b/sql/test/Tests/select_window_pushdown.test
@@ -262,5 +262,32 @@ project (
 ) [ "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