1. Taro L. Saito
  2. sqlite-jdbc
  3. Issues
Issue #68 resolved

DatabaseMetaData.getTables() erroneously returns INDEX, TRIGGER, and other non-table objects

Lukas Eder
created an issue

I'm running jOOQ integration tests against the Xerial driver and I've found a couple of bugs, which I'm reporting here. I have detected these bugs with the downloaded version 3.7.15-SNAPSHOT-2.

Run this Java program against an existing database:

Connection c = getConnection();
DatabaseMetaData dbMeta = c.getMetaData();
ResultSet tables = dbMeta.getTables(null, null, "%", null);
while (tables.next()) {
    ResultSet rs = dbMeta.getColumns(
        null, null, tables.getString("TABLE_NAME"), "%");
    ResultSetMetaData rsMeta = rs.getMetaData();

    System.out.println(
        tables.getString("TABLE_NAME") + ", " +
        tables.getString("TABLE_TYPE") + ", " +
        rsMeta.getColumnCount());
}

The above returns indexes, triggers, and all sorts of non-table objects on the getTables() call. An example:

t_booleans, TABLE, 22
sqlite_autoindex_t_booleans_1, INDEX, 1
t_triggers, TABLE, 22
t_triggers_trigger, TRIGGER, 1

As a call to getTables() is expected to return tables only, it is then surprising when the returned table name cannot be used in getColumns(), which erroneously returns a single column and no record (see also https://bitbucket.org/xerial/sqlite-jdbc/issue/67)