From 0d115dde82bf368ae0f9755113bd8fd53ac0b64b Mon Sep 17 00:00:00 2001
From: Tom Lane
Date: Tue, 7 Oct 2008 19:27:04 +0000
Subject: Extend CTE patch to support recursive UNION (ie, without ALL).  The
 implementation uses an in-memory hash table, so it will poop out for very
 large recursive results ... but the performance characteristics of a
 sort-based implementation would be pretty unpleasant too.

---
 src/test/regress/expected/with.out | 48 +++++++++++++++++++++++++++++---------
 src/test/regress/sql/with.sql      | 18 ++++++++++----
 2 files changed, 51 insertions(+), 15 deletions(-)

(limited to 'src/test')

diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index 5b45ac89dfc..4760aa9d9fd 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -49,6 +49,18 @@ SELECT * FROM t;
  5
 (5 rows)
 
+-- This is an infinite loop with UNION ALL, but not with UNION
+WITH RECURSIVE t(n) AS (
+    SELECT 1
+UNION
+    SELECT 10-n FROM t)
+SELECT * FROM t;
+ n 
+---
+ 1
+ 9
+(2 rows)
+
 -- This'd be an infinite loop, but outside query reads only as much as needed
 WITH RECURSIVE t(n) AS (
     VALUES (1)
@@ -69,6 +81,26 @@ SELECT * FROM t LIMIT 10;
  10
 (10 rows)
 
+-- UNION case should have same property
+WITH RECURSIVE t(n) AS (
+    SELECT 1
+UNION
+    SELECT n+1 FROM t)
+SELECT * FROM t LIMIT 10;
+ n  
+----
+  1
+  2
+  3
+  4
+  5
+  6
+  7
+  8
+  9
+ 10
+(10 rows)
+
 -- Test behavior with an unknown-type literal in the WITH
 WITH q AS (SELECT 'foo' AS x)
 SELECT x, x IS OF (unknown) as is_unknown FROM q;
@@ -510,38 +542,32 @@ WITH RECURSIVE
 --
 -- error cases
 --
--- UNION (should be supported someday)
-WITH RECURSIVE x(n) AS (SELECT 1 UNION SELECT n+1 FROM x)
-	SELECT * FROM x;
-ERROR:  recursive query "x" does not have the form non-recursive-term UNION ALL recursive-term
-LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 UNION SELECT n+1 FROM x)
-                       ^
 -- INTERSECT
 WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x)
 	SELECT * FROM x;
-ERROR:  recursive query "x" does not have the form non-recursive-term UNION ALL recursive-term
+ERROR:  recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x...
                        ^
 WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FROM x)
 	SELECT * FROM x;
-ERROR:  recursive query "x" does not have the form non-recursive-term UNION ALL recursive-term
+ERROR:  recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FR...
                        ^
 -- EXCEPT
 WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x)
 	SELECT * FROM x;
-ERROR:  recursive query "x" does not have the form non-recursive-term UNION ALL recursive-term
+ERROR:  recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x)
                        ^
 WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM x)
 	SELECT * FROM x;
-ERROR:  recursive query "x" does not have the form non-recursive-term UNION ALL recursive-term
+ERROR:  recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM ...
                        ^
 -- no non-recursive term
 WITH RECURSIVE x(n) AS (SELECT n FROM x)
 	SELECT * FROM x;
-ERROR:  recursive query "x" does not have the form non-recursive-term UNION ALL recursive-term
+ERROR:  recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
 LINE 1: WITH RECURSIVE x(n) AS (SELECT n FROM x)
                        ^
 -- recursive term in the left hand side (strictly speaking, should allow this)
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index c3ff5e285a7..60c545d0676 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -31,6 +31,13 @@ UNION ALL
 )
 SELECT * FROM t;
 
+-- This is an infinite loop with UNION ALL, but not with UNION
+WITH RECURSIVE t(n) AS (
+    SELECT 1
+UNION
+    SELECT 10-n FROM t)
+SELECT * FROM t;
+
 -- This'd be an infinite loop, but outside query reads only as much as needed
 WITH RECURSIVE t(n) AS (
     VALUES (1)
@@ -38,6 +45,13 @@ UNION ALL
     SELECT n+1 FROM t)
 SELECT * FROM t LIMIT 10;
 
+-- UNION case should have same property
+WITH RECURSIVE t(n) AS (
+    SELECT 1
+UNION
+    SELECT n+1 FROM t)
+SELECT * FROM t LIMIT 10;
+
 -- Test behavior with an unknown-type literal in the WITH
 WITH q AS (SELECT 'foo' AS x)
 SELECT x, x IS OF (unknown) as is_unknown FROM q;
@@ -265,10 +279,6 @@ WITH RECURSIVE
 -- error cases
 --
 
--- UNION (should be supported someday)
-WITH RECURSIVE x(n) AS (SELECT 1 UNION SELECT n+1 FROM x)
-	SELECT * FROM x;
-
 -- INTERSECT
 WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x)
 	SELECT * FROM x;
-- 
cgit v1.2.3