Exception when binding variable in a concatenation expression

[GoogleCodeExporter]

This works:
-----------
PreparedStatement stmt = connection.prepareStatement(
  "select (cast(1 as varchar) || 'ab' || '45') from dual");
stmt.executeQuery();

So does this:
-------------
PreparedStatement stmt = connection.prepareStatement(
  "select (cast(1 as varchar) || ? || '45') from dual");
stmt.setString(1, "ab");
stmt.executeQuery();

And this:
---------
PreparedStatement stmt = connection.prepareStatement(
  "select (cast(1 as varchar) || ? || ?) from dual");
stmt.setString(1, "ab");
stmt.setString(2, "45");
stmt.executeQuery();

And this:
---------
PreparedStatement stmt = connection.prepareStatement(
  "select (cast(1 as varchar) || ? || ?) from dual");
stmt.setString(1, "ab");
stmt.setInt(2, 45);
stmt.executeQuery();

But not this:
-------------
PreparedStatement stmt = connection.prepareStatement(
  "select (cast(? as varchar) || ? || ?) from dual");
stmt.setInt(1, 1);
stmt.setString(2, "ab");
stmt.setInt(3, 45);
stmt.executeQuery();

Exception (message doesn't make sense to me, and I don't see why there should 
be a problem):
--------------------------------------------
org.h2.jdbc.JdbcSQLException: Datenumwandlungsfehler beim Umwandeln von "a"
Data conversion error converting "a"; SQL statement:
select (cast(? as varchar) || ? || ?) from dual [22018-155]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
    at org.h2.message.DbException.get(DbException.java:156)
    at org.h2.value.Value.convertTo(Value.java:847)
    at org.h2.value.Value.compareTo(Value.java:893)
    at org.h2.engine.Database.areEqual(Database.java:272)
    at org.h2.command.dml.Query.sameResultAsLast(Query.java:232)
    at org.h2.command.dml.Query.query(Query.java:279)
    at org.h2.command.dml.Query.query(Query.java:260)
    at org.h2.command.dml.Query.query(Query.java:37)
    at org.h2.command.CommandContainer.query(CommandContainer.java:80)
    at org.h2.command.Command.executeQuery(Command.java:181)
    at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:96)
    at org.jooq.test.jOOQAbstractTest.testFunctionsOnStrings(jOOQAbstractTest.java:3839)

Note, a double cast would fix the issue again. This works:
----------------------------------------------------------
PreparedStatement stmt = connection.prepareStatement(
  "select (cast(cast(? as int) as varchar) || ? || ?) from dual");
stmt.setInt(1, 1);
stmt.setString(2, "ab");
stmt.setInt(3, 45);
stmt.executeQuery();

Original issue reported on code.google.com by lukas.eder@gmail.com on 13 Jul 2011 at 5:40

[GoogleCodeExporter]

This works for me (I can't reproduce the problem):

    public static void main(String... args) throws Exception {
        Class.forName("org.h2.Driver");
        DeleteDbFiles.execute("~/temp", "test", true);
        Connection conn;
        conn = DriverManager.getConnection("jdbc:h2:~/temp/test");
        PreparedStatement stmt = conn.prepareStatement(
                "select (cast(? as varchar) || ? || ?) from dual");
        stmt.setInt(1, 1);
        stmt.setString(2, "ab");
        stmt.setInt(3, 45);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            System.out.println(rs.getString(1));
        }
        conn.close();
    }

Original comment by thomas.t...@gmail.com on 20 Aug 2011 at 10:46

• Changed state: WorksForMe

[GoogleCodeExporter]

Thanks for looking into this. I'll check again, maybe there is some piece of 
information missing from my bug report

Original comment by lukas.eder@gmail.com on 22 Aug 2011 at 8:12