Ordering wrong for 2, 4, 8 and 24-byte HASHTYPE columns in analytic functions

Details

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

Description

Analytic function may return incorrect results due to wrong ordering if

  • the analytic function used ordering in the OVER clause
  • the analytic function is ordered by a HASHTYPE column
  • the HASHTYPE column is a 2, 4, 8 or 24-byte HASHTYPE

Preparation 

create table A (A1 hashtype(2 byte));
insert into A values ('1f00'), ('1f1f'), ('1fff'), ('2000'), ('2020'), ('20ff'), ('2100'), ('2121'), ('21ff');

Example 

select A1,
       row_number() over (order by A1) as ORDER_USING_HASHTYPE,
       row_number() over (order by cast(A1 as char(4))) as ORDER_USING_CHAR
from A
order by A1;

Expected: 9 rows, values (1f00, 1, 1),
                         (1f1f, 2, 2),
                         (1fff, 3, 3),
                         (2000, 4, 4),
                         (2020, 5, 5),
                         (20ff, 6, 6),
                         (2100, 7, 7),
                         (2121, 8, 8),
                         (21ff, 9, 9)

Observed: 9 rows, values (1f00, 1, 1),
                         (1f1f, 4, 2),
                         (1fff, 7, 3),
                         (2000, 2, 4),
                         (2020, 5, 5),
                         (20ff, 8, 6),
                         (2100, 3, 7),
                         (2121, 6, 8),
                         (21ff, 9, 9)

Workaround

Cast the HASHTYPE column to a corresponding CHAR data type, as in the example above.

Fix

The analytic function returns the expected ordering.