Column name resolution of WITH clause in combination with UNION ALL fails

Detail name	Value
Changelog Number	20541
Type	Bug
Status	Resolved
Affected Versions	Exasol 8.0.0
Fix Versions	Exasol 8.29.0
Resolution Date	2024-07-12

Description

A valid query containing a WITH clause fails if:

• The WITH clause contains a UNION ALL table.
• The WITH clause sets different column names than the first SELECT within the UNION ALL table.
• The query uses the column names of the WITH clause.

Example

-- This fails with "object D not found". 
WITH q(d,e,f) AS (
SELECT 1 a, 2 b, 3 c
UNION ALL
SELECT 1 a, 2 b, 3 c
)
SELECT d FROM q;

Workaround

Set the same column names in the WITH clause as well as the first SELECT of the UNION ALL table.

WITH q(d,e,f) AS (
SELECT 1 d, 2 e, 3 f
UNION ALL
SELECT 1 a, 2 b, 3 c
)
SELECT d FROM q;

Fix

Those queries run as expected.