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

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Fix Version/s: 1.12.0
• Component/s: Functions - Hive, Storage - Parquet
• Labels: ready-to-commit
• Assignee: Vitalii Diravka
• Reporter: Rahul Kumar Challapalli
• Created: 04/Nov/16 08:36
• Updated: 20/Sep/17 21:31
• Resolved: 20/Sep/17 18:13

Description

git.commit.id.abbrev=190d5d4

Wrong Result 1 :

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       |
+-------------+---------+

Wrong Result 2 :

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       |
+-------------+---------+

Correct Result :

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       |
+-------------+---------+

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.

Issue Links

• Blocked: DRILL-5250 Date is stored wrongly in HIVE generated JSON
• is related to: DRILL-5332 DateVector support uses questionable conversions to a time
• is related to: DRILL-4116 Inconsistent results with datetime functions on different machines
• links to: GitHub Pull Request #937