[Django] #36689: Top-level __in lookup on JSONField fails on MySQL, Oracle

Django

unread,

Oct 27, 2025, 9:56:45 AM10/27/25

to django-...@googlegroups.com

#36689: Top-level __in lookup on JSONField fails on MySQL, Oracle
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: dev | Severity: Normal
Keywords: JSON, In | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
This test for `test_jsonfield.TestQuerying` passes on SQLite, Postgres,
and MariaDB.

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

Fails on MySQL and Oracle. Clifford
[https://github.com/django/django/pull/19794#issuecomment-3447789894
suspects] the specialization in `KeyTransformIn` is missing in the top-
level `In` lookup.

Failure on MySQL:
{{{#!py
======================================================================
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:
{{{#!py
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

----------------------------------------------------------------------
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36689>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Oct 27, 2025, 11:10:52 AM10/27/25

to django-...@googlegroups.com

#36689: Top-level __in lookup on JSONField fails on MySQL, Oracle
-------------------------------------+-------------------------------------

Changes (by Clifford Gama):

* owner: (none) => Clifford Gama
* status: new => assigned

Comment:

Thanks for the report!
--
Ticket URL: <https://code.djangoproject.com/ticket/36689#comment:1>

Django

unread,

Oct 27, 2025, 3:24:29 PM10/27/25

to django-...@googlegroups.com

#36689: Top-level __in lookup on JSONField fails on MySQL, Oracle
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Owner: Clifford
| Gama
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:

Keywords: JSON, In | Triage Stage: Accepted

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Natalia Bidart):

* stage: Unreviewed => Accepted

--
Ticket URL: <https://code.djangoproject.com/ticket/36689#comment:2>

Django

unread,

Oct 27, 2025, 4:50:38 PM10/27/25

to django-...@googlegroups.com

#36689: Top-level __in lookup on JSONField fails on MySQL, Oracle
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Owner: Clifford
| Gama
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: JSON, In | Triage Stage: Accepted

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Clifford Gama):

* has_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/36689#comment:3>

Django

unread,

Oct 30, 2025, 5:52:22 AM10/30/25

to django-...@googlegroups.com

Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Clifford Gama):

* needs_better_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/36689#comment:4>

Django

unread,

Oct 30, 2025, 7:41:46 AM10/30/25

to django-...@googlegroups.com

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Clifford Gama):

* needs_better_patch: 1 => 0

--
Ticket URL: <https://code.djangoproject.com/ticket/36689#comment:5>

Django

unread,

Oct 30, 2025, 8:48:19 AM10/30/25

to django-...@googlegroups.com

Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Jacob Walls):

* cc: Sage Abdullah (added)

* needs_better_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/36689#comment:6>

Django

unread,

Oct 31, 2025, 7:30:15 AM10/31/25

to django-...@googlegroups.com

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Clifford Gama):

* needs_better_patch: 1 => 0

--
Ticket URL: <https://code.djangoproject.com/ticket/36689#comment:7>

Django

unread,

Nov 4, 2025, 8:43:21 PM11/4/25

to django-...@googlegroups.com

Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Jacob Walls):

* needs_better_patch: 0 => 1

Comment:

Looking really good, just one last suggestion about avoiding the skip on
Oracle.
--
Ticket URL: <https://code.djangoproject.com/ticket/36689#comment:8>

Django

unread,

Dec 8, 2025, 6:34:35 AM12/8/25

to django-...@googlegroups.com

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Clifford Gama):

* needs_better_patch: 1 => 0

--
Ticket URL: <https://code.djangoproject.com/ticket/36689#comment:9>

Django

unread,

Dec 9, 2025, 3:33:41 PM12/9/25

to django-...@googlegroups.com

#36689: Top-level __in lookup on JSONField fails on MySQL, Oracle
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Owner: Clifford
| Gama
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:

Keywords: JSON, In | Triage Stage: Ready for
| checkin

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Jacob Walls):

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/36689#comment:10>

Django

unread,

Dec 10, 2025, 5:46:01 PM12/10/25

to django-...@googlegroups.com

#36689: Top-level __in lookup on JSONField fails on MySQL, Oracle
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Owner: Clifford
| Gama

Type: Bug | Status: closed

Component: Database layer | Version: dev
(models, ORM) |

Severity: Normal | Resolution: fixed

Changes (by Jacob Walls <jacobtylerwalls@…>):

* resolution: => fixed
* status: assigned => closed

Comment:

In [changeset:"66fed37ecb78daf0a50e95151a752b5760293514" 66fed37]:
{{{#!CommitTicketReference repository=""
revision="66fed37ecb78daf0a50e95151a752b5760293514"
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 <jacobty...@gmail.com>

Thanks Jacob Walls for the report and review.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36689#comment:11>

Django

unread,

Dec 10, 2025, 5:46:01 PM12/10/25

to django-...@googlegroups.com

#36689: Top-level __in lookup on JSONField fails on MySQL, Oracle
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Owner: Clifford
| Gama
Type: Bug | Status: closed
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: JSON, In | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Jacob Walls <jacobtylerwalls@…>):

In [changeset:"7b54ddd5e64c96c641b70bb5f0e958a9e2035fb2" 7b54ddd]:
{{{#!CommitTicketReference repository=""
revision="7b54ddd5e64c96c641b70bb5f0e958a9e2035fb2"
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.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36689#comment:13>

Django

unread,

Dec 10, 2025, 5:46:02 PM12/10/25

to django-...@googlegroups.com

#36689: Top-level __in lookup on JSONField fails on MySQL, Oracle
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Owner: Clifford
| Gama
Type: Bug | Status: closed
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: JSON, In | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Jacob Walls <jacobtylerwalls@…>):

In [changeset:"9b8e4c6d7d47f7b2e96817ba334e9fd53c10a0df" 9b8e4c6d]:
{{{#!CommitTicketReference repository=""
revision="9b8e4c6d7d47f7b2e96817ba334e9fd53c10a0df"
Refs #36689 -- Serialized JSONIn rhs parameters wrapped in Value
expressions.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36689#comment:12>

Reply all

Reply to author

Forward