WITH-clause combined with prepared parameters influences join order

Detail name	Value
Changelog Number	8697
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.1.0, Exasol 6.2.0
Fix Versions	Exasol 7.0.0
Resolution Date	2020-09-11

Problem

Using a WITH-clause combined with prepared parameters influences the join order in a query. This can have a relevant performance impact on the query execution.
When they are not combined in one query (only prepared parameters or only a WITH-clause) the expected optimal join order can be achieved.

The underlying problem is that the parameter within the WITH clause is ignored when estimating filter effects.

Example1:

WITH TEMP(col) AS (SELECT 1)
    SELECT 1 FROM t1
    JOIN t2 ON t2.t1_id = t1.id
    JOIN t3 ON t3.id = t2.t3_id
    JOIN t4 ON t4.id = t3.t4_id
    JOIN t5 ON t5.id = t4.t5_id
    WHERE t1.id = ?;

Example2:

WITH TEMP AS 
     (SELECT t1.id FROM t1
     JOIN t2 ON t2.t1_id = t1.id
     JOIN t3 ON t3.id = t2.t3_id
     JOIN t4 ON t4.id = t3.t4_id
     JOIN t5 ON t5.id = t4.t5_id
     WHERE t1.id = ?)
SELECT * FROM TEMP; 

Workaround

Possible workarounds can be seen in the alternatives for the previous listed examples.
In the first example possible alternatives can be achieved by removing the WITH-clause or replacing the prepared parameter with a constant.
The second example shows that the use of WITH-clause can also be replaced by the use of a sub-select.

Alternative for Example 1:

WITH TEMP(col) AS (SELECT 1)
    SELECT 1 FROM t1
    JOIN t2 ON t2.t1_id = t1.id
    JOIN t3 ON t3.id = t2.t3_id
    JOIN t4 ON t4.id = t3.t4_id
    JOIN t5 ON t5.id = t4.t5_id
    WHERE t1.id = 10000;

SELECT 1 FROM t1
JOIN t2 ON t2.t1_id = t1.id
JOIN t3 ON t3.id = t2.t3_id
JOIN t4 ON t4.id = t3.t4_id
JOIN t5 ON t5.id = t4.t5_id
WHERE t1.id = ?;

Alternative for Example 2:

SELECT * FROM (SELECT t1.id FROM t1
     JOIN t2 ON t2.t1_id = t1.id
     JOIN t3 ON t3.id = t2.t3_id
     JOIN t4 ON t4.id = t3.t4_id
     JOIN t5 ON t5.id = t4.t5_id
     WHERE t1.id = ?);