Nishant Bangarwa created HIVE-20014:
---------------------------------------
Summary: 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
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)
