Custom lookup example raises TypeError when looked up against a Subquery

[Jacob Walls]

On the forum I ​shared the TypeError I was getting when registering a custom lookup for a JSONField:

File models/lookups.py:13, in NotEqual.as_sql(self, compiler, connection)
     11 lhs, lhs_params = self.process_lhs(compiler, connection)
     12 rhs, rhs_params = self.process_rhs(compiler, connection)
---> 13 params = lhs_params + rhs_params
     14 return "%s <> %s" % (lhs, rhs), params

TypeError: can only concatenate list (not "tuple") to list

Since this problematic pattern is ​documented, we should either fix the documentation or fix the underlying reason it doesn't work. I haven't looked into whether there are backwards-compatible ways to do the latter.

Reproduction is just to follow the documented pattern and register it with a JSONField e.g. @JSONField.register_lookup, and then try to use it in an ORM query.

[Sarah Boyce]

Testing against main with postgres 17 I don't get the TypeError but I get django.db.utils.DataError: invalid input syntax for type json (but I think that's expected as the documented example is not for JSON fields)

So far I found no issues with the documented example

This is what I have:

tests/custom_lookups/models.py

@@ -18 +18 @@
 
 class MySQLUnixTimestamp(models.Model):
     timestamp = models.PositiveIntegerField()
+
+
+class JSONModel(models.Model):
+    value = models.JSONField()
+
+    class Meta:
+        required_db_features = {"supports_json_field"}

tests/custom_lookups/tests.py

@@ -6 +6 @@
 from django.db import connection, models
 from django.db.models.fields.related_lookups import RelatedGreaterThan
 from django.db.models.lookups import EndsWith, StartsWith
-from django.test import SimpleTestCase, TestCase, override_settings
+from django.test import SimpleTestCase, TestCase, override_settings, skipUnlessDBFeature
 from django.test.utils import register_lookup
 from django.utils import timezone
 
-from .models import Article, Author, MySQLUnixTimestamp
+from .models import Article, Author, MySQLUnixTimestamp, JSONModel
 
 
 class Div3Lookup(models.Lookup):
@@ -249 +249 @@
             self.assertSequenceEqual(qs1, [a1])
             self.assertSequenceEqual(qs2, [a1])
 
+    @skipUnlessDBFeature("supports_json_field")
+    def test_custom_lookup_json_field(self):
+        class NotEqual(models.Lookup):
+            lookup_name = "ne"
+
+            def as_sql(self, compiler, connection):
+                lhs, lhs_params = self.process_lhs(compiler, connection)
+                rhs, rhs_params = self.process_rhs(compiler, connection)
+                params = lhs_params + rhs_params
+                return "%s <> %s" % (lhs, rhs), params
+
+        json_model_instance = JSONModel.objects.create(value={"test": "a"})
+
+        with (register_lookup(models.JSONField, NotEqual)):
+            qs = JSONModel.objects.filter(value__ne="somevalue")
+            self.assertSequenceEqual(qs, [json_model_instance])
+
     def test_custom_exact_lookup_none_rhs(self):
         """
         __exact=None is transformed to __isnull=True if a custom lookup class

[Jacob Walls]

Thanks Sarah for stubbing out a test. Sorry I didn't notice the special ingredient was Subquery and not JSONField, although I take it there could be other offenders besides Subquery?

This fails:

tests/custom_lookups/tests.py

@@ -249 +249 @@
             self.assertSequenceEqual(qs1, [a1])
             self.assertSequenceEqual(qs2, [a1])
 
+    def test_custom_lookup_with_subquery(self):
+        class NotEqual(models.Lookup):
+            lookup_name = "ne"
+
+            def as_sql(self, compiler, connection):
+                lhs, lhs_params = self.process_lhs(compiler, connection)
+                rhs, rhs_params = self.process_rhs(compiler, connection)
+                params = lhs_params + rhs_params
+                return "%s <> %s" % (lhs, rhs), params
+
+        author = Author.objects.create(name="Isabella")
+
+        with register_lookup(models.Field, NotEqual):
+            qs = Author.objects.annotate(
+                unknown_age=models.Subquery(
+                    Author.objects.filter(age__isnull=True).values("name")
+                )
+            ).filter(unknown_age__ne="Plato")
+            self.assertSequenceEqual(qs, [author])
+
     def test_custom_exact_lookup_none_rhs(self):
         """
         __exact=None is transformed to __isnull=True if a custom lookup class

[Sarah Boyce]

Thank you for the follow up Jacob 👍

[Jacob Walls]

I originally framed it as a documentation issue, given that a cleanup/optimization to harden this is probably blocked on a DEP to type-annotate the ORM, but here is a test that fails on MariaDB using only built in lookups (almost certainly on MySQL as well), so we do have a bug in core:

tests/custom_lookups/tests.py

@@ -249 +249 @@
             self.assertSequenceEqual(qs1, [a1])
             self.assertSequenceEqual(qs2, [a1])
 
+    def test_regex_lookup_with_subquery(self):
+        author = Author.objects.create(name="Isabella")
+
+        qs = Author.objects.annotate(
+            unknown_age=models.Subquery(
+                Author.objects.filter(age__isnull=True).values("name")
+            )
+        ).filter(name__regex=models.F("unknown_age"))
+        self.assertSequenceEqual(qs, [author])
+
     def test_custom_exact_lookup_none_rhs(self):
         """
         __exact=None is transformed to __isnull=True if a custom lookup class

So I think a documentation update is still worthwhile, but after that we should leave this open until we fix the regex lookup or do that DEP :D

[Jacob Walls]

​PR