Fix oversight in join removal patch: we have to delete the removed relation

tglsfdc · tglsfdc · commit 7df4cf7fd3af · 2010-05-23T16:34:38.000Z
from SpecialJoinInfo relid sets as well.  Per example from Vaclav Novotny.
diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c
@@ -16,7 +16,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/optimizer/plan/analyzejoins.c,v 1.1 2010/03/28 22:59:32 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/optimizer/plan/analyzejoins.c,v 1.2 2010/05/23 16:34:38 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -343,6 +343,24 @@ remove_rel_from_query(PlannerInfo *root, int relid)
 		}
 	}
 
+	/*
+	 * Likewise remove references from SpecialJoinInfo data structures.
+	 *
+	 * This is relevant in case the outer join we're deleting is nested
+	 * inside other outer joins: the upper joins' relid sets have to be
+	 * adjusted.  The RHS of the target outer join will be made empty here,
+	 * but that's OK since caller will delete that SpecialJoinInfo entirely.
+	 */
+	foreach(l, root->join_info_list)
+	{
+		SpecialJoinInfo *sjinfo = (SpecialJoinInfo *) lfirst(l);
+
+		sjinfo->min_lefthand = bms_del_member(sjinfo->min_lefthand, relid);
+		sjinfo->min_righthand = bms_del_member(sjinfo->min_righthand, relid);
+		sjinfo->syn_lefthand = bms_del_member(sjinfo->syn_lefthand, relid);
+		sjinfo->syn_righthand = bms_del_member(sjinfo->syn_righthand, relid);
+	}
+
 	/*
 	 * Likewise remove references from PlaceHolderVar data structures.
 	 *
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
@@ -2525,6 +2525,20 @@ explain (costs off)
  Seq Scan on a
 (1 row)
 
+-- check optimization of outer join within another special join
+explain (costs off)
+select id from a where id in (
+	select b.id from b left join c on b.id = c.id
+);
+         QUERY PLAN         
+----------------------------
+ Hash Semi Join
+   Hash Cond: (a.id = b.id)
+   ->  Seq Scan on a
+   ->  Hash
+         ->  Seq Scan on b
+(5 rows)
+
 rollback;
 create temp table parent (k int primary key, pd int);
 NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "parent_pkey" for table "parent"
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
@@ -588,6 +588,12 @@ explain (costs off)
   SELECT a.* FROM a LEFT JOIN (b left join c on b.c_id = c.id)
   ON (a.b_id = b.id);
 
+-- check optimization of outer join within another special join
+explain (costs off)
+select id from a where id in (
+	select b.id from b left join c on b.id = c.id
+);
+
 rollback;
 
 create temp table parent (k int primary key, pd int);

Original file line number	Diff line number	Diff line change
`@@ -16,7 +16,7 @@`
`16`	`16`	`*`
`17`	`17`	`*`
`18`	`18`	`* IDENTIFICATION`
`19`		`- * $PostgreSQL: pgsql/src/backend/optimizer/plan/analyzejoins.c,v 1.1 2010/03/28 22:59:32 tgl Exp $`
	`19`	`+ * $PostgreSQL: pgsql/src/backend/optimizer/plan/analyzejoins.c,v 1.2 2010/05/23 16:34:38 tgl Exp $`
`20`	`20`	`*`
`21`	`21`	`*-------------------------------------------------------------------------`
`22`	`22`	`*/`
`@@ -343,6 +343,24 @@ remove_rel_from_query(PlannerInfo *root, int relid)`
`343`	`343`	`}`
`344`	`344`	`}`
`345`	`345`
	`346`	`+ /*`
	`347`	`+ * Likewise remove references from SpecialJoinInfo data structures.`
	`348`	`+ *`
	`349`	`+ * This is relevant in case the outer join we're deleting is nested`
	`350`	`+ * inside other outer joins: the upper joins' relid sets have to be`
	`351`	`+ * adjusted. The RHS of the target outer join will be made empty here,`
	`352`	`+ * but that's OK since caller will delete that SpecialJoinInfo entirely.`
	`353`	`+ */`
	`354`	`+ foreach(l, root->join_info_list)`
	`355`	`+ {`
	`356`	`+ SpecialJoinInfo sjinfo = (SpecialJoinInfo ) lfirst(l);`
	`357`	`+`
	`358`	`+ sjinfo->min_lefthand = bms_del_member(sjinfo->min_lefthand, relid);`
	`359`	`+ sjinfo->min_righthand = bms_del_member(sjinfo->min_righthand, relid);`
	`360`	`+ sjinfo->syn_lefthand = bms_del_member(sjinfo->syn_lefthand, relid);`
	`361`	`+ sjinfo->syn_righthand = bms_del_member(sjinfo->syn_righthand, relid);`
	`362`	`+ }`
	`363`	`+`
`346`	`364`	`/*`
`347`	`365`	`* Likewise remove references from PlaceHolderVar data structures.`
`348`	`366`	`*`