[ https://issues.apache.org/jira/browse/DRILL-2093?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Steven Phillips updated DRILL-2093: ----------------------------------- Fix Version/s: 0.9.0 > 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 > 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)