summaryrefslogtreecommitdiffstats
path: root/tests/fixtures/optimizer/optimizer.sql
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2023-01-04 07:24:05 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2023-01-04 07:24:05 +0000
commit621555af37594a213d91ea113d5fc7739af84d40 (patch)
tree5aaa3b586692062accffc21cfaaa5a3917ee77b3 /tests/fixtures/optimizer/optimizer.sql
parentAdding upstream version 10.2.9. (diff)
downloadsqlglot-621555af37594a213d91ea113d5fc7739af84d40.tar.xz
sqlglot-621555af37594a213d91ea113d5fc7739af84d40.zip
Adding upstream version 10.4.2.upstream/10.4.2
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.sql78
1 files changed, 78 insertions, 0 deletions
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index a692c7d..b502d81 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -291,3 +291,81 @@ SELECT a1 FROM cte1;
SELECT
"x"."a" AS "a1"
FROM "x" AS "x";
+
+# title: recursive cte
+WITH RECURSIVE cte1 AS (
+ SELECT *
+ FROM (
+ SELECT 1 AS a, 2 AS b
+ ) base
+ CROSS JOIN (SELECT 3 c) y
+ UNION ALL
+ SELECT *
+ FROM cte1
+ WHERE a < 1
+)
+SELECT *
+FROM cte1;
+WITH RECURSIVE "base" AS (
+ SELECT
+ 1 AS "a",
+ 2 AS "b"
+), "y" AS (
+ SELECT
+ 3 AS "c"
+), "cte1" AS (
+ SELECT
+ "base"."a" AS "a",
+ "base"."b" AS "b",
+ "y"."c" AS "c"
+ FROM "base" AS "base"
+ CROSS JOIN "y" AS "y"
+ UNION ALL
+ SELECT
+ "cte1"."a" AS "a",
+ "cte1"."b" AS "b",
+ "cte1"."c" AS "c"
+ FROM "cte1"
+ WHERE
+ "cte1"."a" < 1
+)
+SELECT
+ "cte1"."a" AS "a",
+ "cte1"."b" AS "b",
+ "cte1"."c" AS "c"
+FROM "cte1";
+
+# title: right join should not push down to from
+SELECT x.a, y.b
+FROM x
+RIGHT JOIN y
+ON x.a = y.b
+WHERE x.b = 1;
+SELECT
+ "x"."a" AS "a",
+ "y"."b" AS "b"
+FROM "x" AS "x"
+RIGHT JOIN "y" AS "y"
+ ON "x"."a" = "y"."b"
+WHERE
+ "x"."b" = 1;
+
+# title: right join can push down to itself
+SELECT x.a, y.b
+FROM x
+RIGHT JOIN y
+ON x.a = y.b
+WHERE y.b = 1;
+WITH "y_2" AS (
+ SELECT
+ "y"."b" AS "b"
+ FROM "y" AS "y"
+ WHERE
+ "y"."b" = 1
+)
+SELECT
+ "x"."a" AS "a",
+ "y"."b" AS "b"
+FROM "x" AS "x"
+RIGHT JOIN "y_2" AS "y"
+ ON "x"."a" = "y"."b";