You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@drill.apache.org by "Jinfeng Ni (JIRA)" <ji...@apache.org> on 2017/07/20 23:26:00 UTC
[jira] [Updated] (DRILL-5683) Incorrect query result when query
uses NOT(IS NOT NULL) expression
[ https://issues.apache.org/jira/browse/DRILL-5683?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Jinfeng Ni updated DRILL-5683:
------------------------------
Description:
The following repo was modified from a testcase provided by Arjun Rajan(arajan@mapr.com).
1. Prepare dataset with null.
{code}
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 |
+--------------+--------------+-------+
{code}
2. Query with NOT(IS NOT NULL) expression in the filter.
{code}
select * from dfs.tmp.t1 where NOT (flag IS NOT NULL);
+--------------+---------+-------+
| r_regionkey | r_name | flag |
+--------------+---------+-------+
| 0 | AFRICA | null |
| 3 | EUROPE | null |
+--------------+---------+-------+
{code}
3. Switch run-time code compiler from default to 'JDK', and get wrong result.
{code}
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 |
+--------------+--------------+-------+
{code}
4. Wrong result could happen too, when NOT(IS NOT NULL) in Project operator.
{code}
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 |
+--------------+--------------+-------+
{code}
was:
The following repo was modified from a testcase provided by Arjun Rajan(arajan@mapr.com).
1. Prepare dataset with null.
{code}
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 |
+--------------+--------------+-------+
{code}
2. Query with NOT(IS NOT NULL) expression in the filter.
{code}
select * from dfs.tmp.t1 where NOT (flag IS NOT NULL);
+--------------+---------+-------+
| r_regionkey | r_name | flag |
+--------------+---------+-------+
| 0 | AFRICA | null |
| 3 | EUROPE | null |
+--------------+---------+-------+
{code}
3. Switch run-time code compiler from default to 'JDK', and get wrong result.
{code}
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 |
+--------------+--------------+-------+
{code}
4. Wrong result could happen too, when NOT(IS NOT NULL) in Project operator.
{code}
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 |
+--------------+--------------+-------+
{code}
> Incorrect query result when query uses NOT(IS NOT NULL) expression
> -------------------------------------------------------------------
>
> Key: DRILL-5683
> URL: https://issues.apache.org/jira/browse/DRILL-5683
> Project: Apache Drill
> Issue Type: Bug
> Reporter: Jinfeng Ni
>
> The following repo was modified from a testcase provided by Arjun Rajan(arajan@mapr.com).
> 1. Prepare dataset with null.
> {code}
> 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 |
> +--------------+--------------+-------+
> {code}
> 2. Query with NOT(IS NOT NULL) expression in the filter.
> {code}
> select * from dfs.tmp.t1 where NOT (flag IS NOT NULL);
> +--------------+---------+-------+
> | r_regionkey | r_name | flag |
> +--------------+---------+-------+
> | 0 | AFRICA | null |
> | 3 | EUROPE | null |
> +--------------+---------+-------+
> {code}
> 3. Switch run-time code compiler from default to 'JDK', and get wrong result.
> {code}
> 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 |
> +--------------+--------------+-------+
> {code}
> 4. Wrong result could happen too, when NOT(IS NOT NULL) in Project operator.
> {code}
> 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 |
> +--------------+--------------+-------+
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)