NumberFormatException when running the same query twice with different bind variable types

[Lukas Eder]

Hello,

Here is a simple scenario to reproduce the issue:

        PreparedStatement stmt = connection.prepareStatement("select (? || ? || ?) from dual");
        stmt.setString(1, "a");
        stmt.setString(2, "b");
        stmt.setString(3, "c");
        stmt.executeQuery();

        stmt.setInt(1, 1);
        stmt.setString(2, "ab");
        stmt.setInt(3, 45);
        stmt.executeQuery();

The second execution fails with a NumberFormatException:

org.h2.jdbc.JdbcSQLException: Datenumwandlungsfehler beim Umwandeln von "a"
Data conversion error converting "a"; SQL statement:
select (? || ? || ?) from dual [22018-163]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
    at org.h2.message.DbException.get(DbException.java:158)
    at org.h2.value.Value.convertTo(Value.java:852)
    at org.h2.value.Value.compareTo(Value.java:898)
    at org.h2.engine.Database.areEqual(Database.java:276)
    at org.h2.command.dml.Query.sameResultAsLast(Query.java:239)
    at org.h2.command.dml.Query.query(Query.java:286)
    at org.h2.command.dml.Query.query(Query.java:267)
    at org.h2.command.dml.Query.query(Query.java:36)
    at org.h2.command.CommandContainer.query(CommandContainer.java:82)
    at org.h2.command.Command.executeQuery(Command.java:187)
    at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:96)
    [...]
Caused by: java.lang.NumberFormatException: For input string: "a"
    at java.lang.NumberFormatException.forInputString(NumberFormatException.java:48)
    at java.lang.Integer.parseInt(Integer.java:449)
    at java.lang.Integer.parseInt(Integer.java:499)
    at org.h2.value.Value.convertTo(Value.java:809)
    ... 38 more

From the stack trace (marked in red), it looks as though some sort of bind variable cache is used before the same SQL statement is re-executed. It is, however, unsafe to assume that bind types stay the same

Cheers
Lukas

[Thomas Mueller]

Hi,

Thanks a lot! Yes, this is a bug. It will be fixed in the next release.

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/q6Ea1hE3feAJ.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.