Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

DataBaseMetada.getIndexInfo() does not return computed indexes [JDBC228] #277

Closed
firebird-automations opened this issue Jan 14, 2012 · 9 comments

Comments

@firebird-automations
Copy link

Submitted by: @mrotteveel

Is related to JDBC193

consider the following table:

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

Reported by Thomas in Firebird-Java:

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.

Commits: 452fdf0 e6e4cc6

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Version: Jaybird 2.1.6 [ 10285 ]

Version: Jaybird 2.2 [ 10053 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

assignee: Roman Rokytskyy [ rrokytskyy ] => Mark Rotteveel [ avalanche1979 ]

Fix Version: Jaybird 2.2 [ 10053 ]

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

Posted by Thomas to Firebird-Java:

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.

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Link: This issue is related to JDBC193 [ JDBC193 ]

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

As far as I know function indexes are ascending or descending (at least: if I look at the definition in http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-ddl-index.html ); AFAIK all indexes in Firebird have an order.

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

* Changed getIndexInfo() to include expression indexes, the COLUMN_NAME column will contain the expression used for the index (if available)
* Also fixed getIndexInfo() to return only the unique indexes when parameter unique is set to true
* Added basic test of getIndexInfo()

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

status: In Progress [ 3 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment