diff options
Diffstat (limited to 'tests/fixtures/optimizer')
19 files changed, 546 insertions, 382 deletions
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 |