[HIVE-11375] Broken processing of queries containing NOT (x IS NOT NULL and x <> 0)

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.0.0
• Fix Version/s: 2.0.0
• Component/s: Logical Optimizer
• Target Version/s: 1.3.0
• Assignee: Aihua Xu
• Reporter: Mariusz Sakowski
• Created: 27/Jul/15 09:50
• Updated: 16/Feb/16 23:51
• Resolved: 21/Aug/15 17:41

Description

When running query like this:

explain select * from test where (val is not null and val <> 0);

hive will simplify expression in parenthesis and omit is not null check:

          Filter Operator
            predicate: (val <> 0) (type: boolean)

which is fine.

but if we negate condition using NOT operator:

explain select * from test where not (val is not null and val <> 0);

hive will also simplify thing, but now it will break stuff:

          Filter Operator
            predicate: (not (val <> 0)) (type: boolean)

because valid predicate should be val == 0 or val is null, while above row is equivalent to val == 0 only, filtering away rows where val is null

simple example:

CREATE TABLE example (
    val bigint
);
INSERT INTO example VALUES (1), (NULL), (0);

-- returns 2 rows - NULL and 0
select * from example where (val is null or val == 0);
-- returns 1 row - 0
select * from example where not (val is not null and val <> 0);

Issue Links

• links to: code review