JSONIn lookup crashes or returns wrong result for primitives
On databases besides Postgres (which has a native JSONField), a branch is taken that does not check the output_field for a Value expression. (Primitive strings, ints, bools, etc., might have other output_fields than JSONField.)
class Person(models.Model):
data = models.JSONField(null=True)
def run():
instance = Person.objects.create(data="primitive")
qs = Person.objects.filter(data__in=[models.Value("primitive")])
print(qs)
This query worked(*) on 6.0, but on 6.1 it returns the wrong result (MariaDB) or crashes (SQLite). Notice for SQLite here, the string "primitive" should not be provided to JSON_EXTRACT():
SELECT "app_person"."id", "app_person"."data" FROM "app_person" WHERE (CASE WHEN JSON_TYPE("app_person"."data", $) IN ('null','false','true') THEN JSON_TYPE("app_person"."data", $) ELSE JSON_EXTRACT("app_person"."data", $) END) IN (JSON_EXTRACT(primitive, '$'))
django.db.utils.OperationalError: malformed JSON
(*) -- On SQLite, you'd have to adjust the second query to use extra quotes, because of some quote weirdness, possibly similar to #32491?
Change History
(6)
| Owner: |
set to Clifford Gama
|
| Status: |
new → assigned
|
| Has patch: |
set
|
| Triage Stage: |
Unreviewed → Accepted
|
| Patch needs improvement: |
set
|
| Patch needs improvement: |
unset
|
| Triage Stage: |
Accepted → Ready for checkin
|
| Resolution: |
→ fixed
|
| Status: |
assigned → closed
|
Thanks Jacob for the report!
When I wrote the regression test, I found that the case for Boolean primitives wrapped in
Valuewith nooutput_fieldwas failing prior to refs #36689 which introduces the reported regression. I used Claude to run tests against different points in the history to get a clear picture of what was supported prior to #36689 and what wasn't. Here's what I found:Value(<primitive>)inJSONField.__in— by commit and backendJSONIn)Legend: PASS = correct row returned · FAIL = wrong result, silently returns
[](no exception) · ERROR = raises a DB-level exception.Errors by cell, where relevant:
OperationalError: malformed JSON(3146, 'Invalid data type for JSON data in argument 1 to function json_extract...')(str:3141, invalid JSON text)operator does not exist: jsonb = <type>— unaffected by this whole history, see belowORA-61800: invalid boolean literalORA-01722: unabntaining '[' to a number(collides with the[1,[2]]row'sCOALESCE(JSON_VALUE,JSON_QUERY)text extraction)ORA-22848: cannot use NCLOB type as comparison keyCaveats:
mysql_is_mariadb=False_UNQUOTE()` branch was not exercised against an actualMariaDB server.ERRORin every row/commit is out of scope --has_native_json_field=Trueskips this code path entirely regardless of commit, reflecting a separate, pre-existing gap (untypedValue()vs.jsonboperators) unrelated to #37377.JSONIn's OracleCOALESCE(JSON_VALUE(...), JSON_QUERY(...))LHS extraction, because that always yields text, and an untypedValue()'s raw (non-text) bind collides with it once any row holds a container value. `9b8anged *how* Oracle failed for strings (silent wrong result →still wrong, no longer erroring) — they didn't newly break anything there.