id summary reporter owner description type status component version severity resolution keywords cc stage has_patch needs_docs needs_tests needs_better_patch easy ui_ux 33257 Case() and ExpressionWrapper() doesn't work with DecimalField on SQLite. Matthijs Kooijman Matthijs Kooijman "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." Bug closed Database layer (models, ORM) dev Normal fixed Ready for checkin 1 0 0 0 0 0