Local index for hashtype not used with implicit casts

Detail name	Value
Changelog Number	10837
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.0.0
Fix Versions	Exasol 7.0.3
Resolution Date	2020-10-15

Description

Queries that perform comparisons on two hashtype data types can use a local  index to improve performance. If the same query compares a hashtype to a string literal (implicit type conversion), this will not use a local index and may have sub-optimal performance.

For example:

-- Preparation
create table t (a hashtype(2 byte));
enforce local index on t(a);

--The below query uses an index
select * from t where a = cast('aaaa' as hashtype(2 byte));

-- The below query does not use an index
select * from t where a = 'aaaa';

-- The below query does not use an index
select * from t where a in ('aaaa', 'bbbb', 'cccc');

Workaround

There is no workaround.

Fix

Comparing a hashtype with a string literal allows the usage of local indexes, if one is available.