Adding upstream version 21.1.1.upstream/21.1.1

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

4 files changed, 51 insertions, 37 deletions

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