diff options
Diffstat (limited to 'tests/fixtures/optimizer/qualify_columns.sql')
| -rw-r--r-- | tests/fixtures/optimizer/qualify_columns.sql | 36 |
1 files changed, 26 insertions, 10 deletions
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 |