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

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

From: "Gill, Jerry T(dot)" <JTGill(at)west(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: BUG #1528: Rows returned that should be excluded by WHERE clause
Date: 2005-03-08 17:31:03
Message-ID: 9D87A98A6510F24C817257895EF4282A01AA8E7D@omacex08.corp.westworlds.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Sorry Tom, I missed a sentence in you previous email. My understanding of the having clause is that the row should be filtered. Here is the same example with the having clause in DB2.


[gill(at)c2n2 gill]$ db2 "select 2 as id, max(apn3) from phoenix.client having 2 =1"

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

  0 record(s) selected.

[gill(at)c2n2 gill]$ db2 "select 2 as id, max(apn3) from phoenix.client where 2 =1 having 2 = 1"

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

  0 record(s) selected.

-jgill

-----Original Message-----
From: pgsql-bugs-owner(at)postgresql(dot)org
[mailto:pgsql-bugs-owner(at)postgresql(dot)org]On Behalf Of Tom Lane
Sent: Tuesday, March 08, 2005 11:15 AM
To: Gill, Jerry T.
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #1528: Rows returned that should be excluded by
WHERE clause 


"Gill, Jerry T." <JTGill(at)west(dot)com> writes:
> 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(at)c2n2 gill]$ db2 "select 2 as id, max(apn3) from phoenix.client where 2 =1"

> ID          2
> ----------- ------
>           2      -

>   1 record(s) selected.

In the WHERE case I think there's no question that the above is correct:
WHERE is defined to filter rows before application of aggregates, so
zero rows arrive at the MAX aggregate, and that means it produces a
NULL.

But HAVING is supposed to filter after aggregation, so I think probably
there should be no row out in that case.

What does DB2 do when you say HAVING 2 = 1?

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match

pgsql-bugs by date

Next:From: Joe BrownDate: 2005-03-08 17:40:02
Subject: CC Date format code defaults to current centry
Previous:From: Tom LaneDate: 2005-03-08 17:14:35
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