summaryrefslogtreecommitdiffstats
path: root/tests/fixtures/optimizer
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2023-07-14 05:43:02 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2023-07-14 05:43:02 +0000
commitc8d4d4ead5df1c84966431eec8b88e974414dafc (patch)
tree847e292ab98fe79a3a78cd6797b564b73f4d2681 /tests/fixtures/optimizer
parentReleasing debian version 17.3.0-1. (diff)
downloadsqlglot-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 'tests/fixtures/optimizer')
-rw-r--r--tests/fixtures/optimizer/optimizer.sql105
-rw-r--r--tests/fixtures/optimizer/qualify_columns.sql4
-rw-r--r--tests/fixtures/optimizer/qualify_tables.sql50
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;