summaryrefslogtreecommitdiffstats
path: root/tests/fixtures/optimizer
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2023-06-02 23:59:40 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2023-06-02 23:59:46 +0000
commit20739a12c39121a9e7ad3c9a2469ec5a6876199d (patch)
treec000de91c59fd29b2d9beecf9f93b84e69727f37 /tests/fixtures/optimizer
parentReleasing debian version 12.2.0-1. (diff)
downloadsqlglot-20739a12c39121a9e7ad3c9a2469ec5a6876199d.tar.xz
sqlglot-20739a12c39121a9e7ad3c9a2469ec5a6876199d.zip
Merging upstream version 15.0.0.
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'tests/fixtures/optimizer')
-rw-r--r--tests/fixtures/optimizer/canonicalize.sql4
-rw-r--r--tests/fixtures/optimizer/eliminate_subqueries.sql14
-rw-r--r--tests/fixtures/optimizer/expand_laterals.sql40
-rw-r--r--tests/fixtures/optimizer/expand_multi_table_selects.sql11
-rw-r--r--tests/fixtures/optimizer/isolate_table_selects.sql18
-rw-r--r--tests/fixtures/optimizer/merge_subqueries.sql6
-rw-r--r--tests/fixtures/optimizer/normalize.sql3
-rw-r--r--tests/fixtures/optimizer/normalize_identifiers.sql (renamed from tests/fixtures/optimizer/lower_identities.sql)30
-rw-r--r--tests/fixtures/optimizer/optimize_joins.sql16
-rw-r--r--tests/fixtures/optimizer/optimizer.sql121
-rw-r--r--tests/fixtures/optimizer/pushdown_predicates.sql4
-rw-r--r--tests/fixtures/optimizer/pushdown_projections.sql4
-rw-r--r--tests/fixtures/optimizer/qualify_columns.sql35
-rw-r--r--tests/fixtures/optimizer/qualify_columns__invalid.sql2
-rw-r--r--tests/fixtures/optimizer/qualify_columns__with_invisible.sql16
-rw-r--r--tests/fixtures/optimizer/qualify_tables.sql7
-rw-r--r--tests/fixtures/optimizer/simplify.sql17
-rw-r--r--tests/fixtures/optimizer/tpc-ds/tpc-ds.sql540
-rw-r--r--tests/fixtures/optimizer/tpc-h/tpc-h.sql40
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