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

• Type: Bug
• Status: Resolved
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 0.8.0
• Fix Version/s: 1.0.0
• Component/s: Storage - JSON
• Assignee: Jacques Nadeau
• Reporter: Victoria Markman
• Created: 28/Jan/15 18:05
• Updated: 05/May/15 14:29
• Resolved: 05/May/15 14:29

Description

I have a csv file and am trying to create matching file in json format.

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`;

Create table succeeds, but I can't read back time or timestamp:

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)

Issue Links

• is duplicated by: DRILL-2513 JsonWriter writes a timestamp values for 'time' datatype