[jira] [Assigned] (DRILL-5581) Query with CASE statement returns wrong results

["Jinfeng Ni (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DRILL-5581?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Jinfeng Ni reassigned DRILL-5581:
---------------------------------

    Assignee: Jinfeng Ni

> Query with CASE statement returns wrong results
> -----------------------------------------------
>
>                 Key: DRILL-5581
>                 URL: https://issues.apache.org/jira/browse/DRILL-5581
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Execution - Flow
>    Affects Versions: 1.11.0
>            Reporter: Khurram Faraaz
>            Assignee: Jinfeng Ni
>
> A query that uses case statement, returns wrong results.
> {noformat}
> Apache Drill 1.11.0-SNAPSHOT, commit id: 874bf629
> [test@centos-101 ~]# cat order_sample.csv
> 202634342,20000101,20160301
> apache drill 1.11.0-SNAPSHOT
> "this isn't your grandfather's sql"
> 0: jdbc:drill:schema=dfs.tmp> ALTER SESSION SET `store.format`='csv';
> +-------+------------------------+
> |  ok   |        summary         |
> +-------+------------------------+
> | true  | store.format updated.  |
> +-------+------------------------+
> 1 row selected (0.245 seconds)
> 0: jdbc:drill:schema=dfs.tmp> CREATE VIEW  `vw_order_sample_csv` as
> . . . . . . . . . . . . . . > SELECT
> . . . . . . . . . . . . . . > `columns`[0] AS `ND`,
> . . . . . . . . . . . . . . > CAST(`columns`[1] AS BIGINT) AS `col1`,
> . . . . . . . . . . . . . . > CAST(`columns`[2] AS BIGINT) AS `col2`
> . . . . . . . . . . . . . . > FROM `order_sample.csv`;
> +-------+----------------------------------------------------------------------+
> |  ok   |                               summary                                |
> +-------+----------------------------------------------------------------------+
> | true  | View 'vw_order_sample_csv' created successfully in 'dfs.tmp' schema  |
> +-------+----------------------------------------------------------------------+
> 1 row selected (0.253 seconds)
> 0: jdbc:drill:schema=dfs.tmp> select
> . . . . . . . . . . . . . . > case
> . . . . . . . . . . . . . . > when col1 > col2 then col1
> . . . . . . . . . . . . . . > else col2
> . . . . . . . . . . . . . . > end as temp_col,
> . . . . . . . . . . . . . . > case
> . . . . . . . . . . . . . . > when col1 = 20000101 and (20170302 - col2) > 10000 then 'D'
> . . . . . . . . . . . . . . > when col2 = 20000101 then 'P'
> . . . . . . . . . . . . . . > when col1 - col2 > 10000 then '0'
> . . . . . . . . . . . . . . > else 'A'
> . . . . . . . . . . . . . . > end as status
> . . . . . . . . . . . . . . > from  `vw_order_sample_csv`;
> +-----------+---------+
> | temp_col  | status  |
> +-----------+---------+
> | 20160301  | A       |
> +-----------+---------+
> 1 row selected (0.318 seconds)
> 0: jdbc:drill:schema=dfs.tmp> explain plan for
> . . . . . . . . . . . . . . > select
> . . . . . . . . . . . . . . > case
> . . . . . . . . . . . . . . > when col1 > col2 then col1
> . . . . . . . . . . . . . . > else col2
> . . . . . . . . . . . . . . > end as temp_col,
> . . . . . . . . . . . . . . > case
> . . . . . . . . . . . . . . > when col1 = 20000101 and (20170302 - col2) > 10000 then 'D'
> . . . . . . . . . . . . . . > when col2 = 20000101 then 'P'
> . . . . . . . . . . . . . . > when col1 - col2 > 10000 then '0'
> . . . . . . . . . . . . . . > else 'A'
> . . . . . . . . . . . . . . > end as status
> . . . . . . . . . . . . . . > from  `vw_order_sample_csv`;
> +------+------+
> | text | json |
> +------+------+
> | 00-00    Screen
> 00-01      Project(temp_col=[CASE(>(CAST(ITEM($0, 1)):BIGINT, CAST(ITEM($0, 2)):BIGINT), CAST(ITEM($0, 1)):BIGINT, CAST(ITEM($0, 2)):BIGINT)], status=[CASE(AND(=(CAST(ITEM($0, 1)):BIGINT, 20000101), >(-(20170302, CAST(ITEM($0, 2)):BIGINT), 10000)), 'D', =(CAST(ITEM($0, 2)):BIGINT, 20000101), 'P', >(-(CAST(ITEM($0, 1)):BIGINT, CAST(ITEM($0, 2)):BIGINT), 10000), '0', 'A')])
> 00-02        Scan(groupscan=[EasyGroupScan [selectionRoot=maprfs:/tmp/order_sample.csv, numFiles=1, columns=[`columns`[1], `columns`[2]], files=[maprfs:///tmp/order_sample.csv]]])
> // Details of Java compiler from sys.options
> 0: jdbc:drill:schema=dfs.tmp> select name, status from sys.options where name like '%java_compiler%';
> +----------------------------------------+----------+
> |                  name                  |  status  |
> +----------------------------------------+----------+
> | exec.java.compiler.exp_in_method_size  | DEFAULT  |
> | exec.java_compiler                     | DEFAULT  |
> | exec.java_compiler_debug               | DEFAULT  |
> | exec.java_compiler_janino_maxsize      | DEFAULT  |
> +----------------------------------------+----------+
> 4 rows selected (0.21 seconds)
> {noformat}



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)