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

Thanks Philipp Maino for the report.

2 files changed, 80 insertions, 6 deletions

diff --git a/django/db/backends/oracle/introspection.py b/django/db/backends/oracle/introspection.py
index c4a734f7ec..2063b535ce 100644
--- a/django/db/backends/oracle/introspection.py
+++ b/django/db/backends/oracle/introspection.py
@@ -110,6 +110,31 @@ class DatabaseIntrospection(BaseDatabaseIntrospection):
         Return a description of the table with the DB-API cursor.description
         interface.
         """
+        # A default collation for the given table/view/materialized view.
+        cursor.execute(
+            """
+            SELECT user_tables.default_collation
+            FROM user_tables
+            WHERE
+                user_tables.table_name = UPPER(%s) AND
+                NOT EXISTS (
+                    SELECT 1
+                    FROM user_mviews
+                    WHERE user_mviews.mview_name = user_tables.table_name
+                )
+            UNION ALL
+            SELECT user_views.default_collation
+            FROM user_views
+            WHERE user_views.view_name = UPPER(%s)
+            UNION ALL
+            SELECT user_mviews.default_collation
+            FROM user_mviews
+            WHERE user_mviews.mview_name = UPPER(%s)
+            """,
+            [table_name, table_name, table_name],
+        )
+        row = cursor.fetchone()
+        default_table_collation = row[0] if row else ""
         # user_tab_columns gives data default for columns
         cursor.execute(
             """
@@ -117,7 +142,7 @@ class DatabaseIntrospection(BaseDatabaseIntrospection):
                 user_tab_cols.column_name,
                 user_tab_cols.data_default,
                 CASE
-                    WHEN user_tab_cols.collation = user_tables.default_collation
+                    WHEN user_tab_cols.collation = %s
                     THEN NULL
                     ELSE user_tab_cols.collation
                 END collation,
@@ -144,14 +169,12 @@ class DatabaseIntrospection(BaseDatabaseIntrospection):
                 user_col_comments.comments as col_comment
             FROM user_tab_cols
             LEFT OUTER JOIN
-                user_tables ON user_tables.table_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 = UPPER(%s)
             """,
-            [table_name],
+            [default_table_collation, table_name],
         )
         field_map = {
             column: (
diff --git a/tests/backends/oracle/test_introspection.py b/tests/backends/oracle/test_introspection.py
index c6a143eb2d..beeb4f603e 100644
--- a/tests/backends/oracle/test_introspection.py
+++ b/tests/backends/oracle/test_introspection.py
@@ -1,9 +1,9 @@
 import unittest
 
 from django.db import connection
-from django.test import TransactionTestCase
+from django.test import TransactionTestCase, skipUnlessDBFeature
 
-from ..models import Square
+from ..models import Person, Square
 
 
 @unittest.skipUnless(connection.vendor == "oracle", "Oracle tests")
@@ -33,3 +33,54 @@ class DatabaseSequenceTests(TransactionTestCase):
                 # Recreate model, because adding identity is impossible.
                 editor.delete_model(Square)
                 editor.create_model(Square)
+
+    @skipUnlessDBFeature("supports_collation_on_charfield")
+    def test_get_table_description_view_default_collation(self):
+        person_table = connection.introspection.identifier_converter(
+            Person._meta.db_table
+        )
+        first_name_column = connection.ops.quote_name(
+            Person._meta.get_field("first_name").column
+        )
+        person_view = connection.introspection.identifier_converter("TEST_PERSON_VIEW")
+        with connection.cursor() as cursor:
+            cursor.execute(
+                f"CREATE VIEW {person_view} "
+                f"AS SELECT {first_name_column} FROM {person_table}"
+            )
+            try:
+                columns = connection.introspection.get_table_description(
+                    cursor, person_view
+                )
+                self.assertEqual(len(columns), 1)
+                self.assertIsNone(columns[0].collation)
+            finally:
+                cursor.execute(f"DROP VIEW {person_view}")
+
+    @skipUnlessDBFeature("supports_collation_on_charfield")
+    def test_get_table_description_materialized_view_non_default_collation(self):
+        person_table = connection.introspection.identifier_converter(
+            Person._meta.db_table
+        )
+        first_name_column = connection.ops.quote_name(
+            Person._meta.get_field("first_name").column
+        )
+        person_mview = connection.introspection.identifier_converter(
+            "TEST_PERSON_MVIEW"
+        )
+        collation = connection.features.test_collations.get("ci")
+        with connection.cursor() as cursor:
+            cursor.execute(
+                f"CREATE MATERIALIZED VIEW {person_mview} "
+                f"DEFAULT COLLATION {collation} "
+                f"AS SELECT {first_name_column} FROM {person_table}"
+            )
+            try:
+                columns = connection.introspection.get_table_description(
+                    cursor, person_mview
+                )
+                self.assertEqual(len(columns), 1)
+                self.assertIsNotNone(columns[0].collation)
+                self.assertNotEqual(columns[0].collation, collation)
+            finally:
+                cursor.execute(f"DROP MATERIALIZED VIEW {person_mview}")