[
https://issues.apache.org/jira/browse/HIVE-20014?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Nishant Bangarwa updated HIVE-20014:
------------------------------------
Description:
Reported by [~dileep529] -
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}
was:
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}
> 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
>
> Reported by [~dileep529] -
> 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)
