From 5f8be2e0852f3c925fb873a48946caee3050899f Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Wed, 31 Jan 2024 06:44:37 +0100 Subject: Adding upstream version 20.11.0. Signed-off-by: Daniel Baumann --- tests/fixtures/optimizer/merge_subqueries.sql | 10 +-- tests/fixtures/optimizer/optimizer.sql | 72 +++++++++++++---- tests/fixtures/optimizer/pushdown_projections.sql | 20 ++--- tests/fixtures/optimizer/qualify_columns.sql | 36 ++++++--- tests/fixtures/optimizer/qualify_columns_ddl.sql | 12 +-- tests/fixtures/optimizer/qualify_tables.sql | 20 ++++- tests/fixtures/optimizer/tpc-ds/tpc-ds.sql | 98 +++++++++++------------ tests/fixtures/optimizer/tpc-h/tpc-h.sql | 4 +- tests/fixtures/optimizer/unnest_subqueries.sql | 12 +-- 9 files changed, 177 insertions(+), 107 deletions(-) (limited to 'tests/fixtures/optimizer') diff --git a/tests/fixtures/optimizer/merge_subqueries.sql b/tests/fixtures/optimizer/merge_subqueries.sql index 7fc4b25..7bc45a7 100644 --- a/tests/fixtures/optimizer/merge_subqueries.sql +++ b/tests/fixtures/optimizer/merge_subqueries.sql @@ -226,7 +226,7 @@ FROM t1 WHERE row_num = 1; -WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.a AS a, t1.b AS b FROM t1 WHERE t1.row_num = 1; +WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.a AS a, t1.b AS b FROM t1 AS t1 WHERE t1.row_num = 1; # title: Test preventing merge of window expressions join clause with t1 as ( @@ -241,7 +241,7 @@ SELECT t1.a, t1.b FROM t1 JOIN y ON t1.a = y.c AND t1.row_num = 1; -WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.a AS a, t1.b AS b FROM t1 JOIN y AS y ON t1.a = y.c AND t1.row_num = 1; +WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.a AS a, t1.b AS b FROM t1 AS t1 JOIN y AS y ON t1.a = y.c AND t1.row_num = 1; # title: Test preventing merge of window expressions agg function with t1 as ( @@ -256,7 +256,7 @@ SELECT SUM(t1.row_num) as total_rows FROM t1; -WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT SUM(t1.row_num) AS total_rows FROM t1; +WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT SUM(t1.row_num) AS total_rows FROM t1 AS t1; # title: Test prevent merging of window if in group by func with t1 as ( @@ -274,7 +274,7 @@ FROM t1 GROUP BY t1.row_num ORDER BY t1.row_num; -WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.row_num AS row_num, SUM(t1.a) AS total FROM t1 GROUP BY t1.row_num ORDER BY row_num; +WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.row_num AS row_num, SUM(t1.a) AS total FROM t1 AS t1 GROUP BY t1.row_num ORDER BY row_num; # title: Test prevent merging of window if in order by func with t1 as ( @@ -291,7 +291,7 @@ SELECT FROM t1 ORDER BY t1.row_num, t1.a; -WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.row_num AS row_num, t1.a AS a FROM t1 ORDER BY t1.row_num, t1.a; +WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.row_num AS row_num, t1.a AS a FROM t1 AS t1 ORDER BY t1.row_num, t1.a; # title: Test allow merging of window function with t1 as ( diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql index ea29e3e..8f4a551 100644 --- a/tests/fixtures/optimizer/optimizer.sql +++ b/tests/fixtures/optimizer/optimizer.sql @@ -79,7 +79,7 @@ WITH "cte" AS ( ) SELECT "cte"."a" AS "a" -FROM "cte"; +FROM "cte" AS "cte"; # title: Chained CTEs WITH cte1 AS ( @@ -103,11 +103,11 @@ WITH "cte1" AS ( ) SELECT "cte1"."a" AS "a" -FROM "cte1" +FROM "cte1" AS "cte1" UNION ALL SELECT "cte1"."a" + 1 AS "a" -FROM "cte1"; +FROM "cte1" AS "cte1"; # title: Correlated subquery SELECT a, SUM(b) AS sum_b @@ -223,14 +223,14 @@ WITH "m" AS ( SELECT "m"."a" AS "a", "m"."b" AS "b" - FROM "m" + FROM "m" AS "m" WHERE "m"."a" = 1 ), "o" AS ( SELECT "m"."a" AS "a", "m"."b" AS "b" - FROM "m" + FROM "m" AS "m" WHERE "m"."a" = 2 ) @@ -238,10 +238,10 @@ SELECT "n"."a" AS "a", "n"."b" AS "b", "o"."b" AS "b" -FROM "n" +FROM "n" AS "n" JOIN "n" AS "n2" ON "n"."a" = "n2"."a" -FULL JOIN "o" +FULL JOIN "o" AS "o" ON "n"."a" = "o"."a" WHERE "o"."b" > 0; @@ -355,7 +355,7 @@ WITH RECURSIVE "base" AS ( "cte1"."a" AS "a", "cte1"."b" AS "b", "cte1"."c" AS "c" - FROM "cte1" + FROM "cte1" AS "cte1" WHERE "cte1"."a" < 1 ) @@ -363,7 +363,7 @@ SELECT "cte1"."a" AS "a", "cte1"."b" AS "b", "cte1"."c" AS "c" -FROM "cte1"; +FROM "cte1" AS "cte1"; # title: right join should not push down to from SELECT x.a, y.b @@ -747,7 +747,7 @@ SELECT `_q_0`.`second_half_sales` AS `second_half_sales` FROM `produce` AS `produce` UNPIVOT((`first_half_sales`, `second_half_sales`) FOR `semesters` IN ((`produce`.`q1`, `produce`.`q2`) AS 'semester_1', (`produce`.`q3`, `produce`.`q4`) AS 'semester_2')) AS `_q_0`; -# title: quoting is maintained +# title: quoting is preserved # dialect: snowflake with cte1("id", foo) as (select 1, 2) select "id" from cte1; WITH "CTE1" AS ( @@ -756,7 +756,7 @@ WITH "CTE1" AS ( ) SELECT "CTE1"."id" AS "id" -FROM "CTE1"; +FROM "CTE1" AS "CTE1"; # title: ensures proper quoting happens after all optimizations # execute: false @@ -1090,7 +1090,7 @@ WITH "m" AS ( ) SELECT COALESCE("m"."a", "foo"."a") AS "a" -FROM "m" +FROM "m" AS "m" JOIN "n" AS "foo"("a") ON "foo"."a" = "m"."a"; @@ -1170,7 +1170,7 @@ SELECT "alias3"."m_od" AS "m_od", COALESCE("alias3"."c_od", 0) AS "c_od" FROM "table1" AS "table1" -LEFT JOIN "alias3" +LEFT JOIN "alias3" AS "alias3" ON "alias3"."cid" = "table1"."cid"; # title: CTE with EXPLODE cannot be merged @@ -1205,7 +1205,7 @@ WITH `T` AS ( ) SELECT `T`.`CoL` AS `CoL` -FROM `T`; +FROM `T` AS `T`; # title: override mysql's settings so it normalizes to lowercase # dialect: mysql, normalization_strategy = lowercase @@ -1217,7 +1217,7 @@ WITH `t` AS ( ) SELECT `t`.`CoL` AS `CoL` -FROM `t`; +FROM `t` AS `t`; # title: top-level query is parenthesized # execute: false @@ -1239,11 +1239,11 @@ WITH "x" AS ( ( SELECT "x"."a" AS "a" - FROM "x" + FROM "x" AS "x" UNION ALL SELECT "x"."a" AS "a" - FROM "x" + FROM "x" AS "x" LIMIT 10 ) LIMIT 10; @@ -1273,3 +1273,41 @@ JOIN "stops" AS "d"("id", "name") AND ( "c"."name" = 'Craiglockhart' OR "d"."name" = 'Tollcross' ); + +# title: avoid dag cycles with unnesting subqueries +# execute: false +# dialect: snowflake +SELECT + A.ACCOUNT_ID, + A.NAME, + C.EMAIL_DOMAIN +FROM ACCOUNTS AS A +LEFT JOIN CONTACTS AS C + ON C.ACCOUNT_ID = A.ACCOUNT_ID + AND C.EMAIL_DOMAIN IN ( + SELECT + D.DOMAIN + FROM DOMAINS D + WHERE + TYPE = 'education' + ); +WITH "_u_0" AS ( + SELECT + "D"."DOMAIN" AS "DOMAIN" + FROM "DOMAINS" AS "D" + WHERE + "D"."TYPE" = 'education' + GROUP BY + "D"."DOMAIN" +) +SELECT + "A"."ACCOUNT_ID" AS "ACCOUNT_ID", + "A"."NAME" AS "NAME", + "C"."EMAIL_DOMAIN" AS "EMAIL_DOMAIN" +FROM "ACCOUNTS" AS "A" +LEFT JOIN "CONTACTS" AS "C" + ON "A"."ACCOUNT_ID" = "C"."ACCOUNT_ID" +LEFT JOIN "_u_0" AS "_u_0" + ON "C"."EMAIL_DOMAIN" = "_u_0"."DOMAIN" +WHERE + NOT "_u_0"."DOMAIN" IS NULL; diff --git a/tests/fixtures/optimizer/pushdown_projections.sql b/tests/fixtures/optimizer/pushdown_projections.sql index 8497c5b..b7103ef 100644 --- a/tests/fixtures/optimizer/pushdown_projections.sql +++ b/tests/fixtures/optimizer/pushdown_projections.sql @@ -23,19 +23,19 @@ SELECT a FROM (SELECT a, b FROM x UNION ALL SELECT a, b FROM x); SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x UNION ALL SELECT x.a AS a FROM x AS x) AS _q_0; WITH t1 AS (SELECT x.a AS a, x.b AS b FROM x UNION ALL SELECT z.b AS b, z.c AS c FROM z) SELECT a, b FROM t1; -WITH t1 AS (SELECT x.a AS a, x.b AS b FROM x AS x UNION ALL SELECT z.b AS b, z.c AS c FROM z AS z) SELECT t1.a AS a, t1.b AS b FROM t1; +WITH t1 AS (SELECT x.a AS a, x.b AS b FROM x AS x UNION ALL SELECT z.b AS b, z.c AS c FROM z AS z) SELECT t1.a AS a, t1.b AS b FROM t1 AS t1; SELECT a FROM (SELECT a, b FROM x UNION SELECT a, b FROM x); SELECT _q_0.a AS a FROM (SELECT x.a AS a, x.b AS b FROM x AS x UNION SELECT x.a AS a, x.b AS b FROM x AS x) AS _q_0; WITH y AS (SELECT * FROM x) SELECT a FROM y; -WITH y AS (SELECT x.a AS a FROM x AS x) SELECT y.a AS a FROM y; +WITH y AS (SELECT x.a AS a FROM x AS x) SELECT y.a AS a FROM y AS y; WITH z AS (SELECT * FROM x), q AS (SELECT b FROM z) SELECT b FROM q; -WITH z AS (SELECT x.b AS b FROM x AS x), q AS (SELECT z.b AS b FROM z) SELECT q.b AS b FROM q; +WITH z AS (SELECT x.b AS b FROM x AS x), q AS (SELECT z.b AS b FROM z AS z) SELECT q.b AS b FROM q AS q; WITH z AS (SELECT * FROM x) SELECT a FROM z UNION SELECT a FROM z; -WITH z AS (SELECT x.a AS a FROM x AS x) SELECT z.a AS a FROM z UNION SELECT z.a AS a FROM z; +WITH z AS (SELECT x.a AS a FROM x AS x) SELECT z.a AS a FROM z AS z UNION SELECT z.a AS a FROM z AS z; SELECT b FROM (SELECT a, SUM(b) AS b FROM x GROUP BY a); SELECT _q_0.b AS b FROM (SELECT SUM(x.b) AS b FROM x AS x GROUP BY x.a) AS _q_0; @@ -50,7 +50,7 @@ SELECT x FROM UNNEST([1, 2]) AS q(x, y); SELECT q.x AS x FROM UNNEST(ARRAY(1, 2)) AS q(x, y); WITH t1 AS (SELECT cola, colb FROM UNNEST([STRUCT(1 AS cola, 'test' AS colb)]) AS "q"("cola", "colb")) SELECT cola FROM t1; -WITH t1 AS (SELECT "q".cola AS cola FROM UNNEST(ARRAY(STRUCT(1 AS cola, 'test' AS colb))) AS "q"("cola", "colb")) SELECT t1.cola AS cola FROM t1; +WITH t1 AS (SELECT "q".cola AS cola FROM UNNEST(ARRAY(STRUCT(1 AS cola, 'test' AS colb))) AS "q"("cola", "colb")) SELECT t1.cola AS cola FROM t1 AS t1; SELECT x FROM VALUES(1, 2) AS q(x, y); SELECT q.x AS x FROM (VALUES (1, 2)) AS q(x, y); @@ -59,7 +59,7 @@ SELECT i.a FROM x AS i LEFT JOIN (SELECT a, b FROM (SELECT a, b FROM x)) AS j ON SELECT i.a AS a FROM x AS i LEFT JOIN (SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0) AS j ON i.a = j.a; WITH cte AS (SELECT source.a AS a, ROW_NUMBER() OVER (PARTITION BY source.id, source.timestamp ORDER BY source.a DESC) AS index FROM source AS source QUALIFY index) SELECT cte.a AS a FROM cte; -WITH cte AS (SELECT source.a AS a FROM source AS source QUALIFY ROW_NUMBER() OVER (PARTITION BY source.id, source.timestamp ORDER BY source.a DESC)) SELECT cte.a AS a FROM cte; +WITH cte AS (SELECT source.a AS a FROM source AS source QUALIFY ROW_NUMBER() OVER (PARTITION BY source.id, source.timestamp ORDER BY source.a DESC)) SELECT cte.a AS a FROM cte AS cte; WITH cte AS (SELECT 1 AS x, 2 AS y, 3 AS z) SELECT cte.a FROM cte AS cte(a); WITH cte AS (SELECT 1 AS x) SELECT cte.a AS a FROM cte AS cte(a); @@ -71,13 +71,13 @@ WITH cte(x, y, z) AS (SELECT 1, 2, 3) SELECT a, z FROM (SELECT * FROM cte AS cte WITH cte AS (SELECT 1 AS x, 3 AS z) SELECT cte.a AS a, cte.z AS z FROM (SELECT cte.b AS a, cte.z AS z FROM cte AS cte(b)) AS cte; WITH y AS (SELECT a FROM x) SELECT 1 FROM y; -WITH y AS (SELECT 1 AS _ FROM x AS x) SELECT 1 AS "1" FROM y; +WITH y AS (SELECT 1 AS _ FROM x AS x) SELECT 1 AS "1" FROM y AS y; WITH y AS (SELECT SUM(a) FROM x) SELECT 1 FROM y; -WITH y AS (SELECT MAX(1) AS _ FROM x AS x) SELECT 1 AS "1" FROM y; +WITH y AS (SELECT MAX(1) AS _ FROM x AS x) SELECT 1 AS "1" FROM y AS y; WITH y AS (SELECT a FROM x GROUP BY a) SELECT 1 FROM y; -WITH y AS (SELECT 1 AS _ FROM x AS x GROUP BY x.a) SELECT 1 AS "1" FROM y; +WITH y AS (SELECT 1 AS _ FROM x AS x GROUP BY x.a) SELECT 1 AS "1" FROM y AS y; -------------------------------------- -- Unknown Star Expansion @@ -102,7 +102,7 @@ SELECT a FROM (SELECT * FROM (SELECT * FROM aa)); SELECT _q_1.a AS a FROM (SELECT _q_0.a AS a FROM (SELECT aa.a AS a FROM aa AS aa) AS _q_0) AS _q_1; with cte1 as (SELECT cola, colb FROM tb UNION ALL SELECT colc, cold FROM tb2) SELECT cola FROM cte1; -WITH cte1 AS (SELECT tb.cola AS cola FROM tb AS tb UNION ALL SELECT tb2.colc AS colc FROM tb2 AS tb2) SELECT cte1.cola AS cola FROM cte1; +WITH cte1 AS (SELECT tb.cola AS cola FROM tb AS tb UNION ALL SELECT tb2.colc AS colc FROM tb2 AS tb2) SELECT cte1.cola AS cola FROM cte1 AS cte1; SELECT * FROM ((SELECT c FROM t1) JOIN t2); SELECT * FROM ((SELECT t1.c AS c FROM t1 AS t1) AS _q_0, t2 AS t2); diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql index 5641ed4..ad197db 100644 --- a/tests/fixtures/optimizer/qualify_columns.sql +++ b/tests/fixtures/optimizer/qualify_columns.sql @@ -227,9 +227,22 @@ SELECT x.a AS a FROM x AS x WHERE x.b IN (SELECT y.c AS c FROM y AS y); SELECT (SELECT c FROM y) FROM x; SELECT (SELECT y.c AS c FROM y AS y) AS _col_0 FROM x AS x; +# execute: false +WITH t(c) AS (SELECT 1) SELECT (SELECT c) FROM t; +WITH t AS (SELECT 1 AS c) SELECT (SELECT t.c AS c) AS _col_0 FROM t AS t; + +# execute: false +WITH t1(c1) AS (SELECT 1), t2(c2) AS (SELECT 2) SELECT (SELECT c1 FROM t2) FROM t1; +WITH t1 AS (SELECT 1 AS c1), t2 AS (SELECT 2 AS c2) SELECT (SELECT t1.c1 AS c1 FROM t2 AS t2) AS _col_0 FROM t1 AS t1; + SELECT a FROM (SELECT a FROM x) WHERE a IN (SELECT b FROM (SELECT b FROM y)); SELECT _q_1.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_1 WHERE _q_1.a IN (SELECT _q_0.b AS b FROM (SELECT y.b AS b FROM y AS y) AS _q_0); +# dialect: mysql +# execute: false +SELECT * FROM table_a as A WHERE A.col1 IN (SELECT MAX(B.col2) FROM table_b as B UNION ALL SELECT MAX(C.col2) FROM table_b as C); +SELECT * FROM table_a AS `A` WHERE `A`.col1 IN (SELECT MAX(`B`.col2) AS _col_0 FROM table_b AS `B` UNION ALL SELECT MAX(`C`.col2) AS _col_0 FROM table_b AS `C`); + -------------------------------------- -- Correlated subqueries -------------------------------------- @@ -291,28 +304,28 @@ SELECT s.b AS b FROM (SELECT t1.b AS b FROM t1 AS t1 UNION ALL SELECT t2.b AS b -- CTEs -------------------------------------- WITH z AS (SELECT x.a AS a FROM x) SELECT z.a AS a FROM z; -WITH z AS (SELECT x.a AS a FROM x AS x) SELECT z.a AS a FROM z; +WITH z AS (SELECT x.a AS a FROM x AS x) SELECT z.a AS a FROM z AS z; WITH z(a) AS (SELECT a FROM x) SELECT * FROM z; -WITH z AS (SELECT x.a AS a FROM x AS x) SELECT z.a AS a FROM z; +WITH z AS (SELECT x.a AS a FROM x AS x) SELECT z.a AS a FROM z AS z; WITH z AS (SELECT a FROM x) SELECT * FROM z as q; WITH z AS (SELECT x.a AS a FROM x AS x) SELECT q.a AS a FROM z AS q; WITH z AS (SELECT a FROM x) SELECT * FROM z; -WITH z AS (SELECT x.a AS a FROM x AS x) SELECT z.a AS a FROM z; +WITH z AS (SELECT x.a AS a FROM x AS x) SELECT z.a AS a FROM z AS z; WITH z AS (SELECT a FROM x), q AS (SELECT * FROM z) SELECT * FROM q; -WITH z AS (SELECT x.a AS a FROM x AS x), q AS (SELECT z.a AS a FROM z) SELECT q.a AS a FROM q; +WITH z AS (SELECT x.a AS a FROM x AS x), q AS (SELECT z.a AS a FROM z AS z) SELECT q.a AS a FROM q AS q; WITH z AS (SELECT * FROM x) SELECT * FROM z UNION SELECT * FROM z; -WITH z AS (SELECT x.a AS a, x.b AS b FROM x AS x) SELECT z.a AS a, z.b AS b FROM z UNION SELECT z.a AS a, z.b AS b FROM z; +WITH z AS (SELECT x.a AS a, x.b AS b FROM x AS x) SELECT z.a AS a, z.b AS b FROM z AS z UNION SELECT z.a AS a, z.b AS b FROM z AS z; WITH z AS (SELECT * FROM x), q AS (SELECT b FROM z) SELECT b FROM q; -WITH z AS (SELECT x.a AS a, x.b AS b FROM x AS x), q AS (SELECT z.b AS b FROM z) SELECT q.b AS b FROM q; +WITH z AS (SELECT x.a AS a, x.b AS b FROM x AS x), q AS (SELECT z.b AS b FROM z AS z) SELECT q.b AS b FROM q AS q; WITH z AS ((SELECT b FROM x UNION ALL SELECT b FROM y) ORDER BY b) SELECT * FROM z; -WITH z AS ((SELECT x.b AS b FROM x AS x UNION ALL SELECT y.b AS b FROM y AS y) ORDER BY b) SELECT z.b AS b FROM z; +WITH z AS ((SELECT x.b AS b FROM x AS x UNION ALL SELECT y.b AS b FROM y AS y) ORDER BY b) SELECT z.b AS b FROM z AS z; WITH cte(x) AS (SELECT 1) SELECT * FROM cte AS cte(a); WITH cte AS (SELECT 1 AS x) SELECT cte.a AS a FROM cte AS cte(a); @@ -322,7 +335,7 @@ WITH cte AS (SELECT 1 AS x, 2 AS y) SELECT cte.a AS a, cte.y AS y FROM cte AS ct # execute: false WITH player AS (SELECT player.name, player.asset.info FROM players) SELECT * FROM player; -WITH player AS (SELECT players.player.name AS name, players.player.asset.info AS info FROM players AS players) SELECT player.name AS name, player.info AS info FROM player; +WITH player AS (SELECT players.player.name AS name, players.player.asset.info AS info FROM players AS players) SELECT player.name AS name, player.info AS info FROM player AS player; -------------------------------------- -- Except and Replace @@ -346,6 +359,9 @@ SELECT x.b AS b FROM x AS x; SELECT * EXCEPT (a, b) FROM x; SELECT * EXCEPT (x.a, x.b) FROM x AS x; +SELECT COALESCE(t1.a, '') AS a, t2.* EXCEPT (a) FROM x AS t1, x AS t2; +SELECT COALESCE(t1.a, '') AS a, t2.b AS b FROM x AS t1, x AS t2; + -------------------------------------- -- Using -------------------------------------- @@ -354,7 +370,7 @@ SELECT x.b AS b FROM x AS x JOIN y AS y ON x.b = y.b; # execute: false WITH cte AS (SELECT a.b.c.d.f.g FROM tbl1) SELECT g FROM (SELECT g FROM tbl2) tbl2 JOIN cte USING(g); -WITH cte AS (SELECT tbl1.a.b.c.d.f.g AS g FROM tbl1 AS tbl1) SELECT COALESCE(tbl2.g, cte.g) AS g FROM (SELECT tbl2.g AS g FROM tbl2 AS tbl2) AS tbl2 JOIN cte ON tbl2.g = cte.g; +WITH cte AS (SELECT tbl1.a.b.c.d.f.g AS g FROM tbl1 AS tbl1) SELECT COALESCE(tbl2.g, cte.g) AS g FROM (SELECT tbl2.g AS g FROM tbl2 AS tbl2) AS tbl2 JOIN cte AS cte ON tbl2.g = cte.g; SELECT x.b FROM x JOIN y USING (b) JOIN z USING (b); SELECT x.b AS b FROM x AS x JOIN y AS y ON x.b = y.b JOIN z AS z ON x.b = z.b; @@ -408,7 +424,7 @@ SELECT a FROM t1 JOIN t2 USING(a); SELECT COALESCE(t1.a, t2.a) AS a FROM t1 AS t1 JOIN t2 AS t2 ON t1.a = t2.a; WITH m(a) AS (SELECT 1), n(b) AS (SELECT 1) SELECT * FROM m JOIN n AS foo(a) USING (a); -WITH m AS (SELECT 1 AS a), n AS (SELECT 1 AS b) SELECT COALESCE(m.a, foo.a) AS a FROM m JOIN n AS foo(a) ON m.a = foo.a; +WITH m AS (SELECT 1 AS a), n AS (SELECT 1 AS b) SELECT COALESCE(m.a, foo.a) AS a FROM m AS m JOIN n AS foo(a) ON m.a = foo.a; -------------------------------------- -- Hint with table reference diff --git a/tests/fixtures/optimizer/qualify_columns_ddl.sql b/tests/fixtures/optimizer/qualify_columns_ddl.sql index 87e0f6d..907780b 100644 --- a/tests/fixtures/optimizer/qualify_columns_ddl.sql +++ b/tests/fixtures/optimizer/qualify_columns_ddl.sql @@ -1,6 +1,6 @@ # title: Create with CTE WITH cte AS (SELECT b FROM y) CREATE TABLE s AS SELECT * FROM cte; -WITH cte AS (SELECT y.b AS b FROM y AS y) CREATE TABLE s AS SELECT cte.b AS b FROM cte; +WITH cte AS (SELECT y.b AS b FROM y AS y) CREATE TABLE s AS SELECT cte.b AS b FROM cte AS cte; # title: Create without CTE CREATE TABLE foo AS SELECT a FROM tbl; @@ -8,15 +8,15 @@ CREATE TABLE foo AS SELECT tbl.a AS a FROM tbl AS tbl; # title: Create with complex CTE with derived table WITH cte AS (SELECT a FROM (SELECT a from x)) CREATE TABLE s AS SELECT * FROM cte; -WITH cte AS (SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0) CREATE TABLE s AS SELECT cte.a AS a FROM cte; +WITH cte AS (SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0) CREATE TABLE s AS SELECT cte.a AS a FROM cte AS cte; # title: Create wtih multiple CTEs WITH cte1 AS (SELECT b FROM y), cte2 AS (SELECT b FROM cte1) CREATE TABLE s AS SELECT * FROM cte2; -WITH cte1 AS (SELECT y.b AS b FROM y AS y), cte2 AS (SELECT cte1.b AS b FROM cte1) CREATE TABLE s AS SELECT cte2.b AS b FROM cte2; +WITH cte1 AS (SELECT y.b AS b FROM y AS y), cte2 AS (SELECT cte1.b AS b FROM cte1 AS cte1) CREATE TABLE s AS SELECT cte2.b AS b FROM cte2 AS cte2; # title: Create with multiple CTEs, selecting only from the first CTE (unnecessary code) WITH cte1 AS (SELECT b FROM y), cte2 AS (SELECT b FROM cte1) CREATE TABLE s AS SELECT * FROM cte1; -WITH cte1 AS (SELECT y.b AS b FROM y AS y), cte2 AS (SELECT cte1.b AS b FROM cte1) CREATE TABLE s AS SELECT cte1.b AS b FROM cte1; +WITH cte1 AS (SELECT y.b AS b FROM y AS y), cte2 AS (SELECT cte1.b AS b FROM cte1 AS cte1) CREATE TABLE s AS SELECT cte1.b AS b FROM cte1 AS cte1; # title: Create with multiple derived tables CREATE TABLE s AS SELECT * FROM (SELECT b FROM (SELECT b FROM y)); @@ -24,11 +24,11 @@ CREATE TABLE s AS SELECT _q_1.b AS b FROM (SELECT _q_0.b AS b FROM (SELECT y.b A # title: Create with a CTE and a derived table WITH cte AS (SELECT b FROM y) CREATE TABLE s AS SELECT * FROM (SELECT b FROM (SELECT b FROM cte)); -WITH cte AS (SELECT y.b AS b FROM y AS y) CREATE TABLE s AS SELECT _q_1.b AS b FROM (SELECT _q_0.b AS b FROM (SELECT cte.b AS b FROM cte) AS _q_0) AS _q_1; +WITH cte AS (SELECT y.b AS b FROM y AS y) CREATE TABLE s AS SELECT _q_1.b AS b FROM (SELECT _q_0.b AS b FROM (SELECT cte.b AS b FROM cte AS cte) AS _q_0) AS _q_1; # title: Insert with CTE WITH cte AS (SELECT b FROM y) INSERT INTO s SELECT * FROM cte; -WITH cte AS (SELECT y.b AS b FROM y AS y) INSERT INTO s SELECT cte.b AS b FROM cte; +WITH cte AS (SELECT y.b AS b FROM y AS y) INSERT INTO s SELECT cte.b AS b FROM cte AS cte; # title: Insert without CTE INSERT INTO foo SELECT a FROM tbl; diff --git a/tests/fixtures/optimizer/qualify_tables.sql b/tests/fixtures/optimizer/qualify_tables.sql index 0ebfcd3..1426aa7 100644 --- a/tests/fixtures/optimizer/qualify_tables.sql +++ b/tests/fixtures/optimizer/qualify_tables.sql @@ -19,9 +19,13 @@ SELECT 1 FROM x.y.z AS z; SELECT 1 FROM y.z AS z, z.a; SELECT 1 FROM c.y.z AS z, z.a; -# title: cte can't be qualified +# title: single cte WITH a AS (SELECT 1 FROM z) SELECT 1 FROM a; -WITH a AS (SELECT 1 FROM c.db.z AS z) SELECT 1 FROM a; +WITH a AS (SELECT 1 FROM c.db.z AS z) SELECT 1 FROM a AS a; + +# title: two ctes that are self-joined +WITH a AS (SELECT 1 FROM z) SELECT 1 FROM a CROSS JOIN a; +WITH a AS (SELECT 1 FROM c.db.z AS z) SELECT 1 FROM a AS a CROSS JOIN a AS a; # title: query that yields a single column as projection SELECT (SELECT y.c FROM y AS y) FROM x; @@ -130,3 +134,15 @@ SELECT x FROM c.db.t AS t, LATERAL UNNEST(t.xs) AS _q_0; # title: table with ordinality SELECT * FROM t CROSS JOIN JSON_ARRAY_ELEMENTS(t.response) WITH ORDINALITY AS kv_json; SELECT * FROM c.db.t AS t CROSS JOIN JSON_ARRAY_ELEMENTS(t.response) WITH ORDINALITY AS kv_json; + +# title: alter table +ALTER TABLE t ADD PRIMARY KEY (id) NOT ENFORCED; +ALTER TABLE c.db.t ADD PRIMARY KEY (id) NOT ENFORCED; + +# title: create statement with cte +CREATE TABLE t1 AS (WITH cte AS (SELECT x FROM t2) SELECT * FROM cte); +CREATE TABLE c.db.t1 AS (WITH cte AS (SELECT x FROM c.db.t2 AS t2) SELECT * FROM cte AS cte); + +# title: insert statement with cte +WITH cte AS (SELECT b FROM y) INSERT INTO s SELECT * FROM cte; +WITH cte AS (SELECT b FROM c.db.y AS y) INSERT INTO c.db.s SELECT * FROM cte AS cte; diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql index d38c3cc..5ea51e0 100644 --- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql +++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql @@ -191,7 +191,7 @@ WITH "wscs" AS ( ELSE NULL END ) AS "sat_sales" - FROM "wscs" + FROM "wscs" AS "wscs" JOIN "date_dim" AS "date_dim" ON "date_dim"."d_date_sk" = "wscs"."sold_date_sk" GROUP BY @@ -206,7 +206,7 @@ SELECT ROUND("wswscs"."thu_sales" / "wswscs_2"."thu_sales", 2) AS "_col_5", ROUND("wswscs"."fri_sales" / "wswscs_2"."fri_sales", 2) AS "_col_6", ROUND("wswscs"."sat_sales" / "wswscs_2"."sat_sales", 2) AS "_col_7" -FROM "wswscs" +FROM "wswscs" AS "wswscs" JOIN "date_dim" AS "date_dim" ON "date_dim"."d_week_seq" = "wswscs"."d_week_seq" AND "date_dim"."d_year" = 1998 JOIN "wswscs" AS "wswscs_2" @@ -821,7 +821,7 @@ WITH "salesreturns" AS ( "ssr"."sales" AS "sales", "ssr"."returns1" AS "returns1", "ssr"."profit" - "ssr"."profit_loss" AS "profit" - FROM "ssr" + FROM "ssr" AS "ssr" UNION ALL SELECT 'catalog channel' AS "channel", @@ -829,7 +829,7 @@ WITH "salesreturns" AS ( "csr"."sales" AS "sales", "csr"."returns1" AS "returns1", "csr"."profit" - "csr"."profit_loss" AS "profit" - FROM "csr" + FROM "csr" AS "csr" UNION ALL SELECT 'web channel' AS "channel", @@ -837,7 +837,7 @@ WITH "salesreturns" AS ( "wsr"."sales" AS "sales", "wsr"."returns1" AS "returns1", "wsr"."profit" - "wsr"."profit_loss" AS "profit" - FROM "wsr" + FROM "wsr" AS "wsr" ) SELECT "x"."channel" AS "channel", @@ -2203,7 +2203,7 @@ WITH "item_2" AS ( ), "_u_1" AS ( SELECT "avg_sales"."average_sales" AS "average_sales" - FROM "avg_sales" + FROM "avg_sales" AS "avg_sales" ), "_u_0" AS ( SELECT "item"."i_item_sk" AS "ss_item_sk" @@ -2996,7 +2996,7 @@ WITH "frequent_ss_items" AS ( SELECT "customer"."c_customer_sk" AS "c_customer_sk" FROM "store_sales" AS "store_sales" - CROSS JOIN "max_store_sales" + CROSS JOIN "max_store_sales" AS "max_store_sales" JOIN "customer_2" AS "customer" ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk" GROUP BY @@ -3006,13 +3006,13 @@ WITH "frequent_ss_items" AS ( ), "_u_1" AS ( SELECT "frequent_ss_items"."item_sk" AS "item_sk" - FROM "frequent_ss_items" + FROM "frequent_ss_items" AS "frequent_ss_items" GROUP BY "frequent_ss_items"."item_sk" ), "_u_2" AS ( SELECT "best_ss_customer"."c_customer_sk" AS "c_customer_sk" - FROM "best_ss_customer" + FROM "best_ss_customer" AS "best_ss_customer" GROUP BY "best_ss_customer"."c_customer_sk" ), "date_dim_4" AS ( @@ -3137,14 +3137,14 @@ WITH "ssales" AS ( ), "_u_0" AS ( SELECT 0.05 * AVG("ssales"."netpaid") AS "_col_0" - FROM "ssales" + FROM "ssales" AS "ssales" ) SELECT "ssales"."c_last_name" AS "c_last_name", "ssales"."c_first_name" AS "c_first_name", "ssales"."s_store_name" AS "s_store_name", SUM("ssales"."netpaid") AS "paid" -FROM "ssales" +FROM "ssales" AS "ssales" CROSS JOIN "_u_0" AS "_u_0" WHERE "ssales"."i_color" = 'papaya' @@ -4095,17 +4095,17 @@ WITH "customer_address_2" AS ( SELECT "ss"."i_manufact_id" AS "i_manufact_id", "ss"."total_sales" AS "total_sales" - FROM "ss" + FROM "ss" AS "ss" UNION ALL SELECT "cs"."i_manufact_id" AS "i_manufact_id", "cs"."total_sales" AS "total_sales" - FROM "cs" + FROM "cs" AS "cs" UNION ALL SELECT "ws"."i_manufact_id" AS "i_manufact_id", "ws"."total_sales" AS "total_sales" - FROM "ws" + FROM "ws" AS "ws" ) SELECT "tmp1"."i_manufact_id" AS "i_manufact_id", @@ -5534,7 +5534,7 @@ SELECT "v1"."sum_sales" AS "sum_sales", "v1_lag"."sum_sales" AS "psum", "v1_lead"."sum_sales" AS "nsum" -FROM "v1" +FROM "v1" AS "v1" JOIN "v1" AS "v1_lag" ON "v1"."i_brand" = "v1_lag"."i_brand" AND "v1"."i_category" = "v1_lag"."i_category" @@ -6455,7 +6455,7 @@ WITH "cs_or_ws_sales" AS ( ), "my_revenue" AS ( SELECT SUM("store_sales"."ss_ext_sales_price") AS "revenue" - FROM "my_customers" + FROM "my_customers" AS "my_customers" JOIN "customer_address" AS "customer_address" ON "customer_address"."ca_address_sk" = "my_customers"."c_current_addr_sk" JOIN "store_sales" AS "store_sales" @@ -6480,7 +6480,7 @@ SELECT CAST(( "my_revenue"."revenue" / 50 ) AS INT) * 50 AS "segment_base" -FROM "my_revenue" +FROM "my_revenue" AS "my_revenue" GROUP BY CAST(( "my_revenue"."revenue" / 50 @@ -6681,17 +6681,17 @@ WITH "customer_address_2" AS ( SELECT "ss"."i_item_id" AS "i_item_id", "ss"."total_sales" AS "total_sales" - FROM "ss" + FROM "ss" AS "ss" UNION ALL SELECT "cs"."i_item_id" AS "i_item_id", "cs"."total_sales" AS "total_sales" - FROM "cs" + FROM "cs" AS "cs" UNION ALL SELECT "ws"."i_item_id" AS "i_item_id", "ws"."total_sales" AS "total_sales" - FROM "ws" + FROM "ws" AS "ws" ) SELECT "tmp1"."i_item_id" AS "i_item_id", @@ -6813,7 +6813,7 @@ SELECT "v1"."sum_sales" AS "sum_sales", "v1_lag"."sum_sales" AS "psum", "v1_lead"."sum_sales" AS "nsum" -FROM "v1" +FROM "v1" AS "v1" JOIN "v1" AS "v1_lag" ON "v1"."cc_name" = "v1_lag"."cc_name" AND "v1"."i_brand" = "v1_lag"."i_brand" @@ -7014,14 +7014,14 @@ SELECT ( "ss_items"."ss_item_rev" + "cs_items"."cs_item_rev" + "ws_items"."ws_item_rev" ) / 3 AS "average" -FROM "ss_items" -JOIN "cs_items" +FROM "ss_items" AS "ss_items" +JOIN "cs_items" AS "cs_items" ON "cs_items"."cs_item_rev" <= 1.1 * "ss_items"."ss_item_rev" AND "cs_items"."cs_item_rev" >= 0.9 * "ss_items"."ss_item_rev" AND "cs_items"."item_id" = "ss_items"."item_id" AND "ss_items"."ss_item_rev" <= 1.1 * "cs_items"."cs_item_rev" AND "ss_items"."ss_item_rev" >= 0.9 * "cs_items"."cs_item_rev" -JOIN "ws_items" +JOIN "ws_items" AS "ws_items" ON "cs_items"."cs_item_rev" <= 1.1 * "ws_items"."ws_item_rev" AND "cs_items"."cs_item_rev" >= 0.9 * "ws_items"."ws_item_rev" AND "ss_items"."item_id" = "ws_items"."item_id" @@ -7193,7 +7193,7 @@ WITH "wss" AS ( "wss"."thu_sales" AS "thu_sales2", "wss"."fri_sales" AS "fri_sales2", "wss"."sat_sales" AS "sat_sales2" - FROM "wss" + FROM "wss" AS "wss" JOIN "date_dim" AS "d" ON "d"."d_month_seq" <= 1219 AND "d"."d_month_seq" >= 1208 @@ -7212,7 +7212,7 @@ SELECT "wss"."thu_sales" / "x"."thu_sales2" AS "_col_7", "wss"."fri_sales" / "x"."fri_sales2" AS "_col_8", "wss"."sat_sales" / "x"."sat_sales2" AS "_col_9" -FROM "wss" +FROM "wss" AS "wss" JOIN "date_dim" AS "d" ON "d"."d_month_seq" <= 1207 AND "d"."d_month_seq" >= 1196 @@ -7379,17 +7379,17 @@ WITH "customer_address_2" AS ( SELECT "ss"."i_item_id" AS "i_item_id", "ss"."total_sales" AS "total_sales" - FROM "ss" + FROM "ss" AS "ss" UNION ALL SELECT "cs"."i_item_id" AS "i_item_id", "cs"."total_sales" AS "total_sales" - FROM "cs" + FROM "cs" AS "cs" UNION ALL SELECT "ws"."i_item_id" AS "i_item_id", "ws"."total_sales" AS "total_sales" - FROM "ws" + FROM "ws" AS "ws" ) SELECT "tmp1"."i_item_id" AS "i_item_id", @@ -7921,7 +7921,7 @@ WITH "cs_ui" AS ( ON "ad1"."ca_address_sk" = "store_sales"."ss_addr_sk" JOIN "customer_demographics" AS "cd1" ON "cd1"."cd_demo_sk" = "store_sales"."ss_cdemo_sk" - JOIN "cs_ui" + JOIN "cs_ui" AS "cs_ui" ON "cs_ui"."cs_item_sk" = "store_sales"."ss_item_sk" JOIN "customer" AS "customer" ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk" @@ -10343,8 +10343,8 @@ WITH "date_dim_2" AS ( "ss"."sales" AS "sales", COALESCE("sr"."returns1", 0) AS "returns1", "ss"."profit" - COALESCE("sr"."profit_loss", 0) AS "profit" - FROM "ss" - LEFT JOIN "sr" + FROM "ss" AS "ss" + LEFT JOIN "sr" AS "sr" ON "sr"."s_store_sk" = "ss"."s_store_sk" UNION ALL SELECT @@ -10353,8 +10353,8 @@ WITH "date_dim_2" AS ( "cs"."sales" AS "sales", "cr"."returns1" AS "returns1", "cs"."profit" - "cr"."profit_loss" AS "profit" - FROM "cs" - CROSS JOIN "cr" + FROM "cs" AS "cs" + CROSS JOIN "cr" AS "cr" UNION ALL SELECT 'web channel' AS "channel", @@ -10362,8 +10362,8 @@ WITH "date_dim_2" AS ( "ws"."sales" AS "sales", COALESCE("wr"."returns1", 0) AS "returns1", "ws"."profit" - COALESCE("wr"."profit_loss", 0) AS "profit" - FROM "ws" - LEFT JOIN "wr" + FROM "ws" AS "ws" + LEFT JOIN "wr" AS "wr" ON "wr"."wp_web_page_sk" = "ws"."wp_web_page_sk" ) SELECT @@ -10546,12 +10546,12 @@ SELECT COALESCE("ws"."ws_qty", 0) + COALESCE("cs"."cs_qty", 0) AS "other_chan_qty", COALESCE("ws"."ws_wc", 0) + COALESCE("cs"."cs_wc", 0) AS "other_chan_wholesale_cost", COALESCE("ws"."ws_sp", 0) + COALESCE("cs"."cs_sp", 0) AS "other_chan_sales_price" -FROM "ss" -LEFT JOIN "cs" +FROM "ss" AS "ss" +LEFT JOIN "cs" AS "cs" ON "cs"."cs_customer_sk" = "ss"."ss_customer_sk" AND "cs"."cs_item_sk" = "cs"."cs_item_sk" AND "cs"."cs_sold_year" = "ss"."ss_sold_year" -LEFT JOIN "ws" +LEFT JOIN "ws" AS "ws" 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" @@ -10850,7 +10850,7 @@ WITH "date_dim_2" AS ( "ssr"."sales" AS "sales", "ssr"."returns1" AS "returns1", "ssr"."profit" AS "profit" - FROM "ssr" + FROM "ssr" AS "ssr" UNION ALL SELECT 'catalog channel' AS "channel", @@ -10858,7 +10858,7 @@ WITH "date_dim_2" AS ( "csr"."sales" AS "sales", "csr"."returns1" AS "returns1", "csr"."profit" AS "profit" - FROM "csr" + FROM "csr" AS "csr" UNION ALL SELECT 'web channel' AS "channel", @@ -10866,7 +10866,7 @@ WITH "date_dim_2" AS ( "wsr"."sales" AS "sales", "wsr"."returns1" AS "returns1", "wsr"."profit" AS "profit" - FROM "wsr" + FROM "wsr" AS "wsr" ) SELECT "x"."channel" AS "channel", @@ -11249,10 +11249,10 @@ SELECT ( "sr_items"."sr_item_qty" + "cr_items"."cr_item_qty" + "wr_items"."wr_item_qty" ) / 3.0 AS "average" -FROM "sr_items" -JOIN "cr_items" +FROM "sr_items" AS "sr_items" +JOIN "cr_items" AS "cr_items" ON "cr_items"."item_id" = "sr_items"."item_id" -JOIN "wr_items" +JOIN "wr_items" AS "wr_items" ON "sr_items"."item_id" = "wr_items"."item_id" ORDER BY "sr_items"."item_id", @@ -12312,14 +12312,14 @@ WITH "ws_wh" AS ( ), "_u_0" AS ( SELECT "ws_wh"."ws_order_number" AS "ws_order_number" - FROM "ws_wh" + FROM "ws_wh" AS "ws_wh" GROUP BY "ws_wh"."ws_order_number" ), "_u_1" AS ( SELECT "web_returns"."wr_order_number" AS "wr_order_number" FROM "web_returns" AS "web_returns" - JOIN "ws_wh" + JOIN "ws_wh" AS "ws_wh" ON "web_returns"."wr_order_number" = "ws_wh"."ws_order_number" GROUP BY "web_returns"."wr_order_number" @@ -12475,8 +12475,8 @@ SELECT ELSE 0 END ) AS "store_and_catalog" -FROM "ssci" -FULL JOIN "csci" +FROM "ssci" AS "ssci" +FULL JOIN "csci" AS "csci" ON "csci"."customer_sk" = "ssci"."customer_sk" AND "csci"."item_sk" = "ssci"."item_sk" LIMIT 100; diff --git a/tests/fixtures/optimizer/tpc-h/tpc-h.sql b/tests/fixtures/optimizer/tpc-h/tpc-h.sql index 36f096c..bf624da 100644 --- a/tests/fixtures/optimizer/tpc-h/tpc-h.sql +++ b/tests/fixtures/optimizer/tpc-h/tpc-h.sql @@ -882,7 +882,7 @@ WITH "revenue" AS ( ), "_u_0" AS ( SELECT MAX("revenue"."total_revenue") AS "_col_0" - FROM "revenue" + FROM "revenue" AS "revenue" ) SELECT "supplier"."s_suppkey" AS "s_suppkey", @@ -891,7 +891,7 @@ SELECT "supplier"."s_phone" AS "s_phone", "revenue"."total_revenue" AS "total_revenue" FROM "supplier" AS "supplier" -JOIN "revenue" +JOIN "revenue" AS "revenue" ON "revenue"."supplier_no" = "supplier"."s_suppkey" JOIN "_u_0" AS "_u_0" ON "_u_0"."_col_0" = "revenue"."total_revenue" diff --git a/tests/fixtures/optimizer/unnest_subqueries.sql b/tests/fixtures/optimizer/unnest_subqueries.sql index f83191d..3d1e7ec 100644 --- a/tests/fixtures/optimizer/unnest_subqueries.sql +++ b/tests/fixtures/optimizer/unnest_subqueries.sql @@ -41,7 +41,7 @@ LEFT JOIN ( GROUP BY y.a ) AS _u_1 - ON x.a = "_u_1"."a" + ON x.a = _u_1.a LEFT JOIN ( SELECT y.b AS b @@ -49,7 +49,7 @@ LEFT JOIN ( GROUP BY y.b ) AS _u_2 - ON x.a = "_u_2"."b" + ON x.a = _u_2.b LEFT JOIN ( SELECT y.a AS a @@ -57,7 +57,7 @@ LEFT JOIN ( GROUP BY y.a ) AS _u_3 - ON x.a = "_u_3"."a" + ON x.a = _u_3.a LEFT JOIN ( SELECT SUM(y.b) AS b, @@ -157,9 +157,9 @@ LEFT JOIN ( ON _u_21._u_22 = x.a WHERE x.a = _u_0.a - AND NOT "_u_1"."a" IS NULL - AND NOT "_u_2"."b" IS NULL - AND NOT "_u_3"."a" IS NULL + AND NOT _u_1.a IS NULL + AND NOT _u_2.b IS NULL + AND NOT _u_3.a IS NULL AND x.a = _u_4.b AND x.a > _u_6.b AND x.a = _u_8.a -- cgit v1.2.3