id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
18082	get_indexes produces wrong resuls under oracle	Anssi Kääriäinen	nobody	"Currently oracle/introspection.py get_indexes uses this SQL:
{{{
SELECT LOWER(all_tab_cols.column_name) AS column_name,
       CASE user_constraints.constraint_type
           WHEN 'P' THEN 1 ELSE 0
       END AS is_primary_key,
       CASE user_indexes.uniqueness
           WHEN 'UNIQUE' THEN 1 ELSE 0
       END AS is_unique
FROM   all_tab_cols, user_cons_columns, user_constraints, user_ind_columns, user_indexes
WHERE  all_tab_cols.column_name = user_cons_columns.column_name (+)
  AND  all_tab_cols.table_name = user_cons_columns.table_name (+)
  AND  user_cons_columns.constraint_name = user_constraints.constraint_name (+)
  AND  user_constraints.constraint_type (+) = 'P'
  AND  user_ind_columns.column_name (+) = all_tab_cols.column_name
  AND  user_ind_columns.table_name (+) = all_tab_cols.table_name
  AND  user_indexes.uniqueness (+) = 'UNIQUE'
  AND  user_indexes.index_name (+) = user_ind_columns.index_name
  AND  all_tab_cols.table_name = UPPER(%s)
}}}

This SQL has two failings: first, it LEFT JOINs instead of INNER JOINs the user_ind_columns, so every column will show as indexed. In addition the results are ordering dependant, for example if we check AUTH_USER by that SQL, the result is:
{{{
id	        1	1
id	        0	1
username	0	1
is_staff	0	0
is_staff	0	0
last_name	0	0
is_superuser	0	0
is_superuser	0	0
last_login	0	0
date_joined	0	0
first_name	0	0
email	        0	0
is_active	0	0
is_active	0	0
password	0	0
}}}
So, if ID is a primary key is purely luck-based.

In addition the SQL is really slow, it takes around 0.9 seconds for single table on my laptop. As the test suite has nearly 1000 tables, inspectdb tests will take half an hour just executing this SQL.

I think one could get the same results by using the three following queries:
{{{
select * from user_ind_columns where table_name = 'AUTH_GROUP';
select * from user_constraints where constraint_type = 'P' and table_name = 'AUTH_GROUP';
select * from user_indexes where uniqueness = 'UNIQUE' and table_name = 'AUTH_GROUP';
}}}
and then doing the joins in Python. It seems this approach would also be at least an order of magnitude faster."	Bug	closed	Database layer (models, ORM)	1.4	Normal	fixed	oracle	anssi.kaariainen@…	Ready for checkin	0	0	0	0	0	0