Wrong results from PL/SQL function that returns HASHTYPE on large VARCHAR inputs

Details

Detail name Value
Changelog Number 15062
Type Bug
Status Resolved
Affected Versions Exasol 7.0.0, Exasol 7.1.0, Exasol 8.0.0
Fix Versions Exasol 8.0.6, Exasol 7.1.12, Exasol 7.0.20
Resolution Date 2022-07-13

Description

A query may return incorrect results if the following conditions are met:

  • The query contains a PL/SQL function
  • The PL/SQL function uses HASHTYPE. E.g. as return type or as the type for an intermediate result
  • An input column for the function is of type VARCHAR and contains more than 8 MB in a span of 2048 rows

Preparation

create schema test;

--/
create or replace FUNCTION hash_function(IN_VAR VARCHAR(2000000))
RETURN HASHTYPE(20 BYTE)
BEGIN
RETURN '1122334455667788990011223344556677889900';
END hash_function;
/

create or replace table t(c varchar(2000000));
-- 43 * 200000 = 8600000 which is more than 8 MB
insert into t select repeat('a', 200000) c from values between 1 and 43;

Example

SELECT
    hash_function(c) AS a, 
    local.a = '1122334455667788990011223344556677889900'
FROM 
    t
ORDER BY 
    2;

This will return something like the following which is obviously not correct:
 

A HASH_FUNCTION(T.C)='1122334455667788990011223344556677889900' 
 ---------------------------------------- ------------------------------------------------------------- 
 1100000000010000000200000003000000040000 false 
 0005150000001600000017000000180000001900 false 
 00000000000b0000000c0000000d0000000e0000 false 
 000f000000100000001100000012000000130000 false 
 00140000005b7f0000400d030000000000112233 false 
 4455667788990011223344556677889900112233 false 
 4455667788990011223344556677889900112233 false 
 4455667788990011223344556677889900112233 false 
 4455667788990011223344556677889900112233 false 
 4455667788990011223344556677889900112233 false 
 ...

Workaround

There is no workaround.

Fix

Those queries return correct results.