Internal server error for CONNECT BY on JOIN of at least 3 tables

Detail name	Value
Changelog Number	9308
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.2.0
Fix Versions	Exasol 7.0.0, Exasol 6.2.4
Resolution Date	2020-01-13

Description

A query with CONNECT BY on a FROM with joins returns 'Successfully reconnected after internal server error' instead of a correct result. This happens even if the query is only part of a view definition (CREATE VIEW).

The bug occurs under the following detailed conditions:

• The query contains a CONNECT BY clause on a FROM that joins at least 3 tables
• Either the join-conditions (ON-clause) of the joins don't reference all the tables joined by that specific join (typical case)
• Or a join-condition contains a scalar subquery (unusual case)

Example

create or replace table t1 ( i int, j int, k int);
create or replace table t2 ( j int );
create or replace table t3 ( k int );

select 1
from
  t1 inner join t2 on t1.j=t2.j
     join t3 on t1.j=t3.k -- joins (t1,t2) with t3, but doesn't reference t2
connect by prior t1.i=t1.i;

Workaround

It is possible to fix this issue by avoiding the conditions:

Option 1: Put FROM-clause in subselect

select 1
from
  (select t1.i as t1_i
   from t1 inner join t2 on t1.j=t2.j
           join t3 on t1.j=t3.k)
connect by prior t1_i=t1_i;

Option 2: Add dummy references to the tables that are not referenced yet

select 1
from
  t1 inner join t2 on t1.j=t2.j
     join t3 on t1.j=t3.k AND (t2.j IS NULL or t2.j IS NOT NULL)
connect by prior t1.i=t1.i;