FIRST_VALUE/LAST_VALUE on HASHTYPE columns may cause an error if the result is NULL

Detail name	Value
Changelog Number	12763
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.2.0, Exasol 7.0.0
Fix Versions	Exasol 7.0.12, Exasol 7.1.1
Resolution Date	2021-08-31

Background

The Exasol database uses several optimizations for analytic functions. These optimizations differ depending on the datatype, the analytic function type, and the window clause.

Description

A query returns an internal server error if the following conditions are met:

• The query uses FIRST_VALUE or LAST_VALUE.
• The over clause of his analytic function contains no PARTITION BY.
• The over clause of this analytic function contains an ORDER BY.
• The window clause of this analytic function is ROWS/RANGE/GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
• The argument type of this analytic function is HASHTYPE. The size of the HASHTYPE is not 128 BIT.
• The result of this analytic function is NULL.

Preparation

CREATE OR REPLACE TABLE t(o int, a HASHTYPE(80 BIT));
INSERT INTO t VALUES(1, NULL);
INSERT INTO t VALUES(2, NULL);
INSERT INTO t VALUES(3, NULL);

Example:

SELECT FIRST_VALUE(a) OVER(order by o ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) from t;

SELECT LAST_VALUE(a) OVER(order by o ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) from t;

Workaround

You can use NTH_VALUE.

SELECT NTH_VALUE(a,1) OVER(order by o ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) from t;

SELECT NTH_VALUE(a,1) OVER(order by o DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) from t;

The workarounds may negatively impact the query performance.

Fix

The query returns the correct results and throws no error.