#1450 metadata for datetime types incorrect

[Luciano Joublanc]

In 2.3.3 The table INFORMATION_SCHEMA.SYSTEM_COLUMNS, which just shows the result of the jdbc introspection getColumns has some problems with 'DATA_TYPE' and 'TYPE_NAME' of datetime types. There are two issues I can see:

1) For a column declared as TIMESTAMP WITH ZONE, the 'DATA_TYPE' returned is 93, which corresponds to TIMESTAMP (i.e. without zone) according to the JDBC spec http://docs.oracle.com/javase/8/docs/api/constant-values.html#java.sql.Types.TIMESTAMP_WITH_TIMEZONE . It should be 2014. I suspect the same for TIME WITH ZONE, but haven't checked
2) When declaring a DOMAIN of TIMESTAMP WITH TIMEZONE CHECK VALUE IS NOT NULL, and then declaring a table column with this alias, 'DATA_TYPE' is again 93 as in issue #1, but also, the 'TYPE_NAME' is 'TIMESTAMP' (i.e. without time zone). Again, I suspect a similar problem with other date/time types but haven't checked.

Here is the result of an example, query on SYSTEM_COLUMNS. The first row is declared directly as TIMESTAMP WITH TIME ZONE as in #1, whereas the others all use an alias DOMAIN as in #2 above.

PUBLIC  REXDB   FOO BAR 93  TIMESTAMP WITH TIME ZONE    32              1   [null]  [null]  93  3   0   1   YES [null]  [null]  [null]      NO  NO
PUBLIC  REXDB   BUS_TRADE   VERSION 93  TIMESTAMP   26              0   [null]  CURRENT_TIMESTAMP   93  3   0   11  NO  [null]  [null]  [null]      NO  NO
PUBLIC  REXDB   BUS_ORDER   VERSION 93  TIMESTAMP   26              0   [null]  CURRENT_TIMESTAMP   93  3   0   12  NO  [null]  [null]  [null]      NO  NO
PUBLIC  REXDB   BUS_ORDER_STATE VERSION 93  TIMESTAMP   26              0   [null]  [null]  93  3   0   2   NO  [null]  [null]  [null]      NO  NO
PUBLIC  REXDB   BUS_BOOK_INSTRUMENT_PNL VERSION 93  TIMESTAMP   26              0   [null]  CURRENT_TIMESTAMP   93  3   0   10  NO  [null]  [null]  [null]      NO  NO
PUBLIC  REXDB   BUS_REMOTE_TRADE_CODE   VERSION 93  TIMESTAMP   26              0   [null]  [null]  93  3   0   4   NO  [null]  [null]  [null]      NO  NO
PUBLIC  REXDB   BUS_REMOTE_ORDER_CODE   VERSION 93  TIMESTAMP   26              0   [null]  [null]  93  3   0   4   NO  [null]  [null]  [null]      NO  NO

I guess that not many people care about this, but looking into rising populatrity of Java8 time, the driver should distinguish between these clearly to help the user to cast these as e.g. Time or OffsetTime.

[Fred Toussi]

These values are new in Java 1.8. As we publish jars that work with Java 1.6 and later, we cannot change this for now. Changing the behaviour according to the Java version will cause inconsistency.

[Luciano Joublanc]

I just wanted to add here that I've discovered from the documentation that there is a property which affects this. From your manual:

JDBC has an unfortunate limitation and does not include type codes for SQL datetime types that have a TIME ZONE property. Therefore, for compatibility with database tools that are limited to the JDBC type codes, HyperSQL reports these types by default as datetime types without TIME ZONE. You can use the URL property hsqldb.translate_dti_types=false to override the default behaviour.

For example, switching this off results in value 95 (instead of 93) being returned for TIMESTAMP WITH TIME ZONE. So it may be worth considering changing this to 2014, at least for 'DATA_TYPE' (the JDBC type), if not 'SQL_TYPE', when translate_dti_types is false. This would go some way to preserving backward compatibility.

[Fred Toussi]

Java 8 and JDBC 4.2 are supported in version 2.4.0. The code for TIMESTAMP WITH TIME ZONE is changed to 2014.