[HIVE-9632] inconsistent results between year(), month(), day(), and the actual values in formulas

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 0.14.0
• Fix Version/s: 1.0.0
• Component/s: CLI

Environment

CentOS 6.5, HDP 2.2

• Assignee: Unassigned
• Reporter: Robert Miller
• Created: 10/Feb/15 01:00
• Updated: 12/Feb/15 16:37
• Resolved: 12/Feb/15 08:01

Description

In wanting to create a date dimension value which would match our existing database environment, I figured I would be able to do as I have done in the past and use the following formula:

(year(date)*10000)+(month(date)*100)+day(date)

Given the date of 2015-01-09, the above formula should result in a value of 20150109. Instead, the resulting value is 20353515.

SELECT
> adjusted_activity_date_utc,
> year(adjusted_activity_date_utc),
> month(adjusted_activity_date_utc),
> day(adjusted_activity_date_utc),
> (year(adjusted_activity_date_utc)*10000)+(month(adjusted_activity_date_utc)*100)+day(adjusted_activity_date_utc),
> (year(adjusted_activity_date_utc)*10000),
> (month(adjusted_activity_date_utc)*100),
> day(adjusted_activity_date_utc)
> from event_histories limit 5;
OK
adjusted_activity_date_utc _c1 _c2 _c3 _c4 _c5 _c6 _c7
2015-01-09 2015 1 9 20353515 20150000 100 9
2015-01-09 2015 1 9 20353515 20150000 100 9
2015-01-09 2015 1 9 20353515 20150000 100 9
2015-01-09 2015 1 9 20353515 20150000 100 9
2015-01-09 2015 1 9 20353515 20150000 100 9

Oddly enough, this works as expected when a specific date value is used for the column.

I have tried this with partition and non-partition columns and found the result to be the same.

SELECT
> adjusted_activity_date_utc,
> year(adjusted_activity_date_utc),
> month(adjusted_activity_date_utc),
> day(adjusted_activity_date_utc),
> (year(adjusted_activity_date_utc)*10000)+(month(adjusted_activity_date_utc)*100)+day(adjusted_activity_date_utc),
> (year(adjusted_activity_date_utc)*10000),
> (month(adjusted_activity_date_utc)*100),
> day(adjusted_activity_date_utc)
> from event_histories
> where adjusted_activity_date_utc = '2015-01-09'
> limit 5;
OK
adjusted_activity_date_utc _c1 _c2 _c3 _c4 _c5 _c6 _c7
2015-01-09 2015 1 9 20150109 20150000 100 9
2015-01-09 2015 1 9 20150109 20150000 100 9
2015-01-09 2015 1 9 20150109 20150000 100 9
2015-01-09 2015 1 9 20150109 20150000 100 9
2015-01-09 2015 1 9 20150109 20150000 100 9

Issue Links

• duplicates: HIVE-9278 Cached expression feature broken in one case