Re: [h2] Year alias in select statement
Thanks for checking Evgenij. I was mistaken - adding "YEAR" to the
"NON_KEYWORDS" does indeed work. There was a different issue on my end that was
throwing things off. Appreciate everyone's help.
Best,Peter
On Saturday, February 17, 2024 at 02:19:07 AM EST, Evgenij Ryazanov
wrote:
Hello!
YEAR is a reserved word even in archaic SQL-92, so it was a bad idea to use it
as unquoted identifier. BTW, DATE and TRANSACTION are reserved words too, but
H2 allows their usage as identifiers. Also there is no YEAR function in the
Standard, correct syntax is EXTRACT(YEAR FROM someValue).
Newer versions of H2 may have more keywords than old versions and reserved
words from the latest version of the SQL Standard (from SQL:2023 for now) are
potential candidates.
Anyway, the following test case prints 2024 as expected, so NON_KEYWORDS
settings works well and you have some other problem in your application, most
likely your query is executed from a connection with different settings.
Properties p = new Properties();
p.put("MODE", "PostgreSQL");
p.put("DATABASE_TO_LOWER", "TRUE");
p.put("DEFAULT_NULL_ORDERING", "HIGH");
p.put("NON_KEYWORDS", "YEAR");
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:", p)) {
Statement s = connection.createStatement();
s.execute("CREATE TABLE transaction(date DATE) AS VALUES CURRENT_DATE");
ResultSet rs = s.executeQuery("SELECT year(date) AS year FROM transaction");
rs.next();
System.out.println(rs.getInt(1));
}
--
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/fc4ea7a4-c9f8-47a1-89be-191e9d807f15n%40googlegroups.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/797384501.3014179.1708171658213%40mail.yahoo.com.
Re: [h2] Year alias in select statement
Hello!
YEAR is a reserved word even in archaic SQL-92, so it was a bad idea to use
it as unquoted identifier. BTW, DATE and TRANSACTION are reserved words
too, but H2 allows their usage as identifiers. Also there is no YEAR
function in the Standard, correct syntax is EXTRACT(YEAR FROM someValue).
Newer versions of H2 may have more keywords than old versions and reserved
words from the latest version of the SQL Standard (from SQL:2023 for now)
are potential candidates.
Anyway, the following test case prints 2024 as expected, so NON_KEYWORDS
settings works well and you have some other problem in your application,
most likely your query is executed from a connection with different
settings.
Properties p = new Properties();
p.put("MODE", "PostgreSQL");
p.put("DATABASE_TO_LOWER", "TRUE");
p.put("DEFAULT_NULL_ORDERING", "HIGH");
p.put("NON_KEYWORDS", "YEAR");
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:", p))
{
Statement s = connection.createStatement();
s.execute("CREATE TABLE transaction(date DATE) AS VALUES CURRENT_DATE");
ResultSet rs = s.executeQuery("SELECT year(date) AS year FROM transaction");
rs.next();
System.out.println(rs.getInt(1));
}
--
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/fc4ea7a4-c9f8-47a1-89be-191e9d807f15n%40googlegroups.com.
Re: [h2] Year alias in select statement
Please discuss with the H2 developers first. My understanding is: the current trend goes to SQL:2016 compliance and the Compatibility modes are more or less seen as a tolerated legacy. So any extension of those modes may not be welcome unconditionally, when it introduced complexity or maintenance issues. I may be wrong of course. Cheers Andreas On Sat, 2024-02-17 at 02:46 +, 'Peter Borissow' via H2 Database wrote: > 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 -- 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/1da41bb4d651bf53e448314e9becd705a52d8b4b.camel%40manticore-projects.com.
Re: [h2] Year alias in select statement
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
wrote:
Greetings!
On Sat, 2024-02-17 at 00:47 +, '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.
Re: [h2] Year alias in select statement
On Sat, 2024-02-17 at 08:09 +0700, Andreas Reichel wrote: > Quoting transaction didn't have any effect. Try again on MS SQL Server -- 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/b2d47526b97a12b3ce697ad8a752bdee32c6e0a5.camel%40manticore-projects.com.
Re: [h2] Year alias in select statement
Greetings!
On Sat, 2024-02-17 at 00:47 +, '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 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.
Re: [h2] Year alias in select statement
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");
(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
Why do I have to quote year? Why has the behavior changed from 1.x to 2.x?
Thanks in advance!Peter
On Friday, February 16, 2024 at 07:31:03 PM EST, Andreas Reichel
wrote:
Good Morning.
`YEAR` is a SQL:2016 reserved keyword:
http://www.h2database.com/html/advanced.html?highlight=keyword=keyword#keywordsYou
will need to quote your alias:
SELECT Year( date ) AS "YEAR"
FROM "transaction"
;
Similar thing for `TRANSACTION`.It is always advisable to avoid such keywords
are object identifiers.
CheersAndreas
On Sat, 2024-02-17 at 00:25 +, 'Peter Borissow' via H2 Database wrote:
Dear H2 Community, I ran into an unexpected error today migrating from 1.x
to 2.x. I'm using H2 2.2.224 in PostgreSQL mode using the following parameters
properties.setProperty("MODE", "PostgreSQL");
properties.setProperty("DATABASE_TO_LOWER", "TRUE");
properties.setProperty("DEFAULT_NULL_ORDERING", "HIGH");
The following query is failing:
select year(date) as year from transaction
Error:
SELECT year(date) AS [*]year FROM transaction"; expected "identifier"
Looks like it doesn't like the year alias in the select statement. The
following query works:
select year(date) as y from transaction
I tried the following but it didn't seem to help:
properties.setProperty("NON_KEYWORDS", "YEAR");
Any suggestions?
Thanks,Peter
--
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/1040186991.2923444.1708129516544%40mail.yahoo.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/83c98a852da9a0ef3683051984c612304744c66c.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/1548447917.2938581.1708130867832%40mail.yahoo.com.
Re: [h2] Year alias in select statement
Good Morning.
`YEAR` is a SQL:2016 reserved
keyword:
http://www.h2database.com/html/advanced.html?highlight=keyword=keyword#keywords
You will need to quote your alias:
SELECT Year( date ) AS "YEAR"
FROM "transaction"
;
Similar thing for `TRANSACTION`.
It is always advisable to avoid such keywords are object identifiers.
Cheers
Andreas
On Sat, 2024-02-17 at 00:25 +, 'Peter Borissow' via H2 Database
wrote:
> Dear H2 Community,
> I ran into an unexpected error today migrating from 1.x to 2.x.
> I'm using H2 2.2.224 in PostgreSQL mode using the following
> parameters
>
> properties.setProperty("MODE", "PostgreSQL");
> properties.setProperty("DATABASE_TO_LOWER", "TRUE");
> properties.setProperty("DEFAULT_NULL_ORDERING", "HIGH");
>
> The following query is failing:
>
> select year(date) as year from transaction
>
> Error:
>
> SELECT year(date) AS [*]year FROM transaction"; expected "identifier"
>
> Looks like it doesn't like the year alias in the select statement.
> The following query works:
>
> select year(date) as y from transaction
>
> I tried the following but it didn't seem to help:
>
> properties.setProperty("NON_KEYWORDS", "YEAR");
>
> Any suggestions?
>
> Thanks,
> Peter
> --
> 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/1040186991.2923444.1708129516544%40mail.yahoo.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/83c98a852da9a0ef3683051984c612304744c66c.camel%40manticore-projects.com.
