Presto: java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date

[avasilevskiy]

Running Presto query on MySQL datasource :

presto:default> SELECT hdate FROM mysql.service.users limit 10;

Query 20150205_181558_00013_xduyk, FAILED, 1 node
Splits: 2 total, 0 done (0.00%)
0:01 [0 rows, 0B] [0 rows/s, 0B/s]

Query 20150205_181558_00013_xduyk failed: java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date

From MySQL workbench the same query yields:

'0000-00-00'
'2014-10-24'
'2014-12-06'
'2014-12-01'
'2014-10-07'

Presto worker log:

2015-02-05T18:15:58.406+0000 ERROR SplitRunner-13-44 com.facebook.presto.execution.TaskExecutor Error processing Split 20150205_181558_00013_xduyk.1.0-0 (start = 1423160158317, wall = 82 ms, cpu = 0 ms, calls = 1)
java.lang.RuntimeException: java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date
at com.google.common.base.Throwables.propagate(Throwables.java:160) ~[guava-18.0.jar:na]
at com.facebook.presto.plugin.jdbc.JdbcRecordCursor.handleSqlException(JdbcRecordCursor.java:243) ~[na:na]
at com.facebook.presto.plugin.jdbc.JdbcRecordCursor.isNull(JdbcRecordCursor.java:214) ~[na:na]
at com.facebook.presto.spi.RecordPageSource.getNextPage(RecordPageSource.java:101) ~[presto-spi-0.92.jar:0.92]
at com.facebook.presto.operator.TableScanOperator.getOutput(TableScanOperator.java:230) ~[presto-main-0.92.jar:0.92]
at com.facebook.presto.operator.Driver.processInternal(Driver.java:351) ~[presto-main-0.92.jar:0.92]
at com.facebook.presto.operator.Driver.processFor(Driver.java:283) ~[presto-main-0.92.jar:0.92]
at com.facebook.presto.execution.SqlTaskExecution$DriverSplitRunner.processFor(SqlTaskExecution.java:563) ~[presto-main-0.92.jar:0.92]
at com.facebook.presto.execution.TaskExecutor$PrioritizedSplitRunner.process(TaskExecutor.java:443) ~[presto-main-0.92.jar:0.92]
at com.facebook.presto.execution.TaskExecutor$Runner.run(TaskExecutor.java:577) ~[presto-main-0.92.jar:0.92]
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [na:1.8.0_31]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [na:1.8.0_31]
at java.lang.Thread.run(Unknown Source) [na:1.8.0_31]
Caused by: java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078) ~[na:na]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989) ~[na:na]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975) ~[na:na]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920) ~[na:na]
at com.mysql.jdbc.ResultSetRow.getDateFast(ResultSetRow.java:141) ~[na:na]
at com.mysql.jdbc.ByteArrayRow.getDateFast(ByteArrayRow.java:238) ~[na:na]
at com.mysql.jdbc.ResultSetImpl.getDate(ResultSetImpl.java:2211) ~[na:na]
at com.mysql.jdbc.ResultSetImpl.getDate(ResultSetImpl.java:2173) ~[na:na]
at com.mysql.jdbc.ResultSetImpl.getObject(ResultSetImpl.java:5052) ~[na:na]
at com.facebook.presto.plugin.jdbc.JdbcRecordCursor.isNull(JdbcRecordCursor.java:209) ~[na:na]
... 10 common frames omitted
2015-02-05

[yuananf]

I believe this is caused by JDBC, add zeroDateTimeBehavior=convertToNull at the end of the jdbc connection string will fix.
like this
jdbc:mysql://127.0.0.1:3306?zeroDateTimeBehavior=convertToNull

[avasilevskiy]

Looks good !

NULL
'2014-10-24'
'2014-12-06'
'2014-12-01'
'2014-10-07'

[patelnainesh222]

Thanks

[chandankumarv11]

yes it's working
[ jdbc:mysql://127.0.0.1:3306?zeroDateTimeBehavior=convertToNull ]

[Manindersaini]

worked well......

[swapna-subramani]

yes,it works...thank u