Top-level __in lookup on JSONField fails on MySQL, Oracle

[Jacob Walls]

This test for test_jsonfield.TestQuerying passes on SQLite, Postgres, and MariaDB.

    def test_in(self):
        self.assertSequenceEqual(
            NullableJSONModel.objects.filter(value__in=[{}]),
            [self.objs[2]],
        )

Fails on MySQL and Oracle. Clifford ​suspects the specialization in KeyTransformIn is missing in the top-level In lookup.

Failure on MySQL:

======================================================================
FAIL: test_in (model_fields.test_jsonfield.TestQuerying.test_in)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/django/source/tests/model_fields/test_jsonfield.py", line 379, in test_in
    self.assertSequenceEqual(
AssertionError: Sequences differ: <QuerySet []> != [<NullableJSONModel: NullableJSONModel object (3)>]

Second sequence contains 1 additional elements.
First extra element 0:
<NullableJSONModel: NullableJSONModel object (3)>

- <QuerySet []>
+ [<NullableJSONModel: NullableJSONModel object (3)>]

----------------------------------------------------------------------
(0.001) SELECT `model_fields_nullablejsonmodel`.`id`,
       `model_fields_nullablejsonmodel`.`value`,
       `model_fields_nullablejsonmodel`.`value_custom`
FROM `model_fields_nullablejsonmodel`
WHERE `model_fields_nullablejsonmodel`.`value` IN ('{}'); args=('{}',); alias=default

----------------------------------------------------------------------

Failure on Oracle:

django.db.utils.DatabaseError: ORA-22848: cannot use NCLOB type as comparison key
Help: https://docs.oracle.com/error-help/db/ora-22848/

----------------------------------------------------------------------
(0.016) SELECT "MODEL_FIELDS_NULLABLEJSONMODEL"."ID",
       "MODEL_FIELDS_NULLABLEJSONMODEL"."VALUE",
       "MODEL_FIELDS_NULLABLEJSONMODEL"."VALUE_CUSTOM"
FROM "MODEL_FIELDS_NULLABLEJSONMODEL"
WHERE "MODEL_FIELDS_NULLABLEJSONMODEL"."VALUE" IN ({}); args=('{}',); alias=default

----------------------------------------------------------------------

[Clifford Gama]

Thanks for the report!

[Jacob Walls]

Looking really good, just one last suggestion about avoiding the skip on Oracle.

[Jacob Walls <jacobtylerwalls@…>]

In 66fed37:

Fixed #36689 -- Fixed top-level JSONField in lookup failures on MySQL and Oracle.

Added a JSONIn lookup to handle correct serialization and extraction
for JSONField top-level in queries on backends without native JSON
support. KeyTransformIn now subclasses JSONIn.

Co-authored-by: Jacob Walls <jacobtylerwalls@…>

Thanks Jacob Walls for the report and review.

[Jacob Walls <jacobtylerwalls@…>]

In 9b8e4c6d:

Refs #36689 -- Serialized JSONIn rhs parameters wrapped in Value expressions.

[Jacob Walls <jacobtylerwalls@…>]

In 7b54ddd:

Refs #36025 -- Made get_prep_lookup() pass output_field when wrapping direct values in Value.

Previously, only strings were supplied with an output_field when wrapping
direct value iterable elements in Value expressions for ExpressionList. This
caused problems for in lookups on JSONField when using expressions
alongside direct values, as JSONField values can have different types which
need to be adapted by the field's get_db_prep_value().

Refs #36689.

Thanks Jacob Walls for the review.