From fb7e79eb4c8d6e22b7324de4bb1ea9cd11b8da7c Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Fri, 7 Apr 2023 14:35:04 +0200 Subject: Merging upstream version 11.5.2. Signed-off-by: Daniel Baumann --- tests/fixtures/optimizer/optimizer.sql | 1 + tests/fixtures/optimizer/qualify_columns.sql | 22 +++++++++++++--------- tests/fixtures/optimizer/simplify.sql | 16 +++++++++++----- tests/fixtures/optimizer/tpc-ds/tpc-ds.sql | 16 ++++++++-------- 4 files changed, 33 insertions(+), 22 deletions(-) (limited to 'tests/fixtures/optimizer') diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql index c5112b2..9e7880c 100644 --- a/tests/fixtures/optimizer/optimizer.sql +++ b/tests/fixtures/optimizer/optimizer.sql @@ -462,6 +462,7 @@ SELECT FROM "db1"."tbl" AS "tbl" CROSS JOIN "db2"."tbl" AS "tbl_2"; +# execute: false SELECT *, IFF( diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql index df65e65..74e2d0a 100644 --- a/tests/fixtures/optimizer/qualify_columns.sql +++ b/tests/fixtures/optimizer/qualify_columns.sql @@ -35,6 +35,9 @@ SELECT 1 AS "1", 2 + 3 AS _col_1 FROM x AS x; SELECT a + b FROM x; SELECT x.a + x.b AS _col_0 FROM x AS x; +SELECT l.a FROM x l WHERE a IN (select a FROM x ORDER by a); +SELECT l.a AS a FROM x AS l WHERE l.a IN (SELECT x.a AS a FROM x AS x ORDER BY a); + # execute: false SELECT a, SUM(b) FROM x WHERE a > 1 AND b > 1 GROUP BY a; SELECT x.a AS a, SUM(x.b) AS _col_1 FROM x AS x WHERE x.a > 1 AND x.b > 1 GROUP BY x.a; @@ -46,15 +49,14 @@ SELECT SUM(a) AS a FROM x HAVING SUM(a) > 3; SELECT SUM(x.a) AS a FROM x AS x HAVING SUM(x.a) > 3; SELECT SUM(a) AS c FROM x HAVING c > 3; -SELECT SUM(x.a) AS c FROM x AS x HAVING c > 3; +SELECT SUM(x.a) AS c FROM x AS x HAVING SUM(x.a) > 3; # execute: false SELECT SUM(a) AS a FROM x HAVING a > 3; -SELECT SUM(x.a) AS a FROM x AS x HAVING a > 3; +SELECT SUM(x.a) AS a FROM x AS x HAVING SUM(x.a) > 3; -# execute: false -SELECT SUM(a) AS c FROM x HAVING SUM(c) > 3; -SELECT SUM(x.a) AS c FROM x AS x HAVING SUM(c) > 3; +SELECT SUM(a) AS c FROM x HAVING SUM(b) > 3; +SELECT SUM(x.a) AS c FROM x AS x HAVING SUM(x.b) > 3; SELECT a AS j, b FROM x ORDER BY j; SELECT x.a AS j, x.b AS b FROM x AS x ORDER BY j; @@ -95,6 +97,7 @@ SELECT COALESCE(x.a) AS d FROM x AS x JOIN y AS y ON x.b = y.b GROUP BY COALESCE SELECT a + 1 AS d FROM x WHERE d > 1; SELECT x.a + 1 AS d FROM x AS x WHERE x.a + 1 > 1; +# execute: false SELECT a + 1 AS d, d + 2 FROM x; SELECT x.a + 1 AS d, x.a + 1 + 2 AS _col_1 FROM x AS x; @@ -124,6 +127,10 @@ SELECT DATE_TRUNC('week', x.a) AS a FROM x AS x; SELECT DATE_TRUNC(a, MONTH) AS a FROM x; SELECT DATE_TRUNC(x.a, MONTH) AS a FROM x AS x; +# execute: false +SELECT x FROM READ_PARQUET('path.parquet', hive_partition=1); +SELECT _q_0.x AS x FROM READ_PARQUET('path.parquet', hive_partition = 1) AS _q_0; + -------------------------------------- -- Derived tables -------------------------------------- @@ -262,11 +269,9 @@ SELECT x.a AS d, x.b AS b FROM x AS x; SELECT * EXCEPT(b) REPLACE(a AS d) FROM x; SELECT x.a AS d FROM x AS x; -# execute: false SELECT x.* EXCEPT(a), y.* FROM x, y; SELECT x.b AS b, y.b AS b, y.c AS c FROM x AS x, y AS y; -# execute: false SELECT * EXCEPT(a) FROM x; SELECT x.b AS b FROM x AS x; @@ -338,12 +343,11 @@ SELECT t.c AS c FROM x AS x LATERAL VIEW EXPLODE(x.a) t AS c; SELECT aa FROM x, UNNEST(a) AS t(aa); SELECT t.aa AS aa FROM x AS x, UNNEST(x.a) AS t(aa); -# execute: false # dialect: bigquery +# execute: false SELECT aa FROM x, UNNEST(a) AS aa; SELECT aa AS aa FROM x AS x, UNNEST(x.a) AS aa; -# execute: false # 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); diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql index 08e8700..54ec64b 100644 --- a/tests/fixtures/optimizer/simplify.sql +++ b/tests/fixtures/optimizer/simplify.sql @@ -282,6 +282,9 @@ x * (1 - y); -1 + 3; 2; +1 - 2 - 4; +-5; + -(-1); 1; @@ -307,19 +310,22 @@ x * (1 - y); 0.0219; 1 / 3; -0; +1 / 3; + +1 / 3.0; +0.3333333333333333333333333333; 20.0 / 6; 3.333333333333333333333333333; 10 / 5; -2; +10 / 5; (1.0 * 3) * 4 - 2 * (5 / 2); -8.0; +12.0 - 2 * (5 / 2); 6 - 2 + 4 * 2 + a; -a + 12; +12 + a; a + 1 + 1 + 2; a + 4; @@ -376,7 +382,7 @@ interval '1' year + date '1998-01-01'; CAST('1999-01-01' AS DATE); interval '1' year + date '1998-01-01' + 3 * 7 * 4; -84 + CAST('1999-01-01' AS DATE); +CAST('1999-01-01' AS DATE) + 84; date '1998-12-01' - interval '90' foo; CAST('1998-12-01' AS DATE) - INTERVAL '90' foo; diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql index b92ad37..d9a06cc 100644 --- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql +++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql @@ -6145,7 +6145,7 @@ WITH web_v1 AS ( SELECT ws_item_sk item_sk, d_date, - sum(Sum(ws_sales_price)) OVER (partition BY ws_item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row) cume_sales + sum(Sum(ws_sales_price)) OVER (partition BY ws_item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cume_sales FROM web_sales , date_dim WHERE ws_sold_date_sk=d_date_sk @@ -6156,7 +6156,7 @@ WITH web_v1 AS ( SELECT ss_item_sk item_sk, d_date, - sum(sum(ss_sales_price)) OVER (partition BY ss_item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row) cume_sales + sum(sum(ss_sales_price)) OVER (partition BY ss_item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cume_sales FROM store_sales , date_dim WHERE ss_sold_date_sk=d_date_sk @@ -6171,8 +6171,8 @@ FROM ( d_date , web_sales , store_sales , - max(web_sales) OVER (partition BY item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row) web_cumulative , - max(store_sales) OVER (partition BY item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row) store_cumulative + max(web_sales) OVER (partition BY item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) web_cumulative , + max(store_sales) OVER (partition BY item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) store_cumulative FROM ( SELECT CASE @@ -6206,7 +6206,7 @@ WITH "date_dim_2" AS ( SELECT "web_sales"."ws_item_sk" AS "item_sk", "date_dim"."d_date" AS "d_date", - SUM(SUM("web_sales"."ws_sales_price")) OVER (PARTITION BY "web_sales"."ws_item_sk" ORDER BY "date_dim"."d_date" rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row) AS "cume_sales" + SUM(SUM("web_sales"."ws_sales_price")) OVER (PARTITION BY "web_sales"."ws_item_sk" ORDER BY "date_dim"."d_date" rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "cume_sales" FROM "web_sales" AS "web_sales" JOIN "date_dim_2" AS "date_dim" ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk" @@ -6219,7 +6219,7 @@ WITH "date_dim_2" AS ( SELECT "store_sales"."ss_item_sk" AS "item_sk", "date_dim"."d_date" AS "d_date", - SUM(SUM("store_sales"."ss_sales_price")) OVER (PARTITION BY "store_sales"."ss_item_sk" ORDER BY "date_dim"."d_date" rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row) AS "cume_sales" + SUM(SUM("store_sales"."ss_sales_price")) OVER (PARTITION BY "store_sales"."ss_item_sk" ORDER BY "date_dim"."d_date" rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "cume_sales" FROM "store_sales" AS "store_sales" JOIN "date_dim_2" AS "date_dim" ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" @@ -6242,12 +6242,12 @@ WITH "date_dim_2" AS ( WHEN NOT "web"."item_sk" IS NULL THEN "web"."item_sk" ELSE "store"."item_sk" - END ORDER BY CASE WHEN NOT "web"."d_date" IS NULL THEN "web"."d_date" ELSE "store"."d_date" END rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row) AS "web_cumulative", + END ORDER BY CASE WHEN NOT "web"."d_date" IS NULL THEN "web"."d_date" ELSE "store"."d_date" END rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "web_cumulative", MAX("store"."cume_sales") OVER (PARTITION BY CASE WHEN NOT "web"."item_sk" IS NULL THEN "web"."item_sk" ELSE "store"."item_sk" - END ORDER BY CASE WHEN NOT "web"."d_date" IS NULL THEN "web"."d_date" ELSE "store"."d_date" END rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row) AS "store_cumulative" + END ORDER BY CASE WHEN NOT "web"."d_date" IS NULL THEN "web"."d_date" ELSE "store"."d_date" END rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "store_cumulative" FROM "web_v1" AS "web" FULL JOIN "store_v1" AS "store" ON "web"."d_date" = "store"."d_date" AND "web"."item_sk" = "store"."item_sk" -- cgit v1.2.3