Non-cachable queries not reliably recognized in queries containing UNION ALL.

Detail name	Value
Changelog Number	5433
Type	Bug
Status	Resolved
Affected Versions	EXASOL 6.0.0
Fix Versions	EXASOL 6.0.10
Resolution Date	2018-05-04

Problem

Queries with UNION ALL that contain non-cachable functions in simple filter expressions may erroneously be classified as cachable if these expressions can be evaluated at compile time. This may lead to wrong results when the cached result does no longer fit the actual query.

 Example

create table t1(a timestamp, b timestamp);

-- no-cachable (ok)
select * from t1 where nvl(a,b) > trunc(SYSTIMESTAMP)-INTERVAL '1' DAY;

-- cachable (wrong!)
select 1 from t1 union all select 1 from t1 where nvl(a,b) > trunc(SYSTIMESTAMP)-INTERVAL '1' DAY;

Workaround

1. disable query cache (ALTER SESSION / ALTER SYSTEM)
2. make the expression more complex.
i.e.

select 1 from t1 union all select 1 from t1 where nvl(a,b) > (select trunc(SYSTIMESTAMP)-INTERVAL '1' DAY);