H2 and SQL quoted identifiers

Description

We have a legacy database with column names that use reserved keywords. For example:

Table named "sample" with columns:
id,
offset,
value

The names of these columns appear not to be an issue when using hibernate with H2 1.3.175. No quoted needed. However, starting with H2 1.4.177 it appears that H2 needs these column that have reserved keywords as the name to be quoted.

I have two problems trying to use hibernate with quoted identifiers with any version of H2.

1. H2 uses case-insensitive for unquoted identifiers but uses case-sensitive when identifiers are quoted.
For example see the result in comments when I run the following queries against H2 using SQuirrel SQL:

select id, offset, value from sample
– successful: returns result set

select id, OFFSET, VALUE from sample
– successful: returns result set

select id, Offset, Value from sample
– successful: returns result set

select id, "offset", "value" from sample
– Error: Column "offset" not found; SQL statement:
– select standard_condition_id, "offset", "value" from condition_standard [42122-172]
– SQLState: 42S22
– ErrorCode: 42122

select id, "Offset", "Value" from sample
– Error: Column "offset" not found; SQL statement:
– select standard_condition_id, "offset", "value" from condition_standard [42122-172]
– SQLState: 42S22
– ErrorCode: 42122

select id, "OFFSET", "VALUE" from sample
– successful: returns result set

Since my column names are lower case only the lower case quoted identifiers execute successfully.

2. When using hibernate setting "<prop key="hibernate.auto_quote_keyword">true</prop>" it recognizes that "value" column needs to be quoted but does not recognized that "offset" needs to be quoted. For this my workaround is to explicitly tell hibernate which fields to quote specifying that it needs to be quoted in the @Column(name = "\"offset\""). However, I ran into the issue in number 1. It can not see anything to tell hibernate which case to use for quoted identifier column names.

Environment

Using Java 8 (also tried Java 11), hibernate 5.4.12.Final (also tried 5.4.22.Final), H2 1.3.175 with page store and H2 compatibility mode (also tried versions up to 1.4.200), OS macOS Catalina...

Assignee

Unassigned

Reporter

Richard Conover

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Priority

Blocker
Configure