Skip site navigation (1) Skip section navigation (2)

Re: Proposed patch for qual pushdown into UNION/INTERSECT

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-patches(at)postgreSQL(dot)org>
Subject: Re: Proposed patch for qual pushdown into UNION/INTERSECT
Date: 2002-08-29 15:46:00
Message-ID: 20020829082316.Q97253-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: 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
  + 	{


In response to

Responses

pgsql-patches by date

Next:From: John GrayDate: 2002-08-29 15:51:10
Subject: Re: Visibility regression test
Previous:From: Tom LaneDate: 2002-08-29 15:37:39
Subject: Re: Visibility regression test

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group