Virtual schema error with analytic function and ORDER BY

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

Description

A query will return an internal server error if the query meets the following conditions:

• The query references a virtual table
• The query contains an analytic function
• The query contains an ORDER BY

Preparation

Any virtual schema can be used, the below example creates one using a test adapter.

CREATE SCHEMA test;
--/
CREATE OR REPLACE PYTHON ADAPTER SCRIPT test.vs_adapter AS
import cjson
def adapter_call(requestJS):
 req = cjson.decode(requestJS)
 req['schemaMetadata'] = {'tables': [{'name': 'TBL', 'columns': [{'dataType': {'type': 'BOOLEAN'}, 'name': 'COL1'}]}]} 
 req['capabilities'] = ['SELECTLIST_PROJECTION','SELECTLIST_EXPRESSIONS','ORDER_BY_EXPRESSION','LITERAL_EXACTNUMERIC']
 req['sql'] = 'SELECT TRUE'
 return cjson.encode(req).encode('utf-8')
/
CREATE VIRTUAL SCHEMA test_vs USING test.vs_adapter;

Example

SELECT 2 AS col1 ,LAG( 1, 1 ) OVER (order by 1) AS col2 FROM test_vs.tbl ORDER BY 1;

Workaround

Remove the ORDER_BY_EXPRESSION capability from the virtual schema adapter.

Fix

Queries meeting the above conditions will run, as expected.