HAVING ignored in some aggregated subqueries if no GROUP BY clause is given

Detail name	Value
Changelog Number	8067
Type	Bug
Status	Resolved
Affected Versions	EXASOL 6.0.0, Exasol 6.1.0, Exasol 6.2.0, Exasol 7.0.alpha1
Fix Versions	Exasol 7.0.0, Exasol 6.2.6
Resolution Date	2020-04-17

Description

In a few situations a HAVING without GROUP BY is ignored for subqueries. This results in a single result row for some subqueries that should have an empty result instead.

Note: A typical use-case for HAVING without GROUP BY is just using HAVING FALSE on the top-level SELECT to get only the structure of a result set. That use case is not affected by this bug, it only appears for HAVING in subqueries.

Expected Behavior

HAVING is a filter executed after aggregation. For queries without GROUP BY it should lead to an empty result if does not evaluate to TRUE.

Example

-- this query returns the value 6 from subsel even though HAVING FALSE
-- should have removed that row
WITH t(i) AS (VALUES 1,2,3)
SELECT * FROM (SELECT SUM(i) si FROM T HAVING FALSE) subsel
WHERE (NOT si IN (SELECT ( 1 ))) or (si < si);

Workaround

There is no general workaround for the problem.
However, rewriting the query to something different might help in some cases:

-- this rewritten query with the OR removed behaves correctly
WITH t(i) AS (VALUES 1,2,3)
SELECT * FROM (SELECT SUM(i) si FROM T HAVING FALSE) subsel
WHERE (NOT si IN (SELECT ( 1 )));