"Peter Wright" <[EMAIL PROTECTED]> writes:
> Description: Rows returned that should be excluded by WHERE clause
Interesting point. The view and union don't seem to be the issue;
I think the problem can be expressed as
regression=# select 2 as id, max(b) from t2 having 2 = 1;
id | max
----+-----
2 |
(1 row)
Now, if this were a WHERE clause, I think the answer would be right:
regression=# select 2 as id, max(b) from t2 where 2 = 1;
id | max
----+-----
2 |
(1 row)
but since it's HAVING I think this is probably wrong. Looking at the
EXPLAIN output
regression=# explain select 2 as id, max(b) from t2 having 2 = 1;
QUERY PLAN
----------------------------------------------------------------
Aggregate (cost=3.68..3.68 rows=1 width=2)
-> Result (cost=0.00..3.14 rows=214 width=2)
One-Time Filter: false
-> Seq Scan on t2 (cost=0.00..3.14 rows=214 width=2)
(4 rows)
the issue is clearly that the known-false HAVING clause is pushed down
inside the aggregation, as though it were WHERE. The existing code
pushes down HAVING to WHERE if the clause contains no aggregates, but
evidently this is too simplistic. What are the correct conditions for
pushing down HAVING clauses to WHERE?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
