Exception: Table has no columns, if view is outdated

[tkilias]

Situation:

If you use the exasol-virtual-schema with a remote Exasol Database via JDBC you can get an Exception that a table has no columns. However, the table is actually a view and this view is for some reason outdated. This usually happens, if the dependencies of the view get changed after the view was created.

Details:

• You can update the view with describe <viewname>
• And you can see the OUTDATED status in the SYS.EXA_ALL_COLUMNS table in the STATUS column.

[redcatbear]

When is this exception observed? During push-down or create? In case of push-down, I think it is the responsibility of the source database owner to make sure that views are up-to-date before they are used. We could in theory add a run-time check in the push-down, but that check would be very expensive.

[tkilias]

I understood it the way, that it happens during create or refresh in the metadata query for columns of the views. It failed for more than 5000 views after each other.

[redcatbear]

Okay, as strange as this sounds, that is good news. We can afford more runtime checks during CREATE and REFRESH. Those requests are a lot less time-critical.

[chiaradiamarcelo]

This is actually not a bug, and it's working as intended. Virtual Schemas does not support tables/views with no columns, the view should be updated and maintained by the remote database.

The current error message is not ideal in this case, we need to provide a clearer error message with clear mitigations.

[jakobbraun]

An example error messages looks like:

22002] VM error: java.lang.Exception: Unexpected error in adapter: Exception for table AUFTRAGSDATEN Stacktrace: java.lang.RuntimeException: Exception for table AUFTRAGSDATEN at com.exasol.adapter.jdbc.JdbcMetadataReader.findTables(JdbcMetadataReader.java:279)
 at com.exasol.adapter.jdbc.JdbcMetadataReader.readRemoteMetadata(JdbcMetadataReader.java:58) at com.exasol.adapter.jdbc.JdbcAdapter.readMetadata(JdbcAdapter.java:110) at com.exasol.adapter.jdbc.JdbcAdapter.readMetadata(JdbcAdapter.java:102) at com.exasol.adapter.jdbc.JdbcAdapter.handleRefresh(JdbcAdapter.java:128)
 at com.exasol.adapter.jdbc.JdbcAdapter.adapterCall(JdbcAdapter.java:68) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at
 java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:566) at com.exasol.ExaWrapper.runSingleCall(ExaWrapper.java:95) Caused by: com.exasol.adapter.metadata.MetadataException:
 A table without columns was encountered: AUFTRAGSDATEN. This is not supported. Please check if this table has columns. If the table does have columns, the dialect probably does not properly handle the data types of the columns. at com.exasol.adapter.metadata.TableMetadata.(TableMetadata.java:23)
 at com.exasol.adapter.jdbc.JdbcMetadataReader.findTables(JdbcMetadataReader.java:277) ... 10 more For following request: { "schemaMetadataInfo" : { "name" : "AUFTRAGSDATEN", "properties" : { "CONNECTION_NAME" : "AUFTRAGSDATEN",
 "EXA_CONNECTION_STRING" : "AUFTRAGSDATEN", "IMPORT_FROM_EXA" : "true", "SCHEMA_NAME" : "AUFTRAGSDATEN", "SQL_DIALECT" : "EXASOL" } }, "type" : "refresh" } Response: Stack trace: com.exasol.adapter.jdbc.JdbcAdapter.adapterCall(JdbcAdapter.java:90)
 java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 java.base/java.lang.reflect.Method.invoke(Method.java:566) com.exasol.ExaWrapper.runSingleCall(ExaWrapper.java:95) (Session: 1690147644996459941)

[jakobbraun]

So basically the main issue is that the stack traceis not very readable. Let's move this discussion to a new issue: https://github.com/exasol/virtual-schema-common-java/issues/218

[jakobbraun]

Left for this ticket: change exception message:

 A table without columns was encountered: AUFTRAGSDATEN. This is not supported. Please check if this table has columns. If the table does have columns, the dialect probably does not properly handle the data types of the columns.

[AnastasiiaSergienko]

This message should be added in the common-jdbc, so I'm moving this ticket.

[jakobbraun]

I could not find this error message in the current version.

[jakobbraun]

Current error message for this error:

com.exasol.adapter.jdbc.RemoteMetadataReaderException: E-VS-COM-JDBC-30: Unable to read remote metadata for push-down query trying to generate result column description. Please, make sure that you provided valid CATALOG_NAME and SCHEMA_NAME properties if required. Caused by: 'object "TABLE_THAT_WILL_CHANGE"."I" not found [line 1, column 8] (Session: 1691500247300112384)'
Caused by: java.sql.SQLException: object "TABLE_THAT_WILL_CHANGE"."I" not found [line 1, column 8] (Session: 1691500247300112384)

(that's the only thing I could reproduce until now)

[jakobbraun]

Here is a test case to reproduce this case:

 @Test
    void testVsTableOnOutdatedView() throws SQLException {
        final Table table = this.sourceSchema.createTable("TABLE_THAT_WILL_CHANGE", "I", "INTEGER", "J", "INTEGER");
        connection.createStatement().executeUpdate("CREATE FORCE VIEW " + this.sourceSchema.getFullyQualifiedName()
                + ".EMPTY_VIEW AS SELECT * FROM " + table.getFullyQualifiedName());
        this.virtualSchema = createVirtualSchema(this.sourceSchema);
    }

The current behavior is, that no virtual schema table is created, since the outdated view, created by CREATE FORCE VIEW has no columns.

In the Virtual Schema remote logging output it shows:

2021-02-15 10:27:40.382 FINE    [c.e.a.j.BaseTableMetadataReader] Not mapping table "EMPTY_VIEW" because it has no columns. This can happen if the view containing the columns is invalid or if the Virtual Schema adapter does not support mapping the column types.

We agreed that this is acceptable behavior and we will add an FAQ entry for this.