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

Detail name	Value
Changelog Number	31187
Type	Bug
Status	Resolved
Affected Versions	Exasol 2026.1.0
Fix Versions	Exasol 2026.1.2
Resolution Date	2026-09-14

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.