"Feature not supported" error with expression converting NULL to valid value in outer join with UNION ALL and grouping/aggregation

Details

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

Description

A query can give the error “Feature not supported: this kind of correlated subselect” for a query with the following conditions, when it should succeed.

  • There is an outer join in a SELECT which has grouping or aggregation.
  • The “inner” (NULL-generating) table of the outer join has an expression of the following form in its SELECT list:
  • One of the other tables in the join has a UNION ALL.
  • The expression contains a scalar subquery. (It does not matter whether or not the scalar subquery is correlated.)
  • This expression contains an expression or function that can give a non-NULL result for a NULL input. This is most likely to be a CASE expression or a similar conditional expression (such as COALESCE, DECODE, IFNULL or NULLIF). It also includes user-defined functions and a few other functions and predicates.

Examples

-- Setup
CREATE TABLE a (a1 INT);
INSERT INTO a VALUES (1), (2), (null);
CREATE TABLE a_ (a1 BIGINT);
INSERT INTO a_ VALUES (10000000001), (10000000002);
CREATE TABLE b (b1 INT);
INSERT INTO b VALUES (123);
CREATE TABLE c (c1 INT);
INSERT INTO c VALUES (0), (1), (100);

CREATE FUNCTION f(a1 INT) RETURN INT IS
BEGIN
    RETURN 1;
END
/

-- Expected/Observed: 1 row, values (NULL)
SELECT MAX(x)
FROM (SELECT * FROM a UNION ALL SELECT * FROM a_)
     LEFT JOIN
     (SELECT b1, (SELECT MAX(c1) FROM c) + b1 x FROM b)
     ON a1 = b1;

-- Expected: 1 row, values (NULL)
-- Observed: Error "Feature not supported: this kind of correlated subselect"
SELECT MAX(x)
FROM (SELECT * FROM a UNION ALL SELECT * FROM a_)
     LEFT JOIN
     (SELECT b1, (SELECT MAX(c1) FROM c) + IFNULL(b1, 123) x FROM b)
     ON a1 = b1;

-- Expected: 1 row, values (NULL)
-- Observed: Error "Feature not supported: this kind of correlated subselect"
SELECT MAX(x)
FROM (SELECT * FROM a UNION ALL SELECT * FROM a_)
     LEFT JOIN
     (SELECT b1, (SELECT MAX(c1) FROM c) + f(b1) x FROM b)
     ON a1 = b1;

-- Expected: 1 row, values (NULL)
-- Observed: Error "Feature not supported: this kind of correlated subselect"
SELECT MAX(x)
FROM (SELECT * FROM a UNION ALL SELECT * FROM a_)
     LEFT JOIN
     (SELECT b1, (SELECT MAX(c1) FROM c) + CAST((b1 IS NULL) AS INT) x FROM b)
     ON a1 = b1;

Workaround

Disable the optimization that pushes all joins and GROUP BY into UNION ALL. Please contact Exasol Support for more information.

Fix

The query succeeds.