Interval day to second feature not supported in H2 1.4.199

[Saurabh Singh]

Hi,As part of spring boot upgrade, I have upgraded H2 db from 1.4.190 to 1.4.199. now few of my test cases are getting failed saying org.h2.jdbc.JdbcSqlFeatureNotSupportedException: Feature not supported : "INTERVAL DAY TO SECOND - BIGINT".

Anybody has encountered any such issue before or general idea what can cause this?

[Evgenij Ryazanov]

Hello.

I think it can be caused only by invalid SQL that was used with the old version of H2 as a workaround over its missing features, and that workaround doesn't work any more; or by some other invalid SQL expression that somehow worked in the old version.

You need to show us your query, without it we can only guess what can be wrong.

[Saurabh Singh]

Thanks Evgenij for replying. You are right, it's due to invalid SQL. In nutshell part of my SQL query in select clause says Floor( Date - Date - Integer) .. thats why m getting Interval - BigInt not supported.

Anyway this is working fine in Oracle. Was Working good in previous H2.

[Evgenij Ryazanov]

According to the SQL Standard, result of subtraction of one datetime value from another is an interval, but Oracle violates the Standard and returns a DATE JULIAN for (DATE - DATE) and NUMBER for (TIMESTAMP - TIMESTAMP) for historic reasons. H2 does not provide compatibility on that level, that's too much, H2 is not an emulator of other databases.

It works in older versions of H2 only because they also violate the Standard and incorrectly return BIGINT values from that subtraction operation. H2 was fixed in 1.4.198, so you can try to use 1.4.197 if you cannot rewrite your query in some correct way. Unfortunately, it looks like Oracle, at least version 18c, doesn't support the datetime and interval arithmetic from the Standard well enough and you need different queries for Oracle and H2.