Query with filter on scalar subselect that references UNION ALL fails

Detail name	Value
Changelog Number	10502
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.2.0, Exasol 7.0.0
Fix Versions	Exasol 8.25.0, Exasol 7.1.26
Resolution Date	2024-02-15

Description

If a query contains a filter on a scalar subselect, and the subselect contains or references a UNION ALL, the query will fail with an internal server error. The following example shows this case and will cause an internal server error:

select a
from (
select 1 as a, 2 as b from dual union all
select 3 as a, 4 as b from dual
)
where a=(select max(a) from (select 1))

Workaround

Since the sub-select select max(a) from (select 1) has no column a, it is directly referencing the column a from the UNION ALL query. This is why this can be rewritten to:

select max(a)
from (
select 1 as a, 2 as b from dual union all
select 3 as a, 4 as b from dual
)

If the goal was to filter with the sub-select, the column in the sub-select has to be named properly:

select a
from (
select 1 as a, 2 as b from dual union all
select 3 as a, 4 as b from dual
)
where a=(select max(a) from (select 1 a))

Fix

The above query will no longer throw an internal server error.