Scalar subqueries projected from outer join tables can incorrectly give non-NULL values

Details

Detail name Value
Changelog Number 31105
Type Bug
Status Resolved
Affected Versions Exasol 7.1.0, Exasol 8.0.0, Exasol 2025.1.0, Exasol 2025.2.0, Exasol 2026.1.0
Fix Versions Exasol 2026.1.1, Exasol 2025.1.14
Resolution Date 2026-08-06

Description

When evaluating an outer join, if a row from the outer table does not join to any rows from the “inner” (NULL-generating) table, then the outer table row will be joined to a “default” row of NULL values from the inner table.

A query can give wrong results for queries where the following conditions hold:

  • It has an outer join.
  • The join’s “inner” (NULL-generating) table has an expression in its SELECT list including a scalar subquery.
  • The expression with the scalar subquery does not also include columns from the inner table.
  • It does not matter whether or not the scalar subquery is correlated.

Example:

-- Setup
CREATE TABLE a (a1 INT);
INSERT INTO a VALUES (1);
CREATE TABLE b (b1 INT, b2 INT);
INSERT INTO b VALUES (2, 1);
CREATE TABLE c (c1 INT, c2 INT);
INSERT INTO c VALUES (100, 1);

-- Query with non-correlated scalar subquery
-- Expected: 1 row, values (1, NULL, NULL)
-- Observed: 1 row, values (1, NULL, 100)
SELECT a1, b1, x
FROM a
     LEFT JOIN
     (SELECT b1, (SELECT MAX(c1) FROM c) x FROM b)
     ON a1 = b1;

-- Query with correlated scalar subquery
-- Expected: 1 row, values (1, NULL, NULL, NULL)
-- Observed: 1 row, values (1, NULL, NULL, 0)
SELECT a1, b1, b2, x
FROM a LEFT JOIN
     (SELECT b1, b2, (SELECT COUNT(*) FROM c WHERE c2 = b2) x FROM b)
     ON a1 = b1;

Workaround

Wrap the scalar subselect with an expression using a column from the join table, without actually changing the result of the expression. For example, add the column and then subtract it again:

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

Or add the column multiplied by zero:

-- Expected/Observed: 1 row, values (NULL)
SELECT a1, b1, b2, x
FROM a LEFT JOIN
     (SELECT b1, b2, b1 * 0  + (SELECT COUNT(*) FROM c WHERE c2 = b2) x FROM b)
     ON a1 = b1;

Fix

The query gives the correct results.