Column lookup for tables is not escaping wildcards

[exaSR]

(again tested using exasol dialect, but is jdbc standard problem)

setup

create schema VSA;
CREATE JAVA ADAPTER SCRIPT VSA.EXASOL_ADAPTER AS
    %scriptclass com.exasol.adapter.RequestDispatcher;
    %jar /buckets/bfsdefault/default/virtual-schema-dist-10.1.0-exasol-7.0.2.jar;
/


create or replace connection LOCAL_JDBC
to 'jdbc:exa:localhost/<fingerprint>:8563'
user 'sys' identified by 'secret';


create schema mixup;
create table HELLO_WORLD(i int);
create table "HELLOxWORLD"(x int);


CREATE VIRTUAL SCHEMA mixed_up
USING VSA.EXASOL_ADAPTER WITH
    CONNECTION_NAME = 'LOCAL_JDBC'
    SCHEMA_NAME     = 'MIXUP'
;

Bug: Column lookup passes 'HELLO_WORLD' to jdbc's getColumns() call, which is interpreted as SQL wildcard

describe mixed_up."HELLO_WORLD";

returns

COLUMN_NAME SQL_TYPE      
----------- ------------- 
I           DECIMAL(18,0) 
X           DECIMAL(18,0) 

Of course, column X is not really there and any access will result in errors.

On top of that, neither the VS adapter nor the database backend checks for duplicate column names...

[ckunki]

Hello @exaSR @redcatbear and me have the same assumption:

• VSCJDBC asks JDBC driver for column metadata and provides the table name, potentially containing wildcards
• Method getColumns() in JDBC driver accepts wildcards and simply returns all matching tables or all columns of all matching tables

Proposed change

• update VSCJDBC to escape potential wildcards before asking the JDBC driver

[ckunki]

I will add a test in VSEXA, see exasol/exasol-virtual-schema#94