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

["Jacques Nadeau (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DRILL-2093?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Jacques Nadeau reassigned DRILL-2093:
-------------------------------------

    Assignee: Jacques Nadeau  (was: Steven Phillips)

> 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: Jacques Nadeau
>            Priority: Critical
>             Fix For: 0.9.0
>
>         Attachments: t1.csv
>
>
> 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)