[jira] [Created] (DRILL-2093) Columns of time and timestamp data type are not stored correctly in json format on CTAS

["Victoria Markman (JIRA)" <ji...@apache.org>]

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)