Wrong number of rows after Join is pushed in UNION ALL

Detail name	Value
Changelog Number	26715
Type	Bug
Status	Resolved
Affected Versions	Exasol 8.34.0
Fix Versions	Exasol 2025.1.3, Exasol 2025.2.0
Resolution Date	2025-09-17

Description

In some scenarios joins are pushed into UNION ALL (see CHANGELOG: Optimization: Push joins through UNION ALL). If UNION ALL contains LIMIT clause, it gets discarded and returned number of rows may be incorrect.

Preparation

create or replace table A(A1 int);
insert into A values 1;
create or replace table A_(A1 int);
insert into A_ values 1;
create or replace table B (B1 int, B2 int);
insert into B values (1,1);

Example

-- expected: 1 row
-- observed: 2 rows  
select * from (select * from A union all select * from A_ limit 1)
                                   join B on A1 = B1 and B2 = 1;

Workaround

Set database parameter enablePushJoinsIntoUnionsOptimization to false.

Fix

Query results are correct.