Make table operator syntax SQL Standard compliant

Details

Detail name	Value
Changelog Number	27358
Type	Improvement
Status	Resolved
Fix Versions	Exasol 2026.1.0, Exasol 2025.2.1
Resolution Date	2026-03-20

Description

According to the SQL Standard, a table operator (e.g. UNION, UNION ALL, INTERSECT, EXCEPT) can have ORDER BY and LIMIT clauses after the last table operand. These clauses apply to the whole result set of the table operator.

-- Setup
CREATE TABLE a AS SELECT * FROM VALUES (1), (2) AS a(a1);
CREATE TABLE b AS SELECT * FROM VALUES (3), (4) AS b(b1);
CREATE TABLE c AS SELECT * FROM VALUES (5), (6) AS c(c1);

-- SQL Standard/Exasol: 1 row, values (1)
SELECT * FROM a
UNION ALL
SELECT * FROM b
ORDER BY a1 LIMIT 1;

Table operands (which are query specifications) of a query operator that are not in parentheses can not have ORDER BY or LIMIT clauses.

-- SQL Standard/Exasol: Error "order by clause is not allowed in operand"
SELECT * FROM a
UNION ALL
SELECT * FROM b ORDER BY b1 LIMIT 1
UNION ALL
SELECT * FROM c;

According to the SQL Standard, you can apply a limit to a table operand by wrapping it in parentheses. However, Exasol’s behavior before this improvement differs in three ways:

It gives an error if a table operator has a parenthesized query specification with an ORDER BY or LIMIT clause as a table operand, except for the last one. See query Q1 below for an example.
If the last table operand is a parenthesized query specification with an ORDER BY clause then the name resolution references columns projected from the first table operand. This is likely to cause an “object not found” error. See query Q2 below for an example.
If the last table operand is a parenthesized query specification with a LIMIT clause then the limit will apply to the whole result set of the table operator, giving unexpected results. See query Q3 below for an example

-- Q1
-- SQL Standard: 3 rows, values (1), (2), (3), (5), (6)
-- Exasol: Error "order by clause is not allowed in operand"
SELECT * FROM a
UNION ALL
(SELECT * FROM b ORDER BY b1 LIMIT 1)
UNION ALL
SELECT * FROM c;

-- Q2
-- SQL Standard: 3 rows, values (1), (2), (3), (4), (5)
-- Exasol: Error "object C1 not found"
SELECT * FROM a
UNION ALL
SELECT * FROM b
UNION ALL
(SELECT * FROM c ORDER BY c1 LIMIT 1);

-- Q3
-- SQL Standard: 3 rows, values (1), (2), (3), (4), (5)
-- Exasol: 1 ROW, VALUES (1)
SELECT * FROM a
UNION ALL
SELECT * FROM b
UNION ALL
(SELECT * FROM c ORDER BY 1 LIMIT 1);

Workaround

Wrap the table operands including the ORDER BY and LIMIT clauses in a subselect. For example, you can rewrite the queries above as follows:

-- Workaround for Q1
-- SQL Standard/Exasol: 3 rows, values (1), (2), (3), (5), (6)
SELECT * FROM a
UNION ALL
SELECT * FROM (SELECT * FROM b ORDER BY b1 LIMIT 1)
UNION ALL
SELECT * FROM c;

-- Workaround for Q2 or Q3
-- SQL Standard/Exasol: 3 rows, values (1), (2), (3), (4), (5)
SELECT * FROM a
UNION ALL
SELECT * FROM b
UNION ALL
SELECT * FROM (SELECT * FROM c ORDER BY c1 LIMIT 1);

Improvement

We changed the table operator syntax so that use of ORDER BY, LIMIT and parentheses complies with the SQL Standard. The queries above behave as expected by the SQL Standard.

Notes and related changes

Expressions allowed in ORDER BY

The ORDER BY clause of a table operator must be the name of a column projected from the first table operand’s select list, a column sequence number (e.g, 1, 2, …) or FALSE.
Exasol no longer allows queries to use other constants in a table operator’s ORDER BY clause (e.g., Q5 below). It also no longer allows queries to use complex expressions in a table operator’s ORDER BY clause that are projected from the first table operand’s select list (e.g., Q4 below).
The special Exasol syntax order by false prevents subquery elimination on the complete union and prevents certain optimizations. For more details, see changelog entry 27741.

-- Setup
create table A (A1 int);

-- Ordering using a column projected from the select list of the first table operand
-- Observed (Exasol 8.x)/(Changelog Entry 27358): Success
select A1 * A1 as P from A union select 1 order by P;

-- Ordering using a column sequence number
-- Observed (Exasol 8.x)/(Changelog Entry 27358): Success
select A1 from A union all select B1 from B order by 1;

-- ORDER BY FALSE prevents elimination of the complete union
-- Observed (Exasol 8.x): Error: "order by object not found"
-- Observed (Changelog Entry 27358): Success
select A1 from A union all select B1 from B order by false;

-- Q4
-- Observed (Exasol 8.x): Success
-- Observed (Changelog Entry 27358): Error "Expressions are not allowed in ORDER BY on table operators"
select A1 * A1 as P from A union select 1 order by A1 * A1;

-- Q5
-- Observed (Exasol 8.x): Success
-- Observed (Changelog Entry 27358): Error "Only decimal constants and FALSE are allowed as column references in ORDER BY"
select A1, 'foo' from A union select A1, 'bar' from A order by 'foo';

-- Workaround for Q5 above
-- Observed (Exasol 8.x)/(Changelog Entry 27358): Success 
select A1, 'foo' str from A union select A1, 'bar' str from A order by str;

Improved error messages

We improved various error messages:

-- Observed (Exasol 8.x): Error: "order by object not found"
-- Observed (Changelog Entry 27358): Error: "FALSE in ORDER BY is not allowed with other sorting elements"
select A1 from A union select A1 from A order by A1, false;
-- Observed (Exasol 8.x): Error: "object B1 not found"
-- Observed (Changelog Entry 27358): Error: "Identifier B1 in ORDER BY on table operator is not found"
select A1 from A union select A1 from A order by B1;
-- Observed (Exasol 8.x): Error: "order by object not found"
-- Observed (Changelog Entry 27358): Error: "Only decimal constants and FALSE are allowed as column references in ORDER BY"
select A1 from A union select A1 from A order by 'foo';

Related changes

We fixed the precedence of EXCEPT, which should be the same as UNION and lower than INTERSECT. Previously, EXCEPT had an incorrect precedence higher than INTERSECT. For more details, see changelog entry 28180.

Exasol syntax no longer allows unbracketed Common Table Expression (i.e., CTE, WITH clause) on the right-hand side of a table operator. This fixes a possible wrong result bug when a query has such a CTE. For more details, see changelog entry 28252 and changelog entry 28243.
We fixed a bug that could give wrong results when using the SELECT … INVALID … syntax as a table operand to a table operator with an ORDER BY and LIMIT clause. For more details, see changelog entry 28244.

Breaking change

This is a breaking change and some queries may no longer work as intended. Please reach out to support if you require help.

Changed behavior

Changes the behavior of ORDER BY and LIMIT in combination with table operators to comply with the SQL Standard.