JSONIn with Value(None, JSONField()) does not match JSON null on Oracle

[Clifford Gama]

On Oracle, JSONIn.as_oracle() ​only special-cases JSONNull() on the right-hand side. A Value(None, output_field=JSONField()) is not routed to the same JSON_EQUAL path, so it doesn't match a JSON null row in __in. Both JSONNull() and Value(None, JSONField()) match in __exact.

Failing test:

tests/model_fields/test_jsonfield.py

@@ -1076 +1076 @@
                     [obj],
                 )
 
+    @skipUnlessDBFeature("supports_primitives_in_json_field")
+    @skipIfDBFeature("has_native_json_field")
+    def test_in_value_json_typed_none_matches_json_null(self):
+        json_null = NullableJSONModel.objects.create(value=JSONNull())
+        self.assertSequenceEqual(
+            NullableJSONModel.objects.filter(value__in=[Value(None, JSONField())]),
+            [json_null],
+        )
+
     def test_key_in(self):
         tests = [
             ("value__c__in", [14], self.objs[3:5]),

Found while working on #37403, but the bug predates the introduction of JSONIn or JSONNull, so it's not a regression in 6.1.

[Jacob Walls]

Great, thank you!

I reproduced on 5.2 by replacing JSONNull() in the setup step with Value(None, JSONField()). I got:

django.db.utils.DatabaseError: ORA-22848: cannot use NCLOB type as comparison key

On 6.1 the query succeeds, but with the wrong result.

[Clifford Gama]

A note on scope: JSONNull() and Value(None, JSONField()) aren't expected to be fully equivalent. JSONNull() was added in #35381 because the older spelling couldn't reliably mean JSON null, and #36445 (Value(None, JSONField()) saving SQL NULL in bulk_update()) was closed as wontfix on that basis.

This change only widens the existing JSONNull() check in JSONIn.as_oracle() to cover a JSON-typed Value(None). Value(None, JSONField()) already matches JSON null with __exact on Oracle, and with __in on the other backends, so Oracle's __in is the one place the lookup disagrees with itself.

That said, since only JSONNull() is documented as of 6.1 and Value(None, JSONField()) is no longer doc'd, I'd understand closing this on the same grounds as #36445 if we'd rather not add more handling for the old spelling.

[Jacob Walls]

Oh good call, a wontfix is more consistent with #36445. I think the related PR where we were going to reference this ticket to bless a skip for Oracle ended up chopping said test anyway.