Using EMITed columns in a surrounding HAVING clause returns an error

Detail name	Value
Changelog Number	11244
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.1.0, Exasol 6.2.0, Exasol 7.0.0
Fix Versions	Exasol 7.0.5
Resolution Date	2020-12-18

Description

The following conditions will cause the error message:

HAVING on result of a map script is not allowed.

• A query uses an emitting UDF
• The UDF is called within a subselect
• The emitted result of the UDF is used in a surrounding HAVING clause
• The HAVING clause can either be explicit or implicit

Preparation

drop schema if exists test cascade;
create schema test;

--/
create or replace lua scalar script my_udf(col varchar(10)) emits (res varchar(10))
as
function run(ctx)
    ctx.emit('a');
end
/ 

Example with explicit HAVING

select 
    max(a.res) as m_res 
from 
    (select my_udf('test') res from dual) a 
group by 
    a.res 
having 
    max(a.res) = 'a';

Example with implicit HAVING

select * from 
    (select max(a.res) as m_res 
        from 
            (select my_udf('test') res from dual) a 
        group by a.res
     ) b 
where 
    b.m_res = 'a';

Both of the above queries return the error message mentioned above,

Workaround

Adding ORDER BY 1 to the subquery containing the UDF avoids the error

select 
    max(a.res) as m_res 
from 
    (select my_udf('test') res from dual order by 1) a 
group by 
    a.res 
having 
    max(a.res) = 'a';

Fix

The query will not return an error message as expected.