Re: Proposed patch for qual pushdown into UNION/INTERSECT - Mailing list pgsql-patches

On Thu, 29 Aug 2002, Tom Lane wrote:

Actually, hadn't we figured that pushing to the left side of except was
safe?  It's only in pushing to the right that we ran into a problem since
that could make the query return more rows than it should.

I only did a little minimal testing, so I'm not 100% sure about this
patch to the patch, but it still seemed to give the right results for
my test (admittedly simple) queries.

*** t1.patch.old    Thu Aug 29 09:32:38 2002
--- t1.patch    Thu Aug 29 09:34:58 2002
***************
*** 150,159 ****
  +  * particular tlist items, but that's much clumsier to check.)
  +  *
  +  * 3. If the subquery contains EXCEPT or EXCEPT ALL set ops we cannot push
! +  * quals into it, because that would change the results.  For subqueries
! +  * using UNION/UNION ALL/INTERSECT/INTERSECT ALL, we can push the quals
! +  * into each component query, so long as all the component queries share
! +  * identical output types.  (That restriction could probably be relaxed,
  +  * but it would take much more code to include type coercion code into
  +  * the quals, and I'm also concerned about possible semantic gotchas.)
  +  */
--- 150,159 ----
  +  * particular tlist items, but that's much clumsier to check.)
  +  *
  +  * 3. If the subquery contains EXCEPT or EXCEPT ALL set ops we cannot push
! +  * quals into it's right hand side, because that would change the results.
! +  * For subqueries using UNION/UNION ALL/INTERSECT/INTERSECT ALL, we can push
! +  * the quals into each component query, so long as all the component queries
! +  * share identical output types.  (That restriction could probably be relaxed,
  +  * but it would take much more code to include type coercion code into
  +  * the quals, and I'm also concerned about possible semantic gotchas.)
  +  */
***************
*** 213,226 ****
  +     {
  +         SetOperationStmt *op = (SetOperationStmt *) setOp;
  +
- +         /* EXCEPT is no good */
- +         if (op->op == SETOP_EXCEPT)
- +             return false;
  +         /* Else recurse */
  +         if (!recurse_pushdown_safe(op->larg, topquery))
  +             return false;
! +         if (!recurse_pushdown_safe(op->rarg, topquery))
! +             return false;
  +     }
  +     else
  +     {
--- 213,224 ----
  +     {
  +         SetOperationStmt *op = (SetOperationStmt *) setOp;
  +
  +         /* Else recurse */
  +         if (!recurse_pushdown_safe(op->larg, topquery))
  +             return false;
! +        if (op->op != SETOP_EXCEPT)
! +             if (!recurse_pushdown_safe(op->rarg, topquery))
! +                 return false;
  +     }
  +     else
  +     {
***************
*** 289,295 ****
  +         SetOperationStmt *op = (SetOperationStmt *) setOp;
  +
  +         recurse_push_qual(op->larg, topquery, rti, qual);
! +         recurse_push_qual(op->rarg, topquery, rti, qual);
  +     }
  +     else
  +     {
--- 287,294 ----
  +         SetOperationStmt *op = (SetOperationStmt *) setOp;
  +
  +         recurse_push_qual(op->larg, topquery, rti, qual);
! +        if (op->op != SETOP_EXCEPT)
! +             recurse_push_qual(op->rarg, topquery, rti, qual);
  +     }
  +     else
  +     {