summaryrefslogtreecommitdiffstats
path: root/tests/fixtures
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2023-06-02 23:59:11 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2023-06-02 23:59:11 +0000
commitcaea5267cb8e1fea3702adbdf6f68fd37d13b3b7 (patch)
treef06f1da1ab3b6906beca1c3c7222d28ff00766ac /tests/fixtures
parentAdding upstream version 12.2.0. (diff)
downloadsqlglot-caea5267cb8e1fea3702adbdf6f68fd37d13b3b7.tar.xz
sqlglot-caea5267cb8e1fea3702adbdf6f68fd37d13b3b7.zip
Adding upstream version 15.0.0.upstream/15.0.0
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to '')
-rw-r--r--tests/fixtures/identity.sql24
-rw-r--r--tests/fixtures/optimizer/canonicalize.sql4
-rw-r--r--tests/fixtures/optimizer/eliminate_subqueries.sql14
-rw-r--r--tests/fixtures/optimizer/expand_laterals.sql40
-rw-r--r--tests/fixtures/optimizer/expand_multi_table_selects.sql11
-rw-r--r--tests/fixtures/optimizer/isolate_table_selects.sql18
-rw-r--r--tests/fixtures/optimizer/merge_subqueries.sql6
-rw-r--r--tests/fixtures/optimizer/normalize.sql3
-rw-r--r--tests/fixtures/optimizer/normalize_identifiers.sql (renamed from tests/fixtures/optimizer/lower_identities.sql)30
-rw-r--r--tests/fixtures/optimizer/optimize_joins.sql16
-rw-r--r--tests/fixtures/optimizer/optimizer.sql121
-rw-r--r--tests/fixtures/optimizer/pushdown_predicates.sql4
-rw-r--r--tests/fixtures/optimizer/pushdown_projections.sql4
-rw-r--r--tests/fixtures/optimizer/qualify_columns.sql35
-rw-r--r--tests/fixtures/optimizer/qualify_columns__invalid.sql2
-rw-r--r--tests/fixtures/optimizer/qualify_columns__with_invisible.sql16
-rw-r--r--tests/fixtures/optimizer/qualify_tables.sql7
-rw-r--r--tests/fixtures/optimizer/simplify.sql17
-rw-r--r--tests/fixtures/optimizer/tpc-ds/tpc-ds.sql540
-rw-r--r--tests/fixtures/optimizer/tpc-h/tpc-h.sql40
-rw-r--r--tests/fixtures/partial.sql2
21 files changed, 559 insertions, 395 deletions
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index ea695c9..0a1e305 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -174,6 +174,8 @@ SET variable = value
SET GLOBAL variable = value
SET LOCAL variable = value
SET @user OFF
+@x
+@"x"
COMMIT
USE db
USE role x
@@ -183,6 +185,7 @@ USE schema x.y
NOT 1
NOT NOT 1
SELECT * FROM test
+SELECT * FROM db.FOO()
SELECT *, 1 FROM test
SELECT * FROM a.b
SELECT * FROM a.b.c
@@ -288,10 +291,6 @@ SELECT a FROM test ORDER BY a, b
SELECT x FROM tests ORDER BY a DESC, b DESC, c
SELECT a FROM test ORDER BY a > 1
SELECT * FROM test ORDER BY DATE DESC, TIMESTAMP DESC
-SELECT * FROM test DISTRIBUTE BY y SORT BY x DESC ORDER BY l
-SELECT * FROM test CLUSTER BY y
-SELECT * FROM test CLUSTER BY y
-SELECT * FROM test WHERE RAND() <= 0.1 DISTRIBUTE BY RAND() SORT BY RAND()
SELECT a, b FROM test GROUP BY 1
SELECT a, b FROM test GROUP BY a
SELECT a, b FROM test WHERE a = 1 GROUP BY a HAVING a = 2
@@ -414,7 +413,7 @@ SELECT 1 AS delete, 2 AS alter
SELECT * FROM (x)
SELECT * FROM ((x))
SELECT * FROM ((SELECT 1))
-SELECT * FROM (x LATERAL VIEW EXPLODE(y) JOIN foo)
+SELECT * FROM (x CROSS JOIN foo LATERAL VIEW EXPLODE(y))
SELECT * FROM (SELECT 1) AS x
SELECT * FROM (SELECT 1 UNION SELECT 2) AS x
SELECT * FROM (SELECT 1 UNION ALL SELECT 2) AS x
@@ -447,10 +446,6 @@ SELECT 1 UNION (SELECT 2)
(SELECT 1) ORDER BY x LIMIT 1 OFFSET 1
(SELECT 1 UNION SELECT 2) UNION (SELECT 2 UNION ALL SELECT 3)
(SELECT 1 UNION SELECT 2) ORDER BY x LIMIT 1 OFFSET 1
-(SELECT 1 UNION SELECT 2) CLUSTER BY y DESC
-(SELECT 1 UNION SELECT 2) SORT BY z
-(SELECT 1 UNION SELECT 2) DISTRIBUTE BY z
-(SELECT 1 UNION SELECT 2) DISTRIBUTE BY z SORT BY x
SELECT 1 UNION (SELECT 2) ORDER BY x
(SELECT 1) UNION SELECT 2 ORDER BY x
SELECT * FROM (((SELECT 1) UNION SELECT 2) ORDER BY x LIMIT 1 OFFSET 1)
@@ -563,6 +558,7 @@ CREATE TABLE foo (bar INT REFERENCES baz(baz_id) ON UPDATE SET DEFAULT)
CREATE TABLE asd AS SELECT asd FROM asd WITH NO DATA
CREATE TABLE asd AS SELECT asd FROM asd WITH DATA
CREATE TABLE products (x INT GENERATED BY DEFAULT AS IDENTITY)
+CREATE TABLE products (x INT GENERATED BY DEFAULT ON NULL AS IDENTITY)
CREATE TABLE products (x INT GENERATED ALWAYS AS IDENTITY)
CREATE TABLE konyvszerzo (szerzo_azon INT CONSTRAINT konyvszerzo_szerzo_fk REFERENCES szerzo)
CREATE TABLE IF NOT EXISTS customer (pk BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (INCREMENT BY 1))
@@ -610,6 +606,7 @@ CREATE FUNCTION a.b(x INT) RETURNS INT AS RETURN x + 1
CREATE FUNCTION a.b.c()
CREATE INDEX abc ON t (a)
CREATE INDEX abc ON t (a, b, b)
+CREATE INDEX abc ON t (a NULLS LAST)
CREATE UNIQUE INDEX abc ON t (a, b, b)
CREATE UNIQUE INDEX IF NOT EXISTS my_idx ON tbl (a, b)
CREATE SCHEMA x
@@ -685,6 +682,7 @@ INSERT OVERWRITE TABLE a.b IF EXISTS SELECT * FROM y
INSERT OVERWRITE DIRECTORY 'x' SELECT 1
INSERT OVERWRITE LOCAL DIRECTORY 'x' SELECT 1
INSERT OVERWRITE LOCAL DIRECTORY 'x' ROW FORMAT DELIMITED FIELDS TERMINATED BY '1' COLLECTION ITEMS TERMINATED BY '2' MAP KEYS TERMINATED BY '3' LINES TERMINATED BY '4' NULL DEFINED AS '5' SELECT 1
+LOAD foo
LOAD DATA INPATH 'x' INTO TABLE y PARTITION(ds = 'yyyy')
LOAD DATA LOCAL INPATH 'x' INTO TABLE y PARTITION(ds = 'yyyy')
LOAD DATA LOCAL INPATH 'x' INTO TABLE y PARTITION(ds = 'yyyy') INPUTFORMAT 'y'
@@ -721,9 +719,9 @@ SELECT ((SELECT 1) + 1)
SELECT * FROM project.dataset.INFORMATION_SCHEMA.TABLES
SELECT * FROM (table1 AS t1 LEFT JOIN table2 AS t2 ON 1 = 1)
SELECT * FROM (tbl1 LEFT JOIN tbl2 ON 1 = 1)
-SELECT * FROM (tbl1 JOIN tbl2 JOIN tbl3)
-SELECT * FROM (tbl1 JOIN (tbl2 JOIN tbl3) ON bla = foo)
-SELECT * FROM (tbl1 JOIN LATERAL (SELECT * FROM bla) AS tbl)
+SELECT * FROM (tbl1, tbl2 JOIN tbl3 ON TRUE)
+SELECT * FROM (tbl1 JOIN (tbl2 CROSS JOIN tbl3) ON bla = foo)
+SELECT * FROM (tbl1, LATERAL (SELECT * FROM bla) AS tbl)
SELECT CAST(x AS INT) /* comment */ FROM foo
SELECT a /* x */, b /* x */
SELECT a /* x */ /* y */ /* z */, b /* k */ /* m */
@@ -826,3 +824,5 @@ SELECT NEXT VALUE FOR db.schema.sequence_name OVER (ORDER BY foo), col
SELECT PERCENTILE_CONT(x, 0.5) OVER ()
SELECT PERCENTILE_CONT(x, 0.5 RESPECT NULLS) OVER ()
SELECT PERCENTILE_CONT(x, 0.5 IGNORE NULLS) OVER ()
+WITH my_cte AS (SELECT 'a' AS desc) SELECT desc AS description FROM my_cte
+WITH my_cte AS (SELECT 'a' AS asc) SELECT asc AS description FROM my_cte
diff --git a/tests/fixtures/optimizer/canonicalize.sql b/tests/fixtures/optimizer/canonicalize.sql
index ccf2f16..1fc44ef 100644
--- a/tests/fixtures/optimizer/canonicalize.sql
+++ b/tests/fixtures/optimizer/canonicalize.sql
@@ -10,8 +10,8 @@ SELECT CAST(1 AS VARCHAR) AS "a" FROM "w" AS "w";
SELECT CAST(1 + 3.2 AS DOUBLE) AS a FROM w AS w;
SELECT 1 + 3.2 AS "a" FROM "w" AS "w";
-SELECT CAST("2022-01-01" AS DATE) + INTERVAL '1' day;
-SELECT CAST("2022-01-01" AS DATE) + INTERVAL '1' day AS "_col_0";
+SELECT CAST('2022-01-01' AS DATE) + INTERVAL '1' day;
+SELECT CAST('2022-01-01' AS DATE) + INTERVAL '1' day AS "_col_0";
--------------------------------------
-- Ensure boolean predicates
diff --git a/tests/fixtures/optimizer/eliminate_subqueries.sql b/tests/fixtures/optimizer/eliminate_subqueries.sql
index 4fa63dd..4c79c22 100644
--- a/tests/fixtures/optimizer/eliminate_subqueries.sql
+++ b/tests/fixtures/optimizer/eliminate_subqueries.sql
@@ -35,8 +35,8 @@ SELECT * FROM (SELECT * FROM (SELECT a FROM x) AS x) AS y JOIN (SELECT * FROM x)
WITH x_2 AS (SELECT a FROM x), y AS (SELECT * FROM x_2 AS x), z AS (SELECT * FROM x) SELECT * FROM y AS y JOIN z AS z ON x.a = y.a;
-- Name conflicts with table alias
-SELECT a FROM (SELECT a FROM (SELECT a FROM x) AS y) AS z JOIN q AS y;
-WITH y AS (SELECT a FROM x), z AS (SELECT a FROM y AS y) SELECT a FROM z AS z JOIN q AS y;
+SELECT a FROM (SELECT a FROM (SELECT a FROM x) AS y) AS z CROSS JOIN q AS y;
+WITH y AS (SELECT a FROM x), z AS (SELECT a FROM y AS y) SELECT a FROM z AS z CROSS JOIN q AS y;
-- Name conflicts with existing CTE
WITH y AS (SELECT a FROM (SELECT a FROM x) AS y) SELECT a FROM y;
@@ -63,12 +63,12 @@ SELECT a FROM x WHERE b = (SELECT c FROM y WHERE y.a = x.a);
SELECT a FROM x WHERE b = (SELECT c FROM y WHERE y.a = x.a);
-- Duplicate CTE
-SELECT a FROM (SELECT b FROM x) AS y JOIN (SELECT b FROM x) AS z;
-WITH y AS (SELECT b FROM x) SELECT a FROM y AS y JOIN y AS z;
+SELECT a FROM (SELECT b FROM x) AS y CROSS JOIN (SELECT b FROM x) AS z;
+WITH y AS (SELECT b FROM x) SELECT a FROM y AS y CROSS JOIN y AS z;
-- Doubly duplicate CTE
SELECT * FROM (SELECT * FROM x JOIN (SELECT * FROM x) AS y) AS z JOIN (SELECT * FROM x JOIN (SELECT * FROM x) AS y) AS q;
-WITH y AS (SELECT * FROM x), z AS (SELECT * FROM x JOIN y AS y) SELECT * FROM z AS z JOIN z AS q;
+WITH y AS (SELECT * FROM x), z AS (SELECT * FROM x, y AS y) SELECT * FROM z AS z, z AS q;
-- Another duplicate...
SELECT x.id FROM (SELECT * FROM x AS x JOIN y AS y ON x.id = y.id) AS x JOIN (SELECT * FROM x AS x JOIN y AS y ON x.id = y.id) AS y ON x.id = y.id;
@@ -79,8 +79,8 @@ WITH x_2 AS (SELECT * FROM x AS x JOIN y AS y ON x.id = y.id) SELECT x.id FROM x
(WITH cte AS (SELECT * FROM x) SELECT * FROM cte AS cte) LIMIT 1;
-- Existing duplicate CTE
-WITH y AS (SELECT a FROM x) SELECT a FROM (SELECT a FROM x) AS y JOIN y AS z;
-WITH y AS (SELECT a FROM x) SELECT a FROM y AS y JOIN y AS z;
+WITH y AS (SELECT a FROM x) SELECT a FROM (SELECT a FROM x) AS y CROSS JOIN y AS z;
+WITH y AS (SELECT a FROM x) SELECT a FROM y AS y CROSS JOIN y AS z;
-- Nested CTE
WITH cte1 AS (SELECT a FROM x) SELECT a FROM (WITH cte2 AS (SELECT a FROM cte1) SELECT a FROM cte2);
diff --git a/tests/fixtures/optimizer/expand_laterals.sql b/tests/fixtures/optimizer/expand_laterals.sql
deleted file mode 100644
index 09bbd0f..0000000
--- a/tests/fixtures/optimizer/expand_laterals.sql
+++ /dev/null
@@ -1,40 +0,0 @@
-# title: expand alias reference
-SELECT
- x.a + 1 AS i,
- i + 1 AS j,
- j + 1 AS k
-FROM x;
-SELECT
- x.a + 1 AS i,
- x.a + 1 + 1 AS j,
- x.a + 1 + 1 + 1 AS k
-FROM x;
-
-# title: noop - reference comes before alias
-SELECT
- b + 1 AS j,
- x.a + 1 AS i
-FROM x;
-SELECT
- b + 1 AS j,
- x.a + 1 AS i
-FROM x;
-
-
-# title: subquery
-SELECT
- *
-FROM (
- SELECT
- x.a + 1 AS i,
- i + 1 AS j
- FROM x
-);
-SELECT
- *
-FROM (
- SELECT
- x.a + 1 AS i,
- x.a + 1 + 1 AS j
- FROM x
-);
diff --git a/tests/fixtures/optimizer/expand_multi_table_selects.sql b/tests/fixtures/optimizer/expand_multi_table_selects.sql
deleted file mode 100644
index a5a4664..0000000
--- a/tests/fixtures/optimizer/expand_multi_table_selects.sql
+++ /dev/null
@@ -1,11 +0,0 @@
---------------------------------------
--- Multi Table Selects
---------------------------------------
-SELECT * FROM x AS x, y AS y WHERE x.a = y.a;
-SELECT * FROM x AS x CROSS JOIN y AS y WHERE x.a = y.a;
-
-SELECT * FROM x AS x, y AS y WHERE x.a = y.a AND x.a = 1 and y.b = 1;
-SELECT * FROM x AS x CROSS JOIN y AS y WHERE x.a = y.a AND x.a = 1 AND y.b = 1;
-
-SELECT * FROM x AS x, y AS y WHERE x.a > y.a;
-SELECT * FROM x AS x CROSS JOIN y AS y WHERE x.a > y.a;
diff --git a/tests/fixtures/optimizer/isolate_table_selects.sql b/tests/fixtures/optimizer/isolate_table_selects.sql
index 93c0f7c..43540e8 100644
--- a/tests/fixtures/optimizer/isolate_table_selects.sql
+++ b/tests/fixtures/optimizer/isolate_table_selects.sql
@@ -4,20 +4,20 @@ SELECT * FROM (SELECT * FROM x AS x) AS x, (SELECT * FROM y AS y) AS y2;
SELECT * FROM x AS x WHERE x = 1;
SELECT * FROM x AS x WHERE x = 1;
-SELECT * FROM x AS x JOIN y AS y;
-SELECT * FROM (SELECT * FROM x AS x) AS x JOIN (SELECT * FROM y AS y) AS y;
+SELECT * FROM x AS x CROSS JOIN y AS y;
+SELECT * FROM (SELECT * FROM x AS x) AS x CROSS JOIN (SELECT * FROM y AS y) AS y;
-SELECT * FROM (SELECT 1) AS x JOIN y AS y;
-SELECT * FROM (SELECT 1) AS x JOIN (SELECT * FROM y AS y) AS y;
+SELECT * FROM (SELECT 1) AS x CROSS JOIN y AS y;
+SELECT * FROM (SELECT 1) AS x CROSS JOIN (SELECT * FROM y AS y) AS y;
SELECT * FROM x AS x JOIN (SELECT * FROM y) AS y;
-SELECT * FROM (SELECT * FROM x AS x) AS x JOIN (SELECT * FROM y) AS y;
+SELECT * FROM (SELECT * FROM x AS x) AS x, (SELECT * FROM y) AS y;
WITH y AS (SELECT *) SELECT * FROM x AS x;
WITH y AS (SELECT *) SELECT * FROM x AS x;
-WITH y AS (SELECT * FROM y AS y2 JOIN x AS z2) SELECT * FROM x AS x JOIN y as y;
-WITH y AS (SELECT * FROM (SELECT * FROM y AS y) AS y2 JOIN (SELECT * FROM x AS x) AS z2) SELECT * FROM (SELECT * FROM x AS x) AS x JOIN y AS y;
+WITH y AS (SELECT * FROM y AS y2 CROSS JOIN x AS z2) SELECT * FROM x AS x CROSS JOIN y as y;
+WITH y AS (SELECT * FROM (SELECT * FROM y AS y) AS y2 CROSS JOIN (SELECT * FROM x AS x) AS z2) SELECT * FROM (SELECT * FROM x AS x) AS x CROSS JOIN y AS y;
-SELECT * FROM x AS x JOIN xx AS y;
-SELECT * FROM (SELECT * FROM x AS x) AS x JOIN xx AS y;
+SELECT * FROM x AS x CROSS JOIN xx AS y;
+SELECT * FROM (SELECT * FROM x AS x) AS x CROSS JOIN xx AS y;
diff --git a/tests/fixtures/optimizer/merge_subqueries.sql b/tests/fixtures/optimizer/merge_subqueries.sql
index 4c06e42..1124a79 100644
--- a/tests/fixtures/optimizer/merge_subqueries.sql
+++ b/tests/fixtures/optimizer/merge_subqueries.sql
@@ -48,8 +48,8 @@ SELECT r.b FROM (SELECT b FROM x AS x) AS q JOIN (SELECT b FROM x) AS r ON q.b =
SELECT x_2.b AS b FROM x AS x JOIN x AS x_2 ON x.b = x_2.b;
# title: WHERE clause in joined derived table is merged to ON clause
-SELECT x.a, y.c FROM x JOIN (SELECT b, c FROM y WHERE c > 1) AS y ON x.b = y.b;
-SELECT x.a AS a, y.c AS c FROM x AS x JOIN y AS y ON x.b = y.b AND y.c > 1;
+SELECT x.a, y.c FROM x JOIN (SELECT b, c FROM y WHERE c > 1) AS y ON x.b = y.b ORDER BY x.a;
+SELECT x.a AS a, y.c AS c FROM x AS x JOIN y AS y ON x.b = y.b AND y.c > 1 ORDER BY x.a;
# title: Comma JOIN in outer query
SELECT x.a, y.c FROM (SELECT a FROM x) AS x, (SELECT c FROM y) AS y;
@@ -57,7 +57,7 @@ SELECT x.a AS a, y.c AS c FROM x AS x, y AS y;
# title: Comma JOIN in inner query
SELECT x.a, x.c FROM (SELECT x.a, z.c FROM x, y AS z) AS x;
-SELECT x.a AS a, z.c AS c FROM x AS x CROSS JOIN y AS z;
+SELECT x.a AS a, z.c AS c FROM x AS x, y AS z;
# title: (Regression) Column in ORDER BY
SELECT * FROM (SELECT * FROM (SELECT * FROM x)) ORDER BY a LIMIT 1;
diff --git a/tests/fixtures/optimizer/normalize.sql b/tests/fixtures/optimizer/normalize.sql
index 803a474..12bc388 100644
--- a/tests/fixtures/optimizer/normalize.sql
+++ b/tests/fixtures/optimizer/normalize.sql
@@ -42,3 +42,6 @@ A OR ((((B OR C) AND (B OR D)) OR C) AND (((B OR C) AND (B OR D)) OR D));
SELECT * FROM x WHERE (A AND B) OR C;
SELECT * FROM x WHERE (A OR C) AND (B OR C);
+
+dt2 between '2022-01-01 12:00:00' and '2022-12-31' and dt2 >= '2022-05-01 12:00:00' or dt2 = '2021-06-01 12:00:00';
+(dt2 <= '2022-12-31' OR dt2 = '2021-06-01 12:00:00') AND (dt2 = '2021-06-01 12:00:00' OR dt2 >= '2022-01-01 12:00:00') AND (dt2 = '2021-06-01 12:00:00' OR dt2 >= '2022-05-01 12:00:00')
diff --git a/tests/fixtures/optimizer/lower_identities.sql b/tests/fixtures/optimizer/normalize_identifiers.sql
index cea346f..ddb755f 100644
--- a/tests/fixtures/optimizer/lower_identities.sql
+++ b/tests/fixtures/optimizer/normalize_identifiers.sql
@@ -1,11 +1,19 @@
SELECT a FROM x;
SELECT a FROM x;
+# dialect: snowflake
+SELECT A FROM X;
+SELECT A FROM X;
+
SELECT "A" FROM "X";
SELECT "A" FROM "X";
SELECT a AS A FROM x;
-SELECT a AS A FROM x;
+SELECT a AS a FROM x;
+
+# dialect: snowflake
+SELECT A AS a FROM X;
+SELECT A AS A FROM X;
SELECT * FROM x;
SELECT * FROM x;
@@ -13,29 +21,37 @@ SELECT * FROM x;
SELECT A FROM x;
SELECT a FROM x;
+# dialect: snowflake
+SELECT a FROM X;
+SELECT A FROM X;
+
SELECT a FROM X;
SELECT a FROM x;
+# dialect: snowflake
+SELECT A FROM x;
+SELECT A FROM X;
+
SELECT A AS A FROM (SELECT a AS A FROM x);
-SELECT a AS A FROM (SELECT a AS a FROM x);
+SELECT a AS a FROM (SELECT a AS a FROM x);
SELECT a AS B FROM x ORDER BY B;
-SELECT a AS B FROM x ORDER BY B;
+SELECT a AS b FROM x ORDER BY b;
SELECT A FROM x ORDER BY A;
SELECT a FROM x ORDER BY a;
SELECT A AS B FROM X GROUP BY A HAVING SUM(B) > 0;
-SELECT a AS B FROM x GROUP BY a HAVING SUM(b) > 0;
+SELECT a AS b FROM x GROUP BY a HAVING SUM(b) > 0;
SELECT A AS B, SUM(B) AS C FROM X GROUP BY A HAVING C > 0;
-SELECT a AS B, SUM(b) AS C FROM x GROUP BY a HAVING C > 0;
+SELECT a AS b, SUM(b) AS c FROM x GROUP BY a HAVING c > 0;
SELECT A FROM X UNION SELECT A FROM X;
SELECT a FROM x UNION SELECT a FROM x;
SELECT A AS A FROM X UNION SELECT A AS A FROM X;
-SELECT a AS A FROM x UNION SELECT a AS A FROM x;
+SELECT a AS a FROM x UNION SELECT a AS a FROM x;
(SELECT A AS A FROM X);
-(SELECT a AS A FROM x);
+(SELECT a AS a FROM x);
diff --git a/tests/fixtures/optimizer/optimize_joins.sql b/tests/fixtures/optimizer/optimize_joins.sql
index b64544e..b8e39c3 100644
--- a/tests/fixtures/optimizer/optimize_joins.sql
+++ b/tests/fixtures/optimizer/optimize_joins.sql
@@ -10,11 +10,23 @@ SELECT * FROM x JOIN z ON x.a = z.a AND TRUE JOIN y ON y.a = z.a;
SELECT * FROM x LEFT JOIN y ON y.a = 1 JOIN z ON x.a = z.a AND y.a = z.a;
SELECT * FROM x JOIN z ON x.a = z.a AND TRUE LEFT JOIN y ON y.a = 1 AND y.a = z.a;
-SELECT * FROM x INNER JOIN z;
-SELECT * FROM x JOIN z;
+SELECT * FROM x INNER JOIN z ON x.id = z.id;
+SELECT * FROM x JOIN z ON x.id = z.id;
SELECT * FROM x LEFT OUTER JOIN z;
SELECT * FROM x LEFT JOIN z;
SELECT * FROM x CROSS JOIN z;
SELECT * FROM x CROSS JOIN z;
+
+SELECT * FROM x JOIN z;
+SELECT * FROM x CROSS JOIN z;
+
+SELECT * FROM x NATURAL JOIN z;
+SELECT * FROM x NATURAL JOIN z;
+
+SELECT * FROM x RIGHT JOIN z;
+SELECT * FROM x RIGHT JOIN z;
+
+SELECT * FROM x JOIN z USING (id);
+SELECT * FROM x JOIN z USING (id);
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index 9e7880c..e0567d7 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -101,10 +101,10 @@ SELECT
"x"."a" AS "a",
SUM("y"."b") AS "sum_b"
FROM "x" AS "x"
-LEFT JOIN "_u_0" AS "_u_0"
- ON "x"."b" = "_u_0"."_u_1"
JOIN "y" AS "y"
ON "x"."b" = "y"."b"
+LEFT JOIN "_u_0" AS "_u_0"
+ ON "x"."b" = "_u_0"."_u_1"
WHERE
"_u_0"."_col_0" >= 0 AND "x"."a" > 1
GROUP BY
@@ -502,3 +502,120 @@ WHERE
"unioned"."source_system" = 'bamboohr' OR "unioned"."source_system" = 'workday'
QUALIFY
ROW_NUMBER() OVER (PARTITION BY "unioned"."unique_filter_key" ORDER BY "unioned"."sort_order" DESC, 1) = 1;
+
+# title: pivoted source with explicit selections
+# execute: false
+SELECT * FROM (SELECT a, b, c FROM sc.tb) PIVOT (SUM(c) FOR b IN ('x','y','z'));
+SELECT
+ "_q_1"."a" AS "a",
+ "_q_1"."x" AS "x",
+ "_q_1"."y" AS "y",
+ "_q_1"."z" AS "z"
+FROM (
+ SELECT
+ "tb"."a" AS "a",
+ "tb"."b" AS "b",
+ "tb"."c" AS "c"
+ FROM "sc"."tb" AS "tb"
+) AS "_q_0" PIVOT(SUM("_q_0"."c") FOR "_q_0"."b" IN ('x', 'y', 'z')) AS "_q_1";
+
+# title: pivoted source with implicit selections
+# execute: false
+SELECT * FROM (SELECT * FROM u) PIVOT (SUM(f) FOR h IN ('x', 'y'));
+SELECT
+ "_q_1"."g" AS "g",
+ "_q_1"."x" AS "x",
+ "_q_1"."y" AS "y"
+FROM (
+ SELECT
+ "u"."f" AS "f",
+ "u"."g" AS "g",
+ "u"."h" AS "h"
+ FROM "u" AS "u"
+) AS "_q_0" PIVOT(SUM("_q_0"."f") FOR "_q_0"."h" IN ('x', 'y')) AS "_q_1";
+
+# title: selecting explicit qualified columns from pivoted source with explicit selections
+# execute: false
+SELECT piv.x, piv.y FROM (SELECT f, h FROM u) PIVOT (SUM(f) FOR h IN ('x', 'y')) AS piv;
+SELECT
+ "piv"."x" AS "x",
+ "piv"."y" AS "y"
+FROM (
+ SELECT
+ "u"."f" AS "f",
+ "u"."h" AS "h"
+ FROM "u" AS "u"
+) AS "_q_0" PIVOT(SUM("_q_0"."f") FOR "_q_0"."h" IN ('x', 'y')) AS "piv";
+
+# title: selecting explicit unqualified columns from pivoted source with implicit selections
+# execute: false
+SELECT x, y FROM u PIVOT (SUM(f) FOR h IN ('x', 'y'));
+SELECT
+ "_q_0"."x" AS "x",
+ "_q_0"."y" AS "y"
+FROM "u" AS "u" PIVOT(SUM("u"."f") FOR "u"."h" IN ('x', 'y')) AS "_q_0";
+
+# title: selecting all columns from a pivoted CTE source, using alias for the aggregation and generating bigquery
+# execute: false
+# dialect: bigquery
+WITH u_cte(f, g, h) AS (SELECT * FROM u) SELECT * FROM u_cte PIVOT(SUM(f) AS sum FOR h IN ('x', 'y'));
+WITH `u_cte` AS (
+ SELECT
+ `u`.`f` AS `f`,
+ `u`.`g` AS `g`,
+ `u`.`h` AS `h`
+ FROM `u` AS `u`
+)
+SELECT
+ `_q_0`.`g` AS `g`,
+ `_q_0`.`sum_x` AS `sum_x`,
+ `_q_0`.`sum_y` AS `sum_y`
+FROM `u_cte` AS `u_cte` PIVOT(SUM(`u_cte`.`f`) AS `sum` FOR `u_cte`.`h` IN ('x', 'y')) AS `_q_0`;
+
+# title: selecting all columns from a pivoted source and generating snowflake
+# execute: false
+# dialect: snowflake
+SELECT * FROM u PIVOT (SUM(f) FOR h IN ('x', 'y'));
+SELECT
+ "_q_0"."G" AS "G",
+ "_q_0"."'x'" AS "'x'",
+ "_q_0"."'y'" AS "'y'"
+FROM "U" AS "U" PIVOT(SUM("U"."F") FOR "U"."H" IN ('x', 'y')) AS "_q_0"
+;
+
+# title: selecting all columns from a pivoted source and generating spark
+# note: spark doesn't allow pivot aliases or qualified columns for the pivot's "field" (`h`)
+# execute: false
+# dialect: spark
+SELECT * FROM u PIVOT (SUM(f) FOR h IN ('x', 'y'));
+SELECT
+ `_q_0`.`g` AS `g`,
+ `_q_0`.`x` AS `x`,
+ `_q_0`.`y` AS `y`
+FROM (
+ SELECT
+ *
+ FROM `u` AS `u` PIVOT(SUM(`u`.`f`) FOR `h` IN ('x', 'y'))
+) AS `_q_0`;
+
+# title: quoting is maintained
+# dialect: snowflake
+with cte1("id", foo) as (select 1, 2) select "id" from cte1;
+WITH "CTE1" AS (
+ SELECT
+ 1 AS "id"
+)
+SELECT
+ "CTE1"."id" AS "id"
+FROM "CTE1";
+
+# title: ensures proper quoting happens after all optimizations
+# execute: false
+SELECT "foO".x FROM (SELECT 1 AS x) AS "foO";
+WITH "foO" AS (
+ SELECT
+ 1 AS "x"
+)
+SELECT
+ "foO"."x" AS "x"
+FROM "foO" AS "foO";
diff --git a/tests/fixtures/optimizer/pushdown_predicates.sql b/tests/fixtures/optimizer/pushdown_predicates.sql
index dd318a2..83a353d 100644
--- a/tests/fixtures/optimizer/pushdown_predicates.sql
+++ b/tests/fixtures/optimizer/pushdown_predicates.sql
@@ -4,8 +4,8 @@ SELECT x.a AS a FROM (SELECT x.a FROM x AS x WHERE x.a = 1 AND x.b = 1) AS x JOI
WITH x AS (SELECT y.a FROM y) SELECT * FROM x WHERE x.a = 1;
WITH x AS (SELECT y.a FROM y WHERE y.a = 1) SELECT * FROM x WHERE TRUE;
-SELECT x.a FROM (SELECT * FROM x) AS x JOIN y WHERE y.a = 1 OR (x.a = 1 AND x.b = 1);
-SELECT x.a FROM (SELECT * FROM x) AS x JOIN y WHERE (x.a = 1 AND x.b = 1) OR y.a = 1;
+SELECT x.a FROM (SELECT * FROM x) AS x CROSS JOIN y WHERE y.a = 1 OR (x.a = 1 AND x.b = 1);
+SELECT x.a FROM (SELECT * FROM x) AS x CROSS JOIN y WHERE (x.a = 1 AND x.b = 1) OR y.a = 1;
SELECT x.a FROM (SELECT * FROM x) AS x JOIN y WHERE (x.a = y.a AND x.a = 1 AND x.b = 1) OR x.a = y.a;
SELECT x.a FROM (SELECT * FROM x) AS x JOIN y ON x.a = y.a WHERE TRUE;
diff --git a/tests/fixtures/optimizer/pushdown_projections.sql b/tests/fixtures/optimizer/pushdown_projections.sql
index 6ff9383..0821339 100644
--- a/tests/fixtures/optimizer/pushdown_projections.sql
+++ b/tests/fixtures/optimizer/pushdown_projections.sql
@@ -61,9 +61,13 @@ SELECT q.x AS x FROM (VALUES (1, 2)) AS q(x, y);
SELECT i.a FROM x AS i LEFT JOIN (SELECT a, b FROM (SELECT a, b FROM x)) AS j ON i.a = j.a;
SELECT i.a AS a FROM x AS i LEFT JOIN (SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0) AS j ON i.a = j.a;
+WITH cte AS (SELECT source.a AS a, ROW_NUMBER() OVER (PARTITION BY source.id, source.timestamp ORDER BY source.a DESC) AS index FROM source AS source QUALIFY index) SELECT cte.a AS a FROM cte;
+WITH cte AS (SELECT source.a AS a FROM source AS source QUALIFY ROW_NUMBER() OVER (PARTITION BY source.id, source.timestamp ORDER BY source.a DESC)) SELECT cte.a AS a FROM cte;
+
--------------------------------------
-- Unknown Star Expansion
--------------------------------------
+
SELECT a FROM (SELECT * FROM zz) WHERE b = 1;
SELECT _q_0.a AS a FROM (SELECT zz.a AS a, zz.b AS b FROM zz AS zz) AS _q_0 WHERE _q_0.b = 1;
diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql
index f077647..7be2c7f 100644
--- a/tests/fixtures/optimizer/qualify_columns.sql
+++ b/tests/fixtures/optimizer/qualify_columns.sql
@@ -5,7 +5,7 @@ SELECT a FROM x;
SELECT x.a AS a FROM x AS x;
SELECT "a" FROM x;
-SELECT x."a" AS "a" FROM x AS x;
+SELECT x.a AS a FROM x AS x;
# execute: false
SELECT a FROM zz GROUP BY a ORDER BY a;
@@ -396,8 +396,39 @@ SELECT x.a + 1 AS a, ROW_NUMBER() OVER (PARTITION BY x.b ORDER BY x.a) AS row_nu
# dialect: bigquery
SELECT ROW_NUMBER() OVER (PARTITION BY a ORDER BY b) AS row_num FROM x QUALIFY row_num = 1;
-SELECT ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.b) AS row_num FROM x AS x QUALIFY row_num = 1;
+SELECT ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.b) AS row_num FROM x AS x QUALIFY ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.b) = 1;
# dialect: bigquery
SELECT x.b, x.a FROM x LEFT JOIN y ON x.b = y.b QUALIFY ROW_NUMBER() OVER(PARTITION BY x.b ORDER BY x.a DESC) = 1;
SELECT x.b AS b, x.a AS a FROM x AS x LEFT JOIN y AS y ON x.b = y.b QUALIFY ROW_NUMBER() OVER (PARTITION BY x.b ORDER BY x.a DESC) = 1;
+
+SELECT * FROM x QUALIFY COUNT(a) OVER (PARTITION BY b) > 1;
+SELECT x.a AS a, x.b AS b FROM x AS x QUALIFY COUNT(x.a) OVER (PARTITION BY x.b) > 1;
+
+--------------------------------------
+-- Expand laterals
+--------------------------------------
+
+# title: expand alias reference
+SELECT
+ x.a + 1 AS i,
+ i + 1 AS j,
+ j + 1 AS k
+FROM x;
+SELECT x.a + 1 AS i, x.a + 1 + 1 AS j, x.a + 1 + 1 + 1 AS k FROM x AS x;
+
+# title: noop - reference comes before alias
+# execute: false
+SELECT i + 1 AS j, x.a + 1 AS i FROM x;
+SELECT i + 1 AS j, x.a + 1 AS i FROM x AS x;
+
+# title: subquery
+SELECT
+ *
+FROM (
+ SELECT
+ x.a + 1 AS i,
+ i + 1 AS j
+ FROM x
+);
+SELECT _q_0.i AS i, _q_0.j AS j FROM (SELECT x.a + 1 AS i, x.a + 1 + 1 AS j FROM x AS x) AS _q_0;
diff --git a/tests/fixtures/optimizer/qualify_columns__invalid.sql b/tests/fixtures/optimizer/qualify_columns__invalid.sql
index f0f9f87..d2d4959 100644
--- a/tests/fixtures/optimizer/qualify_columns__invalid.sql
+++ b/tests/fixtures/optimizer/qualify_columns__invalid.sql
@@ -1,7 +1,7 @@
SELECT z.a FROM x;
SELECT z.* FROM x;
SELECT x FROM x;
-INSERT INTO x VALUES (1, 2);
+SELECT x FROM VALUES (1, 2);
SELECT a FROM x AS z JOIN y AS z;
SELECT a FROM x JOIN (SELECT b FROM y WHERE y.b = x.c);
SELECT a FROM x AS y JOIN (SELECT a FROM y) AS q ON y.a = q.a;
diff --git a/tests/fixtures/optimizer/qualify_columns__with_invisible.sql b/tests/fixtures/optimizer/qualify_columns__with_invisible.sql
index 05253f3..1e07015 100644
--- a/tests/fixtures/optimizer/qualify_columns__with_invisible.sql
+++ b/tests/fixtures/optimizer/qualify_columns__with_invisible.sql
@@ -10,11 +10,11 @@ SELECT x.b AS b FROM x AS x;
--------------------------------------
-- Derived tables
--------------------------------------
-SELECT x.a FROM x AS x JOIN (SELECT * FROM x);
-SELECT x.a AS a FROM x AS x JOIN (SELECT x.a AS a FROM x AS x) AS _q_0;
+SELECT x.a FROM x AS x CROSS JOIN (SELECT * FROM x);
+SELECT x.a AS a FROM x AS x CROSS JOIN (SELECT x.a AS a FROM x AS x) AS _q_0;
-SELECT x.b FROM x AS x JOIN (SELECT b FROM x);
-SELECT x.b AS b FROM x AS x JOIN (SELECT x.b AS b FROM x AS x) AS _q_0;
+SELECT x.b FROM x AS x CROSS JOIN (SELECT b FROM x);
+SELECT x.b AS b FROM x AS x CROSS JOIN (SELECT x.b AS b FROM x AS x) AS _q_0;
--------------------------------------
-- Expand *
@@ -22,11 +22,11 @@ SELECT x.b AS b FROM x AS x JOIN (SELECT x.b AS b FROM x AS x) AS _q_0;
SELECT * FROM x;
SELECT x.a AS a FROM x AS x;
-SELECT * FROM y JOIN z ON y.b = z.b;
-SELECT y.b AS b, z.b AS b FROM y AS y JOIN z AS z ON y.b = z.b;
+SELECT * FROM y CROSS JOIN z ON y.b = z.b;
+SELECT y.b AS b, z.b AS b FROM y AS y CROSS JOIN z AS z ON y.b = z.b;
-SELECT * FROM y JOIN z ON y.c = z.c;
-SELECT y.b AS b, z.b AS b FROM y AS y JOIN z AS z ON y.c = z.c;
+SELECT * FROM y CROSS JOIN z ON y.c = z.c;
+SELECT y.b AS b, z.b AS b FROM y AS y CROSS JOIN z AS z ON y.c = z.c;
SELECT a FROM (SELECT * FROM x);
SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0;
diff --git a/tests/fixtures/optimizer/qualify_tables.sql b/tests/fixtures/optimizer/qualify_tables.sql
index 0ad155a..24d1b65 100644
--- a/tests/fixtures/optimizer/qualify_tables.sql
+++ b/tests/fixtures/optimizer/qualify_tables.sql
@@ -16,9 +16,12 @@ WITH a AS (SELECT 1 FROM c.db.z AS z) SELECT 1 FROM a;
SELECT (SELECT y.c FROM y AS y) FROM x;
SELECT (SELECT y.c FROM c.db.y AS y) FROM c.db.x AS x;
--------------------------
+SELECT * FROM x PIVOT (SUM(a) FOR b IN ('a', 'b'));
+SELECT * FROM c.db.x AS x PIVOT(SUM(a) FOR b IN ('a', 'b')) AS _q_0;
+
+----------------------------
-- Expand join constructs
--------------------------
+----------------------------
-- This is valid in Trino, so we treat the (tbl AS tbl) as a "join construct" per postgres' terminology.
SELECT * FROM (tbl AS tbl) AS _q_0;
diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql
index a2cd859..5c8d371 100644
--- a/tests/fixtures/optimizer/simplify.sql
+++ b/tests/fixtures/optimizer/simplify.sql
@@ -201,6 +201,21 @@ A AND B AND C AND D;
(((((A) AND B)) AND C)) AND D;
A AND B AND C AND D;
+(x + 1) + 2;
+x + 3;
+
+x + (1 + 2);
+x + 3;
+
+(x * 2) * 4 + (1 + 3) + 5;
+x * 8 + 9;
+
+(x - 1) - 2;
+(x - 1) - 2;
+
+x - (3 - 2);
+x - 1;
+
--------------------------------------
-- Comparison and Pruning
--------------------------------------
@@ -574,4 +589,4 @@ x > 3;
TRUE;
x = 2018 OR x <> 2018;
-x <> 2018 OR x = 2018; \ No newline at end of file
+x <> 2018 OR x = 2018;
diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
index 9908756..a6ee325 100644
--- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
+++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
@@ -46,12 +46,12 @@ WITH "customer_total_return" AS (
SELECT
"customer"."c_customer_id" AS "c_customer_id"
FROM "customer_total_return" AS "ctr1"
-LEFT JOIN "_u_0" AS "_u_0"
- ON "ctr1"."ctr_store_sk" = "_u_0"."_u_1"
JOIN "store" AS "store"
ON "store"."s_state" = 'TN' AND "store"."s_store_sk" = "ctr1"."ctr_store_sk"
JOIN "customer" AS "customer"
ON "ctr1"."ctr_customer_sk" = "customer"."c_customer_sk"
+LEFT JOIN "_u_0" AS "_u_0"
+ ON "ctr1"."ctr_store_sk" = "_u_0"."_u_1"
WHERE
"ctr1"."ctr_total_return" > "_u_0"."_col_0"
ORDER BY
@@ -211,9 +211,8 @@ JOIN "date_dim" AS "date_dim"
ON "date_dim"."d_week_seq" = "wswscs"."d_week_seq" AND "date_dim"."d_year" = 1998
JOIN "wswscs" AS "wswscs_2"
ON "wswscs"."d_week_seq" = "wswscs_2"."d_week_seq" - 53
-CROSS JOIN "date_dim" AS "date_dim_2"
-WHERE
- "date_dim_2"."d_week_seq" = "wswscs_2"."d_week_seq" AND "date_dim_2"."d_year" = 1999
+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
ORDER BY
"d_week_seq1";
@@ -953,13 +952,13 @@ JOIN "date_dim" AS "date_dim"
ON "date_dim"."d_month_seq" = "_u_0"."_col_0"
JOIN "store_sales" AS "store_sales"
ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+JOIN "customer" AS "customer"
+ ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk"
+ AND "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
JOIN "item" AS "item"
ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
LEFT JOIN "_u_1" AS "_u_1"
ON "_u_1"."_u_2" = "item"."i_category"
-JOIN "customer" AS "customer"
- ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk"
- AND "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
WHERE
"item"."i_current_price" > 1.2 * "_u_1"."_col_0"
GROUP BY
@@ -1256,9 +1255,9 @@ LIMIT 100;
WITH "a1" AS (
SELECT
SUBSTR("customer_address"."ca_zip", 1, 5) AS "ca_zip"
- FROM "customer_address" AS "customer_address", "customer" AS "customer"
- WHERE
- "customer"."c_preferred_cust_flag" = 'Y'
+ FROM "customer_address" AS "customer_address"
+ JOIN "customer" AS "customer"
+ ON "customer"."c_preferred_cust_flag" = 'Y'
AND "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
GROUP BY
"customer_address"."ca_zip"
@@ -1612,17 +1611,17 @@ SELECT
"customer_demographics"."cd_dep_college_count" AS "cd_dep_college_count",
COUNT(*) AS "cnt6"
FROM "customer" AS "customer"
+JOIN "customer_address" AS "customer_address"
+ ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
+ AND "customer_address"."ca_county" IN ('Lycoming County', 'Sheridan County', 'Kandiyohi County', 'Pike County', 'Greene County')
+JOIN "customer_demographics" AS "customer_demographics"
+ ON "customer_demographics"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
LEFT JOIN "_u_0" AS "_u_0"
ON "customer"."c_customer_sk" = "_u_0"."_u_1"
LEFT JOIN "_u_2" AS "_u_2"
ON "customer"."c_customer_sk" = "_u_2"."_u_3"
LEFT JOIN "_u_4" AS "_u_4"
ON "customer"."c_customer_sk" = "_u_4"."_u_5"
-JOIN "customer_address" AS "customer_address"
- ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
- AND "customer_address"."ca_county" IN ('Lycoming County', 'Sheridan County', 'Kandiyohi County', 'Pike County', 'Greene County')
-JOIN "customer_demographics" AS "customer_demographics"
- ON "customer_demographics"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
WHERE
NOT "_u_0"."_u_1" IS NULL
AND (
@@ -2179,33 +2178,30 @@ WITH "item_2" AS (
"ics"."i_class_id" AS "i_class_id",
"ics"."i_category_id" AS "i_category_id"
FROM "catalog_sales" AS "catalog_sales"
- CROSS JOIN "item_2" AS "ics"
- CROSS JOIN "d1" AS "d2"
- WHERE
- "catalog_sales"."cs_item_sk" = "ics"."i_item_sk"
- AND "catalog_sales"."cs_sold_date_sk" = "d2"."d_date_sk"
+ JOIN "item_2" AS "ics"
+ ON "catalog_sales"."cs_item_sk" = "ics"."i_item_sk"
+ JOIN "d1" AS "d2"
+ ON "catalog_sales"."cs_sold_date_sk" = "d2"."d_date_sk"
INTERSECT
SELECT
"iws"."i_brand_id" AS "i_brand_id",
"iws"."i_class_id" AS "i_class_id",
"iws"."i_category_id" AS "i_category_id"
FROM "web_sales" AS "web_sales"
- CROSS JOIN "item_2" AS "iws"
- CROSS JOIN "d1" AS "d3"
- WHERE
- "web_sales"."ws_item_sk" = "iws"."i_item_sk"
- AND "web_sales"."ws_sold_date_sk" = "d3"."d_date_sk"
+ JOIN "item_2" AS "iws"
+ ON "web_sales"."ws_item_sk" = "iws"."i_item_sk"
+ JOIN "d1" AS "d3"
+ ON "web_sales"."ws_sold_date_sk" = "d3"."d_date_sk"
), "_q_0" AS (
SELECT
"iss"."i_brand_id" AS "brand_id",
"iss"."i_class_id" AS "class_id",
"iss"."i_category_id" AS "category_id"
FROM "store_sales" AS "store_sales"
- CROSS JOIN "item_2" AS "iss"
- CROSS JOIN "d1" AS "d1"
- WHERE
- "store_sales"."ss_item_sk" = "iss"."i_item_sk"
- AND "store_sales"."ss_sold_date_sk" = "d1"."d_date_sk"
+ JOIN "item_2" AS "iss"
+ ON "store_sales"."ss_item_sk" = "iss"."i_item_sk"
+ JOIN "d1" AS "d1"
+ ON "store_sales"."ss_sold_date_sk" = "d1"."d_date_sk"
INTERSECT
SELECT
"cte_4"."i_brand_id" AS "i_brand_id",
@@ -2242,6 +2238,14 @@ WITH "item_2" AS (
SELECT
AVG("x"."quantity" * "x"."list_price") AS "average_sales"
FROM "x" AS "x"
+), "date_dim_2" AS (
+ SELECT
+ "date_dim"."d_date_sk" AS "d_date_sk",
+ "date_dim"."d_year" AS "d_year",
+ "date_dim"."d_moy" AS "d_moy"
+ FROM "date_dim" AS "date_dim"
+ WHERE
+ "date_dim"."d_moy" = 11 AND "date_dim"."d_year" = 2001
), "_u_0" AS (
SELECT
"item"."i_item_sk" AS "ss_item_sk"
@@ -2256,14 +2260,6 @@ WITH "item_2" AS (
SELECT
"avg_sales"."average_sales" AS "average_sales"
FROM "avg_sales"
-), "date_dim_2" AS (
- SELECT
- "date_dim"."d_date_sk" AS "d_date_sk",
- "date_dim"."d_year" AS "d_year",
- "date_dim"."d_moy" AS "d_moy"
- FROM "date_dim" AS "date_dim"
- WHERE
- "date_dim"."d_moy" = 11 AND "date_dim"."d_year" = 2001
), "cte_9" AS (
SELECT
'store' AS "channel",
@@ -2273,13 +2269,13 @@ WITH "item_2" AS (
SUM("store_sales"."ss_quantity" * "store_sales"."ss_list_price") AS "sales",
COUNT(*) AS "number_sales"
FROM "store_sales" AS "store_sales"
- LEFT JOIN "_u_0" AS "_u_0"
- ON "store_sales"."ss_item_sk" = "_u_0"."ss_item_sk"
- CROSS JOIN "_u_1" AS "_u_1"
JOIN "item_2" AS "item"
ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
JOIN "date_dim_2" AS "date_dim"
ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+ LEFT JOIN "_u_0" AS "_u_0"
+ ON "store_sales"."ss_item_sk" = "_u_0"."ss_item_sk"
+ CROSS JOIN "_u_1" AS "_u_1"
WHERE
NOT "_u_0"."ss_item_sk" IS NULL
GROUP BY
@@ -2297,13 +2293,13 @@ WITH "item_2" AS (
SUM("catalog_sales"."cs_quantity" * "catalog_sales"."cs_list_price") AS "sales",
COUNT(*) AS "number_sales"
FROM "catalog_sales" AS "catalog_sales"
- LEFT JOIN "_u_0" AS "_u_2"
- ON "catalog_sales"."cs_item_sk" = "_u_2"."ss_item_sk"
- CROSS JOIN "_u_1" AS "_u_3"
JOIN "item_2" AS "item"
ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
JOIN "date_dim_2" AS "date_dim"
ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
+ LEFT JOIN "_u_0" AS "_u_2"
+ ON "catalog_sales"."cs_item_sk" = "_u_2"."ss_item_sk"
+ CROSS JOIN "_u_1" AS "_u_3"
WHERE
NOT "_u_2"."ss_item_sk" IS NULL
GROUP BY
@@ -2321,13 +2317,13 @@ WITH "item_2" AS (
SUM("web_sales"."ws_quantity" * "web_sales"."ws_list_price") AS "sales",
COUNT(*) AS "number_sales"
FROM "web_sales" AS "web_sales"
- LEFT JOIN "_u_0" AS "_u_4"
- ON "web_sales"."ws_item_sk" = "_u_4"."ss_item_sk"
- CROSS JOIN "_u_1" AS "_u_5"
JOIN "item_2" AS "item"
ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
JOIN "date_dim_2" AS "date_dim"
ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
+ LEFT JOIN "_u_0" AS "_u_4"
+ ON "web_sales"."ws_item_sk" = "_u_4"."ss_item_sk"
+ CROSS JOIN "_u_1" AS "_u_5"
WHERE
NOT "_u_4"."ss_item_sk" IS NULL
GROUP BY
@@ -2492,10 +2488,6 @@ SELECT
SUM("catalog_sales"."cs_ext_ship_cost") AS "total shipping cost",
SUM("catalog_sales"."cs_net_profit") AS "total net profit"
FROM "catalog_sales" AS "catalog_sales"
-LEFT JOIN "_u_0" AS "_u_0"
- ON "catalog_sales"."cs_order_number" = "_u_0"."_u_1"
-LEFT JOIN "_u_3" AS "_u_3"
- ON "catalog_sales"."cs_order_number" = "_u_3"."_u_4"
JOIN "date_dim" AS "date_dim"
ON "catalog_sales"."cs_ship_date_sk" = "date_dim"."d_date_sk"
AND "date_dim"."d_date" >= '2002-3-01'
@@ -2508,6 +2500,10 @@ JOIN "customer_address" AS "customer_address"
JOIN "call_center" AS "call_center"
ON "call_center"."cc_county" IN ('Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County')
AND "catalog_sales"."cs_call_center_sk" = "call_center"."cc_call_center_sk"
+LEFT JOIN "_u_0" AS "_u_0"
+ ON "catalog_sales"."cs_order_number" = "_u_0"."_u_1"
+LEFT JOIN "_u_3" AS "_u_3"
+ ON "catalog_sales"."cs_order_number" = "_u_3"."_u_4"
WHERE
"_u_3"."_u_4" IS NULL
AND ARRAY_ANY("_u_0"."_u_2", "_x" -> "catalog_sales"."cs_warehouse_sk" <> "_x")
@@ -3012,13 +3008,21 @@ WITH "frequent_ss_items" AS (
SELECT
"customer"."c_customer_sk" AS "c_customer_sk"
FROM "store_sales" AS "store_sales"
- CROSS JOIN "max_store_sales"
JOIN "customer_2" AS "customer"
ON "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
+ CROSS JOIN "max_store_sales"
GROUP BY
"customer"."c_customer_sk"
HAVING
SUM("store_sales"."ss_quantity" * "store_sales"."ss_sales_price") > 0.95 * MAX("max_store_sales"."tpcds_cmax")
+), "date_dim_4" AS (
+ SELECT
+ "date_dim"."d_date_sk" AS "d_date_sk",
+ "date_dim"."d_year" AS "d_year",
+ "date_dim"."d_moy" AS "d_moy"
+ FROM "date_dim" AS "date_dim"
+ WHERE
+ "date_dim"."d_moy" = 6 AND "date_dim"."d_year" = 1998
), "_u_1" AS (
SELECT
"frequent_ss_items"."item_sk" AS "item_sk"
@@ -3031,36 +3035,28 @@ WITH "frequent_ss_items" AS (
FROM "best_ss_customer"
GROUP BY
"best_ss_customer"."c_customer_sk"
-), "date_dim_4" AS (
- SELECT
- "date_dim"."d_date_sk" AS "d_date_sk",
- "date_dim"."d_year" AS "d_year",
- "date_dim"."d_moy" AS "d_moy"
- FROM "date_dim" AS "date_dim"
- WHERE
- "date_dim"."d_moy" = 6 AND "date_dim"."d_year" = 1998
), "_q_1" AS (
SELECT
"catalog_sales"."cs_quantity" * "catalog_sales"."cs_list_price" AS "sales"
FROM "catalog_sales" AS "catalog_sales"
+ JOIN "date_dim_4" AS "date_dim"
+ ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
LEFT JOIN "_u_1" AS "_u_1"
ON "catalog_sales"."cs_item_sk" = "_u_1"."item_sk"
LEFT JOIN "_u_2" AS "_u_2"
ON "catalog_sales"."cs_bill_customer_sk" = "_u_2"."c_customer_sk"
- JOIN "date_dim_4" AS "date_dim"
- ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
WHERE
NOT "_u_1"."item_sk" IS NULL AND NOT "_u_2"."c_customer_sk" IS NULL
UNION ALL
SELECT
"web_sales"."ws_quantity" * "web_sales"."ws_list_price" AS "sales"
FROM "web_sales" AS "web_sales"
+ JOIN "date_dim_4" AS "date_dim"
+ ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
LEFT JOIN "_u_1" AS "_u_3"
ON "web_sales"."ws_item_sk" = "_u_3"."item_sk"
LEFT JOIN "_u_2" AS "_u_4"
ON "web_sales"."ws_bill_customer_sk" = "_u_4"."c_customer_sk"
- JOIN "date_dim_4" AS "date_dim"
- ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
WHERE
NOT "_u_3"."item_sk" IS NULL AND NOT "_u_4"."c_customer_sk" IS NULL
)
@@ -3717,13 +3713,13 @@ SELECT
"customer"."c_last_review_date" AS "c_last_review_date",
"ctr1"."ctr_total_return" AS "ctr_total_return"
FROM "customer_total_return" AS "ctr1"
-LEFT JOIN "_u_0" AS "_u_0"
- ON "ctr1"."ctr_state" = "_u_0"."_u_1"
JOIN "customer" AS "customer"
ON "ctr1"."ctr_customer_sk" = "customer"."c_customer_sk"
JOIN "customer_address" AS "customer_address"
ON "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
AND "customer_address"."ca_state" = 'IN'
+LEFT JOIN "_u_0" AS "_u_0"
+ ON "ctr1"."ctr_state" = "_u_0"."_u_1"
WHERE
"ctr1"."ctr_total_return" > "_u_0"."_col_0"
ORDER BY
@@ -3957,10 +3953,10 @@ SELECT
FROM "catalog_sales_2" AS "catalog_sales"
JOIN "item" AS "item"
ON "item"."i_item_sk" = "catalog_sales"."cs_item_sk" AND "item"."i_manufact_id" = 610
-LEFT JOIN "_u_0" AS "_u_0"
- ON "_u_0"."_u_1" = "item"."i_item_sk"
JOIN "date_dim_2" AS "date_dim"
ON "date_dim"."d_date_sk" = "catalog_sales"."cs_sold_date_sk"
+LEFT JOIN "_u_0" AS "_u_0"
+ ON "_u_0"."_u_1" = "item"."i_item_sk"
WHERE
"catalog_sales"."cs_ext_discount_amt" > "_u_0"."_col_0"
LIMIT 100;
@@ -4032,20 +4028,7 @@ FROM (SELECT *
GROUP BY i_manufact_id
ORDER BY total_sales
LIMIT 100;
-WITH "item_2" AS (
- SELECT
- "item"."i_item_sk" AS "i_item_sk",
- "item"."i_manufact_id" AS "i_manufact_id"
- FROM "item" AS "item"
-), "_u_0" AS (
- SELECT
- "item"."i_manufact_id" AS "i_manufact_id"
- FROM "item" AS "item"
- WHERE
- "item"."i_category" IN ('Books')
- GROUP BY
- "item"."i_manufact_id"
-), "date_dim_2" AS (
+WITH "date_dim_2" AS (
SELECT
"date_dim"."d_date_sk" AS "d_date_sk",
"date_dim"."d_year" AS "d_year",
@@ -4060,19 +4043,32 @@ WITH "item_2" AS (
FROM "customer_address" AS "customer_address"
WHERE
"customer_address"."ca_gmt_offset" = -5
+), "item_2" AS (
+ SELECT
+ "item"."i_item_sk" AS "i_item_sk",
+ "item"."i_manufact_id" AS "i_manufact_id"
+ FROM "item" AS "item"
+), "_u_0" AS (
+ SELECT
+ "item"."i_manufact_id" AS "i_manufact_id"
+ FROM "item" AS "item"
+ WHERE
+ "item"."i_category" IN ('Books')
+ GROUP BY
+ "item"."i_manufact_id"
), "ss" AS (
SELECT
"item"."i_manufact_id" AS "i_manufact_id",
SUM("store_sales"."ss_ext_sales_price") AS "total_sales"
FROM "store_sales" AS "store_sales"
- JOIN "item_2" AS "item"
- ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
- LEFT JOIN "_u_0" AS "_u_0"
- ON "item"."i_manufact_id" = "_u_0"."i_manufact_id"
JOIN "date_dim_2" AS "date_dim"
ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
JOIN "customer_address_2" AS "customer_address"
ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
+ JOIN "item_2" AS "item"
+ ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+ LEFT JOIN "_u_0" AS "_u_0"
+ ON "item"."i_manufact_id" = "_u_0"."i_manufact_id"
WHERE
NOT "_u_0"."i_manufact_id" IS NULL
GROUP BY
@@ -4082,14 +4078,14 @@ WITH "item_2" AS (
"item"."i_manufact_id" AS "i_manufact_id",
SUM("catalog_sales"."cs_ext_sales_price") AS "total_sales"
FROM "catalog_sales" AS "catalog_sales"
- JOIN "item_2" AS "item"
- ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
- LEFT JOIN "_u_0" AS "_u_1"
- ON "item"."i_manufact_id" = "_u_1"."i_manufact_id"
JOIN "date_dim_2" AS "date_dim"
ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
JOIN "customer_address_2" AS "customer_address"
ON "catalog_sales"."cs_bill_addr_sk" = "customer_address"."ca_address_sk"
+ JOIN "item_2" AS "item"
+ ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
+ LEFT JOIN "_u_0" AS "_u_1"
+ ON "item"."i_manufact_id" = "_u_1"."i_manufact_id"
WHERE
NOT "_u_1"."i_manufact_id" IS NULL
GROUP BY
@@ -4099,14 +4095,14 @@ WITH "item_2" AS (
"item"."i_manufact_id" AS "i_manufact_id",
SUM("web_sales"."ws_ext_sales_price") AS "total_sales"
FROM "web_sales" AS "web_sales"
- JOIN "item_2" AS "item"
- ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
- LEFT JOIN "_u_0" AS "_u_2"
- ON "item"."i_manufact_id" = "_u_2"."i_manufact_id"
JOIN "date_dim_2" AS "date_dim"
ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
JOIN "customer_address_2" AS "customer_address"
ON "web_sales"."ws_bill_addr_sk" = "customer_address"."ca_address_sk"
+ JOIN "item_2" AS "item"
+ ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
+ LEFT JOIN "_u_0" AS "_u_2"
+ ON "item"."i_manufact_id" = "_u_2"."i_manufact_id"
WHERE
NOT "_u_2"."i_manufact_id" IS NULL
GROUP BY
@@ -4382,16 +4378,16 @@ SELECT
AVG("customer_demographics"."cd_dep_college_count") AS "_col_16",
MAX("customer_demographics"."cd_dep_college_count") AS "_col_17"
FROM "customer" AS "customer"
+JOIN "customer_address" AS "customer_address"
+ ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
+JOIN "customer_demographics" AS "customer_demographics"
+ ON "customer_demographics"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
LEFT JOIN "_u_0" AS "_u_0"
ON "customer"."c_customer_sk" = "_u_0"."_u_1"
LEFT JOIN "_u_2" AS "_u_2"
ON "customer"."c_customer_sk" = "_u_2"."_u_3"
LEFT JOIN "_u_4" AS "_u_4"
ON "customer"."c_customer_sk" = "_u_4"."_u_5"
-JOIN "customer_address" AS "customer_address"
- ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
-JOIN "customer_demographics" AS "customer_demographics"
- ON "customer_demographics"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
WHERE
NOT "_u_0"."_u_1" IS NULL
AND (
@@ -4468,7 +4464,7 @@ ROLLUP (
)
ORDER BY
"lochierarchy" DESC,
- CASE WHEN "lochierarchy" = 0 THEN "item"."i_category" END,
+ CASE WHEN "lochierarchy" = 0 THEN "i_category" END,
"rank_within_parent"
LIMIT 100;
@@ -5349,10 +5345,6 @@ SELECT
"customer_address"."ca_state" AS "ca_state",
SUM("web_sales"."ws_sales_price") AS "_col_2"
FROM "web_sales" AS "web_sales"
-JOIN "item" AS "item"
- ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
-LEFT JOIN "_u_0" AS "_u_0"
- ON "item"."i_item_id" = "_u_0"."i_item_id"
JOIN "customer" AS "customer"
ON "web_sales"."ws_bill_customer_sk" = "customer"."c_customer_sk"
JOIN "customer_address" AS "customer_address"
@@ -5361,6 +5353,10 @@ JOIN "date_dim" AS "date_dim"
ON "date_dim"."d_qoy" = 1
AND "date_dim"."d_year" = 2000
AND "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
+JOIN "item" AS "item"
+ ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
+LEFT JOIN "_u_0" AS "_u_0"
+ ON "item"."i_item_id" = "_u_0"."i_item_id"
WHERE
NOT "_u_0"."i_item_id" IS NULL
OR SUBSTR("customer_address"."ca_zip", 1, 5) IN ('85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792')
@@ -6145,7 +6141,7 @@ WITH web_v1 AS
(
SELECT ws_item_sk item_sk,
d_date,
- sum(Sum(ws_sales_price)) OVER (partition BY ws_item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cume_sales
+ sum(Sum(ws_sales_price)) OVER (partition BY ws_item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row) cume_sales
FROM web_sales ,
date_dim
WHERE ws_sold_date_sk=d_date_sk
@@ -6156,7 +6152,7 @@ WITH web_v1 AS
(
SELECT ss_item_sk item_sk,
d_date,
- sum(sum(ss_sales_price)) OVER (partition BY ss_item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cume_sales
+ sum(sum(ss_sales_price)) OVER (partition BY ss_item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row) cume_sales
FROM store_sales ,
date_dim
WHERE ss_sold_date_sk=d_date_sk
@@ -6171,8 +6167,8 @@ FROM (
d_date ,
web_sales ,
store_sales ,
- max(web_sales) OVER (partition BY item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) web_cumulative ,
- max(store_sales) OVER (partition BY item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) store_cumulative
+ max(web_sales) OVER (partition BY item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row) web_cumulative ,
+ max(store_sales) OVER (partition BY item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row) store_cumulative
FROM (
SELECT
CASE
@@ -6533,10 +6529,6 @@ WITH "cs_or_ws_sales" AS (
SUM("store_sales"."ss_ext_sales_price") AS "revenue"
FROM "my_customers"
CROSS JOIN "date_dim" AS "date_dim"
- JOIN "_u_0" AS "_u_0"
- ON "date_dim"."d_month_seq" >= "_u_0"."_col_0"
- JOIN "_u_1" AS "_u_1"
- ON "date_dim"."d_month_seq" <= "_u_1"."_col_0"
JOIN "store_sales" AS "store_sales"
ON "my_customers"."c_customer_sk" = "store_sales"."ss_customer_sk"
AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
@@ -6545,6 +6537,10 @@ WITH "cs_or_ws_sales" AS (
JOIN "store" AS "store"
ON "customer_address"."ca_county" = "store"."s_county"
AND "customer_address"."ca_state" = "store"."s_state"
+ JOIN "_u_0" AS "_u_0"
+ ON "date_dim"."d_month_seq" >= "_u_0"."_col_0"
+ JOIN "_u_1" AS "_u_1"
+ ON "date_dim"."d_month_seq" <= "_u_1"."_col_0"
GROUP BY
"my_customers"."c_customer_sk"
)
@@ -6674,20 +6670,7 @@ FROM (SELECT *
GROUP BY i_item_id
ORDER BY total_sales
LIMIT 100;
-WITH "item_2" AS (
- SELECT
- "item"."i_item_sk" AS "i_item_sk",
- "item"."i_item_id" AS "i_item_id"
- FROM "item" AS "item"
-), "_u_0" AS (
- SELECT
- "item"."i_item_id" AS "i_item_id"
- FROM "item" AS "item"
- WHERE
- "item"."i_color" IN ('firebrick', 'rosy', 'white')
- GROUP BY
- "item"."i_item_id"
-), "date_dim_2" AS (
+WITH "date_dim_2" AS (
SELECT
"date_dim"."d_date_sk" AS "d_date_sk",
"date_dim"."d_year" AS "d_year",
@@ -6702,19 +6685,32 @@ WITH "item_2" AS (
FROM "customer_address" AS "customer_address"
WHERE
"customer_address"."ca_gmt_offset" = -6
+), "item_2" AS (
+ SELECT
+ "item"."i_item_sk" AS "i_item_sk",
+ "item"."i_item_id" AS "i_item_id"
+ FROM "item" AS "item"
+), "_u_0" AS (
+ SELECT
+ "item"."i_item_id" AS "i_item_id"
+ FROM "item" AS "item"
+ WHERE
+ "item"."i_color" IN ('firebrick', 'rosy', 'white')
+ GROUP BY
+ "item"."i_item_id"
), "ss" AS (
SELECT
"item"."i_item_id" AS "i_item_id",
SUM("store_sales"."ss_ext_sales_price") AS "total_sales"
FROM "store_sales" AS "store_sales"
- JOIN "item_2" AS "item"
- ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
- LEFT JOIN "_u_0" AS "_u_0"
- ON "item"."i_item_id" = "_u_0"."i_item_id"
JOIN "date_dim_2" AS "date_dim"
ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
JOIN "customer_address_2" AS "customer_address"
ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
+ JOIN "item_2" AS "item"
+ ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+ LEFT JOIN "_u_0" AS "_u_0"
+ ON "item"."i_item_id" = "_u_0"."i_item_id"
WHERE
NOT "_u_0"."i_item_id" IS NULL
GROUP BY
@@ -6724,14 +6720,14 @@ WITH "item_2" AS (
"item"."i_item_id" AS "i_item_id",
SUM("catalog_sales"."cs_ext_sales_price") AS "total_sales"
FROM "catalog_sales" AS "catalog_sales"
- JOIN "item_2" AS "item"
- ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
- LEFT JOIN "_u_0" AS "_u_1"
- ON "item"."i_item_id" = "_u_1"."i_item_id"
JOIN "date_dim_2" AS "date_dim"
ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
JOIN "customer_address_2" AS "customer_address"
ON "catalog_sales"."cs_bill_addr_sk" = "customer_address"."ca_address_sk"
+ JOIN "item_2" AS "item"
+ ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
+ LEFT JOIN "_u_0" AS "_u_1"
+ ON "item"."i_item_id" = "_u_1"."i_item_id"
WHERE
NOT "_u_1"."i_item_id" IS NULL
GROUP BY
@@ -6741,14 +6737,14 @@ WITH "item_2" AS (
"item"."i_item_id" AS "i_item_id",
SUM("web_sales"."ws_ext_sales_price") AS "total_sales"
FROM "web_sales" AS "web_sales"
- JOIN "item_2" AS "item"
- ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
- LEFT JOIN "_u_0" AS "_u_2"
- ON "item"."i_item_id" = "_u_2"."i_item_id"
JOIN "date_dim_2" AS "date_dim"
ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
JOIN "customer_address_2" AS "customer_address"
ON "web_sales"."ws_bill_addr_sk" = "customer_address"."ca_address_sk"
+ JOIN "item_2" AS "item"
+ ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
+ LEFT JOIN "_u_0" AS "_u_2"
+ ON "item"."i_item_id" = "_u_2"."i_item_id"
WHERE
NOT "_u_2"."i_item_id" IS NULL
GROUP BY
@@ -7075,12 +7071,13 @@ WITH "wss" AS (
"wss"."thu_sales" AS "thu_sales2",
"wss"."fri_sales" AS "fri_sales2",
"wss"."sat_sales" AS "sat_sales2"
- FROM "wss", "store" AS "store", "date_dim" AS "date_dim"
- WHERE
- "date_dim"."d_month_seq" <= 1219
+ FROM "wss"
+ JOIN "store" AS "store"
+ ON "wss"."ss_store_sk" = "store"."s_store_sk"
+ JOIN "date_dim" AS "date_dim"
+ ON "date_dim"."d_month_seq" <= 1219
AND "date_dim"."d_month_seq" >= 1208
AND "date_dim"."d_week_seq" = "wss"."d_week_seq"
- AND "wss"."ss_store_sk" = "store"."s_store_sk"
)
SELECT
"store"."s_store_name" AS "s_store_name1",
@@ -7177,20 +7174,7 @@ GROUP BY i_item_id
ORDER BY i_item_id,
total_sales
LIMIT 100;
-WITH "item_2" AS (
- SELECT
- "item"."i_item_sk" AS "i_item_sk",
- "item"."i_item_id" AS "i_item_id"
- FROM "item" AS "item"
-), "_u_0" AS (
- SELECT
- "item"."i_item_id" AS "i_item_id"
- FROM "item" AS "item"
- WHERE
- "item"."i_category" IN ('Jewelry')
- GROUP BY
- "item"."i_item_id"
-), "date_dim_2" AS (
+WITH "date_dim_2" AS (
SELECT
"date_dim"."d_date_sk" AS "d_date_sk",
"date_dim"."d_year" AS "d_year",
@@ -7205,19 +7189,32 @@ WITH "item_2" AS (
FROM "customer_address" AS "customer_address"
WHERE
"customer_address"."ca_gmt_offset" = -6
+), "item_2" AS (
+ SELECT
+ "item"."i_item_sk" AS "i_item_sk",
+ "item"."i_item_id" AS "i_item_id"
+ FROM "item" AS "item"
+), "_u_0" AS (
+ SELECT
+ "item"."i_item_id" AS "i_item_id"
+ FROM "item" AS "item"
+ WHERE
+ "item"."i_category" IN ('Jewelry')
+ GROUP BY
+ "item"."i_item_id"
), "ss" AS (
SELECT
"item"."i_item_id" AS "i_item_id",
SUM("store_sales"."ss_ext_sales_price") AS "total_sales"
FROM "store_sales" AS "store_sales"
- JOIN "item_2" AS "item"
- ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
- LEFT JOIN "_u_0" AS "_u_0"
- ON "item"."i_item_id" = "_u_0"."i_item_id"
JOIN "date_dim_2" AS "date_dim"
ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
JOIN "customer_address_2" AS "customer_address"
ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
+ JOIN "item_2" AS "item"
+ ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+ LEFT JOIN "_u_0" AS "_u_0"
+ ON "item"."i_item_id" = "_u_0"."i_item_id"
WHERE
NOT "_u_0"."i_item_id" IS NULL
GROUP BY
@@ -7227,14 +7224,14 @@ WITH "item_2" AS (
"item"."i_item_id" AS "i_item_id",
SUM("catalog_sales"."cs_ext_sales_price") AS "total_sales"
FROM "catalog_sales" AS "catalog_sales"
- JOIN "item_2" AS "item"
- ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
- LEFT JOIN "_u_0" AS "_u_1"
- ON "item"."i_item_id" = "_u_1"."i_item_id"
JOIN "date_dim_2" AS "date_dim"
ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
JOIN "customer_address_2" AS "customer_address"
ON "catalog_sales"."cs_bill_addr_sk" = "customer_address"."ca_address_sk"
+ JOIN "item_2" AS "item"
+ ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
+ LEFT JOIN "_u_0" AS "_u_1"
+ ON "item"."i_item_id" = "_u_1"."i_item_id"
WHERE
NOT "_u_1"."i_item_id" IS NULL
GROUP BY
@@ -7244,14 +7241,14 @@ WITH "item_2" AS (
"item"."i_item_id" AS "i_item_id",
SUM("web_sales"."ws_ext_sales_price") AS "total_sales"
FROM "web_sales" AS "web_sales"
- JOIN "item_2" AS "item"
- ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
- LEFT JOIN "_u_0" AS "_u_2"
- ON "item"."i_item_id" = "_u_2"."i_item_id"
JOIN "date_dim_2" AS "date_dim"
ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
JOIN "customer_address_2" AS "customer_address"
ON "web_sales"."ws_bill_addr_sk" = "customer_address"."ca_address_sk"
+ JOIN "item_2" AS "item"
+ ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
+ LEFT JOIN "_u_0" AS "_u_2"
+ ON "item"."i_item_id" = "_u_2"."i_item_id"
WHERE
NOT "_u_2"."i_item_id" IS NULL
GROUP BY
@@ -7395,13 +7392,17 @@ WITH "store_2" AS (
), "all_sales" AS (
SELECT
SUM("store_sales"."ss_ext_sales_price") AS "total"
- FROM "store_sales" AS "store_sales", "store_2" AS "store", "date_dim_2" AS "date_dim", "customer_2" AS "customer", "customer_address_2" AS "customer_address", "item_2" AS "item"
- WHERE
- "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
- AND "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
- AND "store_sales"."ss_item_sk" = "item"."i_item_sk"
- AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
- AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+ FROM "store_sales" AS "store_sales"
+ JOIN "store_2" AS "store"
+ ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
+ JOIN "date_dim_2" AS "date_dim"
+ ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+ JOIN "customer_2" AS "customer"
+ ON "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
+ JOIN "customer_address_2" AS "customer_address"
+ ON "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
+ JOIN "item_2" AS "item"
+ ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
)
SELECT
"promotional_sales"."promotions" AS "promotions",
@@ -9111,17 +9112,17 @@ SELECT
"customer_demographics"."cd_credit_rating" AS "cd_credit_rating",
COUNT(*) AS "cnt3"
FROM "customer" AS "customer"
+JOIN "customer_address" AS "customer_address"
+ ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
+ AND "customer_address"."ca_state" IN ('KS', 'AZ', 'NE')
+JOIN "customer_demographics" AS "customer_demographics"
+ ON "customer_demographics"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
LEFT JOIN "_u_0" AS "_u_0"
ON "customer"."c_customer_sk" = "_u_0"."_u_1"
LEFT JOIN "_u_2" AS "_u_2"
ON "customer"."c_customer_sk" = "_u_2"."_u_3"
LEFT JOIN "_u_4" AS "_u_4"
ON "customer"."c_customer_sk" = "_u_4"."_u_5"
-JOIN "customer_address" AS "customer_address"
- ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
- AND "customer_address"."ca_state" IN ('KS', 'AZ', 'NE')
-JOIN "customer_demographics" AS "customer_demographics"
- ON "customer_demographics"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
WHERE
"_u_2"."_u_3" IS NULL AND "_u_4"."_u_5" IS NULL AND NOT "_u_0"."_u_1" IS NULL
GROUP BY
@@ -9186,7 +9187,7 @@ WITH "store_sales_2" AS (
"store_sales"."ss_store_sk" AS "ss_store_sk",
"store_sales"."ss_net_profit" AS "ss_net_profit"
FROM "store_sales" AS "store_sales"
-), "date_dim_2" AS (
+), "d1" AS (
SELECT
"date_dim"."d_date_sk" AS "d_date_sk",
"date_dim"."d_month_seq" AS "d_month_seq"
@@ -9200,7 +9201,7 @@ WITH "store_sales_2" AS (
FROM "store_sales_2" AS "store_sales"
JOIN "store" AS "store"
ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
- JOIN "date_dim_2" AS "date_dim"
+ JOIN "d1" AS "date_dim"
ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
GROUP BY
"store"."s_state"
@@ -9220,12 +9221,12 @@ SELECT
GROUPING("store"."s_state") + GROUPING("store"."s_county") AS "lochierarchy",
RANK() OVER (PARTITION BY GROUPING("store"."s_state") + GROUPING("store"."s_county"), CASE WHEN GROUPING("store"."s_county") = 0 THEN "store"."s_state" END ORDER BY SUM("store_sales"."ss_net_profit") DESC) AS "rank_within_parent"
FROM "store_sales_2" AS "store_sales"
+JOIN "d1" AS "d1"
+ ON "d1"."d_date_sk" = "store_sales"."ss_sold_date_sk"
JOIN "store" AS "store"
ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
LEFT JOIN "_u_0" AS "_u_0"
ON "store"."s_state" = "_u_0"."s_state"
-JOIN "date_dim_2" AS "d1"
- ON "d1"."d_date_sk" = "store_sales"."ss_sold_date_sk"
WHERE
NOT "_u_0"."s_state" IS NULL
GROUP BY
@@ -9235,7 +9236,7 @@ ROLLUP (
)
ORDER BY
"lochierarchy" DESC,
- CASE WHEN "lochierarchy" = 0 THEN "store"."s_state" END,
+ CASE WHEN "lochierarchy" = 0 THEN "s_state" END,
"rank_within_parent"
LIMIT 100;
@@ -9303,27 +9304,24 @@ WITH "date_dim_2" AS (
"catalog_sales"."cs_item_sk" AS "sold_item_sk",
"catalog_sales"."cs_sold_time_sk" AS "time_sk"
FROM "catalog_sales" AS "catalog_sales"
- CROSS JOIN "date_dim_2" AS "date_dim"
- WHERE
- "date_dim"."d_date_sk" = "catalog_sales"."cs_sold_date_sk"
+ JOIN "date_dim_2" AS "date_dim"
+ ON "date_dim"."d_date_sk" = "catalog_sales"."cs_sold_date_sk"
UNION ALL
SELECT
"store_sales"."ss_ext_sales_price" AS "ext_price",
"store_sales"."ss_item_sk" AS "sold_item_sk",
"store_sales"."ss_sold_time_sk" AS "time_sk"
FROM "store_sales" AS "store_sales"
- CROSS JOIN "date_dim_2" AS "date_dim"
- WHERE
- "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+ JOIN "date_dim_2" AS "date_dim"
+ ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
), "tmp" AS (
SELECT
"web_sales"."ws_ext_sales_price" AS "ext_price",
"web_sales"."ws_item_sk" AS "sold_item_sk",
"web_sales"."ws_sold_time_sk" AS "time_sk"
FROM "web_sales" AS "web_sales"
- CROSS JOIN "date_dim_2" AS "date_dim"
- WHERE
- "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
+ JOIN "date_dim_2" AS "date_dim"
+ ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
UNION ALL
SELECT
"cte_4"."ext_price" AS "ext_price",
@@ -10932,13 +10930,13 @@ SELECT
"customer_address"."ca_location_type" AS "ca_location_type",
"ctr1"."ctr_total_return" AS "ctr_total_return"
FROM "customer_total_return" AS "ctr1"
-LEFT JOIN "_u_0" AS "_u_0"
- ON "ctr1"."ctr_state" = "_u_0"."_u_1"
JOIN "customer" AS "customer"
ON "ctr1"."ctr_customer_sk" = "customer"."c_customer_sk"
JOIN "customer_address" AS "customer_address"
ON "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
AND "customer_address"."ca_state" = 'TX'
+LEFT JOIN "_u_0" AS "_u_0"
+ ON "ctr1"."ctr_state" = "_u_0"."_u_1"
WHERE
"ctr1"."ctr_total_return" > "_u_0"."_col_0"
ORDER BY
@@ -11089,7 +11087,12 @@ WHERE sr_items.item_id = cr_items.item_id
ORDER BY sr_items.item_id,
sr_item_qty
LIMIT 100;
-WITH "date_dim_2" AS (
+WITH "item_2" AS (
+ SELECT
+ "item"."i_item_sk" AS "i_item_sk",
+ "item"."i_item_id" AS "i_item_id"
+ FROM "item" AS "item"
+), "date_dim_2" AS (
SELECT
"date_dim"."d_date_sk" AS "d_date_sk",
"date_dim"."d_date" AS "d_date"
@@ -11112,22 +11115,17 @@ WITH "date_dim_2" AS (
NOT "_u_0"."d_week_seq" IS NULL
GROUP BY
"date_dim"."d_date"
-), "item_2" AS (
- SELECT
- "item"."i_item_sk" AS "i_item_sk",
- "item"."i_item_id" AS "i_item_id"
- FROM "item" AS "item"
), "sr_items" AS (
SELECT
"item"."i_item_id" AS "item_id",
SUM("store_returns"."sr_return_quantity") AS "sr_item_qty"
FROM "store_returns" AS "store_returns"
+ JOIN "item_2" AS "item"
+ ON "store_returns"."sr_item_sk" = "item"."i_item_sk"
JOIN "date_dim_2" AS "date_dim"
ON "store_returns"."sr_returned_date_sk" = "date_dim"."d_date_sk"
LEFT JOIN "_u_1" AS "_u_1"
ON "date_dim"."d_date" = "_u_1"."d_date"
- JOIN "item_2" AS "item"
- ON "store_returns"."sr_item_sk" = "item"."i_item_sk"
WHERE
NOT "_u_1"."d_date" IS NULL
GROUP BY
@@ -11147,12 +11145,12 @@ WITH "date_dim_2" AS (
"item"."i_item_id" AS "item_id",
SUM("catalog_returns"."cr_return_quantity") AS "cr_item_qty"
FROM "catalog_returns" AS "catalog_returns"
+ JOIN "item_2" AS "item"
+ ON "catalog_returns"."cr_item_sk" = "item"."i_item_sk"
JOIN "date_dim_2" AS "date_dim"
ON "catalog_returns"."cr_returned_date_sk" = "date_dim"."d_date_sk"
LEFT JOIN "_u_3" AS "_u_3"
ON "date_dim"."d_date" = "_u_3"."d_date"
- JOIN "item_2" AS "item"
- ON "catalog_returns"."cr_item_sk" = "item"."i_item_sk"
WHERE
NOT "_u_3"."d_date" IS NULL
GROUP BY
@@ -11172,12 +11170,12 @@ WITH "date_dim_2" AS (
"item"."i_item_id" AS "item_id",
SUM("web_returns"."wr_return_quantity") AS "wr_item_qty"
FROM "web_returns" AS "web_returns"
+ JOIN "item_2" AS "item"
+ ON "web_returns"."wr_item_sk" = "item"."i_item_sk"
JOIN "date_dim_2" AS "date_dim"
ON "web_returns"."wr_returned_date_sk" = "date_dim"."d_date_sk"
LEFT JOIN "_u_5" AS "_u_5"
ON "date_dim"."d_date" = "_u_5"."d_date"
- JOIN "item_2" AS "item"
- ON "web_returns"."wr_item_sk" = "item"."i_item_sk"
WHERE
NOT "_u_5"."d_date" IS NULL
GROUP BY
@@ -11437,7 +11435,7 @@ ROLLUP (
)
ORDER BY
"lochierarchy" DESC,
- CASE WHEN "lochierarchy" = 0 THEN "item"."i_category" END,
+ CASE WHEN "lochierarchy" = 0 THEN "i_category" END,
"rank_within_parent"
LIMIT 100;
@@ -11687,73 +11685,87 @@ WITH "store_sales_2" AS (
), "s2" AS (
SELECT
COUNT(*) AS "h9_to_9_30"
- FROM "store_sales_2" AS "store_sales", "household_demographics_2" AS "household_demographics", "time_dim" AS "time_dim", "store_2" AS "store"
- WHERE
- "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
- AND "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
- AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+ FROM "store_sales_2" AS "store_sales"
+ JOIN "household_demographics_2" AS "household_demographics"
+ ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+ JOIN "time_dim" AS "time_dim"
+ ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
AND "time_dim"."t_hour" = 9
AND "time_dim"."t_minute" < 30
+ JOIN "store_2" AS "store"
+ ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
), "s3" AS (
SELECT
COUNT(*) AS "h9_30_to_10"
- FROM "store_sales_2" AS "store_sales", "household_demographics_2" AS "household_demographics", "time_dim" AS "time_dim", "store_2" AS "store"
- WHERE
- "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
- AND "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
- AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+ FROM "store_sales_2" AS "store_sales"
+ JOIN "household_demographics_2" AS "household_demographics"
+ ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+ JOIN "time_dim" AS "time_dim"
+ ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
AND "time_dim"."t_hour" = 9
AND "time_dim"."t_minute" >= 30
+ JOIN "store_2" AS "store"
+ ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
), "s4" AS (
SELECT
COUNT(*) AS "h10_to_10_30"
- FROM "store_sales_2" AS "store_sales", "household_demographics_2" AS "household_demographics", "time_dim" AS "time_dim", "store_2" AS "store"
- WHERE
- "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
- AND "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
- AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+ FROM "store_sales_2" AS "store_sales"
+ JOIN "household_demographics_2" AS "household_demographics"
+ ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+ JOIN "time_dim" AS "time_dim"
+ ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
AND "time_dim"."t_hour" = 10
AND "time_dim"."t_minute" < 30
+ JOIN "store_2" AS "store"
+ ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
), "s5" AS (
SELECT
COUNT(*) AS "h10_30_to_11"
- FROM "store_sales_2" AS "store_sales", "household_demographics_2" AS "household_demographics", "time_dim" AS "time_dim", "store_2" AS "store"
- WHERE
- "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
- AND "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
- AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+ FROM "store_sales_2" AS "store_sales"
+ JOIN "household_demographics_2" AS "household_demographics"
+ ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+ JOIN "time_dim" AS "time_dim"
+ ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
AND "time_dim"."t_hour" = 10
AND "time_dim"."t_minute" >= 30
+ JOIN "store_2" AS "store"
+ ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
), "s6" AS (
SELECT
COUNT(*) AS "h11_to_11_30"
- FROM "store_sales_2" AS "store_sales", "household_demographics_2" AS "household_demographics", "time_dim" AS "time_dim", "store_2" AS "store"
- WHERE
- "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
- AND "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
- AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+ FROM "store_sales_2" AS "store_sales"
+ JOIN "household_demographics_2" AS "household_demographics"
+ ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+ JOIN "time_dim" AS "time_dim"
+ ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
AND "time_dim"."t_hour" = 11
AND "time_dim"."t_minute" < 30
+ JOIN "store_2" AS "store"
+ ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
), "s7" AS (
SELECT
COUNT(*) AS "h11_30_to_12"
- FROM "store_sales_2" AS "store_sales", "household_demographics_2" AS "household_demographics", "time_dim" AS "time_dim", "store_2" AS "store"
- WHERE
- "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
- AND "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
- AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+ FROM "store_sales_2" AS "store_sales"
+ JOIN "household_demographics_2" AS "household_demographics"
+ ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+ JOIN "time_dim" AS "time_dim"
+ ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
AND "time_dim"."t_hour" = 11
AND "time_dim"."t_minute" >= 30
+ JOIN "store_2" AS "store"
+ ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
), "s8" AS (
SELECT
COUNT(*) AS "h12_to_12_30"
- FROM "store_sales_2" AS "store_sales", "household_demographics_2" AS "household_demographics", "time_dim" AS "time_dim", "store_2" AS "store"
- WHERE
- "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
- AND "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
- AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+ FROM "store_sales_2" AS "store_sales"
+ JOIN "household_demographics_2" AS "household_demographics"
+ ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+ JOIN "time_dim" AS "time_dim"
+ ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
AND "time_dim"."t_hour" = 12
AND "time_dim"."t_minute" < 30
+ JOIN "store_2" AS "store"
+ ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
)
SELECT
"s1"."h8_30_to_9" AS "h8_30_to_9",
@@ -11946,13 +11958,15 @@ WITH "web_sales_2" AS (
), "pt" AS (
SELECT
COUNT(*) AS "pmc"
- FROM "web_sales_2" AS "web_sales", "household_demographics_2" AS "household_demographics", "time_dim" AS "time_dim", "web_page_2" AS "web_page"
- WHERE
- "time_dim"."t_hour" <= 21
+ FROM "web_sales_2" AS "web_sales"
+ JOIN "household_demographics_2" AS "household_demographics"
+ ON "web_sales"."ws_ship_hdemo_sk" = "household_demographics"."hd_demo_sk"
+ JOIN "time_dim" AS "time_dim"
+ ON "time_dim"."t_hour" <= 21
AND "time_dim"."t_hour" >= 20
- AND "web_sales"."ws_ship_hdemo_sk" = "household_demographics"."hd_demo_sk"
AND "web_sales"."ws_sold_time_sk" = "time_dim"."t_time_sk"
- AND "web_sales"."ws_web_page_sk" = "web_page"."wp_web_page_sk"
+ JOIN "web_page_2" AS "web_page"
+ ON "web_sales"."ws_web_page_sk" = "web_page"."wp_web_page_sk"
)
SELECT
CAST("at1"."amc" AS DECIMAL(15, 4)) / CAST("pt"."pmc" AS DECIMAL(15, 4)) AS "am_pm_ratio"
@@ -11997,10 +12011,10 @@ GROUP BY cc_call_center_id,
cd_education_status
ORDER BY Sum(cr_net_loss) DESC;
SELECT
- "call_center"."cc_call_center_id" AS Call_Center,
- "call_center"."cc_name" AS Call_Center_Name,
- "call_center"."cc_manager" AS Manager,
- SUM("catalog_returns"."cr_net_loss") AS Returns_Loss
+ "call_center"."cc_call_center_id" AS "call_center",
+ "call_center"."cc_name" AS "call_center_name",
+ "call_center"."cc_manager" AS "manager",
+ SUM("catalog_returns"."cr_net_loss") AS "returns_loss"
FROM "call_center" AS "call_center"
JOIN "catalog_returns" AS "catalog_returns"
ON "catalog_returns"."cr_call_center_sk" = "call_center"."cc_call_center_sk"
@@ -12096,10 +12110,10 @@ SELECT
FROM "web_sales_2" AS "web_sales"
JOIN "item" AS "item"
ON "item"."i_item_sk" = "web_sales"."ws_item_sk" AND "item"."i_manufact_id" = 718
-LEFT JOIN "_u_0" AS "_u_0"
- ON "_u_0"."_u_1" = "item"."i_item_sk"
JOIN "date_dim_2" AS "date_dim"
ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
+LEFT JOIN "_u_0" AS "_u_0"
+ ON "_u_0"."_u_1" = "item"."i_item_sk"
WHERE
"web_sales"."ws_ext_discount_amt" > "_u_0"."_col_0"
ORDER BY
@@ -12208,10 +12222,6 @@ SELECT
SUM("web_sales"."ws_ext_ship_cost") AS "total shipping cost",
SUM("web_sales"."ws_net_profit") AS "total net profit"
FROM "web_sales" AS "web_sales"
-LEFT JOIN "_u_0" AS "_u_0"
- ON "web_sales"."ws_order_number" = "_u_0"."_u_1"
-LEFT JOIN "_u_3" AS "_u_3"
- ON "web_sales"."ws_order_number" = "_u_3"."_u_4"
JOIN "date_dim" AS "date_dim"
ON "date_dim"."d_date" >= '2000-3-01'
AND "web_sales"."ws_ship_date_sk" = "date_dim"."d_date_sk"
@@ -12224,6 +12234,10 @@ JOIN "customer_address" AS "customer_address"
JOIN "web_site" AS "web_site"
ON "web_sales"."ws_web_site_sk" = "web_site"."web_site_sk"
AND "web_site"."web_company_name" = 'pri'
+LEFT JOIN "_u_0" AS "_u_0"
+ ON "web_sales"."ws_order_number" = "_u_0"."_u_1"
+LEFT JOIN "_u_3" AS "_u_3"
+ ON "web_sales"."ws_order_number" = "_u_3"."_u_4"
WHERE
"_u_3"."_u_4" IS NULL
AND ARRAY_ANY("_u_0"."_u_2", "_x" -> "web_sales"."ws_warehouse_sk" <> "_x")
@@ -12303,10 +12317,6 @@ SELECT
SUM("web_sales"."ws_ext_ship_cost") AS "total shipping cost",
SUM("web_sales"."ws_net_profit") AS "total net profit"
FROM "web_sales" AS "web_sales"
-LEFT JOIN "_u_0" AS "_u_0"
- ON "web_sales"."ws_order_number" = "_u_0"."ws_order_number"
-LEFT JOIN "_u_1" AS "_u_1"
- ON "web_sales"."ws_order_number" = "_u_1"."wr_order_number"
JOIN "date_dim" AS "date_dim"
ON "date_dim"."d_date" >= '2000-4-01'
AND "web_sales"."ws_ship_date_sk" = "date_dim"."d_date_sk"
@@ -12319,6 +12329,10 @@ JOIN "customer_address" AS "customer_address"
JOIN "web_site" AS "web_site"
ON "web_sales"."ws_web_site_sk" = "web_site"."web_site_sk"
AND "web_site"."web_company_name" = 'pri'
+LEFT JOIN "_u_0" AS "_u_0"
+ ON "web_sales"."ws_order_number" = "_u_0"."ws_order_number"
+LEFT JOIN "_u_1" AS "_u_1"
+ ON "web_sales"."ws_order_number" = "_u_1"."wr_order_number"
WHERE
NOT "_u_0"."ws_order_number" IS NULL AND NOT "_u_1"."wr_order_number" IS NULL
ORDER BY
diff --git a/tests/fixtures/optimizer/tpc-h/tpc-h.sql b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
index ae50f92..a25e247 100644
--- a/tests/fixtures/optimizer/tpc-h/tpc-h.sql
+++ b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
@@ -99,19 +99,19 @@ order by
p_partkey
limit
100;
-WITH "partsupp_2" AS (
- SELECT
- "partsupp"."ps_partkey" AS "ps_partkey",
- "partsupp"."ps_suppkey" AS "ps_suppkey",
- "partsupp"."ps_supplycost" AS "ps_supplycost"
- FROM "partsupp" AS "partsupp"
-), "region_2" AS (
+WITH "region_2" AS (
SELECT
"region"."r_regionkey" AS "r_regionkey",
"region"."r_name" AS "r_name"
FROM "region" AS "region"
WHERE
"region"."r_name" = 'EUROPE'
+), "partsupp_2" AS (
+ SELECT
+ "partsupp"."ps_partkey" AS "ps_partkey",
+ "partsupp"."ps_suppkey" AS "ps_suppkey",
+ "partsupp"."ps_supplycost" AS "ps_supplycost"
+ FROM "partsupp" AS "partsupp"
), "_u_0" AS (
SELECT
MIN("partsupp"."ps_supplycost") AS "_col_0",
@@ -136,8 +136,6 @@ SELECT
"supplier"."s_phone" AS "s_phone",
"supplier"."s_comment" AS "s_comment"
FROM "part" AS "part"
-LEFT JOIN "_u_0" AS "_u_0"
- ON "part"."p_partkey" = "_u_0"."_u_1"
CROSS JOIN "region_2" AS "region"
JOIN "nation" AS "nation"
ON "nation"."n_regionkey" = "region"."r_regionkey"
@@ -146,6 +144,8 @@ JOIN "partsupp_2" AS "partsupp"
JOIN "supplier" AS "supplier"
ON "supplier"."s_nationkey" = "nation"."n_nationkey"
AND "supplier"."s_suppkey" = "partsupp"."ps_suppkey"
+LEFT JOIN "_u_0" AS "_u_0"
+ ON "part"."p_partkey" = "_u_0"."_u_1"
WHERE
"part"."p_size" = 15
AND "part"."p_type" LIKE '%BRASS'
@@ -681,11 +681,11 @@ SELECT
"partsupp"."ps_partkey" AS "ps_partkey",
SUM("partsupp"."ps_supplycost" * "partsupp"."ps_availqty") AS "value"
FROM "partsupp" AS "partsupp"
-CROSS JOIN "_u_0" AS "_u_0"
JOIN "supplier_2" AS "supplier"
ON "partsupp"."ps_suppkey" = "supplier"."s_suppkey"
JOIN "nation_2" AS "nation"
ON "supplier"."s_nationkey" = "nation"."n_nationkey"
+CROSS JOIN "_u_0" AS "_u_0"
GROUP BY
"partsupp"."ps_partkey"
HAVING
@@ -950,13 +950,13 @@ SELECT
"part"."p_size" AS "p_size",
COUNT(DISTINCT "partsupp"."ps_suppkey") AS "supplier_cnt"
FROM "partsupp" AS "partsupp"
-LEFT JOIN "_u_0" AS "_u_0"
- ON "partsupp"."ps_suppkey" = "_u_0"."s_suppkey"
JOIN "part" AS "part"
ON "part"."p_brand" <> 'Brand#45'
AND "part"."p_partkey" = "partsupp"."ps_partkey"
AND "part"."p_size" IN (49, 14, 23, 45, 19, 3, 36, 9)
AND NOT "part"."p_type" LIKE 'MEDIUM POLISHED%'
+LEFT JOIN "_u_0" AS "_u_0"
+ ON "partsupp"."ps_suppkey" = "_u_0"."s_suppkey"
WHERE
"_u_0"."s_suppkey" IS NULL
GROUP BY
@@ -1066,10 +1066,10 @@ SELECT
FROM "customer" AS "customer"
JOIN "orders" AS "orders"
ON "customer"."c_custkey" = "orders"."o_custkey"
-LEFT JOIN "_u_0" AS "_u_0"
- ON "orders"."o_orderkey" = "_u_0"."l_orderkey"
JOIN "lineitem" AS "lineitem"
ON "orders"."o_orderkey" = "lineitem"."l_orderkey"
+LEFT JOIN "_u_0" AS "_u_0"
+ ON "orders"."o_orderkey" = "_u_0"."l_orderkey"
WHERE
NOT "_u_0"."l_orderkey" IS NULL
GROUP BY
@@ -1260,10 +1260,10 @@ SELECT
"supplier"."s_name" AS "s_name",
"supplier"."s_address" AS "s_address"
FROM "supplier" AS "supplier"
-LEFT JOIN "_u_4" AS "_u_4"
- ON "supplier"."s_suppkey" = "_u_4"."ps_suppkey"
JOIN "nation" AS "nation"
ON "nation"."n_name" = 'CANADA' AND "supplier"."s_nationkey" = "nation"."n_nationkey"
+LEFT JOIN "_u_4" AS "_u_4"
+ ON "supplier"."s_suppkey" = "_u_4"."ps_suppkey"
WHERE
NOT "_u_4"."ps_suppkey" IS NULL
ORDER BY
@@ -1337,15 +1337,15 @@ FROM "supplier" AS "supplier"
JOIN "lineitem" AS "lineitem"
ON "lineitem"."l_receiptdate" > "lineitem"."l_commitdate"
AND "supplier"."s_suppkey" = "lineitem"."l_suppkey"
-LEFT JOIN "_u_0" AS "_u_0"
- ON "_u_0"."l_orderkey" = "lineitem"."l_orderkey"
-LEFT JOIN "_u_2" AS "_u_2"
- ON "_u_2"."l_orderkey" = "lineitem"."l_orderkey"
JOIN "orders" AS "orders"
ON "orders"."o_orderkey" = "lineitem"."l_orderkey" AND "orders"."o_orderstatus" = 'F'
JOIN "nation" AS "nation"
ON "nation"."n_name" = 'SAUDI ARABIA'
AND "supplier"."s_nationkey" = "nation"."n_nationkey"
+LEFT JOIN "_u_0" AS "_u_0"
+ ON "_u_0"."l_orderkey" = "lineitem"."l_orderkey"
+LEFT JOIN "_u_2" AS "_u_2"
+ ON "_u_2"."l_orderkey" = "lineitem"."l_orderkey"
WHERE
(
"_u_2"."l_orderkey" IS NULL
diff --git a/tests/fixtures/partial.sql b/tests/fixtures/partial.sql
index c6be364..6c578ab 100644
--- a/tests/fixtures/partial.sql
+++ b/tests/fixtures/partial.sql
@@ -2,7 +2,7 @@ SELECT a FROM
SELECT a FROM x WHERE
SELECT a +
a *
-SELECT a FROM x JOIN
+SELECT a FROM x,
SELECT a FROM x GROUP BY
WITH a AS (SELECT 1), b AS (SELECT 2)
SELECT FROM x