Incorrect results when combining WHERE FALSE, aggregations, and UNION

Detail name	Value
Changelog Number	8225
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.1.0, Exasol 6.2.0, Exasol 7.0.0
Fix Versions	Exasol 7.1.0, Exasol 7.0.4
Resolution Date	2020-11-26

Description

In some cases, a branch of a UNION may produce wrong results. The following conditions are required:

• The query contains a UNION
• One of the UNION branches contains a predicate which evaluates to FALSE (WHERE FALSE)
• The same union branch performs a full table aggregation (example: MAX())

The aggregation should return a single row with the default aggregate values (zero for counts and NULL for other aggregates). Instead, no rows are returned.

For example:

-- Setup
create schema AA;
create table A (A1 int);
insert into A values (3);

-- Expected result: 2 rows, values (1), (NULL)
-- Actual result: 1 row, values (1)
select max(A1) from A where 1 + 1 = 3
union
select 1 from A;

Workaround

You can put the union branch with the false predicate into a subselect to get the correct results. For example:

select * from (select max(A1) from A where 1 + 1 = 3) DT
union
select 1 from A;

Fix

Queries containing the listed conditions will return the correct, expected results.