Exists in Combination with NVL and Expressions leads to Wrong Results

Detail name	Value
Changelog Number	7647
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.1.0, Exasol 6.1.1, Exasol 6.1.2
Fix Versions	Exasol 6.1.3
Resolution Date	2019-03-19

Background

The combination of exists and a correlated condition can lead to wrong results. In such cases the exists returns too few existing rows.
Besides the exist and the correlation there are other conditions that have to be met by the query to trigger the problem:

• The correlated condition has to contain NVL or a CASE that checks for NULL values.
• The checked condition has to contain an expression that has a result of data type varchar or char (e.g., TRIM(col1)).
• The internal, temporary data during the execution in Exasol has to be in a specific order. Thus, the wrong results do not appear for each execution of the query.

Note that in some cases, Exasol transforms IN-subselect expressions into queries with exists internally.

Example:

CREATE SCHEMA test;
OPEN SCHEMA test;
CREATE OR REPLACE TABLE T(col1 int);
INSERT INTO T VALUES (1);
INSERT INTO T VALUES (2);
INSERT INTO T VALUES (3);
CREATE OR REPLACE TABLE  T2(col2 VARCHAR(100));
INSERT INTO T2 VALUES (1);
INSERT INTO T2 VALUES (3);

-- Expected result: 2
SELECT
    COUNT(*)
FROM
    (
        SELECT
            NVL(TRIM(col1),-1) col1
        FROM
            T) tmp
WHERE
    EXISTS
    (
        SELECT
            cur.col2
        FROM
            T2 cur
        WHERE
            cur.col2 = tmp.col1 ) ;  
       
-- Expected result: 2     
SELECT
    COUNT(*)
FROM
    (
        SELECT
            NVL(TRIM(col1),-1) col1
        FROM
            T) tmp
WHERE
    tmp.col1 in 
    (
        SELECT
            cur.col2
        FROM
            T2 cur
        WHERE
            cur.col2 = tmp.col1 ) ; 

Workaround

If it is not possible to remove NVL or the expression inside NVL (e.g., the TRIM in the example), please contact our support for possible alternatives.