1 files changed, 100 insertions, 0 deletions
diff --git a/tests/fixtures/optimizer/merge_subqueries.sql b/tests/fixtures/optimizer/merge_subqueries.sql
index c8186cc..a82e1ed 100644
--- a/tests/fixtures/optimizer/merge_subqueries.sql
+++ b/tests/fixtures/optimizer/merge_subqueries.sql
@@ -187,3 +187,103 @@ FROM (
   ) AS x
 ) AS x;
 SELECT /*+ BROADCAST(x) */ x.a AS a, x.c AS c FROM (SELECT x.a AS a, COUNT(1) AS c FROM x AS x GROUP BY x.a) AS x;
+
+# title: Test preventing merge of window expressions where clause
+with t1 as (
+  SELECT
+    x.a,
+    x.b,
+    ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) as row_num
+  FROM
+    x
+)
+SELECT
+  t1.a,
+  t1.b
+FROM
+  t1
+WHERE
+  row_num = 1;
+WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.a AS a, t1.b AS b FROM t1 WHERE t1.row_num = 1;
+
+# title: Test preventing merge of window expressions join clause
+with t1 as (
+  SELECT
+    x.a,
+    x.b,
+    ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) as row_num
+  FROM
+    x
+)
+SELECT
+  t1.a,
+  t1.b
+FROM t1 JOIN y ON t1.a = y.c AND t1.row_num = 1;
+WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.a AS a, t1.b AS b FROM t1 JOIN y AS y ON t1.a = y.c AND t1.row_num = 1;
+
+# title: Test preventing merge of window expressions agg function
+with t1 as (
+  SELECT
+    x.a,
+    x.b,
+    ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) as row_num
+  FROM
+    x
+)
+SELECT
+  SUM(t1.row_num) as total_rows
+FROM
+  t1;
+WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT SUM(t1.row_num) AS total_rows FROM t1;
+
+# title: Test prevent merging of window if in group by func
+with t1 as (
+  SELECT
+    x.a,
+    x.b,
+    ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) as row_num
+  FROM
+    x
+)
+SELECT
+  t1.row_num AS row_num,
+  SUM(t1.a) AS total
+FROM
+  t1
+GROUP BY t1.row_num
+ORDER BY t1.row_num;
+WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.row_num AS row_num, SUM(t1.a) AS total FROM t1 GROUP BY t1.row_num ORDER BY t1.row_num;
+
+# title: Test prevent merging of window if in order by func
+with t1 as (
+  SELECT
+    x.a,
+    x.b,
+    ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) as row_num
+  FROM
+    x
+)
+SELECT
+  t1.row_num AS row_num,
+  t1.a AS a
+FROM
+  t1
+ORDER BY t1.row_num, t1.a;
+WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.row_num AS row_num, t1.a AS a FROM t1 ORDER BY t1.row_num, t1.a;
+
+# title: Test allow merging of window function
+with t1 as (
+  SELECT
+    x.a,
+    x.b,
+    ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) as row_num
+  FROM
+    x
+)
+SELECT
+  t1.a,
+  t1.b,
+  t1.row_num
+FROM
+  t1;
+SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x;