Outer join and complex expression in subquery can give an error

Detail name	Value
Changelog Number	23791
Type	Bug
Status	Resolved
Affected Versions	Exasol 8.19.0
Fix Versions	Exasol 8.34.0, Exasol 8.29.9
Resolution Date	2025-04-02

Description

If a query has an outer join, where the NULL-generating side of the join is a subselect that projects a complex expression, then in some cases it will give the error “Feature not supported: subselects as row value expressions“ or an “Internal Server Error”.

Example

-- Expected: Success
-- Observed: Error "Feature not supported: subselects as row value expressions"
WITH temp_table_pre AS 
(SELECT
         'a' as x 
        , 'a' AS y
),

temp_table AS 
(SELECT x, CASE WHEN y = 'a' THEN 'b' END AS  y FROM temp_table_pre)
, 

temp_table2 AS 
(SELECT CONCAT(tp.y, tp.x) AS x 
FROM temp_table tp)
,

temp_table3 AS 
(SELECT tp.x
FROM temp_table tp
LEFT JOIN temp_table2 tp2
ON tp.x = tp2.x)
, 

temp_table4 as
(SELECT x 
FROM temp_table3
order by x
)
select * from temp_table4;

Workaround

Add "ORDER BY FALSE" to the outer table in the join with the complex expression.

WITH temp_table_pre AS 
(SELECT
         'a' as x 
        , 'a' AS y
),

temp_table AS 
(SELECT x, CASE WHEN y = 'a' THEN 'b' END AS  y FROM temp_table_pre)
, 

temp_table2 AS 
(SELECT CONCAT(tp.y, tp.x) AS x 
FROM temp_table tp order by false)
,

temp_table3 AS 
(SELECT tp.x
FROM temp_table tp
LEFT JOIN temp_table2 tp2
ON tp.x = tp2.x)
, 

temp_table4 as
(SELECT x 
FROM temp_table3
order by x
)
select * from temp_table4;

Fix

The query will run correctly, without giving an error.