Unbracketed CTE on right-hand side of a table operator with ORDER BY or LIMIT can give wrong results

Detail name	Value
Changelog Number	28243
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 allows a non-standard syntax allowing a Common Table Expression (CTE, WITH clause) to be used on the right-hand side of a table operator without being put in parentheses. (This is not allowed by the 2016 SQL Standard.)

If such a CTE has 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.

Examples

-- Q1
-- Expected: 1 row, values (1)
-- Observed: 2 rows, values (2), (1)
SELECT 2 c
UNION ALL
WITH t AS ( SELECT 1 c )
SELECT 1 FROM dual ORDER BY 1 LIMIT 1;

-- Q2
-- Expected: 2 rows, values (1), (2) in that order
-- Observed: 2 rows, values (2), (1) in that order
SELECT 2 c
UNION ALL
WITH t AS ( SELECT 1 c )
SELECT 1 FROM dual ORDER BY 1;

Workarounds

Wrap the query within SELECT … ORDER BY …, moving the query’s ORDER BY and LIMIT clauses to the outer SELECT:

-- Workaround applied to Q1
-- Expected/Observed: 1 row, values (1)
SELECT *
FROM (SELECT 2 c
      UNION ALL
      WITH t AS ( SELECT 1 c )
      SELECT 1 FROM dual)
ORDER BY 1 LIMIT 1;

Alternatively, move the WITH clause before the first table operand or reorder the table operands to put the WITH clause first:

-- Workaround applied to Q2
-- Expected/Observed: 2 rows, values (1), (2) in that order
WITH t AS ( SELECT 1 c )
SELECT 2 c
UNION ALL
SELECT 1 FROM dual ORDER BY 1;

-- Workaround applied to Q2 with table operands swapped
-- Expected/Observed: 2 rows, values (1), (2) in that order
WITH t AS ( SELECT 1 c )
SELECT 1 FROM dual
UNION ALL
SELECT 2 c ORDER BY 1;

Fix

Exasol no longer allows queries of this non-standard form: see Changelog entry 28252. If a table operand is a CTE then the query must now have explicit parentheses around the operand.