Analytic function on subselect with prepared parameter causes error 'Successfully reconnected after internal server error'

Detail name	Value
Changelog Number	9619
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.2.0
Fix Versions	Exasol 7.0.0, Exasol 6.2.5
Resolution Date	2020-03-02

Description

In specific situations analytic functions in combination with prepared parameters can cause the error message:

[Code: 0, SQL State: 40005]  Successfully reconnected after internal server error, transaction was rolled back.

The circumstances might vary because there are a number of conditions that only cause this error if combined.

Roughly this happens in the following situation.

• The query has a WITH-clause analytic_with containing an analytic function in select-list
• The FROM-clause of that WITH-clause contains a subselect that directly or indirectly contains a prepared parameter
• The WITH-clause analytic_with is used at least twice in the query

Testcase

create table t(i int, j int);
WITH analytic_with AS                                  -- with clause
   (SELECT lead(i,1) OVER (ORDER BY 1 desc) AS af    -- analytic function
   FROM (SELECT i FROM t WHERE j = ?))             -- subselect with prepared parameter

, encompassing_with AS
   (SELECT 1 AS x FROM analytic_with, analytic_with) -- with-clause used at least twice

SELECT * FROM encompassing_with;

Workaround

There are two possible workarounds:

Replace parameters by values

If it is an option to not execute the query as prepared statement, but instead replace the prepared parameters by the values, the problem should not appear.

Avoid multiple uses of the WITH-clause by copying it

If prepared execution is needed in this situation the only known workaround is to copy the WITH-clause containing the analytic function in a way that each copy is only used once (even indirectly through other WITH-clauses).

Note: This also means that the number of prepared parameters of the query increases and you have to bind the same values multiple times.

For the simple example above this means:

WITH analytic_with AS
   (SELECT lead(i,1) OVER (ORDER BY 1 desc) AS af
    FROM (SELECT i FROM t WHERE j = ?))

, analytic_with_copy AS                                  -- create a copy
   (SELECT lead(i,1) OVER (ORDER BY 1 desc) AS af
    FROM (SELECT i FROM t WHERE j = ?))

, encompassing_with AS
(SELECT 1 AS x FROM analytic_with, analytic_with_copy) -- use the copy

SELECT * FROM encompassing_with;