Internal Server Error for UNION ALL with LIMIT in WITH-clause

Detail name	Value
Changelog Number	12888
Type	Bug
Status	Resolved
Affected Versions	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

Background

In Exasol versions prior to 2025.2.1 and 2026.1.0, table operators including UNION had a non-standard behavior where ORDER BY and LIMIT in the last table operand is applied to the whole table operator (even if it is in parentheses). ORDER BY and LIMIT are not allowed in the other table operands, even if they are in parentheses.

(From versions 2025.2.1 and 2026.1.0 onwards, the behavior is as described in the SQL standard where queries can specify ORDER BY and LIMIT clauses for table operands in parentheses. ORDER BY and LIMIT clauses after the last table operand applies to the whole table operator.)

Description

When using a SELECT with a WITH-clause as operand of UNION ALL the query returns an 'Internal Server Error' if the SELECT contains a LIMIT clause without ORDER BY.
If the SELECT with a WITH-clause uses ORDER BY and LIMIT the ordering and LIMIT are applied to the operand instead of to UNION ALL.

Note: Similar problems affect other table operators (UNION, MINUS, INTERSECT) and are also addressed by this ticket.

Testcases

-- Q1
-- Observed: The query fails with "Internal server error"
SELECT 1
UNION ALL
WITH cte AS ( SELECT 1 AS c )
SELECT 1 LIMIT 1;

-- Q2
-- Observed: The query fails with "Internal server error"
SELECT 1
UNION
(
  WITH cte AS ( SELECT 1 AS c )
  SELECT 1 LIMIT 1
);

Workaround

The query works just like any SELECT-query if the WITH-clause is rewritten in a standard compliant way.
For table operators that means we put the WITH-clauses before the table operator (before the first operand).

-- Put the WITH-clause to the beginning of the query
WITH t AS ( SELECT 1 c )
SELECT 1 c
UNION ALL
SELECT 1 FROM t LIMIT 1;

Alternatively, wrap a SELECT clause around the last table operand, leaving the ORDER BY and LIMIT clause at the end.

SELECT 1
UNION
SELECT * FROM (
  WITH cte AS ( SELECT 1 AS c )
  SELECT 1
)
-- Put the ORDER BY and LIMIT clause at the end of the query
LIMIT 1;

Fix

Query Q1: 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.

Query Q2: The query succeeds.