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 ... USINGclause. - The
USINGclause references a column common to the joined tables. - The query joins a table with a common table expression (
WITHclause). - The common table expression selects the column used by the
USINGclause. - The query is compiled with the
DISTINCToperation 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.