Implementation of "simple" case-when expressions broken for expressions that are not constant per row

Detail name	Value
Changelog Number	6857
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.2.0, Exasol 7.0.0
Fix Versions	Exasol 7.0.7, Exasol 6.2.14
Resolution Date	2021-02-02

Background:

In some situations, expressions that are used in more than one place may get calculated independently in those places to allow local optimizations to take place.

Problem

If affected expressions are not deterministic (esp. UDF and the RANDOM function), this may lead to unexpected or wrong results.

Example

SELECT
    CASE FLOOR(RANDOM() * 4)
        WHEN 0 THEN 'A'
        WHEN 1 THEN 'B'
        WHEN 2 THEN 'C'
        WHEN 3 THEN 'D'
        ELSE 'WRONG'
    END;

returns 'WRONG' with quite high probability.
The problem is that internally the optimizer replaces this query with

SELECT
    CASE 
        WHEN FLOOR(RANDOM() * 4) = 0 THEN 'A'
        WHEN FLOOR(RANDOM() * 4) = 1 THEN 'B'
        WHEN FLOOR(RANDOM() * 4) = 2 THEN 'C'
        WHEN FLOOR(RANDOM() * 4) = 3 THEN 'D'
        ELSE 'WRONG'
    END;

As each RANDOM call is handled independently, all of the case conditions may become false.

Workaround(s)

For the simple scalar case it is possible to use a scalar subselect as workaround:

SELECT
    CASE (select FLOOR(RANDOM() * 4))
        WHEN 0 THEN 'A'
        WHEN 1 THEN 'B'
        WHEN 2 THEN 'C'
        WHEN 3 THEN 'D'
        ELSE 'WRONG'
END;

In general, it is quite tricky to get right and usually requires a pre-materialization of the non-deterministic expression.

Assume we want to evaluate the CASE expression for each for of values 1,2,3, here is a way to do it:

SELECT
    CASE rnd
        WHEN 0 THEN 'A'
        WHEN 1 THEN 'B'
        WHEN 2 THEN 'C'
        WHEN 3 THEN 'D'
        ELSE 'WRONG'
    END v
from (
    select
        x
        , floor(random()*4) as rnd
    from (
        values 1,2,3 as p(x)
    )
    -- PRE-MATERIALIZE
    order by false
);

The idea is to add an extra "random" column (rnd) to each row.