From 0a7abcd4c983e25a4efb46a4a4942aef92c70338 Mon Sep 17 00:00:00 2001
From: Tom Lane
Date: Mon, 13 Oct 2008 00:41:41 +0000
Subject: Fix corner case wherein a WorkTableScan node could get initialized
 before the RecursiveUnion to which it refers.  It turns out that we can just
 postpone the relevant initialization steps until the first exec call for the
 node, by which time the ancestor node must surely be initialized.  Per report
 from Greg Stark.

---
 src/test/regress/expected/with.out | 83 ++++++++++++++++++++++++++++++++++++++
 src/test/regress/sql/with.sql      | 21 ++++++++++
 2 files changed, 104 insertions(+)

(limited to 'src/test')

diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index 4760aa9d9fd..fe7561065ed 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -292,6 +292,89 @@ SELECT pg_get_viewdef('vsubdepartment'::regclass, true);
     FROM subdepartment;
 (1 row)
 
+-- corner case in which sub-WITH gets initialized first
+with recursive q as (
+      select * from department
+    union all
+      (with x as (select * from q)
+       select * from x)
+    )
+select * from q limit 24;
+ id | parent_department | name 
+----+-------------------+------
+  0 |                   | ROOT
+  1 |                 0 | A
+  2 |                 1 | B
+  3 |                 2 | C
+  4 |                 2 | D
+  5 |                 0 | E
+  6 |                 4 | F
+  7 |                 5 | G
+  0 |                   | ROOT
+  1 |                 0 | A
+  2 |                 1 | B
+  3 |                 2 | C
+  4 |                 2 | D
+  5 |                 0 | E
+  6 |                 4 | F
+  7 |                 5 | G
+  0 |                   | ROOT
+  1 |                 0 | A
+  2 |                 1 | B
+  3 |                 2 | C
+  4 |                 2 | D
+  5 |                 0 | E
+  6 |                 4 | F
+  7 |                 5 | G
+(24 rows)
+
+with recursive q as (
+      select * from department
+    union all
+      (with recursive x as (
+           select * from department
+         union all
+           (select * from q union all select * from x)
+        )
+       select * from x)
+    )
+select * from q limit 32;
+ id | parent_department | name 
+----+-------------------+------
+  0 |                   | ROOT
+  1 |                 0 | A
+  2 |                 1 | B
+  3 |                 2 | C
+  4 |                 2 | D
+  5 |                 0 | E
+  6 |                 4 | F
+  7 |                 5 | G
+  0 |                   | ROOT
+  1 |                 0 | A
+  2 |                 1 | B
+  3 |                 2 | C
+  4 |                 2 | D
+  5 |                 0 | E
+  6 |                 4 | F
+  7 |                 5 | G
+  0 |                   | ROOT
+  1 |                 0 | A
+  2 |                 1 | B
+  3 |                 2 | C
+  4 |                 2 | D
+  5 |                 0 | E
+  6 |                 4 | F
+  7 |                 5 | G
+  0 |                   | ROOT
+  1 |                 0 | A
+  2 |                 1 | B
+  3 |                 2 | C
+  4 |                 2 | D
+  5 |                 0 | E
+  6 |                 4 | F
+  7 |                 5 | G
+(32 rows)
+
 -- recursive term has sub-UNION
 WITH RECURSIVE t(i,j) AS (
 	VALUES (1,2)
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index 60c545d0676..54d311101d1 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -178,6 +178,27 @@ SELECT * FROM vsubdepartment ORDER BY name;
 SELECT pg_get_viewdef('vsubdepartment'::regclass);
 SELECT pg_get_viewdef('vsubdepartment'::regclass, true);
 
+-- corner case in which sub-WITH gets initialized first
+with recursive q as (
+      select * from department
+    union all
+      (with x as (select * from q)
+       select * from x)
+    )
+select * from q limit 24;
+
+with recursive q as (
+      select * from department
+    union all
+      (with recursive x as (
+           select * from department
+         union all
+           (select * from q union all select * from x)
+        )
+       select * from x)
+    )
+select * from q limit 32;
+
 -- recursive term has sub-UNION
 WITH RECURSIVE t(i,j) AS (
 	VALUES (1,2)
-- 
cgit v1.2.3