Fix wrong ordering with "SELECT INVALID UNIQUE / PRIMARY KEY / FOREIGN KEY"

Detail name	Value
Changelog Number	28244
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.1.0, Exasol 8.0.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

Exasol has SELECT … INVALID … syntax to verify unique, primary key and foreign key constraints for a table. If a query uses SELECT … INVALID … as the last table operand in a table operator (such as UNION, EXCEPT or INTERSECT) and it is followed by an ORDER BY or LIMIT clause, then the query can give wrong results, as according to the 2016 SQL Standard this ORDER BY or LIMIT clause should apply to the entire table operator.

-- Expected: 2 rows, values (NULL), ('a') in that order
-- Observed: 2 rows, values ('a'), (NULL) in that order
SELECT 'a' c
UNION ALL
SELECT * with invalid primary key (dummy) FROM dual order by 1 desc nulls first;

Workaround

Wrap the query with SELECT … ORDER BY.

-- Expected/Observed: 2 rows, values (NULL), ('a') in that order
SELECT *
FROM (SELECT 'a' c
      UNION ALL
      SELECT * with invalid primary key (dummy) FROM dual)
order by 1 desc nulls first;

Fix

The query returns the correct results.