SCALAR RETURN UDFs with large input and output data may produce wrong results

Detail name	Value
Changelog Number	13796
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.0.0, Exasol 7.1.0
Fix Versions	Exasol 8.0.0, Exasol 7.0.17, Exasol 7.1.7
Resolution Date	2022-03-15

Description

Queries that use a UDF with large input and output values may produce wrong results under the following conditions:

• The UDF is a SCALAR RETURN UDF.
• The UDF language is either Python, Python 3, Java or R.
• The input data for 4096 rows is larger then 2 MiB.
• The output data for 4096 rows is larger then 2 MiB.

Lua UDFs are not affected.

Preparation

--/
CREATE OR REPLACE PYTHON3 SCALAR SCRIPT python3_script (x varchar(10000)) RETURNS varchar(10000) AS
def run(ctx):
        return ctx.x
/

CREATE OR REPLACE TABLE basis(a int);
INSERT INTO basis VALUES 0,1,2,3,4,5,6,7,8,9;
CREATE OR REPLACE TABLE t(id int, v4096 VARCHAR(10000));
INSERT INTO t(id) select rownum from (select a.a from basis a, basis, basis, basis);
UPDATE t SET v4096 = REPEAT(CAST(HASH_SHA512(id) as VARCHAR(10000)), 32);

Example

-- Every result != 0 is wrong
SELECT COUNT(*) FROM (SELECT id, v4096, python3_script(v4096) as e_col FROM t) WHERE v4096 != e_col;

Workaround

Use a scalar emits function.

Example

CREATE OR REPLACE PYTHON3 SCALAR SCRIPT python3_emits_script (x varchar(10000)) EMITS (e_col varchar(10000)) AS
def run(ctx):
        ctx.emit(ctx.x)
/

SELECT COUNT(*) FROM (SELECT id, v4096, python3_emits_script(v4096) FROM t) WHERE v4096 != e_col;

Fix

In the above scenario, UDFs return the correct results.