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

On Thursday, February 4, 2016, <tarasbob(at)gmail(dot)com> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 13918
> Logged by: Taras Bobrovytsky
> Email address: tarasbob(at)gmail(dot)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.