Using HASHTYPE datatypes in IN predicates may cause wrong results

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

Description

The use of HASHTYPE datatypes in an IN predicate can lead to wrong results. This happens if the following conditions are met:

• The IN predicate uses a subquery.
• The subquery returns more than 2000 rows.
• The subquery returns less than 100,000 rows

The following example reproduces this case:

CREATE OR REPLACE TABLE ten(x int);
INSERT INTO ten VALUES 0,1,2,3,4,5,6,7,8,9;
CREATE OR REPLACE TABLE hash_t_10t(idx decimal(36,0), h8 hashtype(8 byte));
INSERT INTO hash_t_10t(idx) SELECT ROWNUM FROM (SELECT a.x FROM ten a, ten, ten, ten);
UPDATE hash_t_10t SET h8 = LEFT(hash_md5(idx), 16);
CREATE OR REPLACE TABLE A AS (SELECT * FROM hash_t_10t);

--Empty result expected
SELECT h8 FROM (SELECT h8 FROM A WHERE (h8) NOT IN (SELECT h8 FROM hash_t_10t));

Workaround

It is possible to replace such an IN predicate with an EXISTS.

Example

SELECT h8 FROM (SELECT h8 FROM A WHERE NOT EXISTS (SELECT h8 FROM hash_t_10t WHERE A.h8 = hash_t_10t.h8));

Fix

These types of queries will return the correct results.