Reading column metadata from existing connection returns inconsistent result

Detail name	Value
Changelog Number	17523
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.1.0, Exasol 8.0.0
Fix Versions	Exasol 7.1.23, Exasol 8.23.0
Resolution Date	2023-09-11

Description

A JDBC connection object shows outdated column data if the following conditions are met:

• The column meta data has been read before.
• The session changes the columns on an existing table.

Preparation

conn1.createStatement().execute("CREATE SCHEMA testing" );
conn1.createStatement().execute("CREATE TABLE testing.tab (a varchar(5))");

Example

ResultSet resultSetTmp = conn1.getMetaData().getColumns(null, "TESTING", "TAB", null);
conn1.createStatement().execute("ALTER TABLE testing.tab ADD (b DECIMAL(4))");
conn1.commit();
List<String> columns = new ArrayList<>();
try (ResultSet resultSet = conn1.getMetaData().getColumns(null, "TESTING", "TAB", null)) {
    while (resultSet.next()) {
        columns.add(resultSet.getString("COLUMN_NAME"));
    }
}
// The metadata should contain column "B"
if (!columns.contains("B"))
{
    throw new RuntimeException("Column B not found");
}

Workaround

Trigger a meta data refresh with an ALTER SYSTEM or CREATE TABLE statement.

ResultSet resultSetTmp = conn1.getMetaData().getColumns(null, "TESTING", "TAB", null);
conn1.createStatement().execute("ALTER TABLE testing.tab ADD (b DECIMAL(4))");
conn1.createStatement().execute("CREATE TABLE Tmp (a int);");
conn1.createStatement().execute("DROP TABLE Tmp;");
conn1.commit();
List<String> columns = new ArrayList<>();
try (ResultSet resultSet = conn1.getMetaData().getColumns(null, "TESTING", "TAB", null)) {
    while (resultSet.next()) {
        columns.add(resultSet.getString("COLUMN_NAME"));
    }
}
// The metadata should contain column "B"
if (!columns.contains("B"))
{
    throw new RuntimeException("Column B not found");
}

Fix

The column meta data is never outdated.