Opened 17 months ago
Closed 17 months ago
#34671 closed Bug (fixed)
Inspectdb collation issue with oracle views
Reported by: | Philipp Maino | Owned by: | Mariusz Felisiak |
---|---|---|---|
Component: | Core (Management commands) | Version: | 4.2 |
Severity: | Normal | Keywords: | oracle, inspectdb, collation |
Cc: | Triage Stage: | Accepted | |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
Given a simple oracle table with a primary id column and a VARCHAR column as well as an oracle view that simply selects all column from that table without any manipulation.
When using inspectdb to introspect the table we get a models.CharField
for the VARCHAR column without a db_collation
argument.
When using inspectdb to introspect the view we get a models.CharField
for the VARCHAR column with a db_collation
argument.
I believe this to be a bug that's being caused by DatabaseIntrospection.get_table_description
in django/db/backends/oracle/introspection.py.
This will cause an issue when DiscoverRunner.run_checks
triggers CharField._check_db_collation()
in django/db/models/fields/init.py as this section will return an error if db_collation is not None
.
Recreate Issue
CREATE TABLE COLLATION_TEST ( ID NUMBER, TEST VARCHAR2(10), TEST2 NVARCHAR2(10), TEST3 VARCHAR(10) ); CREATE VIEW COLLATION_TEST_VIEW AS SELECT * FROM COLLATION_TEST;
Run manage.py inspectdb COLLATION_TEST
:
class CollationTest(models.Model): id = models.FloatField(blank=True, null=True) test = models.CharField(max_length=10, blank=True, null=True) test2 = models.CharField(max_length=10, blank=True, null=True) test3 = models.CharField(max_length=10, blank=True, null=True) class Meta: managed = False db_table = 'collation_test'
Run manage.py inspectdb COLLATION_TEST_VIEW
.
class CollationTestView(models.Model): id = models.FloatField(blank=True, null=True) test = models.CharField(max_length=10, db_collation='USING_NLS_COMP', blank=True, null=True) test2 = models.CharField(max_length=10, db_collation='USING_NLS_COMP', blank=True, null=True) test3 = models.CharField(max_length=10, db_collation='USING_NLS_COMP', blank=True, null=True) class Meta: managed = False db_table = 'collation_test_view'
If you consider this as a bug as well then this could be fixed for example by adjusting get_table_description
.
You could check first whether table_name
is a table or a view by querying user_object
as column OBJECT_TYPE identifies an obhect as TABLE' or "VIEW'.
If it's a view you have to adjust the introspection query in get_table_description
to use user_views
instead of user_tables
. This will yield the correct CharField definition without db_collation.
SELECT user_tab_cols.column_name, user_tab_cols.data_default, CASE WHEN user_tab_cols.collation = user_views.default_collation THEN NULL ELSE user_tab_cols.collation END collation, CASE WHEN user_tab_cols.char_used IS NULL THEN user_tab_cols.data_length ELSE user_tab_cols.char_length END as display_size, CASE WHEN user_tab_cols.identity_column = 'YES' THEN 1 ELSE 0 END as is_autofield, CASE WHEN EXISTS (SELECT 1 FROM user_json_columns WHERE user_json_columns.table_name = user_tab_cols.table_name AND user_json_columns.column_name = user_tab_cols.column_name) THEN 1 ELSE 0 END as is_json, user_col_comments.comments as col_comment FROM user_tab_cols LEFT OUTER JOIN user_views ON user_views.view_name = user_tab_cols.table_name LEFT OUTER JOIN user_col_comments ON user_col_comments.column_name = user_tab_cols.column_name AND user_col_comments.table_name = user_tab_cols.table_name WHERE user_tab_cols.table_name = 'COLLATION_TEST_VIEW'
Attachments (1)
Change History (8)
by , 17 months ago
Attachment: | collation.png added |
---|
comment:1 by , 17 months ago
Description: | modified (diff) |
---|
comment:2 by , 17 months ago
Description: | modified (diff) |
---|
comment:3 by , 17 months ago
Component: | Error reporting → Core (Management commands) |
---|---|
Owner: | set to |
Triage Stage: | Unreviewed → Accepted |
comment:5 by , 17 months ago
Has patch: | set |
---|---|
Needs tests: | set |
Owner: | changed from | to
Patch needs improvement: | set |
Status: | new → assigned |
comment:6 by , 17 months ago
Needs tests: | unset |
---|---|
Owner: | changed from | to
Patch needs improvement: | unset |
Thanks for the report! Materialized views are also affected. Unfortunately, data dictionary views are extremely slow on Oracle, we must be carefully with introducing any additional complexity here.