#36453: 5.2.3 introduces a regression when using `Value(None,
output_field=JSONField()` in a `When` clause.
-------------------------------------+-------------------------------------
Reporter: Thomas | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Thomas:
Old description:
> We've tried to upgrade to 5.2.3 and several of our tests started failing.
> We've pinpointed the problem to one query which uses `Value(None,
> output_field=JSONField()` in a `When` clause. Here's a minimal
> reproducible example (with a `WHERE` clause too, to highlight the
> difference in behavior):
>
> {{{
> from django.db import connection
> from django.db.models import (
> BigIntegerField,
> Case,
> F,
> JSONField,
> Model,
> Value,
> When,
> )
> from django.db.models.functions import Cast
>
> class Foo(Model):
> bar = BigIntegerField(blank=True, null=True)
> json_field = JSONField(blank=True, null=True)
>
> if __name__ == '__main__':
> Foo.objects.filter(
> json_field__key_1=Value(None, output_field=JSONField())
> ).update(
> bar=Case(
> When(
> json_field__key_2=Value(None, output_field=JSONField()),
> then=None
> ),
> default=Cast(F('json_field__key_2'), BigIntegerField()),
> output_field=BigIntegerField(),
> ),
> )
>
> print(connection.queries[-1]['sql'])
> }}}
>
> We ran this code with 5.2.2 (with this version our test suite passes) and
> 5.2.3 (with this version our test suite fails) and got this:
> {{{
> -- Generated with Django 5.2.2
> UPDATE "polls_foo"
> SET "bar" = CASE WHEN (
> ("polls_foo"."json_field" -> 'key_2') = 'null'::jsonb -- JSON
> "null"
> )
> THEN NULL
> ELSE (("polls_foo"."json_field" -> 'key_2'))::bigint
> END
> WHERE ("polls_foo"."json_field" -> 'key_1') = 'null'::jsonb;
>
> -- Generated with Django 5.2.3
> UPDATE "polls_foo"
> SET "bar" = CASE WHEN (
> ("polls_foo"."json_field" -> 'key_2') = NULL -- SQL NULL
> )
> THEN NULL
> ELSE (("polls_foo"."json_field" -> 'key_2'))::bigint
> END
> WHERE ("polls_foo"."json_field" -> 'key_1') = 'null'::jsonb;
> }}}
>
> Notice how the `WHEN` clause differs, but the `WHERE` clause stays the
> same, despite both using the same `Value(None, output_field=JSONField())`
> syntax.
New description:
We've tried to upgrade to 5.2.3 and several of our tests started failing.
We've pinpointed the problem to one query which uses `Value(None,
output_field=JSONField()` in a `When` clause. Here's a minimal
reproducible example (with a `WHERE` clause too, to highlight the
difference in behavior):
{{{
from django.db import connection
from django.db.models import (
BigIntegerField,
Case,
F,
JSONField,
Model,
Value,
When,
)
from django.db.models.functions import Cast
class Foo(Model):
bar = BigIntegerField(blank=True, null=True)
json_field = JSONField(blank=True, null=True)
if __name__ == '__main__':
Foo.objects.filter(
json_field__key_1=Value(None, output_field=JSONField())
).update(
bar=Case(
When(
json_field__key_2=Value(None, output_field=JSONField()),
then=None
),
default=Cast(F('json_field__key_2'), BigIntegerField()),
output_field=BigIntegerField(),
),
)
print(connection.queries[-1]['sql'])
}}}
We ran this code with 5.2.2 (with this version our test suite passes) and
5.2.3 (with this version our test suite fails) and got this:
{{{
-- Generated with Django 5.2.2
UPDATE "polls_foo"
SET "bar" = CASE WHEN (
("polls_foo"."json_field" -> 'key_2') = 'null'::jsonb -- JSON
"null"
)
THEN NULL
ELSE (("polls_foo"."json_field" -> 'key_2'))::bigint
END
WHERE ("polls_foo"."json_field" -> 'key_1') = 'null'::jsonb;
-- Generated with Django 5.2.3
UPDATE "polls_foo"
SET "bar" = CASE WHEN (
("polls_foo"."json_field" -> 'key_2') = NULL -- SQL NULL
)
THEN NULL
ELSE (("polls_foo"."json_field" -> 'key_2'))::bigint
END
WHERE ("polls_foo"."json_field" -> 'key_1') = 'null'::jsonb;
}}}
Notice how the `WHEN` clause differs, but the `WHERE` clause stays the
same, despite both using the same `Value(None, output_field=JSONField())`
syntax.
Edit: If that makes any difference, we're using PostgreSQL as a backend.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/36453#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To view this discussion visit
https://groups.google.com/d/msgid/django-updates/010701975be1de5b-566e2bd1-1d68-425d-b5c7-6c9216cee2e4-000000%40eu-central-1.amazonses.com.
