Thank you. I get it. Compatibility mode with PostgreSQL is incomplete and
certain regressions are to be expected when jumping major versions.
I would be willing to help patch the sw. Whether I would succeed is another
matter :-)
I'll pull the source and start a new thread as needed.
Many thanks to you and the team. Love H2!
Best,Peter
On Friday, February 16, 2024 at 08:09:52 PM EST, Andreas Reichel
<[email protected]> wrote:
Greetings!
On Sat, 2024-02-17 at 00:47 +0000, 'Peter Borissow' via H2 Database wrote:
Hi Andreas, Thanks for the quick reply! As you suggest, quoting "year" as
the alias works. Quoting transaction didn't have any effect. Several questions:
(1) What doesn't this work?
properties.setProperty("NON_KEYWORDS", "YEAR");
Because it really is just a "work around" for uncommon keywords related to more
exotic features.The problem is that the parser based on the Grammar needs to be
able to distinguish the tokens. Example:
-- value can workSELECT Year( date ) value FROM ...
vs.
-- value can workINSERT INTO table_name VALUE ..
Further illustration (unrelated to H2 though):
https://manticore-projects.com/JSQLParser/contribution.html#manage-reserved-keywords
In short, `YEAR` seems to be a keyword that can not be worked around (for good).
(2) I am in PostgreSQL mode. On a PostgreSQL server, I don't have to quote year
as an alias in PostgreSQL in a statement like this:select date as year from
transaction
The Compatibility modes are "certain syntax and functions are emulated" modes.
The Developers don't aim or claim full compliance.Example: NEXT VALUE for a
sequence is supported for all 3 syntax (Postgres, Oracle and MS SQL Server) as
a courtesy (which I find just awesome). But non of those dialect is fully
implemented (and will never be).
Why do I have to quote year? Why has the behavior changed from 1.x to 2.x?
Because H2 has massively evolved since and supports now a more complex
Grammar.More Grammar, more restricted keywords.
Example: When you don't support `Exclusive` lock modes, then `Exclusive` is not
needed as a keyword. But when support is added, then suddenly `Exclusive`
becomes a keyword.Thus it is best practise to avoid all SQL:2016 reserved
keywords (long list!) and/or to quote identifiers always.
(If you have a massive library of existing statements, then you could engage
JSQLParser and a) identify all such colliding identifiers and/or b) rewrite
your statements quoting the identifiers.)
Good luckAndreas
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/d08ac7158a006eb2e514c5d5c54fcdaadcbd47e3.camel%40manticore-projects.com.
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/64293294.2948642.1708138018550%40mail.yahoo.com.
