Internal server error when using DISTINCT on a select-list containing a correlated subquery

Detail name	Value
Changelog Number	6305
Type	Bug
Status	Open
Affected Versions	EXASOL 6.0.0, Exasol 6.1.0, Exasol 6.2.0, Exasol 7.0.0, Exasol 7.1.0, Exasol 8.0.0, Exasol 8.29.0, Exasol 2025.1.0, Exasol 2025.2.0

Description

An internal server error occurs when using DISTINCT in a select list that also contains a correlated subquery. This only happens if the correlated subquery has a view or subquery in the FROM-clause.

Please note, that even without the internal server error the combination of DISTINCT and a correlated subquery in select-list will result in a 'Feature not supported: this kind of correlated subselect' error message.

Example

drop schema if exists test cascade;
create schema test;

create or replace table t1( i int, j int, k );
create or replace table t2( i int );

select
  DISTINCT
  A.i,
  (
    SELECT max(B.k)
    from (select * from t1) B
    WHERE b.i =  a.i AND B.j = 42
  )
from
  t2 A
;

Workaround

The easiest way to avoid this problem is to add another query level for the DISTINCT operation:

-- just select DISTINCT * in a separate select one layer above
-- and remove DISTINCT from the original select
SELECT DISTINCT * FROM
(
select
-- only removed DISTINCT here
  A.i,
  (
    SELECT
      max(B.k)
    from
      (select * from t1) B
    WHERE
      b.i =  a.i AND
      B.j = 42
  )
from
  t2 A
)
;

Another option would be to transform the correlated subquery in the select-list to a JOIN in the FROM-clause.