Victoria Markman created DRILL-2093:
---------------------------------------
Summary: Columns of time and timestamp data type are not stored
correctly in json format on CTAS
Key: DRILL-2093
URL: https://issues.apache.org/jira/browse/DRILL-2093
Project: Apache Drill
Issue Type: Bug
Components: Storage - JSON
Affects Versions: 0.8.0
Reporter: Victoria Markman
Assignee: Steven Phillips
Priority: Critical
I have a csv file and am trying to create matching file in json format.
{code}
alter session set `store.format` = 'json';
create table test_json(c_varchar, c_integer, c_bigint, c_smalldecimal,
c_bigdecimal, c_float, c_date, c_time, c_timestamp, c_boolean) as
select
case when columns[0] = '' then cast(null as varchar(255)) else
cast(columns[0] as varchar(255)) end,
case when columns[1] = '' then cast(null as integer) else
cast(columns[1] as integer) end,
case when columns[2] = '' then cast(null as bigint) else
cast(columns[2] as bigint) end,
case when columns[3] = '' then cast(null as decimal(18,4)) else
cast(columns[3] as decimal(18, 4)) end,
case when columns[4] = '' then cast(null as decimal(38,4)) else
cast(columns[4] as decimal(38, 4)) end,
case when columns[5] = '' then cast(null as float) else cast(columns[5]
as float) end,
case when columns[6] = '' then cast(null as date) else cast(columns[6]
as date) end,
case when columns[7] = '' then cast(null as time) else cast(columns[7]
as time) end,
case when columns[8] = '' then cast(null as timestamp) else
cast(columns[8] as timestamp) end,
case when columns[9] = '' then cast(null as boolean) else
cast(columns[9] as boolean) end
from `t1.csv`;
{code}
Create table succeeds, but I can't read back time or timestamp:
{code}
0: jdbc:drill:schema=dfs> select cast(c_time as time) from test_json;
Query failed: RemoteRpcException: Failure while running fragment., Invalid
format: "1970-01-01T08:13:16.000Z" is malformed at "70-01-01T08:13:16.000Z" [
b3f6c0c9-01e4-410f-919d-a899ede35ed9 on atsqa4-133.qa.lab:31010 ]
[ b3f6c0c9-01e4-410f-919d-a899ede35ed9 on atsqa4-133.qa.lab:31010 ]
Error: exception while executing query: Failure while executing query.
(state=,code=0)
0: jdbc:drill:schema=dfs> select c_time from test_json;
+------------+
| c_time |
+------------+
| 1970-01-01T08:13:16.000Z |
| 1970-01-01T04:15:45.000Z |
| 1970-01-01T18:21:06.000Z |
| 1970-01-01T13:35:54.000Z |
| 1970-01-01T05:17:11.000Z |
+------------+
5 rows selected (0.055 seconds)
0: jdbc:drill:schema=dfs> select cast(c_timestamp as timestamp) from test_json;
Query failed: RemoteRpcException: Failure while running fragment., Invalid
format: "2014-03-16T03:55:21.000Z" is malformed at "T03:55:21.000Z" [
527a42e5-2eb7-41d7-a55b-c6cee0779db6 on atsqa4-133.qa.lab:31010 ]
[ 527a42e5-2eb7-41d7-a55b-c6cee0779db6 on atsqa4-133.qa.lab:31010 ]
Error: exception while executing query: Failure while executing query.
(state=,code=0)
{code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
