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.