Exasol syntax no longer allows unbracketed CTE on right-hand side of a table operator

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

Background

Exasol allowed Common Table Expression (i.e., 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 2023 SQL Standard.

SELECT * FROM a
UNION ALL
WITH w AS (SELECT * FROM b)
SELECT * FROM w
UNION ALL
SELECT * FROM c;

Improvement

Exasol doesn’t allow Common Table Expression (i.e., CTE, WITH clause) to be used on the right-hand side of a table operator without being put in parentheses anymore.

Workaround

Existing queries that use this syntax can be rewritten by adding brackets as appropriate (as in Q1 or Q2 below) or moving the WITH clause before the first table operator (as in Q3). Note that adding brackets around later table operators (e.g., UNION), as in Q1, will result in two operations in the execution, which will be slower than Q2 or Q3, as sometimes all three table operation operands could be processed together.

-- Q1
SELECT * FROM a
UNION ALL
(
  WITH w AS (SELECT * FROM b)
  SELECT * FROM w
  UNION ALL
  SELECT * FROM c
);

-- Q2
SELECT * FROM a
UNION ALL
(
  WITH w AS (SELECT * FROM b)
  SELECT * FROM w
)
UNION ALL
SELECT * FROM c;

-- Q3
WITH w AS (SELECT * FROM b)
SELECT * FROM a
UNION ALL
SELECT * FROM w
UNION ALL
SELECT * FROM c;

Changed behavior

Exasol no longer allows common table expressions (CTEs) that are not in parentheses to be used immediately after a table operator. For example, "SELECT ... UNION WITH CTE AS (SELECT ...) SELECT ..." is no longer allowed.