Internal server error when using a comparing column from outer subselect with scalar-joined subquery

Detail name	Value
Changelog Number	13675
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.1.0
Fix Versions	Exasol 8.0.0, Exasol 7.1.7
Resolution Date	2022-03-16

Description

An internal server error may occur if the following conditions are met:

• A query joins a subselect with an outer join
• A column of the outer join is involved in a comparison with a scalar subquery outer_col = (<scalar_subselect>)
• The scalar_subselect itself includes joins to other tables

Preparation

create table t1(join_col int);
create table t2(join_col int, comp_col int);
create table tsub1(join_col int, comp_col int);
create table tsub2(join_col int);

Example

-- This query gets an internal server error
select * from (
  select 1
  from (
    select outer_subsel.comp_col as outer_comp_col
    from t1
      left join (select join_col, comp_col from t2 order by false) outer_subsel 
        on t1.join_col=outer_subsel.join_col
  )
  where
    -- comparison between outer column and scalar subquery with joins
    outer_comp_col = (select tsub1.comp_col from tsub1 join tsub2 on tsub1.join_col = tsub2.join_col)
  order by false
);

Workaround

Wrap the scalar subquery with an expression, for example using IS TRUE or CAST(... AS BOOL):

select * from (
  select 1
  from (
    select outer_subsel.comp_col as outer_comp_col
    from t1
      left join (select join_col, comp_col from t2 order by false) outer_subsel 
        on t1.join_col=outer_subsel.join_col
  )
  where
    -- comparison embedded in is true
    (outer_comp_col = (select tsub1.comp_col from tsub1 join tsub2 on tsub1.join_col = tsub2.join_col)) is true
  order by false
);

Fix

The query does not return an internal server error, as expected.