summaryrefslogtreecommitdiffstats
path: root/tests/fixtures
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-08 08:11:50 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-08 08:11:50 +0000
commit8978da3b39d7ca3cf83ee30fcc63ffe0e5453fb2 (patch)
tree2e29f131dff77b31e84c957266de8f18655b6f88 /tests/fixtures
parentAdding upstream version 22.2.0. (diff)
downloadsqlglot-8978da3b39d7ca3cf83ee30fcc63ffe0e5453fb2.tar.xz
sqlglot-8978da3b39d7ca3cf83ee30fcc63ffe0e5453fb2.zip
Adding upstream version 23.7.0.upstream/23.7.0
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'tests/fixtures')
-rw-r--r--tests/fixtures/identity.sql10
-rw-r--r--tests/fixtures/optimizer/canonicalize.sql11
-rw-r--r--tests/fixtures/optimizer/merge_subqueries.sql18
-rw-r--r--tests/fixtures/optimizer/optimizer.sql19
-rw-r--r--tests/fixtures/optimizer/pushdown_projections.sql6
-rw-r--r--tests/fixtures/optimizer/qualify_columns.sql14
-rw-r--r--tests/fixtures/optimizer/qualify_columns_ddl.sql15
-rw-r--r--tests/fixtures/optimizer/qualify_tables.sql1
-rw-r--r--tests/fixtures/optimizer/simplify.sql99
-rw-r--r--tests/fixtures/optimizer/tpc-ds/call_center.csv.gzbin425 -> 427 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/catalog_page.csv.gzbin460883 -> 393991 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/catalog_returns.csv.gzbin158215 -> 167258 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/catalog_sales.csv.gzbin1814673 -> 639482 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/customer.csv.gzbin107573 -> 109529 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/customer_address.csv.gzbin28719 -> 28542 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/customer_demographics.csv.gzbin126715 -> 33320 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/date_dim.csv.gzbin1575448 -> 208646 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/household_demographics.csv.gzbin23544 -> 23432 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/income_band.csv.gzbin191 -> 194 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/inventory.csv.gzbin202661 -> 43193 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/item.csv.gzbin31336 -> 31259 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/promotion.csv.gzbin501 -> 501 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/reason.csv.gzbin83 -> 87 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/ship_mode.csv.gzbin633 -> 623 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/store.csv.gzbin397 -> 398 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/store_returns.csv.gzbin255650 -> 266354 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/store_sales.csv.gzbin2436694 -> 421923 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/time_dim.csv.gzbin680588 -> 77216 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/tpc-ds.sql306
-rw-r--r--tests/fixtures/optimizer/tpc-ds/warehouse.csv.gzbin221 -> 224 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/web_page.csv.gzbin212 -> 215 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/web_returns.csv.gzbin67833 -> 71921 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/web_sales.csv.gzbin867887 -> 615903 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/web_site.csv.gzbin406 -> 409 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-h/tpc-h.sql18
-rw-r--r--tests/fixtures/pretty.sql11
36 files changed, 383 insertions, 145 deletions
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index 6d3bb07..5453a78 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -141,6 +141,7 @@ x ILIKE '%y%' ESCAPE '\'
INTERVAL '1' DAY
INTERVAL '1' MONTH
INTERVAL '1' YEAR
+INTERVAL '1' HOUR TO SECOND
INTERVAL '-1' CURRENT_DATE
INTERVAL '-31' CAST(GETDATE() AS DATE)
INTERVAL (1 + 3) DAYS
@@ -149,6 +150,7 @@ INTERVAL '1' DAY * 5
CASE WHEN TRUE THEN INTERVAL '15' DAYS END
CASE WHEN TRUE THEN 1 ELSE interval END
CASE WHEN TRUE THEN 1 ELSE "INTERVAL" END
+SELECT asof FROM x
SELECT * WHERE interval IS NULL
SELECT * WHERE NOT interval IS NULL
SELECT * WHERE INTERVAL "is" > 1
@@ -176,6 +178,7 @@ COUNT(DISTINCT CASE WHEN DATE_TRUNC('ISOWEEK', DATE(time_field)) = DATE_TRUNC('I
COUNT(a, b)
x[y - 1]
CASE WHEN SUM(x) > 3 THEN 1 END OVER (PARTITION BY x)
+ANY(x) OVER (PARTITION BY x)
SUM(ROW() OVER (PARTITION BY x))
SUM(ROW() OVER (PARTITION BY x + 1))
SUM(ROW() OVER (PARTITION BY x AND y))
@@ -361,7 +364,6 @@ SELECT GREATEST(a, b, c) FROM test
SELECT LAST_VALUE(a) FROM test
SELECT LAST_VALUE(a) IGNORE NULLS OVER () + 1
SELECT LN(a) FROM test
-SELECT LOG10(a) FROM test
SELECT MAX(a) FROM test
SELECT MIN(a) FROM test
SELECT POWER(a, 2) FROM test
@@ -476,6 +478,7 @@ SELECT 1 UNION (SELECT 2) ORDER BY x
SELECT * FROM (((SELECT 1) UNION SELECT 2) ORDER BY x LIMIT 1 OFFSET 1)
SELECT * FROM ((SELECT 1 AS x) CROSS JOIN (SELECT 2 AS y)) AS z
((SELECT 1) EXCEPT (SELECT 2))
+((SELECT 1)) LIMIT 1
VALUES (1) UNION SELECT * FROM x
WITH a AS (SELECT 1) SELECT a.* FROM a
WITH a AS (SELECT 1), b AS (SELECT 2) SELECT a.*, b.* FROM a CROSS JOIN b
@@ -637,6 +640,8 @@ CREATE DATABASE IF NOT EXISTS y
CREATE PROCEDURE IF NOT EXISTS a.b.c() AS 'DECLARE BEGIN; END'
CREATE TABLE T3 AS (SELECT DISTINCT A FROM T1 EXCEPT (SELECT A FROM T2) LIMIT 1)
DESCRIBE x
+DESCRIBE EXTENDED a.b
+DESCRIBE FORMATTED a.b
DROP INDEX a.b.c
DROP FUNCTION a.b.c (INT)
DROP MATERIALIZED VIEW x.y.z
@@ -831,6 +836,7 @@ SELECT * FROM schema.case
SELECT * FROM current_date
SELECT * FROM schema.current_date
SELECT /*+ SOME_HINT(foo) */ 1
+SELECT /*+ REBALANCE */ * FROM foo
SELECT * FROM (tbl1 CROSS JOIN (SELECT * FROM tbl2) AS t1)
/* comment1 */ INSERT INTO x /* comment2 */ VALUES (1, 2, 3)
/* comment1 */ UPDATE tbl /* comment2 */ SET x = 2 WHERE x < 2
@@ -857,3 +863,5 @@ SELECT truncate
SELECT only
TRUNCATE(a, b)
SELECT enum
+SELECT unlogged
+SELECT name
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;
@@ -824,27 +885,51 @@ 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);
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
--- a/tests/fixtures/optimizer/tpc-ds/call_center.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/call_center.csv.gz
Binary files 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
--- a/tests/fixtures/optimizer/tpc-ds/catalog_page.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/catalog_page.csv.gz
Binary files 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
--- a/tests/fixtures/optimizer/tpc-ds/catalog_returns.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/catalog_returns.csv.gz
Binary files 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
--- a/tests/fixtures/optimizer/tpc-ds/catalog_sales.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/catalog_sales.csv.gz
Binary files 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
--- a/tests/fixtures/optimizer/tpc-ds/customer.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/customer.csv.gz
Binary files 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
--- a/tests/fixtures/optimizer/tpc-ds/customer_address.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/customer_address.csv.gz
Binary files 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
--- a/tests/fixtures/optimizer/tpc-ds/customer_demographics.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/customer_demographics.csv.gz
Binary files 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
--- a/tests/fixtures/optimizer/tpc-ds/date_dim.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/date_dim.csv.gz
Binary files 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
--- a/tests/fixtures/optimizer/tpc-ds/household_demographics.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/household_demographics.csv.gz
Binary files 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
--- a/tests/fixtures/optimizer/tpc-ds/income_band.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/income_band.csv.gz
Binary files 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
--- a/tests/fixtures/optimizer/tpc-ds/inventory.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/inventory.csv.gz
Binary files 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
--- a/tests/fixtures/optimizer/tpc-ds/item.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/item.csv.gz
Binary files 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
--- a/tests/fixtures/optimizer/tpc-ds/promotion.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/promotion.csv.gz
Binary files 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
--- a/tests/fixtures/optimizer/tpc-ds/reason.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/reason.csv.gz
Binary files 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
--- a/tests/fixtures/optimizer/tpc-ds/ship_mode.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/ship_mode.csv.gz
Binary files 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
--- a/tests/fixtures/optimizer/tpc-ds/store.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/store.csv.gz
Binary files 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
--- a/tests/fixtures/optimizer/tpc-ds/store_returns.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/store_returns.csv.gz
Binary files 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
--- a/tests/fixtures/optimizer/tpc-ds/store_sales.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/store_sales.csv.gz
Binary files 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
--- a/tests/fixtures/optimizer/tpc-ds/time_dim.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/time_dim.csv.gz
Binary files 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
--- a/tests/fixtures/optimizer/tpc-ds/warehouse.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/warehouse.csv.gz
Binary files 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
--- a/tests/fixtures/optimizer/tpc-ds/web_page.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/web_page.csv.gz
Binary files 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
--- a/tests/fixtures/optimizer/tpc-ds/web_returns.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/web_returns.csv.gz
Binary files 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
--- a/tests/fixtures/optimizer/tpc-ds/web_sales.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/web_sales.csv.gz
Binary files 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
--- a/tests/fixtures/optimizer/tpc-ds/web_site.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/web_site.csv.gz
Binary files 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
diff --git a/tests/fixtures/pretty.sql b/tests/fixtures/pretty.sql
index 23d9511..fac08be 100644
--- a/tests/fixtures/pretty.sql
+++ b/tests/fixtures/pretty.sql
@@ -384,3 +384,14 @@ JOIN b
CROSS JOIN d
JOIN e
ON d.id = e.id;
+
+SELECT * FROM a JOIN b JOIN c USING (e) JOIN d USING (f) USING (g);
+SELECT
+ *
+FROM a
+JOIN b
+ JOIN c
+ USING (e)
+ JOIN d
+ USING (f)
+ USING (g);