Preview Feature "experimental optimizer" runs for a long time for a query with many joins and atleast one cross join

Detail name	Value
Changelog Number	11509
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.0.0
Fix Versions	Exasol 7.1.0
Resolution Date	2021-08-03

Description

The preview feature "experimental optimizer" will run for a very long time for a query that matches following criteria. 

• more than 11 joins are involved
• one join is a CROSS JOIN
• one of the join conditions uses an expression that involves more than 2 tables, one of which is the cross join table.

The compiler makes a futile attempt to search for a query plan that does not require expensive cross join. 

preparation:

CREATE SCHEMA test;
CREATE OR REPLACE TABLE t1 (id INT);
CREATE OR REPLACE TABLE t2 (id INT);
CREATE OR REPLACE TABLE t3 (id INT);
CREATE OR REPLACE TABLE t4 (id INT);
CREATE OR REPLACE TABLE t5 (id INT);
CREATE OR REPLACE TABLE t6 (id INT);
CREATE OR REPLACE TABLE t7 (id INT);
CREATE OR REPLACE TABLE t8 (id INT);
CREATE OR REPLACE TABLE t9 (id INT);
CREATE OR REPLACE TABLE t10 (id INT);
CREATE OR REPLACE TABLE t11 (id INT);
CREATE OR REPLACE TABLE t12 (id INT);

example:

CONTROL SET JOIN OPTIMIZER EXPERIMENTAL;

SELECT 1
FROM t1
JOIN t2 ON t1.id = t2.id
JOIN t3 ON t2.id = t3.id
JOIN t4 ON t3.id = t4.id
JOIN t5 ON t4.id = t5.id
JOIN t6 ON t5.id = t6.id
JOIN t7 ON t6.id = t7.id
JOIN t8 ON t7.id = t8.id
JOIN t9 ON t8.id = t9.id
JOIN t10 ON t9.id = t10.id
CROSS JOIN t11
LEFT JOIN t12 ON t12.id = t11.id + t10.id;

Workaround

Use the default optimizer:

CONTROL SET JOIN OPTIMIZER DEFAULT;

Fix

The compiler analyzes the query and recognizes that a cross join is needed and reduces the search space for an optimal query plan.