Nicholas Brenwald created HIVE-11812:
----------------------------------------
Summary: datediff sometimes returns incorrect results when called
with dates
Key: HIVE-11812
URL: https://issues.apache.org/jira/browse/HIVE-11812
Project: Hive
Issue Type: Bug
Components: UDF
Affects Versions: 2.0.0
Reporter: Nicholas Brenwald
Priority: Minor
DATEDIFF returns an incorrect result when one of the arguments is a date type.
The Hive Language Manual provides the following signature for datediff:
{code}
int datediff(string enddate, string startdate)
{code}
I think datediff should either throw an error (if date types are not
supported), or return the correct result.
To reproduce, create a table:
{code}
create table t (c1 string, c2 date);
{code}
Assuming you have a table x containing some data, populate table t with 1 row:
{code}
insert into t select '2015-09-15', '2015-09-15' from x limit 1;
{code}
Then run the following 12 test queries:
{code}
select datediff(c1, '2015-09-14') from t;
select datediff(c1, '2015-09-15') from t;
select datediff(c1, '2015-09-16') from t;
select datediff('2015-09-14', c1) from t;
select datediff('2015-09-15', c1) from t;
select datediff('2015-09-16', c1) from t;
select datediff(c2, '2015-09-14') from t;
select datediff(c2, '2015-09-15') from t;
select datediff(c2, '2015-09-16') from t;
select datediff('2015-09-14', c2) from t;
select datediff('2015-09-15', c2) from t;
select datediff('2015-09-16', c2) from t;
{code}
The below table summarises the result. All results for column c1 (which is a
string) are correct, but when using c2 (which is a date), two of the results
are incorrect.
|| Test || Expected Result || Actual Result || Passed / Failed ||
|datediff(c1, '2015-09-14')| 1 | 1| Passed |
|datediff(c1, '2015-09-15')| 0 | 0| Passed |
|datediff(c1, '2015-09-16') | -1 | -1| Passed |
|datediff('2015-09-14', c1) | -1 | -1| Passed |
|datediff('2015-09-15', c1)| 0 | 0| Passed |
|datediff('2015-09-16', c1)| 1 | 1| Passed |
|datediff(c2, '2015-09-14')| 1 | 0| {color:red}Failed{color} |
|datediff(c2, '2015-09-15')| 0 | 0| Passed |
|datediff(c2, '2015-09-16') | -1 | -1| Passed |
|datediff('2015-09-14', c2) | -1 | 0 | {color:red}Failed{color} |
|datediff('2015-09-15', c2)| 0 | 0| Passed |
|datediff('2015-09-16', c2)| 1 | 1| Passed |
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
