Index uniqueness is not always recognized correctly
Description
Environment
Oracle database (in various versions)
Activity
Show:
Nathan Voxland June 19, 2015 at 9:48 PM
The change looks good, I applied it. Thanks!
Fixed
Details
Details
Reporter
Karolin M
Karolin MComponents
Fix versions
Affects versions
Priority
MinorCreated February 6, 2015 at 6:34 AM
Updated June 19, 2015 at 9:48 PM
Resolved June 19, 2015 at 9:48 PM
To get the error:
Oracle database with several users (User1 and User2)
User1 has a table Table1 with unique index IDX1
User2 has a table Table1 with non unique index IDX1
Generate snapshot for User2
=> The IDX1 will be unique in the snapshot although it is not unique for User2
The problem is that the select used to find an index in the class JdbcDatabaseSnapshot of the package liquibase.snapshot in the method fastFetch is returning several results for IDX1. In fact it also returns the results for IDX1 of User1.
The reason for this is the join "JOIN ALL_INDEXES i on i.index_name = c.index_name". The condition index_name is not sufficient.
I was able to fix the problem by changing the join condition into "on (i.index_name=c.index_name and i.table_owner=c.table_owner".
I am not sure if changing the select this way might cause problems in other use cases, so I am posting the bug here.
The fixed select statement:
IndexSelect
String sql = "SELECT c.INDEX_NAME, 3 AS TYPE, c.TABLE_NAME, c.COLUMN_NAME, c.COLUMN_POSITION AS ORDINAL_POSITION, e.COLUMN_EXPRESSION AS FILTER_CONDITION, case I.UNIQUENESS when 'UNIQUE' then 0 else 1 end as NON_UNIQUE " + "FROM ALL_IND_COLUMNS c " + "JOIN ALL_INDEXES i on (i.index_name = c.index_name and i.table_owner = c.table_owner)" + "LEFT JOIN all_ind_expressions e on (e.column_position = c.column_position AND e.index_name = c.index_name) " + "WHERE c.TABLE_OWNER='" + database.correctObjectName(catalogAndSchema.getCatalogName(), Schema.class) + "'";