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 work
SELECT Year( date ) value FROM ...
vs.
-- value can work
INSERT 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 luck
Andreas
--
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.
