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$]
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 :
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).
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.
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.
I think Yoann's first suggestion is the only workable one. Just log that we are skipping the index (and why) and move on...
Fixed in master and 5.0 branches.