Error when using bind variables without casting in subselects involving UNIONs

[GoogleCodeExporter]

This works:
-----------
PreparedStatement stmt = connection.prepareStatement(
  "(select 2008 \"y\" from dual) union (select 2009 \"y\" from dual)");
stmt.executeQuery();

So does this:
-------------
PreparedStatement stmt = connection.prepareStatement(
  "(select ? \"y\" from dual) union (select 2009 \"y\" from dual)");
stmt.setInt(1, 2008);
stmt.executeQuery();

This doesn't:
-------------
PreparedStatement stmt = connection.prepareStatement(
  "(select ? \"y\" from dual) union (select ? \"y\" from dual)");
stmt.setInt(1, 2008);
stmt.setInt(2, 2009);
stmt.executeQuery();

Somewhat cryptic Exception:
---------------------------
org.h2.jdbc.JdbcSQLException: Unbekannter Datentyp: "?, ?"
Unknown data type: "?, ?"; SQL statement:
(select ? "y" from dual) union (select ? "y" from dual) [50004-155]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
    at org.h2.message.DbException.get(DbException.java:167)
    at org.h2.message.DbException.get(DbException.java:144)
    at org.h2.value.Value.getHigherOrder(Value.java:312)
    at org.h2.command.dml.SelectUnion.prepare(SelectUnion.java:280)
    at org.h2.command.Parser.prepare(Parser.java:202)
    at org.h2.command.Parser.prepareCommand(Parser.java:214)
    at org.h2.engine.Session.prepareLocal(Session.java:427)
    at org.h2.engine.Session.prepareCommand(Session.java:375)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1088)
    at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:71)
    at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:241)

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

[GoogleCodeExporter]

Data types need to be known at compile time, otherwise the statement can't be 
compiled.
I will currently not fix this currently.

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

• Changed state: WontFix

[GoogleCodeExporter]

Thanks for clarification. I imagine that bind-time or execution-time type 
inference (as Oracle does it, for instance) is hard to achieve. A possible 
compile-time solution might be to infer "other" if types are unknown? I don't 
know the implications of that, though.

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