Cannot parse "DATE" constant when using JDBC escape syntax

[Lukas Eder]

Hello,

Please consider the following test case:

try (PreparedStatement s1 = connection.prepareStatement("select 1 where date '2000-01-01' < ?");

     PreparedStatement s2 = connection.prepareStatement("select 1 where date '2000-01-01' < timestamp '2000-01-02 00:00:00'");

     PreparedStatement s3 = connection.prepareStatement("select 1 where date '2000-01-01' < {ts '2000-01-02 00:00:00'}")) {

    s1.setTimestamp(1, Timestamp.valueOf("2000-01-02 00:00:00"));

    s1.executeQuery();

    s2.executeQuery();

    s3.executeQuery();

}

Intuitively, I'd expect them to all work the same way, but I'm getting the following exception on the last query with the JDBC escape syntax:

org.h2.jdbc.JdbcSQLException: Kann "DATE" "2000-01-02 00:00:00" nicht umwandeln

Cannot parse "DATE" constant "2000-01-02 00:00:00"; SQL statement:

select 1 where date '2000-01-01' <     '2000-01-02 00:00:00'  [22007-177]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:344)

at org.h2.message.DbException.get(DbException.java:167)

at org.h2.value.ValueDate.parse(ValueDate.java:71)

at org.h2.value.Value.convertTo(Value.java:860)

at org.h2.expression.Comparison.getValue(Comparison.java:257)

at org.h2.expression.Comparison.optimize(Comparison.java:221)

at org.h2.command.dml.Select.prepare(Select.java:834)

at org.h2.command.Parser.prepareCommand(Parser.java:248)

at org.h2.engine.Session.prepareLocal(Session.java:442)

at org.h2.engine.Session.prepareCommand(Session.java:384)

at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1188)

at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:73)

at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:276)

at org.jooq.test.all.testcases.PlainSQLTests.testPlainSQLAndJDBCEscapeSyntax(PlainSQLTests.java:697)

[...]

Caused by: java.lang.NumberFormatException: For input string: "02 00:00:00"

at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)

at java.lang.Integer.parseInt(Integer.java:580)

at java.lang.Integer.parseInt(Integer.java:615)

at org.h2.util.DateTimeUtils.parseDateValue(DateTimeUtils.java:277)

at org.h2.value.ValueDate.parse(ValueDate.java:69)

... 39 more

I think there should be no difference between the SQL standard / H2 TIMESTAMP literal (statement s2) and the JDBC escape syntax.

Cheers

Lukas

[Thomas Mueller]

Hi,

You are right, this is unexpected. I have fixed this now (in the trunk).

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Lukas Eder]

Thanks, Thomas!

Cheers,

Lukas

[Thomas Mueller]

Hi,

Thanks a lot for reporting the issue!

Regards,

Thomas