InformationExtractorJdbcDatabaseMetaDataImpl can't handle Oracle function indexes

Description

Oracle allows indexes to be created using a function rather than a column. Example:

CREATE INDEX OPS_OPERATED_SECTOR_N10
ON OPS_OPERATED_SECTOR (TRUNC(DEPARTURE_DATETIME) ASC)

This causes an exception:
org.hibernate.tool.schema.spi.SchemaManagementException: Could not locate column information using identifier [SYS_NC00137$]
at org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.getIndexes(InformationExtractorJdbcDatabaseMetaDataImpl.java:550)

Environment

None

Activity

Show:
Yoann Rodière
December 1, 2015, 8:57 AM

Hi,

I've got the exact same problem with a PostgreSQL database. PostgreSQL also allows to define indexes on arbitrary expressions. Now we are forced to remove these indexes, because Hibernate won't start otherwise.

Below is a detailed explanation of the issue.

In the case of a "function index", the COLUMN_NAME column in the ResultSet returned by java.sql.DatabaseMetaData.getIndexInfo(String, String, String, boolean, boolean) is in fact the expression that is indexed (not a real name).
Unfortunately, org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.getIndexes(TableInformation), which analyzes this ResultSet, always expects a real column name, and tries to find to which column this string refers to :

Obviously this will always fail in the case of a "function index".

Several options may help solving this case :

  1. Ignore the index (just don't add it to the result of org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.getIndexes(TableInformation)) when we can't retrieve the column information. After all, if the column is unknown, it probably means that Hibernate won't use it anyway. Cons: there might be some cases where throwing an exception is useful (though I can't imagine one).

  2. Try to determine if the string is a valid identifier, for instance with a regex, or maybe a built-in Hibernate function that I don't know. Cons: this would probably be very fragile.

I don't see any other solution, but I would be happy to provide a pull request if a developer could confirm that one of these solutions may be accepted upstream.

Regards.

Guillaume Smet
December 16, 2015, 2:06 PM

Hi!

Any comment on this one? Yoann and I can work on something but it would be better if we can agree on the way to go.

Functional indexes are rather common when using PostgreSQL or Oracle and this issue causes performance issues on several of our projects.

I think Hibernate should simply ignore the indexes if they are not related to a column.

Thanks for the feedback.


Guillaume

Steve Ebersole
December 16, 2015, 6:02 PM

I think Yoann's first suggestion is the only workable one. Just log that we are skipping the index (and why) and move on...

Yoann Rodière
December 21, 2015, 3:35 PM

I submitted a pull request: https://github.com/hibernate/hibernate-orm/pull/1197

Could someone review and (hopefully) approve it?

Thanks,

Gail Badner
December 22, 2015, 12:07 AM

Fixed in master and 5.0 branches.

Assignee

Gail Badner

Reporter

Ramon Casha

Fix versions

Labels

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure