Wrong results for filter with scalar subquery on a UNION with multiple operands

Detail name	Value
Changelog Number	16503
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.0.0, Exasol 7.1.0, Exasol 8.0.0
Fix Versions	Exasol 7.1.19, Exasol 8.11.0
Resolution Date	2023-02-28

Description

In some cases with a filter containing a scalar subquery on a UNION ALL wrong results may occur. The results are wrong in a way that multiple or all union operands have no results such that the result of the UNION operation is empty or too small.

The following conditions trigger this problem:

• The query contains a UNION or UNION ALL
• There is one filter on the UNION ALL that - when put into one of the operands - contains only constants and evaluates to FALSE.
• There is another filter F on the UNION ALL that contains a scalar subquery
• The subselect that evaluates to false contains another subselect which exports the columns used in the filter F and needs to be materialized

Example preparation

drop schema if exists test cascade;
create schema test;
create table t1(d date, i int);
create table t2(d date, i int);
create table dim(i int);
insert into t1 values (date'2000-01-01', 1);
insert into t2 values (date'2002-01-01', 2);
create table lu(d date);
insert into lu values date'2001-01-01';

Example

-- this testcase has the problem
select * from
(
select t1.d as rd, cast('abc' as char(10)) x from t1
union all
select d as rd, cast('def' as char(10)) x from 
(select d from t2 group by d) t2 -- group by requires a materialization
)
where 
-- this is the filter with the scalar subquery
rd <= (select max(d) from lu)
-- this filter 'def'='abc' which is always false when pushed into the second operand
and x='abc'
;

In the above example, the filter x = 'abc' is always false when pushed into the second operand, however the query returns no rows. In the example above, the expected result is one row from the first operand.

Workaround

Modify the query to remove one of the conditions from the query. For example, you could replace the scalar subquery with a computed constant.

...
where 
rd <= date'2001-01-01'
and x='abc'
;

Another possibility is to artificially add lookups to the select list expression for the constant filter, e.g.

...
union all
select d as rd, case when d is not null then cast('def' as char(10)) else cast('def' as char(10)) end x from 
...

Fix

In these conditions, the query returns the correct results.