Correlated Subselects in Select List may produce wrong results

Detail name	Value
Changelog Number	5198
Type	Bug
Status	Resolved
Affected Versions	EXASOL 6.0.3
Fix Versions	EXASOL 6.0.5
Resolution Date	2017-10-18

Scenario

A correlated subselect in the select list may return false positives if its own select element is a constant.

Example

create or replace table a (userid int);
insert into a values 0, 1, 2, 3;
create or replace table b (userid int);
insert into b values 0, 2;

select userid, (select 1 from b where a.userid = b.userid) as flg from a;

USERID	FLG
0	1
1	1
2	1
3	1

The expected result would be NULL-values for USERID 1 and 3.

Workaround

Subselects using an actual column or an aggregation are not affected:

select
userid, 
(select 1 from b where a.userid = b.userid) as flg_1,
(select	userid from b where a.userid = b.userid) as flg_id,
(select max(1) from b where a.userid = b.userid) as flg_max
from a;

 

USERID	FLG_1	FLG_ID	FLG_MAX
0	1	0	1
1	1	(null)	(null)
2	1	2	1
3	1	(null)	(null)

Fix

This fix will make FLG_1 behave like FLG_MAX in the example above.