Inspectdb collation issue with oracle views

[Philipp Maino]

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'

[Mariusz Felisiak]

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.

[Mariusz Felisiak]

​New PR

[GitHub <noreply@…>]

In a6d30f5:

Fixed #34671 -- Fixed collation introspection for views and materialized views on Oracle.

Thanks Philipp Maino for the report.