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/

[kanin.kearpimy@…]

Hi, I'm James (Kanin Kearpimy) from Djangonaut, 6th batch. I'm working on this issue.

[kanin.kearpimy@…]

Duplicate with below.

[kanin.kearpimy@…]

Patch: ​PR

[kanin.kearpimy@…]

Unexpectedly after writing test, I found out postgres does throw similar error. However, my implementation doesn't negatively affect oracle, mysql, mariadb, sqlite (I ran db test in my local and pipeline, no evidence of failure regarding those DBs).

  File "/django/source/django/db/backends/utils.py", line 92, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/django/source/django/db/backends/utils.py", line 100, in _execute
    with self.db.wrap_database_errors:
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/django/source/django/db/utils.py", line 94, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/django/source/django/db/backends/utils.py", line 105, in _execute
    return self.cursor.execute(sql, params)
           ~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^
  File "/usr/local/lib/python3.14/site-packages/psycopg/cursor.py", line 117, in execute
    raise ex.with_traceback(None)
django.db.utils.ProgrammingError: COALESCE types character varying and jsonb cannot be matched
LINE 1: ...data", COALESCE("db_functions_article"."summary", "db_functi...

[kanin.kearpimy@…]

Hi, I added test and it all pass on oracle. So, I unflagged this "need test" for this issue.

[Jacob Walls]

Left some notes about reimagining the test case to be cross-backend.

[Jacob Walls <jacobtylerwalls@…>]

In d7bf843:

Fixed #36906 -- Handled coalescing JSON-primitive strings and JSON values on Oracle.