Querying "null" on key transforms for JSONField returns wrong results on SQLite.

[Johnny Metz]

Let's say I have a Django model with a JSONField:

class Event(models.Model):
    data = models.JSONField()

And I create the following objects:

event1 = Event.objects.create(data={"key": None})
event2 = Event.objects.create(data={"key": "null"})

In Django 3.2.13, the following queries return some results:

Event.objects.filter(data__key=Value("null"))
# [event1]

Event.objects.filter(data__key="null")
# [event2]

In Django 4.0.5, the same queries return different results:

Event.objects.filter(data__key=Value("null"))
# [event1, event2]

Event.objects.filter(data__key="null")
# [event1, event2]

The ​Django docs aren't clear which results are correct. I would lean towards the v3 results.

I'm happy to work on a patch if people think this is a bug in v4.

[Mariusz Felisiak]

Thanks for the report. Regression in 71ec102b01fcc85acae3819426a4e02ef423b0fa. This is also inconsistent with other backends.

[Gregory]

Seems like a type-juggling routine introduced in 4.0.0
I am totally into type-juggling but this case, and actually blocking issue is a great example of the general confusion that a seemingly *simplification* - may lead to hold back on tech dev.

TL;DR:

• consider removing type juggling
• keep principles up, regarding data type comparison

[Mariusz Felisiak]

I tried to fix this few times in the last two weeks, unfortunately, I don't see a proper way to distinguish between {"x": "null"} and {"x": NULL} extracted from the JSON field (the same for boolean values). Mainly due to the lack of proper database types in SQLite. In 71ec102b01fcc85acae3819426a4e02ef423b0fa we tried to use

CASE
    WHEN JSON_TYPE("model"."json_field", $."key") IN ('null', 'false', 'true')
         THEN JSON_TYPE("model"."json_field", $."key")
    ELSE JSON_EXTRACT("model"."json_field", $."key")
END

which fixed #32483 but also introduced this regression because for both NULL and "null" it returns 'null'.

Quo vadis? 🤷 We have two options:

• document this as a SQLite caveat,
• revert 71ec102b01fcc85acae3819426a4e02ef423b0fa and reintroduce another SQLite caveat.

[Carlton Gibson]

document this as a SQLite caveat,

I'd go with this. I think storing "null" is quite niche, and easily worked around.

[Mariusz Felisiak]

Replying to Carlton Gibson:

I'd go with this. I think storing "null" is quite niche, and easily worked around.

Just to be clear, this issue is for all three "true", "false", and "null".

[Carlton Gibson]

Sure, yes. My feeling is that it's better to resolve #32483, which was 71ec102b01fcc85acae3819426a4e02ef423b0fa. Most times I want True/False/None, and I can serialise those if I need to. (That's just a +1 towards option 1 in response to the What to do?)

[Mariusz Felisiak]

​PR

[GitHub <noreply@…>]

In e20e5d1:

Fixed #33820 -- Doc'd "true"/"false"/"null" caveat for JSONField key transforms on SQLite.

Thanks Johnny Metz for the report.

Regression in 71ec102b01fcc85acae3819426a4e02ef423b0fa.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 265c3eb:

[4.1.x] Fixed #33820 -- Doc'd "true"/"false"/"null" caveat for JSONField key transforms on SQLite.

Thanks Johnny Metz for the report.

Regression in 71ec102b01fcc85acae3819426a4e02ef423b0fa.
Backport of e20e5d1557785ba71e8ef0ceb8ccb85bdc13840a from main

[Mariusz Felisiak <felisiak.mariusz@…>]

In f78b18f9:

[4.0.x] Fixed #33820 -- Doc'd "true"/"false"/"null" caveat for JSONField key transforms on SQLite.

Thanks Johnny Metz for the report.

Regression in 71ec102b01fcc85acae3819426a4e02ef423b0fa.
Backport of e20e5d1557785ba71e8ef0ceb8ccb85bdc13840a from main