[HIVE-11812] datediff sometimes returns incorrect results when called with dates

• Type: Bug
• Status: Patch Available
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 2.0.0
• Component/s: UDF
• Labels: timestamp
• Assignee: Chetna Chaudhari
• Reporter: Nicholas Brenwald
• Created: 14/Sep/15 10:39
• Updated: 27/Feb/24 22:23

Description

DATEDIFF returns an incorrect result when one of the arguments is a date type.

The Hive Language Manual provides the following signature for datediff:

int datediff(string enddate, string startdate)

I think datediff should either throw an error (if date types are not supported), or return the correct result.

To reproduce, create a table:

create table t (c1 string, c2 date);

Assuming you have a table x containing some data, populate table t with 1 row:

insert into t select '2015-09-15', '2015-09-15' from x limit 1;

Then run the following 12 test queries:

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;

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	Failed
datediff(c2, '2015-09-15')	0	0	Passed
datediff(c2, '2015-09-16')	-1	-1	Passed
datediff('2015-09-14', c2)	-1	0	Failed
datediff('2015-09-15', c2)	0	0	Passed
datediff('2015-09-16', c2)	1	1	Passed

Issue Links

• is duplicated by: HIVE-14657 datediff function produce different results with timestamp and string combination
• relates to: HIVE-14657 datediff function produce different results with timestamp and string combination