Infinite loop when optimizing "WHERE false"

Detail name	Value
Changelog Number	7218
Type	Bug
Status	Resolved
Affected Versions	EXASOL 6.0.0, EXASolution 5.0.0, Exasol 6.1.0
Fix Versions	Exasol 6.0.14, Exasol 6.1.1
Resolution Date	2019-07-12

Problem Description

A query of this nature leads to an infinite loop:

• A join with a filter that evaluates to (constant) false
• one of the join tables is either a table value constructor or an IMPORT subselect

Example with TVC:

select cnt,42,TO_NUMBER(b.jid) from (select 1 as cnt from dual)
inner join (values 4711) b(jid) on 1=1 where false;

Example with the IMPORT subselect:

select cnt,42,TO_NUMBER(b.jid) from (select 1 as cnt from dual where 1=0)
left outer join (IMPORT FROM exa AT sys_connection statement 'SELECT 4711 as jid from dual') b on 1=1;

Keep in mind that the triggering filter condition might also be propagated/relocated to the join by our optimizer like in the IMPORT example above, where the DUAL subselect gets eliminated.

Workaround

Encapsulate the TVC/IMPORT subselect in a SELECT that enforces materialization (select * from ... order by false):

select cnt,42,TO_NUMBER(b.jid) from (select 1 as cnt from dual)
left outer join (select * from (values 4711) b(jid) order by false) b on 1=1 where false;