DOUBLE PRECISION being classified as TIMESTAMP

1

We are seeing an error when attempting to run a basic SELECT to a PostgreSQL instance that contains a column DOUBLE PRECISION. When the value in that data type has an number with exponent notation e.g. 1.590273450E7, it fails with the below error message:

[ErrorId: 524e67a3-e2d1-439a-97e0-74645d8cacbd]
com.dremio.common.exceptions.UserException: Failure while attempting to read from database.
    at com.dremio.common.exceptions.UserException$Builder.build(UserException.java:746) ~[dremio-common-3.1.1-201901281837360699-30c9d74.jar:3.1.1-201901281837360699-30c9d74]
    at com.dremio.exec.store.jdbc.JdbcRecordReader.next(JdbcRecordReader.java:250) [dremio-extra-plugin-jdbc-3.1.1-201901281837360699-30c9d74.jar:3.1.1-201901281837360699-30c9d74]
    at com.dremio.exec.store.CoercionReader.next(CoercionReader.java:212) [dremio-sabot-kernel-3.1.1-201901281837360699-30c9d74.jar:3.1.1-201901281837360699-30c9d74]
    at com.dremio.sabot.op.scan.ScanOperator.outputData(ScanOperator.java:209) [dremio-sabot-kernel-3.1.1-201901281837360699-30c9d74.jar:3.1.1-201901281837360699-30c9d74]
    at com.dremio.sabot.driver.SmartOp$SmartProducer.outputData(SmartOp.java:526) [dremio-sabot-kernel-3.1.1-201901281837360699-30c9d74.jar:3.1.1-201901281837360699-30c9d74]
    at com.dremio.sabot.driver.StraightPipe.pump(StraightPipe.java:56) [dremio-sabot-kernel-3.1.1-201901281837360699-30c9d74.jar:3.1.1-201901281837360699-30c9d74]
    at com.dremio.sabot.driver.Pipeline.doPump(Pipeline.java:109) [dremio-sabot-kernel-3.1.1-201901281837360699-30c9d74.jar:3.1.1-201901281837360699-30c9d74]
    at com.dremio.sabot.driver.Pipeline.pumpOnce(Pipeline.java:99) [dremio-sabot-kernel-3.1.1-201901281837360699-30c9d74.jar:3.1.1-201901281837360699-30c9d74]
    at com.dremio.sabot.exec.fragment.FragmentExecutor$DoAsPumper.run(FragmentExecutor.java:311) [dremio-sabot-kernel-3.1.1-201901281837360699-30c9d74.jar:3.1.1-201901281837360699-30c9d74]
    at com.dremio.sabot.exec.fragment.FragmentExecutor$DoAsPumper.run(FragmentExecutor.java:307) [dremio-sabot-kernel-3.1.1-201901281837360699-30c9d74.jar:3.1.1-201901281837360699-30c9d74]
    at java.security.AccessController.doPrivileged(Native Method) [na:1.8.0_191]
    at javax.security.auth.Subject.doAs(Subject.java:422) [na:1.8.0_191]
    at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1836) [hadoop-common-2.8.3.jar:na]
    at com.dremio.sabot.exec.fragment.FragmentExecutor.run(FragmentExecutor.java:264) [dremio-sabot-kernel-3.1.1-201901281837360699-30c9d74.jar:3.1.1-201901281837360699-30c9d74]
    at com.dremio.sabot.exec.fragment.FragmentExecutor.access$1000(FragmentExecutor.java:93) [dremio-sabot-kernel-3.1.1-201901281837360699-30c9d74.jar:3.1.1-201901281837360699-30c9d74]
    at com.dremio.sabot.exec.fragment.FragmentExecutor$AsyncTaskImpl.run(FragmentExecutor.java:619) [dremio-sabot-kernel-3.1.1-201901281837360699-30c9d74.jar:3.1.1-201901281837360699-30c9d74]
    at com.dremio.sabot.task.AsyncTaskWrapper.run(AsyncTaskWrapper.java:103) [dremio-sabot-kernel-3.1.1-201901281837360699-30c9d74.jar:3.1.1-201901281837360699-30c9d74]
    at com.dremio.sabot.task.slicing.SlicingThread.mainExecutionLoop(SlicingThread.java:123) [dremio-extra-sabot-scheduler-3.1.1-201901281837360699-30c9d74.jar:3.1.1-201901281837360699-30c9d74]
    at com.dremio.sabot.task.slicing.SlicingThread.run(SlicingThread.java:68) [dremio-extra-sabot-scheduler-3.1.1-201901281837360699-30c9d74.jar:3.1.1-201901281837360699-30c9d74]
Caused by: org.postgresql.util.PSQLException: Bad value for type timestamp/date/time: {1}
    at org.postgresql.jdbc.TimestampUtils.parseBackendTimestamp(TimestampUtils.java:348) ~[postgresql-42.1.1.jre7.jar:42.1.1.jre7]
    at org.postgresql.jdbc.TimestampUtils.toTimestamp(TimestampUtils.java:380) ~[postgresql-42.1.1.jre7.jar:42.1.1.jre7]
    at org.postgresql.jdbc.TimestampUtils.toDate(TimestampUtils.java:479) ~[postgresql-42.1.1.jre7.jar:42.1.1.jre7]
    at org.postgresql.jdbc.PgResultSet.getDate(PgResultSet.java:497) ~[postgresql-42.1.1.jre7.jar:42.1.1.jre7]
    at org.apache.commons.dbcp2.DelegatingResultSet.getDate(DelegatingResultSet.java:682) ~[commons-dbcp2-2.2.0.jar:2.2.0]
    at org.apache.commons.dbcp2.DelegatingResultSet.getDate(DelegatingResultSet.java:682) ~[commons-dbcp2-2.2.0.jar:2.2.0]
    at com.dremio.exec.store.jdbc.JdbcRecordReader$DateCopier.copy(JdbcRecordReader.java:540) ~[dremio-extra-plugin-jdbc-3.1.1-201901281837360699-30c9d74.jar:3.1.1-201901281837360699-30c9d74]
    at com.dremio.exec.store.jdbc.JdbcRecordReader.next(JdbcRecordReader.java:240) [dremio-extra-plugin-jdbc-3.1.1-201901281837360699-30c9d74.jar:3.1.1-201901281837360699-30c9d74]
    ... 17 common frames omitted
Caused by: java.lang.NumberFormatException: Expected time to be colon-separated, got 'e'
    at org.postgresql.jdbc.TimestampUtils.parseBackendTimestamp(TimestampUtils.java:251) ~[postgresql-42.1.1.jre7.jar:42.1.1.jre7]
    ... 24 common frames omitted

Dremio is classifying the column as a timestamp instead of what the schema says which is DOUBLE PRECISION. Why is this occurring?

2

Hi @kylehayes,

What columns are in the table you are querying? Would you be able to submit a profile?

3

id - UUID
week - DATE
hours - DOUBLE PRECISION
scenario_id - UUID

4

Thanks @kylehayes,

Would it be possible to upload a profile?

6

When I enabled the legacy dialect, it began to work. Thanks!

1 Like