PushJoinsIntoUnions optimization with scalar subselects in join predicates causes unexpected exception

Detail name	Value
Changelog Number	30710
Type	Bug
Status	Resolved
Affected Versions	Exasol 8.34.0, Exasol 2025.1.0, Exasol 2025.2.0, Exasol 2026.1.0
Fix Versions	Exasol 2025.1.11, Exasol 2026.1.1
Resolution Date	2026-06-03

Description

Valid queries with UNION all and an INNER JOIN may throw a "Feature not supported: this kind of correlated subselect" exception under specific circumstances.
A query needs the following properties:

• The query contains a UNION ALL.
• The query contains an INNER JOIN.
• The query is eligible for PushJoinsIntoUnions 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: "Feature not supported: this kind of correlated subselect" exception
SELECT * FROM (TABLE a UNION ALL TABLE a_)
              INNER JOIN b ON a1=b1 AND b2 = (SELECT MAX(c1) FROM c) AND b1 = 1;

Workaround

Rewrite the query with a join:

SELECT * FROM (TABLE a UNION ALL TABLE a_)
              INNER JOIN b ON a1=b1 INNER JOIN (SELECT MAX(c1) c1 FROM c) tmp ON B2 = tmp.c1 AND B1 = 1;

Fix

Query runs as expected.