Wrong case with linked table to postgresql #4091
Description
Hello,
(First of all, thanks for this amazing project <3)
I've seen an issue with the case of the linked table columns: the columns are upper cased in some cases (probably related to #3448)
- It seems that the columns is converted to upper case in this method:
- Then the upper case column is quoted and used when building the SQL query to the remote database : we only have the issue when H2 add WHERE clauses to the SQL query
The version 2.1.210 was ok. I've seen the issue when upgrading to 2.2.224 until the latest version
Here's a project I created to reproduce the issue: h2-linked-tables.tar.gz with the version 2.2.224 (I've reproduced also with 2.3.230)
INFO [ main] : PostgreSQL table: CREATE TABLE public.newtable (
id int8 NOT NULL,
"col_quote_lower" varchar,
"COL_QUOTE_UPPER" varchar,
"COL_quote_mixed" varchar,
col_noquote_lower varchar,
COL_noQUOTE_UPPER varchar,
COL_noquote_mixed varchar,
CONSTRAINT newtable_pk PRIMARY KEY (id)
);
INFO [ main] : Create the linked table: CREATE LINKED TABLE THE_LINKED_TABLE('org.postgresql.Driver', 'jdbc:postgresql://localhost/postgres', 'postgres', 'password', 'newtable');
INFO [ main] : List of columns: SHOW COLUMNS FROM THE_LINKED_TABLE
INFO [ main] : Linked table columns
FIELD | TYPE | NULL | KEY | DEFAULT |
------------------------------------------------------------------------
ID | BIGINT | YES | | NULL |
COL_QUOTE_LOWER | CHARACTER VARYING | YES | | NULL |
COL_QUOTE_UPPER | CHARACTER VARYING | YES | | NULL |
COL_quote_mixed | CHARACTER VARYING | YES | | NULL |
COL_NOQUOTE_LOWER | CHARACTER VARYING | YES | | NULL |
COL_NOQUOTE_UPPER | CHARACTER VARYING | YES | | NULL |
COL_NOQUOTE_MIXED | CHARACTER VARYING | YES | | NULL |
INFO [ main] : Get the linked table content: SELECT *
FROM THE_LINKED_TABLE
WHERE
COL_QUOTE_LOWER = ''
AND COL_QUOTE_UPPER = ''
AND "COL_quote_mixed" = ''
AND COL_NOQUOTE_LOWER = ''
AND COL_NOQUOTE_UPPER = ''
AND COL_NOQUOTE_MIXED = ''
;
Exception in thread "main" java.lang.IllegalStateException: program error
at Main.main(Main.java:64)
Caused by: org.h2.jdbc.JdbcSQLNonTransientException: Error accessing linked table with SQL statement "SELECT * FROM public.newtable T WHERE ""COL_QUOTE_LOWER"">=? AND ""COL_QUOTE_LOWER""<=?", cause: "org.postgresql.util.PSQLException: ERROR: column ""COL_QUOTE_LOWER"" does not exist\000a Hint: Perhaps you meant to reference the column ""t.COL_QUOTE_UPPER"".\000a Position: 39"; SQL statement:
SELECT *
FROM THE_LINKED_TABLE
WHERE
COL_QUOTE_LOWER = ''
AND COL_QUOTE_UPPER = ''
AND "COL_quote_mixed" = ''
AND COL_NOQUOTE_LOWER = ''
AND COL_NOQUOTE_UPPER = ''
AND COL_NOQUOTE_MIXED = ''
[90111-224]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:566)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:489)
at org.h2.message.DbException.get(DbException.java:212)
at org.h2.table.TableLink.wrapException(TableLink.java:499)
at org.h2.index.LinkedIndex.find(LinkedIndex.java:132)
at org.h2.index.IndexCursor.find(IndexCursor.java:166)
at org.h2.table.TableFilter.next(TableFilter.java:394)
at org.h2.command.query.Select$LazyResultQueryFlat.fetchNextRow(Select.java:1843)
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:520)
at org.h2.command.query.Query.query(Query.java:483)
at org.h2.command.CommandContainer.query(CommandContainer.java:252)
at org.h2.command.Command.executeQuery(Command.java:192)
at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:131)
at Main.main(Main.java:61)
Caused by: org.postgresql.util.PSQLException: ERROR: column "COL_QUOTE_LOWER" does not exist
Hint: Perhaps you meant to reference the column "t.COL_QUOTE_UPPER".
Position: 39
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:177)
at org.h2.table.TableLink.execute(TableLink.java:556)
at org.h2.index.LinkedIndex.find(LinkedIndex.java:128)
... 14 more
Process finished with exit code 1
If you give me some insights about the way you want to handle this issue, I can submit a pull request