#36494: Various failures for JSONField lookups and right-hand side subqueries
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: dev | Severity: Normal
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
After #35972, I worked on a
[https://github.com/jacobtylerwalls/django/pull/2/files test] to verify
support for `JSONField` lookups against right-hand side subqueries.
Support is mostly complete on Postgres, but largely incomplete on other
backends.
I'm suggesting we close this ticket (in separate PRs) with a combination
of adding support where possible (perhaps for `__range`?), with failing at
the python layer with more appropriate exceptions, or possibly adding a
feature flag to guard some failures. I agree with the
[https://code.djangoproject.com/ticket/31779#comment:3 sentiment] that we
cannot maintain endless feature flags for database quirks or insulate
users from every bug in their database vendor, but some of these failures
seem to me to be on the Django side.
Postgres:
1. `__range`
([https://github.com/django/django/blob/6df19412aabb7d969f5eab4b2ff41269de89b233/django/db/models/lookups.py#L649
source]):
{{{#!py
django.db.utils.ProgrammingError: the query has 2 placeholders but 3
parameters were passed
}}}
2. `__has_keys` or `__has_any_keys`:
{{{#!py
File "/django/source/django/db/models/fields/json.py", line 278, in
get_prep_lookup
return [str(item) for item in self.rhs]
^^^^^^^^
TypeError: 'Subquery' object is not iterable
}}}
----
SQLite & MySQL:
1. `__range`: same as Postgres
2. `__has_keys` or `__has_any_keys`: same as Postgres
3. `__has_key`:
{{{#!py
File "/django/source/django/db/models/fields/json.py", line 186, in
compile_json_path_final_key
return ".%s" % json.dumps(key_transform)
^^^^^^^^^^^^^^^^^^^^^^^^^
TypeError: Object of type Subquery is not JSON serializable
}}}
4. `__gt`, `__gte`, `__lt`, `__lte`:
{{{#!py
File "/django/source/django/db/models/fields/json.py", line 610, in
process_rhs
rhs_params = [json.loads(value) for value in rhs_params]
^^^^^^^^^^^^^^^^^
json.decoder.JSONDecodeError: Expecting value: line 1 column 1 (char 0)
}}}
5. `__exact`:
{{{#!py
sqlite3.OperationalError: malformed JSON
}}}
----
Additional failure on MySQL:
6. `__in`:
{{{#!py
django.db.utils.NotSupportedError: (1235, "This version of MySQL doesn't
yet support 'LIMIT & IN/ALL/ANY/SOME subquery'")
}}}
----
Oracle:
1. `__range`: same as others (database exception)
2. `__has_keys` or `__has_any_keys`: same as others
3. `__has_key`: same as SQLite & MySQL
4. Certain text lookups (`__icontains`, `__startswith`, `__istartswith`,
`__endswith`, `__iendswith`): return wrong results, they appear to return
any results having the key on the left-hand side regardless of the
contents of the right-hand side, refer to the modifications commented out
in the linked test.
----
This is all pretty niche, but if the context helps, I could envision
realistic use cases for `__has_keys=queryset`, as I work on a project that
uses stringified UUIDs identifying other entities as JSON keys.
--
Ticket URL: <https://code.djangoproject.com/ticket/36494>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/django-updates/01070197e0af8de1-bba0f8d7-39a3-461c-b485-c9c72af6d84b-000000%40eu-central-1.amazonses.com.
