[Django] #24662: Sum() returns True/False when used with BooleanField & MySQL
Django
----------------------------------------------+--------------------
Reporter: chriskief | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer (models, ORM) | Version: 1.8
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
When using Sum() on a BooleanField when only 1 record is present, the ORM
returns True/False rather than 1/0. I've tested with MySQL and SQLite,
only MySQL exhibits the bug.
This is a change in behavior from previous versions of Django where a
Decimal would be returned.
Environment:
MySQL - 5.6.24
MySQL-python==1.2.5
Code to reproduce (new project / app):
{{{
class Example(models.Model):
foo = models.BooleanField(default=True)
}}}
Simple query to demonstrate the difference:
{{{
# add a single row
a = Example(foo=True)
a.save()
# query
Example.objects.all().aggregate(count=Count('foo'), sum=Sum('foo'))
# results
# notice Django 1.8 + MySQL
Django 1.7.7 + MySQL
{'count': 1, 'sum': Decimal('1')}
Django 1.7.7 + SQLite
{'count': 1, 'sum': 1}
Django 1.8 + MySQL
{'count': 1, 'sum': True}
Django 1.8 + SQLite
{'count': 1, 'sum': 1}
# add a second row
a = Example(foo=True)
a.save()
# query
Example.objects.all().aggregate(count=Count('foo'), sum=Sum('foo'))
# results
# notice Django 1.8 + MySQL now returns a decimal
Django 1.7.7 + MySQL
{'count': 2, 'sum': Decimal('2')}
Django 1.7.7 + SQLite
{'count': 2, 'sum': 2}
Django 1.8 + MySQL
{'count': 2, 'sum': Decimal('2')}
Django 1.8 + SQLite
{'count': 2, 'sum': 2}
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24662>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: chriskief | Owner: nobody
Type: Uncategorized | Status: new
(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
Changes (by chriskief):
* needs_docs: => 0
* needs_tests: => 0
* needs_better_patch: => 0
Old description:
New description:
When using Sum() on a BooleanField when only 1 record is present, the ORM
returns True/False rather than 1/0. I've tested with MySQL and SQLite,
only MySQL exhibits the bug.
This is a change in behavior from previous versions of Django where a
Decimal would be returned.
Environment:
MySQL - 5.6.24
MySQL-python==1.2.5
Code to reproduce (new project / app):
{{{
class Example(models.Model):
foo = models.BooleanField(default=True)
}}}
Simple query to demonstrate the difference:
{{{
# add a single row
a = Example()
a.save()
# query
Example.objects.all().aggregate(count=Count('foo'), sum=Sum('foo'))
# results
# notice Django 1.8 + MySQL
Django 1.7.7 + MySQL
{'count': 1, 'sum': Decimal('1')}
Django 1.7.7 + SQLite
{'count': 1, 'sum': 1}
Django 1.8 + MySQL
{'count': 1, 'sum': True}
Django 1.8 + SQLite
{'count': 1, 'sum': 1}
# add a second row
a = Example()
a.save()
# query
Example.objects.all().aggregate(count=Count('foo'), sum=Sum('foo'))
# results
# notice Django 1.8 + MySQL now returns a decimal
Django 1.7.7 + MySQL
{'count': 2, 'sum': Decimal('2')}
Django 1.7.7 + SQLite
{'count': 2, 'sum': 2}
Django 1.8 + MySQL
{'count': 2, 'sum': Decimal('2')}
Django 1.8 + SQLite
{'count': 2, 'sum': 2}
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/24662#comment:1>
Django
Reporter: chriskief | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by chriskief):
* type: Uncategorized => Bug
--
Ticket URL: <https://code.djangoproject.com/ticket/24662#comment:2>
Django
Reporter: chriskief | Owner: marco-
| santamaria
Type: Bug | Status: assigned
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by marco-santamaria):
* owner: nobody => marco-santamaria
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/24662#comment:3>
Django
Reporter: chriskief | Owner: marco-
| santamaria
Type: Bug | Status: assigned
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by jarshwah):
* cc: josh.smeaton@… (added)
Comment:
I'm not sure what the correct behaviour should be, but I don't think True
is correct. I'm surprised the previous behaviour returned a Decimal -
that's about the most unlikely type I would think to be returned.
SUM(bool) only works on databases that use numbers to represent booleans
(1, 0). You can get exactly the same behaviour by doing COUNT(bool) and
that will be properly supported. You could also try
`.aggregate(Sum('bool_field', output_type=IntegerField()))` if you really
wanted to keep that behaviour.
I'm tempted to close this as wontfix, because SUM(bool) only ever worked
by accident, and as a by-product of internal representation of some
backends. The correct way to calculate the result is by using count. Can
you convince me that this behaviour should be supported?
--
Ticket URL: <https://code.djangoproject.com/ticket/24662#comment:4>
Django
Reporter: chriskief | Owner: marco-
| santamaria
Type: Bug | Status: assigned
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by chriskief):
True doesn't seem like the correct behavior to me either, but I'm also
surprised that this worked correctly in the past now that I understand
what's going on.
My gut says to lean towards consistency in behavior and in that case, it's
strange that True is returned when there's a single row in the database,
and a Decimal is returned when there is more than one row. In addition, a
numeric value is returned in all cases when using SQLite.
Should probably take a look at what Postgres and Oracle return as well.
--
Ticket URL: <https://code.djangoproject.com/ticket/24662#comment:5>
Django
Reporter: chriskief | Owner: marco-
| santamaria
Type: Bug | Status: assigned
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by chriskief):
Looks like Postgres throws an error:
Django 1.8 + PostgreSQL
ProgrammingError: function sum(boolean) does not exist
Django 1.7 + PostgreSQL
ProgrammingError: function sum(boolean) does not exist
--
Ticket URL: <https://code.djangoproject.com/ticket/24662#comment:6>
Django
Reporter: chriskief | Owner: marco-
| santamaria
Type: Bug | Status: assigned
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by jarshwah):
* stage: Unreviewed => Accepted
Comment:
SUM(bool_field) on Oracle will return 1 also, because bools in Oracle are
just a bit (1 or 0). Postgres has a specific bool type, and you can't sum
a True/False without an implicit conversion to int. This is why I say
SUM(bool) is only accidentally supported for a subset of databases. The
field type that is returned is based on the backend get_db_converters and
the actual values that come back.
Now, something needs to change here. What I would propose to do is:
1) Document that SUM(bool) is not supported in some way. Perhaps by
documenting that SUM can only be applied to numeric types.
2) Raise a warning if SUM(bool_field) is used with the suggestion to
convert it to COUNT(bool_field)
3) Begin to deprecate the ability to SUM(bool_field) (an extension of 2)
above).
--
Ticket URL: <https://code.djangoproject.com/ticket/24662#comment:7>
Django
Reporter: chriskief | Owner: marco-
| santamaria
Type: Bug | Status: assigned
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by akaariai):
`Count(bool_field)` isn't the same as `sum(bool_field)`, what you need is
`sum(case when true then 1 else 0 end)`. I think it is at least worth
considering if we should support min, max and sum for boolean fields.
Sum(bool_field) would be automatically converted to `sum(case when...)`
expression, min would be `min(case when true then 1 else 0 end)` and the
return value would be then converted to either True of False, likewise for
max().
The reason why I think this might be worth supporting is that this is what
you get in Python. For example `sum([True, False, False, True]) == 2` and
min and max work similarly to the above explanation, too.
--
Ticket URL: <https://code.djangoproject.com/ticket/24662#comment:8>
Django
Reporter: chriskief | Owner: marco-
| santamaria
Type: Bug | Status: assigned
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by jarshwah):
Ooh you're right. I was incorrectly thinking that COUNT(0) would be 0, but
it's 1. I don't mind the solution you've put forward because it's database
agnostic and not at all dependent on the underlying storage mechanism. It
will require the Sum aggregate to be a little more complicated though.
Resolve Expression will need to inspect the output_type, and return a
SumBoolean (or MinBoolean.. etc) which will have some small impact on non-
boolean SUMs. Is this worth doing when users are able to create their own
SumBoolean (or just use Count(Case(when..)) directly)?
--
Ticket URL: <https://code.djangoproject.com/ticket/24662#comment:9>
Django
Reporter: chriskief | Owner: marco-
| santamaria
Type: Bug | Status: assigned
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by akaariai):
I'm not sure if this is actually worth doing. It would make
Sum(bool_field) work like sum(bool_values) works in Python, and it
wouldn't take that much code to do. On the other hand, there is something
to be said for doing the Count explicitly.
We should also solve what to do in 1.8. Should 1.8 work like 1.7? We could
also disallow Sum(bool_field), or perhaps we want to push the above idea
to 1.8, too?
My initial feeling is that we should try what the solution actually looks
like. The actual code portion of the changes might be surprisingly small.
--
Ticket URL: <https://code.djangoproject.com/ticket/24662#comment:10>
Django
Reporter: Chris Kief | Owner: Marco
| Santamaria
Type: Bug | Status: closed
Component: Database layer | Version: 1.8
(models, ORM) |
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Simon Charette):
* status: assigned => closed
* resolution: => wontfix
Comment:
Given this was a regression in a version that has long been unsupported
and that the MySQL peculiarity can be worked around by using
`Sum(Case(...))` I also believe this isn't worth doing at this point.
--
Ticket URL: <https://code.djangoproject.com/ticket/24662#comment:11>