#33257: Comparisons against DecimalField annotations behave inconsistently on
sqlite
-------------------------------------+-------------------------------------
Reporter: Matthijs | Owner: nobody
Kooijman |
Type: Bug | Status: new
Component: Database | Version: dev
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
I noticed that, on sqlite, some comparisons against DecimalField
annotations behave unexpectedly, in particular when wrapping a
DecimalField value in a Case/When or ExpressionWrapper. I suspect that
there might be some inconsistencies in the type conversions here somehow.
I've created a few testcase to illustrate the problem on current git main:
https://github.com/matthijskooijman/django/commit/3470b98b42c39fd9a9a4e1443341f16780da7a98
and see below.
{{{
@override_settings(DEBUG=True)
def test_00compare_field(self):
"""Comparing a Case annotation wrapping a field to a literal
works."""
Foo.objects.create(a='', d=1)
try:
Foo.objects.filter(d__gt=0).get()
finally:
from django.db import connection
print(connection.queries[-1]['sql'])
@override_settings(DEBUG=True)
def test_01compare_annotation_value_literal(self):
"""Comparing a literal annotation using Value to a literal
works."""
# Fields are not actually used here
Foo.objects.create(a='', d=0)
try:
Foo.objects.annotate(
x=models.Value(1,
output_field=models.fields.DecimalField(max_digits=1, decimal_places=0)),
).filter(x__gt=0).get()
finally:
from django.db import connection
print(connection.queries[-1]['sql'])
@override_settings(DEBUG=True)
def test_02compare_annotation_expressionwrapper_literal(self):
"""Comparing a literal annotation using ExpressionWraper and Value
to a literal works."""
# Fields are not actually used here
Foo.objects.create(a='', d=0)
try:
Foo.objects.annotate(
x=models.ExpressionWrapper(
models.Value(1),
output_field=models.fields.DecimalField(max_digits=1,
decimal_places=0),
),
).filter(x__gt=0).get()
finally:
from django.db import connection
print(connection.queries[-1]['sql'])
@override_settings(DEBUG=True)
def test_03compare_case_annotation(self):
"""Comparing a Case annotation wrapping a field to a literal
works."""
Foo.objects.create(a='', d=1)
try:
Foo.objects.annotate(
x=models.Case(models.When(a='', then=models.F('d'))),
).filter(x__gt=0).get()
finally:
from django.db import connection
print(connection.queries[-1]['sql'])
}}}
- test_00compare_field compares a field directly with a literal,
which
works.
- test_01compare_annotation_value_literal adds a literal annotation
using just
Value and then compares it, which also works.
- test_02compare_annotation_expressionwrapper_literal adds a literal
annotation using Value wrapped in ExpressionWrapper, which does not
work becomes a literal int, rather than a string like the compared
value.
- test_03compare_case_annotation wraps the field in a case/when and
then
compares it, which also does not work (maybe the CASE changes the
type?)
Running these testcases against sqlite gives:
{{{
SELECT "model_fields_foo"."id", "model_fields_foo"."a",
"model_fields_foo"."d" FROM "model_fields_foo" WHERE
"model_fields_foo"."d" > '0' LIMIT 21
.SELECT "model_fields_foo"."id", "model_fields_foo"."a",
"model_fields_foo"."d", CAST('1' AS NUMERIC) AS "x" FROM
"model_fields_foo" WHERE CAST('1' AS NUMERIC) > '0' LIMIT 21
.SELECT "model_fields_foo"."id", "model_fields_foo"."a",
"model_fields_foo"."d", 1 AS "x" FROM "model_fields_foo" WHERE 1 > '0'
LIMIT 21
ESELECT "model_fields_foo"."id", "model_fields_foo"."a",
"model_fields_foo"."d", CASE WHEN "model_fields_foo"."a" = '' THEN
"model_fields_foo"."d" ELSE NULL END AS "x" FROM "model_fields_foo" WHERE
CASE WHEN ("model_fields_foo"."a" = '') THEN "model_fields_foo"."d" ELSE
NULL END > '0' LIMIT 21
E.s...........
======================================================================
ERROR: test_02compare_annotation_expressionwrapper_literal
(model_fields.test_decimalfield.DecimalFieldTests)
Comparing a literal annotation using ExpressionWraper and Value to a
literal works.
----------------------------------------------------------------------
Traceback (most recent call last):
File "/home/matthijs/docs/src/upstream/django/django/test/utils.py",
line 437, in inner
return func(*args, **kwargs)
File
"/home/matthijs/docs/src/upstream/django/tests/model_fields/test_decimalfield.py",
line 142, in test_02compare_annotation_expressionwrapper_literal
Foo.objects.annotate(
File
"/home/matthijs/docs/src/upstream/django/django/db/models/query.py", line
441, in get
raise self.model.DoesNotExist(
model_fields.models.Foo.DoesNotExist: Foo matching query does not exist.
======================================================================
ERROR: test_03compare_case_annotation
(model_fields.test_decimalfield.DecimalFieldTests)
Comparing a Case annotation wrapping a field to a literal works.
----------------------------------------------------------------------
Traceback (most recent call last):
File "/home/matthijs/docs/src/upstream/django/django/test/utils.py",
line 437, in inner
return func(*args, **kwargs)
File
"/home/matthijs/docs/src/upstream/django/tests/model_fields/test_decimalfield.py",
line 154, in test_03compare_case_annotation
Foo.objects.annotate(
File
"/home/matthijs/docs/src/upstream/django/django/db/models/query.py", line
441, in get
raise self.model.DoesNotExist(
model_fields.models.Foo.DoesNotExist: Foo matching query does not exist.
----------------------------------------------------------------------
}}}
Note in the printed queries that the 0 value that is compared with is a
string in the query (`'0'`), while the ExpressionWrappered value is just a
plain number (`1`). The Value without ExpressionWrapper has a cast to
NUMERIC that I suspect makes that case work?
Also note that the DEBUG stuff and try-catch is only added to capture the
query that is being generated, it is not needed to trigger the problem. I
tried printing the `QuerySet.query` attribute first, but that seems to
hide the quotes around the literal 0 in the query, so took me a while to
realize what was happening. The numbers in the testcase names are to
ensure the SQL queries are printed in a recognizable order.
All four testcases work on MySQL, there the 0 value is written in the
query without the quotes, and the cast to NUMERIC is also missing.
I suspect this issue is highly similar to #18247, and can a fix can
probably build on the fix for that issue.
--
Ticket URL: <https://code.djangoproject.com/ticket/33257>
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/059.f69237dabdff1640cbf40f7b4670b72a%40djangoproject.com.
