Correctly map INTERVAL and GEOMETRY types

[kaklakariada]

This is related to issues #60, #66 and #69.

In #69 we already fixed the mapping of HASHTYPE columns so that they are reported with their real type and length. This is however not possible with INTERVAL and GEOMETRY types as they require changes to the JDBC driver that allow reconstructing the original type from a JDBC ResultSet. Once the internal ticket 13982 is implemented, we can start working on this.

[redcatbear]

@kaklakariada, is this only about the result set information? If that is the case, the upcoming VS interface will include the expected types, so that we can solve this.

[ckunki]

See also

• https://github.com/exasol/virtual-schema-common-java/issues/249
• https://github.com/exasol/virtual-schema-common-jdbc/issues/120

[ckunki]

Empiric results with Exasol database version 7.1.11

Using test AbstractExasolSqlDialectIT.testNonDefaultGeometry()

• ExasolSqlDialectExaConnectionIT generates the following import statement

  IMPORT FROM EXA AT "EXA_CONNECTION" STATEMENT
  'SELECT "SINGLE_COLUMN_TABLE_GEOMETRY4321"."C1"
  FROM   "SOURCE_SCHEMA"."SINGLE_COLUMN_TABLE_GEOMETRY4321"'

• While ExasolSqlDialectJdbcConnectionIT currently uses data type VARCHAR generating the following import statement

  IMPORT INTO (c1 VARCHAR(40) UTF8) FROM JDBC AT JDBC_CONNECTION STATEMENT
  'SELECT TYPEOF("SINGLE_COLUMN_TABLE_GEOMETRY4321"."C1")
  FROM   "SOURCE_SCHEMA"."SINGLE_COLUMN_TABLE_GEOMETRY4321"';

  As JDBC does not support data type GEOMETRY, using data type VARCHAR is the only option here. Note that Exasol Importer ignores the data type specification and internally replaces it by GEOMETRY again. 😃

The following import statement would also be correct:

IMPORT FROM JDBC AT JDBC_CONNECTION STATEMENT
  'SELECT TYPEOF("SINGLE_COLUMN_TABLE_GEOMETRY4321"."C1")
   FROM   "SOURCE_SCHEMA"."SINGLE_COLUMN_TABLE_GEOMETRY4321"';

[ckunki]

testSelectStarConvertedToColumnsListJoinSameTable

org.opentest4j.AssertionFailedError: Unable to run assertion query: 'SELECT * FROM TL JOIN TL AS TL_2 ON TL.L1 = TL_2.L1 ORDER BY TL.L1'
    at com.exasol.adapter.dialects.exasol.AbstractExasolSqlDialectIT.assertVsQuery(AbstractExasolSqlDialectIT.java:381)
    at com.exasol.adapter.dialects.exasol.AbstractExasolSqlDialectIT.testSelectStarConvertedToColumnsListJoinSameTable(AbstractExasolSqlDialectIT.java:656)
Caused by: java.sql.SQLException: Error executing 'SELECT * FROM TL JOIN TL AS TL_2 ON TL.L1 = TL_2.L1 ORDER BY TL.L1': Adapter generated invalid pushdown query for virtual table TL: Expected number of columns is 4 but pushdown query has 2. Try refreshing the Virtual Schema (pushdown query: IMPORT INTO (c1 VARCHAR(5) UTF8, c2 VARCHAR(5) UTF8) FROM JDBC AT JDBC_CONNECTION STATEMENT 'SELECT "TL"."L1", "TL"."L2", "TL_2"."L1", "TL_2"."L2" FROM "SOURCE_SCHEMA"."TL" INNER JOIN "SOURCE_SCHEMA"."TL" "TL_2" ON "TL"."L1" = "TL_2"."L1" ORDER BY "TL"."L1"') (Session: 1744050353092296704)
    at com.exasol.adapter.dialects.exasol.AbstractExasolSqlDialectIT.query(AbstractExasolSqlDialectIT.java:203)
    at com.exasol.adapter.dialects.exasol.AbstractExasolSqlDialectIT.assertVsQuery(AbstractExasolSqlDialectIT.java:379)
    ... 70 more
Caused by: java.sql.SQLException: Adapter generated invalid pushdown query for virtual table TL: Expected number of columns is 4 but pushdown query has 2. Try refreshing the Virtual Schema (pushdown query: IMPORT INTO (c1 VARCHAR(5) UTF8, c2 VARCHAR(5) UTF8) FROM JDBC AT JDBC_CONNECTION STATEMENT 'SELECT "TL"."L1", "TL"."L2", "TL_2"."L1", "TL_2"."L2" FROM "SOURCE_SCHEMA"."TL" INNER JOIN "SOURCE_SCHEMA"."TL" "TL_2" ON "TL"."L1" = "TL_2"."L1" ORDER BY "TL"."L1"') (Session: 1744050353092296704)
    at com.exasol.jdbc.ExceptionFactory.createSQLException(ExceptionFactory.java:258)
    at com.exasol.jdbc.EXASQLException.getSQLExceptionIntern(EXASQLException.java:50)
    at com.exasol.jdbc.AbstractEXAStatement.execute(AbstractEXAStatement.java:478)
    at com.exasol.jdbc.EXAStatement.execute(EXAStatement.java:289)
    at com.exasol.jdbc.AbstractEXAStatement.executeQuery(AbstractEXAStatement.java:614)
    at com.exasol.adapter.dialects.exasol.AbstractExasolSqlDialectIT.query(AbstractExasolSqlDialectIT.java:201)
    ... 71 more

testSelectStarConvertedToColumnsListJoinSameTableReversed

org.opentest4j.AssertionFailedError: Unable to run assertion query: 'SELECT * FROM TL AS TL_2 JOIN TL ON TL_2.L1 = TL.L1 ORDER BY TL_2.L1'
    at com.exasol.adapter.dialects.exasol.AbstractExasolSqlDialectIT.assertVsQuery(AbstractExasolSqlDialectIT.java:381)
    at com.exasol.adapter.dialects.exasol.AbstractExasolSqlDialectIT.testSelectStarConvertedToColumnsListJoinSameTableReversed(AbstractExasolSqlDialectIT.java:669)
Caused by: java.sql.SQLException: Error executing 'SELECT * FROM TL AS TL_2 JOIN TL ON TL_2.L1 = TL.L1 ORDER BY TL_2.L1': Adapter generated invalid pushdown query for virtual table TL_2: Expected number of columns is 4 but pushdown query has 2. Try refreshing the Virtual Schema (pushdown query: IMPORT INTO (c1 VARCHAR(5) UTF8, c2 VARCHAR(5) UTF8) FROM JDBC AT JDBC_CONNECTION STATEMENT 'SELECT "TL_2"."L1", "TL_2"."L2", "TL"."L1", "TL"."L2" FROM "SOURCE_SCHEMA"."TL" "TL_2" INNER JOIN "SOURCE_SCHEMA"."TL" ON "TL_2"."L1" = "TL"."L1" ORDER BY "TL_2"."L1"') (Session: 1744050353092296704)
    at com.exasol.adapter.dialects.exasol.AbstractExasolSqlDialectIT.query(AbstractExasolSqlDialectIT.java:203)
    at com.exasol.adapter.dialects.exasol.AbstractExasolSqlDialectIT.assertVsQuery(AbstractExasolSqlDialectIT.java:379)
    ... 70 more
Caused by: java.sql.SQLException: Adapter generated invalid pushdown query for virtual table TL_2: Expected number of columns is 4 but pushdown query has 2. Try refreshing the Virtual Schema (pushdown query: IMPORT INTO (c1 VARCHAR(5) UTF8, c2 VARCHAR(5) UTF8) FROM JDBC AT JDBC_CONNECTION STATEMENT 'SELECT "TL_2"."L1", "TL_2"."L2", "TL"."L1", "TL"."L2" FROM "SOURCE_SCHEMA"."TL" "TL_2" INNER JOIN "SOURCE_SCHEMA"."TL" ON "TL_2"."L1" = "TL"."L1" ORDER BY "TL_2"."L1"') (Session: 1744050353092296704)
    at com.exasol.jdbc.ExceptionFactory.createSQLException(ExceptionFactory.java:258)
    at com.exasol.jdbc.EXASQLException.getSQLExceptionIntern(EXASQLException.java:50)
    at com.exasol.jdbc.AbstractEXAStatement.execute(AbstractEXAStatement.java:478)
    at com.exasol.jdbc.EXAStatement.execute(EXAStatement.java:289)
    at com.exasol.jdbc.AbstractEXAStatement.executeQuery(AbstractEXAStatement.java:614)
    at com.exasol.adapter.dialects.exasol.AbstractExasolSqlDialectIT.query(AbstractExasolSqlDialectIT.java:201)
    ... 71 more

[ckunki]

Actual table

CREATE TABLE "TL" ("L1", "VARCHAR(5)", "L2", "VARCHAR(5)");

Select * statement on a join of table "TL" with itself expected to return 4 columns in total:

SELECT * FROM TL JOIN TL AS TL_2 ON TL.L1 = TL_2.L1 ORDER BY TL.L1

Generated pushdown query:

IMPORT INTO (c1 VARCHAR(5) UTF8, c2 VARCHAR(5) UTF8)
FROM JDBC AT JDBC_CONNECTION STATEMENT
'SELECT     "TL"."L1", "TL"."L2", "TL_2"."L1", "TL_2"."L2"
 FROM       "SOURCE_SCHEMA"."TL"
 INNER JOIN "SOURCE_SCHEMA"."TL" "TL_2"
 ON         "TL"."L1" = "TL_2"."L1" ORDER BY "TL"."L1"
')

JSON in pushdown request contains only two columns

"selectListDataTypes" : [ {
    "size" : 5,
    "type" : "VARCHAR"
  },
  {
    "size" : 5,
    "type" : "VARCHAR"
  } ],
"type" : "select"