Correlated subqueries in filters comparing count(*) to 0 do not work if the subquery contains a local filter that removes all its elements

Detail name	Value
Changelog Number	5096
Type	Bug
Status	Resolved
Affected Versions	EXASOL 6.0.0, EXASolution 5.0.0, Exasol 6.1.0, Exasol 6.2.0, Exasol 7.0.0
Fix Versions	Exasol 7.1.1, Exasol 7.0.13
Resolution Date	2021-09-14

Problem

queries with correlated filters that use count and compare these counts to 0 do not work.

Workaround

Put the filter in the select list and wrap another SELECT to filter the desired rows:

For example.

with 
sess as (select 100 as id from dual union all select 200 as id from dual ),
sq   as (select 100 as id , null as err from dual )
select sess.* from sess
where  0=(select count(*) from sq where sq.id=sess.id and err is not null);

should be rewritten as

with 
sess as (select 100 as id from dual union all select 200 as id from dual ),
sq   as (select 100 as id , null as err from dual )
select id from (
select sess.*, 0 = (select count(*) from sq where sq.id=sess.id and err is not null) as f
from sess
)
where f;