Broken handling of tables when SCHEMA_NAME property is not set

[exaSR]

Tested this using exasol-virtual-schema, but I assume this is a problem in the jdbc implementation already...

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 user BOB identified by "secret";
grant CREATE SESSION to BOB;

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

create schema s1;
    create table FOR_BOB(i int);
    grant select on for_bob to bob;

create schema s2;
    create table FOR_BOB(j int);
    grant select on for_bob to bob;

create virtual schema nomercy
    using VSA.EXASOL_ADAPTER WITH
    CONNECTION_NAME = 'LOCAL_BOB'
    -- no SCHEMA_NAME filter !
;

Bug 1: Metadata for tables with same name is just mashed together

describe nomercy.FOR_BOB;

results in

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

Bug 2: Generated SQL is not schema-qualified

select * from nomercy.FOR_BOB;

results in

ETL-5402: JDBC-Client-Error: Failed to initialize Query: object "FOR_BOB" not found

because:

explain virtual select i from nomercy.FOR_BOB;
-- IMPORT INTO (c1 DECIMAL(18, 0)) FROM JDBC AT LOCAL_BOB STATEMENT 'SELECT "FOR_BOB"."I" FROM "FOR_BOB"'

[ckunki]

@redcatbear and @ckunki propose to update the VSEXA to

• make property SCHEMA_NAME mandatory.
• report an error if the virtual schema is specified without property SCHEMA_NAME

Rationale: Whether the property SCHEMA_NAME is mandatory or not depends on the specific dialect of JDBC-based virtual schema.

I therefore moved the issue from VSCJDBC to the current repo.