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] [Created] (HIVE-9632) inconsistent results between year(), month(), day(), and the actual values in formulas
Date Tue, 10 Feb 2015 01:04:18 GMT
Robert Miller created HIVE-9632:
-----------------------------------

             Summary: 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