From eda80f09ffd016a9fc12fc89f14019d760d998c8 Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Sat, 18 Oct 2003 16:52:15 +0000
Subject: [PATCH] Repair interaction between IN-join processing and subselect
 pullup that I inadvertently broke a few days ago (per report from Sean
 Thomas). Add regression test case to try to catch any similar breakage in
 future.

---
 src/backend/optimizer/plan/subselect.c  | 15 ++++++++------
 src/test/regress/expected/subselect.out | 36 +++++++++++++++++++++++++++++++++
 src/test/regress/sql/subselect.sql      | 18 +++++++++++++++++
 3 files changed, 63 insertions(+), 6 deletions(-)

diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index d7d25c09f8..11612f408c 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/optimizer/plan/subselect.c,v 1.82 2003/08/08 21:41:51 momjian Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/optimizer/plan/subselect.c,v 1.83 2003/10/18 16:52:15 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -493,8 +493,8 @@ make_subplan(SubLink *slink, List *lefthand, bool isTopQual)
  * If rtindex is 0, we build Params to represent the sub-select outputs.
  * The paramids of the Params created are returned in the *righthandIds list.
  *
- * If rtindex is not 0, we build Vars using that rtindex as varno.	The
- * Vars themselves are returned in *righthandIds (this is a bit of a type
+ * If rtindex is not 0, we build Vars using that rtindex as varno.	Copies
+ * of the Var nodes are returned in *righthandIds (this is a bit of a type
  * cheat, but we can get away with it).
  */
 static List *
@@ -525,8 +525,11 @@ convert_sublink_opers(List *lefthand, List *operOids,
 									   te->resdom->restype,
 									   te->resdom->restypmod,
 									   0);
-			/* Record it for caller */
-			*righthandIds = lappend(*righthandIds, rightop);
+			/*
+			 * Copy it for caller.  NB: we need a copy to avoid having
+			 * doubly-linked substructure in the modified parse tree.
+			 */
+			*righthandIds = lappend(*righthandIds, copyObject(rightop));
 		}
 		else
 		{
@@ -735,7 +738,7 @@ convert_IN_to_join(Query *parse, SubLink *sublink)
 
 	/*
 	 * Build the result qual expressions.  As a side effect,
-	 * ininfo->sub_targetlist is filled with a list of the Vars
+	 * ininfo->sub_targetlist is filled with a list of Vars
 	 * representing the subselect outputs.
 	 */
 	exprs = convert_sublink_opers(sublink->lefthand,
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index 5a2ef11c21..f7b8297809 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -165,3 +165,39 @@ from int8_tbl group by q1 order by q1;
  4567890123456789 |      0.6
 (2 rows)
 
+--
+-- Test cases to catch unpleasant interactions between IN-join processing
+-- and subquery pullup.
+--
+select count(*) from
+  (select 1 from tenk1 a
+   where unique1 IN (select hundred from tenk1 b)) ss;
+ count 
+-------
+   100
+(1 row)
+
+select count(distinct ss.ten) from
+  (select ten from tenk1 a
+   where unique1 IN (select hundred from tenk1 b)) ss;
+ count 
+-------
+    10
+(1 row)
+
+select count(*) from
+  (select 1 from tenk1 a
+   where unique1 IN (select distinct hundred from tenk1 b)) ss;
+ count 
+-------
+   100
+(1 row)
+
+select count(distinct ss.ten) from
+  (select ten from tenk1 a
+   where unique1 IN (select distinct hundred from tenk1 b)) ss;
+ count 
+-------
+    10
+(1 row)
+
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index c2cde6ac20..5ff9d1cf87 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -75,3 +75,21 @@ SELECT '' AS eight, ss.f1 AS "Correlated Field", ss.f3 AS "Second Field"
 
 select q1, float8(count(*)) / (select count(*) from int8_tbl)
 from int8_tbl group by q1 order by q1;
+
+--
+-- Test cases to catch unpleasant interactions between IN-join processing
+-- and subquery pullup.
+--
+
+select count(*) from
+  (select 1 from tenk1 a
+   where unique1 IN (select hundred from tenk1 b)) ss;
+select count(distinct ss.ten) from
+  (select ten from tenk1 a
+   where unique1 IN (select hundred from tenk1 b)) ss;
+select count(*) from
+  (select 1 from tenk1 a
+   where unique1 IN (select distinct hundred from tenk1 b)) ss;
+select count(distinct ss.ten) from
+  (select ten from tenk1 a
+   where unique1 IN (select distinct hundred from tenk1 b)) ss;
-- 
2.11.0