# HG changeset patch
# User nuno-faria <nunofpfaria@gmail.com>
# Date 1658776868 -3600
# Node ID b226dd5edbe8d10e9b28741829c3dfa9fc6f5d29
# Parent  85d2040d989d5b2dd407b4c0d3b3a7130bb5bffa
Add select window pushdown support for 'like' filters

diff --git a/sql/server/rel_optimizer.c b/sql/server/rel_optimizer.c
--- 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 */
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
@@ -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