[jira] [Commented] (HIVE-9632) inconsistent results between year(), month(), day(), and the actual values in formulas
[ https://issues.apache.org/jira/browse/HIVE-9632?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14317767#comment-14317767 ] Robert Miller commented on HIVE-9632: - Based on the problem scope, I expect HIVE-9278 does correct this issue. If the issue continues to exist after we are able to take an update sometime in the next two months, I will file a new ticket or reopen this ticket. > 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 > > 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)*1)+(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)*1)+(month(adjusted_activity_date_utc)*100)+day(adjusted_activity_date_utc), > > (year(adjusted_activity_date_utc)*1), > > (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-0920151 9 203535152015100 > 9 > 2015-01-0920151 9 203535152015100 > 9 > 2015-01-0920151 9 203535152015100 > 9 > 2015-01-0920151 9 203535152015100 > 9 > 2015-01-0920151 9 203535152015100 > 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)*1)+(month(adjusted_activity_date_utc)*100)+day(adjusted_activity_date_utc), > > (year(adjusted_activity_date_utc)*1), > > (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-0920151 9 201501092015100 > 9 > 2015-01-0920151 9 201501092015100 > 9 > 2015-01-0920151 9 201501092015100 > 9 > 2015-01-0920151 9 201501092015100 > 9 > 2015-01-0920151 9 201501092015100 > 9 -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (HIVE-9632) inconsistent results between year(), month(), day(), and the actual values in formulas
[ https://issues.apache.org/jira/browse/HIVE-9632?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14317746#comment-14317746 ] Navis commented on HIVE-9632: - Looks like HIVE-9278. Could you check this in hive-1.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 > > 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)*1)+(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)*1)+(month(adjusted_activity_date_utc)*100)+day(adjusted_activity_date_utc), > > (year(adjusted_activity_date_utc)*1), > > (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-0920151 9 203535152015100 > 9 > 2015-01-0920151 9 203535152015100 > 9 > 2015-01-0920151 9 203535152015100 > 9 > 2015-01-0920151 9 203535152015100 > 9 > 2015-01-0920151 9 203535152015100 > 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)*1)+(month(adjusted_activity_date_utc)*100)+day(adjusted_activity_date_utc), > > (year(adjusted_activity_date_utc)*1), > > (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-0920151 9 201501092015100 > 9 > 2015-01-0920151 9 201501092015100 > 9 > 2015-01-0920151 9 201501092015100 > 9 > 2015-01-0920151 9 201501092015100 > 9 > 2015-01-0920151 9 201501092015100 > 9 -- This message was sent by Atlassian JIRA (v6.3.4#6332)
