You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@drill.apache.org by "Volodymyr Vysotskyi (JIRA)" <ji...@apache.org> on 2019/03/15 07:08:01 UTC
[jira] [Resolved] (DRILL-5581) Query with CASE statement returns
wrong results
[ https://issues.apache.org/jira/browse/DRILL-5581?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Volodymyr Vysotskyi resolved DRILL-5581.
----------------------------------------
Resolution: Fixed
Assignee: Volodymyr Vysotskyi (was: Karthikeyan Manivannan)
Fix Version/s: 1.16.0
Fixed in DRILL-6524
> 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: Volodymyr Vysotskyi
> Priority: Major
> Fix For: 1.16.0
>
>
> 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}
> Results from Postgres 9.3 for the same query, note the difference in results
> {noformat}
> postgres=# create table order_sample(c1 varchar(50), c2 bigint, c3 bigint);
> CREATE TABLE
> postgres=# insert into order_sample values('202634342',20000101,20160301);
> INSERT 0 1
> postgres=# select * from order_sample;
> c1 | c2 | c3
> -----------+----------+----------
> 202634342 | 20000101 | 20160301
> (1 row)
> postgres=# create view vw_order_sample_csv as
> select
> c1 as ND,
> CAST(c2 AS BIGINT) AS col1,
> CAST(c3 AS BIGINT) AS col2
> FROM order_sample;
> CREATE VIEW
> postgres=# select
> postgres-# case
> postgres-# when col1 > col2 then col1
> postgres-# else col2
> postgres-# end as temp_col,
> postgres-# case
> postgres-# when col1 = 20000101 and (20170302 - col2) > 10000 then 'D'
> postgres-# when col2 = 20000101 then 'P'
> postgres-# when col1 - col2 > 10000 then '0'
> postgres-# else 'A'
> postgres-# end as status
> postgres-# from vw_order_sample_csv;
> temp_col | status
> ----------+--------
> 20160301 | D
> (1 row)
> {noformat}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)