From 684905e3de7854a3806ffa55e0d1a09431ba5a19 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 15 Oct 2022 15:53:00 +0200 Subject: Merging upstream version 7.1.3. Signed-off-by: Daniel Baumann --- tests/fixtures/optimizer/merge_subqueries.sql | 100 ++++++++++++++++++++++++++ 1 file changed, 100 insertions(+) (limited to 'tests/fixtures/optimizer/merge_subqueries.sql') 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; -- cgit v1.2.3