[
https://issues.apache.org/jira/browse/HIVE-9632?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ashutosh Chauhan updated HIVE-9632:
-----------------------------------
Fix Version/s: 1.0.0
> inconsistent results between year(), month(), day(), and the actual values in
> formulas
> --------------------------------------------------------------------------------------
>
> Key: HIVE-9632
> URL: https://issues.apache.org/jira/browse/HIVE-9632
> Project: Hive
> Issue Type: Bug
> Components: CLI
> Affects Versions: 0.14.0
> Environment: CentOS 6.5, HDP 2.2
> Reporter: Robert Miller
> Fix For: 1.0.0
>
>
> 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
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
