Join on HASHTYPE with 56 byte or more causes internal server error

Detail name	Value
Changelog Number	13035
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.0.0, Exasol 7.1.0
Fix Versions	Exasol 8.20.0
Resolution Date	2023-07-12

Description

Joining columns of type HASHTYPE will return an 'Internal server error' instead of a result if the following conditions are met:

• The join joins columns of type HASHTYPE(56 BYTE) or with higher Byte-value
• The HASHTYPE column is the only join condition used.

Example

Preparation:

create table t1(hash HASHTYPE(56 byte), i int);
insert into t1 values (repeat('a', 2* 56), 1);
create table t2 as select * from t1;

Testcase:

-- causes internal server error
select * from t1 join t2 on t1.hash=t2.hash;

Workaround

There are two possible workarounds.
1. Use a string conversion of the HASHTYPE.

-- works (string conversion)
select * from t1 join t2 on to_char(t1.hash)=to_char(t2.hash);

Upside: Workaround only affects the direct join condition and can be applied in any case.
Downside: This completely removes the performance benefits of the hash type and might indeed even be slower than joining on VARCHAR because expression indexes are needed.

2. Include other columns in the join condition: As soon as another column is added to the join condition, this problem no longer appears:

-- works (additional join column i)
select * from t1 join t2 on t1.hash=t2.hash and t1.i=t2.i;

Upside: Good performance.
Downside: Can not be applied in all cases or needs an additional dummy column just for this workaround.

Fix

The query runs without an error.