Re: Proposed patch for qual pushdown into UNION/INTERSECT

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
+ {