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

Year Month Day extraction functions need to add an implicit cast for column that are String types

    XMLWordPrintableJSON

Details

    Description

      To avoid surprising/wrong results, Hive Query plan shall add an explicit cast over non date/timestamp column type when user try to extract Year/Month/Hour etc..
      This is an example of misleading results.

      create table test_base_table(`timecolumn` timestamp, `date_c` string, `timestamp_c` string,  `metric_c` double);
      insert into test_base_table values ('2015-03-08 00:00:00', '2015-03-10', '2015-03-08 00:00:00', 5.0);
      CREATE TABLE druid_test_table
      STORED BY 'org.apache.hadoop.hive.druid.DruidStorageHandler'
      TBLPROPERTIES ("druid.segment.granularity" = "DAY")
      AS select
      cast(`timecolumn` as timestamp with local time zone) as `__time`, `date_c`, `timestamp_c`, `metric_c` FROM test_base_table;
      select
      year(date_c), month(date_c),day(date_c), hour(date_c),
      year(timestamp_c), month(timestamp_c),day(timestamp_c), hour(timestamp_c)
      from druid_test_table;
      

      will return the following wrong results:

      PREHOOK: query: select
      year(date_c), month(date_c),day(date_c), hour(date_c),
      year(timestamp_c), month(timestamp_c),day(timestamp_c), hour(timestamp_c)
      from druid_test_table
      PREHOOK: type: QUERY
      PREHOOK: Input: default@druid_test_table
      #### A masked pattern was here ####
      POSTHOOK: query: select
      year(date_c), month(date_c),day(date_c), hour(date_c),
      year(timestamp_c), month(timestamp_c),day(timestamp_c), hour(timestamp_c)
      from druid_test_table
      POSTHOOK: type: QUERY
      POSTHOOK: Input: default@druid_test_table
      #### A masked pattern was here ####
      1969	12	31	16	1969	12	31	16 
      

      Attachments

        1. HIVE-19695.2.patch
          72 kB
          Slim Bouguerra
        2. HIVE-19695.1.patch
          32 kB
          Slim Bouguerra
        3. HIVE-19695.patch
          16 kB
          Ashutosh Chauhan
        4. HIVE-19695.patch
          15 kB
          Slim Bouguerra

        Activity

          People

            bslim Slim Bouguerra
            bslim Slim Bouguerra
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: