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

Just an interesting side note here, this behavior is identical to DB2. I am=
 not sure if that makes it correct or not, but here is an example.

[gill@c2n2 gill]$ db2 "select 2 as id, max(apn3) from phoenix.client where =
2 =3D1"

ID          2
----------- ------
          2      -

  1 record(s) selected.

-jgill

-----Original Message-----
From: pgsql-bugs-owner@postgresql.org
[mailto:pgsql-bugs-owner@postgresql.org]On Behalf Of Tom Lane
Sent: Tuesday, March 08, 2005 2:07 AM
To: Peter Wright
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #1528: Rows returned that should be excluded by
WHERE clause=20


"Peter Wright" <pete@flooble.net> 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=3D# select 2 as id, max(b) from t2 having 2 =3D 1;
 id | max=20
----+-----
  2 |=20=20=20=20
(1 row)

Now, if this were a WHERE clause, I think the answer would be right:

regression=3D# select 2 as id, max(b) from t2 where 2 =3D 1;
 id | max=20
----+-----
  2 |=20=20=20=20
(1 row)

but since it's HAVING I think this is probably wrong.  Looking at the
EXPLAIN output=20

regression=3D# explain select 2 as id, max(b) from t2 having 2 =3D 1;
                           QUERY PLAN=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
----------------------------------------------------------------
 Aggregate  (cost=3D3.68..3.68 rows=3D1 width=3D2)
   ->  Result  (cost=3D0.00..3.14 rows=3D214 width=3D2)
         One-Time Filter: false
         ->  Seq Scan on t2  (cost=3D0.00..3.14 rows=3D214 width=3D2)
(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