Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-9632

inconsistent results between year(), month(), day(), and the actual values in formulas

    XMLWordPrintableJSON

    Details

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

      CentOS 6.5, HDP 2.2

      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

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                Humanavatar Robert Miller
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: