--- 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.