Oracle guard for coalescing text & varchar doesn't fathom JSONField

[Jacob Walls]

Coalesce has an as_oracle() method to make sure that varchar and NCLOB (text) fields are not mixed. JSONField is also implemented as an NCLOB type, so it needs to be included in the check, too.

Discovered in ​https://github.com/django/django/pull/20009#discussion_r2500562079.

Rough test for demonstration purposes -- a little more realism would be nice.

tests/db_functions/comparison/test_coalesce.py

@@ -1 @@
-from django.db.models import Subquery, TextField
+from django.db.models import JSONField, Subquery, TextField
 from django.db.models.functions import Coalesce, Lower
 from django.test import TestCase
 from django.utils import timezone
@@ -51 +51 @@
         self.assertQuerySetEqual(
             article.order_by("title"), [lorem_ipsum.lower()], lambda a: a.headline
         )
+        # mixed JSON and Char
+        article = Article.objects.annotate(
+            # fix this to be more realistic?
+            summary_or_metadata=Coalesce("summary", "metadata", output_field=JSONField()),
+        )
+        self.assertQuerySetEqual(
+            article.order_by("title"), [{}], lambda a: a.summary_or_metadata
+        )
 
     def test_ordering(self):
         Author.objects.create(name="John Smith", alias="smithj")

tests/db_functions/migrations/0002_create_test_models.py

@@ -50 +50 @@
                 ("published", models.DateTimeField(null=True, blank=True)),
                 ("updated", models.DateTimeField(null=True, blank=True)),
                 ("views", models.PositiveIntegerField(default=0)),
+                ("metadata", models.JSONField(default=dict)),
             ],
         ),
         migrations.CreateModel(

tests/db_functions/models.py

@@ -21 +21 @@
     published = models.DateTimeField(null=True, blank=True)
     updated = models.DateTimeField(null=True, blank=True)
     views = models.PositiveIntegerField(default=0)
+    metadata = models.JSONField(default=dict)
 
 
 class Fan(models.Model):

django.db.utils.DatabaseError: ORA-00932: expression ("DB_FUNCTIONS_ARTICLE"."METADATA") is of data type NCLOB, which is incompatible with expected data type NCHAR
Help: https://docs.oracle.com/error-help/db/ora-00932/