Hive SQL CURRENT_DATE导致的datediff错误

Hive SQL中的datediff函数返回的是2个日期的间隔天数。在使用过程中发现了一个比较有趣的坑：

SELECT customer_id, COUNT(DISTINCT date(createdate)) - 1 AS frequency
    , datediff(MAX(createdate), MIN(createdate)) AS recency
    , datediff(CURRENT_DATE, MIN(createdate)) AS T
    , CASE
        WHEN COUNT(DISTINCT createdate) - 1 = 0 THEN 0
        ELSE SUM(totaltakeoff) / COUNT(DISTINCT createdate)
    END AS monetary_value
FROM orderdb.orderdetail

其中createdate为datetime类型，执行SQL发现存在recency>T的数据。执行如下代码，结果也存在问题：

SELECT customer_id, COUNT(DISTINCT date(createdate)) - 1 AS frequency
    , datediff(to_date(MAX(createdate)), to_date(MIN(createdate))) AS recency
    , datediff(CURRENT_DATE, to_date(MIN(createdate))) AS T
    , CASE
        WHEN COUNT(DISTINCT createdate) - 1 = 0 THEN 0
        ELSE SUM(totaltakeoff) / COUNT(DISTINCT createdate)
    END AS monetary_value
FROM orderdb.orderdetail

可能原因是CURRENT_DATE，其中CURRENT_DATE的值等于TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP())，使用TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP())再做测试，结果又是正常的。

SELECT customer_id, COUNT(DISTINCT date(createdate)) - 1 AS frequency
    , datediff(to_date(MAX(createdate)), to_date(MIN(createdate))) AS recency
    , datediff(TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP())), to_date(MIN(createdate))) AS T
    , CASE
        WHEN COUNT(DISTINCT createdate) - 1 = 0 THEN 0
        ELSE SUM(totaltakeoff) / COUNT(DISTINCT createdate)
    END AS monetary_value
FROM orderdb.orderdetail

至此，大致可以将原因定位到CURRENT_DATE上，中间最主要的区别是：current_date返回的内容是date格式，而to_date返回的是字符串（2.1版本之前是string，2.1版本后面是返回date）。再做一次进一步测试如下：

SELECT datediff('2018-09-16 00:01:55', '2018-09-16 00:00:52') AS t1
    , datediff(current_date, '2018-09-16 00:00:52') AS t2
    , datediff(current_date, '2018-09-17 00:00:52') AS t3
    , datediff(current_date, '2018-09-16') AS t4
    , datediff(current_date, '2018-09-17') AS t5
    , datediff(TO_DATE(current_date), '2018-09-16 00:00:52') AS t6
    , datediff(TO_DATE(current_date), '2018-09-17 00:00:52') AS t7
    , datediff(TO_DATE(current_date), '2018-09-16') AS t8
    , datediff(TO_DATE(current_date), '2018-09-17') AS t9

执行后的结果为：0 0 0 0 1 0 1 0，进一步寻找，发现已经人提交了bug：

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

Hengyu Dai commented on HIVE-18304:
-----------------------------------

SimpleDateFormat.parse(String source) method will convert String type(UTC) to java.util.Date
type(use current JVM timezone), this may lead deviations in time when JVM timezone is not
UTC, my environment is GMT+8,  8 hours is added comparing to the UTC time.
while for a  date type argument, the default JVM timezone is used. 
The patch uploaded treats String type and Date type at the same way to remove the deviations.

> datediff() UDF returns a wrong result when dealing with a (date, string) input
> ------------------------------------------------------------------------------
>
>                 Key: HIVE-18304
>                 URL: https://issues.apache.org/jira/browse/HIVE-18304
>             Project: Hive
>          Issue Type: Bug
>          Components: UDF
>            Reporter: Hengyu Dai
>            Assignee: Hengyu Dai
>            Priority: Minor
>         Attachments: 0001.patch
>
>
> for date type argument, datediff() use DateConverter to convert input to a java Date
object, 
> for example, a '2017-12-18' will get 2017-12-18T00:00:00.000+0800
> for string type argument, datediff() use TextConverter to convert a string to date,
> for '2012-01-01' we will get 2012-01-01T08:00:00.000+0800
> now, datediff() will return a number less than the real date diff
> we should use TextConverter to deal with date input too.
> reproduce:
> {code:java}
> select datediff(cast('2017-12-18' as date), '2012-01-01'); --2177
> select datediff('2017-12-18', '2012-01-01'); --2178
> {code}