[
https://issues.apache.org/jira/browse/HIVE-20014?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Nishant Bangarwa resolved HIVE-20014.
-------------------------------------
Resolution: Invalid
Verified that this is the same behavior with orc tables.
This is an expected behavior change in 3.0
> Druid SECOND/HOUR/MINUTE does not return correct values when applied to
> String Columns
> --------------------------------------------------------------------------------------
>
> Key: HIVE-20014
> URL: https://issues.apache.org/jira/browse/HIVE-20014
> Project: Hive
> Issue Type: Bug
> Reporter: Nishant Bangarwa
> Assignee: Nishant Bangarwa
> Priority: Major
>
> Query SELECT MINUTE(`time1`) FROM calcs; returns null when the String column
> only contains timestamp and does not contain any date information in the
> column. The Druid parser fails to parse the time string values and returns
> null.
> {code}
> 1: jdbc:hive2://ctr-e138-1518143905142-379982> SELECT MINUTE(`time1`) FROM
> calcs;
> INFO : Compiling
> command(queryId=hive_20180627145215_05147329-b8d8-491c-9bab-6fd5045542db):
> SELECT MINUTE(`time1`) FROM calcs
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:vc,
> type:int, comment:null)], properties:null)
> INFO : Completed compiling
> command(queryId=hive_20180627145215_05147329-b8d8-491c-9bab-6fd5045542db);
> Time taken: 0.134 seconds
> INFO : Executing
> command(queryId=hive_20180627145215_05147329-b8d8-491c-9bab-6fd5045542db):
> SELECT MINUTE(`time1`) FROM calcs
> INFO : Completed executing
> command(queryId=hive_20180627145215_05147329-b8d8-491c-9bab-6fd5045542db);
> Time taken: 0.002 seconds
> INFO : OK
> +-------+
> | vc |
> +-------+
> | NULL |
> | NULL |
> | NULL |
> | NULL |
> | NULL |
> | NULL |
> | NULL |
> | NULL |
> | NULL |
> | NULL |
> | NULL |
> | NULL |
> | NULL |
> | NULL |
> | NULL |
> | NULL |
> | NULL |
> +-------+
> 17 rows selected (0.266 seconds)
> 1: jdbc:hive2://ctr-e138-1518143905142-379982> SELECT time1 from calcs;
> INFO : Compiling
> command(queryId=hive_20180627145225_93b872de-a698-4859-9730-983eede6935d):
> SELECT time1 from calcs
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:time1,
> type:string, comment:null)], properties:null)
> INFO : Completed compiling
> command(queryId=hive_20180627145225_93b872de-a698-4859-9730-983eede6935d);
> Time taken: 0.116 seconds
> INFO : Executing
> command(queryId=hive_20180627145225_93b872de-a698-4859-9730-983eede6935d):
> SELECT time1 from calcs
> INFO : Completed executing
> command(queryId=hive_20180627145225_93b872de-a698-4859-9730-983eede6935d);
> Time taken: 0.003 seconds
> INFO : OK
> +-----------+
> | time1 |
> +-----------+
> | 22:20:14 |
> | 22:50:16 |
> | 19:36:22 |
> | 19:48:23 |
> | 00:05:57 |
> | NULL |
> | 04:48:07 |
> | NULL |
> | 19:57:33 |
> | NULL |
> | 04:40:49 |
> | 02:05:25 |
> | NULL |
> | NULL |
> | 12:33:57 |
> | 18:58:41 |
> | 09:33:31 |
> +-----------+
> 17 rows selected (0.202 seconds)
> 1: jdbc:hive2://ctr-e138-1518143905142-379982> EXPLAIN SELECT
> MINUTE(`time1`) FROM calcs;
> INFO : Compiling
> command(queryId=hive_20180627145237_39e53a7e-35cb-4e17-8ccb-884c6f6358cd):
> EXPLAIN SELECT MINUTE(`time1`) FROM calcs
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:Explain,
> type:string, comment:null)], properties:null)
> INFO : Completed compiling
> command(queryId=hive_20180627145237_39e53a7e-35cb-4e17-8ccb-884c6f6358cd);
> Time taken: 0.107 seconds
> INFO : Executing
> command(queryId=hive_20180627145237_39e53a7e-35cb-4e17-8ccb-884c6f6358cd):
> EXPLAIN SELECT MINUTE(`time1`) FROM calcs
> INFO : Starting task [Stage-1:EXPLAIN] in serial mode
> INFO : Completed executing
> command(queryId=hive_20180627145237_39e53a7e-35cb-4e17-8ccb-884c6f6358cd);
> Time taken: 0.003 seconds
> INFO : OK
> +----------------------------------------------------+
> | Explain |
> +----------------------------------------------------+
> | Plan optimized by CBO. |
> | |
> | Stage-0 |
> | Fetch Operator |
> | limit:-1 |
> | Select Operator [SEL_1] |
> | Output:["_col0"] |
> | TableScan [TS_0] |
> |
> Output:["vc"],properties:{"druid.fieldNames":"vc","druid.fieldTypes":"int","druid.query.json":"{\"queryType\":\"scan\",\"dataSource\":\"druid_tableau.calcs\",\"intervals\":[\"1900-01-01T00:00:00.000Z/3000-01-01T00:00:00.000Z\"],\"virtualColumns\":[{\"type\":\"expression\",\"name\":\"vc\",\"expression\":\"timestamp_extract(timestamp_parse(\\\"time1\\\",null,'UTC'),'MINUTE','UTC')\",\"outputType\":\"LONG\"}],\"columns\":[\"vc\"],\"resultFormat\":\"compactedList\"}","druid.query.type":"scan"}
> |
> | |
> +----------------------------------------------------+
> 10 rows selected (0.136 seconds)
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
