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.

[Jacob Walls <jacobtylerwalls@…>]

In fabce943:

Fixed #37377 -- Fixed JSONField in crash/wrong result for Value() primitives.

Refs #36689. 9b8e4c6d7d wrapped every Value() rhs param of a JSONIn lookup
in JSON_EXTRACT()/JSON_VALUE() assuming it was already JSON-serialized.
That only holds for Value(..., JSONField()); a bare Value("primitive") gets
an inferred non-JSON output_field (e.g. CharField), so its compiled param
was a raw primitive, not JSON text.

JSONIn.resolve_expression_parameter() now JSON-encodes the Value's raw
.value itself whenever its output_field isn't JSONField.

As a side effect, this also fixes Value(True)/Value(False) in in, which
were previously compared as raw Python bools against the JSON-extracted
lhs and never matched.

Thanks Jacob Walls for the report.

Regression in 9b8e4c6d7d47f7b2e96817ba334e9fd53c10a0df.

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

[Jacob Walls <jacobtylerwalls@…>]

In 71084d6:

[6.1.x] Fixed #37377 -- Fixed JSONField in crash/wrong result for Value() primitives.

Refs #36689. 9b8e4c6d7d wrapped every Value() rhs param of a JSONIn lookup
in JSON_EXTRACT()/JSON_VALUE() assuming it was already JSON-serialized.
That only holds for Value(..., JSONField()); a bare Value("primitive") gets
an inferred non-JSON output_field (e.g. CharField), so its compiled param
was a raw primitive, not JSON text.

JSONIn.resolve_expression_parameter() now JSON-encodes the Value's raw
.value itself whenever its output_field isn't JSONField.

As a side effect, this also fixes Value(True)/Value(False) in in, which
were previously compared as raw Python bools against the JSON-extracted
lhs and never matched.

Thanks Jacob Walls for the report.

Regression in 9b8e4c6d7d47f7b2e96817ba334e9fd53c10a0df.

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

Backport of fabce94374e166e80e2643db8b85d1e3442427bf from main.