[Django] #31936: JSONField not filtering correct with `__in`

[Django]

#31936: JSONField not filtering correct with `__in`
-------------------------------------+-------------------------------------
Reporter: Sébastien | Owner: nobody
Pattyn |
Type: Bug | Status: new
Component: Database | Version: 3.1
layer (models, ORM) |
Severity: Normal | Keywords: JSONField
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
I am currently rewriting our app where we will start using
`models.JSONField` instead of `django_mysql.models.JSONField`. I noticed
that the `__in` operator is not reacting the same way is it does on other
fields.

{{{
first_filter = {‘our_field__key__in': [0]}
first_items = OurModel.objects.filter(**first_filter)
len(first_items)
0
second_filter = {'our_field__key': 0}
second_items = OurModel.objects.filter(**second_filter)
len(second_items )
312
}}}

I would expect that both filters would give me the same queryset but this
is not the case.

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

[Django]

#31936: JSONField not filtering correct with __in lookup on key transforms.
-------------------------------------+-------------------------------------
Reporter: Sébastien Pattyn | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: JSONField | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0

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

Changes (by felixxm):

* status: new => closed
* resolution: => needsinfo

Comment:

Thanks for this ticket, however I cannot reproduce this issue. I tried
with the following test and it works for me (also on MySQL):
{{{
diff --git a/tests/model_fields/test_jsonfield.py
b/tests/model_fields/test_jsonfield.py
index a7648711ac..97d79e5bee 100644
--- a/tests/model_fields/test_jsonfield.py
+++ b/tests/model_fields/test_jsonfield.py
@@ -608,6 +608,14 @@ class TestQuerying(TestCase):
self.objs[3:5],
)

+ def test_31936(self):
+ self.assertSequenceEqual(
+ NullableJSONModel.objects.filter(
+ value__c__in=[14, 15],
+ ),
+ [self.objs[3], self.objs[4]],
+ )
+
@skipUnlessDBFeature('supports_json_field_contains')
def test_array_key_contains(self):
tests = [
}}}

Can you prepare a sample project to reproduce this issue? and provide
details about database (specific version, MariaDB/MySQL)?

--
Ticket URL: <https://code.djangoproject.com/ticket/31936#comment:1>

[Django]

#31936: JSONField not filtering correct with __in lookup on key transforms.
-------------------------------------+-------------------------------------
Reporter: Sébastien Pattyn | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: JSONField | Triage Stage:
| Unreviewed

Has patch: 0 | Needs documentation: 0

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

Comment (by Sébastien Pattyn):

Hi,

I noticed I forgot to mention that this error only occurs if the length of
the list, where `__in` is used, only contains one element.
There were no issues when the list contains more then one element. I'm
currently on MySQL 5.7.

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

[Django]

#31936: On MySQL and SQLite, __in lookup doesn't work on key transforms when RHS
has a single value.

-------------------------------------+-------------------------------------
Reporter: Sébastien Pattyn | Owner: nobody

Type: Bug | Status: new

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

Severity: Release blocker | Resolution:
Keywords: JSONField SQLite | Triage Stage: Accepted
MySQL |
Has patch: 0 | Needs documentation: 0

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

Changes (by felixxm):

* status: closed => new
* severity: Normal => Release blocker
* cc: sage (added)
* keywords: JSONField => JSONField SQLite MySQL
* resolution: needsinfo =>
* stage: Unreviewed => Accepted

Comment:

Thanks I confirmed this issue on SQLite and MySQL, it works on PostgreSQL
and Oracle.

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

[Django]

#31936: On MySQL and SQLite, __in lookup doesn't work on key transforms when RHS
has a single value.
-------------------------------------+-------------------------------------

Reporter: Sébastien Pattyn | Owner: felixxm
Type: Bug | Status: assigned

Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: JSONField SQLite | Triage Stage: Accepted
MySQL |

Has patch: 0 | Needs documentation: 0

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

Changes (by felixxm):

* owner: nobody => felixxm
* status: new => assigned

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

[Django]

#31936: On MySQL, Oracle, and SQLite, __in lookup doesn't work on key transforms.

-------------------------------------+-------------------------------------
Reporter: Sébastien Pattyn | Owner: felixxm
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: JSONField SQLite | Triage Stage: Accepted
MySQL |

Has patch: 0 | Needs documentation: 0

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

Comment (by felixxm):

On Oracle, it doesn't work when list contains strings.

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

[Django]

#31936: On MySQL, Oracle, and SQLite, __in lookup doesn't work on key transforms.
-------------------------------------+-------------------------------------
Reporter: Sébastien Pattyn | Owner: felixxm
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: JSONField SQLite | Triage Stage: Accepted
MySQL |

Has patch: 0 | Needs documentation: 0

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

Comment (by sage):

`django-mysql` [https://github.com/adamchainz/django-
mysql/blob/master/src/django_mysql/models/lookups.py#L89-L97 customizes
the __in] lookup by [https://github.com/adamchainz/django-
mysql/blob/master/src/django_mysql/models/functions.py#L276-L282 encoding
the rhs and CAST-ing it to JSON]. I think it won't work on MariaDB though,
because we can't do casting to JSON. There may be other ways to do it, but
I haven't got an idea. Same with SQLite and Oracle.

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

[Django]

#31936: On MySQL, Oracle, and SQLite, __in lookup doesn't work on key transforms.
-------------------------------------+-------------------------------------
Reporter: Sébastien Pattyn | Owner: felixxm
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: JSONField SQLite | Triage Stage: Accepted
MySQL |

Has patch: 0 | Needs documentation: 0

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

Comment (by felixxm):

I should send PR tomorrow morning, fix is almost ready.

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

[Django]

#31936: On MySQL, Oracle, and SQLite, __in lookup doesn't work on key transforms.
-------------------------------------+-------------------------------------
Reporter: Sébastien Pattyn | Owner: felixxm
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: JSONField SQLite | Triage Stage: Accepted
MySQL |

Has patch: 1 | Needs documentation: 0

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

Changes (by felixxm):

* has_patch: 0 => 1

Comment:

[https://github.com/django/django/pull/13346 PR]

--
Ticket URL: <https://code.djangoproject.com/ticket/31936#comment:8>

[Django]

#31936: On MySQL, Oracle, and SQLite, __in lookup doesn't work on key transforms.
-------------------------------------+-------------------------------------
Reporter: Sébastien Pattyn | Owner: felixxm

Type: Bug | Status: closed

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

Severity: Release blocker | Resolution: fixed

Keywords: JSONField SQLite | Triage Stage: Accepted
MySQL |

Has patch: 1 | Needs documentation: 0

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

Changes (by GitHub <noreply@…>):

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

Comment:

In [changeset:"1251772cb83aa4106f526fe00738e51c0eb59122" 1251772c]:
{{{
#!CommitTicketReference repository=""
revision="1251772cb83aa4106f526fe00738e51c0eb59122"
Fixed #31936 -- Fixed __in lookup on key transforms for JSONField.

This resolves an issue on databases without a native JSONField
(MariaDB, MySQL, SQLite, Oracle), where values must be wrapped.

Thanks Sébastien Pattyn for the report.
}}}

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

[Django]

#31936: On MySQL, Oracle, and SQLite, __in lookup doesn't work on key transforms.
-------------------------------------+-------------------------------------
Reporter: Sébastien Pattyn | Owner: felixxm
Type: Bug | Status: closed
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: JSONField SQLite | Triage Stage: Accepted
MySQL |

Has patch: 1 | Needs documentation: 0

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

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"9075d1f662f8734004d0207a58927c93d2b19092" 9075d1f6]:
{{{
#!CommitTicketReference repository=""
revision="9075d1f662f8734004d0207a58927c93d2b19092"
[3.1.x] Fixed #31936 -- Fixed __in lookup on key transforms for JSONField.

This resolves an issue on databases without a native JSONField
(MariaDB, MySQL, SQLite, Oracle), where values must be wrapped.

Thanks Sébastien Pattyn for the report.

Backport of 1251772cb83aa4106f526fe00738e51c0eb59122 from master
}}}

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

[Django]

#31936: On MySQL, Oracle, and SQLite, __in lookup doesn't work on key transforms.
-------------------------------------+-------------------------------------

Reporter: Sébastien Pattyn | Owner: Mariusz
| Felisiak

Type: Bug | Status: closed
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: JSONField SQLite | Triage Stage: Accepted
MySQL |

Has patch: 1 | Needs documentation: 0

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

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"037607ff192cc924a282f08390c69cef83aa47a5" 037607f]:
{{{
#!CommitTicketReference repository=""
revision="037607ff192cc924a282f08390c69cef83aa47a5"
Refs #31936 -- Added tests for __in lookup on JSONField key transforms
with booleans.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/31936#comment:11>