Potential wrong results when filtering fixed size types from 9 to 15 bytes

Detail name	Value
Changelog Number	18903
Type	Bug
Status	Resolved
Affected Versions	Exasol 8.0.0, Exasol 7.1.1
Fix Versions	Exasol 8.25.0, Exasol 7.1.26
Resolution Date	2024-02-15

Description

A wrong filtering result might occur when using HASHTYPE data type of sizes from 8 to 14 (below 7 and above 15 works correctly). It depends on the internally used data compression.

Preparation

CREATE SCHEMA TEST;

create table t as select pos ,CAST(CAST(pos as char(7))||'fffffffffffff' as HASHTYPE(10 BYTE)) as x from values between 1000000 and 2000000 as tt(pos);

insert into t values ( 1001000 , 'ffffffffffffffffffff');
insert into t values ( 1001000 , 'efffffffffffffffffff');
insert into t values ( 1001000 , 'dfffffffffffffffffff');

recompress table t;

create table t2 (p2 integer);
insert into t2 values(1001000);

Example

-- Current behavior: returns no rows 
-- Expected behavior: returns 1 row

select pos,x from t,t2 where pos=p2 and x='efffffffffffffffffff';

Workaround

Set the database parameter -useRawCompare=0. Unfortunately, this will lower the performance of some queries.

Fix

In these scenarios, queries will return the correct results, as expected.