Query crash with DISTINCT and JOIN USING

Details

Detail name Value
Changelog Number 32333
Type Bug
Status Resolved
Affected Versions Exasol 8.9.0, Exasol 8.29.0, Exasol 2025.1.0
Fix Versions Exasol 2025.1.17
Resolution Date 2026-11-12

Description

A query combining SELECT DISTINCT * with a join using a USING clause could cause the SQL process to crash during query compilation.

The issue occurs when the following conditions are present:

  • The query uses SELECT DISTINCT.
  • The select list uses an unqualified *.
  • The query contains a JOIN ... USING clause.
  • The USING clause references a column common to the joined tables.
  • The query joins a table with a common table expression (WITH clause).
  • The common table expression selects the column used by the USING clause.
  • The query is compiled with the DISTINCT operation applied to the joined result.

For example:

CREATE SCHEMA test;
OPEN SCHEMA test;

CREATE OR REPLACE TABLE T1(id INT);
CREATE OR REPLACE TABLE T2(id INT);

-- Observed: Internal server error
-- Expected: The query completes successfully and returns zero rows
--           with a single column named ID.
WITH C AS
(
    SELECT id FROM test.T1
)
SELECT DISTINCT *
FROM test.T2
JOIN C USING(id);

Workaround

Replace * in the SELECT DISTINCT clause with an explicit list of all required output columns.

For example:

OPEN SCHEMA test;

-- Expected/Observed: The query completes successfully and returns zero rows
--                    with a single column named ID.
WITH C AS
(
    SELECT id FROM test.T1
)
SELECT DISTINCT id
FROM test.T2
JOIN C USING(id);

Fix

The query works as intended.