Correlated subquery with multiple null values in the correlated columns leads to exception

Detail name	Value
Changelog Number	18738
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.1.0, Exasol 8.0.0
Fix Versions	Exasol 8.25.0, Exasol 7.1.26
Resolution Date	2024-02-15

Description

A correlated subquery with multiple null values in the column that is part of the correlation leads to a data exception.

Preparation

CREATE OR REPLACE TABLE t1(SESSION_ID DECIMAL(20,0), USER_NAME VARCHAR(128));
INSERT INTO t1 VALUES (null, 'a'),  (null, 'b'), (1, 'c');
CREATE OR REPLACE TABLE t2(SESSION_ID DECIMAL(20,0));
INSERT INTO t2 VALUES 1;

Example

-- Current behavior: [Code: 0, SQL State: 22735]  data exception - single-row subquery returns more than one row
-- Expected behavior: Single row with 1 | 'c'
SELECT
(SELECT S.USER_NAME FROM t1 S WHERE S.SESSION_ID = M.SESSION_ID) AS SUSER1,
M.*
FROM t2 M;

Workaround

Add IS NOT NULL into correlated subquery condition.

SELECT
(SELECT S.USER_NAME FROM t1 S WHERE S.SESSION_ID = M.SESSION_ID AND S.SESSION_ID IS NOT NULL) AS SUSER1,
M.*
FROM t2 M;

Fix

The query runs as expected.