HASHTYPE in join conditions can cause Internal Server Errors

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

Description

Some join combinations that use the datatype HASHTYPE in their join conditions can cause an internal server error. Whether a query is affected or not depends on a combination of the following conditions:

• HASHTYPE datatype in the JOIN condition
• OUTER JOIN
• NULL values
• Indices on JOIN columns

Analyzing the SQL query text is not sufficient to determine whether the problem occurs or not.

Example
Here is one example that causes these conditions:

CREATE OR REPLACE TABLE input_t(idx decimal(36,0), h8 hashtype(8 byte));
INSERT INTO input_t(idx) select * from VALUES 1,2,3,4,5;
UPDATE input_t set h8 = left(hash_md5(idx), 16);

CREATE OR REPLACE TABLE Th8_1 as (SELECT h8 c1 FROM input_t WHERE idx < 5);
CREATE OR REPLACE TABLE Th8_2 as (SELECT h8 c1 FROM input_t WHERE idx < 5);
CREATE OR REPLACE TABLE Th8_3 as (SELECT h8 c1 FROM input_t);
CREATE OR REPLACE TABLE Th8_4 as (SELECT h8 c1 FROM input_t WHERE idx < 5);

SELECT * FROM Th8_1 INNER JOIN
  (
    SELECT * FROM Th8_2 FULL OUTER JOIN Th8_3 ON Th8_2.c1=Th8_3.c1 INNER JOIN
    Th8_4 ON Th8_2.c1=Th8_4.c1
  )
ON Th8_1.c1=(SELECT h8 FROM input_t WHERE idx=1);

Workaround

Temporarily cast the HASHTYPE columns to CHAR in the join conditions.

Example

SELECT * FROM Th8_1 INNER JOIN
  (
    SELECT * FROM Th8_2 FULL OUTER JOIN Th8_3
      ON cast(Th8_2.c1 as CHAR(16))=cast(Th8_3.c1 as CHAR(16))
      INNER JOIN Th8_4 ON cast(Th8_2.c1 as CHAR(16))=cast(Th8_4.c1 as CHAR(16))
  )
ON Th8_1.c1=(SELECT h8 FROM input_t WHERE idx=1);

Fix

The query will run as expected and not return an internal server error.