With linked table to postgreSQL, case-sensivite column names not respected in where part

[arcadmlafon]

Hi, (Thank for your efforts to maintain this great tool)

We have a problem with a linked table to a PostgreSQL database which contains some case sensitive columns. Its look like, if we make a select on the linked table the column are not correctly send in the "where", the double quotes are removed.

We use tha H2 version 2.1.210
and the PostgreSQL version 14.1, compiled by Visual C++ build 1914, 64-bit

What ever H2 compatibility mode used (STRICT or POSTGRESQL) the error raises.

Here is an example to reproduce this problem, in the PostgrSQL database there this table:

CREATE TABLE "Source"."Employees" (
"EmployeeId" int4 NULL,
"EmployeeName" varchar NULL,
"CITIES" varchar NULL,
country varchar NULL
);

In the H2 database we create the corresponding linked table like this:

CREATE GLOBAL TEMPORARY LINKED TABLE IF NOT EXISTS
   "Source".LT('org.postgresql.Driver','jdbc:postgresql://localhost:5432/extract','postgres','pwd','"Source"','"Source"."Employees"')

Can not query the linked table with a clause WHERE on mixed case columns, H2 rebuilds the query and removes the double quotes whatever the H2 compatibility mode:

select "EmployeeName" from "Source".LT where "EmployeeName" = 'Nina' 

throw the following error:

org.h2.jdbc.JdbcSQLNonTransientException: Error accessing linked table with SQL statement "SELECT * FROM ""Source"".""Employees"" T WHERE EmployeeName>=? AND EmployeeName<=?", cause: "org.postgresql.util.PSQLException: ERREUR: la colonne « employeename » n'existe pas\000a Indice\00a0: Peut-être que vous souhaitiez référencer la colonne « t.EmployeeName ».\000a Position\00a0: 44";
SQL statement:
select * from "Source".LT where "EmployeeName" = 'Nina' [90111-210]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:573)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:496)
at org.h2.message.DbException.get(DbException.java:216)
at org.h2.table.TableLink.wrapException(TableLink.java:495)
at org.h2.index.LinkedIndex.find(LinkedIndex.java:132)
at org.h2.index.IndexCursor.find(IndexCursor.java:161)
at org.h2.table.TableFilter.next(TableFilter.java:394)
at org.h2.command.query.Select$LazyResultQueryFlat.fetchNextRow(Select.java:1825)
at org.h2.result.LazyResult.hasNext(LazyResult.java:78)
at org.h2.result.FetchedResult.next(FetchedResult.java:34)
at org.h2.command.query.Select.queryFlat(Select.java:728)
at org.h2.command.query.Select.queryWithoutCache(Select.java:833)
at org.h2.command.query.Query.queryWithoutCacheLazyCheck(Query.java:197)
at org.h2.command.query.Query.query(Query.java:494)
at org.h2.command.query.Query.query(Query.java:457)
at org.h2.command.CommandContainer.query(CommandContainer.java:256)
at org.h2.command.Command.executeQuery(Command.java:190)
at org.h2.server.TcpServerThread.process(TcpServerThread.java:353)
at org.h2.server.TcpServerThread.run(TcpServerThread.java:191)
at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: org.postgresql.util.PSQLException: ERREUR: la colonne « employeename » n'existe pas
Indice : Peut-être que vous souhaitiez référencer la colonne « t.EmployeeName ».
Position : 44
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:153)
at org.h2.table.TableLink.execute(TableLink.java:552)
at org.h2.index.LinkedIndex.find(LinkedIndex.java:128)
... 15 more
at org.h2.message.DbException.getJdbcSQLException(DbException.java:573)
at org.h2.engine.SessionRemote.readException(SessionRemote.java:637)
at org.h2.engine.SessionRemote.done(SessionRemote.java:606)
at org.h2.command.CommandRemote.executeQuery(CommandRemote.java:171)
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:247)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:223)
at org.h2.server.web.WebApp.getResult(WebApp.java:1339)
at org.h2.server.web.WebApp.query(WebApp.java:1137)
at org.h2.server.web.WebApp$1.next(WebApp.java:1103)
at org.h2.server.web.WebApp$1.next(WebApp.java:1090)
at org.h2.server.web.WebThread.process(WebThread.java:189)
at org.h2.server.web.WebThread.run(WebThread.java:102)
at java.base/java.lang.Thread.run(Thread.java:833)

[katzyn]

There is a problem. Some database systems don't follow the SQL Standard and have own rules for identifiers. Standard quoted identifiers may not work, for example, they don't work in MySQL and its forks. Statement.enquoteIdentifier() method was added only in the latest version of JDBC, many drivers don't have its specialized implementation and we can't rely on it.

Reliability of old DatabaseMetaData.getIdentifierQuoteString() is also questionable, what if some system supports only [identifier]? Recent versions of SQL Server support both bracketed and standard identifiers and its driver returns ", so this problem can be not that important. But how some different character returned by some other driver is escaped is also an open question.

[Shabin]

Because of this change, update query of my linked postgres table is always failing.
For a query update USER_LINKED set USERNAME='changedUser' where ID = 12, I am getting the below error since the ID is getting replaced with "ID"

org.h2.jdbc.JdbcSQLNonTransientException: Error accessing linked table with SQL statement "SELECT * FROM USER_LINKED T WHERE ""ID"">=? AND ""ID""<=?", cause: "org.postgresql.util.PSQLException: ERROR: column ""ID"" does not exist\000a  Position: 42"; SQL statement:
update LW_OPS_FLT_LEG_DTL_E set change_username='another' where id = 1122222 [90111-214]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:554)
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:477)
	at org.h2.message.DbException.get(DbException.java:212)
	at org.h2.table.TableLink.wrapException(TableLink.java:497)
	at org.h2.index.LinkedIndex.find(LinkedIndex.java:132)
	at org.h2.index.IndexCursor.find(IndexCursor.java:161)
	at org.h2.table.TableFilter.next(TableFilter.java:394)
	at org.h2.command.dml.FilteredDataChangeStatement.nextRow(FilteredDataChangeStatement.java:71)
	at org.h2.command.dml.Update.update(Update.java:73)

The getIdentifierQuoteString of PgDatabaseMetaData always returns "\"". So now I am not able to update the linked table. Anyway to fix this issue?