Merge fails for OffsetDateTime when offset is UTC (+00:00)

Question

I’m having an issue with HSQLDB and MERGE with the following merge logic if I use an OffsetDateTime with offset +00:00

    private static final String CREATE_TABLE_SQL = 
            "create table sample (" + 
               "code varchar2(50), " + 
               "updated timestamp with time zone" + 
            ")";

    private static final String MERGE_SQL =
            "merge into sample t " +
            "using (select ? as code, ? as updated from dual) val " +
            "on (t.code = val.code) " +
            "when matched then update set t.updated = val.updated " +
            "when not matched then insert(code, updated) values (val.code, val.updated)";

    private int merge(String code, OffsetDateTime updated) throws SQLException {
        try (PreparedStatement statement = connection.prepareStatement(MERGE_SQL)) {
            statement.setString(1, code);
            statement.setObject(2, updated, Types.TIMESTAMP_WITH_TIMEZONE);
            return statement.executeUpdate();
        }
    }

The merge statement works fine for all offsets except UTC (eg offset +01:00 works) I think this is caused by the fact that toString() of an OffsetDateTime at UTC has the Z suffix instead of +00:00. It seems that an INSERT statement does not have the same problem as MERGE

Exception stack trace

java.sql.SQLDataException: data exception: invalid datetime format
            at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
            at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
            at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)
            at org.hsqldb.jdbc.JDBCPreparedStatement.executeUpdate(Unknown Source)
            at com.sample.DeletemeHsqlBugTest.merge(DeletemeHsqlBugTest.java:79)
            at com.sample.DeletemeHsqlBugTest.testMerge(DeletemeHsqlBugTest.java:58)
            at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
            ...
Caused by: org.hsqldb.HsqlException: data exception: invalid datetime format
            at org.hsqldb.error.Error.error(Unknown Source)
            at org.hsqldb.error.Error.error(Unknown Source)
            at org.hsqldb.types.DateTimeType.convertToDatetimeSpecial(Unknown Source)
            at org.hsqldb.types.DateTimeType.convertToType(Unknown Source)
            at org.hsqldb.ExpressionOp.getValue(Unknown Source)
            at org.hsqldb.StatementDML.getInsertData(Unknown Source)
            at org.hsqldb.StatementDML.executeMergeStatement(Unknown Source)
            at org.hsqldb.StatementDML.getResult(Unknown Source)
            at org.hsqldb.StatementDMQL.execute(Unknown Source)
            at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
            at org.hsqldb.Session.execute(Unknown Source)
            ... 73 more

As a workaround I can do the following which feels hacky

    private static final DateTimeFormatter FORMATTER = new DateTimeFormatterBuilder()
            .appendPattern("yyyy-MM-dd HH:mm:ss.SSS")
            .appendOffset("+HH:MM", "+00:00")
            .toFormatter();

    private int merge(String code, OffsetDateTime updated) throws SQLException {
        try (PreparedStatement statement = connection.prepareStatement(MERGE_SQL)) {
            statement.setString(1, code);
            statement.setString(2, FORMATTER.format(updated));
            return statement.executeUpdate();
        }
    }

Answer 1

I posted this question on the hsqldb-user mailing list and got the following response which fixes my issue. Re-posting here since it's easier to find it here on Stack Overflow

it should work if you add a CAST to the required type:

"using (select ? as code, cast(? as timestamp with time zone) as updated from dual) val " +

Update

It seems that this issue has been fixed and will be available in the next release of HSQLDB

• https://sourceforge.net/p/hsqldb/feature-requests/362/
• https://sourceforge.net/p/hsqldb/svn/6638/
• https://github.com/ryenus/hsqldb/commit/a7cc02e47f93c5001d804d26f246cba7b2657f11