Opened 13 months ago

Closed 13 months ago

Last modified 6 months ago

#31936 closed Bug (fixed)

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

Reported by: Sébastien Pattyn Owned by: Mariusz Felisiak
Component: Database layer (models, ORM) Version: 3.1
Severity: Release blocker Keywords: JSONField SQLite MySQL
Cc: sage Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (11)

comment:1 Changed 13 months ago by Mariusz Felisiak

Resolution: needsinfo
Status: newclosed
Summary: JSONField not filtering correct with `__in`JSONField not filtering correct with __in lookup on key transforms.

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)?

comment:2 Changed 13 months ago 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.

comment:3 Changed 13 months ago by Mariusz Felisiak

Cc: sage added
Keywords: SQLite MySQL added
Resolution: needsinfo
Severity: NormalRelease blocker
Status: closednew
Summary: JSONField not filtering correct with __in lookup on key transforms.On MySQL and SQLite, __in lookup doesn't work on key transforms when RHS has a single value.
Triage Stage: UnreviewedAccepted

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

comment:4 Changed 13 months ago by Mariusz Felisiak

Owner: changed from nobody to Mariusz Felisiak
Status: newassigned

comment:5 Changed 13 months ago by Mariusz Felisiak

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

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

comment:6 Changed 13 months ago by sage

django-mysql customizes the __in lookup by 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.

comment:7 Changed 13 months ago by Mariusz Felisiak

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

comment:8 Changed 13 months ago by Mariusz Felisiak

Has patch: set

comment:9 Changed 13 months ago by GitHub <noreply@…>

Resolution: fixed
Status: assignedclosed

In 1251772c:

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.

comment:10 Changed 13 months ago by Mariusz Felisiak <felisiak.mariusz@…>

In 9075d1f6:

[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

comment:11 Changed 6 months ago by Mariusz Felisiak <felisiak.mariusz@…>

In 037607f:

Refs #31936 -- Added tests for in lookup on JSONField key transforms with booleans.

Note: See TracTickets for help on using tickets.
Back to Top