Valid query with GROUP BY and UNION ALL can fail with invalid GROUP BY expression

Detail name	Value
Changelog Number	31187
Type	Bug
Status	Open
Affected Versions	Exasol 2026.1.0

Description

A valid query can fail with invalid GROUP BY expression when all of the following apply :

• The query uses an outer join
• The query groups the join result using GROUP BY or an aggregate function.
• One input of the outer join is produced by a UNION ALL, either directly or through a WITH clause.

The error can occur even when every selected non-aggregate expression is included in the GROUP BY clause.

Example :

CREATE OR REPLACE TABLE test.t (c int);

-- Expected: 1 row
-- Observed: Error "not a valid GROUP BY expression"
WITH cte1 AS
  (SELECT 1 c
   UNION ALL SELECT 1 c
   FROM test.t),
     cte2 AS
  (SELECT 1 c)
SELECT c
FROM cte1
LEFT JOIN cte2 USING (c)
GROUP BY c

Workaround

Disable PushAllJoinsIntoUnionsForGroupByPushdown optimization by adding ORDER BY FALSE to the UNION ALL.

CREATE OR REPLACE TABLE test.t (c INT);

WITH cte1 AS
  (SELECT 1 AS c
   UNION ALL SELECT 1 AS c
   FROM test.t
   ORDER BY FALSE),
     cte2 AS
  (SELECT 1 AS c)
SELECT c
FROM cte1
LEFT JOIN cte2 USING (c)
GROUP BY c;

Fix

The queries run as expected.