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

[Sébastien Pattyn]

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.

[Mariusz Felisiak]

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

[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.

[Mariusz Felisiak]

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

[Mariusz Felisiak]

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

[Sage Abdullah]

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.

[Mariusz Felisiak]

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

[Mariusz Felisiak]

​PR

[GitHub <noreply@…>]

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.

[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

[Mariusz Felisiak <felisiak.mariusz@…>]

In 037607f:

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