Benjamin Reed <ranger@opennms.org> writes:
> Attached is a test case, including the query that causes the error at
> the end. On 8.3, it returns 1 row (192.168.1.1), on 8.4 including the
> git 8.4 branch, it returns none.
Thanks for the test case. This patch should fix it.
regards, tom lane
Index: joinrels.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/optimizer/path/joinrels.c,v
retrieving revision 1.100.2.1
diff -c -r1.100.2.1 joinrels.c
*** joinrels.c 19 Jul 2009 20:32:56 -0000 1.100.2.1
--- joinrels.c 23 Jul 2009 17:39:13 -0000
***************
*** 400,405 ****
--- 400,421 ----
continue;
/*
+ * If it's a semijoin and we already joined the RHS to any other
+ * rels within either input, then we must have unique-ified the RHS
+ * at that point (see below). Therefore the semijoin is no longer
+ * relevant in this join path.
+ */
+ if (sjinfo->jointype == JOIN_SEMI)
+ {
+ if (bms_is_subset(sjinfo->syn_righthand, rel1->relids) &&
+ !bms_equal(sjinfo->syn_righthand, rel1->relids))
+ continue;
+ if (bms_is_subset(sjinfo->syn_righthand, rel2->relids) &&
+ !bms_equal(sjinfo->syn_righthand, rel2->relids))
+ continue;
+ }
+
+ /*
* If one input contains min_lefthand and the other contains
* min_righthand, then we can perform the SJ at this join.
*
***************
*** 491,499 ****
* We assume that make_outerjoininfo() set things up correctly
* so that we'll only match to some SJ if the join is valid.
* Set flag here to check at bottom of loop.
- *
- * For a semijoin, assume it's okay if either side fully contains
- * the RHS (per the unique-ification case above).
*----------
*/
if (sjinfo->jointype != JOIN_SEMI &&
--- 507,512 ----
***************
*** 503,514 ****
/* seems OK */
Assert(!bms_overlap(joinrelids, sjinfo->min_lefthand));
}
- else if (sjinfo->jointype == JOIN_SEMI &&
- (bms_is_subset(sjinfo->syn_righthand, rel1->relids) ||
- bms_is_subset(sjinfo->syn_righthand, rel2->relids)))
- {
- /* seems OK */
- }
else
is_valid_inner = false;
}
--- 516,521 ----
