summaryrefslogtreecommitdiffstats
path: root/tests/fixtures/optimizer/optimizer.sql
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2023-07-24 08:03:45 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2023-07-24 08:03:45 +0000
commitca57dc468e5d8d0920e964d45ad25271ae6e633d (patch)
tree319d8bffcb5c3e9afe9e62beca9ef401480578d2 /tests/fixtures/optimizer/optimizer.sql
parentAdding upstream version 17.4.1. (diff)
downloadsqlglot-ca57dc468e5d8d0920e964d45ad25271ae6e633d.tar.xz
sqlglot-ca57dc468e5d8d0920e964d45ad25271ae6e633d.zip
Adding upstream version 17.7.0.upstream/17.7.0
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'tests/fixtures/optimizer/optimizer.sql')
-rw-r--r--tests/fixtures/optimizer/optimizer.sql84
1 files changed, 84 insertions, 0 deletions
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index 4b58ea5..14f5cfe 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -806,3 +806,87 @@ LEFT JOIN (
ON "y"."c" = "z"."c"
)
ON "x"."b" = "y"."b";
+
+# title: select * from wrapped subquery
+# execute: false
+SELECT * FROM ((SELECT * FROM tbl));
+WITH "_q_0" AS (
+ SELECT
+ *
+ FROM "tbl" AS "tbl"
+)
+SELECT
+ *
+FROM (
+ "_q_0" AS "_q_0"
+);
+
+# title: select * from wrapped subquery joined to a table (known schema)
+SELECT * FROM ((SELECT * FROM x) INNER JOIN y ON a = c);
+SELECT
+ "x"."a" AS "a",
+ "x"."b" AS "b",
+ "y"."b" AS "b",
+ "y"."c" AS "c"
+FROM (
+ "x" AS "x"
+ JOIN "y" AS "y"
+ ON "x"."a" = "y"."c"
+);
+
+# title: select * from wrapped subquery joined to a table (unknown schema)
+# execute: false
+SELECT * FROM ((SELECT c FROM t1) JOIN t2);
+WITH "_q_0" AS (
+ SELECT
+ "t1"."c" AS "c"
+ FROM "t1" AS "t1"
+)
+SELECT
+ *
+FROM (
+ "_q_0" AS "_q_0"
+ CROSS JOIN "t2" AS "t2"
+);
+
+# title: select specific columns from wrapped subquery joined to a table
+SELECT b FROM ((SELECT a FROM x) INNER JOIN y ON a = b);
+SELECT
+ "y"."b" AS "b"
+FROM (
+ "x" AS "x"
+ JOIN "y" AS "y"
+ ON "x"."a" = "y"."b"
+);
+
+# title: select * from wrapped join of subqueries (unknown schema)
+# execute: false
+SELECT * FROM ((SELECT * FROM t1) JOIN (SELECT * FROM t2));
+WITH "_q_0" AS (
+ SELECT
+ *
+ FROM "t1" AS "t1"
+), "_q_1" AS (
+ SELECT
+ *
+ FROM "t2" AS "t2"
+)
+SELECT
+ *
+FROM (
+ "_q_0" AS "_q_0"
+ CROSS JOIN "_q_1" AS "_q_1"
+);
+
+# title: select * from wrapped join of subqueries (known schema)
+SELECT * FROM ((SELECT * FROM x) INNER JOIN (SELECT * FROM y) ON a = c);
+SELECT
+ "x"."a" AS "a",
+ "x"."b" AS "b",
+ "y"."b" AS "b",
+ "y"."c" AS "c"
+FROM (
+ "x" AS "x"
+ JOIN "y" AS "y"
+ ON "x"."a" = "y"."c"
+);