Timestamps with timezone prevents queries

1

Hello!

I tried to connect to Redshift through JDBC-driver but querying table with timestamptz-type column fails.

Stacktrace from that is below:

      FUNCTION ERROR: Invalid format: "2017-12-01 00:00:03.767+00" is malformed at "+00"

SqlOperatorImpl JDBC_SUB_SCAN
Location 1:0:4
Fragment 1:0

[Error Id: ee873569-6fec-43c7-a8a6-3501ad307f59 on 22ce96165155:31010]

  (java.lang.IllegalArgumentException) Invalid format: "2017-12-01 00:00:03.767+00" is malformed at "+00"
    org.joda.time.format.DateTimeFormatter.parseLocalDateTime():900
    org.joda.time.LocalDateTime.parse():168
    com.dremio.s.ProjectorGen2.doEval():2593
    com.dremio.sabot.op.project.ProjectorTemplate.projectRecords():53
    com.dremio.exec.store.CoercionReader.next():225
    com.dremio.sabot.op.scan.ScanOperator.outputData():208
    com.dremio.sabot.driver.SmartOp$SmartProducer.outputData():518
    com.dremio.sabot.driver.StraightPipe.pump():56
    com.dremio.sabot.driver.Pipeline.doPump():82
    com.dremio.sabot.driver.Pipeline.pumpOnce():72
    com.dremio.sabot.exec.fragment.FragmentExecutor$DoAsPumper.run():288
    com.dremio.sabot.exec.fragment.FragmentExecutor$DoAsPumper.run():284
    java.security.AccessController.doPrivileged():-2
    javax.security.auth.Subject.doAs():422
    org.apache.hadoop.security.UserGroupInformation.doAs():1807
    com.dremio.sabot.exec.fragment.FragmentExecutor.run():243
    com.dremio.sabot.exec.fragment.FragmentExecutor.access$800():83
    com.dremio.sabot.exec.fragment.FragmentExecutor$AsyncTaskImpl.run():577
    com.dremio.sabot.task.AsyncTaskWrapper.run():92
    com.dremio.sabot.task.slicing.SlicingThread.run():71

Probably the cause is related to code generation and invalid mappings between java types and coercion rules and actual JDBC-types. Fix should be pretty easy to implement if you know where to look for.

Opening bug tracker would be helpful

2

Hi,

Looks like you are formatting a column from string to datetime, what formatting pattern (YYYY-MM-DD etc) are you using?

thanks,
Doron

3

Not really,
Easy test would go following:

-- create new table with some data in it
CREATE TABLE test1
(
  created TIMESTAMP WITH TIME ZONE,
  val     VARCHAR(10)
);
--- add some data:
INSERT INTO test1 (created, val) VALUES ('2018-02-05 20:49:24.648000 +03:00', 'val1')
INSERT INTO test1 (created, val) VALUES ('2018-04-05 20:49:24.648000 +03:00', 'val2')

Now connect through Redshift-connector with Dremio and issue a new query such as:

SELECT val
FROM TAT.tat.public.test1

This should work normally, but when querying with timestamp:

SELECT val, created
FROM TAT.tat.public.test1

      FUNCTION ERROR: Invalid format: "2018-02-05 17:49:24.648+00" is malformed at "+00"

SqlOperatorImpl JDBC_SUB_SCAN
Location 1:0:4
Fragment 1:0

[Error Id: 2e6bd158-b48d-4478-aca7-066ac3fb64ce on 22ce96165155:31010]

  (java.lang.IllegalArgumentException) Invalid format: "2018-02-05 17:49:24.648+00" is malformed at "+00"
    org.joda.time.format.DateTimeFormatter.parseLocalDateTime():900
    org.joda.time.LocalDateTime.parse():168
    com.dremio.s.ProjectorGen386.doEval():66
    com.dremio.sabot.op.project.ProjectorTemplate.projectRecords():53
    com.dremio.exec.store.CoercionReader.next():225
    com.dremio.sabot.op.scan.ScanOperator.outputData():208
    com.dremio.sabot.driver.SmartOp$SmartProducer.outputData():518
    com.dremio.sabot.driver.StraightPipe.pump():56
    com.dremio.sabot.driver.Pipeline.doPump():82
    com.dremio.sabot.driver.Pipeline.pumpOnce():72
    com.dremio.sabot.exec.fragment.FragmentExecutor$DoAsPumper.run():288
    com.dremio.sabot.exec.fragment.FragmentExecutor$DoAsPumper.run():284
    java.security.AccessController.doPrivileged():-2
    javax.security.auth.Subject.doAs():422
    org.apache.hadoop.security.UserGroupInformation.doAs():1807
    com.dremio.sabot.exec.fragment.FragmentExecutor.run():243
    com.dremio.sabot.exec.fragment.FragmentExecutor.access$800():83
    com.dremio.sabot.exec.fragment.FragmentExecutor$AsyncTaskImpl.run():577
    com.dremio.sabot.task.AsyncTaskWrapper.run():92
    com.dremio.sabot.task.slicing.SlicingThread.run():
4

With table without timezone definition:

CREATE TABLE test2
(
	created                   TIMESTAMP,
	val VARCHAR(10)
)

INSERT INTO test2 (created, val) VALUES ('2018-04-05 20:49:24.648000', 'val1')
INSERT INTO test2 (created, val) VALUES ('2018-05-05 20:49:24.648000', 'val2')

everything works normally