Inefficient Join when using a join-condition from WHERE in combination with OUTER JOIN

Detail name	Value
Changelog Number	4967
Type	Bug
Status	Resolved
Affected Versions	EXASOL 6.0.0, Exasol 6.1.0
Fix Versions	Exasol 6.2.0
Resolution Date	2019-04-18

Observable behaviour

Some queries that join a table to a subselect/view using the SQL89-Syntax (just comma-separated tables in FROM and join condition in WHERE-clause) are slow. In PROFILE system tables an NL-JOIN (i.e. CROSS JOIN) is shown even though a more efficient equi ('=') condition is given in WHERE-clause.

More detailed, the query needs the following elements:

• a surrounding select joining a table with a subselect/view with an equi-condition in WHERE-clause (SQL89 join syntax)
• the subselect/view joined has SQL92 OUTER JOIN syntax and has a select list column that combines columns from both sides of an OUTER JOIN
• the surrounding select joins on the column with expression from both sides of the OUTER JOIN
• specific conditions that make it necessary to materialize the subselect/view don't exist in the inner subselect/view (e.g. GROUP BY, ORDER BY, analytic functions, etc.)

Note: A similar situation occurs if the subselect view uses old (+)-style outer joins.

The internal reason for this bug is that the optimizer eliminates the subquery/view in case, i.e. the subquery/view is merged into the surrounding select. This results in a new select with all tables from surrounding select and subquery/view combined. In this more complex select combining an OUTER JOIN with a WHERE-join the optimizer is not intelligent enough to determine that the WHERE-condition could be safely used as a ON-condition for an INNER JOIN.

Testcase

create or replace table t_a (the_id varchar(256));

create or replace table t_b_1( other_id int, optional_id varchar(256) );
create or replace table t_b_2( other_id int, id_valid bool );

create or replace view v_b as
	select t_b_1.other_id,
		case when t_b_2.id_valid then nvl(optional_id, 'null')
			else 'null'
		end as expr_id
	from t_b_1
	left join t_b_2
		on t_b_1.other_id = t_b_2.other_id
;

-- creates a NL-JOIN (CROSS JOIN) with table t_a
select *
from t_a, v_b
where t_a.the_id = v_b.expr_id;

Workarounds

There are different workarounds depending on the situation:

1. If you can control the surrounding select it is best to replace SQL89 WHERE-join with SQL92-INNER JOIN:
2. If you can only modify the definition of the view/subquery, the workaround is to enforce materialization of the view using ORDER BY FALSE. This works as a workaround but should be removed again when the problem is fixed since needless materialization can (and usually will) cost performance

select *
from t_a INNER JOIN v_b ON t_a.the_id = v_b.expr_id;

create or replace view v_b as
	select t_b_1.other_id,
		case when t_b_2.id_valid then nvl(optional_id, 'null')
			else 'null'
		end as expr_id
	from t_b_1
	left join t_b_2
		on t_b_1.other_id = t_b_2.other_id
ORDER BY FALSE
;