From 38e6461a8afbd7cb83709ddb998f03d40ba87755 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Tue, 23 Jan 2024 06:06:14 +0100 Subject: Merging upstream version 20.9.0. Signed-off-by: Daniel Baumann --- tests/fixtures/optimizer/canonicalize.sql | 4 +- tests/fixtures/optimizer/optimizer.sql | 133 ++++++++++++++- .../optimizer/pushdown_cte_alias_columns.sql | 20 +++ tests/fixtures/optimizer/pushdown_predicates.sql | 2 +- tests/fixtures/optimizer/qualify_columns.sql | 23 ++- tests/fixtures/optimizer/qualify_tables.sql | 9 ++ tests/fixtures/optimizer/simplify.sql | 54 +++++-- tests/fixtures/optimizer/tpc-ds/tpc-ds.sql | 179 +++++++++++---------- tests/fixtures/optimizer/tpc-h/tpc-h.sql | 71 ++++---- 9 files changed, 358 insertions(+), 137 deletions(-) create mode 100644 tests/fixtures/optimizer/pushdown_cte_alias_columns.sql (limited to 'tests/fixtures/optimizer') diff --git a/tests/fixtures/optimizer/canonicalize.sql b/tests/fixtures/optimizer/canonicalize.sql index 302acb9..4db3764 100644 --- a/tests/fixtures/optimizer/canonicalize.sql +++ b/tests/fixtures/optimizer/canonicalize.sql @@ -16,8 +16,8 @@ SELECT '1' + 1 AS "col"; SELECT '1' + '1' AS "col"; SELECT CONCAT('1', '1') AS "col"; -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"; SELECT CAST('2022-01-01' AS DATE) IS NULL AS "a"; SELECT CAST('2022-01-01' AS DATE) IS NULL AS "a"; diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql index 4ae9e90..ea29e3e 100644 --- a/tests/fixtures/optimizer/optimizer.sql +++ b/tests/fixtures/optimizer/optimizer.sql @@ -626,8 +626,7 @@ 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" -; +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`) @@ -644,6 +643,110 @@ FROM ( FROM `u` AS `u` PIVOT(SUM(`u`.`f`) FOR `h` IN ('x', 'y')) ) AS `_q_0`; +# title: selecting all columns from a pivoted source, pivot has column aliases +# execute: false +# dialect: snowflake +WITH source AS ( + SELECT + id, + key, + value, + timestamp_1, + timestamp_2 + FROM DB_NAME.SCHEMA_NAME.TABLE_NAME +), +enriched AS ( + SELECT * FROM source + PIVOT(MAX(value) FOR key IN ('a', 'b', 'c')) + AS final (id, timestamp_1, timestamp_2, col_1, col_2, col_3) +) +SELECT id, timestamp_1 FROM enriched; +WITH "SOURCE" AS ( + SELECT + "TABLE_NAME"."ID" AS "ID", + "TABLE_NAME"."KEY" AS "KEY", + "TABLE_NAME"."VALUE" AS "VALUE", + "TABLE_NAME"."TIMESTAMP_1" AS "TIMESTAMP_1", + "TABLE_NAME"."TIMESTAMP_2" AS "TIMESTAMP_2" + FROM "DB_NAME"."SCHEMA_NAME"."TABLE_NAME" AS "TABLE_NAME" +) +SELECT + "FINAL"."ID" AS "ID", + "FINAL"."TIMESTAMP_1" AS "TIMESTAMP_1" +FROM "SOURCE" AS "SOURCE" PIVOT(MAX("SOURCE"."VALUE") FOR "SOURCE"."KEY" IN ('a', 'b', 'c')) AS "FINAL"("ID", "TIMESTAMP_1", "TIMESTAMP_2", "COL_1", "COL_2", "COL_3"); + +# title: unpivoted table source with a single value column, unpivot columns can't be qualified +# execute: false +# dialect: snowflake +SELECT * FROM m_sales AS m_sales(empid, dept, jan, feb) UNPIVOT(sales FOR month IN (jan, feb)) ORDER BY empid; +SELECT + "_q_0"."EMPID" AS "EMPID", + "_q_0"."DEPT" AS "DEPT", + "_q_0"."MONTH" AS "MONTH", + "_q_0"."SALES" AS "SALES" +FROM "M_SALES" AS "M_SALES"("EMPID", "DEPT", "JAN", "FEB") UNPIVOT("SALES" FOR "MONTH" IN ("JAN", "FEB")) AS "_q_0" +ORDER BY + "_q_0"."EMPID"; + +# title: unpivoted table source, unpivot has column aliases +# execute: false +SELECT * FROM (SELECT * FROM m_sales) AS m_sales(empid, dept, jan, feb) UNPIVOT(sales FOR month IN (jan, feb)) AS unpiv(a, b, c, d); +SELECT + "unpiv"."a" AS "a", + "unpiv"."b" AS "b", + "unpiv"."c" AS "c", + "unpiv"."d" AS "d" +FROM ( + SELECT + "m_sales"."empid" AS "empid", + "m_sales"."dept" AS "dept", + "m_sales"."jan" AS "jan", + "m_sales"."feb" AS "feb" + FROM "m_sales" AS "m_sales" +) AS "m_sales" UNPIVOT("sales" FOR "month" IN ("m_sales"."jan", "m_sales"."feb")) AS "unpiv"("a", "b", "c", "d"); + +# title: unpivoted derived table source with a single value column +# execute: false +# dialect: snowflake +SELECT * FROM (SELECT * FROM m_sales) AS m_sales(empid, dept, jan, feb) UNPIVOT(sales FOR month IN (jan, feb)) ORDER BY empid; +SELECT + "_q_0"."EMPID" AS "EMPID", + "_q_0"."DEPT" AS "DEPT", + "_q_0"."MONTH" AS "MONTH", + "_q_0"."SALES" AS "SALES" +FROM ( + SELECT + "M_SALES"."EMPID" AS "EMPID", + "M_SALES"."DEPT" AS "DEPT", + "M_SALES"."JAN" AS "JAN", + "M_SALES"."FEB" AS "FEB" + FROM "M_SALES" AS "M_SALES" +) AS "M_SALES" UNPIVOT("SALES" FOR "MONTH" IN ("JAN", "FEB")) AS "_q_0" +ORDER BY + "_q_0"."EMPID"; + +# title: unpivoted table source with a single value column, unpivot columns can be qualified +# execute: false +# dialect: bigquery +# note: the named columns aren't supported by BQ but we add them here to avoid defining a schema +SELECT * FROM produce AS produce(product, q1, q2, q3, q4) UNPIVOT(sales FOR quarter IN (q1, q2, q3, q4)); +SELECT + `_q_0`.`product` AS `product`, + `_q_0`.`quarter` AS `quarter`, + `_q_0`.`sales` AS `sales` +FROM `produce` AS `produce` UNPIVOT(`sales` FOR `quarter` IN (`produce`.`q1`, `produce`.`q2`, `produce`.`q3`, `produce`.`q4`)) AS `_q_0`; + +# title: unpivoted table source with multiple value columns +# execute: false +# dialect: bigquery +SELECT * FROM produce AS produce(product, q1, q2, q3, q4) UNPIVOT((first_half_sales, second_half_sales) FOR semesters IN ((Q1, Q2) AS 'semester_1', (Q3, Q4) AS 'semester_2')); +SELECT + `_q_0`.`product` AS `product`, + `_q_0`.`semesters` AS `semesters`, + `_q_0`.`first_half_sales` AS `first_half_sales`, + `_q_0`.`second_half_sales` AS `second_half_sales` +FROM `produce` AS `produce` UNPIVOT((`first_half_sales`, `second_half_sales`) FOR `semesters` IN ((`produce`.`q1`, `produce`.`q2`) AS 'semester_1', (`produce`.`q3`, `produce`.`q4`) AS 'semester_2')) AS `_q_0`; + # title: quoting is maintained # dialect: snowflake with cte1("id", foo) as (select 1, 2) select "id" from cte1; @@ -1144,3 +1247,29 @@ WITH "x" AS ( LIMIT 10 ) LIMIT 10; + +# title: avoid producing DAG cycle when pushing down predicate to join +# execute: false +SELECT + a.company, + b.num +FROM route AS a(num, company, pos, stop) +JOIN route AS b(num, company, pos, stop) ON (a.num = b.num) +JOIN stops AS c(id, name) ON (c.id = b.stop) +JOIN stops AS d(id, name) ON (d.id = c.id) +WHERE + c.name = 'Craiglockhart' + OR d.name = 'Tollcross'; +SELECT + "a"."company" AS "company", + "b"."num" AS "num" +FROM "route" AS "a"("num", "company", "pos", "stop") +JOIN "route" AS "b"("num", "company", "pos", "stop") + ON "a"."num" = "b"."num" +JOIN "stops" AS "c"("id", "name") + ON "b"."stop" = "c"."id" +JOIN "stops" AS "d"("id", "name") + ON "c"."id" = "d"."id" + AND ( + "c"."name" = 'Craiglockhart' OR "d"."name" = 'Tollcross' + ); diff --git a/tests/fixtures/optimizer/pushdown_cte_alias_columns.sql b/tests/fixtures/optimizer/pushdown_cte_alias_columns.sql new file mode 100644 index 0000000..e25ab19 --- /dev/null +++ b/tests/fixtures/optimizer/pushdown_cte_alias_columns.sql @@ -0,0 +1,20 @@ +WITH y(c) AS (SELECT SUM(a) FROM (SELECT 1 a) AS x HAVING c > 0) SELECT c FROM y; +WITH y(c) AS (SELECT SUM(a) AS c FROM (SELECT 1 AS a) AS x HAVING c > 0) SELECT c FROM y; + +WITH y(c) AS (SELECT SUM(a) as d FROM (SELECT 1 a) AS x HAVING c > 0) SELECT c FROM y; +WITH y(c) AS (SELECT SUM(a) AS c FROM (SELECT 1 AS a) AS x HAVING c > 0) SELECT c FROM y; + +WITH x(c) AS (SELECT SUM(1) a HAVING c > 0 LIMIT 1) SELECT * FROM x; +WITH x(c) AS (SELECT SUM(1) AS c HAVING c > 0 LIMIT 1) SELECT * FROM x; + +-- Invalid statement in Snowflake but checking more complex structures +WITH x(c) AS ((SELECT 1 a) HAVING c > 0) SELECT * FROM x; +WITH x(c) AS ((SELECT 1 AS a) HAVING c > 0) SELECT * FROM x; + +-- Invalid statement in Snowflake but checking more complex structures +WITH x(c) AS ((SELECT SUM(1) a) HAVING c > 0 LIMIT 1) SELECT * FROM x; +WITH x(c) AS ((SELECT SUM(1) AS a) HAVING c > 0 LIMIT 1) SELECT * FROM x; + +-- Invalid statement in Snowflake but checking that we don't fail +WITH x(c) AS (SELECT SUM(a) FROM x HAVING c > 0 UNION ALL SELECT SUM(a) FROM y HAVING c > 0) SELECT * FROM x; +WITH x(c) AS (SELECT SUM(a) FROM x HAVING c > 0 UNION ALL SELECT SUM(a) FROM y HAVING c > 0) SELECT * FROM x; diff --git a/tests/fixtures/optimizer/pushdown_predicates.sql b/tests/fixtures/optimizer/pushdown_predicates.sql index 43f9842..81b9acd 100644 --- a/tests/fixtures/optimizer/pushdown_predicates.sql +++ b/tests/fixtures/optimizer/pushdown_predicates.sql @@ -11,7 +11,7 @@ SELECT x.a FROM (SELECT * FROM x) AS x JOIN y WHERE (x.a = y.a AND x.a = 1 AND x SELECT x.a FROM (SELECT * FROM x) AS x JOIN y ON x.a = y.a WHERE TRUE; 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.b; -SELECT x.a FROM (SELECT * FROM x) AS x JOIN y ON x.a = y.a OR x.a = y.b WHERE (x.a = 1 AND x.a = y.a AND x.b = 1) OR x.a = y.b; +SELECT x.a FROM (SELECT * FROM x) AS x JOIN y ON (x.a = 1 AND x.a = y.a AND x.b = 1) OR x.a = y.b WHERE (x.a = 1 AND x.a = y.a AND x.b = 1) OR x.a = y.b; SELECT x.a FROM (SELECT x.a AS a, x.b * 1 AS c FROM x) AS x WHERE x.c = 1; SELECT x.a FROM (SELECT x.a AS a, x.b * 1 AS c FROM x WHERE x.b * 1 = 1) AS x WHERE TRUE; diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql index 43127a9..5641ed4 100644 --- a/tests/fixtures/optimizer/qualify_columns.sql +++ b/tests/fixtures/optimizer/qualify_columns.sql @@ -153,7 +153,7 @@ SELECT AGGREGATE(ARRAY(x.a, x.b), 0, (x, acc) -> x + acc + x.a) AS sum_agg FROM # dialect: starrocks # execute: false SELECT DATE_TRUNC('week', a) AS a FROM x; -SELECT DATE_TRUNC('week', x.a) AS a FROM x AS x; +SELECT DATE_TRUNC('WEEK', x.a) AS a FROM x AS x; # dialect: bigquery # execute: false @@ -276,6 +276,17 @@ SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0; SELECT * FROM x GROUP BY 1, 2; SELECT x.a AS a, x.b AS b FROM x AS x GROUP BY x.a, x.b; +SELECT * FROM (SELECT * FROM x) AS s(a, b); +SELECT s.a AS a, s.b AS b FROM (SELECT x.a AS a, x.b AS b FROM x AS x) AS s; + +# execute: false +SELECT * FROM (SELECT * FROM t) AS s(a, b); +SELECT s.a AS a, s.b AS b FROM (SELECT t.a AS a, t.b AS b FROM t AS t) AS s; + +# execute: false +SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2) AS s(b); +SELECT s.b AS b FROM (SELECT t1.b AS b FROM t1 AS t1 UNION ALL SELECT t2.b AS b FROM t2 AS t2) AS s; + -------------------------------------- -- CTEs -------------------------------------- @@ -430,6 +441,16 @@ SELECT t.aa AS aa FROM x AS x, UNNEST(x.a) AS t(aa); SELECT aa FROM x, UNNEST(a) AS aa; SELECT aa AS aa FROM x AS x, UNNEST(x.a) AS aa; +# dialect: bigquery +# execute: false +select * from unnest ([1, 2]) as x with offset; +SELECT x AS x, offset AS offset FROM UNNEST([1, 2]) AS x WITH OFFSET AS offset; + +# dialect: bigquery +# execute: false +select * from unnest ([1, 2]) as x with offset as y; +SELECT x AS x, y AS y FROM UNNEST([1, 2]) AS x WITH OFFSET AS y; + # dialect: presto SELECT x.a, i.b FROM x CROSS JOIN UNNEST(SPLIT(b, ',')) AS i(b); SELECT x.a AS a, i.b AS b FROM x AS x CROSS JOIN UNNEST(SPLIT(x.b, ',')) AS i(b); diff --git a/tests/fixtures/optimizer/qualify_tables.sql b/tests/fixtures/optimizer/qualify_tables.sql index 248d7e9..0ebfcd3 100644 --- a/tests/fixtures/optimizer/qualify_tables.sql +++ b/tests/fixtures/optimizer/qualify_tables.sql @@ -14,6 +14,11 @@ SELECT 1 FROM x.y.z AS z; SELECT 1 FROM x.y.z AS z; SELECT 1 FROM x.y.z AS z; +# title: redshift unnest syntax, z.a should be a column, not a table +# dialect: redshift +SELECT 1 FROM y.z AS z, z.a; +SELECT 1 FROM c.y.z AS z, z.a; + # title: cte can't be qualified WITH a AS (SELECT 1 FROM z) SELECT 1 FROM a; WITH a AS (SELECT 1 FROM c.db.z AS z) SELECT 1 FROM a; @@ -26,6 +31,10 @@ 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; +# title: pivoted table, pivot has alias +SELECT * FROM x PIVOT (SUM(a) FOR b IN ('a', 'b')) AS piv; +SELECT * FROM c.db.x AS x PIVOT(SUM(a) FOR b IN ('a', 'b')) AS piv; + # title: wrapped table without alias SELECT * FROM (tbl); SELECT * FROM (c.db.tbl AS tbl); diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql index d3b03fb..a80be17 100644 --- a/tests/fixtures/optimizer/simplify.sql +++ b/tests/fixtures/optimizer/simplify.sql @@ -427,7 +427,7 @@ FALSE; TRUE; date '1998-12-01' - interval x day; -CAST('1998-12-01' AS DATE) - INTERVAL x day; +CAST('1998-12-01' AS DATE) - INTERVAL x DAY; date '1998-12-01' - interval '90' day; CAST('1998-09-02' AS DATE); @@ -442,13 +442,13 @@ interval '1' year + date '1998-01-01' + 3 * 7 * 4; CAST('1999-01-01' AS DATE) + 84; date '1998-12-01' - interval '90' foo; -CAST('1998-12-01' AS DATE) - INTERVAL '90' foo; +CAST('1998-12-01' AS DATE) - INTERVAL '90' FOO; date '1998-12-01' + interval '90' foo; -CAST('1998-12-01' AS DATE) + INTERVAL '90' foo; +CAST('1998-12-01' AS DATE) + INTERVAL '90' FOO; CAST(x AS DATE) + interval '1' week; -CAST(x AS DATE) + INTERVAL '1' week; +CAST(x AS DATE) + INTERVAL '1' WEEK; CAST('2008-11-11' AS DATETIME) + INTERVAL '5' MONTH; CAST('2009-04-11 00:00:00' AS DATETIME); @@ -456,8 +456,8 @@ CAST('2009-04-11 00:00:00' AS DATETIME); datetime '1998-12-01' - interval '90' day; CAST('1998-09-02 00:00:00' AS DATETIME); -CAST(x AS DATETIME) + interval '1' week; -CAST(x AS DATETIME) + INTERVAL '1' week; +CAST(x AS DATETIME) + interval '1' WEEK; +CAST(x AS DATETIME) + INTERVAL '1' WEEK; TS_OR_DS_TO_DATE('1998-12-01 00:00:01') - interval '90' day; CAST('1998-09-02' AS DATE); @@ -844,14 +844,14 @@ x < CAST('2022-01-01' AS DATE) AND x >= CAST('2021-01-01' AS DATE); -- Always false, except for nulls DATE_TRUNC('quarter', x) = CAST('2021-01-02' AS DATE); -DATE_TRUNC('quarter', x) = CAST('2021-01-02' AS DATE); +DATE_TRUNC('QUARTER', x) = CAST('2021-01-02' AS DATE); DATE_TRUNC('year', x) <> CAST('2021-01-01' AS DATE); FALSE; -- Always true, except for nulls DATE_TRUNC('year', x) <> CAST('2021-01-02' AS DATE); -DATE_TRUNC('year', x) <> CAST('2021-01-02' AS DATE); +DATE_TRUNC('YEAR', x) <> CAST('2021-01-02' AS DATE); DATE_TRUNC('year', x) <= CAST('2021-01-01' AS DATE); x < CAST('2022-01-01' AS DATE); @@ -884,11 +884,11 @@ DATE_TRUNC('year', x) > TS_OR_DS_TO_DATE(TS_OR_DS_TO_DATE('2021-01-02')); x >= CAST('2022-01-01' AS DATE); DATE_TRUNC('year', x) > TS_OR_DS_TO_DATE(TS_OR_DS_TO_DATE('2021-01-02', '%Y')); -DATE_TRUNC('year', x) > TS_OR_DS_TO_DATE(TS_OR_DS_TO_DATE('2021-01-02', '%Y')); +DATE_TRUNC('YEAR', x) > CAST(STR_TO_TIME('2021-01-02', '%Y') AS DATE); -- right is not a date DATE_TRUNC('year', x) <> '2021-01-02'; -DATE_TRUNC('year', x) <> '2021-01-02'; +DATE_TRUNC('YEAR', x) <> '2021-01-02'; DATE_TRUNC('year', x) IN (CAST('2021-01-01' AS DATE), CAST('2023-01-01' AS DATE)); (x < CAST('2022-01-01' AS DATE) AND x >= CAST('2021-01-01' AS DATE)) OR (x < CAST('2024-01-01' AS DATE) AND x >= CAST('2023-01-01' AS DATE)); @@ -906,7 +906,7 @@ x < CAST('2022-01-01 00:00:00' AS DATETIME) AND x >= CAST('2021-01-01 00:00:00' -- right side is not a date literal DATE_TRUNC('day', x) = CAST(y AS DATE); -CAST(y AS DATE) = DATE_TRUNC('day', x); +CAST(y AS DATE) = DATE_TRUNC('DAY', x); -- nested cast DATE_TRUNC('day', x) = CAST(CAST('2021-01-01 01:02:03' AS DATETIME) AS DATE); @@ -973,7 +973,7 @@ DATE_ADD(DATE_ADD(DATE_TRUNC('week', DATE_SUB(x, 1, DAY)), 1, DAY), 1, YEAR) < C x < CAST('2020-01-14' AS DATE); x - INTERVAL '1' day = CAST(y AS DATE); -CAST(y AS DATE) = x - INTERVAL '1' day; +CAST(y AS DATE) = x - INTERVAL '1' DAY; -------------------------------------- -- Constant Propagation @@ -1094,3 +1094,33 @@ CASE WHEN x = y THEN z END; CASE x1 + x2 WHEN x3 THEN x4 WHEN x5 + x6 THEN x7 ELSE x8 END; CASE WHEN x3 = (x1 + x2) THEN x4 WHEN (x1 + x2) = (x5 + x6) THEN x7 ELSE x8 END; + +-------------------------------------- +-- Simplify STARTSWITH +-------------------------------------- +STARTS_WITH('foo', 'f'); +TRUE; + +STARTS_WITH('foo', 'g'); +FALSE; + +STARTS_WITH('', 'f'); +FALSE; + +STARTS_WITH('', ''); +TRUE; + +STARTS_WITH('foo', ''); +TRUE; + +STARTS_WITH(NULL, y); +STARTS_WITH(NULL, y); + +STARTS_WITH(x, y); +STARTS_WITH(x, y); + +STARTS_WITH('x', y); +STARTS_WITH('x', y); + +STARTS_WITH(x, 'y'); +STARTS_WITH(x, 'y'); diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql index f0f584f..d38c3cc 100644 --- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql +++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql @@ -527,10 +527,11 @@ SELECT "t_s_secyear"."customer_last_name" AS "customer_last_name", "t_s_secyear"."customer_preferred_cust_flag" AS "customer_preferred_cust_flag" FROM "year_total" AS "t_s_firstyear" -JOIN "year_total" AS "t_c_secyear" - ON "t_c_secyear"."customer_id" = "t_s_firstyear"."customer_id" - AND "t_c_secyear"."dyear" = 2002 - AND "t_c_secyear"."sale_type" = 'c' +JOIN "year_total" AS "t_c_firstyear" + ON "t_c_firstyear"."customer_id" = "t_s_firstyear"."customer_id" + AND "t_c_firstyear"."dyear" = 2001 + AND "t_c_firstyear"."sale_type" = 'c' + AND "t_c_firstyear"."year_total" > 0 JOIN "year_total" AS "t_s_secyear" ON "t_s_firstyear"."customer_id" = "t_s_secyear"."customer_id" AND "t_s_secyear"."dyear" = 2002 @@ -540,15 +541,10 @@ JOIN "year_total" AS "t_w_firstyear" AND "t_w_firstyear"."dyear" = 2001 AND "t_w_firstyear"."sale_type" = 'w' AND "t_w_firstyear"."year_total" > 0 -JOIN "year_total" AS "t_w_secyear" - ON "t_s_firstyear"."customer_id" = "t_w_secyear"."customer_id" - AND "t_w_secyear"."dyear" = 2002 - AND "t_w_secyear"."sale_type" = 'w' -JOIN "year_total" AS "t_c_firstyear" - ON "t_c_firstyear"."customer_id" = "t_s_firstyear"."customer_id" - AND "t_c_firstyear"."dyear" = 2001 - AND "t_c_firstyear"."sale_type" = 'c' - AND "t_c_firstyear"."year_total" > 0 +JOIN "year_total" AS "t_c_secyear" + ON "t_c_secyear"."customer_id" = "t_s_firstyear"."customer_id" + AND "t_c_secyear"."dyear" = 2002 + AND "t_c_secyear"."sale_type" = 'c' AND CASE WHEN "t_c_firstyear"."year_total" > 0 THEN "t_c_secyear"."year_total" / "t_c_firstyear"."year_total" @@ -558,6 +554,10 @@ JOIN "year_total" AS "t_c_firstyear" THEN "t_s_secyear"."year_total" / "t_s_firstyear"."year_total" ELSE NULL END +JOIN "year_total" AS "t_w_secyear" + ON "t_s_firstyear"."customer_id" = "t_w_secyear"."customer_id" + AND "t_w_secyear"."dyear" = 2002 + AND "t_w_secyear"."sale_type" = 'w' AND CASE WHEN "t_c_firstyear"."year_total" > 0 THEN "t_c_secyear"."year_total" / "t_c_firstyear"."year_total" @@ -1787,6 +1787,10 @@ SELECT "t_s_secyear"."customer_last_name" AS "customer_last_name", "t_s_secyear"."customer_birth_country" AS "customer_birth_country" FROM "year_total" AS "t_s_firstyear" +JOIN "year_total" AS "t_s_secyear" + ON "t_s_firstyear"."customer_id" = "t_s_secyear"."customer_id" + AND "t_s_secyear"."dyear" = 2002 + AND "t_s_secyear"."sale_type" = 's' JOIN "year_total" AS "t_w_firstyear" ON "t_s_firstyear"."customer_id" = "t_w_firstyear"."customer_id" AND "t_w_firstyear"."dyear" = 2001 @@ -1796,10 +1800,6 @@ JOIN "year_total" AS "t_w_secyear" ON "t_s_firstyear"."customer_id" = "t_w_secyear"."customer_id" AND "t_w_secyear"."dyear" = 2002 AND "t_w_secyear"."sale_type" = 'w' -JOIN "year_total" AS "t_s_secyear" - ON "t_s_firstyear"."customer_id" = "t_s_secyear"."customer_id" - AND "t_s_secyear"."dyear" = 2002 - AND "t_s_secyear"."sale_type" = 's' AND CASE WHEN "t_s_firstyear"."year_total" > 0 THEN "t_s_secyear"."year_total" / "t_s_firstyear"."year_total" @@ -1934,7 +1934,7 @@ SELECT AVG("store_sales"."ss_ext_wholesale_cost") AS "_col_2", SUM("store_sales"."ss_ext_wholesale_cost") AS "_col_3" FROM "store_sales" AS "store_sales" -CROSS JOIN "customer_demographics" AS "customer_demographics" +CROSS JOIN "household_demographics" AS "household_demographics" JOIN "customer_address" AS "customer_address" ON ( "customer_address"."ca_address_sk" = "store_sales"."ss_addr_sk" @@ -1957,10 +1957,7 @@ JOIN "customer_address" AS "customer_address" AND "store_sales"."ss_net_profit" <= 300 AND "store_sales"."ss_net_profit" >= 150 ) -JOIN "date_dim" AS "date_dim" - ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" - AND "date_dim"."d_year" = 2001 -JOIN "household_demographics" AS "household_demographics" +JOIN "customer_demographics" AS "customer_demographics" ON ( "customer_demographics"."cd_demo_sk" = "store_sales"."ss_cdemo_sk" AND "customer_demographics"."cd_education_status" = 'Advanced Degree' @@ -1988,6 +1985,9 @@ JOIN "household_demographics" AS "household_demographics" AND "store_sales"."ss_sales_price" <= 200.00 AND "store_sales"."ss_sales_price" >= 150.00 ) +JOIN "date_dim" AS "date_dim" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" + AND "date_dim"."d_year" = 2001 JOIN "store" AS "store" ON "store"."s_store_sk" = "store_sales"."ss_store_sk"; @@ -2343,16 +2343,18 @@ SELECT "customer_address"."ca_zip" AS "ca_zip", SUM("catalog_sales"."cs_sales_price") AS "_col_1" FROM "catalog_sales" AS "catalog_sales" -JOIN "customer_address" AS "customer_address" - ON "catalog_sales"."cs_sales_price" > 500 - OR "customer_address"."ca_state" IN ('CA', 'WA', 'GA') - OR SUBSTR("customer_address"."ca_zip", 1, 5) IN ('85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792') +JOIN "customer" AS "customer" + ON "catalog_sales"."cs_bill_customer_sk" = "customer"."c_customer_sk" JOIN "date_dim" AS "date_dim" ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk" AND "date_dim"."d_qoy" = 1 AND "date_dim"."d_year" = 1998 -JOIN "customer" AS "customer" - ON "catalog_sales"."cs_bill_customer_sk" = "customer"."c_customer_sk" +JOIN "customer_address" AS "customer_address" + ON ( + "catalog_sales"."cs_sales_price" > 500 + OR "customer_address"."ca_state" IN ('CA', 'WA', 'GA') + OR SUBSTR("customer_address"."ca_zip", 1, 5) IN ('85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792') + ) AND "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk" GROUP BY "customer_address"."ca_zip" @@ -2428,7 +2430,7 @@ JOIN "date_dim" AS "date_dim" ON "cs1"."cs_ship_date_sk" = "date_dim"."d_date_sk" AND "date_dim"."d_date" >= '2002-3-01' AND ( - CAST('2002-3-01' AS DATE) + INTERVAL '60' day + CAST('2002-3-01' AS DATE) + INTERVAL '60' DAY ) >= CAST("date_dim"."d_date" AS DATE) WHERE "_u_3"."_u_4" IS NULL @@ -2669,15 +2671,15 @@ SELECT FROM "date_dim" AS "date_dim" JOIN "store_sales" AS "store_sales" ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" +JOIN "customer" AS "customer" + ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk" JOIN "item" AS "item" ON "item"."i_item_sk" = "store_sales"."ss_item_sk" AND "item"."i_manager_id" = 38 -JOIN "store" AS "store" - ON "store"."s_store_sk" = "store_sales"."ss_store_sk" JOIN "customer_address" AS "customer_address" - ON SUBSTR("customer_address"."ca_zip", 1, 5) <> SUBSTR("store"."s_zip", 1, 5) -JOIN "customer" AS "customer" ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk" - AND "customer"."c_customer_sk" = "store_sales"."ss_customer_sk" +JOIN "store" AS "store" + ON "store"."s_store_sk" = "store_sales"."ss_store_sk" + AND SUBSTR("customer_address"."ca_zip", 1, 5) <> SUBSTR("store"."s_zip", 1, 5) WHERE "date_dim"."d_moy" = 12 AND "date_dim"."d_year" = 1998 GROUP BY @@ -3109,6 +3111,8 @@ WITH "ssales" AS ( "item"."i_color" AS "i_color", SUM("store_sales"."ss_net_profit") AS "netpaid" FROM "store_sales" AS "store_sales" + JOIN "customer" AS "customer" + ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk" JOIN "item" AS "item" ON "item"."i_item_sk" = "store_sales"."ss_item_sk" JOIN "store" AS "store" @@ -3117,10 +3121,8 @@ WITH "ssales" AS ( ON "store_returns"."sr_item_sk" = "store_sales"."ss_item_sk" AND "store_returns"."sr_ticket_number" = "store_sales"."ss_ticket_number" JOIN "customer_address" AS "customer_address" - ON "customer_address"."ca_zip" = "store"."s_zip" - JOIN "customer" AS "customer" ON "customer"."c_birth_country" = UPPER("customer_address"."ca_country") - AND "customer"."c_customer_sk" = "store_sales"."ss_customer_sk" + AND "customer_address"."ca_zip" = "store"."s_zip" GROUP BY "customer"."c_last_name", "customer"."c_first_name", @@ -3850,6 +3852,8 @@ JOIN "ss" AS "ss2" ON "ss1"."ca_county" = "ss2"."ca_county" AND "ss2"."d_qoy" = 2 AND "ss2"."d_year" = 2001 JOIN "ws" AS "ws1" ON "ss1"."ca_county" = "ws1"."ca_county" AND "ws1"."d_qoy" = 1 AND "ws1"."d_year" = 2001 +JOIN "ss" AS "ss3" + ON "ss2"."ca_county" = "ss3"."ca_county" AND "ss3"."d_qoy" = 3 AND "ss3"."d_year" = 2001 JOIN "ws" AS "ws2" ON "ws1"."ca_county" = "ws2"."ca_county" AND "ws2"."d_qoy" = 2 @@ -3864,11 +3868,9 @@ JOIN "ws" AS "ws2" ELSE NULL END JOIN "ws" AS "ws3" - ON "ws1"."ca_county" = "ws3"."ca_county" AND "ws3"."d_qoy" = 3 AND "ws3"."d_year" = 2001 -JOIN "ss" AS "ss3" - ON "ss2"."ca_county" = "ss3"."ca_county" - AND "ss3"."d_qoy" = 3 - AND "ss3"."d_year" = 2001 + ON "ws1"."ca_county" = "ws3"."ca_county" + AND "ws3"."d_qoy" = 3 + AND "ws3"."d_year" = 2001 AND CASE WHEN "ss2"."store_sales" > 0 THEN "ss3"."store_sales" / "ss2"."store_sales" @@ -7013,22 +7015,22 @@ SELECT "ss_items"."ss_item_rev" + "cs_items"."cs_item_rev" + "ws_items"."ws_item_rev" ) / 3 AS "average" FROM "ss_items" -JOIN "ws_items" - ON "ss_items"."item_id" = "ws_items"."item_id" - AND "ss_items"."ss_item_rev" <= 1.1 * "ws_items"."ws_item_rev" - AND "ss_items"."ss_item_rev" >= 0.9 * "ws_items"."ws_item_rev" - AND "ws_items"."ws_item_rev" <= 1.1 * "ss_items"."ss_item_rev" - AND "ws_items"."ws_item_rev" >= 0.9 * "ss_items"."ss_item_rev" JOIN "cs_items" ON "cs_items"."cs_item_rev" <= 1.1 * "ss_items"."ss_item_rev" - AND "cs_items"."cs_item_rev" <= 1.1 * "ws_items"."ws_item_rev" AND "cs_items"."cs_item_rev" >= 0.9 * "ss_items"."ss_item_rev" - AND "cs_items"."cs_item_rev" >= 0.9 * "ws_items"."ws_item_rev" AND "cs_items"."item_id" = "ss_items"."item_id" AND "ss_items"."ss_item_rev" <= 1.1 * "cs_items"."cs_item_rev" AND "ss_items"."ss_item_rev" >= 0.9 * "cs_items"."cs_item_rev" +JOIN "ws_items" + ON "cs_items"."cs_item_rev" <= 1.1 * "ws_items"."ws_item_rev" + AND "cs_items"."cs_item_rev" >= 0.9 * "ws_items"."ws_item_rev" + AND "ss_items"."item_id" = "ws_items"."item_id" + AND "ss_items"."ss_item_rev" <= 1.1 * "ws_items"."ws_item_rev" + AND "ss_items"."ss_item_rev" >= 0.9 * "ws_items"."ws_item_rev" AND "ws_items"."ws_item_rev" <= 1.1 * "cs_items"."cs_item_rev" + AND "ws_items"."ws_item_rev" <= 1.1 * "ss_items"."ss_item_rev" AND "ws_items"."ws_item_rev" >= 0.9 * "cs_items"."cs_item_rev" + AND "ws_items"."ws_item_rev" >= 0.9 * "ss_items"."ss_item_rev" ORDER BY "item_id", "ss_item_rev" @@ -7915,17 +7917,18 @@ WITH "cs_ui" AS ( SUM("store_sales"."ss_list_price") AS "s2", SUM("store_sales"."ss_coupon_amt") AS "s3" FROM "store_sales" AS "store_sales" - CROSS JOIN "income_band" AS "ib2" JOIN "customer_address" AS "ad1" ON "ad1"."ca_address_sk" = "store_sales"."ss_addr_sk" + JOIN "customer_demographics" AS "cd1" + ON "cd1"."cd_demo_sk" = "store_sales"."ss_cdemo_sk" JOIN "cs_ui" ON "cs_ui"."cs_item_sk" = "store_sales"."ss_item_sk" + JOIN "customer" AS "customer" + ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk" JOIN "date_dim" AS "d1" ON "d1"."d_date_sk" = "store_sales"."ss_sold_date_sk" JOIN "household_demographics" AS "hd1" ON "hd1"."hd_demo_sk" = "store_sales"."ss_hdemo_sk" - JOIN "household_demographics" AS "hd2" - ON "hd2"."hd_income_band_sk" = "ib2"."ib_income_band_sk" JOIN "item" AS "item" ON "item"."i_color" IN ('cyan', 'peach', 'blush', 'frosted', 'powder', 'orange') AND "item"."i_current_price" <= 68 @@ -7938,22 +7941,21 @@ WITH "cs_ui" AS ( JOIN "store_returns" AS "store_returns" ON "store_returns"."sr_item_sk" = "store_sales"."ss_item_sk" AND "store_returns"."sr_ticket_number" = "store_sales"."ss_ticket_number" - JOIN "customer" AS "customer" - ON "customer"."c_current_hdemo_sk" = "hd2"."hd_demo_sk" - AND "customer"."c_customer_sk" = "store_sales"."ss_customer_sk" - JOIN "income_band" AS "ib1" - ON "hd1"."hd_income_band_sk" = "ib1"."ib_income_band_sk" JOIN "customer_address" AS "ad2" ON "ad2"."ca_address_sk" = "customer"."c_current_addr_sk" JOIN "customer_demographics" AS "cd2" - ON "cd2"."cd_demo_sk" = "customer"."c_current_cdemo_sk" + ON "cd1"."cd_marital_status" <> "cd2"."cd_marital_status" + AND "cd2"."cd_demo_sk" = "customer"."c_current_cdemo_sk" JOIN "date_dim" AS "d2" ON "customer"."c_first_sales_date_sk" = "d2"."d_date_sk" JOIN "date_dim" AS "d3" ON "customer"."c_first_shipto_date_sk" = "d3"."d_date_sk" - JOIN "customer_demographics" AS "cd1" - ON "cd1"."cd_demo_sk" = "store_sales"."ss_cdemo_sk" - AND "cd1"."cd_marital_status" <> "cd2"."cd_marital_status" + JOIN "household_demographics" AS "hd2" + ON "customer"."c_current_hdemo_sk" = "hd2"."hd_demo_sk" + JOIN "income_band" AS "ib1" + ON "hd1"."hd_income_band_sk" = "ib1"."ib_income_band_sk" + JOIN "income_band" AS "ib2" + ON "hd2"."hd_income_band_sk" = "ib2"."ib_income_band_sk" GROUP BY "item"."i_product_name", "item"."i_item_sk", @@ -9523,8 +9525,8 @@ LEFT JOIN "catalog_returns" AS "catalog_returns" JOIN "customer_demographics" AS "customer_demographics" ON "catalog_sales"."cs_bill_cdemo_sk" = "customer_demographics"."cd_demo_sk" AND "customer_demographics"."cd_marital_status" = 'M' -JOIN "date_dim" AS "d3" - ON "catalog_sales"."cs_ship_date_sk" = "d3"."d_date_sk" +JOIN "date_dim" AS "d1" + ON "catalog_sales"."cs_sold_date_sk" = "d1"."d_date_sk" AND "d1"."d_year" = 2002 JOIN "household_demographics" AS "household_demographics" ON "catalog_sales"."cs_bill_hdemo_sk" = "household_demographics"."hd_demo_sk" AND "household_demographics"."hd_buy_potential" = '501-1000' @@ -9536,14 +9538,13 @@ JOIN "item" AS "item" LEFT JOIN "promotion" AS "promotion" ON "catalog_sales"."cs_promo_sk" = "promotion"."p_promo_sk" JOIN "date_dim" AS "d2" - ON "d2"."d_date_sk" = "inventory"."inv_date_sk" + ON "d1"."d_week_seq" = "d2"."d_week_seq" + AND "d2"."d_date_sk" = "inventory"."inv_date_sk" +JOIN "date_dim" AS "d3" + ON "catalog_sales"."cs_ship_date_sk" = "d3"."d_date_sk" + AND "d3"."d_date" > "d1"."d_date" + INTERVAL '5' DAY JOIN "warehouse" AS "warehouse" ON "inventory"."inv_warehouse_sk" = "warehouse"."w_warehouse_sk" -JOIN "date_dim" AS "d1" - ON "catalog_sales"."cs_sold_date_sk" = "d1"."d_date_sk" - AND "d1"."d_week_seq" = "d2"."d_week_seq" - AND "d1"."d_year" = 2002 - AND "d3"."d_date" > "d1"."d_date" + INTERVAL '5' day GROUP BY "item"."i_item_desc", "warehouse"."w_warehouse_name", @@ -9769,6 +9770,10 @@ SELECT "t_s_secyear"."customer_first_name" AS "customer_first_name", "t_s_secyear"."customer_last_name" AS "customer_last_name" FROM "year_total" AS "t_s_firstyear" +JOIN "year_total" AS "t_s_secyear" + ON "t_s_firstyear"."customer_id" = "t_s_secyear"."customer_id" + AND "t_s_secyear"."sale_type" = 's' + AND "t_s_secyear"."year1" = 2000 JOIN "year_total" AS "t_w_firstyear" ON "t_s_firstyear"."customer_id" = "t_w_firstyear"."customer_id" AND "t_w_firstyear"."sale_type" = 'w' @@ -9778,10 +9783,6 @@ JOIN "year_total" AS "t_w_secyear" ON "t_s_firstyear"."customer_id" = "t_w_secyear"."customer_id" AND "t_w_secyear"."sale_type" = 'w' AND "t_w_secyear"."year1" = 2000 -JOIN "year_total" AS "t_s_secyear" - ON "t_s_firstyear"."customer_id" = "t_s_secyear"."customer_id" - AND "t_s_secyear"."sale_type" = 's' - AND "t_s_secyear"."year1" = 2000 AND CASE WHEN "t_s_firstyear"."year_total" > 0 THEN "t_s_secyear"."year_total" / "t_s_firstyear"."year_total" @@ -11369,8 +11370,8 @@ JOIN "web_page" AS "web_page" JOIN "web_returns" AS "web_returns" ON "web_returns"."wr_item_sk" = "web_sales"."ws_item_sk" AND "web_returns"."wr_order_number" = "web_sales"."ws_order_number" -JOIN "customer_demographics" AS "cd2" - ON "cd2"."cd_demo_sk" = "web_returns"."wr_returning_cdemo_sk" +JOIN "customer_demographics" AS "cd1" + ON "cd1"."cd_demo_sk" = "web_returns"."wr_refunded_cdemo_sk" JOIN "customer_address" AS "customer_address" ON "customer_address"."ca_address_sk" = "web_returns"."wr_refunded_addr_sk" AND ( @@ -11395,8 +11396,8 @@ JOIN "customer_address" AS "customer_address" ) JOIN "reason" AS "reason" ON "reason"."r_reason_sk" = "web_returns"."wr_reason_sk" -JOIN "customer_demographics" AS "cd1" - ON "cd1"."cd_demo_sk" = "web_returns"."wr_refunded_cdemo_sk" +JOIN "customer_demographics" AS "cd2" + ON "cd2"."cd_demo_sk" = "web_returns"."wr_returning_cdemo_sk" AND ( ( "cd1"."cd_education_status" = "cd2"."cd_education_status" @@ -12032,13 +12033,14 @@ SELECT "call_center"."cc_manager" AS "manager", SUM("catalog_returns"."cr_net_loss") AS "returns_loss" FROM "call_center" AS "call_center" -JOIN "household_demographics" AS "household_demographics" - ON "household_demographics"."hd_buy_potential" LIKE 'Unknown%' -JOIN "customer" AS "customer" - ON "customer"."c_current_hdemo_sk" = "household_demographics"."hd_demo_sk" JOIN "catalog_returns" AS "catalog_returns" ON "call_center"."cc_call_center_sk" = "catalog_returns"."cr_call_center_sk" - AND "catalog_returns"."cr_returning_customer_sk" = "customer"."c_customer_sk" +JOIN "customer" AS "customer" + ON "catalog_returns"."cr_returning_customer_sk" = "customer"."c_customer_sk" +JOIN "date_dim" AS "date_dim" + ON "catalog_returns"."cr_returned_date_sk" = "date_dim"."d_date_sk" + AND "date_dim"."d_moy" = 12 + AND "date_dim"."d_year" = 1999 JOIN "customer_address" AS "customer_address" ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk" AND "customer_address"."ca_gmt_offset" = -7 @@ -12060,10 +12062,9 @@ JOIN "customer_demographics" AS "customer_demographics" "customer_demographics"."cd_marital_status" = 'M' OR "customer_demographics"."cd_marital_status" = 'W' ) -JOIN "date_dim" AS "date_dim" - ON "catalog_returns"."cr_returned_date_sk" = "date_dim"."d_date_sk" - AND "date_dim"."d_moy" = 12 - AND "date_dim"."d_year" = 1999 +JOIN "household_demographics" AS "household_demographics" + ON "customer"."c_current_hdemo_sk" = "household_demographics"."hd_demo_sk" + AND "household_demographics"."hd_buy_potential" LIKE 'Unknown%' GROUP BY "call_center"."cc_call_center_id", "call_center"."cc_name", @@ -12249,7 +12250,7 @@ JOIN "date_dim" AS "date_dim" ON "date_dim"."d_date" >= '2000-3-01' AND "date_dim"."d_date_sk" = "ws1"."ws_ship_date_sk" AND ( - CAST('2000-3-01' AS DATE) + INTERVAL '60' day + CAST('2000-3-01' AS DATE) + INTERVAL '60' DAY ) >= CAST("date_dim"."d_date" AS DATE) JOIN "web_site" AS "web_site" ON "web_site"."web_company_name" = 'pri' @@ -12339,7 +12340,7 @@ JOIN "date_dim" AS "date_dim" ON "date_dim"."d_date" >= '2000-4-01' AND "date_dim"."d_date_sk" = "ws1"."ws_ship_date_sk" AND ( - CAST('2000-4-01' AS DATE) + INTERVAL '60' day + CAST('2000-4-01' AS DATE) + INTERVAL '60' DAY ) >= CAST("date_dim"."d_date" AS DATE) JOIN "web_site" AS "web_site" ON "web_site"."web_company_name" = 'pri' diff --git a/tests/fixtures/optimizer/tpc-h/tpc-h.sql b/tests/fixtures/optimizer/tpc-h/tpc-h.sql index 660b565..36f096c 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 "region_2" AS ( +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 ( 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,16 +136,16 @@ SELECT "supplier"."s_phone" AS "s_phone", "supplier"."s_comment" AS "s_comment" FROM "part" AS "part" -CROSS JOIN "region_2" AS "region" LEFT JOIN "_u_0" AS "_u_0" ON "_u_0"."_u_1" = "part"."p_partkey" -JOIN "nation" AS "nation" - ON "nation"."n_regionkey" = "region"."r_regionkey" JOIN "partsupp_2" AS "partsupp" ON "part"."p_partkey" = "partsupp"."ps_partkey" JOIN "supplier" AS "supplier" + ON "partsupp"."ps_suppkey" = "supplier"."s_suppkey" +JOIN "nation" AS "nation" ON "nation"."n_nationkey" = "supplier"."s_nationkey" - AND "partsupp"."ps_suppkey" = "supplier"."s_suppkey" +JOIN "region_2" AS "region" + ON "nation"."n_regionkey" = "region"."r_regionkey" WHERE "_u_0"."_col_0" = "partsupp"."ps_supplycost" AND "part"."p_size" = 15 @@ -294,10 +294,10 @@ JOIN "orders" AS "orders" ON "customer"."c_custkey" = "orders"."o_custkey" AND CAST("orders"."o_orderdate" AS DATE) < CAST('1995-01-01' AS DATE) AND CAST("orders"."o_orderdate" AS DATE) >= CAST('1994-01-01' AS DATE) -JOIN "supplier" AS "supplier" - ON "customer"."c_nationkey" = "supplier"."s_nationkey" JOIN "lineitem" AS "lineitem" ON "lineitem"."l_orderkey" = "orders"."o_orderkey" +JOIN "supplier" AS "supplier" + ON "customer"."c_nationkey" = "supplier"."s_nationkey" AND "lineitem"."l_suppkey" = "supplier"."s_suppkey" JOIN "nation" AS "nation" ON "nation"."n_nationkey" = "supplier"."s_nationkey" @@ -389,8 +389,13 @@ JOIN "nation" AS "n1" "n1"."n_name" = 'FRANCE' OR "n1"."n_name" = 'GERMANY' ) AND "n1"."n_nationkey" = "supplier"."s_nationkey" +JOIN "orders" AS "orders" + ON "lineitem"."l_orderkey" = "orders"."o_orderkey" +JOIN "customer" AS "customer" + ON "customer"."c_custkey" = "orders"."o_custkey" JOIN "nation" AS "n2" - ON ( + ON "customer"."c_nationkey" = "n2"."n_nationkey" + AND ( "n1"."n_name" = 'FRANCE' OR "n2"."n_name" = 'FRANCE' ) AND ( @@ -399,11 +404,6 @@ JOIN "nation" AS "n2" AND ( "n2"."n_name" = 'FRANCE' OR "n2"."n_name" = 'GERMANY' ) -JOIN "customer" AS "customer" - ON "customer"."c_nationkey" = "n2"."n_nationkey" -JOIN "orders" AS "orders" - ON "customer"."c_custkey" = "orders"."o_custkey" - AND "lineitem"."l_orderkey" = "orders"."o_orderkey" GROUP BY "n1"."n_name", "n2"."n_name", @@ -467,23 +467,22 @@ SELECT 1 - "lineitem"."l_discount" )) AS "mkt_share" FROM "part" AS "part" -JOIN "region" AS "region" - ON "region"."r_name" = 'AMERICA' JOIN "lineitem" AS "lineitem" ON "lineitem"."l_partkey" = "part"."p_partkey" -JOIN "nation" AS "n1" - ON "n1"."n_regionkey" = "region"."r_regionkey" -JOIN "customer" AS "customer" - ON "customer"."c_nationkey" = "n1"."n_nationkey" +JOIN "orders" AS "orders" + ON "lineitem"."l_orderkey" = "orders"."o_orderkey" + AND CAST("orders"."o_orderdate" AS DATE) <= CAST('1996-12-31' AS DATE) + AND CAST("orders"."o_orderdate" AS DATE) >= CAST('1995-01-01' AS DATE) JOIN "supplier" AS "supplier" ON "lineitem"."l_suppkey" = "supplier"."s_suppkey" +JOIN "customer" AS "customer" + ON "customer"."c_custkey" = "orders"."o_custkey" JOIN "nation" AS "n2" ON "n2"."n_nationkey" = "supplier"."s_nationkey" -JOIN "orders" AS "orders" - ON "customer"."c_custkey" = "orders"."o_custkey" - AND "lineitem"."l_orderkey" = "orders"."o_orderkey" - AND CAST("orders"."o_orderdate" AS DATE) <= CAST('1996-12-31' AS DATE) - AND CAST("orders"."o_orderdate" AS DATE) >= CAST('1995-01-01' AS DATE) +JOIN "nation" AS "n1" + ON "customer"."c_nationkey" = "n1"."n_nationkey" +JOIN "region" AS "region" + ON "n1"."n_regionkey" = "region"."r_regionkey" AND "region"."r_name" = 'AMERICA' WHERE "part"."p_type" = 'ECONOMY ANODIZED STEEL' GROUP BY @@ -1126,6 +1125,10 @@ FROM "lineitem" AS "lineitem" JOIN "part" AS "part" ON ( "lineitem"."l_partkey" = "part"."p_partkey" + AND "lineitem"."l_quantity" <= 11 + AND "lineitem"."l_quantity" >= 1 + AND "lineitem"."l_shipinstruct" = 'DELIVER IN PERSON' + AND "lineitem"."l_shipmode" IN ('AIR', 'AIR REG') AND "part"."p_brand" = 'Brand#12' AND "part"."p_container" IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') AND "part"."p_size" <= 5 @@ -1133,6 +1136,10 @@ JOIN "part" AS "part" ) OR ( "lineitem"."l_partkey" = "part"."p_partkey" + AND "lineitem"."l_quantity" <= 20 + AND "lineitem"."l_quantity" >= 10 + AND "lineitem"."l_shipinstruct" = 'DELIVER IN PERSON' + AND "lineitem"."l_shipmode" IN ('AIR', 'AIR REG') AND "part"."p_brand" = 'Brand#23' AND "part"."p_container" IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') AND "part"."p_size" <= 10 @@ -1140,6 +1147,10 @@ JOIN "part" AS "part" ) OR ( "lineitem"."l_partkey" = "part"."p_partkey" + AND "lineitem"."l_quantity" <= 30 + AND "lineitem"."l_quantity" >= 20 + AND "lineitem"."l_shipinstruct" = 'DELIVER IN PERSON' + AND "lineitem"."l_shipmode" IN ('AIR', 'AIR REG') AND "part"."p_brand" = 'Brand#34' AND "part"."p_container" IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') AND "part"."p_size" <= 15 -- cgit v1.2.3