id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
36494	Various failures in JSONField lookups when using expressions in right-hand side	Jacob Walls		"After #35972, I worked on a [https://github.com/jacobtylerwalls/django/commit/3c56106e4c60639f274c4acbfb582f6d1251051b 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."	Bug	new	Database layer (models, ORM)	dev	Normal			Simon Charette Sage Abdullah Mariusz Felisiak	Unreviewed	0	0	0	0	0	0