BUG #13918: Simple query with Having clause returns incorrect results

On Thursday, February 4, 2016, <tarasbob@gmail.com> wrote:

The following bug has been logged on the website:

Bug reference: 13918
Logged by: Taras Bobrovytsky
Email address: tarasbob@gmail.com <javascript:;>
PostgreSQL version: 9.3.10
Operating system: Ubuntu
Description:

The following query incorrectly returns 1 row instead of 0:

SELECT 1
FROM some_table
WHERE FALSE
HAVING TRUE

So, amazingly (to me), this behavior is documented.

http://www.postgresql.org/docs/9.5/interactive/sql-select.html
"""
The presence of HAVING turns a query into a grouped query even if there is
no GROUP BY clause. This is the same as what happens when the query
contains aggregate functions but no GROUP BY clause. All the selected rows
are considered to form a single group, and the SELECT list and HAVING clause
can only reference table columns from within aggregate functions. Such a
query will emit a single row if the HAVINGcondition is true, zero rows if
it is not true.
"""

It doesn't matter that no rows are sourced your are guaranteed a single row
output if having evaluates to true. This is nice since you can do stuff
like counts and sums and get zeros for answers instead of dealing with an
empty result because nothing matched.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Thursday, February 4, 2016, <tarasbob@gmail.com> wrote:

The following query incorrectly returns 1 row instead of 0:

SELECT 1
FROM some_table
WHERE FALSE
HAVING TRUE

So, amazingly (to me), this behavior is documented.

The presence of HAVING turns a query into a grouped query even if there is
no GROUP BY clause. ... Such a
query will emit a single row if the HAVING condition is true, zero rows if
it is not true.

Yeah. This is a pretty weird corner case, but the behavior is required
by SQL spec.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs