Wrong results for queries using virtual schemas that contain the constant condition WHERE FALSE

Detail name	Value
Changelog Number	16241
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.1.0, Exasol 8.0.0
Fix Versions	Exasol 7.1.23, Exasol 8.21.0
Resolution Date	2023-08-04

Description

A query using virtual schemas may produce wrong results if the following conditions are met:

• Exasol performs only a partial pushdown to the remote system (i.e., not the complete query).
• The query contains a WHERE condition that evaluates to FALSE (i.e., 1 = 2).
• The partial pushdown contains the WHERE condition.
• The original query does not contain WHERE FALSE.

Preparation

create SCHEMA test;
open schema test;
--/
CREATE OR REPLACE PYTHON3 ADAPTER SCRIPT test.vs_adapter AS
import json
import string
def adapter_call(request):
    # database expects utf-8 encoded string of type str. unicode not yet supported
    root = json.loads(request)
    if root["type"] == "createVirtualSchema":
        res = {
            "type": "createVirtualSchema",
            "schemaMetadata": {'tables': [{'name': 'VT', 'columns': [{'dataType': {'type': 'BOOLEAN'}, 'name': 'VC'}]}]}
        }
        return json.dumps(res)
    elif root["type"] == "dropVirtualSchema":
        return json.dumps({"type": "dropVirtualSchema"})
    elif root["type"] == "setProperties":
        return json.dumps({"type": "setProperties"})
    elif root["type"] == "refresh":
        return json.dumps({"type": "refresh"})
    if root["type"] == "getCapabilities":
        return json.dumps({
            "type": "getCapabilities",
            "capabilities": ["FILTER_EXPRESSIONS", "LITERAL_BOOL"]
            }) # database expects utf-8 encoded string of type str. unicode not yet supported.
    elif root["type"] == "pushdown":
        res = {
            "type": "pushdown",
            "sql": "SELECT true FROM DUAL WHERE FALSE"
        }
        return json.dumps(res)
    else:
        raise ValueError('Unsupported callback')
/
;

DROP VIRTUAL SCHEMA IF EXISTS test_vs CASCADE;
CREATE VIRTUAL SCHEMA test_vs USING test.vs_adapter;

Example

-- returns a | NULL instead of 0 rows
SELECT * FROM (
  SELECT 'a' AS c, vc FROM dual 
  LEFT JOIN test_vs.vt ON true
  ) 
  WHERE c = 'b';

 

Workaround

Use a scalar subquery in the WHERE condition.

-- returns 0 rows
SELECT * FROM (
  SELECT 'a' AS c, vc FROM dual 
  LEFT JOIN test_vs.vt ON true
  ) 
  WHERE c = (SELECT 'b' FROM DUAL);

Fix

Those queries run as expected.