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
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-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

Browse pgsql-bugs by date

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

Browse pgsql-hackers by date

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