Internal server error in Analytic Functions with Virtual Schema and Order by expression

Detail name	Value
Changelog Number	9908
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.0.0, Exasol 6.2.5
Fix Versions	Exasol 7.0.11
Resolution Date	2021-07-16

Description

An internal server error occurs when Analytic Functions are used with Virtual Schema and Order by expression. It only occurs when the virtual schema supports 'ORDER BY EXPRESSION' capability and the order by expression is a constant and the columns used by the analytic function are not part of the select list.

Steps to reproduce

create schema test;

--/
CREATE OR REPLACE PYTHON ADAPTER SCRIPT test.vs_adapter AS
import cjson
def adapter_call(requestJS):
    req = cjson.decode(requestJS)
    reqType = req['type']
    res = { 'type' : reqType }
    if reqType == 'createVirtualSchema':
        res['schemaMetadata'] = {
            'tables': [{
                'name': 'T',
                'columns': [{
                    'name': 'C1',
                    'dataType': {'scale': 0, 'type': 'DECIMAL', 'precision': 18}
                 },{
                    'name': 'C2',
                    'dataType': {'scale': 0, 'type': 'DECIMAL', 'precision': 18}
                 },{
                    'name': 'C3',
                    'dataType': {'scale': 0, 'type': 'DECIMAL', 'precision': 18}
                 }]
            }]
        }
    elif reqType == 'getCapabilities':
        res['capabilities'] = [ 'FN_AGG_FIRST_VALUE','ORDER_BY_EXPRESSION','LITERAL_BOOL' ]
    elif reqType == 'pushdown':
        res['sql'] = 'SELECT 1, 1, 1'    return cjson.encode(res).encode('utf-8')
/

CREATE VIRTUAL SCHEMA test_vs USING test.vs_adapter;

Given the above setup, this query results in an internal server error:

SELECT FIRST_VALUE(c1) OVER ( PARTITION BY c2 ORDER BY c3 ) 
FROM test_vs.t order by false;

Workaround

Following workarounds can be used.

• Add the column used in the Analytic Function to the select list:

SELECT c1, FIRST_VALUE(c1) OVER ( PARTITION BY c2 ORDER BY c3 ) FROM test_vs.t order by false;

• Move the ORDER BY FALSE clause to the outer query.
• Do not push down ORDER BY EXPRESSION to the Virtual Schema (disable ORDER_BY_EXPRESSION capability}