Oracle Join Syntax causes unexpected error "Feature not supported: circular outer join"

Detail name	Value
Changelog Number	9495
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.2.0, Exasol 7.0.0
Fix Versions	Exasol 7.0.0, Exasol 6.2.5
Resolution Date	2020-02-18

Description

When using the Oracle (+)-syntax for outer joins the query might give the error message

Feature not supported: circular outer join

even though there are no circular dependencies between the outer joins.

This may happen if the following conditions apply:

• The query contains at least two outer joins that are not dependent on each other, i.e. the outer tables of those joins are not inner tables in one of the others
• The query contains another join that has at least one of the inner tables of one of those joins as outer table.

Note: In those cases we neither have a clear outer join chain (t1->t2->t3) nor a list of independent outer joins (t1->t2, t1->t3, t1->t4), but a mix of both cases.
Note: Whether the problem appears or not also depends on the order of the join conditions.

Example

create table t1(a int);
create table t2(b int);
create table t3(c int);
create table t4(d int);

-- This fails:
SELECT *
FROM t1, t2, t3, t4
WHERE
-- two outer joins that are not dependent on each other (t1->t3, t1->t4)
  t1.a = t3.c(+) 
AND t1.a = t4.d(+)
-- an additional dependency on at least one of those joins, in this case on both (t2->t1)
AND t2.b = t1.a(+);

Workaround

The problem appears in the transformation from the Oracle-(+)-syntax to the ANSI-join model of logical dependencies.
Therefor, there is no problem if the query is already written in ANSI-syntax:

-- This works:
SELECT *
FROM t1 RIGHT JOIN t2 ON t1.a=t2.b LEFT JOIN t3 ON t1.a=t3.c LEFT JOIN t4 ON t1.a=t4.c;

Further, as mentioned above, the problem partially depends on the order of conditions. So playing around with the order might help, in this case:

-- This works:
SELECT *
FROM t1, t2, t3, t4
WHERE
-- different order than before
  t1.a = t3.c(+)
AND t2.b = t1.a(+)
AND t1.a = t4.d(+);