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

The following bug has been logged on the website:

Bug reference:      13918
Logged by:          Taras Bobrovytsky
Email address:      tarasbob@gmail.com
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

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