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

Re: BUG #1528: Rows returned that should be excluded by WHERE clause

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Peter Wright <pete(at)flooble(dot)net>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #1528: Rows returned that should be excluded by WHERE clause
Date: 2005-03-11 06:48:47
Message-ID: 29260.1110523727@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
Peter Wright <pete(at)flooble(dot)net> writes:
> [various stuff snipped]
> You say, "WHERE is defined to filter rows before application of
> aggregates", but I'd _think_ that should be interpreted to apply only
> to aggregates in the _current_ query (ie. not in sub-queries).

Well, the subtext of this discussion is that Postgres, like every other
DBMS on the planet, will aggressively push query restrictions down as
far as it's allowed to by the semantic rules.  Consider
	CREATE VIEW v1 AS SELECT c1, sum(c2) FROM tab GROUP BY c1;
	SELECT * FROM v1 WHERE c1 = 42;
A naive implementation would compute every row of the view v1
(ie, every sum of c2 over each existing value of c1) and then
throw away each result except the one for c1 = 42.  This is
obviously not acceptable.  So we have to transform the query to
	SELECT c1, sum(c2) FROM tab WHERE c1 = 42 GROUP BY c1;
which gives the execution engine a fair shot at doing something
reasonable, ie, pulling only the rows of tab that have c1 = 42,
which we could expect would be done with the aid of an index on c1.

(The GROUP BY step is actually redundant in this formulation,
but the cost of doing it is probably negligible; certainly it's
not the major problem compared to computing all the useless
sums over c1 groups other than 42.)

Point here is that to get reasonably efficient behavior we have to be
able to push the WHERE c1 = 42 condition down inside the view's
GROUP BY clause; and therefore we have to understand the exact
semantic conditions under which that is an allowable transformation.
Your bug report is essentially pointing out an error in our rules
for thinking that this transformation is allowable.

			regards, tom lane

In response to

pgsql-hackers by date

Next:From: Laszlo HornyakDate: 2005-03-11 08:05:45
Subject: Re: Runtime accepting build discrepancies
Previous:From: Nicolai TufarDate: 2005-03-11 06:41:39
Subject: Re: [pgsql-hackers-win32] snprintf causes regression tests to fail

pgsql-bugs by date

Next:From: Heikki LinnakangasDate: 2005-03-11 08:20:00
Subject: Re: [HACKERS] We are not following the spec for HAVING without GROUP
Previous:From: Peter WrightDate: 2005-03-11 05:45:18
Subject: Re: BUG #1528: Rows returned that should be excluded by WHERE clause

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