Mixing LOCAL, GROUP BY and analytic functions can lead to an error

Detail name	Value
Changelog Number	3803
Type	Bug
Status	Resolved
Affected Versions	EXASolution 5.0.0
Fix Versions	EXASOL 6.0.0, EXASolution 5.0.19
Resolution Date	2017-03-16

The combination of LOCAL, GROUP BY and analytic functions can cause an internal server error.

Example 1:

CREATE SCHEMA S;
CREATE OR REPLACE TABLE V (
    C0            INT,
    C4            DATE
);

INSERT INTO S.V VALUES (2,'2016-11-01');

SELECT  C0,
        COUNT( C0 ) AS VAL_ALL,
        LOCAL.VAL_ALL=1 AND LOCAL.VAL_ALL=1

        ,
        SUM(c0) OVER (PARTITION BY 1)=0 x FROM S.V group by 1;

SELECT  C0,
        COUNT( C0 ) AS VAL_ALL,
        LOCAL.VAL_ALL=1 AND LOCAL.VAL_ALL >= 1 AND SUM( COUNT(*) ) OVER (PARTITION BY C0) =0 x
FROM S.V GROUP BY 1;	

Example 2:

CREATE SCHEMA TEST;

CREATE OR REPLACE TABLE T0 (C0 int, C1 TIMESTAMP); INSERT INTO T0 VALUES (1,FROM_POSIX_TIME(2)); INSERT INTO T0 VALUES (2,FROM_POSIX_TIME(1));

SELECT
	MAX(T0.C1) AS C2,
	NVL(
		SECONDS_BETWEEN(
			LEAD(LOCAL.C2, 1) OVER(ORDER BY FALSE),
			LOCAL.C2
		),
		0
	)
FROM
	T0
GROUP BY
	T0.C0,
	T0.C1;

Workaround:
Rewrite the query to either avoid local, the analytic function or the group by within the same query.