Correlated subquery with DISTINCT and GROUP BY causes internal server error

Detail name	Value
Changelog Number	13272
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.2.0, Exasol 7.0.0, Exasol 7.1.0
Fix Versions	Exasol 8.9.0
Resolution Date	2023-02-02

Description

A correlated subquery with DISTINCT, GROUP BY and a correlation in the WHERE clause throws an Internal Server Error.

Preparation:

CREATE SCHEMA TEST;
CREATE OR REPLACE TABLE TEST.T1 (C1 INT);
CREATE OR REPLACE TABLE TEST.T2 (C2 INT);
CREATE OR REPLACE TABLE TEST.T3 (C3 INT);

Example:

SELECT c1 FROM test.t1
INNER JOIN test.t2 ON t1.c1 = t2.c2
WHERE t1.c1 IN
    (SELECT DISTINCT c3 FROM test.t3 WHERE c3 > 4 and c2 = 5 GROUP BY t3.c3 );

Workaround

Remove the correlation from the WHERE if the logic allows it.

SELECT c1 FROM test.t1
INNER JOIN test.t2 ON t1.c1 = t2.c2
WHERE t1.c1 IN
    (SELECT DISTINCT c3 FROM test.t3 WHERE c3 > 4 GROUP BY t3.c3)
AND c2 = 5;

Fix

The query throws the error: 'Feature not supported: this kind of correlated subselect'.