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
<andr...@manticore-projects.com> 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 h2-database+unsubscr...@googlegroups.com.
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 h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/64293294.2948642.1708138018550%40mail.yahoo.com.
