Loading ...
Sorry, an error occurred while loading the content.
 

DataBaseMetada.getIndexInfo() does not return all indexes

Expand Messages
  • Thomas
    Hi, consider the following table: CREATE TABLE T (caption varchar(50)); CREATE INDEX idx_capt_upper ON t computed by (upper(caption)); When calling
    Message 1 of 3 , Jan 13, 2012
      Hi,

      consider the following table:

      CREATE TABLE T (caption varchar(50));
      CREATE INDEX idx_capt_upper ON t computed by (upper(caption));

      When calling

      conection.getMetaData().getIndexInfo(null, null, "T", true, false);

      the result set will be empty.

      I had a look at the driver source and the statement that is being used to return all indizes does an inner join on rdb$indices and rdb$index_segments

      An index with "computed by" does not seem to have a row in rdb$index_segments and the join fails.

      When changing the statement to use an outer join, the above index will be returned.

      Of course the NULL values for rdb$field_position and rdb$field_name from rdb$index_segments needs to be taken into account.
    • Mark Rotteveel
      ... It looks like you are right, I created a tracker ticket: http://tracker.firebirdsql.org/browse/JDBC-228 As I am currently working on the DatabaseMetaData
      Message 2 of 3 , Jan 14, 2012
        On 14-1-2012 1:09, Thomas wrote:
        > Hi,
        >
        > consider the following table:
        >
        > CREATE TABLE T (caption varchar(50));
        > CREATE INDEX idx_capt_upper ON t computed by (upper(caption));
        >
        > When calling
        >
        > conection.getMetaData().getIndexInfo(null, null, "T", true, false);
        >
        > the result set will be empty.
        >
        > I had a look at the driver source and the statement that is being used to return all indizes does an inner join on rdb$indices and rdb$index_segments
        >
        > An index with "computed by" does not seem to have a row in rdb$index_segments and the join fails.
        >
        > When changing the statement to use an outer join, the above index will be returned.
        >
        > Of course the NULL values for rdb$field_position and rdb$field_name from rdb$index_segments needs to be taken into account.

        It looks like you are right, I created a tracker ticket:
        http://tracker.firebirdsql.org/browse/JDBC-228

        As I am currently working on the DatabaseMetaData implementation anyway,
        I will probably include this in Jaybird 2.2 if the actual fix is not too
        complex.

        Thank you for reporting this problem.

        Mark
        --
        Mark Rotteveel
      • Thomas
        ... I added a workaround for this to my SQL tool and this is the statement I came up with: SELECT NULL as TABLE_CAT , NULL as TABLE_SCHEM ,
        Message 3 of 3 , Jan 14, 2012
          --- In Firebird-Java@yahoogroups.com, Mark Rotteveel <mark@...> wrote:
          > It looks like you are right, I created a tracker ticket:
          > http://tracker.firebirdsql.org/browse/JDBC-228
          >
          > As I am currently working on the DatabaseMetaData implementation anyway,
          > I will probably include this in Jaybird 2.2 if the actual fix is not too
          > complex.

          I added a workaround for this to my SQL tool and this is the statement I came up with:

          SELECT NULL as TABLE_CAT
          , NULL as TABLE_SCHEM
          , trim(ind.RDB$RELATION_NAME) AS TABLE_NAME
          , ind.RDB$UNIQUE_FLAG AS NON_UNIQUE
          , NULL as INDEX_QUALIFIER
          , trim(ind.RDB$INDEX_NAME) as INDEX_NAME
          , NULL as "TYPE"
          , coalesce(ise.rdb$field_position,0) +1 as ORDINAL_POSITION
          , trim(coalesce(ise.rdb$field_name, ind.rdb$expression_source)) as COLUMN_NAME
          , case
          when ind.rdb$expression_source is not null then null
          when ind.RDB$INDEX_TYPE = 1 then 'D'
          else 'A' end as ASC_OR_DESC
          , 0 as CARDINALITY
          , 0 as "PAGES"
          , null as FILTER_CONDITION
          FROM rdb$indices ind
          LEFT JOIN rdb$index_segments ise ON ind.rdb$index_name = ise.rdb$index_name
          WHERE ind.rdb$relation_name = ?
          ORDER BY 4, 6, 8

          At the heart it's a copy of the statement from the driver.

          Returning NULL for the "ASC_OR_DESC" follows the Javadocs for getIndexInfo() which states that that column should be NULL if the index does not support a direction (which is true for an expression in Firebird as far as I can tell)

          This returns a similar result as e.g. the PostgreSQL driver does for a function based index.
        Your message has been successfully submitted and would be delivered to recipients shortly.