hive-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Matt McCline (JIRA)" <j...@apache.org>
Subject [jira] [Comment Edited] (HIVE-15338) Wrong result from vectorized DATEDIFF with scalar parameter of type DATE
Date Wed, 07 Dec 2016 04:18:58 GMT

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

Matt McCline edited comment on HIVE-15338 at 12/7/16 4:18 AM:
--------------------------------------------------------------

Use mySQL as the *gold* standard.

mysql> SELECT
    ->   fl_time,
    ->   datediff(fl_time, "2000-01-01"),
    ->   datediff(fl_time, date "2000-01-01"),
    ->   datediff(fl_time, "2007-03-14"),
    ->   datediff(fl_time, date "2007-03-14")
    -> FROM date_udf_flight_orc;
+---------------------+---------------------------------+--------------------------------------+---------------------------------+--------------------------------------+
| fl_time             | datediff(fl_time, "2000-01-01") | datediff(fl_time, date "2000-01-01")
| datediff(fl_time, "2007-03-14") | datediff(fl_time, date "2007-03-14") |
+---------------------+---------------------------------+--------------------------------------+---------------------------------+--------------------------------------+
| 2010-10-20 07:00:00 |                            3945 |                                
3945 |                            1316 |                                 1316 |
...
+---------------------+---------------------------------+--------------------------------------+---------------------------------+--------------------------------------+
137 rows in set (0.01 sec)

mysql> SELECT
    ->   fl_date,
    ->   datediff(fl_date, "2000-01-01"),
    ->   datediff(fl_date, date "2000-01-01"),
    ->   datediff(fl_date, "2007-03-14"),
    ->   datediff(fl_date, date "2007-03-14")
    -> FROM date_udf_flight_orc;
+------------+---------------------------------+--------------------------------------+---------------------------------+--------------------------------------+
| fl_date    | datediff(fl_date, "2000-01-01") | datediff(fl_date, date "2000-01-01") | datediff(fl_date,
"2007-03-14") | datediff(fl_date, date "2007-03-14") |
+------------+---------------------------------+--------------------------------------+---------------------------------+--------------------------------------+
| 2010-10-20 |                            3945 |                                 3945 |  
                         1316 |                                 1316 |
...
+------------+---------------------------------+--------------------------------------+---------------------------------+--------------------------------------+
137 rows in set (0.01 sec)
{code}

So, it turns out that vectorization is correct on this.


was (Author: mmccline):
mysql> SELECT
    ->   fl_time,
    ->   datediff(fl_time, "2000-01-01"),
    ->   datediff(fl_time, date "2000-01-01"),
    ->   datediff(fl_time, "2007-03-14"),
    ->   datediff(fl_time, date "2007-03-14")
    -> FROM date_udf_flight_orc;
+---------------------+---------------------------------+--------------------------------------+---------------------------------+--------------------------------------+
| fl_time             | datediff(fl_time, "2000-01-01") | datediff(fl_time, date "2000-01-01")
| datediff(fl_time, "2007-03-14") | datediff(fl_time, date "2007-03-14") |
+---------------------+---------------------------------+--------------------------------------+---------------------------------+--------------------------------------+
| 2010-10-20 07:00:00 |                            3945 |                                
3945 |                            1316 |                                 1316 |
| 2010-10-20 07:00:00 |                            3945 |                                
3945 |                            1316 |                                 1316 |
| 2010-10-20 07:00:00 |                            3945 |                                
3945 |                            1316 |                                 1316 |
| 2010-10-20 07:00:00 |                            3945 |                                
3945 |                            1316 |                                 1316 |
| 2010-10-20 07:00:00 |                            3945 |                                
3945 |                            1316 |                                 1316 |
| 2010-10-20 07:00:00 |                            3945 |                                
3945 |                            1316 |                                 1316 |
| 2010-10-20 07:00:00 |                            3945 |                                
3945 |                            1316 |                                 1316 |
| 2010-10-20 07:00:00 |                            3945 |                                
3945 |                            1316 |                                 1316 |
| 2010-10-20 07:00:00 |                            3945 |                                
3945 |                            1316 |                                 1316 |
| 2010-10-20 07:00:00 |                            3945 |                                
3945 |                            1316 |                                 1316 |
| 2010-10-20 07:00:00 |                            3945 |                                
3945 |                            1316 |                                 1316 |
| 2010-10-21 07:00:00 |                            3946 |                                
3946 |                            1317 |                                 1317 |
| 2010-10-21 07:00:00 |                            3946 |                                
3946 |                            1317 |                                 1317 |
| 2010-10-21 07:00:00 |                            3946 |                                
3946 |                            1317 |                                 1317 |
| 2010-10-21 07:00:00 |                            3946 |                                
3946 |                            1317 |                                 1317 |
| 2010-10-21 07:00:00 |                            3946 |                                
3946 |                            1317 |                                 1317 |
| 2010-10-21 07:00:00 |                            3946 |                                
3946 |                            1317 |                                 1317 |
| 2010-10-21 07:00:00 |                            3946 |                                
3946 |                            1317 |                                 1317 |
| 2010-10-21 07:00:00 |                            3946 |                                
3946 |                            1317 |                                 1317 |
| 2010-10-21 07:00:00 |                            3946 |                                
3946 |                            1317 |                                 1317 |
| 2010-10-21 07:00:00 |                            3946 |                                
3946 |                            1317 |                                 1317 |
| 2010-10-21 07:00:00 |                            3946 |                                
3946 |                            1317 |                                 1317 |
| 2010-10-21 07:00:00 |                            3946 |                                
3946 |                            1317 |                                 1317 |
| 2010-10-22 07:00:00 |                            3947 |                                
3947 |                            1318 |                                 1318 |
| 2010-10-22 07:00:00 |                            3947 |                                
3947 |                            1318 |                                 1318 |
| 2010-10-22 07:00:00 |                            3947 |                                
3947 |                            1318 |                                 1318 |
| 2010-10-22 07:00:00 |                            3947 |                                
3947 |                            1318 |                                 1318 |
| 2010-10-22 07:00:00 |                            3947 |                                
3947 |                            1318 |                                 1318 |
| 2010-10-22 07:00:00 |                            3947 |                                
3947 |                            1318 |                                 1318 |
| 2010-10-22 07:00:00 |                            3947 |                                
3947 |                            1318 |                                 1318 |
| 2010-10-22 07:00:00 |                            3947 |                                
3947 |                            1318 |                                 1318 |
| 2010-10-22 07:00:00 |                            3947 |                                
3947 |                            1318 |                                 1318 |
| 2010-10-22 07:00:00 |                            3947 |                                
3947 |                            1318 |                                 1318 |
| 2010-10-22 07:00:00 |                            3947 |                                
3947 |                            1318 |                                 1318 |
| 2010-10-23 07:00:00 |                            3948 |                                
3948 |                            1319 |                                 1319 |
| 2010-10-23 07:00:00 |                            3948 |                                
3948 |                            1319 |                                 1319 |
| 2010-10-23 07:00:00 |                            3948 |                                
3948 |                            1319 |                                 1319 |
| 2010-10-23 07:00:00 |                            3948 |                                
3948 |                            1319 |                                 1319 |
| 2010-10-23 07:00:00 |                            3948 |                                
3948 |                            1319 |                                 1319 |
| 2010-10-23 07:00:00 |                            3948 |                                
3948 |                            1319 |                                 1319 |
| 2010-10-23 07:00:00 |                            3948 |                                
3948 |                            1319 |                                 1319 |
| 2010-10-23 07:00:00 |                            3948 |                                
3948 |                            1319 |                                 1319 |
| 2010-10-23 07:00:00 |                            3948 |                                
3948 |                            1319 |                                 1319 |
| 2010-10-23 07:00:00 |                            3948 |                                
3948 |                            1319 |                                 1319 |
| 2010-10-23 07:00:00 |                            3948 |                                
3948 |                            1319 |                                 1319 |
| 2010-10-23 07:00:00 |                            3948 |                                
3948 |                            1319 |                                 1319 |
| 2010-10-24 07:00:00 |                            3949 |                                
3949 |                            1320 |                                 1320 |
| 2010-10-24 07:00:00 |                            3949 |                                
3949 |                            1320 |                                 1320 |
| 2010-10-24 07:00:00 |                            3949 |                                
3949 |                            1320 |                                 1320 |
| 2010-10-24 07:00:00 |                            3949 |                                
3949 |                            1320 |                                 1320 |
| 2010-10-24 07:00:00 |                            3949 |                                
3949 |                            1320 |                                 1320 |
| 2010-10-24 07:00:00 |                            3949 |                                
3949 |                            1320 |                                 1320 |
| 2010-10-24 07:00:00 |                            3949 |                                
3949 |                            1320 |                                 1320 |
| 2010-10-24 07:00:00 |                            3949 |                                
3949 |                            1320 |                                 1320 |
| 2010-10-24 07:00:00 |                            3949 |                                
3949 |                            1320 |                                 1320 |
| 2010-10-24 07:00:00 |                            3949 |                                
3949 |                            1320 |                                 1320 |
| 2010-10-24 07:00:00 |                            3949 |                                
3949 |                            1320 |                                 1320 |
| 2010-10-24 07:00:00 |                            3949 |                                
3949 |                            1320 |                                 1320 |
| 2010-10-25 07:00:00 |                            3950 |                                
3950 |                            1321 |                                 1321 |
| 2010-10-25 07:00:00 |                            3950 |                                
3950 |                            1321 |                                 1321 |
| 2010-10-25 07:00:00 |                            3950 |                                
3950 |                            1321 |                                 1321 |
| 2010-10-25 07:00:00 |                            3950 |                                
3950 |                            1321 |                                 1321 |
| 2010-10-25 07:00:00 |                            3950 |                                
3950 |                            1321 |                                 1321 |
| 2010-10-25 07:00:00 |                            3950 |                                
3950 |                            1321 |                                 1321 |
| 2010-10-25 07:00:00 |                            3950 |                                
3950 |                            1321 |                                 1321 |
| 2010-10-25 07:00:00 |                            3950 |                                
3950 |                            1321 |                                 1321 |
...
+---------------------+---------------------------------+--------------------------------------+---------------------------------+--------------------------------------+
137 rows in set (0.01 sec)

mysql> SELECT
    ->   fl_date,
    ->   datediff(fl_date, "2000-01-01"),
    ->   datediff(fl_date, date "2000-01-01"),
    ->   datediff(fl_date, "2007-03-14"),
    ->   datediff(fl_date, date "2007-03-14")
    -> FROM date_udf_flight_orc;
+------------+---------------------------------+--------------------------------------+---------------------------------+--------------------------------------+
| fl_date    | datediff(fl_date, "2000-01-01") | datediff(fl_date, date "2000-01-01") | datediff(fl_date,
"2007-03-14") | datediff(fl_date, date "2007-03-14") |
+------------+---------------------------------+--------------------------------------+---------------------------------+--------------------------------------+
| 2010-10-20 |                            3945 |                                 3945 |  
                         1316 |                                 1316 |
| 2010-10-20 |                            3945 |                                 3945 |  
                         1316 |                                 1316 |
| 2010-10-20 |                            3945 |                                 3945 |  
                         1316 |                                 1316 |
| 2010-10-20 |                            3945 |                                 3945 |  
                         1316 |                                 1316 |
| 2010-10-20 |                            3945 |                                 3945 |  
                         1316 |                                 1316 |
| 2010-10-20 |                            3945 |                                 3945 |  
                         1316 |                                 1316 |
| 2010-10-20 |                            3945 |                                 3945 |  
                         1316 |                                 1316 |
| 2010-10-20 |                            3945 |                                 3945 |  
                         1316 |                                 1316 |
| 2010-10-20 |                            3945 |                                 3945 |  
                         1316 |                                 1316 |
| 2010-10-20 |                            3945 |                                 3945 |  
                         1316 |                                 1316 |
| 2010-10-20 |                            3945 |                                 3945 |  
                         1316 |                                 1316 |
| 2010-10-21 |                            3946 |                                 3946 |  
                         1317 |                                 1317 |
| 2010-10-21 |                            3946 |                                 3946 |  
                         1317 |                                 1317 |
| 2010-10-21 |                            3946 |                                 3946 |  
                         1317 |                                 1317 |
| 2010-10-21 |                            3946 |                                 3946 |  
                         1317 |                                 1317 |
| 2010-10-21 |                            3946 |                                 3946 |  
                         1317 |                                 1317 |
| 2010-10-21 |                            3946 |                                 3946 |  
                         1317 |                                 1317 |
| 2010-10-21 |                            3946 |                                 3946 |  
                         1317 |                                 1317 |
| 2010-10-21 |                            3946 |                                 3946 |  
                         1317 |                                 1317 |
| 2010-10-21 |                            3946 |                                 3946 |  
                         1317 |                                 1317 |
| 2010-10-21 |                            3946 |                                 3946 |  
                         1317 |                                 1317 |
| 2010-10-21 |                            3946 |                                 3946 |  
                         1317 |                                 1317 |
| 2010-10-21 |                            3946 |                                 3946 |  
                         1317 |                                 1317 |
| 2010-10-22 |                            3947 |                                 3947 |  
                         1318 |                                 1318 |
| 2010-10-22 |                            3947 |                                 3947 |  
                         1318 |                                 1318 |
| 2010-10-22 |                            3947 |                                 3947 |  
                         1318 |                                 1318 |
| 2010-10-22 |                            3947 |                                 3947 |  
                         1318 |                                 1318 |
| 2010-10-22 |                            3947 |                                 3947 |  
                         1318 |                                 1318 |
| 2010-10-22 |                            3947 |                                 3947 |  
                         1318 |                                 1318 |
| 2010-10-22 |                            3947 |                                 3947 |  
                         1318 |                                 1318 |
...
+------------+---------------------------------+--------------------------------------+---------------------------------+--------------------------------------+
137 rows in set (0.01 sec)
{code}

So, it turns out that vectorization is correct on this.

> Wrong result from vectorized DATEDIFF with scalar parameter of type DATE
> ------------------------------------------------------------------------
>
>                 Key: HIVE-15338
>                 URL: https://issues.apache.org/jira/browse/HIVE-15338
>             Project: Hive
>          Issue Type: Bug
>          Components: Hive
>            Reporter: Matt McCline
>            Assignee: Matt McCline
>            Priority: Critical
>         Attachments: HIVE-15338.01.patch
>
>
> Vectorization in vectorized DATEDIFF when parameter is (date '12-01-16', column). Jason
noted that ('12-01-16', column) treats the first parameter as a string and is a different
case.
> Current Q file test vectorized_date_funcs.q DOES NOT test the date scalar type case.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message