PushAllJoinsIntoUnionsForGroupByPushdown optimization with scalar subselects in join predicates causes unexpected exception

Detail name	Value
Changelog Number	30711
Type	Bug
Status	Resolved
Affected Versions	Exasol 2026.1.0
Fix Versions	Exasol 2026.1.1
Resolution Date	2026-08-06

Description

Valid aggregate or grouping queries with UNION ALL and an INNER JOIN may throw a "more than one column in select list of correlated subselect" exception under specific circumstances.
A query needs the following properties:

• An outer select should have aggregation or grouping.
• The FROM clause contains a UNION ALL.
• The FROM clause contains an INNER JOIN.
• The query is eligible for PushAllJoinsIntoUnionsForGroupByPushdown optimization.
• The join predicates contain an expression with a scalar subquery.
• The scalar subquery contains at least one column from a table or view (i.e., it works for constant values).

Example

-- Preparation
CREATE TABLE a (a1 INT);
CREATE TABLE a_ (a1 BIGINT);
CREATE TABLE b (b1 INT, b2 int);
CREATE TABLE c (c1 INT);
-- Expected: query runs without errors
-- Observed: "more than one column in select list of correlated subselect" exception
select max(A1+B1) from (SELECT * FROM A union all SELECT * FROM A_)
                  inner join B on A1=B1 and B2 = (select max(C1) from C);

Workaround

Rewrite the query with an additional join:

select max(A1+B1) from (SELECT * FROM A union all SELECT * FROM A_)
                  inner join B on A1=B1 INNER JOIN (SELECT MAX(c1) c1 FROM c) tmp ON B2 = tmp.c1;

Fix

Query runs as expected.