Error using NUMERIC columns with PreparedStatements in PostgreSQL

[Narigo]

When using NUMERIC columns, updating them with an integer value at first and updating it with a floating number later, an exception is thrown. It looks like this happens with PreparedStatements only and I couldn't reproduce it on the database directly using IntelliJ database console.

See the Spec in the PR for a reproducer of this issue.

[Narigo]

Here is the relevant stacktrace as travis logs it:

[ERROR][db-async-netty-thread-3][2016-01-28 02:57:26,715] Error with message -> ErrorMessage(fields=Map(Line -> 104, File -> numutils.c, SQLSTATE -> 22P02, Routine -> pg_atoi, Message -> invalid input syntax for integer: "123.123", Severity -> ERROR))
[ERROR][db-async-netty-thread-3][2016-01-28 02:57:26,715] Error on connectioncom.github.mauricio.async.db.postgresql.exceptions.GenericDatabaseException: ErrorMessage(fields=Map(Line -> 104, File -> numutils.c, SQLSTATE -> 22P02, Routine -> pg_atoi, Message -> invalid input syntax for integer: "123.123", Severity -> ERROR))
    at com.github.mauricio.async.db.postgresql.PostgreSQLConnection.onError(PostgreSQLConnection.scala:172)
    at com.github.mauricio.async.db.postgresql.codec.PostgreSQLConnectionHandler.channelRead0(PostgreSQLConnectionHandler.scala:151)
    at io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:105)
    at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:308)
    at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:294)
    at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:244)
    at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:308)
    at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:294)
    at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:846)
    at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:131)
    at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:511)
    at io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:468)
    at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:382)
    at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:354)
    at io.netty.util.concurrent.SingleThreadEventExecutor$2.run(SingleThreadEventExecutor.java:111)
    at java.lang.Thread.run(Thread.java:745)

[ERROR][db-async-netty-thread-3][2016-01-28 02:57:26,716] Setting error on future scala.concurrent.impl.Promise$DefaultPromise@12d25586

The strange thing is that this only happens when updating with an integer value first and setting it to something different later.

[mauricio]

This is weird, thanks @Narigo, gonna check it out!

[Narigo]

Here is a bit more investigation from psql. Most of the time it works, when using prepared statement, but there is one corner case:

If you use INTEGER as type and pass the value as String, you receive the error mentioned above.

-- using no type and pass number works
joern=# PREPARE update_numcol AS UPDATE test_table SET numcol = $1 WHERE id = 1;
PREPARE
joern=# EXECUTE update_numcol (123);
UPDATE 1
joern=# EXECUTE update_numcol (123.123);
UPDATE 1
joern=# DEALLOCATE update_numcol;
DEALLOCATE

-- using numeric type and pass number works
joern=# PREPARE update_numcol (NUMERIC) AS UPDATE test_table SET numcol = $1 WHERE id = 1;
PREPARE
joern=# EXECUTE update_numcol (123);
UPDATE 1
joern=# EXECUTE update_numcol (123.123);
UPDATE 1
joern=# DEALLOCATE update_numcol;
DEALLOCATE

-- using integer type and pass number works
joern=# PREPARE update_numcol (INTEGER) AS UPDATE test_table SET numcol = $1 WHERE id = 1;
PREPARE
joern=# EXECUTE update_numcol (123);
UPDATE 1
joern=# EXECUTE update_numcol (123.123);
UPDATE 1
joern=# DEALLOCATE update_numcol;
DEALLOCATE

-- using no type and pass string works
joern=# PREPARE update_numcol AS UPDATE test_table SET numcol = $1 WHERE id = 1;
PREPARE
joern=# EXECUTE update_numcol ('123');
UPDATE 1
joern=# EXECUTE update_numcol ('123.123');
UPDATE 1
joern=# DEALLOCATE update_numcol;
DEALLOCATE

-- using numeric type and pass string works
joern=# PREPARE update_numcol (NUMERIC) AS UPDATE test_table SET numcol = $1 WHERE id = 1;
PREPARE
joern=# EXECUTE update_numcol ('123');
UPDATE 1
joern=# EXECUTE update_numcol ('123.123');
UPDATE 1
joern=# DEALLOCATE update_numcol;
DEALLOCATE

-- using integer type and pass string does not work
joern=# PREPARE update_numcol (INTEGER) AS UPDATE test_table SET numcol = $1 WHERE id = 1;
PREPARE
joern=# EXECUTE update_numcol ('123');
UPDATE 1
joern=# EXECUTE update_numcol ('123.123');
ERROR:  invalid input syntax for integer: "123.123"
LINE 1: EXECUTE update_numcol ('123.123');
                               ^
joern=# DEALLOCATE update_numcol;
DEALLOCATE

Hope this helps to fix this weird bug :) Thanks @mauricio !

[Narigo]

@mauricio when you have debug level on, you can see, that there are different values for the types when running the tests included in this PR locally. I've never looked into the PostgreSQL binary protocol, so I'm unsure how else I can help here and if this is a bug in your implementation or if it might even be a bug in PostgreSQL (using the first occurred type inside a prepared statement?)...

[oshai]

I fixed/reverted this in jasync-sql(fork) 0.8.30: https://github.com/jasync-sql/jasync-sql/pull/16 . more details in the issue: https://github.com/jasync-sql/jasync-sql/issues/15