SYSDATE Considered Identifier when used in inner select

[razvanb96]

Hi all,

I've been playing around with h2 v2.1.210 and observed quite an odd behaviour when having SYSDATE Keyword inside an inner select

Running SELECT SYSDATE FROM DUAL is going to work without any issues, but using sysdate inside an inner select is going to throw an error, because it seems like sysdate is expected to be an identifier.

SELECT * FROM (SELECT SYSDATE FROM DUAL) returns

Column "SYSDATE" not found; SQL statement: CREATE FORCE VIEW ( SELECT "SYSDATE" ) AS SELECT "SYSDATE" [42122-210]

[katzyn]

Normally you should use CURRENT_DATE, CURRENT_TIMESTAMP, or LOCALTIMESTAMP in H2. They all have standard-compliant implementations and work as expected.

H2 should probably reject SYSDATE and all similar functions in all compatibility modes, with exception for Oracle compatibility mode in which it should be a keyword.

[katzyn]

Hypothetically this issue can be fixed in a simpler way by preparation of inner query before reading of its execution plan for temporary view.

[razvanb96]

Hi @katzyn I forgot to mention that indeed i am interested in using Oracle Compatibility Mode, as I am planing on using H2 for some Integration tests/ =fast Development Database, so changing the SYSDATE in the queries into CURRENT_TIMESTAMP is not exactly something that would work well in our System.

Thank you very much for you replay and PR. I checked my specific usecase against the code that you are propsing in there an it indeed fixes my problem. Waiting for it to get included in an offictial h2 release!