Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Correctly map INTERVAL and GEOMETRY types #71

Closed
kaklakariada opened this issue Feb 3, 2022 · 5 comments · Fixed by #81
Closed

Correctly map INTERVAL and GEOMETRY types #71

kaklakariada opened this issue Feb 3, 2022 · 5 comments · Fixed by #81
Labels
blocked:yes Currently blocked by another ticket bug Unwanted / harmful behavior

Comments

@kaklakariada
Copy link
Collaborator

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.
@kaklakariada kaklakariada mentioned this issue Feb 3, 2022
Closed
@kaklakariada kaklakariada added bug Unwanted / harmful behavior source:exasol labels Feb 3, 2022
@redcatbear redcatbear added the blocked:yes Currently blocked by another ticket label Feb 18, 2022
@redcatbear
Copy link
Collaborator

@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
Copy link
Contributor

ckunki commented Sep 12, 2022

See also

@ckunki ckunki mentioned this issue Sep 12, 2022
Closed
ckunki added a commit that referenced this issue Sep 12, 2022
@ckunki
#71: Correctly map INTERVAL and GEOMETRY types
1875e4b
ckunki added a commit that referenced this issue Sep 13, 2022
@ckunki
#71: Correctly map INTERVAL and GEOMETRY types
cf00bbd 
updated PK and dependencies to vsc-jdbc
fixed test setup
enabled fingerprint for major version > 7
removed obsolete test after fixing vsc-jdbc/#119
@ckunki ckunki mentioned this issue Sep 13, 2022
Merged
@ckunki
Copy link
Contributor

ckunki commented Sep 14, 2022

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
Copy link
Contributor

ckunki commented Sep 15, 2022

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
Copy link
Contributor

ckunki commented Sep 16, 2022 •
edited
Loading

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"

ckunki added a commit that referenced this issue Sep 22, 2022
@ckunki
#71: Correctly map INTERVAL and GEOMETRY types (#81)
2553b0e 
* #71: Correctly map INTERVAL and GEOMETRY types
Co-authored-by: Christoph Pirkl <[email protected]>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
blocked:yes Currently blocked by another ticket bug Unwanted / harmful behavior
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

#71: Correctly map INTERVAL and GEOMETRY types exasol/exasol-virtual-schema
3 participants
@redcatbear @kaklakariada @ckunki