Analytic function with window frame may cause "Internal server error"

Detail name	Value
Changelog Number	12028
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.2.0, Exasol 7.0.0
Fix Versions	Exasol 6.2.15, Exasol 7.0.10
Resolution Date	2021-05-11

Description

In complex scenarios, a query with an analytic function may return "Internal server error" when the window frame boundary (ROWS BETWEEN) contains column lookups.

This may happen if the following conditions are met:

• an analytical function is used
• the analytical function contains a window frame boundary (ROWS BETWEEN)
• the window frame boundary contains column lookups
• the column from the window clause is from a subselect with joins
• the analytic function is used in nested WITH-clauses

Testcase

Preparation

create schema test;
create or replace table tab (X int, Y int);

Example

WITH 
CTE_analytic_view AS (
SELECT
	sum(y) OVER (	PARTITION BY 1
					ORDER BY 1 
					ROWS BETWEEN x PRECEDING AND CURRENT ROW) AS dynamic_range_sum
FROM
	(
	SELECT
		a.x,
		a.y
	FROM
		TAB a
	JOIN TAB b ON
		a.x = b.x )
),
CTE_additional_view AS (
SELECT
	dynamic_range_sum
FROM
	CTE_analytic_view
)
SELECT
	*
FROM
	CTE_additional_view;

Workaround

There is no workaround.

Fix

The query runs without problems.