From f1c2dbe3b17a0d5edffbb65b85b642d0bb2756c5 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Tue, 19 Dec 2023 12:01:55 +0100 Subject: Merging upstream version 20.3.0. Signed-off-by: Daniel Baumann --- tests/dialects/test_clickhouse.py | 14 + tests/dialects/test_duckdb.py | 9 + tests/dialects/test_postgres.py | 7 + tests/dialects/test_presto.py | 122 +- tests/dialects/test_snowflake.py | 103 +- tests/dialects/test_spark.py | 2 +- tests/dialects/test_tsql.py | 6 + tests/fixtures/identity.sql | 3 + tests/fixtures/optimizer/eliminate_ctes.sql | 68 + tests/fixtures/optimizer/eliminate_subqueries.sql | 10 +- tests/fixtures/optimizer/merge_subqueries.sql | 17 + tests/fixtures/optimizer/optimizer.sql | 39 +- tests/fixtures/optimizer/pushdown_predicates.sql | 4 +- tests/fixtures/optimizer/simplify.sql | 77 +- tests/fixtures/optimizer/tpc-ds/tpc-ds.sql | 1678 +++++++++------------ tests/fixtures/optimizer/tpc-h/tpc-h.sql | 118 +- tests/fixtures/optimizer/unnest_subqueries.sql | 6 +- tests/gen_fixtures.py | 602 ++++++++ tests/test_executor.py | 10 +- tests/test_optimizer.py | 13 +- tests/test_parser.py | 28 +- tests/test_tokens.py | 14 + tests/test_transpile.py | 1 + tests/tpch.py | 115 -- 24 files changed, 1818 insertions(+), 1248 deletions(-) create mode 100644 tests/gen_fixtures.py delete mode 100644 tests/tpch.py (limited to 'tests') diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py index 86ddb00..1f528b6 100644 --- a/tests/dialects/test_clickhouse.py +++ b/tests/dialects/test_clickhouse.py @@ -6,6 +6,8 @@ class TestClickhouse(Validator): dialect = "clickhouse" def test_clickhouse(self): + self.validate_identity("SELECT * FROM x LIMIT 1 UNION ALL SELECT * FROM y") + string_types = [ "BLOB", "LONGBLOB", @@ -68,6 +70,18 @@ class TestClickhouse(Validator): self.validate_identity("CAST(x AS DATETIME)") self.validate_identity("CAST(x as MEDIUMINT)", "CAST(x AS Int32)") self.validate_identity("SELECT arrayJoin([1, 2, 3] AS src) AS dst, 'Hello', src") + self.validate_identity( + "SELECT n, source FROM (SELECT toFloat32(number % 10) AS n, 'original' AS source FROM numbers(10) WHERE number % 3 = 1) ORDER BY n WITH FILL" + ) + self.validate_identity( + "SELECT n, source FROM (SELECT toFloat32(number % 10) AS n, 'original' AS source FROM numbers(10) WHERE number % 3 = 1) ORDER BY n WITH FILL FROM 0 TO 5.51 STEP 0.5" + ) + self.validate_identity( + "SELECT toDate((number * 10) * 86400) AS d1, toDate(number * 86400) AS d2, 'original' AS source FROM numbers(10) WHERE (number % 3) = 1 ORDER BY d2 WITH FILL, d1 WITH FILL STEP 5" + ) + self.validate_identity( + "SELECT n, source, inter FROM (SELECT toFloat32(number % 10) AS n, 'original' AS source, number AS inter FROM numbers(10) WHERE number % 3 = 1) ORDER BY n WITH FILL FROM 0 TO 5.51 STEP 0.5 INTERPOLATE (inter AS inter + 1)" + ) self.validate_identity( "SELECT SUM(1) AS impressions, arrayJoin(cities) AS city, arrayJoin(browsers) AS browser FROM (SELECT ['Istanbul', 'Berlin', 'Bobruisk'] AS cities, ['Firefox', 'Chrome', 'Chrome'] AS browsers) GROUP BY 2, 3" ) diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py index 687a807..f915168 100644 --- a/tests/dialects/test_duckdb.py +++ b/tests/dialects/test_duckdb.py @@ -54,6 +54,13 @@ class TestDuckDB(Validator): }, ) + self.validate_all( + "SELECT {'bla': column1, 'foo': column2, 'bar': column3} AS data FROM source_table", + read={ + "bigquery": "SELECT STRUCT(column1 AS bla, column2 AS foo, column3 AS bar) AS data FROM source_table", + "duckdb": "SELECT {'bla': column1, 'foo': column2, 'bar': column3} AS data FROM source_table", + }, + ) self.validate_all( "WITH cte(x) AS (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) SELECT AVG(x) FILTER (WHERE x > 1) FROM cte", write={ @@ -109,6 +116,8 @@ class TestDuckDB(Validator): parse_one("a // b", read="duckdb").assert_is(exp.IntDiv).sql(dialect="duckdb"), "a // b" ) + self.validate_identity("MAKE_TIMESTAMP(1992, 9, 20, 13, 34, 27.123456)") + self.validate_identity("MAKE_TIMESTAMP(1667810584123456)") self.validate_identity("SELECT EPOCH_MS(10) AS t") self.validate_identity("SELECT MAKE_TIMESTAMP(10) AS t") self.validate_identity("SELECT TO_TIMESTAMP(10) AS t") diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py index 17a65d7..9155696 100644 --- a/tests/dialects/test_postgres.py +++ b/tests/dialects/test_postgres.py @@ -8,6 +8,11 @@ class TestPostgres(Validator): dialect = "postgres" def test_ddl(self): + expr = parse_one("CREATE TABLE t (x INTERVAL day)", read="postgres") + cdef = expr.find(exp.ColumnDef) + cdef.args["kind"].assert_is(exp.DataType) + self.assertEqual(expr.sql(dialect="postgres"), "CREATE TABLE t (x INTERVAL day)") + self.validate_identity("CREATE INDEX idx_x ON x USING BTREE(x, y) WHERE (NOT y IS NULL)") self.validate_identity("CREATE TABLE test (elems JSONB[])") self.validate_identity("CREATE TABLE public.y (x TSTZRANGE NOT NULL)") @@ -203,6 +208,8 @@ class TestPostgres(Validator): self.validate_identity("SELECT 1 OPERATOR(pg_catalog.+) 2") def test_postgres(self): + self.validate_identity("EXEC AS myfunc @id = 123") + expr = parse_one( "SELECT * FROM r CROSS JOIN LATERAL UNNEST(ARRAY[1]) AS s(location)", read="postgres" ) diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py index 6a82756..97a387c 100644 --- a/tests/dialects/test_presto.py +++ b/tests/dialects/test_presto.py @@ -379,6 +379,16 @@ class TestPresto(Validator): "presto": "TIMESTAMP(x, '12:00:00')", }, ) + self.validate_all( + "DATE_ADD('DAY', CAST(x AS BIGINT), y)", + write={ + "presto": "DATE_ADD('DAY', CAST(x AS BIGINT), y)", + }, + read={ + "presto": "DATE_ADD('DAY', x, y)", + }, + ) + self.validate_identity("DATE_ADD('DAY', 1, y)") def test_ddl(self): self.validate_all( @@ -462,10 +472,10 @@ class TestPresto(Validator): ) self.validate_all( - 'CREATE TABLE IF NOT EXISTS x ("cola" INTEGER, "ds" TEXT) WITH (PARTITIONED BY=("ds"))', + """CREATE TABLE IF NOT EXISTS x ("cola" INTEGER, "ds" TEXT) COMMENT 'comment' WITH (PARTITIONED BY=("ds"))""", write={ - "spark": "CREATE TABLE IF NOT EXISTS x (`cola` INT, `ds` STRING) PARTITIONED BY (`ds`)", - "presto": """CREATE TABLE IF NOT EXISTS x ("cola" INTEGER, "ds" VARCHAR) WITH (PARTITIONED_BY=ARRAY['ds'])""", + "spark": "CREATE TABLE IF NOT EXISTS x (`cola` INT, `ds` STRING) COMMENT 'comment' PARTITIONED BY (`ds`)", + "presto": """CREATE TABLE IF NOT EXISTS x ("cola" INTEGER, "ds" VARCHAR) COMMENT 'comment' WITH (PARTITIONED_BY=ARRAY['ds'])""", }, ) @@ -534,26 +544,18 @@ class TestPresto(Validator): }, ) - def test_presto(self): - self.validate_identity("string_agg(x, ',')", "ARRAY_JOIN(ARRAY_AGG(x), ',')") - self.validate_identity( - "SELECT * FROM example.testdb.customer_orders FOR VERSION AS OF 8954597067493422955" - ) - self.validate_identity( - "SELECT * FROM example.testdb.customer_orders FOR TIMESTAMP AS OF CAST('2022-03-23 09:59:29.803 Europe/Vienna' AS TIMESTAMP)" - ) - - self.validate_identity("SELECT * FROM x OFFSET 1 LIMIT 1") - self.validate_identity("SELECT * FROM x OFFSET 1 FETCH FIRST 1 ROWS ONLY") - self.validate_identity("SELECT BOOL_OR(a > 10) FROM asd AS T(a)") - self.validate_identity("SELECT * FROM (VALUES (1))") - self.validate_identity("START TRANSACTION READ WRITE, ISOLATION LEVEL SERIALIZABLE") - self.validate_identity("START TRANSACTION ISOLATION LEVEL REPEATABLE READ") - self.validate_identity("APPROX_PERCENTILE(a, b, c, d)") - self.validate_identity( - "SELECT SPLIT_TO_MAP('a:1;b:2;a:3', ';', ':', (k, v1, v2) -> CONCAT(v1, v2))" - ) + def test_unicode_string(self): + for prefix in ("u&", "U&"): + self.validate_identity( + f"{prefix}'Hello winter \\2603 !'", + "U&'Hello winter \\2603 !'", + ) + self.validate_identity( + f"{prefix}'Hello winter #2603 !' UESCAPE '#'", + "U&'Hello winter #2603 !' UESCAPE '#'", + ) + def test_presto(self): with self.assertLogs(helper_logger) as cm: self.validate_all( "SELECT COALESCE(ELEMENT_AT(MAP_FROM_ENTRIES(ARRAY[(51, '1')]), id), quantity) FROM my_table", @@ -572,6 +574,24 @@ class TestPresto(Validator): }, ) + self.validate_identity("string_agg(x, ',')", "ARRAY_JOIN(ARRAY_AGG(x), ',')") + self.validate_identity("SELECT * FROM x OFFSET 1 LIMIT 1") + self.validate_identity("SELECT * FROM x OFFSET 1 FETCH FIRST 1 ROWS ONLY") + self.validate_identity("SELECT BOOL_OR(a > 10) FROM asd AS T(a)") + self.validate_identity("SELECT * FROM (VALUES (1))") + self.validate_identity("START TRANSACTION READ WRITE, ISOLATION LEVEL SERIALIZABLE") + self.validate_identity("START TRANSACTION ISOLATION LEVEL REPEATABLE READ") + self.validate_identity("APPROX_PERCENTILE(a, b, c, d)") + self.validate_identity( + "SELECT SPLIT_TO_MAP('a:1;b:2;a:3', ';', ':', (k, v1, v2) -> CONCAT(v1, v2))" + ) + self.validate_identity( + "SELECT * FROM example.testdb.customer_orders FOR VERSION AS OF 8954597067493422955" + ) + self.validate_identity( + "SELECT * FROM example.testdb.customer_orders FOR TIMESTAMP AS OF CAST('2022-03-23 09:59:29.803 Europe/Vienna' AS TIMESTAMP)" + ) + self.validate_all( "SELECT MAX_BY(a.id, a.timestamp) FROM a", read={ @@ -1044,3 +1064,61 @@ MATCH_RECOGNIZE ( )""", pretty=True, ) + + def test_to_char(self): + self.validate_all( + "TO_CHAR(ts, 'dd')", + write={ + "bigquery": "FORMAT_DATE('%d', ts)", + "presto": "DATE_FORMAT(ts, '%d')", + }, + ) + self.validate_all( + "TO_CHAR(ts, 'hh')", + write={ + "bigquery": "FORMAT_DATE('%H', ts)", + "presto": "DATE_FORMAT(ts, '%H')", + }, + ) + self.validate_all( + "TO_CHAR(ts, 'hh24')", + write={ + "bigquery": "FORMAT_DATE('%H', ts)", + "presto": "DATE_FORMAT(ts, '%H')", + }, + ) + self.validate_all( + "TO_CHAR(ts, 'mi')", + write={ + "bigquery": "FORMAT_DATE('%M', ts)", + "presto": "DATE_FORMAT(ts, '%i')", + }, + ) + self.validate_all( + "TO_CHAR(ts, 'mm')", + write={ + "bigquery": "FORMAT_DATE('%m', ts)", + "presto": "DATE_FORMAT(ts, '%m')", + }, + ) + self.validate_all( + "TO_CHAR(ts, 'ss')", + write={ + "bigquery": "FORMAT_DATE('%S', ts)", + "presto": "DATE_FORMAT(ts, '%s')", + }, + ) + self.validate_all( + "TO_CHAR(ts, 'yyyy')", + write={ + "bigquery": "FORMAT_DATE('%Y', ts)", + "presto": "DATE_FORMAT(ts, '%Y')", + }, + ) + self.validate_all( + "TO_CHAR(ts, 'yy')", + write={ + "bigquery": "FORMAT_DATE('%y', ts)", + "presto": "DATE_FORMAT(ts, '%y')", + }, + ) diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py index 997c27b..4d8168a 100644 --- a/tests/dialects/test_snowflake.py +++ b/tests/dialects/test_snowflake.py @@ -36,6 +36,8 @@ WHERE )""", ) + self.validate_identity("SELECT TO_ARRAY(CAST(x AS ARRAY))") + self.validate_identity("SELECT TO_ARRAY(CAST(['test'] AS VARIANT))") self.validate_identity("SELECT user_id, value FROM table_name sample ($s) SEED (0)") self.validate_identity("SELECT ARRAY_UNIQUE_AGG(x)") self.validate_identity("SELECT OBJECT_CONSTRUCT()") @@ -72,6 +74,18 @@ WHERE self.validate_identity( 'DESCRIBE TABLE "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF100TCL"."WEB_SITE" type=stage' ) + self.validate_identity( + "SELECT * FROM foo at", + "SELECT * FROM foo AS at", + ) + self.validate_identity( + "SELECT * FROM foo before", + "SELECT * FROM foo AS before", + ) + self.validate_identity( + "SELECT * FROM foo at (col)", + "SELECT * FROM foo AS at(col)", + ) self.validate_identity( "SELECT * FROM unnest(x) with ordinality", "SELECT * FROM TABLE(FLATTEN(INPUT => x)) AS _u(seq, key, path, index, value, this)", @@ -115,11 +129,37 @@ WHERE "SELECT TO_TIMESTAMP(x) FROM t", "SELECT CAST(x AS TIMESTAMPNTZ) FROM t", ) + self.validate_identity( + "CAST(x AS BYTEINT)", + "CAST(x AS INT)", + ) + self.validate_identity( + "CAST(x AS CHAR VARYING)", + "CAST(x AS VARCHAR)", + ) + self.validate_identity( + "CAST(x AS CHARACTER VARYING)", + "CAST(x AS VARCHAR)", + ) + self.validate_identity( + "CAST(x AS NCHAR VARYING)", + "CAST(x AS VARCHAR)", + ) - self.validate_all("CAST(x AS BYTEINT)", write={"snowflake": "CAST(x AS INT)"}) - self.validate_all("CAST(x AS CHAR VARYING)", write={"snowflake": "CAST(x AS VARCHAR)"}) - self.validate_all("CAST(x AS CHARACTER VARYING)", write={"snowflake": "CAST(x AS VARCHAR)"}) - self.validate_all("CAST(x AS NCHAR VARYING)", write={"snowflake": "CAST(x AS VARCHAR)"}) + self.validate_all( + "SELECT TO_ARRAY(['test'])", + write={ + "snowflake": "SELECT TO_ARRAY(['test'])", + "spark": "SELECT ARRAY('test')", + }, + ) + self.validate_all( + "SELECT TO_ARRAY(['test'])", + write={ + "snowflake": "SELECT TO_ARRAY(['test'])", + "spark": "SELECT ARRAY('test')", + }, + ) self.validate_all( # We need to qualify the columns in this query because "value" would be ambiguous 'WITH t(x, "value") AS (SELECT [1, 2, 3], 1) SELECT IFF(_u.pos = _u_2.pos_2, _u_2."value", NULL) AS "value" FROM t, TABLE(FLATTEN(INPUT => ARRAY_GENERATE_RANGE(0, (GREATEST(ARRAY_SIZE(t.x)) - 1) + 1))) AS _u(seq, key, path, index, pos, this) CROSS JOIN TABLE(FLATTEN(INPUT => t.x)) AS _u_2(seq, key, path, pos_2, "value", this) WHERE _u.pos = _u_2.pos_2 OR (_u.pos > (ARRAY_SIZE(t.x) - 1) AND _u_2.pos_2 = (ARRAY_SIZE(t.x) - 1))', @@ -489,8 +529,8 @@ WHERE self.validate_all( "TO_ARRAY(x)", write={ - "spark": "ARRAY(x)", - "snowflake": "[x]", + "spark": "IF(x IS NULL, NULL, ARRAY(x))", + "snowflake": "TO_ARRAY(x)", }, ) self.validate_all( @@ -626,6 +666,10 @@ WHERE "SELECT * FROM @mystage t (c1)", "SELECT * FROM @mystage AS t(c1)", ) + self.validate_identity( + "SELECT * FROM @foo/bar (PATTERN => 'test', FILE_FORMAT => ds_sandbox.test.my_csv_format) AS bla", + "SELECT * FROM @foo/bar (FILE_FORMAT => ds_sandbox.test.my_csv_format, PATTERN => 'test') AS bla", + ) def test_sample(self): self.validate_identity("SELECT * FROM testtable TABLESAMPLE BERNOULLI (20.3)") @@ -775,6 +819,53 @@ WHERE }, ) + def test_historical_data(self): + self.validate_identity("SELECT * FROM my_table AT (STATEMENT => $query_id_var)") + self.validate_identity("SELECT * FROM my_table AT (OFFSET => -60 * 5)") + self.validate_identity("SELECT * FROM my_table BEFORE (STATEMENT => $query_id_var)") + self.validate_identity("SELECT * FROM my_table BEFORE (OFFSET => -60 * 5)") + self.validate_identity("CREATE SCHEMA restored_schema CLONE my_schema AT (OFFSET => -3600)") + self.validate_identity( + "CREATE TABLE restored_table CLONE my_table AT (TIMESTAMP => CAST('Sat, 09 May 2015 01:01:00 +0300' AS TIMESTAMPTZ))", + ) + self.validate_identity( + "CREATE DATABASE restored_db CLONE my_db BEFORE (STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726')" + ) + self.validate_identity( + "SELECT * FROM my_table AT (TIMESTAMP => TO_TIMESTAMP(1432669154242, 3))" + ) + self.validate_identity( + "SELECT * FROM my_table AT (OFFSET => -60 * 5) AS T WHERE T.flag = 'valid'" + ) + self.validate_identity( + "SELECT * FROM my_table AT (STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726')" + ) + self.validate_identity( + "SELECT * FROM my_table BEFORE (STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726')" + ) + self.validate_identity( + "SELECT * FROM my_table AT (TIMESTAMP => 'Fri, 01 May 2015 16:20:00 -0700'::timestamp)", + "SELECT * FROM my_table AT (TIMESTAMP => CAST('Fri, 01 May 2015 16:20:00 -0700' AS TIMESTAMPNTZ))", + ) + self.validate_identity( + "SELECT * FROM my_table AT(TIMESTAMP => 'Fri, 01 May 2015 16:20:00 -0700'::timestamp_tz)", + "SELECT * FROM my_table AT (TIMESTAMP => CAST('Fri, 01 May 2015 16:20:00 -0700' AS TIMESTAMPTZ))", + ) + self.validate_identity( + "SELECT * FROM my_table BEFORE (TIMESTAMP => 'Fri, 01 May 2015 16:20:00 -0700'::timestamp_tz);", + "SELECT * FROM my_table BEFORE (TIMESTAMP => CAST('Fri, 01 May 2015 16:20:00 -0700' AS TIMESTAMPTZ))", + ) + self.validate_identity( + """ + SELECT oldt.* , newt.* + FROM my_table BEFORE(STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726') AS oldt + FULL OUTER JOIN my_table AT(STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726') AS newt + ON oldt.id = newt.id + WHERE oldt.id IS NULL OR newt.id IS NULL; + """, + "SELECT oldt.*, newt.* FROM my_table BEFORE (STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726') AS oldt FULL OUTER JOIN my_table AT (STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726') AS newt ON oldt.id = newt.id WHERE oldt.id IS NULL OR newt.id IS NULL", + ) + def test_ddl(self): self.validate_identity( """create external table et2( diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py index fe37027..24f68f8 100644 --- a/tests/dialects/test_spark.py +++ b/tests/dialects/test_spark.py @@ -75,7 +75,7 @@ class TestSpark(Validator): col_a INTEGER, date VARCHAR ) -COMMENT='Test comment: blah' +COMMENT 'Test comment: blah' WITH ( PARTITIONED_BY=ARRAY['date'], FORMAT='ICEBERG', diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py index 07179ef..a2569ab 100644 --- a/tests/dialects/test_tsql.py +++ b/tests/dialects/test_tsql.py @@ -20,6 +20,12 @@ class TestTSQL(Validator): self.validate_identity("1 AND true", "1 <> 0 AND (1 = 1)") self.validate_identity("CAST(x AS int) OR y", "CAST(x AS INTEGER) <> 0 OR y <> 0") + self.validate_all( + "SELECT TOP 1 * FROM (SELECT x FROM t1 UNION ALL SELECT x FROM t2) AS _l_0", + read={ + "": "SELECT x FROM t1 UNION ALL SELECT x FROM t2 LIMIT 1", + }, + ) self.validate_all( "WITH t(c) AS (SELECT 1) SELECT * INTO foo FROM (SELECT c AS c FROM t) AS temp", read={ diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql index effebca..228f109 100644 --- a/tests/fixtures/identity.sql +++ b/tests/fixtures/identity.sql @@ -865,5 +865,8 @@ KILL CONNECTION 123 KILL QUERY '123' CHR(97) SELECT * FROM UNNEST(x) WITH ORDINALITY UNION ALL SELECT * FROM UNNEST(y) WITH ORDINALITY +SELECT x FROM t1 UNION ALL SELECT x FROM t2 LIMIT 1 +SELECT x FROM t1 UNION ALL SELECT x FROM t2 UNION ALL SELECT x FROM t3 LIMIT 1 WITH use(use) AS (SELECT 1) SELECT use FROM use SELECT recursive FROM t +SELECT (ROW_NUMBER() OVER (PARTITION BY user ORDER BY date ASC) - ROW_NUMBER() OVER (PARTITION BY user, segment ORDER BY date ASC)) AS group_id FROM example_table diff --git a/tests/fixtures/optimizer/eliminate_ctes.sql b/tests/fixtures/optimizer/eliminate_ctes.sql index 11e5e4f..f1f78f4 100644 --- a/tests/fixtures/optimizer/eliminate_ctes.sql +++ b/tests/fixtures/optimizer/eliminate_ctes.sql @@ -46,3 +46,71 @@ FROM x; SELECT a FROM x; + +# title: CTE reference in subquery where alias matches outer table name +WITH q AS ( + SELECT + a + FROM y +) +SELECT + a +FROM x AS q +WHERE + a IN ( + SELECT + a + FROM q + ); +WITH q AS ( + SELECT + a + FROM y +) +SELECT + a +FROM x AS q +WHERE + a IN ( + SELECT + a + FROM q + ); + +# title: CTE reference in subquery where alias matches outer table name and outer alias is also CTE +WITH q AS ( + SELECT + a + FROM y +), q2 AS ( + SELECT + a + FROM y +) +SELECT + a +FROM q2 AS q +WHERE + a IN ( + SELECT + a + FROM q + ); +WITH q AS ( + SELECT + a + FROM y +), q2 AS ( + SELECT + a + FROM y +) +SELECT + a +FROM q2 AS q +WHERE + a IN ( + SELECT + a + FROM q + ); \ No newline at end of file diff --git a/tests/fixtures/optimizer/eliminate_subqueries.sql b/tests/fixtures/optimizer/eliminate_subqueries.sql index e18d2a4..f2b117b 100644 --- a/tests/fixtures/optimizer/eliminate_subqueries.sql +++ b/tests/fixtures/optimizer/eliminate_subqueries.sql @@ -42,17 +42,9 @@ WITH y AS (SELECT a FROM x), z AS (SELECT a FROM y AS y) SELECT a FROM z AS z CR WITH y AS (SELECT a FROM (SELECT a FROM x) AS y) SELECT a FROM y; WITH y_2 AS (SELECT a FROM x), y AS (SELECT a FROM y_2 AS y) SELECT a FROM y; --- Union -SELECT 1 AS x, 2 AS y UNION ALL SELECT 1 AS x, 2 AS y; -WITH cte AS (SELECT 1 AS x, 2 AS y) SELECT cte.x AS x, cte.y AS y FROM cte AS cte UNION ALL SELECT cte.x AS x, cte.y AS y FROM cte AS cte; - -- Union of selects with derived tables (SELECT a FROM (SELECT b FROM x)) UNION (SELECT a FROM (SELECT b FROM y)); -WITH cte AS (SELECT b FROM x), cte_2 AS (SELECT a FROM cte AS cte), cte_3 AS (SELECT b FROM y), cte_4 AS (SELECT a FROM cte_3 AS cte_3) (SELECT cte_2.a AS a FROM cte_2 AS cte_2) UNION (SELECT cte_4.a AS a FROM cte_4 AS cte_4); - --- Three unions -SELECT a FROM x UNION ALL SELECT a FROM y UNION ALL SELECT a FROM z; -WITH cte AS (SELECT a FROM x), cte_2 AS (SELECT a FROM y), cte_3 AS (SELECT a FROM z), cte_4 AS (SELECT cte_2.a AS a FROM cte_2 AS cte_2 UNION ALL SELECT cte_3.a AS a FROM cte_3 AS cte_3) SELECT cte.a AS a FROM cte AS cte UNION ALL SELECT cte_4.a AS a FROM cte_4 AS cte_4; +WITH cte AS (SELECT b FROM x), cte_2 AS (SELECT b FROM y) (SELECT a FROM cte AS cte) UNION (SELECT a FROM cte_2 AS cte_2); -- Subquery SELECT a FROM x WHERE b = (SELECT y.c FROM y); diff --git a/tests/fixtures/optimizer/merge_subqueries.sql b/tests/fixtures/optimizer/merge_subqueries.sql index eec8a73..7fc4b25 100644 --- a/tests/fixtures/optimizer/merge_subqueries.sql +++ b/tests/fixtures/optimizer/merge_subqueries.sql @@ -411,3 +411,20 @@ FROM ( ON _q_0.a = y.b ); SELECT y.b AS b FROM (x AS x JOIN y AS y ON x.a = y.b); + +# title: merge cte into subquery with overlapping alias +WITH q AS ( + SELECT + y.b AS a + FROM y AS y +) +SELECT + q.a AS a +FROM x AS q +WHERE + q.a IN ( + SELECT + q.a AS a + FROM q AS q + ); +SELECT q.a AS a FROM x AS q WHERE q.a IN (SELECT y.b AS a FROM y AS y); \ No newline at end of file diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql index f81d54a..4ae9e90 100644 --- a/tests/fixtures/optimizer/optimizer.sql +++ b/tests/fixtures/optimizer/optimizer.sql @@ -131,7 +131,7 @@ SELECT SUM("y"."b") AS "sum_b" FROM "x" AS "x" LEFT JOIN "_u_0" AS "_u_0" - ON "x"."b" = "_u_0"."_u_1" + ON "_u_0"."_u_1" = "x"."b" JOIN "y" AS "y" ON "x"."b" = "y"."b" WHERE @@ -522,7 +522,7 @@ OR ( SELECT *, IFF( - IFF("unioned"."uploaded_at" >= '2022-06-16', 'workday', 'bamboohr') = "unioned"."source_system", + "unioned"."source_system" = IFF("unioned"."uploaded_at" >= '2022-06-16', 'workday', 'bamboohr'), 1, 0 ) AS "sort_order" @@ -950,7 +950,7 @@ SELECT FROM "y" AS "y" CROSS JOIN "_u_0" AS "_u_0" JOIN "x" AS "x" - ON "y"."b" = "x"."b" + ON "x"."b" = "y"."b" GROUP BY "x"."a"; @@ -989,7 +989,7 @@ SELECT COALESCE("m"."a", "foo"."a") AS "a" FROM "m" JOIN "n" AS "foo"("a") - ON "m"."a" = "foo"."a"; + ON "foo"."a" = "m"."a"; # title: reduction of string concatenation that uses CONCAT(..), || and + # execute: false @@ -1068,7 +1068,7 @@ SELECT COALESCE("alias3"."c_od", 0) AS "c_od" FROM "table1" AS "table1" LEFT JOIN "alias3" - ON "table1"."cid" = "alias3"."cid"; + ON "alias3"."cid" = "table1"."cid"; # title: CTE with EXPLODE cannot be merged # dialect: spark @@ -1115,3 +1115,32 @@ WITH `t` AS ( SELECT `t`.`CoL` AS `CoL` FROM `t`; + +# title: top-level query is parenthesized +# execute: false +WITH x AS ( + SELECT a FROM t +) +( + SELECT * FROM x + UNION ALL + SELECT * FROM x + LIMIT 10 +) +LIMIT 10; +WITH "x" AS ( + SELECT + "t"."a" AS "a" + FROM "t" AS "t" +) +( + SELECT + "x"."a" AS "a" + FROM "x" + UNION ALL + SELECT + "x"."a" AS "a" + FROM "x" + LIMIT 10 +) +LIMIT 10; diff --git a/tests/fixtures/optimizer/pushdown_predicates.sql b/tests/fixtures/optimizer/pushdown_predicates.sql index 9912b89..43f9842 100644 --- a/tests/fixtures/optimizer/pushdown_predicates.sql +++ b/tests/fixtures/optimizer/pushdown_predicates.sql @@ -26,10 +26,10 @@ SELECT x.a FROM x AS x JOIN (SELECT y.a FROM y AS y) AS y ON y.a = 1 AND x.a = y SELECT x.a FROM x AS x JOIN (SELECT y.a FROM y AS y WHERE y.a = 1) AS y ON x.a = y.a AND TRUE; SELECT x.a AS a FROM x AS x JOIN (SELECT * FROM y AS y) AS y ON y.a = 1 WHERE x.a = 1 AND x.b = 1 AND y.a = x.a; -SELECT x.a AS a FROM x AS x JOIN (SELECT * FROM y AS y WHERE y.a = 1) AS y ON TRUE AND y.a = x.a WHERE x.a = 1 AND x.b = 1 AND TRUE; +SELECT x.a AS a FROM x AS x JOIN (SELECT * FROM y AS y WHERE y.a = 1) AS y ON x.a = y.a AND TRUE WHERE x.a = 1 AND TRUE AND x.b = 1; SELECT x.a AS a FROM x AS x CROSS JOIN (SELECT * FROM y AS y) AS y WHERE x.a = 1 AND x.b = 1 AND y.a = x.a AND y.a = 1; -SELECT x.a AS a FROM x AS x JOIN (SELECT * FROM y AS y WHERE y.a = 1) AS y ON TRUE AND y.a = x.a WHERE x.a = 1 AND x.b = 1 AND TRUE AND TRUE; +SELECT x.a AS a FROM x AS x JOIN (SELECT * FROM y AS y WHERE y.a = 1) AS y ON x.a = y.a AND TRUE WHERE x.a = 1 AND TRUE AND x.b = 1 AND TRUE; with t1 as (SELECT x.a, x.b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) as row_num FROM x) SELECT t1.a, t1.b FROM t1 WHERE row_num = 1; WITH t1 AS (SELECT x.a, x.b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x) SELECT t1.a, t1.b FROM t1 WHERE row_num = 1; diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql index 2206e28..fbf5d2c 100644 --- a/tests/fixtures/optimizer/simplify.sql +++ b/tests/fixtures/optimizer/simplify.sql @@ -645,6 +645,9 @@ x < 5 AND x > 3; x > 3 AND 5 < x AND x BETWEEN 9 AND 10; x <= 10 AND x >= 9; +NOT x BETWEEN 0 AND 1; +x < 0 OR x > 1; + 1 < x AND 3 < x; x > 3; @@ -657,6 +660,42 @@ x <> 2018 OR x = 2018; t0.x = t1.x AND t0.y < t1.y AND t0.y <= t1.y; t0.x = t1.x AND t0.y < t1.y AND t0.y <= t1.y; +1 < x; +x > 1; + +1 <= x; +x >= 1; + +1 > x; +x < 1; + +1 >= x; +x <= 1; + +1 = x; +x = 1; + +1 <> x; +x <> 1; + +NOT 1 < x; +x <= 1; + +NOT 1 <= x; +x < 1; + +NOT 1 > x; +x >= 1; + +NOT 1 >= x; +x > 1; + +NOT 1 = x; +x <> 1; + +NOT 1 <> x; +x = 1; + -------------------------------------- -- COALESCE -------------------------------------- @@ -667,7 +706,7 @@ COALESCE(x, 1) = 2; NOT x IS NULL AND x = 2; 2 = COALESCE(x, 1); -2 = x AND NOT x IS NULL; +NOT x IS NULL AND x = 2; COALESCE(x, 1, 1) = 1 + 1; NOT x IS NULL AND x = 2; @@ -759,6 +798,20 @@ CONCAT(a, b) IN (SELECT * FROM foo WHERE cond); -------------------------------------- -- DATE_TRUNC -------------------------------------- +DATE_TRUNC('week', CAST('2023-12-15' AS DATE)); +CAST('2023-12-11' AS DATE); + +DATE_TRUNC('week', CAST('2023-12-16' AS DATE)); +CAST('2023-12-11' AS DATE); + +# dialect: bigquery +DATE_TRUNC(CAST('2023-12-15' AS DATE), WEEK); +CAST('2023-12-10' AS DATE); + +# dialect: bigquery +DATE_TRUNC(CAST('2023-12-16' AS DATE), WEEK); +CAST('2023-12-10' AS DATE); + DATE_TRUNC('year', x) = CAST('2021-01-01' AS DATE); x < CAST('2022-01-01' AS DATE) AND x >= CAST('2021-01-01' AS DATE); @@ -801,7 +854,7 @@ DATE_TRUNC('year', x) < CAST('2021-01-01' AS DATE); x < CAST('2021-01-01' AS DATE); DATE_TRUNC('year', x) < CAST('2021-01-02' AS DATE); -x < CAST('2021-01-01' AS DATE); +x < CAST('2022-01-01' AS DATE); DATE_TRUNC('year', x) >= CAST('2021-01-01' AS DATE); x >= CAST('2021-01-01' AS DATE); @@ -841,7 +894,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); -DATE_TRUNC('day', x) = CAST(y AS DATE); +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); @@ -905,10 +958,10 @@ DATE_SUB(x, 1, DAY) <> CAST('2021-01-01' AS DATE); x <> CAST('2021-01-02' AS DATE); DATE_ADD(DATE_ADD(DATE_TRUNC('week', DATE_SUB(x, 1, DAY)), 1, DAY), 1, YEAR) < CAST('2021-01-08' AS DATE); -x < CAST('2020-01-07' AS DATE); +x < CAST('2020-01-14' AS DATE); x - INTERVAL '1' day = CAST(y AS DATE); -x - INTERVAL '1' day = CAST(y AS DATE); +CAST(y AS DATE) = x - INTERVAL '1' day; -------------------------------------- -- Constant Propagation @@ -917,16 +970,16 @@ x = 5 AND y = x; x = 5 AND y = 5; 5 = x AND y = x; -5 = x AND y = 5; +x = 5 AND y = 5; x = 5 OR y = x; -x = 5 OR y = x; +x = 5 OR x = y; (x = 5 AND y = x) OR y = 1; (x = 5 AND y = 5) OR y = 1; t.x = 5 AND y = x; -t.x = 5 AND y = x; +t.x = 5 AND x = y; t.x = 'a' AND y = CONCAT_WS('-', t.x, 'b'); t.x = 'a' AND y = 'a-b'; @@ -938,7 +991,7 @@ x = 5 AND x = 6; FALSE; x = 5 AND (y = x OR z = 1); -x = 5 AND (y = x OR z = 1); +x = 5 AND (x = y OR z = 1); x = 5 AND x + 3 = 8; x = 5; @@ -950,7 +1003,7 @@ x = 1 AND y > 0 AND (SELECT z = 5 FROM t WHERE y = 1); (SELECT z = 5 FROM t WHERE y = 1) AND x = 1 AND y > 0; x = 1 AND x = y AND (SELECT z FROM t WHERE a AND (b OR c)); -(SELECT z FROM t WHERE a AND (b OR c)) AND 1 = y AND x = 1; +(SELECT z FROM t WHERE a AND (b OR c)) AND x = 1 AND y = 1; t1.a = 39 AND t2.b = t1.a AND t3.c = t2.b; t1.a = 39 AND t2.b = 39 AND t3.c = 39; @@ -968,7 +1021,7 @@ x = y AND CASE WHEN x = 5 THEN FALSE ELSE TRUE END; CASE WHEN x = 5 THEN FALSE ELSE TRUE END AND x = y; x = 1 AND CASE WHEN y = 5 THEN x = z END; -CASE WHEN y = 5 THEN 1 = z END AND x = 1; +CASE WHEN y = 5 THEN z = 1 END AND x = 1; -------------------------------------- -- Simplify Conditionals @@ -1028,4 +1081,4 @@ CASE x WHEN y THEN z END; CASE WHEN x = y THEN z END; CASE x1 + x2 WHEN x3 THEN x4 WHEN x5 + x6 THEN x7 ELSE x8 END; -CASE WHEN (x1 + x2) = x3 THEN x4 WHEN (x1 + x2) = (x5 + x6) THEN x7 ELSE x8 END; +CASE WHEN x3 = (x1 + x2) THEN x4 WHEN (x1 + x2) = (x5 + x6) THEN x7 ELSE x8 END; diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql index d8cf64f..f0f584f 100644 --- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql +++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql @@ -30,8 +30,8 @@ WITH "customer_total_return" AS ( SUM("store_returns"."sr_return_amt") AS "ctr_total_return" FROM "store_returns" AS "store_returns" JOIN "date_dim" AS "date_dim" - ON "date_dim"."d_year" = 2001 - AND "store_returns"."sr_returned_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_returns"."sr_returned_date_sk" + AND "date_dim"."d_year" = 2001 GROUP BY "store_returns"."sr_customer_sk", "store_returns"."sr_store_sk" @@ -47,13 +47,13 @@ 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" + ON "_u_0"."_u_1" = "ctr1"."ctr_store_sk" JOIN "customer" AS "customer" ON "ctr1"."ctr_customer_sk" = "customer"."c_customer_sk" JOIN "store" AS "store" - ON "store"."s_state" = 'TN' AND "store"."s_store_sk" = "ctr1"."ctr_store_sk" + ON "ctr1"."ctr_store_sk" = "store"."s_store_sk" AND "store"."s_state" = 'TN' WHERE - "ctr1"."ctr_total_return" > "_u_0"."_col_0" + "_u_0"."_col_0" < "ctr1"."ctr_total_return" ORDER BY "c_customer_id" LIMIT 100; @@ -246,7 +246,7 @@ FROM "date_dim" AS "dt" JOIN "store_sales" AS "store_sales" ON "dt"."d_date_sk" = "store_sales"."ss_sold_date_sk" JOIN "item" AS "item" - ON "item"."i_manufact_id" = 427 AND "store_sales"."ss_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "store_sales"."ss_item_sk" AND "item"."i_manufact_id" = 427 WHERE "dt"."d_moy" = 11 GROUP BY @@ -429,7 +429,7 @@ WITH "customer_2" AS ( "date_dim"."d_date_sk" AS "d_date_sk", "date_dim"."d_year" AS "d_year" FROM "date_dim" AS "date_dim" -), "cte" AS ( +), "year_total" AS ( SELECT "customer"."c_customer_id" AS "customer_id", "customer"."c_first_name" AS "customer_first_name", @@ -448,7 +448,7 @@ WITH "customer_2" AS ( JOIN "store_sales" AS "store_sales" ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk" JOIN "date_dim_2" AS "date_dim" - ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" GROUP BY "customer"."c_customer_id", "customer"."c_first_name", @@ -458,7 +458,7 @@ WITH "customer_2" AS ( "customer"."c_login", "customer"."c_email_address", "date_dim"."d_year" -), "cte_2" AS ( + UNION ALL SELECT "customer"."c_customer_id" AS "customer_id", "customer"."c_first_name" AS "customer_first_name", @@ -477,7 +477,7 @@ WITH "customer_2" AS ( 'c' AS "sale_type" FROM "customer_2" AS "customer" JOIN "catalog_sales" AS "catalog_sales" - ON "customer"."c_customer_sk" = "catalog_sales"."cs_bill_customer_sk" + ON "catalog_sales"."cs_bill_customer_sk" = "customer"."c_customer_sk" JOIN "date_dim_2" AS "date_dim" ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk" GROUP BY @@ -489,7 +489,7 @@ WITH "customer_2" AS ( "customer"."c_login", "customer"."c_email_address", "date_dim"."d_year" -), "cte_3" AS ( + UNION ALL SELECT "customer"."c_customer_id" AS "customer_id", "customer"."c_first_name" AS "customer_first_name", @@ -510,7 +510,7 @@ WITH "customer_2" AS ( JOIN "web_sales" AS "web_sales" ON "customer"."c_customer_sk" = "web_sales"."ws_bill_customer_sk" JOIN "date_dim_2" AS "date_dim" - ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk" GROUP BY "customer"."c_customer_id", "customer"."c_first_name", @@ -520,46 +520,6 @@ WITH "customer_2" AS ( "customer"."c_login", "customer"."c_email_address", "date_dim"."d_year" -), "cte_4" AS ( - SELECT - "cte_2"."customer_id" AS "customer_id", - "cte_2"."customer_first_name" AS "customer_first_name", - "cte_2"."customer_last_name" AS "customer_last_name", - "cte_2"."customer_preferred_cust_flag" AS "customer_preferred_cust_flag", - "cte_2"."dyear" AS "dyear", - "cte_2"."year_total" AS "year_total", - "cte_2"."sale_type" AS "sale_type" - FROM "cte_2" AS "cte_2" - UNION ALL - SELECT - "cte_3"."customer_id" AS "customer_id", - "cte_3"."customer_first_name" AS "customer_first_name", - "cte_3"."customer_last_name" AS "customer_last_name", - "cte_3"."customer_preferred_cust_flag" AS "customer_preferred_cust_flag", - "cte_3"."dyear" AS "dyear", - "cte_3"."year_total" AS "year_total", - "cte_3"."sale_type" AS "sale_type" - FROM "cte_3" AS "cte_3" -), "year_total" AS ( - SELECT - "cte"."customer_id" AS "customer_id", - "cte"."customer_first_name" AS "customer_first_name", - "cte"."customer_last_name" AS "customer_last_name", - "cte"."customer_preferred_cust_flag" AS "customer_preferred_cust_flag", - "cte"."dyear" AS "dyear", - "cte"."year_total" AS "year_total", - "cte"."sale_type" AS "sale_type" - FROM "cte" AS "cte" - UNION ALL - SELECT - "cte_4"."customer_id" AS "customer_id", - "cte_4"."customer_first_name" AS "customer_first_name", - "cte_4"."customer_last_name" AS "customer_last_name", - "cte_4"."customer_preferred_cust_flag" AS "customer_preferred_cust_flag", - "cte_4"."dyear" AS "dyear", - "cte_4"."year_total" AS "year_total", - "cte_4"."sale_type" AS "sale_type" - FROM "cte_4" AS "cte_4" ) SELECT "t_s_secyear"."customer_id" AS "customer_id", @@ -568,11 +528,11 @@ SELECT "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"."dyear" = 2002 + 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 "t_s_firstyear"."customer_id" = "t_c_secyear"."customer_id" JOIN "year_total" AS "t_s_secyear" - ON "t_s_secyear"."customer_id" = "t_s_firstyear"."customer_id" + 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" @@ -585,10 +545,10 @@ JOIN "year_total" AS "t_w_secyear" AND "t_w_secyear"."dyear" = 2002 AND "t_w_secyear"."sale_type" = 'w' JOIN "year_total" AS "t_c_firstyear" - ON "t_c_firstyear"."dyear" = 2001 + 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 - AND "t_s_firstyear"."customer_id" = "t_c_firstyear"."customer_id" AND CASE WHEN "t_c_firstyear"."year_total" > 0 THEN "t_c_secyear"."year_total" / "t_c_firstyear"."year_total" @@ -782,7 +742,7 @@ WITH "salesreturns" AS ( SUM("salesreturns"."net_loss") AS "profit_loss" FROM "salesreturns" AS "salesreturns" JOIN "date_dim_2" AS "date_dim" - ON "salesreturns"."date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "salesreturns"."date_sk" JOIN "store" AS "store" ON "salesreturns"."store_sk" = "store"."s_store_sk" GROUP BY @@ -814,9 +774,9 @@ WITH "salesreturns" AS ( SUM("salesreturns"."net_loss") AS "profit_loss" FROM "salesreturns_2" AS "salesreturns" JOIN "catalog_page" AS "catalog_page" - ON "salesreturns"."page_sk" = "catalog_page"."cp_catalog_page_sk" + ON "catalog_page"."cp_catalog_page_sk" = "salesreturns"."page_sk" JOIN "date_dim_2" AS "date_dim" - ON "salesreturns"."date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "salesreturns"."date_sk" GROUP BY "catalog_page"."cp_catalog_page_id" ), "salesreturns_3" AS ( @@ -849,12 +809,20 @@ WITH "salesreturns" AS ( SUM("salesreturns"."net_loss") AS "profit_loss" FROM "salesreturns_3" AS "salesreturns" JOIN "date_dim_2" AS "date_dim" - ON "salesreturns"."date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "salesreturns"."date_sk" JOIN "web_site" AS "web_site" ON "salesreturns"."wsr_web_site_sk" = "web_site"."web_site_sk" GROUP BY "web_site"."web_site_id" -), "cte_10" AS ( +), "x" AS ( + SELECT + 'store channel' AS "channel", + CONCAT('store', "ssr"."s_store_id") AS "id", + "ssr"."sales" AS "sales", + "ssr"."returns1" AS "returns1", + "ssr"."profit" - "ssr"."profit_loss" AS "profit" + FROM "ssr" + UNION ALL SELECT 'catalog channel' AS "channel", CONCAT('catalog_page', "csr"."cp_catalog_page_id") AS "id", @@ -870,22 +838,6 @@ WITH "salesreturns" AS ( "wsr"."returns1" AS "returns1", "wsr"."profit" - "wsr"."profit_loss" AS "profit" FROM "wsr" -), "x" AS ( - SELECT - 'store channel' AS "channel", - CONCAT('store', "ssr"."s_store_id") AS "id", - "ssr"."sales" AS "sales", - "ssr"."returns1" AS "returns1", - "ssr"."profit" - "ssr"."profit_loss" AS "profit" - FROM "ssr" - UNION ALL - SELECT - "cte_10"."channel" AS "channel", - "cte_10"."id" AS "id", - "cte_10"."sales" AS "sales", - "cte_10"."returns1" AS "returns1", - "cte_10"."profit" AS "profit" - FROM "cte_10" AS "cte_10" ) SELECT "x"."channel" AS "channel", @@ -952,11 +904,11 @@ JOIN "customer" AS "c" JOIN "store_sales" AS "s" ON "c"."c_customer_sk" = "s"."ss_customer_sk" JOIN "date_dim" AS "d" - ON "s"."ss_sold_date_sk" = "d"."d_date_sk" + ON "d"."d_date_sk" = "s"."ss_sold_date_sk" JOIN "item" AS "i" - ON "s"."ss_item_sk" = "i"."i_item_sk" + ON "i"."i_item_sk" = "s"."ss_item_sk" JOIN "_u_0" AS "_u_0" - ON "d"."d_month_seq" = "_u_0"."d_month_seq" + ON "_u_0"."d_month_seq" = "d"."d_month_seq" LEFT JOIN "_u_1" AS "_u_1" ON "_u_1"."_u_2" = "i"."i_category" WHERE @@ -1003,20 +955,20 @@ SELECT AVG("store_sales"."ss_sales_price") AS "agg4" FROM "store_sales" AS "store_sales" JOIN "customer_demographics" AS "customer_demographics" - ON "customer_demographics"."cd_education_status" = '2 yr Degree' + ON "customer_demographics"."cd_demo_sk" = "store_sales"."ss_cdemo_sk" + AND "customer_demographics"."cd_education_status" = '2 yr Degree' AND "customer_demographics"."cd_gender" = 'F' AND "customer_demographics"."cd_marital_status" = 'W' - AND "store_sales"."ss_cdemo_sk" = "customer_demographics"."cd_demo_sk" JOIN "date_dim" AS "date_dim" - ON "date_dim"."d_year" = 1998 - AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" + AND "date_dim"."d_year" = 1998 JOIN "item" AS "item" - ON "store_sales"."ss_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "store_sales"."ss_item_sk" JOIN "promotion" AS "promotion" ON ( "promotion"."p_channel_email" = 'N' OR "promotion"."p_channel_event" = 'N' ) - AND "store_sales"."ss_promo_sk" = "promotion"."p_promo_sk" + AND "promotion"."p_promo_sk" = "store_sales"."ss_promo_sk" GROUP BY "item"."i_item_id" ORDER BY @@ -1257,8 +1209,8 @@ WITH "a1" AS ( SUBSTR("customer_address"."ca_zip", 1, 5) AS "ca_zip" 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" + ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk" + AND "customer"."c_preferred_cust_flag" = 'Y' GROUP BY "customer_address"."ca_zip" HAVING @@ -1279,13 +1231,13 @@ SELECT SUM("store_sales"."ss_net_profit") AS "_col_1" FROM "store_sales" AS "store_sales" JOIN "date_dim" AS "date_dim" - ON "date_dim"."d_qoy" = 2 + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" + AND "date_dim"."d_qoy" = 2 AND "date_dim"."d_year" = 2000 - AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" JOIN "store" AS "store" - ON "store_sales"."ss_store_sk" = "store"."s_store_sk" + ON "store"."s_store_sk" = "store_sales"."ss_store_sk" JOIN "a2" AS "a2" - ON SUBSTR("store"."s_zip", 1, 2) = SUBSTR("a2"."ca_zip", 1, 2) + ON SUBSTR("a2"."ca_zip", 1, 2) = SUBSTR("store"."s_zip", 1, 2) GROUP BY "store"."s_store_name" ORDER BY @@ -1595,7 +1547,7 @@ WITH "date_dim_2" AS ( "store_sales"."ss_customer_sk" AS "_u_1" FROM "store_sales" AS "store_sales" JOIN "date_dim_2" AS "date_dim" - ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" GROUP BY "store_sales"."ss_customer_sk" ), "_u_2" AS ( @@ -1603,7 +1555,7 @@ WITH "date_dim_2" AS ( "web_sales"."ws_bill_customer_sk" AS "_u_3" FROM "web_sales" AS "web_sales" JOIN "date_dim_2" AS "date_dim" - ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk" GROUP BY "web_sales"."ws_bill_customer_sk" ), "_u_4" AS ( @@ -1632,16 +1584,16 @@ SELECT COUNT(*) AS "cnt6" FROM "customer" AS "c" LEFT JOIN "_u_0" AS "_u_0" - ON "c"."c_customer_sk" = "_u_0"."_u_1" + ON "_u_0"."_u_1" = "c"."c_customer_sk" LEFT JOIN "_u_2" AS "_u_2" - ON "c"."c_customer_sk" = "_u_2"."_u_3" + ON "_u_2"."_u_3" = "c"."c_customer_sk" LEFT JOIN "_u_4" AS "_u_4" - ON "c"."c_customer_sk" = "_u_4"."_u_5" + ON "_u_4"."_u_5" = "c"."c_customer_sk" JOIN "customer_address" AS "ca" ON "c"."c_current_addr_sk" = "ca"."ca_address_sk" AND "ca"."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" = "c"."c_current_cdemo_sk" + ON "c"."c_current_cdemo_sk" = "customer_demographics"."cd_demo_sk" WHERE NOT "_u_0"."_u_1" IS NULL AND ( @@ -1782,7 +1734,7 @@ WITH "customer_2" AS ( "date_dim"."d_date_sk" AS "d_date_sk", "date_dim"."d_year" AS "d_year" FROM "date_dim" AS "date_dim" -), "cte" AS ( +), "year_total" AS ( SELECT "customer"."c_customer_id" AS "customer_id", "customer"."c_first_name" AS "customer_first_name", @@ -1795,7 +1747,7 @@ WITH "customer_2" AS ( JOIN "store_sales" AS "store_sales" ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk" JOIN "date_dim_2" AS "date_dim" - ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" GROUP BY "customer"."c_customer_id", "customer"."c_first_name", @@ -1805,7 +1757,7 @@ WITH "customer_2" AS ( "customer"."c_login", "customer"."c_email_address", "date_dim"."d_year" -), "cte_2" AS ( + UNION ALL SELECT "customer"."c_customer_id" AS "customer_id", "customer"."c_first_name" AS "customer_first_name", @@ -1818,7 +1770,7 @@ WITH "customer_2" AS ( JOIN "web_sales" AS "web_sales" ON "customer"."c_customer_sk" = "web_sales"."ws_bill_customer_sk" JOIN "date_dim_2" AS "date_dim" - ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk" GROUP BY "customer"."c_customer_id", "customer"."c_first_name", @@ -1828,26 +1780,6 @@ WITH "customer_2" AS ( "customer"."c_login", "customer"."c_email_address", "date_dim"."d_year" -), "year_total" AS ( - SELECT - "cte"."customer_id" AS "customer_id", - "cte"."customer_first_name" AS "customer_first_name", - "cte"."customer_last_name" AS "customer_last_name", - "cte"."customer_birth_country" AS "customer_birth_country", - "cte"."dyear" AS "dyear", - "cte"."year_total" AS "year_total", - "cte"."sale_type" AS "sale_type" - FROM "cte" AS "cte" - UNION ALL - SELECT - "cte_2"."customer_id" AS "customer_id", - "cte_2"."customer_first_name" AS "customer_first_name", - "cte_2"."customer_last_name" AS "customer_last_name", - "cte_2"."customer_birth_country" AS "customer_birth_country", - "cte_2"."dyear" AS "dyear", - "cte_2"."year_total" AS "year_total", - "cte_2"."sale_type" AS "sale_type" - FROM "cte_2" AS "cte_2" ) SELECT "t_s_secyear"."customer_id" AS "customer_id", @@ -1865,17 +1797,17 @@ JOIN "year_total" AS "t_w_secyear" AND "t_w_secyear"."dyear" = 2002 AND "t_w_secyear"."sale_type" = 'w' JOIN "year_total" AS "t_s_secyear" - ON "t_s_secyear"."customer_id" = "t_s_firstyear"."customer_id" + 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_w_firstyear"."year_total" > 0 - THEN "t_w_secyear"."year_total" / "t_w_firstyear"."year_total" - ELSE 0.0 - END > CASE WHEN "t_s_firstyear"."year_total" > 0 THEN "t_s_secyear"."year_total" / "t_s_firstyear"."year_total" ELSE 0.0 + END < CASE + WHEN "t_w_firstyear"."year_total" > 0 + THEN "t_w_secyear"."year_total" / "t_w_firstyear"."year_total" + ELSE 0.0 END WHERE "t_s_firstyear"."dyear" = 2001 @@ -1930,12 +1862,12 @@ SELECT SUM("web_sales"."ws_ext_sales_price") * 100 / SUM(SUM("web_sales"."ws_ext_sales_price")) OVER (PARTITION BY "item"."i_class") AS "revenueratio" FROM "web_sales" AS "web_sales" JOIN "date_dim" AS "date_dim" - ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk" AND CAST("date_dim"."d_date" AS DATE) <= CAST('2000-06-10' AS DATE) AND CAST("date_dim"."d_date" AS DATE) >= CAST('2000-05-11' AS DATE) JOIN "item" AS "item" ON "item"."i_category" IN ('Home', 'Men', 'Women') - AND "web_sales"."ws_item_sk" = "item"."i_item_sk" + AND "item"."i_item_sk" = "web_sales"."ws_item_sk" GROUP BY "item"."i_item_id", "item"."i_item_desc", @@ -2005,36 +1937,36 @@ FROM "store_sales" AS "store_sales" CROSS JOIN "customer_demographics" AS "customer_demographics" JOIN "customer_address" AS "customer_address" ON ( - "customer_address"."ca_country" = 'United States' + "customer_address"."ca_address_sk" = "store_sales"."ss_addr_sk" + AND "customer_address"."ca_country" = 'United States' AND "customer_address"."ca_state" IN ('AZ', 'NE', 'IA') - AND "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk" AND "store_sales"."ss_net_profit" <= 200 AND "store_sales"."ss_net_profit" >= 100 ) OR ( - "customer_address"."ca_country" = 'United States' + "customer_address"."ca_address_sk" = "store_sales"."ss_addr_sk" + AND "customer_address"."ca_country" = 'United States' AND "customer_address"."ca_state" IN ('GA', 'TX', 'NJ') - AND "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk" AND "store_sales"."ss_net_profit" <= 250 AND "store_sales"."ss_net_profit" >= 50 ) OR ( - "customer_address"."ca_country" = 'United States' + "customer_address"."ca_address_sk" = "store_sales"."ss_addr_sk" + AND "customer_address"."ca_country" = 'United States' AND "customer_address"."ca_state" IN ('MS', 'CA', 'NV') - AND "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk" AND "store_sales"."ss_net_profit" <= 300 AND "store_sales"."ss_net_profit" >= 150 ) JOIN "date_dim" AS "date_dim" - ON "date_dim"."d_year" = 2001 - AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" + AND "date_dim"."d_year" = 2001 JOIN "household_demographics" AS "household_demographics" ON ( "customer_demographics"."cd_demo_sk" = "store_sales"."ss_cdemo_sk" AND "customer_demographics"."cd_education_status" = 'Advanced Degree' AND "customer_demographics"."cd_marital_status" = 'U' + AND "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk" AND "household_demographics"."hd_dep_count" = 3 - AND "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk" AND "store_sales"."ss_sales_price" <= 150.00 AND "store_sales"."ss_sales_price" >= 100.00 ) @@ -2042,8 +1974,8 @@ JOIN "household_demographics" AS "household_demographics" "customer_demographics"."cd_demo_sk" = "store_sales"."ss_cdemo_sk" AND "customer_demographics"."cd_education_status" = 'Primary' AND "customer_demographics"."cd_marital_status" = 'M' + AND "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk" AND "household_demographics"."hd_dep_count" = 1 - AND "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk" AND "store_sales"."ss_sales_price" <= 100.00 AND "store_sales"."ss_sales_price" >= 50.00 ) @@ -2051,8 +1983,8 @@ JOIN "household_demographics" AS "household_demographics" "customer_demographics"."cd_demo_sk" = "store_sales"."ss_cdemo_sk" AND "customer_demographics"."cd_education_status" = 'Secondary' AND "customer_demographics"."cd_marital_status" = 'D' + AND "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk" AND "household_demographics"."hd_dep_count" = 1 - AND "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk" AND "store_sales"."ss_sales_price" <= 200.00 AND "store_sales"."ss_sales_price" >= 150.00 ) @@ -2200,7 +2132,19 @@ WITH "item_2" AS ( "item"."i_class_id" AS "i_class_id", "item"."i_category_id" AS "i_category_id" FROM "item" AS "item" -), "cte_4" AS ( +), "_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" + JOIN "date_dim" AS "d1" + ON "d1"."d_date_sk" = "store_sales"."ss_sold_date_sk" + AND "d1"."d_year" <= 2001 + AND "d1"."d_year" >= 1999 + JOIN "item" AS "iss" + ON "iss"."i_item_sk" = "store_sales"."ss_item_sk" + INTERSECT SELECT "ics"."i_brand_id" AS "i_brand_id", "ics"."i_class_id" AS "i_class_id", @@ -2219,29 +2163,11 @@ WITH "item_2" AS ( "iws"."i_category_id" AS "i_category_id" FROM "web_sales" AS "web_sales" JOIN "date_dim" AS "d3" - ON "d3"."d_year" <= 2001 + ON "d3"."d_date_sk" = "web_sales"."ws_sold_date_sk" + AND "d3"."d_year" <= 2001 AND "d3"."d_year" >= 1999 - AND "web_sales"."ws_sold_date_sk" = "d3"."d_date_sk" JOIN "item" AS "iws" - ON "web_sales"."ws_item_sk" = "iws"."i_item_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" - JOIN "date_dim" AS "d1" - ON "d1"."d_year" <= 2001 - AND "d1"."d_year" >= 1999 - AND "store_sales"."ss_sold_date_sk" = "d1"."d_date_sk" - JOIN "item" AS "iss" - ON "store_sales"."ss_item_sk" = "iss"."i_item_sk" - INTERSECT - SELECT - "cte_4"."i_brand_id" AS "i_brand_id", - "cte_4"."i_class_id" AS "i_class_id", - "cte_4"."i_category_id" AS "i_category_id" - FROM "cte_4" AS "cte_4" + ON "iws"."i_item_sk" = "web_sales"."ws_item_sk" ), "date_dim_2" AS ( SELECT "date_dim"."d_date_sk" AS "d_date_sk", @@ -2249,7 +2175,14 @@ WITH "item_2" AS ( FROM "date_dim" AS "date_dim" WHERE "date_dim"."d_year" <= 2001 AND "date_dim"."d_year" >= 1999 -), "cte_8" AS ( +), "x" AS ( + SELECT + "store_sales"."ss_quantity" AS "quantity", + "store_sales"."ss_list_price" AS "list_price" + FROM "store_sales" AS "store_sales" + JOIN "date_dim_2" AS "date_dim" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" + UNION ALL SELECT "catalog_sales"."cs_quantity" AS "quantity", "catalog_sales"."cs_list_price" AS "list_price" @@ -2262,19 +2195,7 @@ WITH "item_2" AS ( "web_sales"."ws_list_price" AS "list_price" FROM "web_sales" AS "web_sales" JOIN "date_dim_2" AS "date_dim" - ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk" -), "x" AS ( - SELECT - "store_sales"."ss_quantity" AS "quantity", - "store_sales"."ss_list_price" AS "list_price" - FROM "store_sales" AS "store_sales" - JOIN "date_dim_2" AS "date_dim" - ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" - UNION ALL - SELECT - "cte_8"."quantity" AS "quantity", - "cte_8"."list_price" AS "list_price" - FROM "cte_8" AS "cte_8" + ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk" ), "avg_sales" AS ( SELECT AVG("x"."quantity" * "x"."list_price") AS "average_sales" @@ -2288,9 +2209,9 @@ WITH "item_2" AS ( "item"."i_item_sk" AS "ss_item_sk" FROM "item_2" AS "item" JOIN "_q_0" AS "_q_0" - ON "item"."i_brand_id" = "_q_0"."brand_id" - AND "item"."i_category_id" = "_q_0"."category_id" - AND "item"."i_class_id" = "_q_0"."class_id" + ON "_q_0"."brand_id" = "item"."i_brand_id" + AND "_q_0"."category_id" = "item"."i_category_id" + AND "_q_0"."class_id" = "item"."i_class_id" GROUP BY "item"."i_item_sk" ), "date_dim_3" AS ( @@ -2301,7 +2222,7 @@ WITH "item_2" AS ( FROM "date_dim" AS "date_dim" WHERE "date_dim"."d_moy" = 11 AND "date_dim"."d_year" = 2001 -), "cte_9" AS ( +), "y" AS ( SELECT 'store' AS "channel", "item"."i_brand_id" AS "i_brand_id", @@ -2312,11 +2233,11 @@ WITH "item_2" AS ( FROM "store_sales" AS "store_sales" CROSS JOIN "_u_1" AS "_u_1" LEFT JOIN "_u_0" AS "_u_0" - ON "store_sales"."ss_item_sk" = "_u_0"."ss_item_sk" + ON "_u_0"."ss_item_sk" = "store_sales"."ss_item_sk" JOIN "date_dim_3" AS "date_dim" - ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" JOIN "item_2" AS "item" - ON "store_sales"."ss_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "store_sales"."ss_item_sk" WHERE NOT "_u_0"."ss_item_sk" IS NULL GROUP BY @@ -2324,8 +2245,8 @@ WITH "item_2" AS ( "item"."i_class_id", "item"."i_category_id" HAVING - SUM("store_sales"."ss_quantity" * "store_sales"."ss_list_price") > MAX("_u_1"."average_sales") -), "cte_10" AS ( + MAX("_u_1"."average_sales") < SUM("store_sales"."ss_quantity" * "store_sales"."ss_list_price") + UNION ALL SELECT 'catalog' AS "channel", "item"."i_brand_id" AS "i_brand_id", @@ -2336,7 +2257,7 @@ WITH "item_2" AS ( FROM "catalog_sales" AS "catalog_sales" CROSS JOIN "_u_1" AS "_u_3" LEFT JOIN "_u_0" AS "_u_2" - ON "catalog_sales"."cs_item_sk" = "_u_2"."ss_item_sk" + ON "_u_2"."ss_item_sk" = "catalog_sales"."cs_item_sk" JOIN "date_dim_3" AS "date_dim" ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk" JOIN "item_2" AS "item" @@ -2348,8 +2269,8 @@ WITH "item_2" AS ( "item"."i_class_id", "item"."i_category_id" HAVING - SUM("catalog_sales"."cs_quantity" * "catalog_sales"."cs_list_price") > MAX("_u_3"."average_sales") -), "cte_11" AS ( + MAX("_u_3"."average_sales") < SUM("catalog_sales"."cs_quantity" * "catalog_sales"."cs_list_price") + UNION ALL SELECT 'web' AS "channel", "item"."i_brand_id" AS "i_brand_id", @@ -2360,11 +2281,11 @@ WITH "item_2" AS ( FROM "web_sales" AS "web_sales" CROSS JOIN "_u_1" AS "_u_5" LEFT JOIN "_u_0" AS "_u_4" - ON "web_sales"."ws_item_sk" = "_u_4"."ss_item_sk" + ON "_u_4"."ss_item_sk" = "web_sales"."ws_item_sk" JOIN "date_dim_3" AS "date_dim" - ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk" JOIN "item_2" AS "item" - ON "web_sales"."ws_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "web_sales"."ws_item_sk" WHERE NOT "_u_4"."ss_item_sk" IS NULL GROUP BY @@ -2372,43 +2293,7 @@ WITH "item_2" AS ( "item"."i_class_id", "item"."i_category_id" HAVING - SUM("web_sales"."ws_quantity" * "web_sales"."ws_list_price") > MAX("_u_5"."average_sales") -), "cte_12" AS ( - SELECT - "cte_10"."channel" AS "channel", - "cte_10"."i_brand_id" AS "i_brand_id", - "cte_10"."i_class_id" AS "i_class_id", - "cte_10"."i_category_id" AS "i_category_id", - "cte_10"."sales" AS "sales", - "cte_10"."number_sales" AS "number_sales" - FROM "cte_10" AS "cte_10" - UNION ALL - SELECT - "cte_11"."channel" AS "channel", - "cte_11"."i_brand_id" AS "i_brand_id", - "cte_11"."i_class_id" AS "i_class_id", - "cte_11"."i_category_id" AS "i_category_id", - "cte_11"."sales" AS "sales", - "cte_11"."number_sales" AS "number_sales" - FROM "cte_11" AS "cte_11" -), "y" AS ( - SELECT - "cte_9"."channel" AS "channel", - "cte_9"."i_brand_id" AS "i_brand_id", - "cte_9"."i_class_id" AS "i_class_id", - "cte_9"."i_category_id" AS "i_category_id", - "cte_9"."sales" AS "sales", - "cte_9"."number_sales" AS "number_sales" - FROM "cte_9" AS "cte_9" - UNION ALL - SELECT - "cte_12"."channel" AS "channel", - "cte_12"."i_brand_id" AS "i_brand_id", - "cte_12"."i_class_id" AS "i_class_id", - "cte_12"."i_category_id" AS "i_category_id", - "cte_12"."sales" AS "sales", - "cte_12"."number_sales" AS "number_sales" - FROM "cte_12" AS "cte_12" + MAX("_u_5"."average_sales") < SUM("web_sales"."ws_quantity" * "web_sales"."ws_list_price") ) SELECT "y"."channel" AS "channel", @@ -2530,21 +2415,21 @@ SELECT SUM("cs1"."cs_net_profit") AS "total net profit" FROM "catalog_sales" AS "cs1" LEFT JOIN "_u_0" AS "_u_0" - ON "cs1"."cs_order_number" = "_u_0"."_u_1" + ON "_u_0"."_u_1" = "cs1"."cs_order_number" LEFT JOIN "_u_3" AS "_u_3" - ON "cs1"."cs_order_number" = "_u_3"."_u_4" + ON "_u_3"."_u_4" = "cs1"."cs_order_number" JOIN "call_center" AS "call_center" - ON "call_center"."cc_county" IN ('Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County') - AND "cs1"."cs_call_center_sk" = "call_center"."cc_call_center_sk" + ON "call_center"."cc_call_center_sk" = "cs1"."cs_call_center_sk" + AND "call_center"."cc_county" IN ('Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County') JOIN "customer_address" AS "customer_address" ON "cs1"."cs_ship_addr_sk" = "customer_address"."ca_address_sk" AND "customer_address"."ca_state" = 'IA' 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("date_dim"."d_date" AS DATE) <= ( + AND ( CAST('2002-3-01' AS DATE) + INTERVAL '60' day - ) + ) >= CAST("date_dim"."d_date" AS DATE) WHERE "_u_3"."_u_4" IS NULL AND NOT "_u_0"."_u_1" IS NULL @@ -2636,15 +2521,15 @@ JOIN "item" AS "item" JOIN "store" AS "store" ON "store"."s_store_sk" = "store_sales"."ss_store_sk" JOIN "store_returns" AS "store_returns" - ON "store_sales"."ss_customer_sk" = "store_returns"."sr_customer_sk" - AND "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk" - AND "store_sales"."ss_ticket_number" = "store_returns"."sr_ticket_number" + ON "store_returns"."sr_customer_sk" = "store_sales"."ss_customer_sk" + AND "store_returns"."sr_item_sk" = "store_sales"."ss_item_sk" + AND "store_returns"."sr_ticket_number" = "store_sales"."ss_ticket_number" JOIN "catalog_sales" AS "catalog_sales" - ON "store_returns"."sr_customer_sk" = "catalog_sales"."cs_bill_customer_sk" - AND "store_returns"."sr_item_sk" = "catalog_sales"."cs_item_sk" + ON "catalog_sales"."cs_bill_customer_sk" = "store_returns"."sr_customer_sk" + AND "catalog_sales"."cs_item_sk" = "store_returns"."sr_item_sk" JOIN "date_dim" AS "d2" - ON "d2"."d_quarter_name" IN ('1999Q1', '1999Q2', '1999Q3') - AND "store_returns"."sr_returned_date_sk" = "d2"."d_date_sk" + ON "d2"."d_date_sk" = "store_returns"."sr_returned_date_sk" + AND "d2"."d_quarter_name" IN ('1999Q1', '1999Q2', '1999Q3') JOIN "date_dim" AS "d3" ON "catalog_sales"."cs_sold_date_sk" = "d3"."d_date_sk" AND "d3"."d_quarter_name" IN ('1999Q1', '1999Q2', '1999Q3') @@ -2724,7 +2609,7 @@ JOIN "date_dim" AS "date_dim" JOIN "item" AS "item" ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk" JOIN "customer_demographics" AS "cd2" - ON "customer"."c_current_cdemo_sk" = "cd2"."cd_demo_sk" + ON "cd2"."cd_demo_sk" = "customer"."c_current_cdemo_sk" JOIN "customer_address" AS "customer_address" ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk" AND "customer_address"."ca_state" IN ('KS', 'IA', 'AL', 'UT', 'VA', 'NC', 'TX') @@ -2785,14 +2670,14 @@ 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 "item" AS "item" - ON "item"."i_manager_id" = 38 AND "store_sales"."ss_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "store_sales"."ss_item_sk" AND "item"."i_manager_id" = 38 JOIN "store" AS "store" - ON "store_sales"."ss_store_sk" = "store"."s_store_sk" + 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 "store_sales"."ss_customer_sk" = "customer"."c_customer_sk" + AND "customer"."c_customer_sk" = "store_sales"."ss_customer_sk" WHERE "date_dim"."d_moy" = 12 AND "date_dim"."d_year" = 1998 GROUP BY @@ -2930,13 +2815,13 @@ WITH "x" AS ( ) AS "inv_after" FROM "inventory" AS "inventory" JOIN "date_dim" AS "date_dim" - ON "inventory"."inv_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "inventory"."inv_date_sk" AND CAST("date_dim"."d_date" AS DATE) <= CAST('2000-06-12' AS DATE) AND CAST("date_dim"."d_date" AS DATE) >= CAST('2000-04-13' AS DATE) JOIN "item" AS "item" - ON "item"."i_current_price" <= 1.49 + ON "inventory"."inv_item_sk" = "item"."i_item_sk" + AND "item"."i_current_price" <= 1.49 AND "item"."i_current_price" >= 0.99 - AND "item"."i_item_sk" = "inventory"."inv_item_sk" JOIN "warehouse" AS "warehouse" ON "inventory"."inv_warehouse_sk" = "warehouse"."w_warehouse_sk" GROUP BY @@ -2988,9 +2873,9 @@ SELECT AVG("inventory"."inv_quantity_on_hand") AS "qoh" FROM "inventory" AS "inventory" JOIN "date_dim" AS "date_dim" - ON "date_dim"."d_month_seq" <= 1216 + ON "date_dim"."d_date_sk" = "inventory"."inv_date_sk" + AND "date_dim"."d_month_seq" <= 1216 AND "date_dim"."d_month_seq" >= 1205 - AND "inventory"."inv_date_sk" = "date_dim"."d_date_sk" JOIN "item" AS "item" ON "inventory"."inv_item_sk" = "item"."i_item_sk" JOIN "warehouse" AS "warehouse" @@ -3076,10 +2961,10 @@ WITH "frequent_ss_items" AS ( "item"."i_item_sk" AS "item_sk" FROM "store_sales" AS "store_sales" JOIN "date_dim" AS "date_dim" - ON "date_dim"."d_year" IN (1998, 1999, 2000, 2001) - AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" + AND "date_dim"."d_year" IN (1998, 1999, 2000, 2001) JOIN "item" AS "item" - ON "store_sales"."ss_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "store_sales"."ss_item_sk" GROUP BY SUBSTR("item"."i_item_desc", 1, 30), "item"."i_item_sk", @@ -3095,10 +2980,10 @@ WITH "frequent_ss_items" AS ( SUM("store_sales"."ss_quantity" * "store_sales"."ss_sales_price") AS "csales" FROM "store_sales" AS "store_sales" JOIN "customer_2" AS "customer" - ON "store_sales"."ss_customer_sk" = "customer"."c_customer_sk" + ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk" JOIN "date_dim" AS "date_dim" - ON "date_dim"."d_year" IN (1998, 1999, 2000, 2001) - AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" + AND "date_dim"."d_year" IN (1998, 1999, 2000, 2001) GROUP BY "customer"."c_customer_sk" ), "max_store_sales" AS ( @@ -3111,11 +2996,11 @@ WITH "frequent_ss_items" AS ( 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" + ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk" 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") + 0.95 * MAX("max_store_sales"."tpcds_cmax") < SUM("store_sales"."ss_quantity" * "store_sales"."ss_sales_price") ), "_u_1" AS ( SELECT "frequent_ss_items"."item_sk" AS "item_sk" @@ -3141,9 +3026,9 @@ WITH "frequent_ss_items" AS ( "catalog_sales"."cs_quantity" * "catalog_sales"."cs_list_price" AS "sales" FROM "catalog_sales" AS "catalog_sales" LEFT JOIN "_u_1" AS "_u_1" - ON "catalog_sales"."cs_item_sk" = "_u_1"."item_sk" + ON "_u_1"."item_sk" = "catalog_sales"."cs_item_sk" LEFT JOIN "_u_2" AS "_u_2" - ON "catalog_sales"."cs_bill_customer_sk" = "_u_2"."c_customer_sk" + ON "_u_2"."c_customer_sk" = "catalog_sales"."cs_bill_customer_sk" JOIN "date_dim_4" AS "date_dim" ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk" WHERE @@ -3153,11 +3038,11 @@ WITH "frequent_ss_items" AS ( "web_sales"."ws_quantity" * "web_sales"."ws_list_price" AS "sales" FROM "web_sales" AS "web_sales" LEFT JOIN "_u_1" AS "_u_3" - ON "web_sales"."ws_item_sk" = "_u_3"."item_sk" + ON "_u_3"."item_sk" = "web_sales"."ws_item_sk" LEFT JOIN "_u_2" AS "_u_4" - ON "web_sales"."ws_bill_customer_sk" = "_u_4"."c_customer_sk" + ON "_u_4"."c_customer_sk" = "web_sales"."ws_bill_customer_sk" JOIN "date_dim_4" AS "date_dim" - ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk" WHERE NOT "_u_3"."item_sk" IS NULL AND NOT "_u_4"."c_customer_sk" IS NULL ) @@ -3225,17 +3110,17 @@ WITH "ssales" AS ( SUM("store_sales"."ss_net_profit") AS "netpaid" FROM "store_sales" AS "store_sales" JOIN "item" AS "item" - ON "store_sales"."ss_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "store_sales"."ss_item_sk" JOIN "store" AS "store" - ON "store"."s_market_id" = 6 AND "store_sales"."ss_store_sk" = "store"."s_store_sk" + ON "store"."s_market_id" = 6 AND "store"."s_store_sk" = "store_sales"."ss_store_sk" JOIN "store_returns" AS "store_returns" - ON "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk" - AND "store_sales"."ss_ticket_number" = "store_returns"."sr_ticket_number" + 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 "store"."s_zip" = "customer_address"."ca_zip" + ON "customer_address"."ca_zip" = "store"."s_zip" JOIN "customer" AS "customer" ON "customer"."c_birth_country" = UPPER("customer_address"."ca_country") - AND "store_sales"."ss_customer_sk" = "customer"."c_customer_sk" + AND "customer"."c_customer_sk" = "store_sales"."ss_customer_sk" GROUP BY "customer"."c_last_name", "customer"."c_first_name", @@ -3266,7 +3151,7 @@ GROUP BY "ssales"."c_first_name", "ssales"."s_store_name" HAVING - SUM("ssales"."netpaid") > MAX("_u_0"."_col_0"); + MAX("_u_0"."_col_0") < SUM("ssales"."netpaid"); -------------------------------------- -- TPC-DS 25 @@ -3329,17 +3214,17 @@ JOIN "item" AS "item" JOIN "store" AS "store" ON "store"."s_store_sk" = "store_sales"."ss_store_sk" JOIN "store_returns" AS "store_returns" - ON "store_sales"."ss_customer_sk" = "store_returns"."sr_customer_sk" - AND "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk" - AND "store_sales"."ss_ticket_number" = "store_returns"."sr_ticket_number" + ON "store_returns"."sr_customer_sk" = "store_sales"."ss_customer_sk" + AND "store_returns"."sr_item_sk" = "store_sales"."ss_item_sk" + AND "store_returns"."sr_ticket_number" = "store_sales"."ss_ticket_number" JOIN "catalog_sales" AS "catalog_sales" - ON "store_returns"."sr_customer_sk" = "catalog_sales"."cs_bill_customer_sk" - AND "store_returns"."sr_item_sk" = "catalog_sales"."cs_item_sk" + ON "catalog_sales"."cs_bill_customer_sk" = "store_returns"."sr_customer_sk" + AND "catalog_sales"."cs_item_sk" = "store_returns"."sr_item_sk" JOIN "date_dim" AS "d2" - ON "d2"."d_moy" <= 10 + ON "d2"."d_date_sk" = "store_returns"."sr_returned_date_sk" + AND "d2"."d_moy" <= 10 AND "d2"."d_moy" >= 4 AND "d2"."d_year" = 2001 - AND "store_returns"."sr_returned_date_sk" = "d2"."d_date_sk" JOIN "date_dim" AS "d3" ON "catalog_sales"."cs_sold_date_sk" = "d3"."d_date_sk" AND "d3"."d_moy" <= 10 @@ -3450,18 +3335,18 @@ SELECT AVG("store_sales"."ss_sales_price") AS "agg4" FROM "store_sales" AS "store_sales" JOIN "customer_demographics" AS "customer_demographics" - ON "customer_demographics"."cd_education_status" = 'College' + ON "customer_demographics"."cd_demo_sk" = "store_sales"."ss_cdemo_sk" + AND "customer_demographics"."cd_education_status" = 'College' AND "customer_demographics"."cd_gender" = 'M' AND "customer_demographics"."cd_marital_status" = 'D' - AND "store_sales"."ss_cdemo_sk" = "customer_demographics"."cd_demo_sk" JOIN "date_dim" AS "date_dim" - ON "date_dim"."d_year" = 2000 - AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" + AND "date_dim"."d_year" = 2000 JOIN "item" AS "item" - ON "store_sales"."ss_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "store_sales"."ss_item_sk" JOIN "store" AS "store" ON "store"."s_state" IN ('TN', 'TN', 'TN', 'TN', 'TN', 'TN') - AND "store_sales"."ss_store_sk" = "store"."s_store_sk" + AND "store"."s_store_sk" = "store_sales"."ss_store_sk" GROUP BY ROLLUP ( "item"."i_item_id", @@ -3697,17 +3582,17 @@ JOIN "item" AS "item" JOIN "store" AS "store" ON "store"."s_store_sk" = "store_sales"."ss_store_sk" JOIN "store_returns" AS "store_returns" - ON "store_sales"."ss_customer_sk" = "store_returns"."sr_customer_sk" - AND "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk" - AND "store_sales"."ss_ticket_number" = "store_returns"."sr_ticket_number" + ON "store_returns"."sr_customer_sk" = "store_sales"."ss_customer_sk" + AND "store_returns"."sr_item_sk" = "store_sales"."ss_item_sk" + AND "store_returns"."sr_ticket_number" = "store_sales"."ss_ticket_number" JOIN "catalog_sales" AS "catalog_sales" - ON "store_returns"."sr_customer_sk" = "catalog_sales"."cs_bill_customer_sk" - AND "store_returns"."sr_item_sk" = "catalog_sales"."cs_item_sk" + ON "catalog_sales"."cs_bill_customer_sk" = "store_returns"."sr_customer_sk" + AND "catalog_sales"."cs_item_sk" = "store_returns"."sr_item_sk" JOIN "date_dim" AS "d2" - ON "d2"."d_moy" <= 7 + ON "d2"."d_date_sk" = "store_returns"."sr_returned_date_sk" + AND "d2"."d_moy" <= 7 AND "d2"."d_moy" >= 4 AND "d2"."d_year" = 1998 - AND "store_returns"."sr_returned_date_sk" = "d2"."d_date_sk" JOIN "date_dim" AS "d3" ON "catalog_sales"."cs_sold_date_sk" = "d3"."d_date_sk" AND "d3"."d_year" IN (1998, 1999, 2000) @@ -3781,10 +3666,10 @@ WITH "customer_total_return" AS ( SUM("web_returns"."wr_return_amt") AS "ctr_total_return" FROM "web_returns" AS "web_returns" JOIN "customer_address" AS "customer_address" - ON "web_returns"."wr_returning_addr_sk" = "customer_address"."ca_address_sk" + ON "customer_address"."ca_address_sk" = "web_returns"."wr_returning_addr_sk" JOIN "date_dim" AS "date_dim" - ON "date_dim"."d_year" = 2000 - AND "web_returns"."wr_returned_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "web_returns"."wr_returned_date_sk" + AND "date_dim"."d_year" = 2000 GROUP BY "web_returns"."wr_returning_customer_sk", "customer_address"."ca_state" @@ -3812,14 +3697,14 @@ SELECT "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" + ON "_u_0"."_u_1" = "ctr1"."ctr_state" 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" + ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk" AND "customer_address"."ca_state" = 'IN' WHERE - "ctr1"."ctr_total_return" > "_u_0"."_col_0" + "_u_0"."_col_0" < "ctr1"."ctr_total_return" ORDER BY "c_customer_id", "c_salutation", @@ -3930,9 +3815,9 @@ WITH "customer_address_2" AS ( SUM("store_sales"."ss_ext_sales_price") AS "store_sales" FROM "store_sales" AS "store_sales" JOIN "customer_address_2" AS "customer_address" - ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk" + ON "customer_address"."ca_address_sk" = "store_sales"."ss_addr_sk" JOIN "date_dim_2" AS "date_dim" - ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" GROUP BY "customer_address"."ca_county", "date_dim"."d_qoy", @@ -3945,9 +3830,9 @@ WITH "customer_address_2" AS ( SUM("web_sales"."ws_ext_sales_price") AS "web_sales" FROM "web_sales" AS "web_sales" JOIN "customer_address_2" AS "customer_address" - ON "web_sales"."ws_bill_addr_sk" = "customer_address"."ca_address_sk" + ON "customer_address"."ca_address_sk" = "web_sales"."ws_bill_addr_sk" JOIN "date_dim_2" AS "date_dim" - ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk" GROUP BY "customer_address"."ca_county", "date_dim"."d_qoy", @@ -3970,13 +3855,13 @@ JOIN "ws" AS "ws2" AND "ws2"."d_qoy" = 2 AND "ws2"."d_year" = 2001 AND CASE - WHEN "ws1"."web_sales" > 0 - THEN "ws2"."web_sales" / "ws1"."web_sales" - ELSE NULL - END > CASE WHEN "ss1"."store_sales" > 0 THEN "ss2"."store_sales" / "ss1"."store_sales" ELSE NULL + END < CASE + WHEN "ws1"."web_sales" > 0 + THEN "ws2"."web_sales" / "ws1"."web_sales" + ELSE NULL END JOIN "ws" AS "ws3" ON "ws1"."ca_county" = "ws3"."ca_county" AND "ws3"."d_qoy" = 3 AND "ws3"."d_year" = 2001 @@ -3985,13 +3870,13 @@ JOIN "ss" AS "ss3" AND "ss3"."d_qoy" = 3 AND "ss3"."d_year" = 2001 AND CASE - WHEN "ws2"."web_sales" > 0 - THEN "ws3"."web_sales" / "ws2"."web_sales" - ELSE NULL - END > CASE WHEN "ss2"."store_sales" > 0 THEN "ss3"."store_sales" / "ss2"."store_sales" ELSE NULL + END < CASE + WHEN "ws2"."web_sales" > 0 + THEN "ws3"."web_sales" / "ws2"."web_sales" + ELSE NULL END WHERE "ss1"."d_qoy" = 1 AND "ss1"."d_year" = 2001 @@ -4041,7 +3926,7 @@ WITH "catalog_sales_2" AS ( "catalog_sales"."cs_item_sk" AS "_u_1" FROM "catalog_sales_2" AS "catalog_sales" JOIN "date_dim_2" AS "date_dim" - ON "date_dim"."d_date_sk" = "catalog_sales"."cs_sold_date_sk" + ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk" GROUP BY "catalog_sales"."cs_item_sk" ) @@ -4049,13 +3934,13 @@ SELECT SUM("catalog_sales"."cs_ext_discount_amt") AS "excess discount amount" FROM "catalog_sales_2" AS "catalog_sales" JOIN "date_dim_2" AS "date_dim" - ON "date_dim"."d_date_sk" = "catalog_sales"."cs_sold_date_sk" + ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk" JOIN "item" AS "item" - ON "item"."i_item_sk" = "catalog_sales"."cs_item_sk" AND "item"."i_manufact_id" = 610 + ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk" AND "item"."i_manufact_id" = 610 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" + "_u_0"."_col_0" < "catalog_sales"."cs_ext_discount_amt" LIMIT 100; -------------------------------------- @@ -4159,13 +4044,13 @@ WITH "customer_address_2" AS ( SUM("store_sales"."ss_ext_sales_price") AS "total_sales" FROM "store_sales" AS "store_sales" JOIN "customer_address_2" AS "customer_address" - ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk" + ON "customer_address"."ca_address_sk" = "store_sales"."ss_addr_sk" JOIN "date_dim_2" AS "date_dim" - ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" JOIN "item_2" AS "item" - ON "store_sales"."ss_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "store_sales"."ss_item_sk" LEFT JOIN "_u_0" AS "_u_0" - ON "item"."i_manufact_id" = "_u_0"."i_manufact_id" + ON "_u_0"."i_manufact_id" = "item"."i_manufact_id" WHERE NOT "_u_0"."i_manufact_id" IS NULL GROUP BY @@ -4182,7 +4067,7 @@ WITH "customer_address_2" AS ( 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" + ON "_u_1"."i_manufact_id" = "item"."i_manufact_id" WHERE NOT "_u_1"."i_manufact_id" IS NULL GROUP BY @@ -4193,18 +4078,23 @@ WITH "customer_address_2" AS ( SUM("web_sales"."ws_ext_sales_price") AS "total_sales" FROM "web_sales" AS "web_sales" JOIN "customer_address_2" AS "customer_address" - ON "web_sales"."ws_bill_addr_sk" = "customer_address"."ca_address_sk" + ON "customer_address"."ca_address_sk" = "web_sales"."ws_bill_addr_sk" JOIN "date_dim_2" AS "date_dim" - ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk" JOIN "item_2" AS "item" - ON "web_sales"."ws_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "web_sales"."ws_item_sk" LEFT JOIN "_u_0" AS "_u_2" - ON "item"."i_manufact_id" = "_u_2"."i_manufact_id" + ON "_u_2"."i_manufact_id" = "item"."i_manufact_id" WHERE NOT "_u_2"."i_manufact_id" IS NULL GROUP BY "item"."i_manufact_id" -), "cte_4" AS ( +), "tmp1" AS ( + SELECT + "ss"."i_manufact_id" AS "i_manufact_id", + "ss"."total_sales" AS "total_sales" + FROM "ss" + UNION ALL SELECT "cs"."i_manufact_id" AS "i_manufact_id", "cs"."total_sales" AS "total_sales" @@ -4214,16 +4104,6 @@ WITH "customer_address_2" AS ( "ws"."i_manufact_id" AS "i_manufact_id", "ws"."total_sales" AS "total_sales" FROM "ws" -), "tmp1" AS ( - SELECT - "ss"."i_manufact_id" AS "i_manufact_id", - "ss"."total_sales" AS "total_sales" - FROM "ss" - UNION ALL - SELECT - "cte_4"."i_manufact_id" AS "i_manufact_id", - "cte_4"."total_sales" AS "total_sales" - FROM "cte_4" AS "cte_4" ) SELECT "tmp1"."i_manufact_id" AS "i_manufact_id", @@ -4290,8 +4170,8 @@ WITH "dn" AS ( COUNT(*) AS "cnt" FROM "store_sales" AS "store_sales" JOIN "date_dim" AS "date_dim" - ON "date_dim"."d_year" IN (1999, 2000, 2001) - AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" + AND "date_dim"."d_year" IN (1999, 2000, 2001) AND ( ( "date_dim"."d_dom" <= 28 AND "date_dim"."d_dom" >= 25 @@ -4305,8 +4185,8 @@ WITH "dn" AS ( "household_demographics"."hd_buy_potential" = '>10000' OR "household_demographics"."hd_buy_potential" = 'unknown' ) + AND "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk" AND "household_demographics"."hd_vehicle_count" > 0 - AND "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk" AND CASE WHEN "household_demographics"."hd_vehicle_count" > 0 THEN "household_demographics"."hd_dep_count" / "household_demographics"."hd_vehicle_count" @@ -4314,7 +4194,7 @@ WITH "dn" AS ( END > 1.2 JOIN "store" AS "store" ON "store"."s_county" IN ('Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County') - AND "store_sales"."ss_store_sk" = "store"."s_store_sk" + AND "store"."s_store_sk" = "store_sales"."ss_store_sk" GROUP BY "store_sales"."ss_ticket_number", "store_sales"."ss_customer_sk" @@ -4328,7 +4208,7 @@ SELECT "dn"."cnt" AS "cnt" FROM "dn" AS "dn" JOIN "customer" AS "customer" - ON "dn"."ss_customer_sk" = "customer"."c_customer_sk" + ON "customer"."c_customer_sk" = "dn"."ss_customer_sk" WHERE "dn"."cnt" <= 20 AND "dn"."cnt" >= 15 ORDER BY @@ -4435,7 +4315,7 @@ WITH "date_dim_2" AS ( "store_sales"."ss_customer_sk" AS "_u_1" FROM "store_sales" AS "store_sales" JOIN "date_dim_2" AS "date_dim" - ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" GROUP BY "store_sales"."ss_customer_sk" ), "_u_2" AS ( @@ -4443,7 +4323,7 @@ WITH "date_dim_2" AS ( "web_sales"."ws_bill_customer_sk" AS "_u_3" FROM "web_sales" AS "web_sales" JOIN "date_dim_2" AS "date_dim" - ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk" GROUP BY "web_sales"."ws_bill_customer_sk" ), "_u_4" AS ( @@ -4476,15 +4356,15 @@ SELECT MAX("customer_demographics"."cd_dep_college_count") AS "_col_17" FROM "customer" AS "c" LEFT JOIN "_u_0" AS "_u_0" - ON "c"."c_customer_sk" = "_u_0"."_u_1" + ON "_u_0"."_u_1" = "c"."c_customer_sk" LEFT JOIN "_u_2" AS "_u_2" - ON "c"."c_customer_sk" = "_u_2"."_u_3" + ON "_u_2"."_u_3" = "c"."c_customer_sk" LEFT JOIN "_u_4" AS "_u_4" - ON "c"."c_customer_sk" = "_u_4"."_u_5" + ON "_u_4"."_u_5" = "c"."c_customer_sk" JOIN "customer_address" AS "ca" ON "c"."c_current_addr_sk" = "ca"."ca_address_sk" JOIN "customer_demographics" AS "customer_demographics" - ON "customer_demographics"."cd_demo_sk" = "c"."c_current_cdemo_sk" + ON "c"."c_current_cdemo_sk" = "customer_demographics"."cd_demo_sk" WHERE NOT "_u_0"."_u_1" IS NULL AND ( @@ -4663,17 +4543,17 @@ WITH "customer_2" AS ( FROM "date_dim" AS "date_dim" WHERE "date_dim"."d_month_seq" <= 1199 AND "date_dim"."d_month_seq" >= 1188 -), "cte" AS ( +), "hot_cust" AS ( SELECT DISTINCT "customer"."c_last_name" AS "c_last_name", "customer"."c_first_name" AS "c_first_name", "date_dim"."d_date" AS "d_date" FROM "store_sales" AS "store_sales" JOIN "customer_2" AS "customer" - ON "store_sales"."ss_customer_sk" = "customer"."c_customer_sk" + ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk" JOIN "date_dim_2" AS "date_dim" - ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" -), "cte_2" AS ( + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" + INTERSECT SELECT DISTINCT "customer"."c_last_name" AS "c_last_name", "customer"."c_first_name" AS "c_first_name", @@ -4683,40 +4563,16 @@ WITH "customer_2" AS ( ON "catalog_sales"."cs_bill_customer_sk" = "customer"."c_customer_sk" JOIN "date_dim_2" AS "date_dim" ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk" -), "cte_3" AS ( + INTERSECT SELECT DISTINCT "customer"."c_last_name" AS "c_last_name", "customer"."c_first_name" AS "c_first_name", "date_dim"."d_date" AS "d_date" FROM "web_sales" AS "web_sales" JOIN "customer_2" AS "customer" - ON "web_sales"."ws_bill_customer_sk" = "customer"."c_customer_sk" + ON "customer"."c_customer_sk" = "web_sales"."ws_bill_customer_sk" JOIN "date_dim_2" AS "date_dim" - ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk" -), "cte_4" AS ( - SELECT - "cte_2"."c_last_name" AS "c_last_name", - "cte_2"."c_first_name" AS "c_first_name", - "cte_2"."d_date" AS "d_date" - FROM "cte_2" AS "cte_2" - INTERSECT - SELECT - "cte_3"."c_last_name" AS "c_last_name", - "cte_3"."c_first_name" AS "c_first_name", - "cte_3"."d_date" AS "d_date" - FROM "cte_3" AS "cte_3" -), "hot_cust" AS ( - SELECT - "cte"."c_last_name" AS "c_last_name", - "cte"."c_first_name" AS "c_first_name", - "cte"."d_date" AS "d_date" - FROM "cte" AS "cte" - INTERSECT - SELECT - "cte_4"."c_last_name" AS "c_last_name", - "cte_4"."c_first_name" AS "c_first_name", - "cte_4"."d_date" AS "d_date" - FROM "cte_4" AS "cte_4" + ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk" ) SELECT COUNT(*) AS "_col_0" @@ -4792,7 +4648,7 @@ WITH "foo" AS ( AVG("inventory"."inv_quantity_on_hand") AS "mean" FROM "inventory" AS "inventory" JOIN "date_dim" AS "date_dim" - ON "date_dim"."d_year" = 2002 AND "inventory"."inv_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "inventory"."inv_date_sk" AND "date_dim"."d_year" = 2002 JOIN "item" AS "item" ON "inventory"."inv_item_sk" = "item"."i_item_sk" JOIN "warehouse" AS "warehouse" @@ -4897,16 +4753,16 @@ SELECT ) AS "sales_after" FROM "catalog_sales" AS "catalog_sales" LEFT JOIN "catalog_returns" AS "catalog_returns" - ON "catalog_sales"."cs_item_sk" = "catalog_returns"."cr_item_sk" - AND "catalog_sales"."cs_order_number" = "catalog_returns"."cr_order_number" + ON "catalog_returns"."cr_item_sk" = "catalog_sales"."cs_item_sk" + AND "catalog_returns"."cr_order_number" = "catalog_sales"."cs_order_number" JOIN "date_dim" AS "date_dim" ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk" AND CAST("date_dim"."d_date" AS DATE) <= CAST('2002-07-01' AS DATE) AND CAST("date_dim"."d_date" AS DATE) >= CAST('2002-05-02' AS DATE) JOIN "item" AS "item" - ON "item"."i_current_price" <= 1.49 + ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk" + AND "item"."i_current_price" <= 1.49 AND "item"."i_current_price" >= 0.99 - AND "item"."i_item_sk" = "catalog_sales"."cs_item_sk" JOIN "warehouse" AS "warehouse" ON "catalog_sales"."cs_warehouse_sk" = "warehouse"."w_warehouse_sk" GROUP BY @@ -4997,7 +4853,7 @@ WHERE FROM "item" AS "item" WHERE ( - "item"."i_manufact" = "i1"."i_manufact" + "i1"."i_manufact" = "item"."i_manufact" AND ( ( "item"."i_category" = 'Men' @@ -5050,7 +4906,7 @@ WHERE ) ) OR ( - "item"."i_manufact" = "i1"."i_manufact" + "i1"."i_manufact" = "item"."i_manufact" AND ( ( "item"."i_category" = 'Men' @@ -5139,7 +4995,7 @@ FROM "date_dim" AS "dt" JOIN "store_sales" AS "store_sales" ON "dt"."d_date_sk" = "store_sales"."ss_sold_date_sk" JOIN "item" AS "item" - ON "item"."i_manager_id" = 1 AND "store_sales"."ss_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "store_sales"."ss_item_sk" AND "item"."i_manager_id" = 1 WHERE "dt"."d_moy" = 12 AND "dt"."d_year" = 2000 GROUP BY @@ -5351,7 +5207,7 @@ WITH "_u_0" AS ( GROUP BY "ss1"."ss_item_sk" HAVING - AVG("ss1"."ss_net_profit") > 0.9 * MAX("_u_0"."rank_col") + 0.9 * MAX("_u_0"."rank_col") < AVG("ss1"."ss_net_profit") ), "v11" AS ( SELECT "v1"."item_sk" AS "item_sk", @@ -5368,7 +5224,7 @@ WITH "_u_0" AS ( GROUP BY "ss1"."ss_item_sk" HAVING - AVG("ss1"."ss_net_profit") > 0.9 * MAX("_u_1"."rank_col") + 0.9 * MAX("_u_1"."rank_col") < AVG("ss1"."ss_net_profit") ), "v21" AS ( SELECT "v2"."item_sk" AS "item_sk", @@ -5437,15 +5293,15 @@ SELECT SUM("web_sales"."ws_sales_price") AS "_col_2" FROM "web_sales" AS "web_sales" JOIN "customer" AS "customer" - ON "web_sales"."ws_bill_customer_sk" = "customer"."c_customer_sk" + ON "customer"."c_customer_sk" = "web_sales"."ws_bill_customer_sk" JOIN "date_dim" AS "date_dim" - ON "date_dim"."d_qoy" = 1 + ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk" + AND "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" + ON "item"."i_item_sk" = "web_sales"."ws_item_sk" LEFT JOIN "_u_0" AS "_u_0" - ON "item"."i_item_id" = "_u_0"."i_item_id" + ON "_u_0"."i_item_id" = "item"."i_item_id" JOIN "customer_address" AS "customer_address" ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk" WHERE @@ -5514,20 +5370,20 @@ WITH "dn" AS ( SUM("store_sales"."ss_net_profit") AS "profit" FROM "store_sales" AS "store_sales" JOIN "customer_address" AS "customer_address" - ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk" + ON "customer_address"."ca_address_sk" = "store_sales"."ss_addr_sk" JOIN "date_dim" AS "date_dim" - ON "date_dim"."d_dow" IN (6, 0) + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" + AND "date_dim"."d_dow" IN (6, 0) AND "date_dim"."d_year" IN (2000, 2001, 2002) - AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" JOIN "household_demographics" AS "household_demographics" - ON ( + ON "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk" + AND ( "household_demographics"."hd_dep_count" = 6 OR "household_demographics"."hd_vehicle_count" = 0 ) - AND "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk" JOIN "store" AS "store" ON "store"."s_city" IN ('Midway', 'Fairview', 'Fairview', 'Fairview', 'Fairview') - AND "store_sales"."ss_store_sk" = "store"."s_store_sk" + AND "store"."s_store_sk" = "store_sales"."ss_store_sk" GROUP BY "store_sales"."ss_ticket_number", "store_sales"."ss_customer_sk", @@ -5544,10 +5400,10 @@ SELECT "dn"."profit" AS "profit" FROM "dn" AS "dn" JOIN "customer" AS "customer" - ON "dn"."ss_customer_sk" = "customer"."c_customer_sk" + ON "customer"."c_customer_sk" = "dn"."ss_customer_sk" JOIN "customer_address" AS "current_addr" - ON "current_addr"."ca_city" <> "dn"."bought_city" - AND "customer"."c_current_addr_sk" = "current_addr"."ca_address_sk" + ON "current_addr"."ca_address_sk" = "customer"."c_current_addr_sk" + AND "current_addr"."ca_city" <> "dn"."bought_city" ORDER BY "c_last_name", "c_first_name", @@ -5643,9 +5499,10 @@ WITH "v1" AS ( RANK() OVER (PARTITION BY "item"."i_category", "item"."i_brand", "store"."s_store_name", "store"."s_company_name" ORDER BY "date_dim"."d_year", "date_dim"."d_moy") AS "rn" FROM "item" AS "item" JOIN "store_sales" AS "store_sales" - ON "store_sales"."ss_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "store_sales"."ss_item_sk" JOIN "date_dim" AS "date_dim" - ON ( + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" + AND ( "date_dim"."d_moy" = 1 OR "date_dim"."d_moy" = 12 OR "date_dim"."d_year" = 1999 ) AND ( @@ -5657,9 +5514,8 @@ WITH "v1" AS ( AND ( "date_dim"."d_year" = 1998 OR "date_dim"."d_year" = 1999 OR "date_dim"."d_year" = 2000 ) - AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" JOIN "store" AS "store" - ON "store_sales"."ss_store_sk" = "store"."s_store_sk" + ON "store"."s_store_sk" = "store_sales"."ss_store_sk" GROUP BY "item"."i_category", "item"."i_brand", @@ -5743,23 +5599,23 @@ SELECT FROM "store_sales" AS "store_sales" JOIN "customer_address" AS "customer_address" ON ( - "customer_address"."ca_country" = 'United States' + "customer_address"."ca_address_sk" = "store_sales"."ss_addr_sk" + AND "customer_address"."ca_country" = 'United States' AND "customer_address"."ca_state" IN ('CO', 'TN', 'ND') - AND "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk" AND "store_sales"."ss_net_profit" <= 3000 AND "store_sales"."ss_net_profit" >= 150 ) OR ( - "customer_address"."ca_country" = 'United States' + "customer_address"."ca_address_sk" = "store_sales"."ss_addr_sk" + AND "customer_address"."ca_country" = 'United States' AND "customer_address"."ca_state" IN ('OK', 'PA', 'CA') - AND "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk" AND "store_sales"."ss_net_profit" <= 25000 AND "store_sales"."ss_net_profit" >= 50 ) OR ( - "customer_address"."ca_country" = 'United States' + "customer_address"."ca_address_sk" = "store_sales"."ss_addr_sk" + AND "customer_address"."ca_country" = 'United States' AND "customer_address"."ca_state" IN ('TX', 'NE', 'MO') - AND "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk" AND "store_sales"."ss_net_profit" <= 2000 AND "store_sales"."ss_net_profit" >= 0 ) @@ -5786,8 +5642,8 @@ JOIN "customer_demographics" AS "customer_demographics" AND "store_sales"."ss_sales_price" >= 100.00 ) JOIN "date_dim" AS "date_dim" - ON "date_dim"."d_year" = 1999 - AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" + AND "date_dim"."d_year" = 1999 JOIN "store" AS "store" ON "store"."s_store_sk" = "store_sales"."ss_store_sk"; @@ -5941,10 +5797,10 @@ WITH "date_dim_2" AS ( CAST(SUM(COALESCE("wr"."wr_return_amt", 0)) AS DECIMAL(15, 4)) / CAST(SUM(COALESCE("ws"."ws_net_paid", 0)) AS DECIMAL(15, 4)) AS "currency_ratio" FROM "web_sales" AS "ws" JOIN "date_dim_2" AS "date_dim" - ON "ws"."ws_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "ws"."ws_sold_date_sk" LEFT JOIN "web_returns" AS "wr" - ON "ws"."ws_item_sk" = "wr"."wr_item_sk" - AND "ws"."ws_order_number" = "wr"."wr_order_number" + ON "wr"."wr_item_sk" = "ws"."ws_item_sk" + AND "wr"."wr_order_number" = "ws"."ws_order_number" WHERE "wr"."wr_return_amt" > 10000 AND "ws"."ws_net_paid" > 0 @@ -5966,8 +5822,8 @@ WITH "date_dim_2" AS ( CAST(SUM(COALESCE("cr"."cr_return_amount", 0)) AS DECIMAL(15, 4)) / CAST(SUM(COALESCE("cs"."cs_net_paid", 0)) AS DECIMAL(15, 4)) AS "currency_ratio" FROM "catalog_sales" AS "cs" LEFT JOIN "catalog_returns" AS "cr" - ON "cs"."cs_item_sk" = "cr"."cr_item_sk" - AND "cs"."cs_order_number" = "cr"."cr_order_number" + ON "cr"."cr_item_sk" = "cs"."cs_item_sk" + AND "cr"."cr_order_number" = "cs"."cs_order_number" JOIN "date_dim_2" AS "date_dim" ON "cs"."cs_sold_date_sk" = "date_dim"."d_date_sk" WHERE @@ -5991,10 +5847,10 @@ WITH "date_dim_2" AS ( CAST(SUM(COALESCE("sr"."sr_return_amt", 0)) AS DECIMAL(15, 4)) / CAST(SUM(COALESCE("sts"."ss_net_paid", 0)) AS DECIMAL(15, 4)) AS "currency_ratio" FROM "store_sales" AS "sts" JOIN "date_dim_2" AS "date_dim" - ON "sts"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "sts"."ss_sold_date_sk" LEFT JOIN "store_returns" AS "sr" - ON "sts"."ss_item_sk" = "sr"."sr_item_sk" - AND "sts"."ss_ticket_number" = "sr"."sr_ticket_number" + ON "sr"."sr_item_sk" = "sts"."ss_item_sk" + AND "sr"."sr_ticket_number" = "sts"."ss_ticket_number" WHERE "sr"."sr_return_amt" > 10000 AND "sts"."ss_net_paid" > 0 @@ -6009,39 +5865,6 @@ WITH "date_dim_2" AS ( RANK() OVER (ORDER BY "in_store"."return_ratio") AS "return_rank", RANK() OVER (ORDER BY "in_store"."currency_ratio") AS "currency_rank" FROM "in_store" AS "in_store" -), "cte_3" AS ( - SELECT - 'store' AS "channel", - "store"."item" AS "item", - "store"."return_ratio" AS "return_ratio", - "store"."return_rank" AS "return_rank", - "store"."currency_rank" AS "currency_rank" - FROM "store" AS "store" - WHERE - "store"."currency_rank" <= 10 OR "store"."return_rank" <= 10 - ORDER BY - "channel", - "return_rank", - "currency_rank" - LIMIT 100 -), "cte_4" AS ( - SELECT - 'catalog' AS "channel", - "catalog"."item" AS "item", - "catalog"."return_ratio" AS "return_ratio", - "catalog"."return_rank" AS "return_rank", - "catalog"."currency_rank" AS "currency_rank" - FROM "catalog" AS "catalog" - WHERE - "catalog"."currency_rank" <= 10 OR "catalog"."return_rank" <= 10 - UNION - SELECT - "cte_3"."channel" AS "channel", - "cte_3"."item" AS "item", - "cte_3"."return_ratio" AS "return_ratio", - "cte_3"."return_rank" AS "return_rank", - "cte_3"."currency_rank" AS "currency_rank" - FROM "cte_3" AS "cte_3" ) SELECT 'web' AS "channel", @@ -6054,12 +5877,29 @@ WHERE "web"."currency_rank" <= 10 OR "web"."return_rank" <= 10 UNION SELECT - "cte_4"."channel" AS "channel", - "cte_4"."item" AS "item", - "cte_4"."return_ratio" AS "return_ratio", - "cte_4"."return_rank" AS "return_rank", - "cte_4"."currency_rank" AS "currency_rank" -FROM "cte_4" AS "cte_4"; + 'catalog' AS "channel", + "catalog"."item" AS "item", + "catalog"."return_ratio" AS "return_ratio", + "catalog"."return_rank" AS "return_rank", + "catalog"."currency_rank" AS "currency_rank" +FROM "catalog" AS "catalog" +WHERE + "catalog"."currency_rank" <= 10 OR "catalog"."return_rank" <= 10 +UNION +SELECT + 'store' AS "channel", + "store"."item" AS "item", + "store"."return_ratio" AS "return_ratio", + "store"."return_rank" AS "return_rank", + "store"."currency_rank" AS "currency_rank" +FROM "store" AS "store" +WHERE + "store"."currency_rank" <= 10 OR "store"."return_rank" <= 10 +ORDER BY + "channel", + "return_rank", + "currency_rank" +LIMIT 100; -------------------------------------- -- TPC-DS 50 @@ -6185,17 +6025,17 @@ SELECT ) AS ">120 days" FROM "store_sales" AS "store_sales" JOIN "date_dim" AS "d1" - ON "store_sales"."ss_sold_date_sk" = "d1"."d_date_sk" + ON "d1"."d_date_sk" = "store_sales"."ss_sold_date_sk" JOIN "store" AS "store" - ON "store_sales"."ss_store_sk" = "store"."s_store_sk" + ON "store"."s_store_sk" = "store_sales"."ss_store_sk" JOIN "store_returns" AS "store_returns" - ON "store_sales"."ss_customer_sk" = "store_returns"."sr_customer_sk" - AND "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk" - AND "store_sales"."ss_ticket_number" = "store_returns"."sr_ticket_number" + ON "store_returns"."sr_customer_sk" = "store_sales"."ss_customer_sk" + AND "store_returns"."sr_item_sk" = "store_sales"."ss_item_sk" + AND "store_returns"."sr_ticket_number" = "store_sales"."ss_ticket_number" JOIN "date_dim" AS "d2" - ON "d2"."d_moy" = 9 + ON "d2"."d_date_sk" = "store_returns"."sr_returned_date_sk" + AND "d2"."d_moy" = 9 AND "d2"."d_year" = 2002 - AND "store_returns"."sr_returned_date_sk" = "d2"."d_date_sk" GROUP BY "store"."s_store_name", "store"."s_company_id", @@ -6291,7 +6131,7 @@ WITH "date_dim_2" AS ( SUM(SUM("web_sales"."ws_sales_price")) OVER (PARTITION BY "web_sales"."ws_item_sk" ORDER BY "date_dim"."d_date" rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "cume_sales" FROM "web_sales" AS "web_sales" JOIN "date_dim_2" AS "date_dim" - ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk" WHERE NOT "web_sales"."ws_item_sk" IS NULL GROUP BY @@ -6304,7 +6144,7 @@ WITH "date_dim_2" AS ( SUM(SUM("store_sales"."ss_sales_price")) OVER (PARTITION BY "store_sales"."ss_item_sk" ORDER BY "date_dim"."d_date" rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "cume_sales" FROM "store_sales" AS "store_sales" JOIN "date_dim_2" AS "date_dim" - ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" WHERE NOT "store_sales"."ss_item_sk" IS NULL GROUP BY @@ -6332,7 +6172,7 @@ WITH "date_dim_2" AS ( END ORDER BY CASE WHEN NOT "web"."d_date" IS NULL THEN "web"."d_date" ELSE "store"."d_date" END rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "store_cumulative" FROM "web_v1" AS "web" FULL JOIN "store_v1" AS "store" - ON "web"."d_date" = "store"."d_date" AND "web"."item_sk" = "store"."item_sk" + ON "store"."d_date" = "web"."d_date" AND "store"."item_sk" = "web"."item_sk" ) SELECT "y"."item_sk" AS "item_sk", @@ -6343,7 +6183,7 @@ SELECT "y"."store_cumulative" AS "store_cumulative" FROM "y" AS "y" WHERE - "y"."web_cumulative" > "y"."store_cumulative" + "y"."store_cumulative" < "y"."web_cumulative" ORDER BY "y"."item_sk", "y"."d_date" @@ -6380,7 +6220,7 @@ FROM "date_dim" AS "dt" JOIN "store_sales" AS "store_sales" ON "dt"."d_date_sk" = "store_sales"."ss_sold_date_sk" JOIN "item" AS "item" - ON "item"."i_manager_id" = 1 AND "store_sales"."ss_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "store_sales"."ss_item_sk" AND "item"."i_manager_id" = 1 WHERE "dt"."d_moy" = 11 AND "dt"."d_year" = 1999 GROUP BY @@ -6448,12 +6288,12 @@ WITH "tmp1" AS ( AVG(SUM("store_sales"."ss_sales_price")) OVER (PARTITION BY "item"."i_manufact_id") AS "avg_quarterly_sales" FROM "item" AS "item" JOIN "store_sales" AS "store_sales" - ON "store_sales"."ss_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "store_sales"."ss_item_sk" JOIN "date_dim" AS "date_dim" - ON "date_dim"."d_month_seq" IN (1199, 1200, 1201, 1202, 1203, 1204, 1205, 1206, 1207, 1208, 1209, 1210) - AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" + AND "date_dim"."d_month_seq" IN (1199, 1200, 1201, 1202, 1203, 1204, 1205, 1206, 1207, 1208, 1209, 1210) JOIN "store" AS "store" - ON "store_sales"."ss_store_sk" = "store"."s_store_sk" + ON "store"."s_store_sk" = "store_sales"."ss_store_sk" WHERE ( "item"."i_brand" IN ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1') @@ -6589,7 +6429,7 @@ WITH "cs_or_ws_sales" AS ( "customer"."c_current_addr_sk" AS "c_current_addr_sk" FROM "cs_or_ws_sales" AS "cs_or_ws_sales" JOIN "customer" AS "customer" - ON "customer"."c_customer_sk" = "cs_or_ws_sales"."customer_sk" + ON "cs_or_ws_sales"."customer_sk" = "customer"."c_customer_sk" JOIN "date_dim" AS "date_dim" ON "cs_or_ws_sales"."sold_date_sk" = "date_dim"."d_date_sk" AND "date_dim"."d_moy" = 5 @@ -6615,18 +6455,18 @@ WITH "cs_or_ws_sales" AS ( SUM("store_sales"."ss_ext_sales_price") AS "revenue" FROM "my_customers" JOIN "customer_address" AS "customer_address" - ON "my_customers"."c_current_addr_sk" = "customer_address"."ca_address_sk" + ON "customer_address"."ca_address_sk" = "my_customers"."c_current_addr_sk" JOIN "store_sales" AS "store_sales" ON "my_customers"."c_customer_sk" = "store_sales"."ss_customer_sk" JOIN "date_dim" AS "date_dim" - ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" 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" + ON "_u_0"."_col_0" <= "date_dim"."d_month_seq" JOIN "_u_1" AS "_u_1" - ON "date_dim"."d_month_seq" <= "_u_1"."_col_0" + ON "_u_1"."_col_0" >= "date_dim"."d_month_seq" GROUP BY "my_customers"."c_customer_sk" ) @@ -6675,7 +6515,7 @@ 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 "item" AS "item" - ON "item"."i_manager_id" = 33 AND "store_sales"."ss_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "store_sales"."ss_item_sk" AND "item"."i_manager_id" = 33 WHERE "date_dim"."d_moy" = 12 AND "date_dim"."d_year" = 1998 GROUP BY @@ -6790,13 +6630,13 @@ WITH "customer_address_2" AS ( SUM("store_sales"."ss_ext_sales_price") AS "total_sales" FROM "store_sales" AS "store_sales" JOIN "customer_address_2" AS "customer_address" - ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk" + ON "customer_address"."ca_address_sk" = "store_sales"."ss_addr_sk" JOIN "date_dim_2" AS "date_dim" - ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" JOIN "item_2" AS "item" - ON "store_sales"."ss_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "store_sales"."ss_item_sk" LEFT JOIN "_u_0" AS "_u_0" - ON "item"."i_item_id" = "_u_0"."i_item_id" + ON "_u_0"."i_item_id" = "item"."i_item_id" WHERE NOT "_u_0"."i_item_id" IS NULL GROUP BY @@ -6813,7 +6653,7 @@ WITH "customer_address_2" AS ( 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" + ON "_u_1"."i_item_id" = "item"."i_item_id" WHERE NOT "_u_1"."i_item_id" IS NULL GROUP BY @@ -6824,18 +6664,23 @@ WITH "customer_address_2" AS ( SUM("web_sales"."ws_ext_sales_price") AS "total_sales" FROM "web_sales" AS "web_sales" JOIN "customer_address_2" AS "customer_address" - ON "web_sales"."ws_bill_addr_sk" = "customer_address"."ca_address_sk" + ON "customer_address"."ca_address_sk" = "web_sales"."ws_bill_addr_sk" JOIN "date_dim_2" AS "date_dim" - ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk" JOIN "item_2" AS "item" - ON "web_sales"."ws_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "web_sales"."ws_item_sk" LEFT JOIN "_u_0" AS "_u_2" - ON "item"."i_item_id" = "_u_2"."i_item_id" + ON "_u_2"."i_item_id" = "item"."i_item_id" WHERE NOT "_u_2"."i_item_id" IS NULL GROUP BY "item"."i_item_id" -), "cte_4" AS ( +), "tmp1" AS ( + SELECT + "ss"."i_item_id" AS "i_item_id", + "ss"."total_sales" AS "total_sales" + FROM "ss" + UNION ALL SELECT "cs"."i_item_id" AS "i_item_id", "cs"."total_sales" AS "total_sales" @@ -6845,16 +6690,6 @@ WITH "customer_address_2" AS ( "ws"."i_item_id" AS "i_item_id", "ws"."total_sales" AS "total_sales" FROM "ws" -), "tmp1" AS ( - SELECT - "ss"."i_item_id" AS "i_item_id", - "ss"."total_sales" AS "total_sales" - FROM "ss" - UNION ALL - SELECT - "cte_4"."i_item_id" AS "i_item_id", - "cte_4"."total_sales" AS "total_sales" - FROM "cte_4" AS "cte_4" ) SELECT "tmp1"."i_item_id" AS "i_item_id", @@ -7095,7 +6930,7 @@ WITH "date_dim_2" AS ( "date_dim"."d_date" AS "d_date" FROM "date_dim" AS "date_dim" JOIN "_u_0" AS "_u_0" - ON "date_dim"."d_week_seq" = "_u_0"."d_week_seq" + ON "_u_0"."d_week_seq" = "date_dim"."d_week_seq" GROUP BY "date_dim"."d_date" ), "ss_items" AS ( @@ -7104,11 +6939,11 @@ WITH "date_dim_2" AS ( SUM("store_sales"."ss_ext_sales_price") AS "ss_item_rev" FROM "store_sales" AS "store_sales" JOIN "date_dim_2" AS "date_dim" - ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" JOIN "item_2" AS "item" - ON "store_sales"."ss_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "store_sales"."ss_item_sk" LEFT JOIN "_u_1" AS "_u_1" - ON "date_dim"."d_date" = "_u_1"."d_date" + ON "_u_1"."d_date" = "date_dim"."d_date" WHERE NOT "_u_1"."d_date" IS NULL GROUP BY @@ -7118,7 +6953,7 @@ WITH "date_dim_2" AS ( "date_dim"."d_date" AS "d_date" FROM "date_dim" AS "date_dim" JOIN "_u_0" AS "_u_2" - ON "date_dim"."d_week_seq" = "_u_2"."d_week_seq" + ON "_u_2"."d_week_seq" = "date_dim"."d_week_seq" GROUP BY "date_dim"."d_date" ), "cs_items" AS ( @@ -7131,7 +6966,7 @@ WITH "date_dim_2" AS ( JOIN "item_2" AS "item" ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk" LEFT JOIN "_u_3" AS "_u_3" - ON "date_dim"."d_date" = "_u_3"."d_date" + ON "_u_3"."d_date" = "date_dim"."d_date" WHERE NOT "_u_3"."d_date" IS NULL GROUP BY @@ -7141,7 +6976,7 @@ WITH "date_dim_2" AS ( "date_dim"."d_date" AS "d_date" FROM "date_dim" AS "date_dim" JOIN "_u_0" AS "_u_4" - ON "date_dim"."d_week_seq" = "_u_4"."d_week_seq" + ON "_u_4"."d_week_seq" = "date_dim"."d_week_seq" GROUP BY "date_dim"."d_date" ), "ws_items" AS ( @@ -7150,11 +6985,11 @@ WITH "date_dim_2" AS ( SUM("web_sales"."ws_ext_sales_price") AS "ws_item_rev" FROM "web_sales" AS "web_sales" JOIN "date_dim_2" AS "date_dim" - ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk" JOIN "item_2" AS "item" - ON "web_sales"."ws_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "web_sales"."ws_item_sk" LEFT JOIN "_u_5" AS "_u_5" - ON "date_dim"."d_date" = "_u_5"."d_date" + ON "_u_5"."d_date" = "date_dim"."d_date" WHERE NOT "_u_5"."d_date" IS NULL GROUP BY @@ -7189,7 +7024,7 @@ JOIN "cs_items" 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 "ss_items"."item_id" = "cs_items"."item_id" + 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" AND "ws_items"."ws_item_rev" <= 1.1 * "cs_items"."cs_item_rev" @@ -7362,7 +7197,7 @@ WITH "wss" AS ( AND "d"."d_month_seq" >= 1208 AND "d"."d_week_seq" = "wss"."d_week_seq" JOIN "store" AS "store" - ON "wss"."ss_store_sk" = "store"."s_store_sk" + ON "store"."s_store_sk" = "wss"."ss_store_sk" ) SELECT "store"."s_store_name" AS "s_store_name1", @@ -7381,7 +7216,7 @@ JOIN "date_dim" AS "d" AND "d"."d_month_seq" >= 1196 AND "d"."d_week_seq" = "wss"."d_week_seq" JOIN "store" AS "store" - ON "wss"."ss_store_sk" = "store"."s_store_sk" + ON "store"."s_store_sk" = "wss"."ss_store_sk" JOIN "x" AS "x" ON "store"."s_store_id" = "x"."s_store_id2" AND "wss"."d_week_seq" = "x"."d_week_seq2" - 52 @@ -7493,13 +7328,13 @@ WITH "customer_address_2" AS ( SUM("store_sales"."ss_ext_sales_price") AS "total_sales" FROM "store_sales" AS "store_sales" JOIN "customer_address_2" AS "customer_address" - ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk" + ON "customer_address"."ca_address_sk" = "store_sales"."ss_addr_sk" JOIN "date_dim_2" AS "date_dim" - ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" JOIN "item_2" AS "item" - ON "store_sales"."ss_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "store_sales"."ss_item_sk" LEFT JOIN "_u_0" AS "_u_0" - ON "item"."i_item_id" = "_u_0"."i_item_id" + ON "_u_0"."i_item_id" = "item"."i_item_id" WHERE NOT "_u_0"."i_item_id" IS NULL GROUP BY @@ -7516,7 +7351,7 @@ WITH "customer_address_2" AS ( 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" + ON "_u_1"."i_item_id" = "item"."i_item_id" WHERE NOT "_u_1"."i_item_id" IS NULL GROUP BY @@ -7527,18 +7362,23 @@ WITH "customer_address_2" AS ( SUM("web_sales"."ws_ext_sales_price") AS "total_sales" FROM "web_sales" AS "web_sales" JOIN "customer_address_2" AS "customer_address" - ON "web_sales"."ws_bill_addr_sk" = "customer_address"."ca_address_sk" + ON "customer_address"."ca_address_sk" = "web_sales"."ws_bill_addr_sk" JOIN "date_dim_2" AS "date_dim" - ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk" JOIN "item_2" AS "item" - ON "web_sales"."ws_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "web_sales"."ws_item_sk" LEFT JOIN "_u_0" AS "_u_2" - ON "item"."i_item_id" = "_u_2"."i_item_id" + ON "_u_2"."i_item_id" = "item"."i_item_id" WHERE NOT "_u_2"."i_item_id" IS NULL GROUP BY "item"."i_item_id" -), "cte_4" AS ( +), "tmp1" AS ( + SELECT + "ss"."i_item_id" AS "i_item_id", + "ss"."total_sales" AS "total_sales" + FROM "ss" + UNION ALL SELECT "cs"."i_item_id" AS "i_item_id", "cs"."total_sales" AS "total_sales" @@ -7548,16 +7388,6 @@ WITH "customer_address_2" AS ( "ws"."i_item_id" AS "i_item_id", "ws"."total_sales" AS "total_sales" FROM "ws" -), "tmp1" AS ( - SELECT - "ss"."i_item_id" AS "i_item_id", - "ss"."total_sales" AS "total_sales" - FROM "ss" - UNION ALL - SELECT - "cte_4"."i_item_id" AS "i_item_id", - "cte_4"."total_sales" AS "total_sales" - FROM "cte_4" AS "cte_4" ) SELECT "tmp1"."i_item_id" AS "i_item_id", @@ -7658,36 +7488,36 @@ WITH "customer_2" AS ( SUM("store_sales"."ss_ext_sales_price") AS "promotions" FROM "store_sales" AS "store_sales" JOIN "customer_2" AS "customer" - ON "store_sales"."ss_customer_sk" = "customer"."c_customer_sk" + ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk" JOIN "date_dim_2" AS "date_dim" - ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" JOIN "item_2" AS "item" - ON "store_sales"."ss_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "store_sales"."ss_item_sk" JOIN "promotion" AS "promotion" ON ( "promotion"."p_channel_dmail" = 'Y' OR "promotion"."p_channel_email" = 'Y' OR "promotion"."p_channel_tv" = 'Y' ) - AND "store_sales"."ss_promo_sk" = "promotion"."p_promo_sk" + AND "promotion"."p_promo_sk" = "store_sales"."ss_promo_sk" JOIN "store_2" AS "store" - ON "store_sales"."ss_store_sk" = "store"."s_store_sk" + ON "store"."s_store_sk" = "store_sales"."ss_store_sk" JOIN "customer_address_2" AS "customer_address" - ON "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk" + ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk" ), "all_sales" AS ( SELECT SUM("store_sales"."ss_ext_sales_price") AS "total" FROM "store_sales" AS "store_sales" JOIN "customer_2" AS "customer" - ON "store_sales"."ss_customer_sk" = "customer"."c_customer_sk" + ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk" JOIN "date_dim_2" AS "date_dim" - ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" JOIN "item_2" AS "item" - ON "store_sales"."ss_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "store_sales"."ss_item_sk" JOIN "store_2" AS "store" - ON "store_sales"."ss_store_sk" = "store"."s_store_sk" + ON "store"."s_store_sk" = "store_sales"."ss_store_sk" JOIN "customer_address_2" AS "customer_address" - ON "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk" + ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk" ) SELECT "promotional_sales"."promotions" AS "promotions", @@ -7791,13 +7621,13 @@ SELECT ) AS ">120 days" FROM "web_sales" AS "web_sales" JOIN "date_dim" AS "date_dim" - ON "date_dim"."d_month_seq" <= 1233 + ON "date_dim"."d_date_sk" = "web_sales"."ws_ship_date_sk" + AND "date_dim"."d_month_seq" <= 1233 AND "date_dim"."d_month_seq" >= 1222 - AND "web_sales"."ws_ship_date_sk" = "date_dim"."d_date_sk" JOIN "ship_mode" AS "ship_mode" - ON "web_sales"."ws_ship_mode_sk" = "ship_mode"."sm_ship_mode_sk" + ON "ship_mode"."sm_ship_mode_sk" = "web_sales"."ws_ship_mode_sk" JOIN "warehouse" AS "warehouse" - ON "web_sales"."ws_warehouse_sk" = "warehouse"."w_warehouse_sk" + ON "warehouse"."w_warehouse_sk" = "web_sales"."ws_warehouse_sk" JOIN "web_site" AS "web_site" ON "web_sales"."ws_web_site_sk" = "web_site"."web_site_sk" GROUP BY @@ -7864,12 +7694,12 @@ WITH "tmp1" AS ( AVG(SUM("store_sales"."ss_sales_price")) OVER (PARTITION BY "item"."i_manager_id") AS "avg_monthly_sales" FROM "item" AS "item" JOIN "store_sales" AS "store_sales" - ON "store_sales"."ss_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "store_sales"."ss_item_sk" JOIN "date_dim" AS "date_dim" - ON "date_dim"."d_month_seq" IN (1200, 1201, 1202, 1203, 1204, 1205, 1206, 1207, 1208, 1209, 1210, 1211) - AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" + AND "date_dim"."d_month_seq" IN (1200, 1201, 1202, 1203, 1204, 1205, 1206, 1207, 1208, 1209, 1210, 1211) JOIN "store" AS "store" - ON "store_sales"."ss_store_sk" = "store"."s_store_sk" + ON "store"."s_store_sk" = "store_sales"."ss_store_sk" WHERE ( "item"."i_brand" IN ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1') @@ -8057,14 +7887,14 @@ WITH "cs_ui" AS ( "catalog_sales"."cs_item_sk" AS "cs_item_sk" FROM "catalog_sales" AS "catalog_sales" JOIN "catalog_returns" AS "catalog_returns" - ON "catalog_sales"."cs_item_sk" = "catalog_returns"."cr_item_sk" - AND "catalog_sales"."cs_order_number" = "catalog_returns"."cr_order_number" + ON "catalog_returns"."cr_item_sk" = "catalog_sales"."cs_item_sk" + AND "catalog_returns"."cr_order_number" = "catalog_sales"."cs_order_number" GROUP BY "catalog_sales"."cs_item_sk" HAVING - SUM("catalog_sales"."cs_ext_list_price") > 2 * SUM( + 2 * SUM( "catalog_returns"."cr_refunded_cash" + "catalog_returns"."cr_reversed_charge" + "catalog_returns"."cr_store_credit" - ) + ) < SUM("catalog_sales"."cs_ext_list_price") ), "cross_sales" AS ( SELECT "item"."i_product_name" AS "product_name", @@ -8087,43 +7917,43 @@ WITH "cs_ui" AS ( FROM "store_sales" AS "store_sales" CROSS JOIN "income_band" AS "ib2" JOIN "customer_address" AS "ad1" - ON "store_sales"."ss_addr_sk" = "ad1"."ca_address_sk" + ON "ad1"."ca_address_sk" = "store_sales"."ss_addr_sk" JOIN "cs_ui" - ON "store_sales"."ss_item_sk" = "cs_ui"."cs_item_sk" + ON "cs_ui"."cs_item_sk" = "store_sales"."ss_item_sk" JOIN "date_dim" AS "d1" - ON "store_sales"."ss_sold_date_sk" = "d1"."d_date_sk" + ON "d1"."d_date_sk" = "store_sales"."ss_sold_date_sk" JOIN "household_demographics" AS "hd1" - ON "store_sales"."ss_hdemo_sk" = "hd1"."hd_demo_sk" + 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 AND "item"."i_current_price" >= 59 - AND "store_sales"."ss_item_sk" = "item"."i_item_sk" + AND "item"."i_item_sk" = "store_sales"."ss_item_sk" JOIN "promotion" AS "promotion" - ON "store_sales"."ss_promo_sk" = "promotion"."p_promo_sk" + ON "promotion"."p_promo_sk" = "store_sales"."ss_promo_sk" JOIN "store" AS "store" - ON "store_sales"."ss_store_sk" = "store"."s_store_sk" + ON "store"."s_store_sk" = "store_sales"."ss_store_sk" JOIN "store_returns" AS "store_returns" - ON "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk" - AND "store_sales"."ss_ticket_number" = "store_returns"."sr_ticket_number" + 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 "store_sales"."ss_customer_sk" = "customer"."c_customer_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 "customer"."c_current_addr_sk" = "ad2"."ca_address_sk" + ON "ad2"."ca_address_sk" = "customer"."c_current_addr_sk" JOIN "customer_demographics" AS "cd2" - ON "customer"."c_current_cdemo_sk" = "cd2"."cd_demo_sk" + ON "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_marital_status" <> "cd2"."cd_marital_status" - AND "store_sales"."ss_cdemo_sk" = "cd1"."cd_demo_sk" + ON "cd1"."cd_demo_sk" = "store_sales"."ss_cdemo_sk" + AND "cd1"."cd_marital_status" <> "cd2"."cd_marital_status" GROUP BY "item"."i_product_name", "item"."i_item_sk", @@ -8165,10 +7995,10 @@ SELECT "cs2"."cnt" AS "cnt" FROM "cross_sales" AS "cs1" JOIN "cross_sales" AS "cs2" - ON "cs1"."item_sk" = "cs2"."item_sk" + ON "cs1"."cnt" >= "cs2"."cnt" + AND "cs1"."item_sk" = "cs2"."item_sk" AND "cs1"."store_name" = "cs2"."store_name" AND "cs1"."store_zip" = "cs2"."store_zip" - AND "cs2"."cnt" <= "cs1"."cnt" AND "cs2"."syear" = 2002 WHERE "cs1"."syear" = 2001 @@ -8237,7 +8067,7 @@ WITH "store_sales_2" AS ( SUM("store_sales"."ss_sales_price") AS "revenue" FROM "store_sales_2" AS "store_sales" JOIN "date_dim_2" AS "date_dim" - ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" GROUP BY "store_sales"."ss_store_sk", "store_sales"."ss_item_sk" @@ -8247,7 +8077,7 @@ WITH "store_sales_2" AS ( SUM("store_sales"."ss_sales_price") AS "revenue" FROM "store_sales_2" AS "store_sales" JOIN "date_dim_2" AS "date_dim" - ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" GROUP BY "store_sales"."ss_store_sk", "store_sales"."ss_item_sk" @@ -8268,7 +8098,7 @@ SELECT "item"."i_brand" AS "i_brand" FROM "store" AS "store" JOIN "sc" AS "sc" - ON "store"."s_store_sk" = "sc"."ss_store_sk" + ON "sc"."ss_store_sk" = "store"."s_store_sk" JOIN "item" AS "item" ON "item"."i_item_sk" = "sc"."ss_item_sk" JOIN "sb" AS "sb" @@ -8618,7 +8448,7 @@ WITH "date_dim_2" AS ( "warehouse"."w_state" AS "w_state", "warehouse"."w_country" AS "w_country" FROM "warehouse" AS "warehouse" -), "cte" AS ( +), "x" AS ( SELECT "warehouse"."w_warehouse_name" AS "w_warehouse_name", "warehouse"."w_warehouse_sq_ft" AS "w_warehouse_sq_ft", @@ -8798,13 +8628,13 @@ WITH "date_dim_2" AS ( ) AS "dec_net" FROM "web_sales" AS "web_sales" JOIN "date_dim_2" AS "date_dim" - ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk" JOIN "ship_mode_2" AS "ship_mode" - ON "web_sales"."ws_ship_mode_sk" = "ship_mode"."sm_ship_mode_sk" + ON "ship_mode"."sm_ship_mode_sk" = "web_sales"."ws_ship_mode_sk" JOIN "time_dim_2" AS "time_dim" - ON "web_sales"."ws_sold_time_sk" = "time_dim"."t_time_sk" + ON "time_dim"."t_time_sk" = "web_sales"."ws_sold_time_sk" JOIN "warehouse_2" AS "warehouse" - ON "web_sales"."ws_warehouse_sk" = "warehouse"."w_warehouse_sk" + ON "warehouse"."w_warehouse_sk" = "web_sales"."ws_warehouse_sk" GROUP BY "warehouse"."w_warehouse_name", "warehouse"."w_warehouse_sq_ft", @@ -8813,7 +8643,7 @@ WITH "date_dim_2" AS ( "warehouse"."w_state", "warehouse"."w_country", "date_dim"."d_year" -), "cte_2" AS ( + UNION ALL SELECT "warehouse"."w_warehouse_name" AS "w_warehouse_name", "warehouse"."w_warehouse_sq_ft" AS "w_warehouse_sq_ft", @@ -9008,76 +8838,6 @@ WITH "date_dim_2" AS ( "warehouse"."w_state", "warehouse"."w_country", "date_dim"."d_year" -), "x" AS ( - SELECT - "cte"."w_warehouse_name" AS "w_warehouse_name", - "cte"."w_warehouse_sq_ft" AS "w_warehouse_sq_ft", - "cte"."w_city" AS "w_city", - "cte"."w_county" AS "w_county", - "cte"."w_state" AS "w_state", - "cte"."w_country" AS "w_country", - "cte"."ship_carriers" AS "ship_carriers", - "cte"."year1" AS "year1", - "cte"."jan_sales" AS "jan_sales", - "cte"."feb_sales" AS "feb_sales", - "cte"."mar_sales" AS "mar_sales", - "cte"."apr_sales" AS "apr_sales", - "cte"."may_sales" AS "may_sales", - "cte"."jun_sales" AS "jun_sales", - "cte"."jul_sales" AS "jul_sales", - "cte"."aug_sales" AS "aug_sales", - "cte"."sep_sales" AS "sep_sales", - "cte"."oct_sales" AS "oct_sales", - "cte"."nov_sales" AS "nov_sales", - "cte"."dec_sales" AS "dec_sales", - "cte"."jan_net" AS "jan_net", - "cte"."feb_net" AS "feb_net", - "cte"."mar_net" AS "mar_net", - "cte"."apr_net" AS "apr_net", - "cte"."may_net" AS "may_net", - "cte"."jun_net" AS "jun_net", - "cte"."jul_net" AS "jul_net", - "cte"."aug_net" AS "aug_net", - "cte"."sep_net" AS "sep_net", - "cte"."oct_net" AS "oct_net", - "cte"."nov_net" AS "nov_net", - "cte"."dec_net" AS "dec_net" - FROM "cte" AS "cte" - UNION ALL - SELECT - "cte_2"."w_warehouse_name" AS "w_warehouse_name", - "cte_2"."w_warehouse_sq_ft" AS "w_warehouse_sq_ft", - "cte_2"."w_city" AS "w_city", - "cte_2"."w_county" AS "w_county", - "cte_2"."w_state" AS "w_state", - "cte_2"."w_country" AS "w_country", - "cte_2"."ship_carriers" AS "ship_carriers", - "cte_2"."year1" AS "year1", - "cte_2"."jan_sales" AS "jan_sales", - "cte_2"."feb_sales" AS "feb_sales", - "cte_2"."mar_sales" AS "mar_sales", - "cte_2"."apr_sales" AS "apr_sales", - "cte_2"."may_sales" AS "may_sales", - "cte_2"."jun_sales" AS "jun_sales", - "cte_2"."jul_sales" AS "jul_sales", - "cte_2"."aug_sales" AS "aug_sales", - "cte_2"."sep_sales" AS "sep_sales", - "cte_2"."oct_sales" AS "oct_sales", - "cte_2"."nov_sales" AS "nov_sales", - "cte_2"."dec_sales" AS "dec_sales", - "cte_2"."jan_net" AS "jan_net", - "cte_2"."feb_net" AS "feb_net", - "cte_2"."mar_net" AS "mar_net", - "cte_2"."apr_net" AS "apr_net", - "cte_2"."may_net" AS "may_net", - "cte_2"."jun_net" AS "jun_net", - "cte_2"."jul_net" AS "jul_net", - "cte_2"."aug_net" AS "aug_net", - "cte_2"."sep_net" AS "sep_net", - "cte_2"."oct_net" AS "oct_net", - "cte_2"."nov_net" AS "nov_net", - "cte_2"."dec_net" AS "dec_net" - FROM "cte_2" AS "cte_2" ) SELECT "x"."w_warehouse_name" AS "w_warehouse_name", @@ -9181,8 +8941,7 @@ order by i_category ,s_store_id ,sumsales ,rk -limit 100 -; +limit 100; WITH "dw1" AS ( SELECT "item"."i_category" AS "i_category", @@ -9196,13 +8955,13 @@ WITH "dw1" AS ( SUM(COALESCE("store_sales"."ss_sales_price" * "store_sales"."ss_quantity", 0)) AS "sumsales" FROM "store_sales" AS "store_sales" JOIN "date_dim" AS "date_dim" - ON "date_dim"."d_month_seq" <= 1192 + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" + AND "date_dim"."d_month_seq" <= 1192 AND "date_dim"."d_month_seq" >= 1181 - AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" JOIN "item" AS "item" - ON "store_sales"."ss_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "store_sales"."ss_item_sk" JOIN "store" AS "store" - ON "store_sales"."ss_store_sk" = "store"."s_store_sk" + ON "store"."s_store_sk" = "store_sales"."ss_store_sk" GROUP BY ROLLUP ( "item"."i_category", @@ -9308,21 +9067,21 @@ WITH "dn" AS ( SUM("store_sales"."ss_ext_tax") AS "extended_tax" FROM "store_sales" AS "store_sales" JOIN "customer_address" AS "customer_address" - ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk" + ON "customer_address"."ca_address_sk" = "store_sales"."ss_addr_sk" JOIN "date_dim" AS "date_dim" - ON "date_dim"."d_dom" <= 2 + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" + AND "date_dim"."d_dom" <= 2 AND "date_dim"."d_dom" >= 1 AND "date_dim"."d_year" IN (1998, 1999, 2000) - AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" JOIN "household_demographics" AS "household_demographics" - ON ( + ON "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk" + AND ( "household_demographics"."hd_dep_count" = 8 OR "household_demographics"."hd_vehicle_count" = 3 ) - AND "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk" JOIN "store" AS "store" ON "store"."s_city" IN ('Fairview', 'Midway') - AND "store_sales"."ss_store_sk" = "store"."s_store_sk" + AND "store"."s_store_sk" = "store_sales"."ss_store_sk" GROUP BY "store_sales"."ss_ticket_number", "store_sales"."ss_customer_sk", @@ -9340,10 +9099,10 @@ SELECT "dn"."list_price" AS "list_price" FROM "dn" AS "dn" JOIN "customer" AS "customer" - ON "dn"."ss_customer_sk" = "customer"."c_customer_sk" + ON "customer"."c_customer_sk" = "dn"."ss_customer_sk" JOIN "customer_address" AS "current_addr" - ON "current_addr"."ca_city" <> "dn"."bought_city" - AND "customer"."c_current_addr_sk" = "current_addr"."ca_address_sk" + ON "current_addr"."ca_address_sk" = "customer"."c_current_addr_sk" + AND "current_addr"."ca_city" <> "dn"."bought_city" ORDER BY "c_last_name", "ss_ticket_number" @@ -9436,7 +9195,7 @@ WITH "date_dim_2" AS ( "store_sales"."ss_customer_sk" AS "_u_1" FROM "store_sales" AS "store_sales" JOIN "date_dim_2" AS "date_dim" - ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" GROUP BY "store_sales"."ss_customer_sk" ), "_u_2" AS ( @@ -9444,7 +9203,7 @@ WITH "date_dim_2" AS ( "web_sales"."ws_bill_customer_sk" AS "_u_3" FROM "web_sales" AS "web_sales" JOIN "date_dim_2" AS "date_dim" - ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk" GROUP BY "web_sales"."ws_bill_customer_sk" ), "_u_4" AS ( @@ -9467,16 +9226,16 @@ SELECT COUNT(*) AS "cnt3" FROM "customer" AS "c" LEFT JOIN "_u_0" AS "_u_0" - ON "c"."c_customer_sk" = "_u_0"."_u_1" + ON "_u_0"."_u_1" = "c"."c_customer_sk" LEFT JOIN "_u_2" AS "_u_2" - ON "c"."c_customer_sk" = "_u_2"."_u_3" + ON "_u_2"."_u_3" = "c"."c_customer_sk" LEFT JOIN "_u_4" AS "_u_4" - ON "c"."c_customer_sk" = "_u_4"."_u_5" + ON "_u_4"."_u_5" = "c"."c_customer_sk" JOIN "customer_address" AS "ca" ON "c"."c_current_addr_sk" = "ca"."ca_address_sk" AND "ca"."ca_state" IN ('KS', 'AZ', 'NE') JOIN "customer_demographics" AS "customer_demographics" - ON "customer_demographics"."cd_demo_sk" = "c"."c_current_cdemo_sk" + ON "c"."c_current_cdemo_sk" = "customer_demographics"."cd_demo_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 @@ -9577,7 +9336,7 @@ JOIN "date_dim" AS "d1" 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" + ON "_u_0"."s_state" = "store"."s_state" WHERE NOT "_u_0"."s_state" IS NULL GROUP BY @@ -9649,14 +9408,22 @@ WITH "date_dim_2" AS ( FROM "date_dim" AS "date_dim" WHERE "date_dim"."d_moy" = 11 AND "date_dim"."d_year" = 2001 -), "cte_4" AS ( +), "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" + JOIN "date_dim_2" AS "date_dim" + ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk" + UNION ALL SELECT "catalog_sales"."cs_ext_sales_price" AS "ext_price", "catalog_sales"."cs_item_sk" AS "sold_item_sk", "catalog_sales"."cs_sold_time_sk" AS "time_sk" FROM "catalog_sales" AS "catalog_sales" JOIN "date_dim_2" AS "date_dim" - ON "date_dim"."d_date_sk" = "catalog_sales"."cs_sold_date_sk" + ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk" UNION ALL SELECT "store_sales"."ss_ext_sales_price" AS "ext_price", @@ -9665,20 +9432,6 @@ WITH "date_dim_2" AS ( FROM "store_sales" AS "store_sales" 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" - 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", - "cte_4"."sold_item_sk" AS "sold_item_sk", - "cte_4"."time_sk" AS "time_sk" - FROM "cte_4" AS "cte_4" ) SELECT "item"."i_brand_id" AS "brand_id", @@ -9688,12 +9441,12 @@ SELECT SUM("tmp"."ext_price") AS "ext_price" FROM "item" AS "item" JOIN "tmp" AS "tmp" - ON "tmp"."sold_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "tmp"."sold_item_sk" JOIN "time_dim" AS "time_dim" ON ( "time_dim"."t_meal_time" = 'breakfast' OR "time_dim"."t_meal_time" = 'dinner' ) - AND "tmp"."time_sk" = "time_dim"."t_time_sk" + AND "time_dim"."t_time_sk" = "tmp"."time_sk" WHERE "item"."i_manager_id" = 1 GROUP BY @@ -9777,15 +9530,15 @@ JOIN "household_demographics" AS "household_demographics" AND "household_demographics"."hd_buy_potential" = '501-1000' JOIN "inventory" AS "inventory" ON "catalog_sales"."cs_item_sk" = "inventory"."inv_item_sk" - AND "inventory"."inv_quantity_on_hand" < "catalog_sales"."cs_quantity" + AND "catalog_sales"."cs_quantity" > "inventory"."inv_quantity_on_hand" JOIN "item" AS "item" - ON "item"."i_item_sk" = "catalog_sales"."cs_item_sk" + ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk" LEFT JOIN "promotion" AS "promotion" ON "catalog_sales"."cs_promo_sk" = "promotion"."p_promo_sk" JOIN "date_dim" AS "d2" - ON "inventory"."inv_date_sk" = "d2"."d_date_sk" + ON "d2"."d_date_sk" = "inventory"."inv_date_sk" JOIN "warehouse" AS "warehouse" - ON "warehouse"."w_warehouse_sk" = "inventory"."inv_warehouse_sk" + 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" @@ -9850,17 +9603,17 @@ WITH "dj" AS ( COUNT(*) AS "cnt" FROM "store_sales" AS "store_sales" JOIN "date_dim" AS "date_dim" - ON "date_dim"."d_dom" <= 2 + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" + AND "date_dim"."d_dom" <= 2 AND "date_dim"."d_dom" >= 1 AND "date_dim"."d_year" IN (2000, 2001, 2002) - AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" JOIN "household_demographics" AS "household_demographics" ON ( "household_demographics"."hd_buy_potential" = '0-500' OR "household_demographics"."hd_buy_potential" = '>10000' ) + AND "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk" AND "household_demographics"."hd_vehicle_count" > 0 - AND "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk" AND CASE WHEN "household_demographics"."hd_vehicle_count" > 0 THEN "household_demographics"."hd_dep_count" / "household_demographics"."hd_vehicle_count" @@ -9868,7 +9621,7 @@ WITH "dj" AS ( END > 1 JOIN "store" AS "store" ON "store"."s_county" IN ('Williamson County', 'Williamson County', 'Williamson County', 'Williamson County') - AND "store_sales"."ss_store_sk" = "store"."s_store_sk" + AND "store"."s_store_sk" = "store_sales"."ss_store_sk" GROUP BY "store_sales"."ss_ticket_number", "store_sales"."ss_customer_sk" @@ -9882,7 +9635,7 @@ SELECT "dj"."cnt" AS "cnt" FROM "dj" AS "dj" JOIN "customer" AS "customer" - ON "dj"."ss_customer_sk" = "customer"."c_customer_sk" + ON "customer"."c_customer_sk" = "dj"."ss_customer_sk" WHERE "dj"."cnt" <= 5 AND "dj"."cnt" >= 1 ORDER BY @@ -9974,7 +9727,7 @@ WITH "customer_2" AS ( FROM "date_dim" AS "date_dim" WHERE "date_dim"."d_year" IN (1999, 2000) -), "cte" AS ( +), "year_total" AS ( SELECT "customer"."c_customer_id" AS "customer_id", "customer"."c_first_name" AS "customer_first_name", @@ -9986,13 +9739,13 @@ WITH "customer_2" AS ( JOIN "store_sales" AS "store_sales" ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk" JOIN "date_dim_2" AS "date_dim" - ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" GROUP BY "customer"."c_customer_id", "customer"."c_first_name", "customer"."c_last_name", "date_dim"."d_year" -), "cte_2" AS ( + UNION ALL SELECT "customer"."c_customer_id" AS "customer_id", "customer"."c_first_name" AS "customer_first_name", @@ -10004,30 +9757,12 @@ WITH "customer_2" AS ( JOIN "web_sales" AS "web_sales" ON "customer"."c_customer_sk" = "web_sales"."ws_bill_customer_sk" JOIN "date_dim_2" AS "date_dim" - ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk" GROUP BY "customer"."c_customer_id", "customer"."c_first_name", "customer"."c_last_name", "date_dim"."d_year" -), "year_total" AS ( - SELECT - "cte"."customer_id" AS "customer_id", - "cte"."customer_first_name" AS "customer_first_name", - "cte"."customer_last_name" AS "customer_last_name", - "cte"."year1" AS "year1", - "cte"."year_total" AS "year_total", - "cte"."sale_type" AS "sale_type" - FROM "cte" AS "cte" - UNION ALL - SELECT - "cte_2"."customer_id" AS "customer_id", - "cte_2"."customer_first_name" AS "customer_first_name", - "cte_2"."customer_last_name" AS "customer_last_name", - "cte_2"."year1" AS "year1", - "cte_2"."year_total" AS "year_total", - "cte_2"."sale_type" AS "sale_type" - FROM "cte_2" AS "cte_2" ) SELECT "t_s_secyear"."customer_id" AS "customer_id", @@ -10044,17 +9779,17 @@ JOIN "year_total" AS "t_w_secyear" AND "t_w_secyear"."sale_type" = 'w' AND "t_w_secyear"."year1" = 2000 JOIN "year_total" AS "t_s_secyear" - ON "t_s_secyear"."customer_id" = "t_s_firstyear"."customer_id" + 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_w_firstyear"."year_total" > 0 - THEN "t_w_secyear"."year_total" / "t_w_firstyear"."year_total" - ELSE NULL - END > CASE WHEN "t_s_firstyear"."year_total" > 0 THEN "t_s_secyear"."year_total" / "t_s_firstyear"."year_total" ELSE NULL + END < CASE + WHEN "t_w_firstyear"."year_total" > 0 + THEN "t_w_secyear"."year_total" / "t_w_firstyear"."year_total" + ELSE NULL END WHERE "t_s_firstyear"."sale_type" = 's' @@ -10177,7 +9912,24 @@ WITH "date_dim_2" AS ( FROM "item" AS "item" WHERE "item"."i_category" = 'Men' -), "cte_4" AS ( +), "sales_detail" AS ( + SELECT + "date_dim"."d_year" AS "d_year", + "item"."i_brand_id" AS "i_brand_id", + "item"."i_class_id" AS "i_class_id", + "item"."i_category_id" AS "i_category_id", + "item"."i_manufact_id" AS "i_manufact_id", + "catalog_sales"."cs_quantity" - COALESCE("catalog_returns"."cr_return_quantity", 0) AS "sales_cnt", + "catalog_sales"."cs_ext_sales_price" - COALESCE("catalog_returns"."cr_return_amount", 0.0) AS "sales_amt" + FROM "catalog_sales" AS "catalog_sales" + LEFT JOIN "catalog_returns" AS "catalog_returns" + ON "catalog_returns"."cr_item_sk" = "catalog_sales"."cs_item_sk" + AND "catalog_returns"."cr_order_number" = "catalog_sales"."cs_order_number" + JOIN "date_dim_2" AS "date_dim" + ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk" + JOIN "item_2" AS "item" + ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk" + UNION SELECT "date_dim"."d_year" AS "d_year", "item"."i_brand_id" AS "i_brand_id", @@ -10192,8 +9944,8 @@ WITH "date_dim_2" AS ( JOIN "item_2" AS "item" ON "item"."i_item_sk" = "store_sales"."ss_item_sk" LEFT JOIN "store_returns" AS "store_returns" - ON "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk" - AND "store_sales"."ss_ticket_number" = "store_returns"."sr_ticket_number" + ON "store_returns"."sr_item_sk" = "store_sales"."ss_item_sk" + AND "store_returns"."sr_ticket_number" = "store_sales"."ss_ticket_number" UNION SELECT "date_dim"."d_year" AS "d_year", @@ -10209,35 +9961,8 @@ WITH "date_dim_2" AS ( JOIN "item_2" AS "item" ON "item"."i_item_sk" = "web_sales"."ws_item_sk" LEFT JOIN "web_returns" AS "web_returns" - ON "web_sales"."ws_item_sk" = "web_returns"."wr_item_sk" - AND "web_sales"."ws_order_number" = "web_returns"."wr_order_number" -), "sales_detail" AS ( - SELECT - "date_dim"."d_year" AS "d_year", - "item"."i_brand_id" AS "i_brand_id", - "item"."i_class_id" AS "i_class_id", - "item"."i_category_id" AS "i_category_id", - "item"."i_manufact_id" AS "i_manufact_id", - "catalog_sales"."cs_quantity" - COALESCE("catalog_returns"."cr_return_quantity", 0) AS "sales_cnt", - "catalog_sales"."cs_ext_sales_price" - COALESCE("catalog_returns"."cr_return_amount", 0.0) AS "sales_amt" - FROM "catalog_sales" AS "catalog_sales" - LEFT JOIN "catalog_returns" AS "catalog_returns" - ON "catalog_sales"."cs_item_sk" = "catalog_returns"."cr_item_sk" - AND "catalog_sales"."cs_order_number" = "catalog_returns"."cr_order_number" - JOIN "date_dim_2" AS "date_dim" - ON "date_dim"."d_date_sk" = "catalog_sales"."cs_sold_date_sk" - JOIN "item_2" AS "item" - ON "item"."i_item_sk" = "catalog_sales"."cs_item_sk" - UNION - SELECT - "cte_4"."d_year" AS "d_year", - "cte_4"."i_brand_id" AS "i_brand_id", - "cte_4"."i_class_id" AS "i_class_id", - "cte_4"."i_category_id" AS "i_category_id", - "cte_4"."i_manufact_id" AS "i_manufact_id", - "cte_4"."sales_cnt" AS "sales_cnt", - "cte_4"."sales_amt" AS "sales_amt" - FROM "cte_4" AS "cte_4" + ON "web_returns"."wr_item_sk" = "web_sales"."ws_item_sk" + AND "web_returns"."wr_order_number" = "web_sales"."ws_order_number" ), "all_sales" AS ( SELECT "sales_detail"."d_year" AS "d_year", @@ -10350,7 +10075,22 @@ WITH "date_dim_2" AS ( "item"."i_item_sk" AS "i_item_sk", "item"."i_category" AS "i_category" FROM "item" AS "item" -), "cte_4" AS ( +), "foo" AS ( + SELECT + 'store' AS "channel", + 'ss_hdemo_sk' AS "col_name", + "date_dim"."d_year" AS "d_year", + "date_dim"."d_qoy" AS "d_qoy", + "item"."i_category" AS "i_category", + "store_sales"."ss_ext_sales_price" AS "ext_sales_price" + FROM "store_sales" AS "store_sales" + JOIN "date_dim_2" AS "date_dim" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" + JOIN "item_2" AS "item" + ON "item"."i_item_sk" = "store_sales"."ss_item_sk" + WHERE + "store_sales"."ss_hdemo_sk" IS NULL + UNION ALL SELECT 'web' AS "channel", 'ws_ship_hdemo_sk' AS "col_name", @@ -10360,9 +10100,9 @@ WITH "date_dim_2" AS ( "web_sales"."ws_ext_sales_price" AS "ext_sales_price" FROM "web_sales" AS "web_sales" JOIN "date_dim_2" AS "date_dim" - ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk" JOIN "item_2" AS "item" - ON "web_sales"."ws_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "web_sales"."ws_item_sk" WHERE "web_sales"."ws_ship_hdemo_sk" IS NULL UNION ALL @@ -10380,30 +10120,6 @@ WITH "date_dim_2" AS ( ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk" WHERE "catalog_sales"."cs_warehouse_sk" IS NULL -), "foo" AS ( - SELECT - 'store' AS "channel", - 'ss_hdemo_sk' AS "col_name", - "date_dim"."d_year" AS "d_year", - "date_dim"."d_qoy" AS "d_qoy", - "item"."i_category" AS "i_category", - "store_sales"."ss_ext_sales_price" AS "ext_sales_price" - FROM "store_sales" AS "store_sales" - JOIN "date_dim_2" AS "date_dim" - ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" - JOIN "item_2" AS "item" - ON "store_sales"."ss_item_sk" = "item"."i_item_sk" - WHERE - "store_sales"."ss_hdemo_sk" IS NULL - UNION ALL - SELECT - "cte_4"."channel" AS "channel", - "cte_4"."col_name" AS "col_name", - "cte_4"."d_year" AS "d_year", - "cte_4"."d_qoy" AS "d_qoy", - "cte_4"."i_category" AS "i_category", - "cte_4"."ext_sales_price" AS "ext_sales_price" - FROM "cte_4" AS "cte_4" ) SELECT "foo"."channel" AS "channel", @@ -10431,7 +10147,6 @@ LIMIT 100; -------------------------------------- -- TPC-DS 77 -------------------------------------- - WITH ss AS ( SELECT s_store_sk, @@ -10556,9 +10271,9 @@ WITH "date_dim_2" AS ( SUM("store_sales"."ss_net_profit") AS "profit" FROM "store_sales" AS "store_sales" JOIN "date_dim_2" AS "date_dim" - ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" JOIN "store_2" AS "store" - ON "store_sales"."ss_store_sk" = "store"."s_store_sk" + ON "store"."s_store_sk" = "store_sales"."ss_store_sk" GROUP BY "store"."s_store_sk" ), "sr" AS ( @@ -10568,9 +10283,9 @@ WITH "date_dim_2" AS ( SUM("store_returns"."sr_net_loss") AS "profit_loss" FROM "store_returns" AS "store_returns" JOIN "date_dim_2" AS "date_dim" - ON "store_returns"."sr_returned_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_returns"."sr_returned_date_sk" JOIN "store_2" AS "store" - ON "store_returns"."sr_store_sk" = "store"."s_store_sk" + ON "store"."s_store_sk" = "store_returns"."sr_store_sk" GROUP BY "store"."s_store_sk" ), "cs" AS ( @@ -10603,9 +10318,9 @@ WITH "date_dim_2" AS ( SUM("web_sales"."ws_net_profit") AS "profit" FROM "web_sales" AS "web_sales" JOIN "date_dim_2" AS "date_dim" - ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk" JOIN "web_page_2" AS "web_page" - ON "web_sales"."ws_web_page_sk" = "web_page"."wp_web_page_sk" + ON "web_page"."wp_web_page_sk" = "web_sales"."ws_web_page_sk" GROUP BY "web_page"."wp_web_page_sk" ), "wr" AS ( @@ -10615,12 +10330,22 @@ WITH "date_dim_2" AS ( SUM("web_returns"."wr_net_loss") AS "profit_loss" FROM "web_returns" AS "web_returns" JOIN "date_dim_2" AS "date_dim" - ON "web_returns"."wr_returned_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "web_returns"."wr_returned_date_sk" JOIN "web_page_2" AS "web_page" - ON "web_returns"."wr_web_page_sk" = "web_page"."wp_web_page_sk" + ON "web_page"."wp_web_page_sk" = "web_returns"."wr_web_page_sk" GROUP BY "web_page"."wp_web_page_sk" -), "cte_4" AS ( +), "x" AS ( + SELECT + 'store channel' AS "channel", + "ss"."s_store_sk" AS "id", + "ss"."sales" AS "sales", + COALESCE("sr"."returns1", 0) AS "returns1", + "ss"."profit" - COALESCE("sr"."profit_loss", 0) AS "profit" + FROM "ss" + LEFT JOIN "sr" + ON "sr"."s_store_sk" = "ss"."s_store_sk" + UNION ALL SELECT 'catalog channel' AS "channel", "cs"."cs_call_center_sk" AS "id", @@ -10638,25 +10363,7 @@ WITH "date_dim_2" AS ( "ws"."profit" - COALESCE("wr"."profit_loss", 0) AS "profit" FROM "ws" LEFT JOIN "wr" - ON "ws"."wp_web_page_sk" = "wr"."wp_web_page_sk" -), "x" AS ( - SELECT - 'store channel' AS "channel", - "ss"."s_store_sk" AS "id", - "ss"."sales" AS "sales", - COALESCE("sr"."returns1", 0) AS "returns1", - "ss"."profit" - COALESCE("sr"."profit_loss", 0) AS "profit" - FROM "ss" - LEFT JOIN "sr" - ON "ss"."s_store_sk" = "sr"."s_store_sk" - UNION ALL - SELECT - "cte_4"."channel" AS "channel", - "cte_4"."id" AS "id", - "cte_4"."sales" AS "sales", - "cte_4"."returns1" AS "returns1", - "cte_4"."profit" AS "profit" - FROM "cte_4" AS "cte_4" + ON "wr"."wp_web_page_sk" = "ws"."wp_web_page_sk" ) SELECT "x"."channel" AS "channel", @@ -10778,10 +10485,10 @@ WITH "date_dim_2" AS ( SUM("web_sales"."ws_sales_price") AS "ws_sp" FROM "web_sales" AS "web_sales" JOIN "date_dim_2" AS "date_dim" - ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk" LEFT JOIN "web_returns" AS "web_returns" - ON "web_returns"."wr_order_number" = "web_sales"."ws_order_number" - AND "web_sales"."ws_item_sk" = "web_returns"."wr_item_sk" + ON "web_returns"."wr_item_sk" = "web_sales"."ws_item_sk" + AND "web_returns"."wr_order_number" = "web_sales"."ws_order_number" WHERE "web_returns"."wr_order_number" IS NULL GROUP BY @@ -10798,8 +10505,8 @@ WITH "date_dim_2" AS ( SUM("catalog_sales"."cs_sales_price") AS "cs_sp" FROM "catalog_sales" AS "catalog_sales" LEFT JOIN "catalog_returns" AS "catalog_returns" - ON "catalog_returns"."cr_order_number" = "catalog_sales"."cs_order_number" - AND "catalog_sales"."cs_item_sk" = "catalog_returns"."cr_item_sk" + ON "catalog_returns"."cr_item_sk" = "catalog_sales"."cs_item_sk" + AND "catalog_returns"."cr_order_number" = "catalog_sales"."cs_order_number" JOIN "date_dim_2" AS "date_dim" ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk" WHERE @@ -10818,10 +10525,10 @@ WITH "date_dim_2" AS ( SUM("store_sales"."ss_sales_price") AS "ss_sp" FROM "store_sales" AS "store_sales" JOIN "date_dim_2" AS "date_dim" - ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" LEFT JOIN "store_returns" AS "store_returns" - ON "store_returns"."sr_ticket_number" = "store_sales"."ss_ticket_number" - AND "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk" + ON "store_returns"."sr_item_sk" = "store_sales"."ss_item_sk" + AND "store_returns"."sr_ticket_number" = "store_sales"."ss_ticket_number" WHERE "store_returns"."sr_ticket_number" IS NULL GROUP BY @@ -10844,9 +10551,9 @@ LEFT JOIN "cs" AND "cs"."cs_item_sk" = "cs"."cs_item_sk" AND "cs"."cs_sold_year" = "ss"."ss_sold_year" LEFT JOIN "ws" - ON "ws"."ws_customer_sk" = "ss"."ss_customer_sk" - AND "ws"."ws_item_sk" = "ss"."ss_item_sk" - AND "ws"."ws_sold_year" = "ss"."ss_sold_year" + ON "ss"."ss_customer_sk" = "ws"."ws_customer_sk" + AND "ss"."ss_item_sk" = "ws"."ws_item_sk" + AND "ss"."ss_sold_year" = "ws"."ws_sold_year" WHERE "cs"."cs_qty" > 0 AND "ss"."ss_sold_year" = 1999 @@ -10910,19 +10617,19 @@ WITH "ms" AS ( SUM("store_sales"."ss_net_profit") AS "profit" FROM "store_sales" AS "store_sales" JOIN "date_dim" AS "date_dim" - ON "date_dim"."d_dow" = 1 + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" + AND "date_dim"."d_dow" = 1 AND "date_dim"."d_year" IN (2000, 2001, 2002) - AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" JOIN "household_demographics" AS "household_demographics" - ON ( + ON "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk" + AND ( "household_demographics"."hd_dep_count" = 8 OR "household_demographics"."hd_vehicle_count" > 4 ) - AND "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk" JOIN "store" AS "store" ON "store"."s_number_employees" <= 295 AND "store"."s_number_employees" >= 200 - AND "store_sales"."ss_store_sk" = "store"."s_store_sk" + AND "store"."s_store_sk" = "store_sales"."ss_store_sk" GROUP BY "store_sales"."ss_ticket_number", "store_sales"."ss_customer_sk", @@ -10938,7 +10645,7 @@ SELECT "ms"."profit" AS "profit" FROM "ms" AS "ms" JOIN "customer" AS "customer" - ON "ms"."ss_customer_sk" = "customer"."c_customer_sk" + ON "customer"."c_customer_sk" = "ms"."ss_customer_sk" ORDER BY "c_last_name", "c_first_name", @@ -11083,16 +10790,16 @@ WITH "date_dim_2" AS ( SUM("store_sales"."ss_net_profit" - COALESCE("store_returns"."sr_net_loss", 0)) AS "profit" FROM "store_sales" AS "store_sales" JOIN "date_dim_2" AS "date_dim" - ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" JOIN "item_2" AS "item" - ON "store_sales"."ss_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "store_sales"."ss_item_sk" JOIN "promotion_2" AS "promotion" - ON "store_sales"."ss_promo_sk" = "promotion"."p_promo_sk" + ON "promotion"."p_promo_sk" = "store_sales"."ss_promo_sk" JOIN "store" AS "store" - ON "store_sales"."ss_store_sk" = "store"."s_store_sk" + ON "store"."s_store_sk" = "store_sales"."ss_store_sk" LEFT JOIN "store_returns" AS "store_returns" - ON "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk" - AND "store_sales"."ss_ticket_number" = "store_returns"."sr_ticket_number" + ON "store_returns"."sr_item_sk" = "store_sales"."ss_item_sk" + AND "store_returns"."sr_ticket_number" = "store_sales"."ss_ticket_number" GROUP BY "store"."s_store_id" ), "csr" AS ( @@ -11103,10 +10810,10 @@ WITH "date_dim_2" AS ( SUM("catalog_sales"."cs_net_profit" - COALESCE("catalog_returns"."cr_net_loss", 0)) AS "profit" FROM "catalog_sales" AS "catalog_sales" JOIN "catalog_page" AS "catalog_page" - ON "catalog_sales"."cs_catalog_page_sk" = "catalog_page"."cp_catalog_page_sk" + ON "catalog_page"."cp_catalog_page_sk" = "catalog_sales"."cs_catalog_page_sk" LEFT JOIN "catalog_returns" AS "catalog_returns" - ON "catalog_sales"."cs_item_sk" = "catalog_returns"."cr_item_sk" - AND "catalog_sales"."cs_order_number" = "catalog_returns"."cr_order_number" + ON "catalog_returns"."cr_item_sk" = "catalog_sales"."cs_item_sk" + AND "catalog_returns"."cr_order_number" = "catalog_sales"."cs_order_number" JOIN "date_dim_2" AS "date_dim" ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk" JOIN "item_2" AS "item" @@ -11123,19 +10830,27 @@ WITH "date_dim_2" AS ( SUM("web_sales"."ws_net_profit" - COALESCE("web_returns"."wr_net_loss", 0)) AS "profit" FROM "web_sales" AS "web_sales" JOIN "date_dim_2" AS "date_dim" - ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk" JOIN "item_2" AS "item" - ON "web_sales"."ws_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "web_sales"."ws_item_sk" JOIN "promotion_2" AS "promotion" - ON "web_sales"."ws_promo_sk" = "promotion"."p_promo_sk" + ON "promotion"."p_promo_sk" = "web_sales"."ws_promo_sk" LEFT JOIN "web_returns" AS "web_returns" - ON "web_sales"."ws_item_sk" = "web_returns"."wr_item_sk" - AND "web_sales"."ws_order_number" = "web_returns"."wr_order_number" + ON "web_returns"."wr_item_sk" = "web_sales"."ws_item_sk" + AND "web_returns"."wr_order_number" = "web_sales"."ws_order_number" JOIN "web_site" AS "web_site" ON "web_sales"."ws_web_site_sk" = "web_site"."web_site_sk" GROUP BY "web_site"."web_site_id" -), "cte_4" AS ( +), "x" AS ( + SELECT + 'store channel' AS "channel", + CONCAT('store', "ssr"."store_id") AS "id", + "ssr"."sales" AS "sales", + "ssr"."returns1" AS "returns1", + "ssr"."profit" AS "profit" + FROM "ssr" + UNION ALL SELECT 'catalog channel' AS "channel", CONCAT('catalog_page', "csr"."catalog_page_id") AS "id", @@ -11151,22 +10866,6 @@ WITH "date_dim_2" AS ( "wsr"."returns1" AS "returns1", "wsr"."profit" AS "profit" FROM "wsr" -), "x" AS ( - SELECT - 'store channel' AS "channel", - CONCAT('store', "ssr"."store_id") AS "id", - "ssr"."sales" AS "sales", - "ssr"."returns1" AS "returns1", - "ssr"."profit" AS "profit" - FROM "ssr" - UNION ALL - SELECT - "cte_4"."channel" AS "channel", - "cte_4"."id" AS "id", - "cte_4"."sales" AS "sales", - "cte_4"."returns1" AS "returns1", - "cte_4"."profit" AS "profit" - FROM "cte_4" AS "cte_4" ) SELECT "x"."channel" AS "channel", @@ -11188,7 +10887,6 @@ LIMIT 100; -------------------------------------- -- TPC-DS 81 -------------------------------------- - WITH customer_total_return AS (SELECT cr_returning_customer_sk AS ctr_customer_sk, ca_state AS ctr_state, @@ -11284,14 +10982,14 @@ SELECT "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" + ON "_u_0"."_u_1" = "ctr1"."ctr_state" 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" + ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk" AND "customer_address"."ca_state" = 'TX' WHERE - "ctr1"."ctr_total_return" > "_u_0"."_col_0" + "_u_0"."_col_0" < "ctr1"."ctr_total_return" ORDER BY "c_customer_id", "c_salutation", @@ -11314,7 +11012,6 @@ LIMIT 100; -------------------------------------- -- TPC-DS 82 -------------------------------------- - SELECT i_item_id , i_item_desc , @@ -11346,7 +11043,7 @@ JOIN "inventory" AS "inventory" AND "inventory"."inv_quantity_on_hand" <= 500 AND "inventory"."inv_quantity_on_hand" >= 100 JOIN "store_sales" AS "store_sales" - ON "store_sales"."ss_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "store_sales"."ss_item_sk" JOIN "date_dim" AS "date_dim" ON "date_dim"."d_date_sk" = "inventory"."inv_date_sk" AND CAST("date_dim"."d_date" AS DATE) <= CAST('1998-06-26' AS DATE) @@ -11463,7 +11160,7 @@ WITH "date_dim_2" AS ( "date_dim"."d_date" AS "d_date" FROM "date_dim" AS "date_dim" LEFT JOIN "_u_0" AS "_u_0" - ON "date_dim"."d_week_seq" = "_u_0"."d_week_seq" + ON "_u_0"."d_week_seq" = "date_dim"."d_week_seq" WHERE NOT "_u_0"."d_week_seq" IS NULL GROUP BY @@ -11474,11 +11171,11 @@ WITH "date_dim_2" AS ( SUM("store_returns"."sr_return_quantity") AS "sr_item_qty" FROM "store_returns" AS "store_returns" JOIN "date_dim_2" AS "date_dim" - ON "store_returns"."sr_returned_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_returns"."sr_returned_date_sk" JOIN "item_2" AS "item" - ON "store_returns"."sr_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "store_returns"."sr_item_sk" LEFT JOIN "_u_1" AS "_u_1" - ON "date_dim"."d_date" = "_u_1"."d_date" + ON "_u_1"."d_date" = "date_dim"."d_date" WHERE NOT "_u_1"."d_date" IS NULL GROUP BY @@ -11488,7 +11185,7 @@ WITH "date_dim_2" AS ( "date_dim"."d_date" AS "d_date" FROM "date_dim" AS "date_dim" LEFT JOIN "_u_0" AS "_u_2" - ON "date_dim"."d_week_seq" = "_u_2"."d_week_seq" + ON "_u_2"."d_week_seq" = "date_dim"."d_week_seq" WHERE NOT "_u_2"."d_week_seq" IS NULL GROUP BY @@ -11503,7 +11200,7 @@ WITH "date_dim_2" AS ( JOIN "item_2" AS "item" ON "catalog_returns"."cr_item_sk" = "item"."i_item_sk" LEFT JOIN "_u_3" AS "_u_3" - ON "date_dim"."d_date" = "_u_3"."d_date" + ON "_u_3"."d_date" = "date_dim"."d_date" WHERE NOT "_u_3"."d_date" IS NULL GROUP BY @@ -11513,7 +11210,7 @@ WITH "date_dim_2" AS ( "date_dim"."d_date" AS "d_date" FROM "date_dim" AS "date_dim" LEFT JOIN "_u_0" AS "_u_4" - ON "date_dim"."d_week_seq" = "_u_4"."d_week_seq" + ON "_u_4"."d_week_seq" = "date_dim"."d_week_seq" WHERE NOT "_u_4"."d_week_seq" IS NULL GROUP BY @@ -11524,11 +11221,11 @@ WITH "date_dim_2" AS ( SUM("web_returns"."wr_return_quantity") AS "wr_item_qty" FROM "web_returns" AS "web_returns" JOIN "date_dim_2" AS "date_dim" - ON "web_returns"."wr_returned_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "web_returns"."wr_returned_date_sk" JOIN "item_2" AS "item" - ON "web_returns"."wr_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "web_returns"."wr_item_sk" LEFT JOIN "_u_5" AS "_u_5" - ON "date_dim"."d_date" = "_u_5"."d_date" + ON "_u_5"."d_date" = "date_dim"."d_date" WHERE NOT "_u_5"."d_date" IS NULL GROUP BY @@ -11553,7 +11250,7 @@ SELECT ) / 3.0 AS "average" FROM "sr_items" JOIN "cr_items" - ON "sr_items"."item_id" = "cr_items"."item_id" + ON "cr_items"."item_id" = "sr_items"."item_id" JOIN "wr_items" ON "sr_items"."item_id" = "wr_items"."item_id" ORDER BY @@ -11592,15 +11289,15 @@ JOIN "customer_address" AS "customer_address" ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk" AND "customer_address"."ca_city" = 'Green Acres' JOIN "customer_demographics" AS "customer_demographics" - ON "customer_demographics"."cd_demo_sk" = "customer"."c_current_cdemo_sk" + ON "customer"."c_current_cdemo_sk" = "customer_demographics"."cd_demo_sk" JOIN "household_demographics" AS "household_demographics" - ON "household_demographics"."hd_demo_sk" = "customer"."c_current_hdemo_sk" + ON "customer"."c_current_hdemo_sk" = "household_demographics"."hd_demo_sk" JOIN "income_band" AS "income_band" - ON "income_band"."ib_income_band_sk" = "household_demographics"."hd_income_band_sk" + ON "household_demographics"."hd_income_band_sk" = "income_band"."ib_income_band_sk" AND "income_band"."ib_lower_bound" >= 54986 AND "income_band"."ib_upper_bound" <= 104986 JOIN "store_returns" AS "store_returns" - ON "store_returns"."sr_cdemo_sk" = "customer_demographics"."cd_demo_sk" + ON "customer_demographics"."cd_demo_sk" = "store_returns"."sr_cdemo_sk" ORDER BY "customer"."c_customer_id" LIMIT 100; @@ -11666,12 +11363,12 @@ SELECT AVG("web_returns"."wr_fee") AS "_col_3" FROM "web_sales" AS "web_sales" JOIN "date_dim" AS "date_dim" - ON "date_dim"."d_year" = 2001 AND "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk" AND "date_dim"."d_year" = 2001 JOIN "web_page" AS "web_page" - ON "web_sales"."ws_web_page_sk" = "web_page"."wp_web_page_sk" + ON "web_page"."wp_web_page_sk" = "web_sales"."ws_web_page_sk" JOIN "web_returns" AS "web_returns" - ON "web_sales"."ws_item_sk" = "web_returns"."wr_item_sk" - AND "web_sales"."ws_order_number" = "web_returns"."wr_order_number" + 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_address" AS "customer_address" @@ -11806,8 +11503,7 @@ from ((select distinct c_last_name, c_first_name, d_date where web_sales.ws_sold_date_sk = date_dim.d_date_sk and web_sales.ws_bill_customer_sk = customer.c_customer_sk and d_month_seq between 1188 and 1188+11) -) cool_cust -; +) cool_cust; WITH "customer_2" AS ( SELECT "customer"."c_customer_sk" AS "c_customer_sk", @@ -11822,66 +11518,42 @@ WITH "customer_2" AS ( FROM "date_dim" AS "date_dim" WHERE "date_dim"."d_month_seq" <= 1199 AND "date_dim"."d_month_seq" >= 1188 -), "cte" AS ( - SELECT DISTINCT - "customer"."c_last_name" AS "c_last_name", - "customer"."c_first_name" AS "c_first_name", - "date_dim"."d_date" AS "d_date" - FROM "store_sales" AS "store_sales" - JOIN "customer_2" AS "customer" - ON "store_sales"."ss_customer_sk" = "customer"."c_customer_sk" - JOIN "date_dim_2" AS "date_dim" - ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" -), "cte_2" AS ( - SELECT DISTINCT - "customer"."c_last_name" AS "c_last_name", - "customer"."c_first_name" AS "c_first_name", - "date_dim"."d_date" AS "d_date" - FROM "catalog_sales" AS "catalog_sales" - JOIN "customer_2" AS "customer" - ON "catalog_sales"."cs_bill_customer_sk" = "customer"."c_customer_sk" - JOIN "date_dim_2" AS "date_dim" - ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk" -), "cte_3" AS ( - SELECT DISTINCT - "customer"."c_last_name" AS "c_last_name", - "customer"."c_first_name" AS "c_first_name", - "date_dim"."d_date" AS "d_date" - FROM "web_sales" AS "web_sales" - JOIN "customer_2" AS "customer" - ON "web_sales"."ws_bill_customer_sk" = "customer"."c_customer_sk" - JOIN "date_dim_2" AS "date_dim" - ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk" -), "cte_4" AS ( +), "cool_cust" AS ( ( - SELECT - "cte_2"."c_last_name" AS "c_last_name", - "cte_2"."c_first_name" AS "c_first_name", - "cte_2"."d_date" AS "d_date" - FROM "cte_2" AS "cte_2" + SELECT DISTINCT + "customer"."c_last_name" AS "c_last_name", + "customer"."c_first_name" AS "c_first_name", + "date_dim"."d_date" AS "d_date" + FROM "store_sales" AS "store_sales" + JOIN "customer_2" AS "customer" + ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk" + JOIN "date_dim_2" AS "date_dim" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" ) EXCEPT ( - SELECT - "cte_3"."c_last_name" AS "c_last_name", - "cte_3"."c_first_name" AS "c_first_name", - "cte_3"."d_date" AS "d_date" - FROM "cte_3" AS "cte_3" + SELECT DISTINCT + "customer"."c_last_name" AS "c_last_name", + "customer"."c_first_name" AS "c_first_name", + "date_dim"."d_date" AS "d_date" + FROM "catalog_sales" AS "catalog_sales" + JOIN "customer_2" AS "customer" + ON "catalog_sales"."cs_bill_customer_sk" = "customer"."c_customer_sk" + JOIN "date_dim_2" AS "date_dim" + ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk" ) -), "cool_cust" AS ( + EXCEPT ( - SELECT - "cte"."c_last_name" AS "c_last_name", - "cte"."c_first_name" AS "c_first_name", - "cte"."d_date" AS "d_date" - FROM "cte" AS "cte" + SELECT DISTINCT + "customer"."c_last_name" AS "c_last_name", + "customer"."c_first_name" AS "c_first_name", + "date_dim"."d_date" AS "d_date" + FROM "web_sales" AS "web_sales" + JOIN "customer_2" AS "customer" + ON "customer"."c_customer_sk" = "web_sales"."ws_bill_customer_sk" + JOIN "date_dim_2" AS "date_dim" + ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk" ) - EXCEPT - SELECT - "cte_4"."c_last_name" AS "c_last_name", - "cte_4"."c_first_name" AS "c_first_name", - "cte_4"."d_date" AS "d_date" - FROM "cte_4" AS "cte_4" ) SELECT COUNT(*) AS "_col_0" @@ -11979,8 +11651,7 @@ from and ((household_demographics.hd_dep_count = -1 and household_demographics.hd_vehicle_count<=-1+2) or (household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2) or (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2)) - and store.s_store_name = 'ese') s8 -; + and store.s_store_name = 'ese') s8; WITH "store_sales_2" AS ( SELECT "store_sales"."ss_sold_time_sk" AS "ss_sold_time_sk", @@ -12021,9 +11692,9 @@ WITH "store_sales_2" AS ( COUNT(*) AS "h8_30_to_9" 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" + ON "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk" JOIN "store_2" AS "store" - ON "store_sales"."ss_store_sk" = "store"."s_store_sk" + ON "store"."s_store_sk" = "store_sales"."ss_store_sk" JOIN "time_dim" AS "time_dim" ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk" AND "time_dim"."t_hour" = 8 @@ -12033,9 +11704,9 @@ WITH "store_sales_2" AS ( COUNT(*) AS "h9_to_9_30" 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" + ON "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk" JOIN "store_2" AS "store" - ON "store_sales"."ss_store_sk" = "store"."s_store_sk" + ON "store"."s_store_sk" = "store_sales"."ss_store_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 @@ -12045,9 +11716,9 @@ WITH "store_sales_2" AS ( COUNT(*) AS "h9_30_to_10" 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" + ON "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk" JOIN "store_2" AS "store" - ON "store_sales"."ss_store_sk" = "store"."s_store_sk" + ON "store"."s_store_sk" = "store_sales"."ss_store_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 @@ -12057,9 +11728,9 @@ WITH "store_sales_2" AS ( COUNT(*) AS "h10_to_10_30" 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" + ON "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk" JOIN "store_2" AS "store" - ON "store_sales"."ss_store_sk" = "store"."s_store_sk" + ON "store"."s_store_sk" = "store_sales"."ss_store_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 @@ -12069,9 +11740,9 @@ WITH "store_sales_2" AS ( COUNT(*) AS "h10_30_to_11" 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" + ON "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk" JOIN "store_2" AS "store" - ON "store_sales"."ss_store_sk" = "store"."s_store_sk" + ON "store"."s_store_sk" = "store_sales"."ss_store_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 @@ -12081,9 +11752,9 @@ WITH "store_sales_2" AS ( COUNT(*) AS "h11_to_11_30" 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" + ON "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk" JOIN "store_2" AS "store" - ON "store_sales"."ss_store_sk" = "store"."s_store_sk" + ON "store"."s_store_sk" = "store_sales"."ss_store_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 @@ -12093,9 +11764,9 @@ WITH "store_sales_2" AS ( COUNT(*) AS "h11_30_to_12" 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" + ON "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk" JOIN "store_2" AS "store" - ON "store_sales"."ss_store_sk" = "store"."s_store_sk" + ON "store"."s_store_sk" = "store_sales"."ss_store_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 @@ -12105,9 +11776,9 @@ WITH "store_sales_2" AS ( COUNT(*) AS "h12_to_12_30" 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" + ON "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk" JOIN "store_2" AS "store" - ON "store_sales"."ss_store_sk" = "store"."s_store_sk" + ON "store"."s_store_sk" = "store_sales"."ss_store_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 @@ -12185,12 +11856,12 @@ WITH "tmp1" AS ( AVG(SUM("store_sales"."ss_sales_price")) OVER (PARTITION BY "item"."i_category", "item"."i_brand", "store"."s_store_name", "store"."s_company_name") AS "avg_monthly_sales" FROM "item" AS "item" JOIN "store_sales" AS "store_sales" - ON "store_sales"."ss_item_sk" = "item"."i_item_sk" + ON "item"."i_item_sk" = "store_sales"."ss_item_sk" JOIN "date_dim" AS "date_dim" - ON "date_dim"."d_year" IN (2002) - AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" + AND "date_dim"."d_year" IN (2002) JOIN "store" AS "store" - ON "store_sales"."ss_store_sk" = "store"."s_store_sk" + ON "store"."s_store_sk" = "store_sales"."ss_store_sk" WHERE ( "item"."i_category" IN ('Home', 'Men', 'Sports') @@ -12242,7 +11913,6 @@ LIMIT 100; -------------------------------------- -- TPC-DS 90 -------------------------------------- - SELECT Cast(amc AS DECIMAL(15, 4)) / Cast(pmc AS DECIMAL(15, 4)) am_pm_ratio FROM (SELECT Count(*) amc @@ -12294,25 +11964,25 @@ WITH "web_sales_2" AS ( COUNT(*) AS "amc" 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" + ON "household_demographics"."hd_demo_sk" = "web_sales"."ws_ship_hdemo_sk" JOIN "time_dim" AS "time_dim" ON "time_dim"."t_hour" <= 13 AND "time_dim"."t_hour" >= 12 - AND "web_sales"."ws_sold_time_sk" = "time_dim"."t_time_sk" + AND "time_dim"."t_time_sk" = "web_sales"."ws_sold_time_sk" JOIN "web_page_2" AS "web_page" - ON "web_sales"."ws_web_page_sk" = "web_page"."wp_web_page_sk" + ON "web_page"."wp_web_page_sk" = "web_sales"."ws_web_page_sk" ), "pt" AS ( SELECT COUNT(*) AS "pmc" 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" + ON "household_demographics"."hd_demo_sk" = "web_sales"."ws_ship_hdemo_sk" JOIN "time_dim" AS "time_dim" ON "time_dim"."t_hour" <= 21 AND "time_dim"."t_hour" >= 20 - AND "web_sales"."ws_sold_time_sk" = "time_dim"."t_time_sk" + AND "time_dim"."t_time_sk" = "web_sales"."ws_sold_time_sk" JOIN "web_page_2" AS "web_page" - ON "web_sales"."ws_web_page_sk" = "web_page"."wp_web_page_sk" + ON "web_page"."wp_web_page_sk" = "web_sales"."ws_web_page_sk" ) SELECT CAST("at1"."amc" AS DECIMAL(15, 4)) / CAST("pt"."pmc" AS DECIMAL(15, 4)) AS "am_pm_ratio" @@ -12365,15 +12035,15 @@ 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 "household_demographics"."hd_demo_sk" = "customer"."c_current_hdemo_sk" + ON "customer"."c_current_hdemo_sk" = "household_demographics"."hd_demo_sk" JOIN "catalog_returns" AS "catalog_returns" - ON "catalog_returns"."cr_call_center_sk" = "call_center"."cc_call_center_sk" + 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_address" AS "customer_address" - ON "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk" + ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk" AND "customer_address"."ca_gmt_offset" = -7 JOIN "customer_demographics" AS "customer_demographics" - ON "customer_demographics"."cd_demo_sk" = "customer"."c_current_cdemo_sk" + ON "customer"."c_current_cdemo_sk" = "customer_demographics"."cd_demo_sk" AND ( "customer_demographics"."cd_education_status" = 'Advanced Degree' OR "customer_demographics"."cd_education_status" = 'Unknown' @@ -12461,7 +12131,7 @@ JOIN "item" AS "item" 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" + "_u_0"."_col_0" < "web_sales"."ws_ext_discount_amt" ORDER BY SUM("web_sales"."ws_ext_discount_amt") LIMIT 100; @@ -12510,7 +12180,7 @@ LEFT 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" WHERE - "store_returns"."sr_reason_sk" = "reason"."r_reason_sk" + "reason"."r_reason_sk" = "store_returns"."sr_reason_sk" GROUP BY "store_sales"."ss_customer_sk" ORDER BY @@ -12569,21 +12239,21 @@ SELECT SUM("ws1"."ws_net_profit") AS "total net profit" FROM "web_sales" AS "ws1" LEFT JOIN "_u_0" AS "_u_0" - ON "ws1"."ws_order_number" = "_u_0"."_u_1" + ON "_u_0"."_u_1" = "ws1"."ws_order_number" LEFT JOIN "_u_3" AS "_u_3" - ON "ws1"."ws_order_number" = "_u_3"."_u_4" + ON "_u_3"."_u_4" = "ws1"."ws_order_number" JOIN "customer_address" AS "customer_address" - ON "customer_address"."ca_state" = 'MT' - AND "ws1"."ws_ship_addr_sk" = "customer_address"."ca_address_sk" + ON "customer_address"."ca_address_sk" = "ws1"."ws_ship_addr_sk" + AND "customer_address"."ca_state" = 'MT' JOIN "date_dim" AS "date_dim" ON "date_dim"."d_date" >= '2000-3-01' - AND "ws1"."ws_ship_date_sk" = "date_dim"."d_date_sk" - AND CAST("date_dim"."d_date" AS DATE) <= ( + AND "date_dim"."d_date_sk" = "ws1"."ws_ship_date_sk" + AND ( 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' - AND "ws1"."ws_web_site_sk" = "web_site"."web_site_sk" + AND "web_site"."web_site_sk" = "ws1"."ws_web_site_sk" WHERE "_u_3"."_u_4" IS NULL AND NOT "_u_0"."_u_1" IS NULL @@ -12659,21 +12329,21 @@ SELECT SUM("ws1"."ws_net_profit") AS "total net profit" FROM "web_sales" AS "ws1" LEFT JOIN "_u_0" AS "_u_0" - ON "ws1"."ws_order_number" = "_u_0"."ws_order_number" + ON "_u_0"."ws_order_number" = "ws1"."ws_order_number" LEFT JOIN "_u_1" AS "_u_1" - ON "ws1"."ws_order_number" = "_u_1"."wr_order_number" + ON "_u_1"."wr_order_number" = "ws1"."ws_order_number" JOIN "customer_address" AS "customer_address" - ON "customer_address"."ca_state" = 'IN' - AND "ws1"."ws_ship_addr_sk" = "customer_address"."ca_address_sk" + ON "customer_address"."ca_address_sk" = "ws1"."ws_ship_addr_sk" + AND "customer_address"."ca_state" = 'IN' JOIN "date_dim" AS "date_dim" ON "date_dim"."d_date" >= '2000-4-01' - AND "ws1"."ws_ship_date_sk" = "date_dim"."d_date_sk" - AND CAST("date_dim"."d_date" AS DATE) <= ( + AND "date_dim"."d_date_sk" = "ws1"."ws_ship_date_sk" + AND ( 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' - AND "ws1"."ws_web_site_sk" = "web_site"."web_site_sk" + AND "web_site"."web_site_sk" = "ws1"."ws_web_site_sk" WHERE NOT "_u_0"."ws_order_number" IS NULL AND NOT "_u_1"."wr_order_number" IS NULL ORDER BY @@ -12701,10 +12371,10 @@ SELECT COUNT(*) AS "_col_0" FROM "store_sales" AS "store_sales" JOIN "household_demographics" AS "household_demographics" - ON "household_demographics"."hd_dep_count" = 7 - AND "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk" + ON "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk" + AND "household_demographics"."hd_dep_count" = 7 JOIN "store" AS "store" - ON "store"."s_store_name" = 'ese' AND "store_sales"."ss_store_sk" = "store"."s_store_sk" + ON "store"."s_store_name" = 'ese' AND "store"."s_store_sk" = "store_sales"."ss_store_sk" JOIN "time_dim" AS "time_dim" ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk" AND "time_dim"."t_hour" = 15 @@ -12716,7 +12386,6 @@ LIMIT 100; -------------------------------------- -- TPC-DS 97 -------------------------------------- - WITH ssci AS (SELECT ss_customer_sk customer_sk, ss_item_sk item_sk @@ -12768,7 +12437,7 @@ WITH "date_dim_2" AS ( "store_sales"."ss_item_sk" AS "item_sk" FROM "store_sales" AS "store_sales" JOIN "date_dim_2" AS "date_dim" - ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" GROUP BY "store_sales"."ss_customer_sk", "store_sales"."ss_item_sk" @@ -12807,13 +12476,12 @@ SELECT ) AS "store_and_catalog" FROM "ssci" FULL JOIN "csci" - ON "ssci"."customer_sk" = "csci"."customer_sk" AND "ssci"."item_sk" = "csci"."item_sk" + ON "csci"."customer_sk" = "ssci"."customer_sk" AND "csci"."item_sk" = "ssci"."item_sk" LIMIT 100; -------------------------------------- -- TPC-DS 98 -------------------------------------- - SELECT i_item_id, i_item_desc, i_category, @@ -12851,12 +12519,12 @@ SELECT SUM("store_sales"."ss_ext_sales_price") * 100 / SUM(SUM("store_sales"."ss_ext_sales_price")) OVER (PARTITION BY "item"."i_class") AS "revenueratio" FROM "store_sales" AS "store_sales" JOIN "date_dim" AS "date_dim" - ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk" + ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk" AND CAST("date_dim"."d_date" AS DATE) <= CAST('2000-06-17' AS DATE) AND CAST("date_dim"."d_date" AS DATE) >= CAST('2000-05-18' AS DATE) JOIN "item" AS "item" ON "item"."i_category" IN ('Men', 'Home', 'Electronics') - AND "store_sales"."ss_item_sk" = "item"."i_item_sk" + AND "item"."i_item_sk" = "store_sales"."ss_item_sk" GROUP BY "item"."i_item_id", "item"."i_item_desc", @@ -12873,8 +12541,6 @@ ORDER BY -------------------------------------- -- TPC-DS 99 -------------------------------------- - - SELECT Substr(w_warehouse_name, 1, 20), sm_type, cc_name, @@ -12963,7 +12629,7 @@ SELECT ) AS ">120 days" FROM "catalog_sales" AS "catalog_sales" JOIN "call_center" AS "call_center" - ON "catalog_sales"."cs_call_center_sk" = "call_center"."cc_call_center_sk" + ON "call_center"."cc_call_center_sk" = "catalog_sales"."cs_call_center_sk" JOIN "date_dim" AS "date_dim" ON "catalog_sales"."cs_ship_date_sk" = "date_dim"."d_date_sk" AND "date_dim"."d_month_seq" <= 1211 diff --git a/tests/fixtures/optimizer/tpc-h/tpc-h.sql b/tests/fixtures/optimizer/tpc-h/tpc-h.sql index 14d8b53..660b565 100644 --- a/tests/fixtures/optimizer/tpc-h/tpc-h.sql +++ b/tests/fixtures/optimizer/tpc-h/tpc-h.sql @@ -118,9 +118,9 @@ WITH "region_2" AS ( "partsupp"."ps_partkey" AS "_u_1" FROM "partsupp_2" AS "partsupp" JOIN "supplier" AS "supplier" - ON "supplier"."s_suppkey" = "partsupp"."ps_suppkey" + ON "partsupp"."ps_suppkey" = "supplier"."s_suppkey" JOIN "nation" AS "nation" - ON "supplier"."s_nationkey" = "nation"."n_nationkey" + ON "nation"."n_nationkey" = "supplier"."s_nationkey" JOIN "region_2" AS "region" ON "nation"."n_regionkey" = "region"."r_regionkey" GROUP BY @@ -138,18 +138,18 @@ SELECT FROM "part" AS "part" CROSS JOIN "region_2" AS "region" LEFT JOIN "_u_0" AS "_u_0" - ON "part"."p_partkey" = "_u_0"."_u_1" + 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 "supplier"."s_nationkey" = "nation"."n_nationkey" - AND "supplier"."s_suppkey" = "partsupp"."ps_suppkey" + ON "nation"."n_nationkey" = "supplier"."s_nationkey" + AND "partsupp"."ps_suppkey" = "supplier"."s_suppkey" WHERE - "part"."p_size" = 15 + "_u_0"."_col_0" = "partsupp"."ps_supplycost" + AND "part"."p_size" = 15 AND "part"."p_type" LIKE '%BRASS' - AND "partsupp"."ps_supplycost" = "_u_0"."_col_0" ORDER BY "s_acctbal" DESC, "n_name", @@ -300,7 +300,7 @@ JOIN "lineitem" AS "lineitem" ON "lineitem"."l_orderkey" = "orders"."o_orderkey" AND "lineitem"."l_suppkey" = "supplier"."s_suppkey" JOIN "nation" AS "nation" - ON "supplier"."s_nationkey" = "nation"."n_nationkey" + ON "nation"."n_nationkey" = "supplier"."s_nationkey" JOIN "region" AS "region" ON "nation"."n_regionkey" = "region"."r_regionkey" AND "region"."r_name" = 'ASIA' GROUP BY @@ -381,14 +381,14 @@ SELECT )) AS "revenue" FROM "supplier" AS "supplier" JOIN "lineitem" AS "lineitem" - ON "supplier"."s_suppkey" = "lineitem"."l_suppkey" + ON "lineitem"."l_suppkey" = "supplier"."s_suppkey" AND CAST("lineitem"."l_shipdate" AS DATE) <= CAST('1996-12-31' AS DATE) AND CAST("lineitem"."l_shipdate" AS DATE) >= CAST('1995-01-01' AS DATE) JOIN "nation" AS "n1" ON ( "n1"."n_name" = 'FRANCE' OR "n1"."n_name" = 'GERMANY' ) - AND "supplier"."s_nationkey" = "n1"."n_nationkey" + AND "n1"."n_nationkey" = "supplier"."s_nationkey" JOIN "nation" AS "n2" ON ( "n1"."n_name" = 'FRANCE' OR "n2"."n_name" = 'FRANCE' @@ -403,7 +403,7 @@ JOIN "customer" AS "customer" ON "customer"."c_nationkey" = "n2"."n_nationkey" JOIN "orders" AS "orders" ON "customer"."c_custkey" = "orders"."o_custkey" - AND "orders"."o_orderkey" = "lineitem"."l_orderkey" + AND "lineitem"."l_orderkey" = "orders"."o_orderkey" GROUP BY "n1"."n_name", "n2"."n_name", @@ -470,18 +470,18 @@ FROM "part" AS "part" JOIN "region" AS "region" ON "region"."r_name" = 'AMERICA' JOIN "lineitem" AS "lineitem" - ON "part"."p_partkey" = "lineitem"."l_partkey" + 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 "supplier" AS "supplier" - ON "supplier"."s_suppkey" = "lineitem"."l_suppkey" + ON "lineitem"."l_suppkey" = "supplier"."s_suppkey" JOIN "nation" AS "n2" - ON "supplier"."s_nationkey" = "n2"."n_nationkey" + ON "n2"."n_nationkey" = "supplier"."s_nationkey" JOIN "orders" AS "orders" - ON "lineitem"."l_orderkey" = "orders"."o_orderkey" - AND "orders"."o_custkey" = "customer"."c_custkey" + 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) WHERE @@ -536,16 +536,16 @@ SELECT ) AS "sum_profit" FROM "part" AS "part" JOIN "lineitem" AS "lineitem" - ON "part"."p_partkey" = "lineitem"."l_partkey" + ON "lineitem"."l_partkey" = "part"."p_partkey" JOIN "orders" AS "orders" - ON "orders"."o_orderkey" = "lineitem"."l_orderkey" + ON "lineitem"."l_orderkey" = "orders"."o_orderkey" JOIN "partsupp" AS "partsupp" - ON "partsupp"."ps_partkey" = "lineitem"."l_partkey" - AND "partsupp"."ps_suppkey" = "lineitem"."l_suppkey" + ON "lineitem"."l_partkey" = "partsupp"."ps_partkey" + AND "lineitem"."l_suppkey" = "partsupp"."ps_suppkey" JOIN "supplier" AS "supplier" - ON "supplier"."s_suppkey" = "lineitem"."l_suppkey" + ON "lineitem"."l_suppkey" = "supplier"."s_suppkey" JOIN "nation" AS "nation" - ON "supplier"."s_nationkey" = "nation"."n_nationkey" + ON "nation"."n_nationkey" = "supplier"."s_nationkey" WHERE "part"."p_name" LIKE '%green%' GROUP BY @@ -672,7 +672,7 @@ WITH "supplier_2" AS ( JOIN "supplier_2" AS "supplier" ON "partsupp"."ps_suppkey" = "supplier"."s_suppkey" JOIN "nation_2" AS "nation" - ON "supplier"."s_nationkey" = "nation"."n_nationkey" + ON "nation"."n_nationkey" = "supplier"."s_nationkey" ) SELECT "partsupp"."ps_partkey" AS "ps_partkey", @@ -682,11 +682,11 @@ 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" + ON "nation"."n_nationkey" = "supplier"."s_nationkey" GROUP BY "partsupp"."ps_partkey" HAVING - SUM("partsupp"."ps_supplycost" * "partsupp"."ps_availqty") > MAX("_u_0"."_col_0") + MAX("_u_0"."_col_0") < SUM("partsupp"."ps_supplycost" * "partsupp"."ps_availqty") ORDER BY "value" DESC; @@ -740,9 +740,9 @@ SELECT FROM "orders" AS "orders" JOIN "lineitem" AS "lineitem" ON "lineitem"."l_commitdate" < "lineitem"."l_receiptdate" - AND "lineitem"."l_shipdate" < "lineitem"."l_commitdate" + AND "lineitem"."l_commitdate" > "lineitem"."l_shipdate" + AND "lineitem"."l_orderkey" = "orders"."o_orderkey" AND "lineitem"."l_shipmode" IN ('MAIL', 'SHIP') - AND "orders"."o_orderkey" = "lineitem"."l_orderkey" AND CAST("lineitem"."l_receiptdate" AS DATE) < CAST('1995-01-01' AS DATE) AND CAST("lineitem"."l_receiptdate" AS DATE) >= CAST('1994-01-01' AS DATE) GROUP BY @@ -893,9 +893,9 @@ SELECT "revenue"."total_revenue" AS "total_revenue" FROM "supplier" AS "supplier" JOIN "revenue" - ON "supplier"."s_suppkey" = "revenue"."supplier_no" + ON "revenue"."supplier_no" = "supplier"."s_suppkey" JOIN "_u_0" AS "_u_0" - ON "revenue"."total_revenue" = "_u_0"."_col_0" + ON "_u_0"."_col_0" = "revenue"."total_revenue" ORDER BY "s_suppkey"; @@ -948,7 +948,7 @@ SELECT 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" + ON "_u_0"."s_suppkey" = "partsupp"."ps_suppkey" JOIN "part" AS "part" ON "part"."p_brand" <> 'Brand#45' AND "part"."p_partkey" = "partsupp"."ps_partkey" @@ -998,13 +998,13 @@ SELECT SUM("lineitem"."l_extendedprice") / 7.0 AS "avg_yearly" FROM "lineitem" AS "lineitem" JOIN "part" AS "part" - ON "part"."p_brand" = 'Brand#23' + ON "lineitem"."l_partkey" = "part"."p_partkey" + AND "part"."p_brand" = 'Brand#23' AND "part"."p_container" = 'MED BOX' - AND "part"."p_partkey" = "lineitem"."l_partkey" LEFT JOIN "_u_0" AS "_u_0" ON "_u_0"."_u_1" = "part"."p_partkey" WHERE - "lineitem"."l_quantity" < "_u_0"."_col_0"; + "_u_0"."_col_0" > "lineitem"."l_quantity"; -------------------------------------- -- TPC-H 18 @@ -1064,9 +1064,9 @@ 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" + ON "_u_0"."l_orderkey" = "orders"."o_orderkey" JOIN "lineitem" AS "lineitem" - ON "orders"."o_orderkey" = "lineitem"."l_orderkey" + ON "lineitem"."l_orderkey" = "orders"."o_orderkey" WHERE NOT "_u_0"."l_orderkey" IS NULL GROUP BY @@ -1125,57 +1125,57 @@ SELECT FROM "lineitem" AS "lineitem" JOIN "part" AS "part" ON ( - "part"."p_brand" = 'Brand#12' + "lineitem"."l_partkey" = "part"."p_partkey" + AND "part"."p_brand" = 'Brand#12' AND "part"."p_container" IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') - AND "part"."p_partkey" = "lineitem"."l_partkey" AND "part"."p_size" <= 5 AND "part"."p_size" >= 1 ) OR ( - "part"."p_brand" = 'Brand#23' + "lineitem"."l_partkey" = "part"."p_partkey" + AND "part"."p_brand" = 'Brand#23' AND "part"."p_container" IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') - AND "part"."p_partkey" = "lineitem"."l_partkey" AND "part"."p_size" <= 10 AND "part"."p_size" >= 1 ) OR ( - "part"."p_brand" = 'Brand#34' + "lineitem"."l_partkey" = "part"."p_partkey" + AND "part"."p_brand" = 'Brand#34' AND "part"."p_container" IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') - AND "part"."p_partkey" = "lineitem"."l_partkey" AND "part"."p_size" <= 15 AND "part"."p_size" >= 1 ) WHERE ( - "lineitem"."l_quantity" <= 11 + "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_partkey" = "lineitem"."l_partkey" AND "part"."p_size" <= 5 AND "part"."p_size" >= 1 ) OR ( - "lineitem"."l_quantity" <= 20 + "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_partkey" = "lineitem"."l_partkey" AND "part"."p_size" <= 10 AND "part"."p_size" >= 1 ) OR ( - "lineitem"."l_quantity" <= 30 + "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_partkey" = "lineitem"."l_partkey" AND "part"."p_size" <= 15 AND "part"."p_size" >= 1 ); @@ -1245,11 +1245,11 @@ WITH "_u_0" AS ( "partsupp"."ps_suppkey" AS "ps_suppkey" FROM "partsupp" AS "partsupp" LEFT JOIN "_u_0" AS "_u_0" - ON "partsupp"."ps_partkey" = "_u_0"."p_partkey" + ON "_u_0"."p_partkey" = "partsupp"."ps_partkey" LEFT JOIN "_u_1" AS "_u_1" ON "_u_1"."_u_2" = "partsupp"."ps_partkey" AND "_u_1"."_u_3" = "partsupp"."ps_suppkey" WHERE - "partsupp"."ps_availqty" > "_u_1"."_col_0" AND NOT "_u_0"."p_partkey" IS NULL + "_u_1"."_col_0" < "partsupp"."ps_availqty" AND NOT "_u_0"."p_partkey" IS NULL GROUP BY "partsupp"."ps_suppkey" ) @@ -1258,9 +1258,9 @@ SELECT "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" + ON "_u_4"."ps_suppkey" = "supplier"."s_suppkey" JOIN "nation" AS "nation" - ON "nation"."n_name" = 'CANADA' AND "supplier"."s_nationkey" = "nation"."n_nationkey" + ON "nation"."n_name" = 'CANADA' AND "nation"."n_nationkey" = "supplier"."s_nationkey" WHERE NOT "_u_4"."ps_suppkey" IS NULL ORDER BY @@ -1323,7 +1323,7 @@ WITH "_u_0" AS ( ARRAY_AGG("l3"."l_suppkey") AS "_u_3" FROM "lineitem" AS "l3" WHERE - "l3"."l_receiptdate" > "l3"."l_commitdate" + "l3"."l_commitdate" < "l3"."l_receiptdate" GROUP BY "l3"."l_orderkey" ) @@ -1332,24 +1332,24 @@ SELECT COUNT(*) AS "numwait" FROM "supplier" AS "supplier" JOIN "lineitem" AS "l1" - ON "l1"."l_receiptdate" > "l1"."l_commitdate" - AND "supplier"."s_suppkey" = "l1"."l_suppkey" + ON "l1"."l_commitdate" < "l1"."l_receiptdate" + AND "l1"."l_suppkey" = "supplier"."s_suppkey" JOIN "nation" AS "nation" ON "nation"."n_name" = 'SAUDI ARABIA' - AND "supplier"."s_nationkey" = "nation"."n_nationkey" + AND "nation"."n_nationkey" = "supplier"."s_nationkey" LEFT JOIN "_u_0" AS "_u_0" ON "_u_0"."l_orderkey" = "l1"."l_orderkey" LEFT JOIN "_u_2" AS "_u_2" ON "_u_2"."l_orderkey" = "l1"."l_orderkey" JOIN "orders" AS "orders" - ON "orders"."o_orderkey" = "l1"."l_orderkey" AND "orders"."o_orderstatus" = 'F' + ON "l1"."l_orderkey" = "orders"."o_orderkey" AND "orders"."o_orderstatus" = 'F' WHERE ( "_u_2"."l_orderkey" IS NULL - OR NOT ARRAY_ANY("_u_2"."_u_3", "_x" -> "_x" <> "l1"."l_suppkey") + OR NOT ARRAY_ANY("_u_2"."_u_3", "_x" -> "l1"."l_suppkey" <> "_x") ) AND NOT "_u_0"."l_orderkey" IS NULL - AND ARRAY_ANY("_u_0"."_u_1", "_x" -> "_x" <> "l1"."l_suppkey") + AND ARRAY_ANY("_u_0"."_u_1", "_x" -> "l1"."l_suppkey" <> "_x") GROUP BY "supplier"."s_name" ORDER BY @@ -1417,7 +1417,7 @@ SELECT SUM("customer"."c_acctbal") AS "totacctbal" FROM "customer" AS "customer" JOIN "_u_0" AS "_u_0" - ON "customer"."c_acctbal" > "_u_0"."_col_0" + ON "_u_0"."_col_0" < "customer"."c_acctbal" LEFT JOIN "_u_1" AS "_u_1" ON "_u_1"."_u_2" = "customer"."c_custkey" WHERE diff --git a/tests/fixtures/optimizer/unnest_subqueries.sql b/tests/fixtures/optimizer/unnest_subqueries.sql index e78bed0..f83191d 100644 --- a/tests/fixtures/optimizer/unnest_subqueries.sql +++ b/tests/fixtures/optimizer/unnest_subqueries.sql @@ -24,6 +24,7 @@ WHERE AND x.a = (SELECT SUM(y.c) AS c FROM y WHERE y.a = x.a OFFSET 10) AND x.a > ALL (SELECT y.c FROM y WHERE y.a = x.a) AND x.a > (SELECT COUNT(*) as d FROM y WHERE y.a = x.a) + AND x.a = SUM(SELECT 1) -- invalid statement left alone ; SELECT * @@ -208,7 +209,10 @@ WHERE OFFSET 10 ) AND ARRAY_ALL(_u_19."", _x -> _x = x.a) - AND x.a > COALESCE(_u_21.d, 0); + AND x.a > COALESCE(_u_21.d, 0) + AND x.a = SUM(SELECT + 1) /* invalid statement left alone */ +; SELECT CAST(( SELECT diff --git a/tests/gen_fixtures.py b/tests/gen_fixtures.py new file mode 100644 index 0000000..7538b87 --- /dev/null +++ b/tests/gen_fixtures.py @@ -0,0 +1,602 @@ +import time + +from sqlglot.optimizer import optimize + +TPCH_SCHEMA = { + "lineitem": { + "l_orderkey": "bigint", + "l_partkey": "bigint", + "l_suppkey": "bigint", + "l_linenumber": "bigint", + "l_quantity": "double", + "l_extendedprice": "double", + "l_discount": "double", + "l_tax": "double", + "l_returnflag": "string", + "l_linestatus": "string", + "l_shipdate": "string", + "l_commitdate": "string", + "l_receiptdate": "string", + "l_shipinstruct": "string", + "l_shipmode": "string", + "l_comment": "string", + }, + "orders": { + "o_orderkey": "bigint", + "o_custkey": "bigint", + "o_orderstatus": "string", + "o_totalprice": "double", + "o_orderdate": "string", + "o_orderpriority": "string", + "o_clerk": "string", + "o_shippriority": "int", + "o_comment": "string", + }, + "customer": { + "c_custkey": "bigint", + "c_name": "string", + "c_address": "string", + "c_nationkey": "bigint", + "c_phone": "string", + "c_acctbal": "double", + "c_mktsegment": "string", + "c_comment": "string", + }, + "part": { + "p_partkey": "bigint", + "p_name": "string", + "p_mfgr": "string", + "p_brand": "string", + "p_type": "string", + "p_size": "int", + "p_container": "string", + "p_retailprice": "double", + "p_comment": "string", + }, + "supplier": { + "s_suppkey": "bigint", + "s_name": "string", + "s_address": "string", + "s_nationkey": "bigint", + "s_phone": "string", + "s_acctbal": "double", + "s_comment": "string", + }, + "partsupp": { + "ps_partkey": "bigint", + "ps_suppkey": "bigint", + "ps_availqty": "int", + "ps_supplycost": "double", + "ps_comment": "string", + }, + "nation": { + "n_nationkey": "bigint", + "n_name": "string", + "n_regionkey": "bigint", + "n_comment": "string", + }, + "region": { + "r_regionkey": "bigint", + "r_name": "string", + "r_comment": "string", + }, +} + +TPCDS_SCHEMA = { + "catalog_sales": { + "cs_sold_date_sk": "bigint", + "cs_sold_time_sk": "bigint", + "cs_ship_date_sk": "bigint", + "cs_bill_customer_sk": "bigint", + "cs_bill_cdemo_sk": "bigint", + "cs_bill_hdemo_sk": "bigint", + "cs_bill_addr_sk": "bigint", + "cs_ship_customer_sk": "bigint", + "cs_ship_cdemo_sk": "bigint", + "cs_ship_hdemo_sk": "bigint", + "cs_ship_addr_sk": "bigint", + "cs_call_center_sk": "bigint", + "cs_catalog_page_sk": "bigint", + "cs_ship_mode_sk": "bigint", + "cs_warehouse_sk": "bigint", + "cs_item_sk": "bigint", + "cs_promo_sk": "bigint", + "cs_order_number": "bigint", + "cs_quantity": "bigint", + "cs_wholesale_cost": "double", + "cs_list_price": "double", + "cs_sales_price": "double", + "cs_ext_discount_amt": "double", + "cs_ext_sales_price": "double", + "cs_ext_wholesale_cost": "double", + "cs_ext_list_price": "double", + "cs_ext_tax": "double", + "cs_coupon_amt": "double", + "cs_ext_ship_cost": "double", + "cs_net_paid": "double", + "cs_net_paid_inc_tax": "double", + "cs_net_paid_inc_ship": "double", + "cs_net_paid_inc_ship_tax": "double", + "cs_net_profit": "double", + }, + "catalog_returns": { + "cr_returned_date_sk": "bigint", + "cr_returned_time_sk": "bigint", + "cr_item_sk": "bigint", + "cr_refunded_customer_sk": "bigint", + "cr_refunded_cdemo_sk": "bigint", + "cr_refunded_hdemo_sk": "bigint", + "cr_refunded_addr_sk": "bigint", + "cr_returning_customer_sk": "bigint", + "cr_returning_cdemo_sk": "bigint", + "cr_returning_hdemo_sk": "bigint", + "cr_returning_addr_sk": "bigint", + "cr_call_center_sk": "bigint", + "cr_catalog_page_sk": "bigint", + "cr_ship_mode_sk": "bigint", + "cr_warehouse_sk": "bigint", + "cr_reason_sk": "bigint", + "cr_order_number": "bigint", + "cr_return_quantity": "bigint", + "cr_return_amount": "double", + "cr_return_tax": "double", + "cr_return_amt_inc_tax": "double", + "cr_fee": "double", + "cr_return_ship_cost": "double", + "cr_refunded_cash": "double", + "cr_reversed_charge": "double", + "cr_store_credit": "double", + "cr_net_loss": "double", + }, + "inventory": { + "inv_date_sk": "bigint", + "inv_item_sk": "bigint", + "inv_warehouse_sk": "bigint", + "inv_quantity_on_hand": "bigint", + }, + "store_sales": { + "ss_sold_date_sk": "bigint", + "ss_sold_time_sk": "bigint", + "ss_item_sk": "bigint", + "ss_customer_sk": "bigint", + "ss_cdemo_sk": "bigint", + "ss_hdemo_sk": "bigint", + "ss_addr_sk": "bigint", + "ss_store_sk": "bigint", + "ss_promo_sk": "bigint", + "ss_ticket_number": "bigint", + "ss_quantity": "bigint", + "ss_wholesale_cost": "double", + "ss_list_price": "double", + "ss_sales_price": "double", + "ss_ext_discount_amt": "double", + "ss_ext_sales_price": "double", + "ss_ext_wholesale_cost": "double", + "ss_ext_list_price": "double", + "ss_ext_tax": "double", + "ss_coupon_amt": "double", + "ss_net_paid": "double", + "ss_net_paid_inc_tax": "double", + "ss_net_profit": "double", + }, + "store_returns": { + "sr_returned_date_sk": "bigint", + "sr_return_time_sk": "bigint", + "sr_item_sk": "bigint", + "sr_customer_sk": "bigint", + "sr_cdemo_sk": "bigint", + "sr_hdemo_sk": "bigint", + "sr_addr_sk": "bigint", + "sr_store_sk": "bigint", + "sr_reason_sk": "bigint", + "sr_ticket_number": "bigint", + "sr_return_quantity": "bigint", + "sr_return_amt": "double", + "sr_return_tax": "double", + "sr_return_amt_inc_tax": "double", + "sr_fee": "double", + "sr_return_ship_cost": "double", + "sr_refunded_cash": "double", + "sr_reversed_charge": "double", + "sr_store_credit": "double", + "sr_net_loss": "double", + }, + "web_sales": { + "ws_sold_date_sk": "bigint", + "ws_sold_time_sk": "bigint", + "ws_ship_date_sk": "bigint", + "ws_item_sk": "bigint", + "ws_bill_customer_sk": "bigint", + "ws_bill_cdemo_sk": "bigint", + "ws_bill_hdemo_sk": "bigint", + "ws_bill_addr_sk": "bigint", + "ws_ship_customer_sk": "bigint", + "ws_ship_cdemo_sk": "bigint", + "ws_ship_hdemo_sk": "bigint", + "ws_ship_addr_sk": "bigint", + "ws_web_page_sk": "bigint", + "ws_web_site_sk": "bigint", + "ws_ship_mode_sk": "bigint", + "ws_warehouse_sk": "bigint", + "ws_promo_sk": "bigint", + "ws_order_number": "bigint", + "ws_quantity": "bigint", + "ws_wholesale_cost": "double", + "ws_list_price": "double", + "ws_sales_price": "double", + "ws_ext_discount_amt": "double", + "ws_ext_sales_price": "double", + "ws_ext_wholesale_cost": "double", + "ws_ext_list_price": "double", + "ws_ext_tax": "double", + "ws_coupon_amt": "double", + "ws_ext_ship_cost": "double", + "ws_net_paid": "double", + "ws_net_paid_inc_tax": "double", + "ws_net_paid_inc_ship": "double", + "ws_net_paid_inc_ship_tax": "double", + "ws_net_profit": "double", + }, + "web_returns": { + "wr_returned_date_sk": "bigint", + "wr_returned_time_sk": "bigint", + "wr_item_sk": "bigint", + "wr_refunded_customer_sk": "bigint", + "wr_refunded_cdemo_sk": "bigint", + "wr_refunded_hdemo_sk": "bigint", + "wr_refunded_addr_sk": "bigint", + "wr_returning_customer_sk": "bigint", + "wr_returning_cdemo_sk": "bigint", + "wr_returning_hdemo_sk": "bigint", + "wr_returning_addr_sk": "bigint", + "wr_web_page_sk": "bigint", + "wr_reason_sk": "bigint", + "wr_order_number": "bigint", + "wr_return_quantity": "bigint", + "wr_return_amt": "double", + "wr_return_tax": "double", + "wr_return_amt_inc_tax": "double", + "wr_fee": "double", + "wr_return_ship_cost": "double", + "wr_refunded_cash": "double", + "wr_reversed_charge": "double", + "wr_account_credit": "double", + "wr_net_loss": "double", + }, + "call_center": { + "cc_call_center_sk": "bigint", + "cc_call_center_id": "string", + "cc_rec_start_date": "string", + "cc_rec_end_date": "string", + "cc_closed_date_sk": "bigint", + "cc_open_date_sk": "bigint", + "cc_name": "string", + "cc_class": "string", + "cc_employees": "bigint", + "cc_sq_ft": "bigint", + "cc_hours": "string", + "cc_manager": "string", + "cc_mkt_id": "bigint", + "cc_mkt_class": "string", + "cc_mkt_desc": "string", + "cc_market_manager": "string", + "cc_division": "bigint", + "cc_division_name": "string", + "cc_company": "bigint", + "cc_company_name": "string", + "cc_street_number": "string", + "cc_street_name": "string", + "cc_street_type": "string", + "cc_suite_number": "string", + "cc_city": "string", + "cc_county": "string", + "cc_state": "string", + "cc_zip": "string", + "cc_country": "string", + "cc_gmt_offset": "double", + "cc_tax_percentage": "double", + }, + "catalog_page": { + "cp_catalog_page_sk": "bigint", + "cp_catalog_page_id": "string", + "cp_start_date_sk": "bigint", + "cp_end_date_sk": "bigint", + "cp_department": "string", + "cp_catalog_number": "bigint", + "cp_catalog_page_number": "bigint", + "cp_description": "string", + "cp_type": "string", + }, + "customer": { + "c_customer_sk": "bigint", + "c_customer_id": "string", + "c_current_cdemo_sk": "bigint", + "c_current_hdemo_sk": "bigint", + "c_current_addr_sk": "bigint", + "c_first_shipto_date_sk": "bigint", + "c_first_sales_date_sk": "bigint", + "c_salutation": "string", + "c_first_name": "string", + "c_last_name": "string", + "c_preferred_cust_flag": "string", + "c_birth_day": "bigint", + "c_birth_month": "bigint", + "c_birth_year": "bigint", + "c_birth_country": "string", + "c_login": "string", + "c_email_address": "string", + "c_last_review_date": "string", + }, + "customer_address": { + "ca_address_sk": "bigint", + "ca_address_id": "string", + "ca_street_number": "string", + "ca_street_name": "string", + "ca_street_type": "string", + "ca_suite_number": "string", + "ca_city": "string", + "ca_county": "string", + "ca_state": "string", + "ca_zip": "string", + "ca_country": "string", + "ca_gmt_offset": "double", + "ca_location_type": "string", + }, + "customer_demographics": { + "cd_demo_sk": "bigint", + "cd_gender": "string", + "cd_marital_status": "string", + "cd_education_status": "string", + "cd_purchase_estimate": "bigint", + "cd_credit_rating": "string", + "cd_dep_count": "bigint", + "cd_dep_employed_count": "bigint", + "cd_dep_college_count": "bigint", + }, + "date_dim": { + "d_date_sk": "bigint", + "d_date_id": "string", + "d_date": "string", + "d_month_seq": "bigint", + "d_week_seq": "bigint", + "d_quarter_seq": "bigint", + "d_year": "bigint", + "d_dow": "bigint", + "d_moy": "bigint", + "d_dom": "bigint", + "d_qoy": "bigint", + "d_fy_year": "bigint", + "d_fy_quarter_seq": "bigint", + "d_fy_week_seq": "bigint", + "d_day_name": "string", + "d_quarter_name": "string", + "d_holiday": "string", + "d_weekend": "string", + "d_following_holiday": "string", + "d_first_dom": "bigint", + "d_last_dom": "bigint", + "d_same_day_ly": "bigint", + "d_same_day_lq": "bigint", + "d_current_day": "string", + "d_current_week": "string", + "d_current_month": "string", + "d_current_quarter": "string", + "d_current_year": "string", + }, + "household_demographics": { + "hd_demo_sk": "bigint", + "hd_income_band_sk": "bigint", + "hd_buy_potential": "string", + "hd_dep_count": "bigint", + "hd_vehicle_count": "bigint", + }, + "income_band": { + "ib_income_band_sk": "bigint", + "ib_lower_bound": "bigint", + "ib_upper_bound": "bigint", + }, + "item": { + "i_item_sk": "bigint", + "i_item_id": "string", + "i_rec_start_date": "string", + "i_rec_end_date": "string", + "i_item_desc": "string", + "i_current_price": "double", + "i_wholesale_cost": "double", + "i_brand_id": "bigint", + "i_brand": "string", + "i_class_id": "bigint", + "i_class": "string", + "i_category_id": "bigint", + "i_category": "string", + "i_manufact_id": "bigint", + "i_manufact": "string", + "i_size": "string", + "i_formulation": "string", + "i_color": "string", + "i_units": "string", + "i_container": "string", + "i_manager_id": "bigint", + "i_product_name": "string", + }, + "promotion": { + "p_promo_sk": "bigint", + "p_promo_id": "string", + "p_start_date_sk": "bigint", + "p_end_date_sk": "bigint", + "p_item_sk": "bigint", + "p_cost": "double", + "p_response_target": "bigint", + "p_promo_name": "string", + "p_channel_dmail": "string", + "p_channel_email": "string", + "p_channel_catalog": "string", + "p_channel_tv": "string", + "p_channel_radio": "string", + "p_channel_press": "string", + "p_channel_event": "string", + "p_channel_demo": "string", + "p_channel_details": "string", + "p_purpose": "string", + "p_discount_active": "string", + }, + "reason": {"r_reason_sk": "bigint", "r_reason_id": "string", "r_reason_desc": "string"}, + "ship_mode": { + "sm_ship_mode_sk": "bigint", + "sm_ship_mode_id": "string", + "sm_type": "string", + "sm_code": "string", + "sm_carrier": "string", + "sm_contract": "string", + }, + "store": { + "s_store_sk": "bigint", + "s_store_id": "string", + "s_rec_start_date": "string", + "s_rec_end_date": "string", + "s_closed_date_sk": "bigint", + "s_store_name": "string", + "s_number_employees": "bigint", + "s_floor_space": "bigint", + "s_hours": "string", + "s_manager": "string", + "s_market_id": "bigint", + "s_geography_class": "string", + "s_market_desc": "string", + "s_market_manager": "string", + "s_division_id": "bigint", + "s_division_name": "string", + "s_company_id": "bigint", + "s_company_name": "string", + "s_street_number": "string", + "s_street_name": "string", + "s_street_type": "string", + "s_suite_number": "string", + "s_city": "string", + "s_county": "string", + "s_state": "string", + "s_zip": "string", + "s_country": "string", + "s_gmt_offset": "double", + "s_tax_precentage": "double", + }, + "time_dim": { + "t_time_sk": "bigint", + "t_time_id": "string", + "t_time": "bigint", + "t_hour": "bigint", + "t_minute": "bigint", + "t_second": "bigint", + "t_am_pm": "string", + "t_shift": "string", + "t_sub_shift": "string", + "t_meal_time": "string", + }, + "warehouse": { + "w_warehouse_sk": "bigint", + "w_warehouse_id": "string", + "w_warehouse_name": "string", + "w_warehouse_sq_ft": "bigint", + "w_street_number": "string", + "w_street_name": "string", + "w_street_type": "string", + "w_suite_number": "string", + "w_city": "string", + "w_county": "string", + "w_state": "string", + "w_zip": "string", + "w_country": "string", + "w_gmt_offset": "double", + }, + "web_page": { + "wp_web_page_sk": "bigint", + "wp_web_page_id": "string", + "wp_rec_start_date": "string", + "wp_rec_end_date": "string", + "wp_creation_date_sk": "bigint", + "wp_access_date_sk": "bigint", + "wp_autogen_flag": "string", + "wp_customer_sk": "bigint", + "wp_url": "string", + "wp_type": "string", + "wp_char_count": "bigint", + "wp_link_count": "bigint", + "wp_image_count": "bigint", + "wp_max_ad_count": "bigint", + }, + "web_site": { + "web_site_sk": "bigint", + "web_site_id": "string", + "web_rec_start_date": "string", + "web_rec_end_date": "string", + "web_name": "string", + "web_open_date_sk": "bigint", + "web_close_date_sk": "bigint", + "web_class": "string", + "web_manager": "string", + "web_mkt_id": "bigint", + "web_mkt_class": "string", + "web_mkt_desc": "string", + "web_market_manager": "string", + "web_company_id": "bigint", + "web_company_name": "string", + "web_street_number": "string", + "web_street_name": "string", + "web_street_type": "string", + "web_suite_number": "string", + "web_city": "string", + "web_county": "string", + "web_state": "string", + "web_zip": "string", + "web_country": "string", + "web_gmt_offset": "string", + "web_tax_percentage": "double", + }, +} + + +def rewrite_fixtures(in_path, out_path, schema, num, kind): + with open(out_path, "w", encoding="UTF-8") as fixture: + for i in range(num): + i = i + 1 + with open(in_path.format(i=i), encoding="UTF-8") as file: + original = "\n".join( + line.rstrip() + for line in file.read().split(";")[0].split("\n") + if not line.startswith("--") + ) + original = original.replace("`", '"').strip() + now = time.time() + try: + optimized = optimize(original, schema=schema) + except Exception as e: + print("****", i, e, "****") + continue + + fixture.write( + f"""-------------------------------------- +-- TPC-{kind} {i} +-------------------------------------- +{original}; +{optimized.sql(pretty=True)}; + +""" + ) + print(i, time.time() - now) + + +rewrite_fixtures( + "/home/toby/dev/tpch/{i}.sql", + "/home/toby/dev/sqlglot/tests/fixtures/optimizer/tpc-h/tpc-h.sql", + TPCH_SCHEMA, + 22, + "H", +) + +rewrite_fixtures( + "/home/toby/dev/tpcds/query{i}.sql", + "/home/toby/dev/sqlglot/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql", + TPCDS_SCHEMA, + 99, + "DS", +) diff --git a/tests/test_executor.py b/tests/test_executor.py index ffe00a7..78d037a 100644 --- a/tests/test_executor.py +++ b/tests/test_executor.py @@ -289,11 +289,6 @@ class TestExecutor(unittest.TestCase): ["a"], [(1,), (2,), (3,)], ), - ( - "SELECT 1 AS a UNION SELECT 2 AS a UNION SELECT 3 AS a", - ["a"], - [(1,), (2,), (3,)], - ), ( "SELECT 1 / 2 AS a", ["a"], @@ -320,6 +315,11 @@ class TestExecutor(unittest.TestCase): (None,), ], ), + ( + "SELECT a FROM x UNION ALL SELECT a FROM x LIMIT 1", + ["a"], + [("a",)], + ), ]: with self.subTest(sql): if isinstance(rows, list): diff --git a/tests/test_optimizer.py b/tests/test_optimizer.py index 141203d..9b68c78 100644 --- a/tests/test_optimizer.py +++ b/tests/test_optimizer.py @@ -109,7 +109,9 @@ class TestOptimizer(unittest.TestCase): }, } - def check_file(self, file, func, pretty=False, execute=False, set_dialect=False, **kwargs): + def check_file( + self, file, func, pretty=False, execute=False, set_dialect=False, only=None, **kwargs + ): with ProcessPoolExecutor() as pool: results = {} @@ -117,6 +119,8 @@ class TestOptimizer(unittest.TestCase): load_sql_fixture_pairs(f"optimizer/{file}.sql"), start=1 ): title = meta.get("title") or f"{i}, {sql}" + if only and title != only: + continue dialect = meta.get("dialect") leave_tables_isolated = meta.get("leave_tables_isolated") @@ -137,13 +141,14 @@ class TestOptimizer(unittest.TestCase): ) for future in as_completed(results): - optimized = future.result() sql, title, expected, dialect, execute = results[future] with self.subTest(title): + optimized = future.result() + actual = optimized.sql(pretty=pretty, dialect=dialect) self.assertEqual( expected, - optimized.sql(pretty=pretty, dialect=dialect), + actual, ) if string_to_bool(execute): @@ -309,7 +314,7 @@ class TestOptimizer(unittest.TestCase): self.check_file("pushdown_projections", pushdown_projections, schema=self.schema) def test_simplify(self): - self.check_file("simplify", simplify) + self.check_file("simplify", simplify, set_dialect=True) expression = parse_one("TRUE AND TRUE AND TRUE") self.assertEqual(exp.true(), optimizer.simplify.simplify(expression)) diff --git a/tests/test_parser.py b/tests/test_parser.py index f3e663e..6611b87 100644 --- a/tests/test_parser.py +++ b/tests/test_parser.py @@ -17,6 +17,13 @@ class TestParser(unittest.TestCase): self.assertIsInstance(parse_one("int", into=exp.DataType), exp.DataType) self.assertIsInstance(parse_one("array", into=exp.DataType), exp.DataType) self.assertIsInstance(parse_one("foo", into=exp.Table), exp.Table) + self.assertIsInstance( + parse_one( + "WHEN MATCHED THEN UPDATE SET target.salary = COALESCE(source.salary, target.salary)", + into=exp.When, + ), + exp.When, + ) with self.assertRaises(ParseError) as ctx: parse_one("SELECT * FROM tbl", into=exp.Table) @@ -94,12 +101,31 @@ class TestParser(unittest.TestCase): tables = [t.sql() for t in parse_one("select * from a, b.c, .d").find_all(exp.Table)] self.assertEqual(set(tables), {"a", "b.c", "d"}) - def test_union_order(self): + def test_union(self): self.assertIsInstance(parse_one("SELECT * FROM (SELECT 1) UNION SELECT 2"), exp.Union) self.assertIsInstance( parse_one("SELECT x FROM y HAVING x > (SELECT 1) UNION SELECT 2"), exp.Union ) + # Check that modifiers are attached to the topmost union node and not the rightmost query + single_union = "SELECT x FROM t1 UNION ALL SELECT x FROM t2 LIMIT 1" + expr = parse_one(single_union) + limit = expr.assert_is(exp.Union).args.get("limit") + self.assertIsInstance(limit, exp.Limit) + self.assertEqual(expr.sql(), single_union) + + two_unions = ( + "SELECT x FROM t1 UNION ALL SELECT x FROM t2 UNION ALL SELECT x FROM t3 LIMIT 1" + ) + expr = parse_one(two_unions) + limit = expr.assert_is(exp.Union).args.get("limit") + self.assertIsInstance(limit, exp.Limit) + self.assertEqual(expr.sql(), two_unions) + + expr = parse_one(single_union, read="clickhouse") + self.assertIsNone(expr.args.get("limit")) + self.assertEqual(expr.sql(dialect="clickhouse"), single_union) + def test_select(self): self.assertIsNotNone(parse_one("select 1 natural")) self.assertIsNotNone(parse_one("select * from (select 1) x order by x.y").args["order"]) diff --git a/tests/test_tokens.py b/tests/test_tokens.py index b97f54a..970c1ac 100644 --- a/tests/test_tokens.py +++ b/tests/test_tokens.py @@ -71,6 +71,20 @@ x""" self.assertEqual(tokens[2].line, 2) self.assertEqual(tokens[3].line, 3) + def test_crlf(self): + tokens = Tokenizer().tokenize("SELECT a\r\nFROM b") + tokens = [(token.token_type, token.text) for token in tokens] + + self.assertEqual( + tokens, + [ + (TokenType.SELECT, "SELECT"), + (TokenType.VAR, "a"), + (TokenType.FROM, "FROM"), + (TokenType.VAR, "b"), + ], + ) + def test_command(self): tokens = Tokenizer().tokenize("SHOW;") self.assertEqual(tokens[0].token_type, TokenType.SHOW) diff --git a/tests/test_transpile.py b/tests/test_transpile.py index b732b45..fb8f831 100644 --- a/tests/test_transpile.py +++ b/tests/test_transpile.py @@ -89,6 +89,7 @@ class TestTranspile(unittest.TestCase): self.validate("SELECT MIN(3)>=MIN(2)", "SELECT MIN(3) >= MIN(2)") self.validate("SELECT 1>0", "SELECT 1 > 0") self.validate("SELECT 3>=3", "SELECT 3 >= 3") + self.validate("SELECT a\r\nFROM b", "SELECT a FROM b") def test_comments(self): self.validate( diff --git a/tests/tpch.py b/tests/tpch.py deleted file mode 100644 index ef2b666..0000000 --- a/tests/tpch.py +++ /dev/null @@ -1,115 +0,0 @@ -import time - -from sqlglot.optimizer import optimize - -INPUT = "/home/toby/dev/tpch/{i}.sql" -OUTPUT = "/home/toby/dev/sqlglot/tests/fixtures/optimizer/tpc-h/tpc-h.sql" -NUM = 22 -SCHEMA = { - "lineitem": { - "l_orderkey": "bigint", - "l_partkey": "bigint", - "l_suppkey": "bigint", - "l_linenumber": "bigint", - "l_quantity": "double", - "l_extendedprice": "double", - "l_discount": "double", - "l_tax": "double", - "l_returnflag": "string", - "l_linestatus": "string", - "l_shipdate": "string", - "l_commitdate": "string", - "l_receiptdate": "string", - "l_shipinstruct": "string", - "l_shipmode": "string", - "l_comment": "string", - }, - "orders": { - "o_orderkey": "bigint", - "o_custkey": "bigint", - "o_orderstatus": "string", - "o_totalprice": "double", - "o_orderdate": "string", - "o_orderpriority": "string", - "o_clerk": "string", - "o_shippriority": "int", - "o_comment": "string", - }, - "customer": { - "c_custkey": "bigint", - "c_name": "string", - "c_address": "string", - "c_nationkey": "bigint", - "c_phone": "string", - "c_acctbal": "double", - "c_mktsegment": "string", - "c_comment": "string", - }, - "part": { - "p_partkey": "bigint", - "p_name": "string", - "p_mfgr": "string", - "p_brand": "string", - "p_type": "string", - "p_size": "int", - "p_container": "string", - "p_retailprice": "double", - "p_comment": "string", - }, - "supplier": { - "s_suppkey": "bigint", - "s_name": "string", - "s_address": "string", - "s_nationkey": "bigint", - "s_phone": "string", - "s_acctbal": "double", - "s_comment": "string", - }, - "partsupp": { - "ps_partkey": "bigint", - "ps_suppkey": "bigint", - "ps_availqty": "int", - "ps_supplycost": "double", - "ps_comment": "string", - }, - "nation": { - "n_nationkey": "bigint", - "n_name": "string", - "n_regionkey": "bigint", - "n_comment": "string", - }, - "region": { - "r_regionkey": "bigint", - "r_name": "string", - "r_comment": "string", - }, -} -KIND = "H" - -with open(OUTPUT, "w", encoding="UTF-8") as fixture: - for i in range(NUM): - i = i + 1 - with open(INPUT.format(i=i), encoding="UTF-8") as file: - original = "\n".join( - line.rstrip() - for line in file.read().split(";")[0].split("\n") - if not line.startswith("--") - ) - original = original.replace("`", '"').strip() - now = time.time() - try: - optimized = optimize(original, schema=SCHEMA) - except Exception as e: - print("****", i, e, "****") - continue - - fixture.write( - f"""-------------------------------------- --- TPC-{KIND} {i} --------------------------------------- -{original}; -{optimized.sql(pretty=True)}; - -""" - ) - print(i, time.time() - now) -- cgit v1.2.3