Analytic functions fails in combination with COUNT of HASHTYPE column and ORDER BY in OVER clause

Detail name	Value
Changelog Number	13502
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.0.0, Exasol 7.1.0
Fix Versions	Exasol 7.0.14, Exasol 7.1.4
Resolution Date	2021-11-26

Description

If the following conditions are met, the query will return an internal server error:

• Query contains COUNT as an analytic function
• The column referenced in the COUNT clause has the data type HASHTYPE
• The OVER clause contains an ORDER BY
• The ORDER BY only contains non-hashtype columns

Preparation

create or replace table t(h1 HASHTYPE (20 BYTE), id dec(2));
insert into t values 
    (CAST('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' AS HASHTYPE (20 BYTE)),1),
    (CAST('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' AS HASHTYPE (20 BYTE)),2),
    (CAST('BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB' AS HASHTYPE (20 BYTE)),3);

Example
The following example returns an internal server error:

SELECT COUNT(h1) OVER (ORDER BY id) FROM t;

Workaround

Cast the HASHTYPE column to a CHAR/VARCHAR type.

Example
The following example completes successfully:

SELECT COUNT(CAST(h1 AS VARCHAR(40))) OVER (ORDER BY id) FROM t;

Fix

In this case, the query does not return an internal server error, as expected.