hive-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Robert Miller (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (HIVE-9632) inconsistent results between year(), month(), day(), and the actual values in formulas
Date Thu, 12 Feb 2015 08:02:12 GMT

    [ 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)*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)

Mime
View raw message