Possible wrong results when using outer join for sub-selects

Detail name	Value
Changelog Number	5601
Type	Bug
Status	Resolved
Affected Versions
Fix Versions	EXASOL 6.0.7
Resolution Date	2018-01-31

Problem Description

Due to a bug in filter propagation optimization, filters may be pushed down to incorrect sub-selects which leads to incorrect results being produced.

This issue was reproducible in the following example.

create table t1 (i1 int, d1 decimal);
insert into t1 values (1, 100), (2, 200), (3, 300);

create table t2 (i2 int, d2 decimal);
insert into t2 values (1, 100), (2, 200), (3, 300);

create table t3 (i3 int, d3 decimal);
insert into t3 values (2, 200), (3, 300);

create view v1 as
    (select * from t1 group by 1, 2);
create view v2 as
    (select * from t2 group by 1, 2);
create view v3 as
    (select * from t3 group by 1, 2);

select *
    from v1
    left join v2 on v1.i1 = v2.i2
    left join v3 on v1.i1 = v3.i3 and v3.i3 = 3;

The query gives the following incorrect result where the filter v3.i3 = 3 is wrongly propagated to v2.i2.

I1	D1	I2	D2	I3	D3
1	100
2	200
3	300	3	300	3	300
4	400

The expected result is,

I1	D1	I2	D2	I3	D3
1	100	1	100
2	200	2	200
3	300	3	300	3	300
4	400

Circumstances

This error may occur in a relatively rare scenario where all of the following conditions apply:

• When sub-selects are used. Views which are not materialized are also transformed into sub-select expressions by the compiler.
• The optimizer is unable to eliminate such sub-select expressions. This happens for many reasons, one of which is that the sub-select expression uses GROUP BY clause.
• When left or right outer joins are used and at least one sub-select occurs in the inner table.
• When outer join conditions contain local filters on join conditions that lead to other tables.

Workaround

As a workaround, it should be possible enforce pre-materializion of the offending table using a subselect containing the problematic local filter:

select *
    from v1
    left join v2 on v1.i1 = v2.i2
    left join (
		select * from v3
		where i3 = 3 -- problematic filter
		order by false -- enforce metarialization
	) v3 on v1.i1 = v3.i3;