Error on UNION with window function and ORDER BY

Detail name	Value
Changelog Number	8214
Type	Bug
Status	Resolved
Affected Versions	EXASOL 6.0.0, Exasol 6.1.0, Exasol 6.2.0, Exasol 7.0.0, Exasol 7.1.0, Exasol 8.0.0, Exasol 8.29.0, Exasol 2025.1.0, Exasol 2025.2.0
Fix Versions	Exasol 2026.1.0, Exasol 2025.2.1
Resolution Date	2026-03-20

Description

A query with a UNION which orders by a window function can give the error “order by object not found”.

Example

-- Observed: Error "order by object not found"
SELECT RANK() OVER (ORDER BY a1) FROM a
UNION
SELECT a1 FROM a
ORDER BY 1;

-- Observed: Error "order by object not found"
SELECT COUNT(*) OVER () as count_star FROM a
UNION
SELECT a1 FROM a
ORDER BY count_star;

Workaround

Put a SELECT around the first table operand.

SELECT * FROM (SELECT RANK() OVER (ORDER BY a1) FROM a)
UNION
SELECT a1 FROM a
ORDER BY 1;

SELECT * FROM (SELECT COUNT(*) OVER () as count_star FROM a)
UNION
SELECT a1 FROM a
ORDER BY count_star;

Fix

The query succeeds.