[Django] #36494: Various failures for JSONField lookups and right-hand side subqueries

[Django]

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

[Django]

#36494: Various failures for JSONField lookups and right-hand side subqueries
-------------------------------------+-------------------------------------

Reporter: Jacob Walls | Owner: (none)
Type: Bug | Status: closed
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution: wontfix

Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Natalia Bidart):

* cc: Simon Charette, Sage Abdullah, Mariusz Felisiak (added)
* resolution: => wontfix
* status: new => closed

Comment:

Hello Jacob, thank you for taking the time to create this report and test
all DB backends.
I can't find evidence in our docs nor in the code base that this is a
supported feature, to me this qualifies as a new feature request, and
while I agree it feels "natural", I fear this should go thru the new
feature request process.

I'm adding others as cc since I'm happy to be overruled on this one to be
`Accepted` instead `wontfix`.
--
Ticket URL: <https://code.djangoproject.com/ticket/36494#comment:1>