Various failures for JSONField lookups and right-hand side subqueries

[Jacob Walls]

After #35972, I worked on a ​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 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 (​source):

   django.db.utils.ProgrammingError: the query has 2 placeholders but 3 parameters were passed
   

2. __has_keys or __has_any_keys:

     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:

     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:

     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:

   sqlite3.OperationalError: malformed JSON
   

---

Additional failure on MySQL:

6. __in:

   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.

[Natalia Bidart]

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.