[jira] [Commented] (DRILL-5002) Using hive's date functions on top of date column gives wrong results for local time-zone

["Vitalii Diravka (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DRILL-5002?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15858106#comment-15858106 ] 

Vitalii Diravka commented on DRILL-5002:
----------------------------------------

I changed UTC time-zone to the local one (-10.00) and reproduced the issue. So the root-causes of the problem are hive's date functions and local time-zone (Hive receives UTC time and converts it to the local time. Therefore it is necessary to pass UTC time to Hive). 

But the issue corresponds to every data source. For example:
{code}
0: jdbc:drill:zk=local> select to_date('1994-01-01','yyyy-mm-dd') from (VALUES(1));
+-------------+
|   EXPR$0    |
+-------------+
| 1994-01-01  |
+-------------+
1 row selected (0.096 seconds)
0: jdbc:drill:zk=local> select last_day(to_date('1994-01-01','yyyy-mm-dd')) from (VALUES(1));
+-------------+
|   EXPR$0    |
+-------------+
| 1993-12-31  |
+-------------+
{code}
Therefore I changed the name of this ticket.

> Using hive's date functions on top of date column gives wrong results for local time-zone
> -----------------------------------------------------------------------------------------
>
>                 Key: DRILL-5002
>                 URL: https://issues.apache.org/jira/browse/DRILL-5002
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Functions - Hive, Storage - Parquet
>            Reporter: Rahul Challapalli
>            Assignee: Vitalii Diravka
>            Priority: Critical
>         Attachments: 0_0_0.parquet
>
>
> git.commit.id.abbrev=190d5d4
> Wrong Result 1 :
> {code}
> select l_shipdate, `month`(l_shipdate) from cp.`tpch/lineitem.parquet` where l_shipdate = date '1994-02-01' limit 2;
> +-------------+---------+
> | l_shipdate  | EXPR$1  |
> +-------------+---------+
> | 1994-02-01  | 1       |
> | 1994-02-01  | 1       |
> +-------------+---------+
> {code}
> Wrong Result 2 : 
> {code}
> select l_shipdate, `day`(l_shipdate) from cp.`tpch/lineitem.parquet` where l_shipdate = date '1998-06-02' limit 2;
> +-------------+---------+
> | l_shipdate  | EXPR$1  |
> +-------------+---------+
> | 1998-06-02  | 1       |
> | 1998-06-02  | 1       |
> +-------------+---------+
> {code}
> Correct Result :
> {code}
> select l_shipdate, `month`(l_shipdate) from cp.`tpch/lineitem.parquet` where l_shipdate = date '1998-06-02' limit 2;
> +-------------+---------+
> | l_shipdate  | EXPR$1  |
> +-------------+---------+
> | 1998-06-02  | 6       |
> | 1998-06-02  | 6       |
> +-------------+---------+
> {code}
> It looks like we are getting wrong results when the 'day' is '01'. I only tried month and day hive functions....but wouldn't be surprised if they have similar issues too.



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)