From 3d48060515ba25b4c49d975a520ee0682327d1b7 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Fri, 16 Feb 2024 06:45:52 +0100 Subject: Merging upstream version 21.1.1. Signed-off-by: Daniel Baumann --- tests/fixtures/identity.sql | 3 +++ tests/fixtures/optimizer/canonicalize.sql | 12 +++++++++ tests/fixtures/optimizer/merge_subqueries.sql | 6 +++-- tests/fixtures/optimizer/qualify_columns.sql | 32 +++++++++++----------- tests/fixtures/optimizer/tpc-h/tpc-h.sql | 38 +++++++++++++-------------- 5 files changed, 54 insertions(+), 37 deletions(-) (limited to 'tests/fixtures') diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql index 366b79e..d9efc57 100644 --- a/tests/fixtures/identity.sql +++ b/tests/fixtures/identity.sql @@ -847,3 +847,6 @@ WITH use(use) AS (SELECT 1) SELECT use FROM use SELECT recursive FROM t SELECT (ROW_NUMBER() OVER (PARTITION BY user ORDER BY date ASC) - ROW_NUMBER() OVER (PARTITION BY user, segment ORDER BY date ASC)) AS group_id FROM example_table CAST(foo AS BPCHAR) +values +SELECT values +SELECT values AS values FROM t WHERE values + 1 > 3 diff --git a/tests/fixtures/optimizer/canonicalize.sql b/tests/fixtures/optimizer/canonicalize.sql index 4db3764..98b2f07 100644 --- a/tests/fixtures/optimizer/canonicalize.sql +++ b/tests/fixtures/optimizer/canonicalize.sql @@ -96,3 +96,15 @@ DATE_TRUNC('DAY', CAST('2023-01-01' AS DATE)); DATEDIFF('2023-01-01', '2023-01-02', DAY); DATEDIFF(CAST('2023-01-01' AS DATETIME), CAST('2023-01-02' AS DATETIME), DAY); + +-------------------------------------- +-- Remove redundant casts +-------------------------------------- +CAST(CAST('2023-01-01' AS DATE) AS DATE); +CAST('2023-01-01' AS DATE); + +CAST(DATE_TRUNC('YEAR', CAST('2023-01-01' AS DATE)) AS DATE); +DATE_TRUNC('YEAR', CAST('2023-01-01' AS DATE)); + +DATE(DATE_TRUNC('YEAR', CAST("x" AS DATE))); +DATE_TRUNC('YEAR', CAST("x" AS DATE)); diff --git a/tests/fixtures/optimizer/merge_subqueries.sql b/tests/fixtures/optimizer/merge_subqueries.sql index 7bc45a7..0f22925 100644 --- a/tests/fixtures/optimizer/merge_subqueries.sql +++ b/tests/fixtures/optimizer/merge_subqueries.sql @@ -218,6 +218,7 @@ with t1 as ( ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) as row_num FROM x + ORDER BY x.a, x.b, row_num ) SELECT t1.a, @@ -226,7 +227,7 @@ 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 AS t1 WHERE 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 ORDER BY x.a, x.b, row_num) SELECT t1.a AS a, t1.b AS b FROM t1 AS t1 WHERE t1.row_num = 1; # title: Test preventing merge of window expressions join clause with t1 as ( @@ -301,6 +302,7 @@ with t1 as ( ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) as row_num FROM x + ORDER BY x.a, x.b, row_num ) SELECT t1.a, @@ -308,7 +310,7 @@ SELECT 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; +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 ORDER BY x.a, x.b, row_num; # title: Don't merge window functions, inner table is aliased in outer query with t1 as ( diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql index ad197db..4fdf33b 100644 --- a/tests/fixtures/optimizer/qualify_columns.sql +++ b/tests/fixtures/optimizer/qualify_columns.sql @@ -208,14 +208,14 @@ SELECT x.a AS a, y.c AS c FROM x AS x, y AS y; -------------------------------------- -- Unions -------------------------------------- -SELECT a FROM x UNION SELECT a FROM x; -SELECT x.a AS a FROM x AS x UNION SELECT x.a AS a FROM x AS x; +SELECT a FROM x UNION SELECT a FROM x ORDER BY a; +SELECT x.a AS a FROM x AS x UNION SELECT x.a AS a FROM x AS x ORDER BY a; -SELECT a FROM x UNION SELECT a FROM x UNION SELECT a FROM x; -SELECT x.a AS a FROM x AS x UNION SELECT x.a AS a FROM x AS x UNION SELECT x.a AS a FROM x AS x; +SELECT a FROM x UNION SELECT a FROM x UNION SELECT a FROM x ORDER BY a; +SELECT x.a AS a FROM x AS x UNION SELECT x.a AS a FROM x AS x UNION SELECT x.a AS a FROM x AS x ORDER BY a; -SELECT a FROM (SELECT a FROM x UNION SELECT a FROM x); -SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x UNION SELECT x.a AS a FROM x AS x) AS _q_0; +SELECT a FROM (SELECT a FROM x UNION SELECT a FROM x) ORDER BY a; +SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x UNION SELECT x.a AS a FROM x AS x) AS _q_0 ORDER BY a; -------------------------------------- -- Subqueries @@ -318,8 +318,8 @@ WITH z AS (SELECT x.a AS a FROM x AS x) SELECT z.a AS a FROM z AS z; WITH z AS (SELECT a FROM x), q AS (SELECT * FROM z) SELECT * FROM q; WITH z AS (SELECT x.a AS a FROM x AS x), q AS (SELECT z.a AS a FROM z AS z) SELECT q.a AS a FROM q AS q; -WITH z AS (SELECT * FROM x) SELECT * FROM z UNION SELECT * FROM z; -WITH z AS (SELECT x.a AS a, x.b AS b FROM x AS x) SELECT z.a AS a, z.b AS b FROM z AS z UNION SELECT z.a AS a, z.b AS b FROM z AS z; +WITH z AS (SELECT * FROM x) SELECT * FROM z UNION SELECT * FROM z ORDER BY a, b; +WITH z AS (SELECT x.a AS a, x.b AS b FROM x AS x) SELECT z.a AS a, z.b AS b FROM z AS z UNION SELECT z.a AS a, z.b AS b FROM z AS z ORDER BY a, b; WITH z AS (SELECT * FROM x), q AS (SELECT b FROM z) SELECT b FROM q; WITH z AS (SELECT x.a AS a, x.b AS b FROM x AS x), q AS (SELECT z.b AS b FROM z AS z) SELECT q.b AS b FROM q AS q; @@ -359,8 +359,8 @@ SELECT x.b AS b FROM x AS x; SELECT * EXCEPT (a, b) FROM x; SELECT * EXCEPT (x.a, x.b) FROM x AS x; -SELECT COALESCE(t1.a, '') AS a, t2.* EXCEPT (a) FROM x AS t1, x AS t2; -SELECT COALESCE(t1.a, '') AS a, t2.b AS b FROM x AS t1, x AS t2; +SELECT COALESCE(CAST(t1.a AS VARCHAR), '') AS a, t2.* EXCEPT (a) FROM x AS t1, x AS t2; +SELECT COALESCE(CAST(t1.a AS VARCHAR), '') AS a, t2.b AS b FROM x AS t1, x AS t2; -------------------------------------- -- Using @@ -468,8 +468,8 @@ select * from unnest ([1, 2]) as x with offset as y; SELECT x AS x, y AS y FROM UNNEST([1, 2]) AS x WITH OFFSET AS y; # dialect: presto -SELECT x.a, i.b FROM x CROSS JOIN UNNEST(SPLIT(b, ',')) AS i(b); -SELECT x.a AS a, i.b AS b FROM x AS x CROSS JOIN UNNEST(SPLIT(x.b, ',')) AS i(b); +SELECT x.a, i.b FROM x CROSS JOIN UNNEST(SPLIT(CAST(b AS VARCHAR), ',')) AS i(b); +SELECT x.a AS a, i.b AS b FROM x AS x CROSS JOIN UNNEST(SPLIT(CAST(x.b AS VARCHAR), ',')) AS i(b); # execute: false SELECT c FROM (SELECT 1 a) AS x LATERAL VIEW EXPLODE(a) AS c; @@ -487,16 +487,16 @@ SELECT t.c1 AS c1, t.c3 AS c3 FROM FOO(bar) AS t(c1, c2, c3); -- Window functions -------------------------------------- # title: ORDER BY in window function -SELECT a + 1 AS a, ROW_NUMBER() OVER (PARTITION BY b ORDER BY a) AS row_num FROM x; -SELECT x.a + 1 AS a, ROW_NUMBER() OVER (PARTITION BY x.b ORDER BY x.a) AS row_num FROM x AS x; +SELECT a + 1 AS a, ROW_NUMBER() OVER (PARTITION BY b ORDER BY a) AS row_num FROM x ORDER BY a, row_num; +SELECT x.a + 1 AS a, ROW_NUMBER() OVER (PARTITION BY x.b ORDER BY x.a) AS row_num FROM x AS x ORDER BY a, row_num; # dialect: bigquery SELECT ROW_NUMBER() OVER (PARTITION BY a ORDER BY b) AS row_num FROM x QUALIFY row_num = 1; SELECT ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.b) AS row_num FROM x AS x QUALIFY ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.b) = 1; # dialect: bigquery -SELECT x.b, x.a FROM x LEFT JOIN y ON x.b = y.b QUALIFY ROW_NUMBER() OVER(PARTITION BY x.b ORDER BY x.a DESC) = 1; -SELECT x.b AS b, x.a AS a FROM x AS x LEFT JOIN y AS y ON x.b = y.b QUALIFY ROW_NUMBER() OVER (PARTITION BY x.b ORDER BY x.a DESC) = 1; +SELECT x.b, x.a FROM x LEFT JOIN y ON x.b = y.b QUALIFY ROW_NUMBER() OVER(PARTITION BY x.b ORDER BY x.a DESC) = 1 ORDER BY x.b, x.a; +SELECT x.b AS b, x.a AS a FROM x AS x LEFT JOIN y AS y ON x.b = y.b QUALIFY ROW_NUMBER() OVER (PARTITION BY x.b ORDER BY x.a DESC) = 1 ORDER BY x.b, x.a; SELECT * FROM x QUALIFY COUNT(a) OVER (PARTITION BY b) > 1; SELECT x.a AS a, x.b AS b FROM x AS x QUALIFY COUNT(x.a) OVER (PARTITION BY x.b) > 1; diff --git a/tests/fixtures/optimizer/tpc-h/tpc-h.sql b/tests/fixtures/optimizer/tpc-h/tpc-h.sql index bf624da..a99abcd 100644 --- a/tests/fixtures/optimizer/tpc-h/tpc-h.sql +++ b/tests/fixtures/optimizer/tpc-h/tpc-h.sql @@ -15,7 +15,7 @@ select from lineitem where - l_shipdate <= date '1998-12-01' - interval '90' day + CAST(l_shipdate AS DATE) <= date '1998-12-01' - interval '90' day group by l_returnflag, l_linestatus @@ -218,8 +218,8 @@ select from orders where - o_orderdate >= date '1993-07-01' - and o_orderdate < date '1993-07-01' + interval '3' month + CAST(o_orderdate AS DATE) >= date '1993-07-01' + and CAST(o_orderdate AS DATE) < date '1993-07-01' + interval '3' month and exists ( select * @@ -278,8 +278,8 @@ where and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'ASIA' - and o_orderdate >= date '1994-01-01' - and o_orderdate < date '1994-01-01' + interval '1' year + and CAST(o_orderdate AS DATE) >= date '1994-01-01' + and CAST(o_orderdate AS DATE) < date '1994-01-01' + interval '1' year group by n_name order by @@ -316,8 +316,8 @@ select from lineitem where - l_shipdate >= date '1994-01-01' - and l_shipdate < date '1994-01-01' + interval '1' year + CAST(l_shipdate AS DATE) >= date '1994-01-01' + and CAST(l_shipdate AS DATE) < date '1994-01-01' + interval '1' year and l_discount between 0.06 - 0.01 and 0.06 + 0.01 and l_quantity < 24; SELECT @@ -362,7 +362,7 @@ from (n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY') or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE') ) - and l_shipdate between date '1995-01-01' and date '1996-12-31' + and CAST(l_shipdate AS DATE) between date '1995-01-01' and date '1996-12-31' ) as shipping group by supp_nation, @@ -446,7 +446,7 @@ from and n1.n_regionkey = r_regionkey and r_name = 'AMERICA' and s_nationkey = n2.n_nationkey - and o_orderdate between date '1995-01-01' and date '1996-12-31' + and CAST(o_orderdate AS DATE) between date '1995-01-01' and date '1996-12-31' and p_type = 'ECONOMY ANODIZED STEEL' ) as all_nations group by @@ -574,8 +574,8 @@ from where c_custkey = o_custkey and l_orderkey = o_orderkey - and o_orderdate >= date '1993-10-01' - and o_orderdate < date '1993-10-01' + interval '3' month + and CAST(o_orderdate AS DATE) >= date '1993-10-01' + and CAST(o_orderdate AS DATE) < date '1993-10-01' + interval '3' month and l_returnflag = 'R' and c_nationkey = n_nationkey group by @@ -714,8 +714,8 @@ where and l_shipmode in ('MAIL', 'SHIP') and l_commitdate < l_receiptdate and l_shipdate < l_commitdate - and l_receiptdate >= date '1994-01-01' - and l_receiptdate < date '1994-01-01' + interval '1' year + and CAST(l_receiptdate AS DATE) >= date '1994-01-01' + and CAST(l_receiptdate AS DATE) < date '1994-01-01' + interval '1' year group by l_shipmode order by @@ -813,8 +813,8 @@ from part where l_partkey = p_partkey - and l_shipdate >= date '1995-09-01' - and l_shipdate < date '1995-09-01' + interval '1' month; + and CAST(l_shipdate AS DATE) >= date '1995-09-01' + and CAST(l_shipdate AS DATE) < date '1995-09-01' + interval '1' month; SELECT 100.00 * SUM( CASE @@ -844,8 +844,8 @@ with revenue (supplier_no, total_revenue) as ( from lineitem where - l_shipdate >= date '1996-01-01' - and l_shipdate < date '1996-01-01' + interval '3' month + CAST(l_shipdate AS DATE) >= date '1996-01-01' + and CAST(l_shipdate AS DATE) < date '1996-01-01' + interval '3' month group by l_suppkey) select @@ -1223,8 +1223,8 @@ where where l_partkey = ps_partkey and l_suppkey = ps_suppkey - and l_shipdate >= date '1994-01-01' - and l_shipdate < date '1994-01-01' + interval '1' year + and CAST(l_shipdate AS DATE) >= date '1994-01-01' + and CAST(l_shipdate AS DATE) < date '1994-01-01' + interval '1' year ) ) and s_nationkey = n_nationkey -- cgit v1.2.3