Wrong 'emitting script in HAVING clause' when an emitting script is used inside a scalar subquery
Details
| Detail name | Value |
|---|---|
| Changelog Number | 8748 |
| Type | Bug |
| Status | Resolved |
| Affected Versions | EXASOL 6.0.0, Exasol 6.1.0, Exasol 6.2.0, Exasol 7.0.0 |
| Fix Versions | Exasol 8.7.0, Exasol 7.1.16 |
| Resolution Date | 2022-11-01 |
Description
A wrong error 'emitting script in HAVING clause' is shown when an emitting script is used inside a scalar subquery in the having clause.
This might also occur in cases where a filter with a scalar subquery containing having is used in WHERE-clause or ON-clause and pushed into a subselect into the HAVING-clause.
Example
-- emitting script in HAVING clause select i, sum(j) sj from tab group by i having sum(j)<(select count(res) from (select emitting_udf(3) from dual)); -- does not show the problem in this simple example, but might in similar cases select * from (select i,sum(j) sj from tab group by i) where sj<(select count(res) from (select emitting_udf(3) from dual)); select * from (select i,sum(j) sj from tab group by i) sub join othertab on sub.i=othertab.i where sj<(select count(res) from (select emitting_udf(3) from dual));
Expected behavior
No error message. Just a running query. SQL is valid.
Workaround
- if the subquery with the emitting UDF is used in HAVING directly you can put an aggregation function around it
- if the subquery with the emitting UDF is not used in HAVING directly but put there through filter pushdown optimization you can rewrite the scalar subquery as a CROSS JOIN (only one row) - note: this might have impact on join queue estimation and that way performance
- Alternatively you can avoid pushing the subquery down by adding columns of other tables used in the query (without those columns modifying the result)
select i, sum(j) sj from tab group by i having sum(j)<first_value(select count(res) from (select emitting_udf(3) from dual));
select * from (select i,sum(j) sj from tab group by i) CROSS JOIN (select count(res) cnt from (select emitting_udf(3) from dual)) scalar_subquery where sj<scalar_subquery.cnt;
select * from (select i,sum(j) sj from tab group by i) sub join othertab on sub.i=othertab.i where sj+case when othertab.x=1 then 0 else 0 end<(select count(res) from (select emitting_udf(3) from dual));
