[HIVE-19695] Year Month Day extraction functions need to add an implicit cast for column that are String types

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.0.0
• Fix Version/s: 3.1.0
• Component/s: Druid integration, Query Planning
• Target Version/s: 3.1.0
• Assignee: Slim Bouguerra
• Reporter: Slim Bouguerra
• Created: 24/May/18 14:39
• Updated: 31/Jul/18 01:32
• Resolved: 16/Jun/18 15:44

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