Query with uncorrelated EXISTS subquery crashes if same index is used inside and outside of the subquery

Detail name	Value
Changelog Number	6418
Type	Bug
Status	Resolved
Affected Versions	EXASOL 6.0.0
Fix Versions	Exasol 6.0.11
Resolution Date	2018-07-10

Description

To process uncorrelated EXISTS Exasol typically materializes the EXISTS subquery. The subquery can be more complex and contain joins and index accesses.

There is a bug so that a query may crash if it contains an uncorrelated EXISTS subquery and uses the same index inside and outside of the subquery.

Example

The following example shows a critical index usages on T2.ID. The column is joined inside and outside of the uncorrelated exists. The left joins ensure that table T2 is joined and not scanned.

select * from T1 left join T2 on T1.ID = T2.ID where 
  exists (
    select * from T3 left join T2 on T3.PARENT_ID = T2.ID
  )

Workaround

You can avoid the problem by encapsulating the EXISTS subquery within another subselect that accesses ROWNUM:

select * from T1 left join T2 on T1.ID = T2.ID where
  exists (
    select ROWNUM from (
      select * from T3 left join T2 on T3.PARENT_ID = T2.ID
    )
  )