Virtual schema filter pushdown may fail if expression is reused

Detail name	Value
Changelog Number	8959
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.1.0, Exasol 6.2.0, Exasol 7.0.0
Fix Versions	Exasol 7.1.0, Exasol 6.2.15, Exasol 7.0.11
Resolution Date	2021-06-02

Problem

If a filter is pushed into remote database and the same filter expression is also used in another context of the statement, the query may fail to execute.

Example

-- local schema NATIVE
-- virtual schema VS

select sum(C1+D1) 
from VS.T, NATIVE.U 
where
    T.C2='a'  -- filter expression
    and C1=1  
group by
    case when T.C2='a'  -- same expression, different context
         then 'a'
         else 'b'
    end;

Filter T.C2='a' is properly pushed into the remote database, but processing of the GROUP BY fails with internal server error.

Also fails if the filter expression is used as a join condition:

CREATE TABLE native.t(c1 bool, c2 bool);
SELECT 1 FROM vs.t1 JOIN dual ON c2 WHERE c2;

It does not fail if the complete query can be pushed down:

SELECT HASH_MD5(c2 is null) FROM vs.t WHERE c2 is null; -- works

ALTER VIRTUAL SCHEMA PRE_FX SET EXCLUDED_CAPABILITIES = 'FN_HASH_MD5'; -- prevent complete pushdown

SELECT HASH_MD5(c2 is null) FROM vs.t WHERE c2 is null; -- now this query aborts

Fix

Such queries will not fail anymore.