diff options
author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2023-07-14 05:43:02 +0000 |
---|---|---|
committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2023-07-14 05:43:02 +0000 |
commit | c8d4d4ead5df1c84966431eec8b88e974414dafc (patch) | |
tree | 847e292ab98fe79a3a78cd6797b564b73f4d2681 /tests/fixtures/optimizer | |
parent | Releasing debian version 17.3.0-1. (diff) | |
download | sqlglot-c8d4d4ead5df1c84966431eec8b88e974414dafc.tar.xz sqlglot-c8d4d4ead5df1c84966431eec8b88e974414dafc.zip |
Merging upstream version 17.4.1.
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to '')
-rw-r--r-- | tests/fixtures/optimizer/optimizer.sql | 105 | ||||
-rw-r--r-- | tests/fixtures/optimizer/qualify_columns.sql | 4 | ||||
-rw-r--r-- | tests/fixtures/optimizer/qualify_tables.sql | 50 |
3 files changed, 134 insertions, 25 deletions
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql index 38e64d7..4b58ea5 100644 --- a/tests/fixtures/optimizer/optimizer.sql +++ b/tests/fixtures/optimizer/optimizer.sql @@ -701,3 +701,108 @@ SELECT "x"."a" * "x"."b" - "x"."b" ) AS "f" FROM "x" AS "x"; + +# title: wrapped table without alias +# execute: false +SELECT * FROM (tbl); +SELECT + * +FROM ( + "tbl" AS "tbl" +); + +# title: wrapped table with alias +# execute: false +SELECT * FROM (tbl AS tbl); +SELECT + * +FROM ( + "tbl" AS "tbl" +); + +# title: wrapped join of tables without alias +SELECT a, c FROM (x LEFT JOIN y ON a = c); +SELECT + "x"."a" AS "a", + "y"."c" AS "c" +FROM ( + "x" AS "x" + LEFT JOIN "y" AS "y" + ON "x"."a" = "y"."c" +); + +# title: wrapped join of tables with alias +# execute: false +SELECT a, c FROM (x LEFT JOIN y ON a = c) AS t; +SELECT + "x"."a" AS "a", + "y"."c" AS "c" +FROM "x" AS "x" +LEFT JOIN "y" AS "y" + ON "x"."a" = "y"."c"; + +# title: chained wrapped joins without aliases +# execute: false +SELECT * FROM ((a CROSS JOIN ((b CROSS JOIN c) CROSS JOIN (d CROSS JOIN e)))); +SELECT + * +FROM ( + ( + "a" AS "a" + CROSS JOIN ( + ( + "b" AS "b" + CROSS JOIN "c" AS "c" + ) + CROSS JOIN ( + "d" AS "d" + CROSS JOIN "e" AS "e" + ) + ) + ) +); + +# title: chained wrapped joins with aliases +# execute: false +SELECT * FROM ((a AS foo CROSS JOIN b AS bar) CROSS JOIN c AS baz); +SELECT + * +FROM ( + ( + "a" AS "foo" + CROSS JOIN "b" AS "bar" + ) + CROSS JOIN "c" AS "baz" +); + +# title: table joined with join construct +SELECT x.a, y.b, z.c FROM x LEFT JOIN (y INNER JOIN z ON y.c = z.c) ON x.b = y.b; +SELECT + "x"."a" AS "a", + "y"."b" AS "b", + "z"."c" AS "c" +FROM "x" AS "x" +LEFT JOIN ( + "y" AS "y" + JOIN "z" AS "z" + ON "y"."c" = "z"."c" +) + ON "x"."b" = "y"."b"; + +# title: select * from table joined with join construct +# execute: false +SELECT * FROM x LEFT JOIN (y INNER JOIN z ON y.c = z.c) ON x.b = y.b; +SELECT + "y"."b" AS "b", + "y"."c" AS "c", + "z"."a" AS "a", + "z"."c" AS "c", + "x"."a" AS "a", + "x"."b" AS "b" +FROM "x" AS "x" +LEFT JOIN ( + "y" AS "y" + JOIN "z" AS "z" + ON "y"."c" = "z"."c" +) + ON "x"."b" = "y"."b"; diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql index f7ece5b..487a831 100644 --- a/tests/fixtures/optimizer/qualify_columns.sql +++ b/tests/fixtures/optimizer/qualify_columns.sql @@ -430,6 +430,10 @@ SELECT x.a AS a, x.b AS b FROM x AS x QUALIFY COUNT(x.a) OVER (PARTITION BY x.b) -- Expand laterals -------------------------------------- +# execute: false +select 2 AS d, d + 1 FROM x WHERE d = 2 GROUP BY d; +SELECT 2 AS d, 2 + 1 AS _col_1 FROM x AS x WHERE 2 = 2 GROUP BY 1; + # title: expand alias reference SELECT x.a + 1 AS i, diff --git a/tests/fixtures/optimizer/qualify_tables.sql b/tests/fixtures/optimizer/qualify_tables.sql index d8ce2b0..cd749a0 100644 --- a/tests/fixtures/optimizer/qualify_tables.sql +++ b/tests/fixtures/optimizer/qualify_tables.sql @@ -26,45 +26,49 @@ SELECT (SELECT y.c FROM c.db.y AS y) FROM c.db.x AS x; SELECT * FROM x PIVOT (SUM(a) FOR b IN ('a', 'b')); SELECT * FROM c.db.x AS x PIVOT(SUM(a) FOR b IN ('a', 'b')) AS _q_0; ------------------------------------------------------------ ---- Unnest wrapped tables / joins, expand join constructs ------------------------------------------------------------ - # title: wrapped table without alias SELECT * FROM (tbl); -SELECT * FROM c.db.tbl AS tbl; +SELECT * FROM (c.db.tbl AS tbl); # title: wrapped table with alias SELECT * FROM (tbl AS tbl); -SELECT * FROM c.db.tbl AS tbl; +SELECT * FROM (c.db.tbl AS tbl); -# title: wrapped table with alias and multiple redundant parentheses +# title: wrapped table with alias using multiple (redundant) parentheses SELECT * FROM ((((tbl AS tbl)))); -SELECT * FROM c.db.tbl AS tbl; +SELECT * FROM ((((c.db.tbl AS tbl)))); + +# title: wrapped join of tables without alias +SELECT * FROM (t1 CROSS JOIN t2); +SELECT * FROM (c.db.t1 AS t1 CROSS JOIN c.db.t2 AS t2); + +# title: wrapped join of tables with alias, expansion of join construct +SELECT * FROM (t1 CROSS JOIN t2) AS t; +SELECT * FROM (SELECT * FROM c.db.t1 AS t1 CROSS JOIN c.db.t2 AS t2) AS t; # title: chained wrapped joins without aliases (1) SELECT * FROM ((a CROSS JOIN b) CROSS JOIN c); -SELECT * FROM c.db.a AS a CROSS JOIN c.db.b AS b CROSS JOIN c.db.c AS c; +SELECT * FROM ((c.db.a AS a CROSS JOIN c.db.b AS b) CROSS JOIN c.db.c AS c); # title: chained wrapped joins without aliases (2) SELECT * FROM (a CROSS JOIN (b CROSS JOIN c)); -SELECT * FROM c.db.a AS a CROSS JOIN c.db.b AS b CROSS JOIN c.db.c AS c; +SELECT * FROM (c.db.a AS a CROSS JOIN (c.db.b AS b CROSS JOIN c.db.c AS c)); # title: chained wrapped joins without aliases (3) SELECT * FROM ((a CROSS JOIN ((b CROSS JOIN c) CROSS JOIN d))); -SELECT * FROM c.db.a AS a CROSS JOIN c.db.b AS b CROSS JOIN c.db.c AS c CROSS JOIN c.db.d AS d; +SELECT * FROM ((c.db.a AS a CROSS JOIN ((c.db.b AS b CROSS JOIN c.db.c AS c) CROSS JOIN c.db.d AS d))); # title: chained wrapped joins without aliases (4) SELECT * FROM ((a CROSS JOIN ((b CROSS JOIN c) CROSS JOIN (d CROSS JOIN e)))); -SELECT * FROM c.db.a AS a CROSS JOIN c.db.b AS b CROSS JOIN c.db.c AS c CROSS JOIN c.db.d AS d CROSS JOIN c.db.e AS e; +SELECT * FROM ((c.db.a AS a CROSS JOIN ((c.db.b AS b CROSS JOIN c.db.c AS c) CROSS JOIN (c.db.d AS d CROSS JOIN c.db.e AS e)))); # title: chained wrapped joins with aliases SELECT * FROM ((a AS foo CROSS JOIN b AS bar) CROSS JOIN c AS baz); -SELECT * FROM c.db.a AS foo CROSS JOIN c.db.b AS bar CROSS JOIN c.db.c AS baz; +SELECT * FROM ((c.db.a AS foo CROSS JOIN c.db.b AS bar) CROSS JOIN c.db.c AS baz); # title: wrapped join with subquery without alias SELECT * FROM (tbl1 CROSS JOIN (SELECT * FROM tbl2) AS t1); -SELECT * FROM c.db.tbl1 AS tbl1 CROSS JOIN (SELECT * FROM c.db.tbl2 AS tbl2) AS t1; +SELECT * FROM (c.db.tbl1 AS tbl1 CROSS JOIN (SELECT * FROM c.db.tbl2 AS tbl2) AS t1); # title: wrapped join with subquery with alias, parentheses can't be omitted because of alias SELECT * FROM (tbl1 CROSS JOIN (SELECT * FROM tbl2) AS t1) AS t2; @@ -72,32 +76,28 @@ SELECT * FROM (SELECT * FROM c.db.tbl1 AS tbl1 CROSS JOIN (SELECT * FROM c.db.tb # title: join construct as the right operand of a left join SELECT * FROM a LEFT JOIN (b INNER JOIN c ON c.id = b.id) ON b.id = a.id; -SELECT * FROM c.db.a AS a LEFT JOIN c.db.b AS b ON b.id = a.id INNER JOIN c.db.c AS c ON c.id = b.id; +SELECT * FROM c.db.a AS a LEFT JOIN (c.db.b AS b INNER JOIN c.db.c AS c ON c.id = b.id) ON b.id = a.id; -# title: nested joins converted to canonical form +# title: nested joins SELECT * FROM a LEFT JOIN b INNER JOIN c ON c.id = b.id ON b.id = a.id; -SELECT * FROM c.db.a AS a LEFT JOIN c.db.b AS b ON b.id = a.id INNER JOIN c.db.c AS c ON c.id = b.id; +SELECT * FROM c.db.a AS a LEFT JOIN c.db.b AS b INNER JOIN c.db.c AS c ON c.id = b.id ON b.id = a.id; # title: parentheses can't be omitted because alias shadows inner table names SELECT t.a FROM (tbl AS tbl) AS t; SELECT t.a FROM (SELECT * FROM c.db.tbl AS tbl) AS t; -# title: outermost set of parentheses can't be omitted due to shadowing (1) -SELECT * FROM ((tbl AS tbl)) AS _q_0; -SELECT * FROM (SELECT * FROM c.db.tbl AS tbl) AS _q_0; - -# title: outermost set of parentheses can't be omitted due to shadowing (2) +# title: wrapped aliased table with outer alias SELECT * FROM ((((tbl AS tbl)))) AS _q_0; SELECT * FROM (SELECT * FROM c.db.tbl AS tbl) AS _q_0; -# title: join construct with three tables in canonical form +# title: join construct with three tables SELECT * FROM (tbl1 AS tbl1 JOIN tbl2 AS tbl2 ON id1 = id2 JOIN tbl3 AS tbl3 ON id1 = id3) AS _q_0; SELECT * FROM (SELECT * FROM c.db.tbl1 AS tbl1 JOIN c.db.tbl2 AS tbl2 ON id1 = id2 JOIN c.db.tbl3 AS tbl3 ON id1 = id3) AS _q_0; -# title: join construct with three tables in canonical form and redundant set of parentheses +# title: join construct with three tables and redundant set of parentheses SELECT * FROM ((tbl1 AS tbl1 JOIN tbl2 AS tbl2 ON id1 = id2 JOIN tbl3 AS tbl3 ON id1 = id3)) AS _q_0; SELECT * FROM (SELECT * FROM c.db.tbl1 AS tbl1 JOIN c.db.tbl2 AS tbl2 ON id1 = id2 JOIN c.db.tbl3 AS tbl3 ON id1 = id3) AS _q_0; -# title: nested join construct in canonical form +# title: join construct within join construct SELECT * FROM (tbl1 AS tbl1 JOIN (tbl2 AS tbl2 JOIN tbl3 AS tbl3 ON id2 = id3) AS _q_0 ON id1 = id3) AS _q_1; SELECT * FROM (SELECT * FROM c.db.tbl1 AS tbl1 JOIN (SELECT * FROM c.db.tbl2 AS tbl2 JOIN c.db.tbl3 AS tbl3 ON id2 = id3) AS _q_0 ON id1 = id3) AS _q_1; |