From 7a2201963d5b03bd1828d350ccaecb4eda30d30c Mon Sep 17 00:00:00 2001
From: Daniel Baumann <daniel.baumann@progress-linux.org>
Date: Wed, 4 Jan 2023 08:24:08 +0100
Subject: Merging upstream version 10.4.2.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
---
 tests/fixtures/optimizer/optimizer.sql | 78 ++++++++++++++++++++++++++++++++++
 1 file changed, 78 insertions(+)

(limited to 'tests/fixtures/optimizer/optimizer.sql')

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";
-- 
cgit v1.2.3