From f10d022e11dcd1015db1a74ce9f4198ebdcb7f40 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Mon, 16 Oct 2023 13:37:39 +0200 Subject: Merging upstream version 18.13.0. Signed-off-by: Daniel Baumann --- tests/fixtures/optimizer/optimizer.sql | 29 ++++++++ tests/fixtures/optimizer/pushdown_projections.sql | 9 +++ tests/fixtures/optimizer/simplify.sql | 86 ++++++++++++++++++++++- tests/fixtures/optimizer/tpc-ds/tpc-ds.sql | 39 ++++++---- 4 files changed, 150 insertions(+), 13 deletions(-) (limited to 'tests/fixtures') diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql index 4cc62c9..a812bc5 100644 --- a/tests/fixtures/optimizer/optimizer.sql +++ b/tests/fixtures/optimizer/optimizer.sql @@ -15,6 +15,35 @@ SELECT "q"."x" AS "x" FROM UNNEST(ARRAY(1, 2)) AS "q"("x", "y"); +# title: explode_outer +# dialect: spark +# execute: false +CREATE OR REPLACE TEMPORARY VIEW latest_boo AS +SELECT + TRIM(split(points, ':')[0]) as points_type, + TRIM(split(points, ':')[1]) as points_value +FROM ( + SELECT + explode_outer(split(object_pointsText, ',')) as points + FROM ( + SELECT + object_pointstext, + FROM boo + ) + WHERE object_pointstext IS NOT NULL + ); +CREATE OR REPLACE TEMPORARY VIEW `latest_boo` AS +SELECT + TRIM(SPLIT(`_q_1`.`points`, ':')[0]) AS `points_type`, + TRIM(SPLIT(`_q_1`.`points`, ':')[1]) AS `points_value` +FROM ( + SELECT + EXPLODE_OUTER(SPLIT(`boo`.`object_pointstext`, ',')) AS `points` + FROM `boo` AS `boo` + WHERE + NOT `boo`.`object_pointstext` IS NULL +) AS `_q_1`; + # title: Union in CTE WITH cte AS ( ( diff --git a/tests/fixtures/optimizer/pushdown_projections.sql b/tests/fixtures/optimizer/pushdown_projections.sql index 70fd9b0..8497c5b 100644 --- a/tests/fixtures/optimizer/pushdown_projections.sql +++ b/tests/fixtures/optimizer/pushdown_projections.sql @@ -70,6 +70,15 @@ WITH cte AS (SELECT 1 AS x, 3 AS z) SELECT cte.a AS a, cte.z AS z FROM cte AS ct WITH cte(x, y, z) AS (SELECT 1, 2, 3) SELECT a, z FROM (SELECT * FROM cte AS cte(b)) AS cte(a); WITH cte AS (SELECT 1 AS x, 3 AS z) SELECT cte.a AS a, cte.z AS z FROM (SELECT cte.b AS a, cte.z AS z FROM cte AS cte(b)) AS cte; +WITH y AS (SELECT a FROM x) SELECT 1 FROM y; +WITH y AS (SELECT 1 AS _ FROM x AS x) SELECT 1 AS "1" FROM y; + +WITH y AS (SELECT SUM(a) FROM x) SELECT 1 FROM y; +WITH y AS (SELECT MAX(1) AS _ FROM x AS x) SELECT 1 AS "1" FROM y; + +WITH y AS (SELECT a FROM x GROUP BY a) SELECT 1 FROM y; +WITH y AS (SELECT 1 AS _ FROM x AS x GROUP BY x.a) SELECT 1 AS "1" FROM y; + -------------------------------------- -- Unknown Star Expansion -------------------------------------- diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql index a9ae192..e54170c 100644 --- a/tests/fixtures/optimizer/simplify.sql +++ b/tests/fixtures/optimizer/simplify.sql @@ -625,7 +625,7 @@ t0.x = t1.x AND t0.y < t1.y AND t0.y <= t1.y; t0.x = t1.x AND t0.y < t1.y AND t0.y <= t1.y; -------------------------------------- --- Coalesce +-- COALESCE -------------------------------------- COALESCE(x); x; @@ -669,18 +669,45 @@ a AND b AND (ROW() OVER () = 1 OR ROW() OVER () IS NULL); CONCAT(x, y); CONCAT(x, y); +CONCAT_WS(sep, x, y); +CONCAT_WS(sep, x, y); + CONCAT(x); x; CONCAT('a', 'b', 'c'); 'abc'; +CONCAT('a', NULL); +CONCAT('a', NULL); + +CONCAT_WS('-', 'a', 'b', 'c'); +'a-b-c'; + CONCAT('a', x, y, 'b', 'c'); CONCAT('a', x, y, 'bc'); +CONCAT_WS('-', 'a', x, y, 'b', 'c'); +CONCAT_WS('-', 'a', x, y, 'b-c'); + 'a' || 'b'; 'ab'; +CONCAT_WS('-', 'a'); +'a'; + +CONCAT_WS('-', x, y); +CONCAT_WS('-', x, y); + +CONCAT_WS('', x, y); +CONCAT_WS('', x, y); + +CONCAT_WS('-', x); +CONCAT_WS('-', x); + +CONCAT_WS(sep, 'a', 'b'); +CONCAT_WS(sep, 'a', 'b'); + 'a' || 'b' || x; CONCAT('ab', x); @@ -837,3 +864,60 @@ x < CAST('2020-01-07' AS DATE); x - INTERVAL '1' day = CAST(y AS DATE); x - INTERVAL '1' day = CAST(y AS DATE); + +-------------------------------------- +-- Constant Propagation +-------------------------------------- +x = 5 AND y = x; +x = 5 AND y = 5; + +5 = x AND y = x; +y = 5 AND 5 = x; + +x = 5 OR y = x; +x = 5 OR y = x; + +(x = 5 AND y = x) OR y = 1; +(x = 5 AND y = 5) OR y = 1; + +t.x = 5 AND y = x; +t.x = 5 AND y = x; + +t.x = 'a' AND y = CONCAT_WS('-', t.x, 'b'); +t.x = 'a' AND y = 'a-b'; + +x = 5 AND y = x AND y + 1 < 5; +FALSE; + +x = 5 AND x = 6; +FALSE; + +x = 5 AND (y = x OR z = 1); +x = 5 AND (y = x OR z = 1); + +x = 5 AND x + 3 = 8; +x = 5; + +x = 5 AND (SELECT x FROM t WHERE y = 1); +x = 5 AND (SELECT x FROM t WHERE y = 1); + +x = 1 AND y > 0 AND (SELECT z = 5 FROM t WHERE y = 1); +x = 1 AND y > 0 AND (SELECT z = 5 FROM t WHERE y = 1); + +x = 1 AND x = y AND (SELECT z FROM t WHERE a AND (b OR c)); +x = 1 AND (SELECT z FROM t WHERE a AND (b OR c)) AND 1 = y; + +t1.a = 39 AND t2.b = t1.a AND t3.c = t2.b; +t1.a = 39 AND t2.b = 39 AND t3.c = 39; + +x = 1 AND CASE WHEN x = 5 THEN FALSE ELSE TRUE END; +x = 1 AND CASE WHEN FALSE THEN FALSE ELSE TRUE END; + +x = 1 AND IF(x = 5, FALSE, TRUE); +x = 1 AND CASE WHEN FALSE THEN FALSE ELSE TRUE END; + +x = y AND CASE WHEN x = 5 THEN FALSE ELSE TRUE END; +x = y AND CASE WHEN x = 5 THEN FALSE ELSE TRUE END; + +x = 1 AND CASE WHEN y = 5 THEN x = z END; +x = 1 AND CASE WHEN y = 5 THEN 1 = z END; diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql index 2218182..91b553e 100644 --- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql +++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql @@ -2029,18 +2029,33 @@ JOIN "date_dim" AS "date_dim" ON "date_dim"."d_year" = 2001 AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" JOIN "household_demographics" AS "household_demographics" - ON "customer_demographics"."cd_demo_sk" = "store_sales"."ss_cdemo_sk" - AND "customer_demographics"."cd_education_status" = 'Advanced Degree' - AND "customer_demographics"."cd_education_status" = 'Primary' - AND "customer_demographics"."cd_education_status" = 'Secondary' - AND "customer_demographics"."cd_marital_status" = 'D' - AND "customer_demographics"."cd_marital_status" = 'M' - AND "customer_demographics"."cd_marital_status" = 'U' - AND "household_demographics"."hd_dep_count" = 1 - AND "household_demographics"."hd_dep_count" = 3 - AND "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk" - AND "store_sales"."ss_sales_price" <= 100.00 - AND "store_sales"."ss_sales_price" >= 150.00 + ON ( + "customer_demographics"."cd_demo_sk" = "store_sales"."ss_cdemo_sk" + AND "customer_demographics"."cd_education_status" = 'Advanced Degree' + AND "customer_demographics"."cd_marital_status" = 'U' + AND "household_demographics"."hd_dep_count" = 3 + AND "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk" + AND "store_sales"."ss_sales_price" <= 150.00 + AND "store_sales"."ss_sales_price" >= 100.00 + ) + OR ( + "customer_demographics"."cd_demo_sk" = "store_sales"."ss_cdemo_sk" + AND "customer_demographics"."cd_education_status" = 'Primary' + AND "customer_demographics"."cd_marital_status" = 'M' + AND "household_demographics"."hd_dep_count" = 1 + AND "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk" + AND "store_sales"."ss_sales_price" <= 100.00 + AND "store_sales"."ss_sales_price" >= 50.00 + ) + OR ( + "customer_demographics"."cd_demo_sk" = "store_sales"."ss_cdemo_sk" + AND "customer_demographics"."cd_education_status" = 'Secondary' + AND "customer_demographics"."cd_marital_status" = 'D' + AND "household_demographics"."hd_dep_count" = 1 + AND "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk" + AND "store_sales"."ss_sales_price" <= 200.00 + AND "store_sales"."ss_sales_price" >= 150.00 + ) JOIN "store" AS "store" ON "store"."s_store_sk" = "store_sales"."ss_store_sk"; -- cgit v1.2.3