Wrong result set for certain filters on outer join tables

Detail name	Value
Changelog Number	9623
Type	Bug
Status	Resolved
Affected Versions	EXASOL 6.0.0, Exasol 6.1.0, Exasol 6.2.0
Fix Versions	Exasol 7.0.0, Exasol 6.1.9, Exasol 6.2.5
Resolution Date	2020-02-25

Background

Null handling in general

The following predicates have builtin NULL-handling:

• IS TRUE
• IS FALSE
• IS NOT TRUE
• IS NOT FALSE

Contrary to most other operators, these predicates evaluate NULL to TRUE or FALSE.

Optimizer: outer join elimination

When Exasol detects a regular filter applied to a column of an inner table of an outer join, it may decide to perform a regular join operation, as any NULLs generated by the outer join would be eliminated by the filter anyway.

A regular filter is defined as a filter that contains no special handling for NULL values.

Bug

This outer join elimination is not recognizing the predicates listed above as handling NULL values, wrongly converting outer joins to inner joins in such cases.

Such queries may yield a wrong result: Rows for which the NULL is generated by the outer join are missing from the result set.

How To Reproduce

CREATE TABLE outer_t(id INT);
CREATE TABLE inner_t(id INT, a BOOLEAN);

INSERT INTO outer_t VALUES (1),       (2),        (3),       (4);
INSERT INTO inner_t VALUES (1, TRUE), (2, FALSE), (3, NULL);

-- Bug: The row containing ID 4 is missing:
SELECT *
FROM outer_t
LEFT JOIN inner_t USING (id)
WHERE inner_t.a IS NOT TRUE;

Workaround

Add COALESCE(..., NULL) around the affected column in the filter:

SELECT *
FROM outer_t
LEFT JOIN inner_t USING (id)
WHERE COALESCE(outer_t.a, NULL) IS NOT TRUE;