[DRILL-5683] Incorrect query result when query uses NOT(IS NOT NULL) expression

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.16.0
• Assignee: Vova Vysotskyi
• Reporter: Jinfeng Ni
• Created: 20/Jul/17 23:24
• Updated: 15/Mar/19 07:06
• Resolved: 15/Mar/19 07:06

Description

The following repo was modified from a testcase provided by Arjun Rajan([e-mail]).

1. Prepare dataset with null.

create table dfs.tmp.t1 as 
  select r_regionkey, r_name, case when mod(r_regionkey, 3) > 0 then mod(r_regionkey, 3) else null end as flag 
  from cp.`tpch/region.parquet`;

select * from dfs.tmp.t1;
+--------------+--------------+-------+
| r_regionkey  |    r_name    | flag  |
+--------------+--------------+-------+
| 0            | AFRICA       | null  |
| 1            | AMERICA      | 1     |
| 2            | ASIA         | 2     |
| 3            | EUROPE       | null  |
| 4            | MIDDLE EAST  | 1     |
+--------------+--------------+-------+

2. Query with NOT(IS NOT NULL) expression in the filter.

select * from dfs.tmp.t1 where NOT (flag IS NOT NULL);

+--------------+---------+-------+
| r_regionkey  | r_name  | flag  |
+--------------+---------+-------+
| 0            | AFRICA  | null  |
| 3            | EUROPE  | null  |
+--------------+---------+-------+

3. Switch run-time code compiler from default to 'JDK', and get wrong result.

alter system set `exec.java_compiler` = 'JDK';

+-------+------------------------------+
|  ok   |           summary            |
+-------+------------------------------+
| true  | exec.java_compiler updated.  |
+-------+------------------------------+

select * from dfs.tmp.t1 where NOT (flag IS NOT NULL);
+--------------+--------------+-------+
| r_regionkey  |    r_name    | flag  |
+--------------+--------------+-------+
| 0            | AFRICA       | null  |
| 1            | AMERICA      | 1     |
| 2            | ASIA         | 2     |
| 3            | EUROPE       | null  |
| 4            | MIDDLE EAST  | 1     |
+--------------+--------------+-------+

4. Wrong result could happen too, when NOT(IS NOT NULL) in Project operator.

select r_regionkey, r_name, NOT(flag IS NOT NULL) as exp1 from dfs.tmp.t1;
+--------------+--------------+-------+
| r_regionkey  |    r_name    | exp1  |
+--------------+--------------+-------+
| 0            | AFRICA       | true  |
| 1            | AMERICA      | true  |
| 2            | ASIA         | true  |
| 3            | EUROPE       | true  |
| 4            | MIDDLE EAST  | true  |
+--------------+--------------+-------+

Issue Links

• relates to: DRILL-6524 Two CASE statements in projection influence results of each other