Filters on Hashtype columns with a local index may fail

Detail name	Value
Changelog Number	14045
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.1.2, Exasol 7.0.13
Fix Versions	Exasol 8.0.0, Exasol 7.0.17, Exasol 7.1.7
Resolution Date	2022-03-15

Description

Queries containing the following conditions may return a "Feature not supported" error: 

• The query contains a join.
• There is an equality filter that compares a HASHTYPE column with a string literal.
• There is a local index on the HASHTYPE column.

Preparation

CREATE OR REPLACE TABLE t1(c1 int, c2 HASHTYPE(16 BYTE));
CREATE OR REPLACE TABLE t2(c1 int, c2 HASHTYPE(16 BYTE));
    
INSERT INTO t1 VALUES (1, '0cc175b9c0f1b6a831c399e269772661'), (2,hash_md5(0));
INSERT INTO t2 VALUES (1, '0cc175b9c0f1b6a831c399e269772661'), (2,hash_md5(0));

ENFORCE LOCAL INDEX ON t1(c2);

Example

-- Throws Feature not supported: Incomparable Types: HASHTYPE(16 BYTE) and CHAR(32) ASCII!
SELECT * 
FROM t1 JOIN t2 ON (t1.c1 = t2.c1) 
WHERE t1.c2 = '0cc175b9c0f1b6a831c399e269772661';

Workaround

Cast the string literal to the corresponding HASHTYPE.

Example

SELECT * 
FROM t1 JOIN t2 ON (t1.c1 = t2.c1) 
WHERE t1.c2 = CAST('0cc175b9c0f1b6a831c399e269772661' AS HASHTYPE(16 BYTE));

Fix

The statement finishes successfully.