Aggregating over subselects containing scalar-emits UDFs might return wrong results

Detail name	Value
Changelog Number	7708
Type	Bug
Status	Resolved
Affected Versions	EXASOL 6.0.0, Exasol 6.1.0
Fix Versions	Exasol 6.0.15, Exasol 6.1.2
Resolution Date	2019-03-27

Problem description
The Exasol-Compiler optimizes queries of the form

SELECT <aggregate-function-or-set-udf> FROM
   SELECT <scalar-emits-udf> FROM XXXX
GROUP BY ...

In such a way that the subselect does not have to be materialized but instead is
computed in a single-pass "Map-Reduce-Style" computation.

This optimization has a bug that can lead to wrong results if XXXX (in the query above) contains views.

How to reproduce

CREATE OR REPLACE LUA SCALAR SCRIPT s (c varchar(5)) EMITS (x CLOB) AS
function run(ctx)
 ctx.emit(ctx[1])
end
/

CREATE OR REPLACE TABLE t AS SELECT 'c1' AS c1, 'c2' AS c2;

CREATE OR REPLACE VIEW v AS SELECT * FROM t ;

SELECT x , COUNT(*) FROM (
  SELECT s(c2) FROM v  
) GROUP BY x;

Workaround
There are two possible workarounds for dealing with this problem:
1. Don't use a view in the emitting scalar UDF. This query returns the correct result:

SELECT x , COUNT(*) FROM (
  SELECT s(c2) FROM t 
) GROUP BY x;

2. Define the View using order by false. If the view is defined like this, the original query will return the correct result:

CREATE OR REPLACE VIEW v AS SELECT * FROM t  ORDER BY FALSE;

Please note: Both workarounds will prevent the optimization that causes the problem, which means they will possibly have worse performance compared to the fixed version.