Failure in optimization for multiple PL-SQL expressions in a statement.

Detail name	Value
Changelog Number	7668
Type	Bug
Status	Resolved
Affected Versions	EXASOL 6.0.0, Exasol 6.1.0
Fix Versions	Exasol 6.2.0, Exasol 6.0.16, Exasol 6.1.4
Resolution Date	2019-04-02

Background

If an expression appears multiple times within a statement, the Exasol engine may optimize this to reduce processing and memory cost.

Description

When such an expression contains a PL/SQL function, this optimization is faulty and may cause wrong results, data exceptions or process aborts.

Example

CREATE OR REPLACE FUNCTION a_plsql (a NUMBER)
RETURN VARCHAR(2000)
IS
res VARCHAR(2000);
BEGIN
    RETURN a;
END a_plsql;
/

create or replace table t (c double);
insert into t values (123.456);
insert into t values (0.1234567891234567);


SELECT
    CASE WHEN 
        c=123.456
    THEN
        a_plsql(c)
    ELSE
        a_plsql(c)
    END
FROM t;

Workaround

Replace PL/SQL functions with UDF scripts or disable the optimization by adding the extra database parameter -noshortcuts=1