From 8978da3b39d7ca3cf83ee30fcc63ffe0e5453fb2 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Mon, 8 Apr 2024 10:11:50 +0200 Subject: Adding upstream version 23.7.0. Signed-off-by: Daniel Baumann --- tests/fixtures/optimizer/canonicalize.sql | 11 +- tests/fixtures/optimizer/merge_subqueries.sql | 18 +- tests/fixtures/optimizer/optimizer.sql | 19 +- tests/fixtures/optimizer/pushdown_projections.sql | 6 + tests/fixtures/optimizer/qualify_columns.sql | 14 +- tests/fixtures/optimizer/qualify_columns_ddl.sql | 15 +- tests/fixtures/optimizer/qualify_tables.sql | 1 + tests/fixtures/optimizer/simplify.sql | 99 ++++++- tests/fixtures/optimizer/tpc-ds/call_center.csv.gz | Bin 425 -> 427 bytes .../fixtures/optimizer/tpc-ds/catalog_page.csv.gz | Bin 460883 -> 393991 bytes .../optimizer/tpc-ds/catalog_returns.csv.gz | Bin 158215 -> 167258 bytes .../fixtures/optimizer/tpc-ds/catalog_sales.csv.gz | Bin 1814673 -> 639482 bytes tests/fixtures/optimizer/tpc-ds/customer.csv.gz | Bin 107573 -> 109529 bytes .../optimizer/tpc-ds/customer_address.csv.gz | Bin 28719 -> 28542 bytes .../optimizer/tpc-ds/customer_demographics.csv.gz | Bin 126715 -> 33320 bytes tests/fixtures/optimizer/tpc-ds/date_dim.csv.gz | Bin 1575448 -> 208646 bytes .../optimizer/tpc-ds/household_demographics.csv.gz | Bin 23544 -> 23432 bytes tests/fixtures/optimizer/tpc-ds/income_band.csv.gz | Bin 191 -> 194 bytes tests/fixtures/optimizer/tpc-ds/inventory.csv.gz | Bin 202661 -> 43193 bytes tests/fixtures/optimizer/tpc-ds/item.csv.gz | Bin 31336 -> 31259 bytes tests/fixtures/optimizer/tpc-ds/promotion.csv.gz | Bin 501 -> 501 bytes tests/fixtures/optimizer/tpc-ds/reason.csv.gz | Bin 83 -> 87 bytes tests/fixtures/optimizer/tpc-ds/ship_mode.csv.gz | Bin 633 -> 623 bytes tests/fixtures/optimizer/tpc-ds/store.csv.gz | Bin 397 -> 398 bytes .../fixtures/optimizer/tpc-ds/store_returns.csv.gz | Bin 255650 -> 266354 bytes tests/fixtures/optimizer/tpc-ds/store_sales.csv.gz | Bin 2436694 -> 421923 bytes tests/fixtures/optimizer/tpc-ds/time_dim.csv.gz | Bin 680588 -> 77216 bytes tests/fixtures/optimizer/tpc-ds/tpc-ds.sql | 306 +++++++++++++-------- tests/fixtures/optimizer/tpc-ds/warehouse.csv.gz | Bin 221 -> 224 bytes tests/fixtures/optimizer/tpc-ds/web_page.csv.gz | Bin 212 -> 215 bytes tests/fixtures/optimizer/tpc-ds/web_returns.csv.gz | Bin 67833 -> 71921 bytes tests/fixtures/optimizer/tpc-ds/web_sales.csv.gz | Bin 867887 -> 615903 bytes tests/fixtures/optimizer/tpc-ds/web_site.csv.gz | Bin 406 -> 409 bytes tests/fixtures/optimizer/tpc-h/tpc-h.sql | 18 +- 34 files changed, 363 insertions(+), 144 deletions(-) (limited to 'tests/fixtures/optimizer') diff --git a/tests/fixtures/optimizer/canonicalize.sql b/tests/fixtures/optimizer/canonicalize.sql index 98b2f07..e4c78b7 100644 --- a/tests/fixtures/optimizer/canonicalize.sql +++ b/tests/fixtures/optimizer/canonicalize.sql @@ -2,7 +2,7 @@ SELECT w.d + w.e AS c FROM w AS w; SELECT CONCAT("w"."d", "w"."e") AS "c" FROM "w" AS "w"; SELECT CAST(w.d AS DATE) > w.e AS a FROM w AS w; -SELECT CAST("w"."d" AS DATE) > CAST("w"."e" AS DATE) AS "a" FROM "w" AS "w"; +SELECT CAST("w"."d" AS DATE) > CAST("w"."e" AS DATETIME) AS "a" FROM "w" AS "w"; SELECT CAST(1 AS VARCHAR) AS a FROM w AS w; SELECT CAST(1 AS VARCHAR) AS "a" FROM "w" AS "w"; @@ -97,6 +97,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); +SELECT "t"."d" > '2023-01-01' AS "d" FROM "temporal" AS "t"; +SELECT "t"."d" > CAST('2023-01-01' AS DATETIME) AS "d" FROM "temporal" AS "t"; + +SELECT "t"."d" > CAST('2023-01-01' AS DATETIME) AS "d" FROM "temporal" AS "t"; +SELECT "t"."d" > CAST('2023-01-01' AS DATETIME) AS "d" FROM "temporal" AS "t"; + +SELECT "t"."t" > '2023-01-01 00:00:01' AS "t" FROM "temporal" AS "t"; +SELECT "t"."t" > CAST('2023-01-01 00:00:01' AS DATETIME) AS "t" FROM "temporal" AS "t"; + -------------------------------------- -- Remove redundant casts -------------------------------------- diff --git a/tests/fixtures/optimizer/merge_subqueries.sql b/tests/fixtures/optimizer/merge_subqueries.sql index 0f22925..f953539 100644 --- a/tests/fixtures/optimizer/merge_subqueries.sql +++ b/tests/fixtures/optimizer/merge_subqueries.sql @@ -429,4 +429,20 @@ WHERE q.a AS a FROM q AS q ); -SELECT q.a AS a FROM x AS q WHERE q.a IN (SELECT y.b AS a FROM y AS y); \ No newline at end of file +SELECT q.a AS a FROM x AS q WHERE q.a IN (SELECT y.b AS a FROM y AS y); + +# title: dont merge when inner query has ORDER BY and outer query is UNION +WITH q AS ( + SELECT + x.a AS a + FROM x + ORDER BY x.a +) +SELECT + q.a AS a +FROM q +UNION ALL +SELECT + 1 AS a; +WITH q AS (SELECT x.a AS a FROM x AS x ORDER BY x.a) SELECT q.a AS a FROM q AS q UNION ALL SELECT 1 AS a; + diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql index 990453b..cc72e6d 100644 --- a/tests/fixtures/optimizer/optimizer.sql +++ b/tests/fixtures/optimizer/optimizer.sql @@ -33,16 +33,17 @@ FROM ( 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 ( +WITH `_q_1` AS ( SELECT EXPLODE_OUTER(SPLIT(`boo`.`object_pointstext`, ',')) AS `points` FROM `boo` AS `boo` WHERE NOT `boo`.`object_pointstext` IS NULL -) AS `_q_1`; +) +SELECT + TRIM(SPLIT(`_q_1`.`points`, ':')[0]) AS `points_type`, + TRIM(SPLIT(`_q_1`.`points`, ':')[1]) AS `points_value` +FROM `_q_1` AS `_q_1`; # title: Union in CTE WITH cte AS ( @@ -480,8 +481,8 @@ JOIN "company_table" AS "company_table_2" LEFT JOIN "unlocked" AS "unlocked" ON "company_table_2"."id" = "unlocked"."company_id" WHERE - NOT "company_table_2"."id" IS NULL - AND CASE WHEN "unlocked"."company_id" IS NULL THEN 0 ELSE 1 END = FALSE; + CASE WHEN "unlocked"."company_id" IS NULL THEN 0 ELSE 1 END = FALSE + AND NOT "company_table_2"."id" IS NULL; # title: db.table alias clash # execute: false @@ -823,7 +824,7 @@ SELECT FROM `bigquery-public-data.GooGle_tReNDs.TOp_TeRmS` AS `TOp_TeRmS` WHERE `TOp_TeRmS`.`rank` = 1 - AND CAST(`TOp_TeRmS`.`refresh_date` AS DATE) >= DATE_SUB(CURRENT_DATE, INTERVAL 2 WEEK) + AND `TOp_TeRmS`.`refresh_date` >= DATE_SUB(CURRENT_DATE, INTERVAL 2 WEEK) GROUP BY `day`, `top_term`, @@ -1379,11 +1380,11 @@ JOIN `date_dim` AS `date_dim` AND `date_dim`.`d_date` >= '2002-02-01' WHERE `_u_3`.`_u_4` IS NULL - AND NOT `_u_0`.`_u_1` IS NULL AND ( SIZE(`_u_0`.`_u_2`) = 0 OR SIZE(FILTER(`_u_0`.`_u_2`, `_x` -> `cs1`.`cs_warehouse_sk` <> `_x`)) <> 0 ) + AND NOT `_u_0`.`_u_1` IS NULL ORDER BY COUNT(DISTINCT `cs1`.`cs_order_number`) LIMIT 100; diff --git a/tests/fixtures/optimizer/pushdown_projections.sql b/tests/fixtures/optimizer/pushdown_projections.sql index b7103ef..47972ac 100644 --- a/tests/fixtures/optimizer/pushdown_projections.sql +++ b/tests/fixtures/optimizer/pushdown_projections.sql @@ -79,6 +79,9 @@ WITH y AS (SELECT MAX(1) AS _ FROM x AS x) SELECT 1 AS "1" FROM y AS 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 AS y; +WITH cte AS (SELECT col FROM t) SELECT IF(1 IN UNNEST(col), 1, 0) AS col FROM cte; +WITH cte AS (SELECT t.col AS col FROM t AS t) SELECT CASE WHEN 1 IN (SELECT UNNEST(cte.col)) THEN 1 ELSE 0 END AS col FROM cte AS cte; + -------------------------------------- -- Unknown Star Expansion -------------------------------------- @@ -106,3 +109,6 @@ WITH cte1 AS (SELECT tb.cola AS cola FROM tb AS tb UNION ALL SELECT tb2.colc AS SELECT * FROM ((SELECT c FROM t1) JOIN t2); SELECT * FROM ((SELECT t1.c AS c FROM t1 AS t1) AS _q_0, t2 AS t2); + +SELECT a, d FROM (SELECT 1 a, 2 c, 3 d, 4 e UNION ALL BY NAME SELECT 5 b, 6 c, 7 d, 8 a, 9 e) +SELECT a, d FROM (SELECT 1 a, 3 d, UNION ALL BY NAME SELECT 7 d, 8 a) diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql index 71c6f45..289145b 100644 --- a/tests/fixtures/optimizer/qualify_columns.sql +++ b/tests/fixtures/optimizer/qualify_columns.sql @@ -96,6 +96,12 @@ SELECT 2 AS "2" FROM x AS x GROUP BY 1; SELECT 'a' AS a FROM x GROUP BY 1; SELECT 'a' AS a FROM x AS x GROUP BY 1; +SELECT NULL AS a FROM x GROUP BY 1; +SELECT NULL AS a FROM x AS x GROUP BY 1; + +SELECT TRUE AS a FROM x GROUP BY 1; +SELECT TRUE AS a FROM x AS x GROUP BY 1; + # execute: false # dialect: oracle SELECT t."col" FROM tbl t; @@ -121,6 +127,10 @@ SELECT 2 AS d FROM x AS x GROUP BY 1 ORDER BY d; SELECT DATE(a), DATE(b) AS c FROM x GROUP BY 1, 2; SELECT DATE(x.a) AS _col_0, DATE(x.b) AS c FROM x AS x GROUP BY DATE(x.a), DATE(x.b); +# execute: false +SELECT (SELECT MIN(a) FROM UNNEST([1, 2])) AS f FROM x GROUP BY 1; +SELECT (SELECT MIN(_q_0.a) AS _col_0 FROM UNNEST(ARRAY(1, 2)) AS _q_0) AS f FROM x AS x GROUP BY 1; + SELECT SUM(x.a) AS c FROM x JOIN y ON x.b = y.b GROUP BY c; SELECT SUM(x.a) AS c FROM x AS x JOIN y AS y ON x.b = y.b GROUP BY y.c; @@ -580,8 +590,8 @@ SELECT * FROM ((SELECT * FROM tbl)); SELECT * FROM ((SELECT * FROM tbl AS tbl) AS _q_0); # execute: false -SELECT * FROM ((SELECT c FROM t1) JOIN t2); -SELECT * FROM ((SELECT t1.c AS c FROM t1 AS t1) AS _q_0, t2 AS t2); +SELECT * FROM ((SELECT c FROM t1) CROSS JOIN t2); +SELECT * FROM ((SELECT t1.c AS c FROM t1 AS t1) AS _q_0 CROSS JOIN t2 AS t2); # execute: false SELECT * FROM ((SELECT * FROM x) INNER JOIN y ON a = c); diff --git a/tests/fixtures/optimizer/qualify_columns_ddl.sql b/tests/fixtures/optimizer/qualify_columns_ddl.sql index 907780b..9b4bb34 100644 --- a/tests/fixtures/optimizer/qualify_columns_ddl.sql +++ b/tests/fixtures/optimizer/qualify_columns_ddl.sql @@ -1,6 +1,10 @@ # title: Create with CTE WITH cte AS (SELECT b FROM y) CREATE TABLE s AS SELECT * FROM cte; -WITH cte AS (SELECT y.b AS b FROM y AS y) CREATE TABLE s AS SELECT cte.b AS b FROM cte AS cte; +CREATE TABLE s AS WITH cte AS (SELECT y.b AS b FROM y AS y) SELECT cte.b AS b FROM cte AS cte; + +# title: Create with CTE, query also has CTE +WITH cte1 AS (SELECT b FROM y) CREATE TABLE s AS WITH cte2 AS (SELECT b FROM cte1) SELECT * FROM cte2; +CREATE TABLE s AS WITH cte1 AS (SELECT y.b AS b FROM y AS y), cte2 AS (SELECT cte1.b AS b FROM cte1 AS cte1) SELECT cte2.b AS b FROM cte2 AS cte2; # title: Create without CTE CREATE TABLE foo AS SELECT a FROM tbl; @@ -8,15 +12,15 @@ CREATE TABLE foo AS SELECT tbl.a AS a FROM tbl AS tbl; # title: Create with complex CTE with derived table WITH cte AS (SELECT a FROM (SELECT a from x)) CREATE TABLE s AS SELECT * FROM cte; -WITH cte AS (SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0) CREATE TABLE s AS SELECT cte.a AS a FROM cte AS cte; +CREATE TABLE s AS WITH cte AS (SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0) SELECT cte.a AS a FROM cte AS cte; # title: Create wtih multiple CTEs WITH cte1 AS (SELECT b FROM y), cte2 AS (SELECT b FROM cte1) CREATE TABLE s AS SELECT * FROM cte2; -WITH cte1 AS (SELECT y.b AS b FROM y AS y), cte2 AS (SELECT cte1.b AS b FROM cte1 AS cte1) CREATE TABLE s AS SELECT cte2.b AS b FROM cte2 AS cte2; +CREATE TABLE s AS WITH cte1 AS (SELECT y.b AS b FROM y AS y), cte2 AS (SELECT cte1.b AS b FROM cte1 AS cte1) SELECT cte2.b AS b FROM cte2 AS cte2; # title: Create with multiple CTEs, selecting only from the first CTE (unnecessary code) WITH cte1 AS (SELECT b FROM y), cte2 AS (SELECT b FROM cte1) CREATE TABLE s AS SELECT * FROM cte1; -WITH cte1 AS (SELECT y.b AS b FROM y AS y), cte2 AS (SELECT cte1.b AS b FROM cte1 AS cte1) CREATE TABLE s AS SELECT cte1.b AS b FROM cte1 AS cte1; +CREATE TABLE s AS WITH cte1 AS (SELECT y.b AS b FROM y AS y), cte2 AS (SELECT cte1.b AS b FROM cte1 AS cte1) SELECT cte1.b AS b FROM cte1 AS cte1; # title: Create with multiple derived tables CREATE TABLE s AS SELECT * FROM (SELECT b FROM (SELECT b FROM y)); @@ -24,9 +28,10 @@ CREATE TABLE s AS SELECT _q_1.b AS b FROM (SELECT _q_0.b AS b FROM (SELECT y.b A # title: Create with a CTE and a derived table WITH cte AS (SELECT b FROM y) CREATE TABLE s AS SELECT * FROM (SELECT b FROM (SELECT b FROM cte)); -WITH cte AS (SELECT y.b AS b FROM y AS y) CREATE TABLE s AS SELECT _q_1.b AS b FROM (SELECT _q_0.b AS b FROM (SELECT cte.b AS b FROM cte AS cte) AS _q_0) AS _q_1; +CREATE TABLE s AS WITH cte AS (SELECT y.b AS b FROM y AS y) SELECT _q_1.b AS b FROM (SELECT _q_0.b AS b FROM (SELECT cte.b AS b FROM cte AS cte) AS _q_0) AS _q_1; # title: Insert with CTE +# dialect: spark WITH cte AS (SELECT b FROM y) INSERT INTO s SELECT * FROM cte; WITH cte AS (SELECT y.b AS b FROM y AS y) INSERT INTO s SELECT cte.b AS b FROM cte AS cte; diff --git a/tests/fixtures/optimizer/qualify_tables.sql b/tests/fixtures/optimizer/qualify_tables.sql index 99b5153..f651a87 100644 --- a/tests/fixtures/optimizer/qualify_tables.sql +++ b/tests/fixtures/optimizer/qualify_tables.sql @@ -159,6 +159,7 @@ CREATE TABLE t1 AS (WITH cte AS (SELECT x FROM t2) SELECT * FROM cte); CREATE TABLE c.db.t1 AS (WITH cte AS (SELECT x FROM c.db.t2 AS t2) SELECT * FROM cte AS cte); # title: insert statement with cte +# dialect: spark WITH cte AS (SELECT b FROM y) INSERT INTO s SELECT * FROM cte; WITH cte AS (SELECT b FROM c.db.y AS y) INSERT INTO c.db.s SELECT * FROM cte AS cte; diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql index da9f26d..a10942d 100644 --- a/tests/fixtures/optimizer/simplify.sql +++ b/tests/fixtures/optimizer/simplify.sql @@ -52,6 +52,9 @@ FALSE; 'x' = 'x'; TRUE; +STRUCT(NULL AS a); +STRUCT(NULL AS a); + NULL AND TRUE; NULL; @@ -102,6 +105,10 @@ a AND b; a AND (b AND b); a AND b; +-- bigquery doesn't allow unparenthesis comparisons +(x is not null) != (y is null); +(NOT x IS NULL) <> (y IS NULL); + -------------------------------------- -- Absorption -------------------------------------- @@ -459,6 +466,18 @@ CAST('1998-09-02 00:00:00' AS DATETIME); CAST(x AS DATETIME) + interval '1' WEEK; CAST(x AS DATETIME) + INTERVAL '1' WEEK; +# dialect: bigquery +CAST('2023-01-01' AS TIMESTAMP) + INTERVAL 1 DAY; +CAST('2023-01-02 00:00:00' AS TIMESTAMP); + +# dialect: bigquery +INTERVAL 1 DAY + CAST('2023-01-01' AS TIMESTAMP); +CAST('2023-01-02 00:00:00' AS TIMESTAMP); + +# dialect: bigquery +CAST('2023-01-02' AS TIMESTAMP) - INTERVAL 1 DAY; +CAST('2023-01-01 00:00:00' AS TIMESTAMP); + TS_OR_DS_TO_DATE('1998-12-01 00:00:01') - interval '90' day; CAST('1998-09-02' AS DATE); @@ -708,6 +727,48 @@ FUN() > 0; RAND() > 0 OR RAND() > 1; RAND() > 0 OR RAND() > 1; +CAST(1 AS UINT) >= 0; +TRUE; + +CAST(-1 AS TINYINT) <= 0; +TRUE; + +CAST(1 AS INT) = CAST(1 AS UINT); +TRUE; + +CASE WHEN CAST(1 AS TINYINT) = 1 THEN FALSE ELSE TRUE END; +FALSE; + +CAST(1 AS INT) + 1; +CAST(1 AS INT) + 1; + +CAST(CAST(CAST(-1 AS INT) AS INT) AS INT) = -1; +TRUE; + +CAST(-1 AS UINT) <= 0; +CAST(-1 AS UINT) <= 0; + +CAST(-129 AS TINYINT) <= 0; +CAST(-129 AS TINYINT) <= 0; + +CAST(256 AS UINT) >= 0; +CAST(256 AS UINT) >= 0; + +CAST(CAST(CAST(-1 AS INT) AS UINT) AS INT) = 1; +CAST(CAST(CAST(-1 AS INT) AS UINT) AS INT) = 1; + +CAST(x AS TINYINT) = 1; +CAST(x AS TINYINT) = 1; + +CAST(CAST(1 AS INT) AS BOOLEAN) = 1; +CAST(CAST(1 AS INT) AS BOOLEAN) = 1; + +CAST(CAST(CAST(1 AS INT) AS BOOLEAN) AS INT) = 1; +CAST(CAST(CAST(1 AS INT) AS BOOLEAN) AS INT) = 1; + +x > CAST('2023-01-01' AS DATE) AND x < CAST('2023-01-01' AS DATETIME); +FALSE; + -------------------------------------- -- COALESCE -------------------------------------- @@ -745,7 +806,7 @@ COALESCE(ROW() OVER (), 1) = 1; ROW() OVER () = 1 OR ROW() OVER () IS NULL; a AND b AND COALESCE(ROW() OVER (), 1) = 1; -a AND b AND (ROW() OVER () = 1 OR ROW() OVER () IS NULL); +(ROW() OVER () = 1 OR ROW() OVER () IS NULL) AND a AND b; COALESCE(1, 2); 1; @@ -823,6 +884,10 @@ CAST('2023-12-11' AS DATE); DATE_TRUNC(CAST('2023-12-15' AS DATE), WEEK); CAST('2023-12-10' AS DATE); +# dialect: bigquery +DATE_TRUNC(CAST('2023-10-01' AS TIMESTAMP), QUARTER); +CAST('2023-10-01 00:00:00' AS TIMESTAMP); + # dialect: bigquery DATE_TRUNC(CAST('2023-12-16' AS DATE), WEEK); CAST('2023-12-10' AS DATE); @@ -830,21 +895,41 @@ CAST('2023-12-10' AS DATE); DATE_TRUNC('year', x) = CAST('2021-01-01' AS DATE); x < CAST('2022-01-01' AS DATE) AND x >= CAST('2021-01-01' AS DATE); +# dialect: bigquery +DATE_TRUNC(x, year) = CAST('2021-01-01' AS TIMESTAMP); +x < CAST('2022-01-01 00:00:00' AS TIMESTAMP) AND x >= CAST('2021-01-01 00:00:00' AS TIMESTAMP); + DATE_TRUNC('quarter', x) = CAST('2021-01-01' AS DATE); x < CAST('2021-04-01' AS DATE) AND x >= CAST('2021-01-01' AS DATE); +# dialect: bigquery +DATE_TRUNC(x, quarter) = CAST('2021-01-01' AS TIMESTAMP); +x < CAST('2021-04-01 00:00:00' AS TIMESTAMP) AND x >= CAST('2021-01-01 00:00:00' AS TIMESTAMP); + DATE_TRUNC('month', x) = CAST('2021-01-01' AS DATE); x < CAST('2021-02-01' AS DATE) AND x >= CAST('2021-01-01' AS DATE); +# dialect: bigquery +DATE_TRUNC(x, month) = CAST('2021-01-01' AS TIMESTAMP); +x < CAST('2021-02-01 00:00:00' AS TIMESTAMP) AND x >= CAST('2021-01-01 00:00:00' AS TIMESTAMP); + DATE_TRUNC('week', x) = CAST('2021-01-04' AS DATE); x < CAST('2021-01-11' AS DATE) AND x >= CAST('2021-01-04' AS DATE); DATE_TRUNC('day', x) = CAST('2021-01-01' AS DATE); x < CAST('2021-01-02' AS DATE) AND x >= CAST('2021-01-01' AS DATE); +# dialect: bigquery +DATE_TRUNC(x, DAY) = CAST('2021-01-01' AS TIMESTAMP); +x < CAST('2021-01-02 00:00:00' AS TIMESTAMP) AND x >= CAST('2021-01-01 00:00:00' AS TIMESTAMP); + CAST('2021-01-01' AS DATE) = DATE_TRUNC('year', x); x < CAST('2022-01-01' AS DATE) AND x >= CAST('2021-01-01' AS DATE); +# dialect: bigquery +CAST('2021-01-01' AS TIMESTAMP) = DATE_TRUNC(x, year); +x < CAST('2022-01-01 00:00:00' AS TIMESTAMP) AND x >= CAST('2021-01-01 00:00:00' AS TIMESTAMP); + -- Always false, except for nulls DATE_TRUNC('quarter', x) = CAST('2021-01-02' AS DATE); DATE_TRUNC('QUARTER', x) = CAST('2021-01-02' AS DATE); @@ -859,12 +944,20 @@ DATE_TRUNC('YEAR', x) <> CAST('2021-01-02' AS DATE); DATE_TRUNC('year', x) <= CAST('2021-01-01' AS DATE); x < CAST('2022-01-01' AS DATE); +# dialect: bigquery +DATE_TRUNC(x, year) <= CAST('2021-01-01' AS TIMESTAMP); +x < CAST('2022-01-01 00:00:00' AS TIMESTAMP); + DATE_TRUNC('year', x) <= CAST('2021-01-02' AS DATE); x < CAST('2022-01-01' AS DATE); CAST('2021-01-01' AS DATE) >= DATE_TRUNC('year', x); x < CAST('2022-01-01' AS DATE); +# dialect: bigquery +CAST('2021-01-01' AS TIMESTAMP) >= DATE_TRUNC(x, year); +x < CAST('2022-01-01 00:00:00' AS TIMESTAMP); + DATE_TRUNC('year', x) < CAST('2021-01-01' AS DATE); x < CAST('2021-01-01' AS DATE); @@ -896,6 +989,10 @@ DATE_TRUNC('YEAR', x) <> '2021-01-02'; DATE_TRUNC('year', x) IN (CAST('2021-01-01' AS DATE), CAST('2023-01-01' AS DATE)); (x < CAST('2022-01-01' AS DATE) AND x >= CAST('2021-01-01' AS DATE)) OR (x < CAST('2024-01-01' AS DATE) AND x >= CAST('2023-01-01' AS DATE)); +# dialect: bigquery +DATE_TRUNC(x, year) IN (CAST('2021-01-01' AS TIMESTAMP), CAST('2023-01-01' AS TIMESTAMP)); +(x < CAST('2022-01-01 00:00:00' AS TIMESTAMP) AND x >= CAST('2021-01-01 00:00:00' AS TIMESTAMP)) OR (x < CAST('2024-01-01 00:00:00' AS TIMESTAMP) AND x >= CAST('2023-01-01 00:00:00' AS TIMESTAMP)); + -- merge ranges DATE_TRUNC('year', x) IN (CAST('2021-01-01' AS DATE), CAST('2022-01-01' AS DATE)); x < CAST('2023-01-01' AS DATE) AND x >= CAST('2021-01-01' AS DATE); diff --git a/tests/fixtures/optimizer/tpc-ds/call_center.csv.gz b/tests/fixtures/optimizer/tpc-ds/call_center.csv.gz index ad5043f..6f51952 100644 Binary files a/tests/fixtures/optimizer/tpc-ds/call_center.csv.gz and b/tests/fixtures/optimizer/tpc-ds/call_center.csv.gz differ diff --git a/tests/fixtures/optimizer/tpc-ds/catalog_page.csv.gz b/tests/fixtures/optimizer/tpc-ds/catalog_page.csv.gz index eed1508..9a736ff 100644 Binary files a/tests/fixtures/optimizer/tpc-ds/catalog_page.csv.gz and b/tests/fixtures/optimizer/tpc-ds/catalog_page.csv.gz differ diff --git a/tests/fixtures/optimizer/tpc-ds/catalog_returns.csv.gz b/tests/fixtures/optimizer/tpc-ds/catalog_returns.csv.gz index e160514..9092c1f 100644 Binary files a/tests/fixtures/optimizer/tpc-ds/catalog_returns.csv.gz and b/tests/fixtures/optimizer/tpc-ds/catalog_returns.csv.gz differ diff --git a/tests/fixtures/optimizer/tpc-ds/catalog_sales.csv.gz b/tests/fixtures/optimizer/tpc-ds/catalog_sales.csv.gz index 1828149..ac9058b 100644 Binary files a/tests/fixtures/optimizer/tpc-ds/catalog_sales.csv.gz and b/tests/fixtures/optimizer/tpc-ds/catalog_sales.csv.gz differ diff --git a/tests/fixtures/optimizer/tpc-ds/customer.csv.gz b/tests/fixtures/optimizer/tpc-ds/customer.csv.gz index 2277f72..5545923 100644 Binary files a/tests/fixtures/optimizer/tpc-ds/customer.csv.gz and b/tests/fixtures/optimizer/tpc-ds/customer.csv.gz differ diff --git a/tests/fixtures/optimizer/tpc-ds/customer_address.csv.gz b/tests/fixtures/optimizer/tpc-ds/customer_address.csv.gz index c553721..7c24e8c 100644 Binary files a/tests/fixtures/optimizer/tpc-ds/customer_address.csv.gz and b/tests/fixtures/optimizer/tpc-ds/customer_address.csv.gz differ diff --git a/tests/fixtures/optimizer/tpc-ds/customer_demographics.csv.gz b/tests/fixtures/optimizer/tpc-ds/customer_demographics.csv.gz index dfc65a0..582d4e5 100644 Binary files a/tests/fixtures/optimizer/tpc-ds/customer_demographics.csv.gz and b/tests/fixtures/optimizer/tpc-ds/customer_demographics.csv.gz differ diff --git a/tests/fixtures/optimizer/tpc-ds/date_dim.csv.gz b/tests/fixtures/optimizer/tpc-ds/date_dim.csv.gz index 26280bf..9960663 100644 Binary files a/tests/fixtures/optimizer/tpc-ds/date_dim.csv.gz and b/tests/fixtures/optimizer/tpc-ds/date_dim.csv.gz differ diff --git a/tests/fixtures/optimizer/tpc-ds/household_demographics.csv.gz b/tests/fixtures/optimizer/tpc-ds/household_demographics.csv.gz index f0cde03..84efa06 100644 Binary files a/tests/fixtures/optimizer/tpc-ds/household_demographics.csv.gz and b/tests/fixtures/optimizer/tpc-ds/household_demographics.csv.gz differ diff --git a/tests/fixtures/optimizer/tpc-ds/income_band.csv.gz b/tests/fixtures/optimizer/tpc-ds/income_band.csv.gz index 4374587..8c60109 100644 Binary files a/tests/fixtures/optimizer/tpc-ds/income_band.csv.gz and b/tests/fixtures/optimizer/tpc-ds/income_band.csv.gz differ diff --git a/tests/fixtures/optimizer/tpc-ds/inventory.csv.gz b/tests/fixtures/optimizer/tpc-ds/inventory.csv.gz index 5afaaf6..d171ae0 100644 Binary files a/tests/fixtures/optimizer/tpc-ds/inventory.csv.gz and b/tests/fixtures/optimizer/tpc-ds/inventory.csv.gz differ diff --git a/tests/fixtures/optimizer/tpc-ds/item.csv.gz b/tests/fixtures/optimizer/tpc-ds/item.csv.gz index 9f65d87..effacb3 100644 Binary files a/tests/fixtures/optimizer/tpc-ds/item.csv.gz and b/tests/fixtures/optimizer/tpc-ds/item.csv.gz differ diff --git a/tests/fixtures/optimizer/tpc-ds/promotion.csv.gz b/tests/fixtures/optimizer/tpc-ds/promotion.csv.gz index e8692c2..918e9c1 100644 Binary files a/tests/fixtures/optimizer/tpc-ds/promotion.csv.gz and b/tests/fixtures/optimizer/tpc-ds/promotion.csv.gz differ diff --git a/tests/fixtures/optimizer/tpc-ds/reason.csv.gz b/tests/fixtures/optimizer/tpc-ds/reason.csv.gz index de1f50f..2ad5473 100644 Binary files a/tests/fixtures/optimizer/tpc-ds/reason.csv.gz and b/tests/fixtures/optimizer/tpc-ds/reason.csv.gz differ diff --git a/tests/fixtures/optimizer/tpc-ds/ship_mode.csv.gz b/tests/fixtures/optimizer/tpc-ds/ship_mode.csv.gz index 14465e8..e193902 100644 Binary files a/tests/fixtures/optimizer/tpc-ds/ship_mode.csv.gz and b/tests/fixtures/optimizer/tpc-ds/ship_mode.csv.gz differ diff --git a/tests/fixtures/optimizer/tpc-ds/store.csv.gz b/tests/fixtures/optimizer/tpc-ds/store.csv.gz index 8d04078..77868fc 100644 Binary files a/tests/fixtures/optimizer/tpc-ds/store.csv.gz and b/tests/fixtures/optimizer/tpc-ds/store.csv.gz differ diff --git a/tests/fixtures/optimizer/tpc-ds/store_returns.csv.gz b/tests/fixtures/optimizer/tpc-ds/store_returns.csv.gz index cba1300..d3426ab 100644 Binary files a/tests/fixtures/optimizer/tpc-ds/store_returns.csv.gz and b/tests/fixtures/optimizer/tpc-ds/store_returns.csv.gz differ diff --git a/tests/fixtures/optimizer/tpc-ds/store_sales.csv.gz b/tests/fixtures/optimizer/tpc-ds/store_sales.csv.gz index 68caa83..21e83df 100644 Binary files a/tests/fixtures/optimizer/tpc-ds/store_sales.csv.gz and b/tests/fixtures/optimizer/tpc-ds/store_sales.csv.gz differ diff --git a/tests/fixtures/optimizer/tpc-ds/time_dim.csv.gz b/tests/fixtures/optimizer/tpc-ds/time_dim.csv.gz index 3e0fa35..fae30e9 100644 Binary files a/tests/fixtures/optimizer/tpc-ds/time_dim.csv.gz and b/tests/fixtures/optimizer/tpc-ds/time_dim.csv.gz differ diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql index 76e6431..35fbb70 100644 --- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql +++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql @@ -62,6 +62,7 @@ LIMIT 100; -------------------------------------- -- TPC-DS 2 -------------------------------------- +# execute: true WITH wscs AS (SELECT sold_date_sk, sales_price @@ -107,13 +108,13 @@ WITH wscs WHERE d_date_sk = sold_date_sk GROUP BY d_week_seq) SELECT d_week_seq1, - Round(sun_sales1 / sun_sales2, 2), - Round(mon_sales1 / mon_sales2, 2), - Round(tue_sales1 / tue_sales2, 2), - Round(wed_sales1 / wed_sales2, 2), - Round(thu_sales1 / thu_sales2, 2), - Round(fri_sales1 / fri_sales2, 2), - Round(sat_sales1 / sat_sales2, 2) + Round(sun_sales1 / sun_sales2, 2) AS "_col_1", + Round(mon_sales1 / mon_sales2, 2) AS "_col_2", + Round(tue_sales1 / tue_sales2, 2) AS "_col_3", + Round(wed_sales1 / wed_sales2, 2) AS "_col_4", + Round(thu_sales1 / thu_sales2, 2) AS "_col_5", + Round(fri_sales1 / fri_sales2, 2) AS "_col_6", + Round(sat_sales1 / sat_sales2, 2) AS "_col_7" FROM (SELECT wswscs.d_week_seq d_week_seq1, sun_sales sun_sales1, mon_sales mon_sales1, @@ -213,7 +214,8 @@ JOIN "date_dim" AS "date_dim" JOIN "wswscs" AS "wswscs_2" ON "wswscs"."d_week_seq" = "wswscs_2"."d_week_seq" - 53 JOIN "date_dim" AS "date_dim_2" - ON "date_dim_2"."d_week_seq" = "wswscs_2"."d_week_seq" AND "date_dim_2"."d_year" = 1999 + ON "date_dim_2"."d_week_seq" = "wswscs_2"."d_week_seq" + AND "date_dim_2"."d_year" = 1999 ORDER BY "d_week_seq1"; @@ -264,6 +266,7 @@ LIMIT 100; -------------------------------------- -- TPC-DS 4 -------------------------------------- +# execute: true WITH year_total AS (SELECT c_customer_id customer_id, c_first_name customer_first_name, @@ -733,8 +736,8 @@ WITH "salesreturns" AS ( "date_dim"."d_date" AS "d_date" FROM "date_dim" AS "date_dim" WHERE - CAST("date_dim"."d_date" AS DATE) <= CAST('2002-09-05' AS DATE) - AND CAST("date_dim"."d_date" AS DATE) >= CAST('2002-08-22' AS DATE) + CAST("date_dim"."d_date" AS DATETIME) <= CAST('2002-09-05' AS DATE) + AND CAST("date_dim"."d_date" AS DATETIME) >= CAST('2002-08-22' AS DATE) ), "ssr" AS ( SELECT "store"."s_store_id" AS "s_store_id", @@ -1628,6 +1631,7 @@ LIMIT 100; -------------------------------------- -- TPC-DS 11 -------------------------------------- +# execute: true WITH year_total AS (SELECT c_customer_id customer_id, c_first_name customer_first_name @@ -1869,8 +1873,8 @@ SELECT FROM "web_sales" AS "web_sales" JOIN "date_dim" AS "date_dim" ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk" - AND CAST("date_dim"."d_date" AS DATE) <= CAST('2000-06-10' AS DATE) - AND CAST("date_dim"."d_date" AS DATE) >= CAST('2000-05-11' AS DATE) + AND CAST("date_dim"."d_date" AS DATETIME) <= CAST('2000-06-10' AS DATE) + AND CAST("date_dim"."d_date" AS DATETIME) >= CAST('2000-05-11' AS DATE) JOIN "item" AS "item" ON "item"."i_category" IN ('Home', 'Men', 'Women') AND "item"."i_item_sk" = "web_sales"."ws_item_sk" @@ -2326,8 +2330,9 @@ LIMIT 100; -------------------------------------- -- TPC-DS 15 -------------------------------------- +# execute: true SELECT ca_zip, - Sum(cs_sales_price) + Sum(cs_sales_price) AS "_col_1" FROM catalog_sales, customer, customer_address, @@ -2437,11 +2442,11 @@ JOIN "date_dim" AS "date_dim" AND "date_dim"."d_date" >= '2002-3-01' AND ( CAST('2002-3-01' AS DATE) + INTERVAL '60' DAY - ) >= CAST("date_dim"."d_date" AS DATE) + ) >= CAST("date_dim"."d_date" AS DATETIME) WHERE "_u_3"."_u_4" IS NULL - AND NOT "_u_0"."_u_1" IS NULL AND ARRAY_ANY("_u_0"."_u_2", "_x" -> "cs1"."cs_warehouse_sk" <> "_x") + AND NOT "_u_0"."_u_1" IS NULL ORDER BY COUNT(DISTINCT "cs1"."cs_order_number") LIMIT 100; @@ -2449,6 +2454,7 @@ LIMIT 100; -------------------------------------- -- TPC-DS 17 -------------------------------------- +# execute: true SELECT i_item_id, i_item_desc, s_state, @@ -2638,6 +2644,7 @@ LIMIT 100; -------------------------------------- -- TPC-DS 19 -------------------------------------- +# execute: true SELECT i_brand_id brand_id, i_brand brand, i_manufact_id, @@ -2744,8 +2751,8 @@ SELECT FROM "catalog_sales" AS "catalog_sales" JOIN "date_dim" AS "date_dim" ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk" - AND CAST("date_dim"."d_date" AS DATE) <= CAST('2001-03-05' AS DATE) - AND CAST("date_dim"."d_date" AS DATE) >= CAST('2001-02-03' AS DATE) + AND CAST("date_dim"."d_date" AS DATETIME) <= CAST('2001-03-05' AS DATE) + AND CAST("date_dim"."d_date" AS DATETIME) >= CAST('2001-02-03' AS DATE) JOIN "item" AS "item" ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk" AND "item"."i_category" IN ('Children', 'Women', 'Electronics') @@ -2824,8 +2831,8 @@ WITH "x" AS ( FROM "inventory" AS "inventory" JOIN "date_dim" AS "date_dim" ON "date_dim"."d_date_sk" = "inventory"."inv_date_sk" - AND CAST("date_dim"."d_date" AS DATE) <= CAST('2000-06-12' AS DATE) - AND CAST("date_dim"."d_date" AS DATE) >= CAST('2000-04-13' AS DATE) + AND CAST("date_dim"."d_date" AS DATETIME) <= CAST('2000-06-12' AS DATE) + AND CAST("date_dim"."d_date" AS DATETIME) >= CAST('2000-04-13' AS DATE) JOIN "item" AS "item" ON "inventory"."inv_item_sk" = "item"."i_item_sk" AND "item"."i_current_price" <= 1.49 @@ -2906,6 +2913,7 @@ LIMIT 100; -------------------------------------- -- TPC-DS 23 -------------------------------------- +# execute: true WITH frequent_ss_items AS (SELECT Substr(i_item_desc, 1, 30) itemdesc, i_item_sk item_sk, @@ -2942,7 +2950,7 @@ WITH frequent_ss_items HAVING Sum(ss_quantity * ss_sales_price) > ( 95 / 100.0 ) * (SELECT * FROM max_store_sales)) -SELECT Sum(sales) +SELECT Sum(sales) AS "_col_0" FROM (SELECT cs_quantity * cs_list_price sales FROM catalog_sales, date_dim @@ -3372,49 +3380,49 @@ LIMIT 100; -- TPC-DS 28 -------------------------------------- SELECT * -FROM (SELECT Avg(ss_list_price) B1_LP, - Count(ss_list_price) B1_CNT, - Count(DISTINCT ss_list_price) B1_CNTD +FROM (SELECT Avg(ss_list_price) b1_lp, + Count(ss_list_price) b1_cnt, + Count(DISTINCT ss_list_price) b1_cntd FROM store_sales WHERE ss_quantity BETWEEN 0 AND 5 AND ( ss_list_price BETWEEN 18 AND 18 + 10 OR ss_coupon_amt BETWEEN 1939 AND 1939 + 1000 OR ss_wholesale_cost BETWEEN 34 AND 34 + 20 )) B1, - (SELECT Avg(ss_list_price) B2_LP, - Count(ss_list_price) B2_CNT, - Count(DISTINCT ss_list_price) B2_CNTD + (SELECT Avg(ss_list_price) b2_lp, + Count(ss_list_price) b2_cnt, + Count(DISTINCT ss_list_price) b2_cntd FROM store_sales WHERE ss_quantity BETWEEN 6 AND 10 AND ( ss_list_price BETWEEN 1 AND 1 + 10 OR ss_coupon_amt BETWEEN 35 AND 35 + 1000 OR ss_wholesale_cost BETWEEN 50 AND 50 + 20 )) B2, - (SELECT Avg(ss_list_price) B3_LP, - Count(ss_list_price) B3_CNT, - Count(DISTINCT ss_list_price) B3_CNTD + (SELECT Avg(ss_list_price) b3_lp, + Count(ss_list_price) b3_cnt, + Count(DISTINCT ss_list_price) b3_cntd FROM store_sales WHERE ss_quantity BETWEEN 11 AND 15 AND ( ss_list_price BETWEEN 91 AND 91 + 10 OR ss_coupon_amt BETWEEN 1412 AND 1412 + 1000 OR ss_wholesale_cost BETWEEN 17 AND 17 + 20 )) B3, - (SELECT Avg(ss_list_price) B4_LP, - Count(ss_list_price) B4_CNT, - Count(DISTINCT ss_list_price) B4_CNTD + (SELECT Avg(ss_list_price) b4_lp, + Count(ss_list_price) b4_cnt, + Count(DISTINCT ss_list_price) b4_cntd FROM store_sales WHERE ss_quantity BETWEEN 16 AND 20 AND ( ss_list_price BETWEEN 9 AND 9 + 10 OR ss_coupon_amt BETWEEN 5270 AND 5270 + 1000 OR ss_wholesale_cost BETWEEN 29 AND 29 + 20 )) B4, - (SELECT Avg(ss_list_price) B5_LP, - Count(ss_list_price) B5_CNT, - Count(DISTINCT ss_list_price) B5_CNTD + (SELECT Avg(ss_list_price) b5_lp, + Count(ss_list_price) b5_cnt, + Count(DISTINCT ss_list_price) b5_cntd FROM store_sales WHERE ss_quantity BETWEEN 21 AND 25 AND ( ss_list_price BETWEEN 45 AND 45 + 10 OR ss_coupon_amt BETWEEN 826 AND 826 + 1000 OR ss_wholesale_cost BETWEEN 5 AND 5 + 20 )) B5, - (SELECT Avg(ss_list_price) B6_LP, - Count(ss_list_price) B6_CNT, - Count(DISTINCT ss_list_price) B6_CNTD + (SELECT Avg(ss_list_price) b6_lp, + Count(ss_list_price) b6_cnt, + Count(DISTINCT ss_list_price) b6_cntd FROM store_sales WHERE ss_quantity BETWEEN 26 AND 30 AND ( ss_list_price BETWEEN 174 AND 174 + 10 @@ -3429,9 +3437,12 @@ WITH "b1" AS ( FROM "store_sales" AS "store_sales" WHERE ( - "store_sales"."ss_coupon_amt" <= 2939 AND "store_sales"."ss_coupon_amt" >= 1939 - OR "store_sales"."ss_list_price" <= 28 AND "store_sales"."ss_list_price" >= 18 - OR "store_sales"."ss_wholesale_cost" <= 54 AND "store_sales"."ss_wholesale_cost" >= 34 + "store_sales"."ss_coupon_amt" <= 2939 + AND "store_sales"."ss_coupon_amt" >= 1939 + OR "store_sales"."ss_list_price" <= 28 + AND "store_sales"."ss_list_price" >= 18 + OR "store_sales"."ss_wholesale_cost" <= 54 + AND "store_sales"."ss_wholesale_cost" >= 34 ) AND "store_sales"."ss_quantity" <= 5 AND "store_sales"."ss_quantity" >= 0 @@ -3443,9 +3454,12 @@ WITH "b1" AS ( FROM "store_sales" AS "store_sales" WHERE ( - "store_sales"."ss_coupon_amt" <= 1035 AND "store_sales"."ss_coupon_amt" >= 35 - OR "store_sales"."ss_list_price" <= 11 AND "store_sales"."ss_list_price" >= 1 - OR "store_sales"."ss_wholesale_cost" <= 70 AND "store_sales"."ss_wholesale_cost" >= 50 + "store_sales"."ss_coupon_amt" <= 1035 + AND "store_sales"."ss_coupon_amt" >= 35 + OR "store_sales"."ss_list_price" <= 11 + AND "store_sales"."ss_list_price" >= 1 + OR "store_sales"."ss_wholesale_cost" <= 70 + AND "store_sales"."ss_wholesale_cost" >= 50 ) AND "store_sales"."ss_quantity" <= 10 AND "store_sales"."ss_quantity" >= 6 @@ -3457,9 +3471,12 @@ WITH "b1" AS ( FROM "store_sales" AS "store_sales" WHERE ( - "store_sales"."ss_coupon_amt" <= 2412 AND "store_sales"."ss_coupon_amt" >= 1412 - OR "store_sales"."ss_list_price" <= 101 AND "store_sales"."ss_list_price" >= 91 - OR "store_sales"."ss_wholesale_cost" <= 37 AND "store_sales"."ss_wholesale_cost" >= 17 + "store_sales"."ss_coupon_amt" <= 2412 + AND "store_sales"."ss_coupon_amt" >= 1412 + OR "store_sales"."ss_list_price" <= 101 + AND "store_sales"."ss_list_price" >= 91 + OR "store_sales"."ss_wholesale_cost" <= 37 + AND "store_sales"."ss_wholesale_cost" >= 17 ) AND "store_sales"."ss_quantity" <= 15 AND "store_sales"."ss_quantity" >= 11 @@ -3471,9 +3488,12 @@ WITH "b1" AS ( FROM "store_sales" AS "store_sales" WHERE ( - "store_sales"."ss_coupon_amt" <= 6270 AND "store_sales"."ss_coupon_amt" >= 5270 - OR "store_sales"."ss_list_price" <= 19 AND "store_sales"."ss_list_price" >= 9 - OR "store_sales"."ss_wholesale_cost" <= 49 AND "store_sales"."ss_wholesale_cost" >= 29 + "store_sales"."ss_coupon_amt" <= 6270 + AND "store_sales"."ss_coupon_amt" >= 5270 + OR "store_sales"."ss_list_price" <= 19 + AND "store_sales"."ss_list_price" >= 9 + OR "store_sales"."ss_wholesale_cost" <= 49 + AND "store_sales"."ss_wholesale_cost" >= 29 ) AND "store_sales"."ss_quantity" <= 20 AND "store_sales"."ss_quantity" >= 16 @@ -3485,9 +3505,12 @@ WITH "b1" AS ( FROM "store_sales" AS "store_sales" WHERE ( - "store_sales"."ss_coupon_amt" <= 1826 AND "store_sales"."ss_coupon_amt" >= 826 - OR "store_sales"."ss_list_price" <= 55 AND "store_sales"."ss_list_price" >= 45 - OR "store_sales"."ss_wholesale_cost" <= 25 AND "store_sales"."ss_wholesale_cost" >= 5 + "store_sales"."ss_coupon_amt" <= 1826 + AND "store_sales"."ss_coupon_amt" >= 826 + OR "store_sales"."ss_list_price" <= 55 + AND "store_sales"."ss_list_price" >= 45 + OR "store_sales"."ss_wholesale_cost" <= 25 + AND "store_sales"."ss_wholesale_cost" >= 5 ) AND "store_sales"."ss_quantity" <= 25 AND "store_sales"."ss_quantity" >= 21 @@ -3499,9 +3522,12 @@ WITH "b1" AS ( FROM "store_sales" AS "store_sales" WHERE ( - "store_sales"."ss_coupon_amt" <= 6548 AND "store_sales"."ss_coupon_amt" >= 5548 - OR "store_sales"."ss_list_price" <= 184 AND "store_sales"."ss_list_price" >= 174 - OR "store_sales"."ss_wholesale_cost" <= 62 AND "store_sales"."ss_wholesale_cost" >= 42 + "store_sales"."ss_coupon_amt" <= 6548 + AND "store_sales"."ss_coupon_amt" >= 5548 + OR "store_sales"."ss_list_price" <= 184 + AND "store_sales"."ss_list_price" >= 174 + OR "store_sales"."ss_wholesale_cost" <= 62 + AND "store_sales"."ss_wholesale_cost" >= 42 ) AND "store_sales"."ss_quantity" <= 30 AND "store_sales"."ss_quantity" >= 26 @@ -3860,11 +3886,17 @@ SELECT "ss3"."store_sales" / "ss2"."store_sales" AS "store_q2_q3_increase" FROM "ss" AS "ss1" JOIN "ss" AS "ss2" - ON "ss1"."ca_county" = "ss2"."ca_county" AND "ss2"."d_qoy" = 2 AND "ss2"."d_year" = 2001 + ON "ss1"."ca_county" = "ss2"."ca_county" + AND "ss2"."d_qoy" = 2 + AND "ss2"."d_year" = 2001 JOIN "ws" AS "ws1" - ON "ss1"."ca_county" = "ws1"."ca_county" AND "ws1"."d_qoy" = 1 AND "ws1"."d_year" = 2001 + ON "ss1"."ca_county" = "ws1"."ca_county" + AND "ws1"."d_qoy" = 1 + AND "ws1"."d_year" = 2001 JOIN "ss" AS "ss3" - ON "ss2"."ca_county" = "ss3"."ca_county" AND "ss3"."d_qoy" = 3 AND "ss3"."d_year" = 2001 + ON "ss2"."ca_county" = "ss3"."ca_county" + AND "ss3"."d_qoy" = 3 + AND "ss3"."d_year" = 2001 JOIN "ws" AS "ws2" ON "ws1"."ca_county" = "ws2"."ca_county" AND "ws2"."d_qoy" = 2 @@ -3932,7 +3964,7 @@ WITH "catalog_sales_2" AS ( FROM "date_dim" AS "date_dim" WHERE "date_dim"."d_date" >= '2001-03-04' - AND CAST("date_dim"."d_date" AS DATE) <= CAST('2001-06-02' AS DATE) + AND CAST("date_dim"."d_date" AS DATETIME) <= CAST('2001-06-02' AS DATE) ), "_u_0" AS ( SELECT 1.3 * AVG("catalog_sales"."cs_ext_discount_amt") AS "_col_0", @@ -3949,7 +3981,8 @@ FROM "catalog_sales_2" AS "catalog_sales" JOIN "date_dim_2" AS "date_dim" ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk" JOIN "item" AS "item" - ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk" AND "item"."i_manufact_id" = 610 + ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk" + AND "item"."i_manufact_id" = 610 LEFT JOIN "_u_0" AS "_u_0" ON "_u_0"."_u_1" = "item"."i_item_sk" WHERE @@ -4132,6 +4165,7 @@ LIMIT 100; -------------------------------------- -- TPC-DS 34 -------------------------------------- +# execute: true SELECT c_last_name, c_first_name, c_salutation, @@ -4234,24 +4268,25 @@ ORDER BY -------------------------------------- -- TPC-DS 35 -------------------------------------- +# execute: true SELECT ca_state, cd_gender, cd_marital_status, cd_dep_count, Count(*) cnt1, - Stddev_samp(cd_dep_count), - Avg(cd_dep_count), - Max(cd_dep_count), + Stddev_samp(cd_dep_count) AS "_col_5", + Avg(cd_dep_count) AS "_col_6", + Max(cd_dep_count) AS "_col_7", cd_dep_employed_count, Count(*) cnt2, - Stddev_samp(cd_dep_employed_count), - Avg(cd_dep_employed_count), - Max(cd_dep_employed_count), + Stddev_samp(cd_dep_employed_count) AS "_col_10", + Avg(cd_dep_employed_count) AS "_col_11", + Max(cd_dep_employed_count) AS "_col_12", cd_dep_college_count, Count(*) cnt3, - Stddev_samp(cd_dep_college_count), - Avg(cd_dep_college_count), - Max(cd_dep_college_count) + Stddev_samp(cd_dep_college_count) AS "_col_15", + Avg(cd_dep_college_count) AS "_col_16", + Max(cd_dep_college_count) AS "_col_17" FROM customer c, customer_address ca, customer_demographics @@ -4495,8 +4530,8 @@ JOIN "inventory" AS "inventory" AND "inventory"."inv_quantity_on_hand" >= 100 JOIN "date_dim" AS "date_dim" ON "date_dim"."d_date_sk" = "inventory"."inv_date_sk" - AND CAST("date_dim"."d_date" AS DATE) <= CAST('1999-05-05' AS DATE) - AND CAST("date_dim"."d_date" AS DATE) >= CAST('1999-03-06' AS DATE) + AND CAST("date_dim"."d_date" AS DATETIME) <= CAST('1999-05-05' AS DATE) + AND CAST("date_dim"."d_date" AS DATETIME) >= CAST('1999-03-06' AS DATE) WHERE "item"."i_current_price" <= 50 AND "item"."i_current_price" >= 20 @@ -4512,7 +4547,8 @@ LIMIT 100; -------------------------------------- -- TPC-DS 38 -------------------------------------- -SELECT Count(*) +# execute: true +SELECT Count(*) AS "_col_0" FROM (SELECT DISTINCT c_last_name, c_first_name, d_date @@ -4771,8 +4807,8 @@ LEFT JOIN "catalog_returns" AS "catalog_returns" AND "catalog_returns"."cr_order_number" = "catalog_sales"."cs_order_number" JOIN "date_dim" AS "date_dim" ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk" - AND CAST("date_dim"."d_date" AS DATE) <= CAST('2002-07-01' AS DATE) - AND CAST("date_dim"."d_date" AS DATE) >= CAST('2002-05-02' AS DATE) + AND CAST("date_dim"."d_date" AS DATETIME) <= CAST('2002-07-01' AS DATE) + AND CAST("date_dim"."d_date" AS DATETIME) >= CAST('2002-05-02' AS DATE) JOIN "item" AS "item" ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk" AND "item"."i_current_price" <= 1.49 @@ -4980,10 +5016,11 @@ LIMIT 100; -------------------------------------- -- TPC-DS 42 -------------------------------------- +# execute: true SELECT dt.d_year, item.i_category_id, item.i_category, - Sum(ss_ext_sales_price) + Sum(ss_ext_sales_price) AS "_col_3" FROM date_dim dt, store_sales, item @@ -5132,7 +5169,8 @@ FROM "date_dim" AS "date_dim" JOIN "store_sales" AS "store_sales" ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" JOIN "store" AS "store" - ON "store"."s_gmt_offset" = -5 AND "store"."s_store_sk" = "store_sales"."ss_store_sk" + ON "store"."s_gmt_offset" = -5 + AND "store"."s_store_sk" = "store_sales"."ss_store_sk" WHERE "date_dim"."d_year" = 2002 GROUP BY @@ -5266,9 +5304,10 @@ LIMIT 100; -------------------------------------- -- TPC-DS 45 -------------------------------------- +# execute: true SELECT ca_zip, ca_state, - Sum(ws_sales_price) + Sum(ws_sales_price) AS "_col_2" FROM web_sales, customer, customer_address, @@ -5333,6 +5372,7 @@ LIMIT 100; -------------------------------------- -- TPC-DS 46 -------------------------------------- +# execute: true SELECT c_last_name, c_first_name, ca_city, @@ -5524,10 +5564,14 @@ WITH "v1" AS ( "date_dim"."d_moy" = 1 OR "date_dim"."d_year" = 1998 OR "date_dim"."d_year" = 1999 ) AND ( - "date_dim"."d_moy" = 12 OR "date_dim"."d_year" = 1999 OR "date_dim"."d_year" = 2000 + "date_dim"."d_moy" = 12 + OR "date_dim"."d_year" = 1999 + OR "date_dim"."d_year" = 2000 ) AND ( - "date_dim"."d_year" = 1998 OR "date_dim"."d_year" = 1999 OR "date_dim"."d_year" = 2000 + "date_dim"."d_year" = 1998 + OR "date_dim"."d_year" = 1999 + OR "date_dim"."d_year" = 2000 ) JOIN "store" AS "store" ON "store"."s_store_sk" = "store_sales"."ss_store_sk" @@ -5576,7 +5620,8 @@ LIMIT 100; -------------------------------------- -- TPC-DS 48 -------------------------------------- -SELECT Sum (ss_quantity) +# execute: true +SELECT Sum (ss_quantity) AS "_col_0" FROM store_sales, store, customer_demographics, @@ -5919,6 +5964,7 @@ LIMIT 100; -------------------------------------- -- TPC-DS 50 -------------------------------------- +# execute: true SELECT s_store_name, s_company_id, s_street_number, @@ -6811,10 +6857,14 @@ WITH "v1" AS ( "date_dim"."d_moy" = 1 OR "date_dim"."d_year" = 1999 OR "date_dim"."d_year" = 2000 ) AND ( - "date_dim"."d_moy" = 12 OR "date_dim"."d_year" = 2000 OR "date_dim"."d_year" = 2001 + "date_dim"."d_moy" = 12 + OR "date_dim"."d_year" = 2000 + OR "date_dim"."d_year" = 2001 ) AND ( - "date_dim"."d_year" = 1999 OR "date_dim"."d_year" = 2000 OR "date_dim"."d_year" = 2001 + "date_dim"."d_year" = 1999 + OR "date_dim"."d_year" = 2000 + OR "date_dim"."d_year" = 2001 ) GROUP BY "item"."i_category", @@ -7056,6 +7106,7 @@ LIMIT 100; -------------------------------------- -- TPC-DS 59 -------------------------------------- +# execute: true WITH wss AS (SELECT d_week_seq, ss_store_sk, @@ -7095,13 +7146,13 @@ WITH wss SELECT s_store_name1, s_store_id1, d_week_seq1, - sun_sales1 / sun_sales2, - mon_sales1 / mon_sales2, - tue_sales1 / tue_sales2, - wed_sales1 / wed_sales2, - thu_sales1 / thu_sales2, - fri_sales1 / fri_sales2, - sat_sales1 / sat_sales2 + sun_sales1 / sun_sales2 AS "_col_3", + mon_sales1 / mon_sales2 AS "_col_4", + tue_sales1 / tue_sales2 AS "_col_5", + wed_sales1 / wed_sales2 AS "_col_6", + thu_sales1 / thu_sales2 AS "_col_7", + fri_sales1 / fri_sales2 AS "_col_8", + sat_sales1 / sat_sales2 AS "_col_9" FROM (SELECT s_store_name s_store_name1, wss.d_week_seq d_week_seq1, s_store_id s_store_id1, @@ -7553,7 +7604,8 @@ LIMIT 100; -------------------------------------- -- TPC-DS 62 -------------------------------------- -SELECT Substr(w_warehouse_name, 1, 20), +# execute: true +SELECT Substr(w_warehouse_name, 1, 20) AS "_col_0", sm_type, web_name, Sum(CASE @@ -8132,6 +8184,7 @@ LIMIT 100; -------------------------------------- -- TPC-DS 66 -------------------------------------- +# execute: true SELECT w_warehouse_name, w_warehouse_sq_ft, w_city, @@ -9038,6 +9091,7 @@ LIMIT 100; -------------------------------------- -- TPC-DS 68 -------------------------------------- +# execute: true SELECT c_last_name, c_first_name, ca_city, @@ -9580,6 +9634,7 @@ LIMIT 100; -------------------------------------- -- TPC-DS 73 -------------------------------------- +# execute: true SELECT c_last_name, c_first_name, c_salutation, @@ -9667,6 +9722,7 @@ ORDER BY -------------------------------------- -- TPC-DS 74 -------------------------------------- +# execute: true WITH year_total AS (SELECT c_customer_id customer_id, c_first_name customer_first_name, @@ -9826,6 +9882,7 @@ LIMIT 100; -------------------------------------- -- TPC-DS 75 -------------------------------------- +# execute: true WITH all_sales AS (SELECT d_year, i_brand_id, @@ -10030,6 +10087,7 @@ LIMIT 100; -------------------------------------- -- TPC-DS 76 -------------------------------------- +# execute: true SELECT channel, col_name, d_year, @@ -10280,8 +10338,8 @@ WITH "date_dim_2" AS ( "date_dim"."d_date" AS "d_date" FROM "date_dim" AS "date_dim" WHERE - CAST("date_dim"."d_date" AS DATE) <= CAST('2001-09-15' AS DATE) - AND CAST("date_dim"."d_date" AS DATE) >= CAST('2001-08-16' AS DATE) + CAST("date_dim"."d_date" AS DATETIME) <= CAST('2001-09-15' AS DATE) + AND CAST("date_dim"."d_date" AS DATETIME) >= CAST('2001-08-16' AS DATE) ), "store_2" AS ( SELECT "store"."s_store_sk" AS "s_store_sk" @@ -10407,6 +10465,7 @@ LIMIT 100; -------------------------------------- -- TPC-DS 78 -------------------------------------- +# execute: true WITH ws AS (SELECT d_year AS ws_sold_year, ws_item_sk, @@ -10596,9 +10655,10 @@ LIMIT 100; -------------------------------------- -- TPC-DS 79 -------------------------------------- +# execute: true SELECT c_last_name, c_first_name, - Substr(s_city, 1, 30), + Substr(s_city, 1, 30) AS "_col_2", ss_ticket_number, amt, profit @@ -10788,8 +10848,8 @@ WITH "date_dim_2" AS ( "date_dim"."d_date" AS "d_date" FROM "date_dim" AS "date_dim" WHERE - CAST("date_dim"."d_date" AS DATE) <= CAST('2000-09-25' AS DATE) - AND CAST("date_dim"."d_date" AS DATE) >= CAST('2000-08-26' AS DATE) + CAST("date_dim"."d_date" AS DATETIME) <= CAST('2000-09-25' AS DATE) + AND CAST("date_dim"."d_date" AS DATETIME) >= CAST('2000-08-26' AS DATE) ), "item_2" AS ( SELECT "item"."i_item_sk" AS "i_item_sk", @@ -10909,6 +10969,7 @@ LIMIT 100; -------------------------------------- -- TPC-DS 81 -------------------------------------- +# execute: true WITH customer_total_return AS (SELECT cr_returning_customer_sk AS ctr_customer_sk, ca_state AS ctr_state, @@ -11068,8 +11129,8 @@ JOIN "store_sales" AS "store_sales" ON "item"."i_item_sk" = "store_sales"."ss_item_sk" JOIN "date_dim" AS "date_dim" ON "date_dim"."d_date_sk" = "inventory"."inv_date_sk" - AND CAST("date_dim"."d_date" AS DATE) <= CAST('1998-06-26' AS DATE) - AND CAST("date_dim"."d_date" AS DATE) >= CAST('1998-04-27' AS DATE) + AND CAST("date_dim"."d_date" AS DATETIME) <= CAST('1998-06-26' AS DATE) + AND CAST("date_dim"."d_date" AS DATETIME) >= CAST('1998-04-27' AS DATE) WHERE "item"."i_current_price" <= 93 AND "item"."i_current_price" >= 63 @@ -11329,10 +11390,11 @@ LIMIT 100; -------------------------------------- -- TPC-DS 85 -------------------------------------- -SELECT Substr(r_reason_desc, 1, 20), - Avg(ws_quantity), - Avg(wr_refunded_cash), - Avg(wr_fee) +# execute: true +SELECT Substr(r_reason_desc, 1, 20) AS "_col_0", + Avg(ws_quantity) AS "_col_1", + Avg(wr_refunded_cash) AS "_col_2", + Avg(wr_fee) AS "_col_3" FROM web_sales, web_returns, web_page, @@ -11387,7 +11449,8 @@ SELECT AVG("web_returns"."wr_fee") AS "_col_3" FROM "web_sales" AS "web_sales" JOIN "date_dim" AS "date_dim" - ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk" AND "date_dim"."d_year" = 2001 + ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk" + AND "date_dim"."d_year" = 2001 JOIN "web_page" AS "web_page" ON "web_page"."wp_web_page_sk" = "web_sales"."ws_web_page_sk" JOIN "web_returns" AS "web_returns" @@ -11509,7 +11572,8 @@ LIMIT 100; -------------------------------------- -- TPC-DS 87 -------------------------------------- -select count(*) +# execute: true +select count(*) as "_col_0" from ((select distinct c_last_name, c_first_name, d_date from store_sales, date_dim, customer where store_sales.ss_sold_date_sk = date_dim.d_date_sk @@ -12020,10 +12084,11 @@ LIMIT 100; -------------------------------------- -- TPC-DS 91 -------------------------------------- -SELECT cc_call_center_id Call_Center, - cc_name Call_Center_Name, - cc_manager Manager, - Sum(cr_net_loss) Returns_Loss +# execute: true +SELECT cc_call_center_id call_center, + cc_name call_center_name, + cc_manager manager, + Sum(cr_net_loss) returns_loss FROM call_center, catalog_returns, date_dim, @@ -12135,7 +12200,7 @@ WITH "web_sales_2" AS ( FROM "date_dim" AS "date_dim" WHERE "date_dim"."d_date" >= '2002-03-29' - AND CAST("date_dim"."d_date" AS DATE) <= CAST('2002-06-27' AS DATE) + AND CAST("date_dim"."d_date" AS DATETIME) <= CAST('2002-06-27' AS DATE) ), "_u_0" AS ( SELECT 1.3 * AVG("web_sales"."ws_ext_discount_amt") AS "_col_0", @@ -12276,14 +12341,14 @@ JOIN "date_dim" AS "date_dim" AND "date_dim"."d_date_sk" = "ws1"."ws_ship_date_sk" AND ( CAST('2000-3-01' AS DATE) + INTERVAL '60' DAY - ) >= CAST("date_dim"."d_date" AS DATE) + ) >= CAST("date_dim"."d_date" AS DATETIME) JOIN "web_site" AS "web_site" ON "web_site"."web_company_name" = 'pri' AND "web_site"."web_site_sk" = "ws1"."ws_web_site_sk" WHERE "_u_3"."_u_4" IS NULL - AND NOT "_u_0"."_u_1" IS NULL AND ARRAY_ANY("_u_0"."_u_2", "_x" -> "ws1"."ws_warehouse_sk" <> "_x") + AND NOT "_u_0"."_u_1" IS NULL ORDER BY COUNT(DISTINCT "ws1"."ws_order_number") LIMIT 100; @@ -12366,7 +12431,7 @@ JOIN "date_dim" AS "date_dim" AND "date_dim"."d_date_sk" = "ws1"."ws_ship_date_sk" AND ( CAST('2000-4-01' AS DATE) + INTERVAL '60' DAY - ) >= CAST("date_dim"."d_date" AS DATE) + ) >= CAST("date_dim"."d_date" AS DATETIME) JOIN "web_site" AS "web_site" ON "web_site"."web_company_name" = 'pri' AND "web_site"."web_site_sk" = "ws1"."ws_web_site_sk" @@ -12379,7 +12444,8 @@ LIMIT 100; -------------------------------------- -- TPC-DS 96 -------------------------------------- -SELECT Count(*) +# execute: true +SELECT Count(*) AS "_col_0" FROM store_sales, household_demographics, time_dim, @@ -12400,7 +12466,8 @@ JOIN "household_demographics" AS "household_demographics" ON "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk" AND "household_demographics"."hd_dep_count" = 7 JOIN "store" AS "store" - ON "store"."s_store_name" = 'ese' AND "store"."s_store_sk" = "store_sales"."ss_store_sk" + ON "store"."s_store_name" = 'ese' + AND "store"."s_store_sk" = "store_sales"."ss_store_sk" JOIN "time_dim" AS "time_dim" ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk" AND "time_dim"."t_hour" = 15 @@ -12412,6 +12479,7 @@ LIMIT 100; -------------------------------------- -- TPC-DS 97 -------------------------------------- +# execute: true WITH ssci AS (SELECT ss_customer_sk customer_sk, ss_item_sk item_sk @@ -12502,7 +12570,8 @@ SELECT ) AS "store_and_catalog" FROM "ssci" AS "ssci" FULL JOIN "csci" AS "csci" - ON "csci"."customer_sk" = "ssci"."customer_sk" AND "csci"."item_sk" = "ssci"."item_sk" + ON "csci"."customer_sk" = "ssci"."customer_sk" + AND "csci"."item_sk" = "ssci"."item_sk" LIMIT 100; -------------------------------------- @@ -12546,8 +12615,8 @@ SELECT FROM "store_sales" AS "store_sales" JOIN "date_dim" AS "date_dim" ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" - AND CAST("date_dim"."d_date" AS DATE) <= CAST('2000-06-17' AS DATE) - AND CAST("date_dim"."d_date" AS DATE) >= CAST('2000-05-18' AS DATE) + AND CAST("date_dim"."d_date" AS DATETIME) <= CAST('2000-06-17' AS DATE) + AND CAST("date_dim"."d_date" AS DATETIME) >= CAST('2000-05-18' AS DATE) JOIN "item" AS "item" ON "item"."i_category" IN ('Men', 'Home', 'Electronics') AND "item"."i_item_sk" = "store_sales"."ss_item_sk" @@ -12567,7 +12636,8 @@ ORDER BY -------------------------------------- -- TPC-DS 99 -------------------------------------- -SELECT Substr(w_warehouse_name, 1, 20), +# execute: true +SELECT Substr(w_warehouse_name, 1, 20) AS "_col_0", sm_type, cc_name, Sum(CASE diff --git a/tests/fixtures/optimizer/tpc-ds/warehouse.csv.gz b/tests/fixtures/optimizer/tpc-ds/warehouse.csv.gz index cf64636..f2f07a3 100644 Binary files a/tests/fixtures/optimizer/tpc-ds/warehouse.csv.gz and b/tests/fixtures/optimizer/tpc-ds/warehouse.csv.gz differ diff --git a/tests/fixtures/optimizer/tpc-ds/web_page.csv.gz b/tests/fixtures/optimizer/tpc-ds/web_page.csv.gz index 894ce3b..62ddd8c 100644 Binary files a/tests/fixtures/optimizer/tpc-ds/web_page.csv.gz and b/tests/fixtures/optimizer/tpc-ds/web_page.csv.gz differ diff --git a/tests/fixtures/optimizer/tpc-ds/web_returns.csv.gz b/tests/fixtures/optimizer/tpc-ds/web_returns.csv.gz index 21f7040..af05d52 100644 Binary files a/tests/fixtures/optimizer/tpc-ds/web_returns.csv.gz and b/tests/fixtures/optimizer/tpc-ds/web_returns.csv.gz differ diff --git a/tests/fixtures/optimizer/tpc-ds/web_sales.csv.gz b/tests/fixtures/optimizer/tpc-ds/web_sales.csv.gz index b384c78..26b09b8 100644 Binary files a/tests/fixtures/optimizer/tpc-ds/web_sales.csv.gz and b/tests/fixtures/optimizer/tpc-ds/web_sales.csv.gz differ diff --git a/tests/fixtures/optimizer/tpc-ds/web_site.csv.gz b/tests/fixtures/optimizer/tpc-ds/web_site.csv.gz index b9b5f72..a8cabdb 100644 Binary files a/tests/fixtures/optimizer/tpc-ds/web_site.csv.gz and b/tests/fixtures/optimizer/tpc-ds/web_site.csv.gz differ diff --git a/tests/fixtures/optimizer/tpc-h/tpc-h.sql b/tests/fixtures/optimizer/tpc-h/tpc-h.sql index 39b5ffa..c131643 100644 --- a/tests/fixtures/optimizer/tpc-h/tpc-h.sql +++ b/tests/fixtures/optimizer/tpc-h/tpc-h.sql @@ -249,9 +249,9 @@ FROM "orders" AS "orders" LEFT JOIN "_u_0" AS "_u_0" ON "_u_0"."l_orderkey" = "orders"."o_orderkey" WHERE - NOT "_u_0"."l_orderkey" IS NULL - AND CAST("orders"."o_orderdate" AS DATE) < CAST('1993-10-01' AS DATE) + CAST("orders"."o_orderdate" AS DATE) < CAST('1993-10-01' AS DATE) AND CAST("orders"."o_orderdate" AS DATE) >= CAST('1993-07-01' AS DATE) + AND NOT "_u_0"."l_orderkey" IS NULL GROUP BY "orders"."o_orderpriority" ORDER BY @@ -609,7 +609,8 @@ JOIN "orders" AS "orders" AND CAST("orders"."o_orderdate" AS DATE) < CAST('1994-01-01' AS DATE) AND CAST("orders"."o_orderdate" AS DATE) >= CAST('1993-10-01' AS DATE) JOIN "lineitem" AS "lineitem" - ON "lineitem"."l_orderkey" = "orders"."o_orderkey" AND "lineitem"."l_returnflag" = 'R' + ON "lineitem"."l_orderkey" = "orders"."o_orderkey" + AND "lineitem"."l_returnflag" = 'R' GROUP BY "customer"."c_custkey", "customer"."c_name", @@ -731,7 +732,8 @@ SELECT ) AS "high_line_count", SUM( CASE - WHEN "orders"."o_orderpriority" <> '1-URGENT' AND "orders"."o_orderpriority" <> '2-HIGH' + WHEN "orders"."o_orderpriority" <> '1-URGENT' + AND "orders"."o_orderpriority" <> '2-HIGH' THEN 1 ELSE 0 END @@ -1257,7 +1259,8 @@ WITH "_u_0" AS ( LEFT JOIN "_u_0" AS "_u_0" ON "_u_0"."p_partkey" = "partsupp"."ps_partkey" LEFT JOIN "_u_1" AS "_u_1" - ON "_u_1"."_u_2" = "partsupp"."ps_partkey" AND "_u_1"."_u_3" = "partsupp"."ps_suppkey" + ON "_u_1"."_u_2" = "partsupp"."ps_partkey" + AND "_u_1"."_u_3" = "partsupp"."ps_suppkey" WHERE "_u_1"."_col_0" < "partsupp"."ps_availqty" AND NOT "_u_0"."p_partkey" IS NULL GROUP BY @@ -1270,7 +1273,8 @@ FROM "supplier" AS "supplier" LEFT JOIN "_u_4" AS "_u_4" ON "_u_4"."ps_suppkey" = "supplier"."s_suppkey" JOIN "nation" AS "nation" - ON "nation"."n_name" = 'CANADA' AND "nation"."n_nationkey" = "supplier"."s_nationkey" + ON "nation"."n_name" = 'CANADA' + AND "nation"."n_nationkey" = "supplier"."s_nationkey" WHERE NOT "_u_4"."ps_suppkey" IS NULL ORDER BY @@ -1358,8 +1362,8 @@ WHERE "_u_2"."l_orderkey" IS NULL OR NOT ARRAY_ANY("_u_2"."_u_3", "_x" -> "l1"."l_suppkey" <> "_x") ) - AND NOT "_u_0"."l_orderkey" IS NULL AND ARRAY_ANY("_u_0"."_u_1", "_x" -> "l1"."l_suppkey" <> "_x") + AND NOT "_u_0"."l_orderkey" IS NULL GROUP BY "supplier"."s_name" ORDER BY -- cgit v1.2.3