User-defined functions returning string types may fail on system tables

Detail name	Value
Changelog Number	9288
Type	Bug
Status	Resolved
Affected Versions	EXASOL 6.0.0, Exasol 6.1.0, Exasol 6.2.0
Fix Versions	Exasol 7.0.0, Exasol 6.1.9, Exasol 6.2.5
Resolution Date	2020-02-07

Bug

Calling a user-defined string-returning function on columns from system tables sometimes causes an internal server error. This happens randomly, so affected statements will work successfully in some executions and fail in others. To be affected, the function must be defined using CREATE FUNCTION (SCRIPTS are not affected) and must be returning a string data type, e.g. VARCHAR.

Example

CREATE FUNCTION function_returning_string(p INT) RETURN VARCHAR(128)
BEGIN
    return 'test';
END;
/

CREATE TABLE example(example INT); -- To ensure that exa_dba_tables is not empty.

-- This sometimes causes an internal server error:
SELECT function_returning_string(table_row_count)
FROM exa_dba_tables;

Workaround

Add a subselect with ORDER BY FALSE around the system table:

SELECT function_returning_string(table_row_count)
FROM
    (SELECT * FROM exa_dba_tables ORDER BY FALSE) -- workaround
;