Wrong results for query with subselect with CONNECT BY and distributed table on multi-node systems

Detail name	Value
Changelog Number	32212
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.0.0, Exasol 6.2.4, Exasol 7.1.0, Exasol 8.0.0, Exasol 8.29.0, Exasol 2025.1.0, Exasol 2025.2.0, Exasol 2026.1.0
Fix Versions	Exasol 2025.1.16
Resolution Date	2026-08-28

Description

A query can return wrong results if all of the following conditions are met:

• The query is running on a multi-node system.
• An inner SELECT uses CONNECT BY and a distributed table and returns all columns from the distribution key of the distributed table.
• An outer SELECT contains an operation that uses the distribution, e.g., grouping by all the distribution key columns.

Example

CREATE OR REPLACE TABLE t AS SELECT a, 0 b FROM VALUES BETWEEN 1 AND 30 t(a) CROSS JOIN VALUES 1,2,3;
ALTER TABLE t DISTRIBUTE BY a;

-- Bug: This query returns more than one row instead of the expected one row.
WITH cte AS (
    SELECT a
    FROM t
    CONNECT BY NOCYCLE PRIOR b = a
)
SELECT a
FROM cte
WHERE a = 10
GROUP BY a;

Workaround

Add ORDER BY FALSE to the subselect with CONNECT BY:

WITH cte AS (
    SELECT a
    FROM t
    CONNECT BY NOCYCLE PRIOR b = a
    /* workaround: */ ORDER BY FALSE
)
SELECT a
FROM cte
WHERE a = 10
GROUP BY a;

Fix

Affected queries now return correct results.