Re: BUG #3494: may be Query Error: subplan does not executed - Mailing list pgsql-bugs

Tom Lane wrote:
> It appears that join_clause_is_redundant() is rejecting the clause as
> redundant.  I suppose some part of that machinery gets confused by the
> fact that the RHS of the clause references both relations.  The
> EquivalenceClass rewrite cleaned this whole area up greatly, so no
> surprise that the bug is gone in HEAD.  No time to look at it more now.

Yeah, that's it.

I don't understand that code very well. Why does it think that the right
pathkeys of "test1.id = test2.id" and "test1.id = test1.id+test2.id" are
equal?

Checking for overlapping left and right relation sets in
join_clause_is_redundant seems to fix the problem, though I'm not sure
if there's some other cases that that doesn't catch.

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com
Index: src/backend/optimizer/util/restrictinfo.c
===================================================================
RCS file: /home/hlinnaka/pgcvsrepository/pgsql/src/backend/optimizer/util/restrictinfo.c,v
retrieving revision 1.49
diff -c -r1.49 restrictinfo.c
*** src/backend/optimizer/util/restrictinfo.c    4 Oct 2006 00:29:55 -0000    1.49
--- src/backend/optimizer/util/restrictinfo.c    31 Jul 2007 08:58:40 -0000
***************
*** 651,656 ****
--- 651,659 ----
   * of the latter, even though they might seem redundant by the pathkey
   * membership test.
   *
+  * The above logic only works for normal join clauses with non-overlapping
+  * left and right relation sets.
+  *
   * Weird special case: if we have two clauses that seem redundant
   * except one is pushed down into an outer join and the other isn't,
   * then they're not really redundant, because one constrains the
***************
*** 673,685 ****
              return refrinfo;
      }

!     /* check for redundant merge clauses */
!     if (rinfo->mergejoinoperator != InvalidOid)
      {
-         /* do the cheap test first: is it a "var = const" clause? */
-         if (bms_is_empty(rinfo->left_relids) ||
-             bms_is_empty(rinfo->right_relids))
-             return NULL;        /* var = const, so not redundant */

          cache_mergeclause_pathkeys(root, rinfo);

--- 676,689 ----
              return refrinfo;
      }

!     /*
!      * check for redundant merge clauses by comparing the pathkeys as
!      * explained above. The check for can_join handles the special case
!      * of "var = const", as well as any clauses with overlapping left
!      * and right rel sets.
!      */
!     if (rinfo->mergejoinoperator != InvalidOid && rinfo->can_join)
      {

          cache_mergeclause_pathkeys(root, rinfo);

***************
*** 687,693 ****
          {
              RestrictInfo *refrinfo = (RestrictInfo *) lfirst(refitem);

!             if (refrinfo->mergejoinoperator != InvalidOid)
              {
                  cache_mergeclause_pathkeys(root, refrinfo);

--- 691,697 ----
          {
              RestrictInfo *refrinfo = (RestrictInfo *) lfirst(refitem);

!             if (refrinfo->mergejoinoperator != InvalidOid && refrinfo->can_join)
              {
                  cache_mergeclause_pathkeys(root, refrinfo);