You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@drill.apache.org by "Vitalii Diravka (JIRA)" <ji...@apache.org> on 2016/12/12 19:22:58 UTC
[jira] [Comment Edited] (DRILL-5002) Using hive's date functions on
top of date column in parquet gives wrong results
[ https://issues.apache.org/jira/browse/DRILL-5002?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15742842#comment-15742842 ]
Vitalii Diravka edited comment on DRILL-5002 at 12/12/16 7:22 PM:
------------------------------------------------------------------
[~rkins] I tested these functions with hive plugin and they worked properly. But I see that you used the classpath plugin.
Could hive's functions work with any other plugin than hive? I tested hive's functions from drill unit tests framework and they don't work with cp plugin there.
But drillbit can run these functions with cp plugin. Anyway they returned correct result.
{code}
0: jdbc:drill:zk=local> select l_shipdate, `month`(l_shipdate), DATE_PART('month', l_shipdate) from hive.`tpch/lineitem.parquet` where l_shipdate = date '1994-02-01' limit 2;
+-------------+---------+---------+
| l_shipdate | EXPR$1 | EXPR$2 |
+-------------+---------+---------+
| 1994-02-01 | 2 | 2 |
| 1994-02-01 | 2 | 2 |
+-------------+---------+---------+
{code}
{code}
0: jdbc:drill:zk=local> select l_shipdate, `day`(l_shipdate), DATE_PART('day', l_shipdate) from cp.`tpch/lineitem.parquet` where l_shipdate = date '1998-06-02' limit 2;
+-------------+---------+---------+
| l_shipdate | EXPR$1 | EXPR$2 |
+-------------+---------+---------+
| 1998-06-02 | 2 | 2 |
| 1998-06-02 | 2 | 2 |
+-------------+---------+---------+
{code}
{code}
0: jdbc:drill:zk=local> select l_shipdate, last_day(l_shipdate) from cp.`tpch/lineitem.parquet` where l_shipdate = date '1994-02-01' limit 2;
+-------------+-------------+
| l_shipdate | EXPR$1 |
+-------------+-------------+
| 1994-02-01 | 1994-02-28 |
| 1994-02-01 | 1994-02-28 |
+-------------+-------------+
{code}
Which version of drill did you use? I tested these functions on the last drill master (1.10.0-SNAPSHOT) and on the 1.9.0 and got the same results.
What are the settings of your cp plugin? Is this issue still reproduced on your environment?
was (Author: vitalii):
[~rkins] I tested these functions with hive plugin and they worked properly. But I see that you used the classpath plugin.
Does hive's functions could work with any other plugin than hive? I tested hive's functions from drill unit tests framework and they don't work with cp plugin there.
But drillbit can run these functions with cp plugin. Anyway they returned correct result.
{code}
0: jdbc:drill:zk=local> select l_shipdate, `month`(l_shipdate), DATE_PART('month', l_shipdate) from hive.`tpch/lineitem.parquet` where l_shipdate = date '1994-02-01' limit 2;
+-------------+---------+---------+
| l_shipdate | EXPR$1 | EXPR$2 |
+-------------+---------+---------+
| 1994-02-01 | 2 | 2 |
| 1994-02-01 | 2 | 2 |
+-------------+---------+---------+
{code}
{code}
0: jdbc:drill:zk=local> select l_shipdate, `day`(l_shipdate), DATE_PART('day', l_shipdate) from cp.`tpch/lineitem.parquet` where l_shipdate = date '1998-06-02' limit 2;
+-------------+---------+---------+
| l_shipdate | EXPR$1 | EXPR$2 |
+-------------+---------+---------+
| 1998-06-02 | 2 | 2 |
| 1998-06-02 | 2 | 2 |
+-------------+---------+---------+
{code}
{code}
0: jdbc:drill:zk=local> select l_shipdate, last_day(l_shipdate) from cp.`tpch/lineitem.parquet` where l_shipdate = date '1994-02-01' limit 2;
+-------------+-------------+
| l_shipdate | EXPR$1 |
+-------------+-------------+
| 1994-02-01 | 1994-02-28 |
| 1994-02-01 | 1994-02-28 |
+-------------+-------------+
{code}
Which version of drill did you use? I tested these functions on the last drill master (1.10.0-SNAPSHOT) and on the 1.9.0 and got the same results.
What are the settings of your cp plugin? Is this issue still reproduced on your environment?
> Using hive's date functions on top of date column in parquet gives wrong results
> --------------------------------------------------------------------------------
>
> 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
>
> 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.4#6332)