diff options
Diffstat (limited to 'tests/fixtures')
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 |