[Django] #27812: When there is a DateTimeField django does not group by

[Django]

#27812: When there is a DateTimeField django does not group by
-------------------------------------+-------------------------------------
Reporter: Fabio | Owner: nobody
García Sánchez |
Type: Bug | Status: new
Component: Database | Version: 1.10
layer (models, ORM) |
Severity: Normal | Keywords: queryset, group by
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
I have 2 models, both have almost the same fields. Just one of them has a
DateTimeField.
The problem comes when I try to group by (not on the DateTimeField) and
summarize using Sum in annotate. The model that does not have the
DateTimeField works properly, the other groups by day, the Sum is per day.

{{{
qs1 =
Bills.objects.values('tipo_documento').annotate(total=Sum('venta_neto'))
print(qs1 )
<QuerySet [{'tipo_documento': 'FV', 'total': Decimal('8419159.0000')},
{'tipo_documento': 'FY', 'total': Decimal('12500000.0000')},
{'tipo_documento': 'FY', 'total': Decimal('210000.0000')}...
}}}

this repeat the type ('tipo_documento'), each Total is per day.

On the other hand, the model without DateTimeField work properly:
{{{
qs2 =
Bills2.objects.values('tipo_documento').annotate(total=Sum('venta_neto'))
print(qs2 )
<QuerySet [{'tipo_documento': 'FV', 'total': Decimal('22367372215.1000')},
{'tipo_documento': 'NV', 'total': Decimal('-38955434.0000')},
{'tipo_documento': 'NI', 'total': Decimal('0.0000')}, {'tipo_documento':
'FY', 'total': Decimal('10155529934.9700')}]>
}}}

qs2 works properly. I have tried to do the same example with another
models and I have got the same issue.

Thanks

--
Ticket URL: <https://code.djangoproject.com/ticket/27812>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#27812: When there is a DateTimeField django does not group by
-------------------------------------+-------------------------------------

Reporter: Fabio García | Owner: nobody

Sánchez |
Type: Bug | Status: new

Component: Database layer | Version: 1.10
(models, ORM) |
Severity: Normal | Resolution:

Keywords: queryset, group by | 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 Fabio García Sánchez:

Old description:

> Thanks

New description:

I have 2 models, both have almost the same fields. Just one of them has a
DateTimeField.
The problem comes when I try to group by (not on the DateTimeField) and
summarize using Sum in annotate. The model that does not have the
DateTimeField works properly, the other groups by day, the Sum is per day.

{{{
qs1 =
Bills.objects.values('tipo_documento').annotate(total=Sum('venta_neto'))
print(qs1 )
<QuerySet [{'tipo_documento': 'FV', 'total': Decimal('8419159.0000')},
{'tipo_documento': 'FY', 'total': Decimal('12500000.0000')},
{'tipo_documento': 'FY', 'total': Decimal('210000.0000')}...
}}}

debug_toolbar shows, effectively is grouping by "document_date", but I
have just "tipo_documento" in values:
{{{
SELECT `biable_facturasbiable`.`tipo_documento`,
SUM(`biable_facturasbiable`.`venta_neto`) AS `cosa` FROM
`biable_facturasbiable` GROUP BY `biable_facturasbiable`.`tipo_documento`,
`biable_facturasbiable`.`document_date` ORDER BY
`biable_facturasbiable`.`document_date` DESC LIMIT 21
}}}

this repeat the type ('tipo_documento'), each Total is per day.

On the other hand, the model without DateTimeField work properly:
{{{
qs2 =
Bills2.objects.values('tipo_documento').annotate(total=Sum('venta_neto'))
print(qs2 )
<QuerySet [{'tipo_documento': 'FV', 'total': Decimal('22367372215.1000')},
{'tipo_documento': 'NV', 'total': Decimal('-38955434.0000')},
{'tipo_documento': 'NI', 'total': Decimal('0.0000')}, {'tipo_documento':
'FY', 'total': Decimal('10155529934.9700')}]>
}}}

qs2 works properly. I have tried to do the same example with another
models and I have got the same issue.

debug_toolbar shows:

{{{
SELECT `biable_movimientoventabiable`.`tipo_documento`,
SUM(`biable_movimientoventabiable`.`venta_neto`) AS `cosa` FROM
`biable_movimientoventabiable` GROUP BY
`biable_movimientoventabiable`.`tipo_documento` ORDER BY NULL LIMIT 21
}}}

Thanks

--

--
Ticket URL: <https://code.djangoproject.com/ticket/27812#comment:1>

[Django]

#27812: When there is a DateTimeField django does not group by
-------------------------------------+-------------------------------------

Reporter: Fabio García | Owner: nobody

Sánchez |
Type: Bug | Status: new

Component: Database layer | Version: 1.10
(models, ORM) |
Severity: Normal | Resolution:

Keywords: queryset, group by | 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 Fabio García Sánchez:

Old description:

> I have 2 models, both have almost the same fields. Just one of them has a

> DateTimeField.
> The problem comes when I try to group by (not on the DateTimeField) and
> summarize using Sum in annotate. The model that does not have the
> DateTimeField works properly, the other groups by day, the Sum is per
> day.
>
> {{{
> qs1 =
> Bills.objects.values('tipo_documento').annotate(total=Sum('venta_neto'))
> print(qs1 )
> <QuerySet [{'tipo_documento': 'FV', 'total': Decimal('8419159.0000')},
> {'tipo_documento': 'FY', 'total': Decimal('12500000.0000')},
> {'tipo_documento': 'FY', 'total': Decimal('210000.0000')}...
> }}}
>

> debug_toolbar shows, effectively is grouping by "document_date", but I
> have just "tipo_documento" in values:
> {{{
> SELECT `biable_facturasbiable`.`tipo_documento`,
> SUM(`biable_facturasbiable`.`venta_neto`) AS `cosa` FROM
> `biable_facturasbiable` GROUP BY
> `biable_facturasbiable`.`tipo_documento`,
> `biable_facturasbiable`.`document_date` ORDER BY
> `biable_facturasbiable`.`document_date` DESC LIMIT 21
> }}}
>

> this repeat the type ('tipo_documento'), each Total is per day.
>
> On the other hand, the model without DateTimeField work properly:
> {{{
> qs2 =
> Bills2.objects.values('tipo_documento').annotate(total=Sum('venta_neto'))
> print(qs2 )
> <QuerySet [{'tipo_documento': 'FV', 'total':
> Decimal('22367372215.1000')}, {'tipo_documento': 'NV', 'total':
> Decimal('-38955434.0000')}, {'tipo_documento': 'NI', 'total':
> Decimal('0.0000')}, {'tipo_documento': 'FY', 'total':
> Decimal('10155529934.9700')}]>
> }}}
>
> qs2 works properly. I have tried to do the same example with another
> models and I have got the same issue.
>

> debug_toolbar shows:
>
> {{{
> SELECT `biable_movimientoventabiable`.`tipo_documento`,
> SUM(`biable_movimientoventabiable`.`venta_neto`) AS `cosa` FROM
> `biable_movimientoventabiable` GROUP BY
> `biable_movimientoventabiable`.`tipo_documento` ORDER BY NULL LIMIT 21
> }}}
>

> Thanks

New description:

I have 2 models, both have almost the same fields. Just one of them has a
DateTimeField.
The problem comes when I try to group by (not on the DateTimeField) and
summarize using Sum in annotate. The model that does not have the
DateTimeField works properly, the other groups by day, the Sum is per day.

{{{
qs1 =
Bills.objects.values('tipo_documento').annotate(total=Sum('venta_neto'))
print(qs1 )
<QuerySet [{'tipo_documento': 'FV', 'total': Decimal('8419159.0000')},
{'tipo_documento': 'FY', 'total': Decimal('12500000.0000')},
{'tipo_documento': 'FY', 'total': Decimal('210000.0000')}...
}}}

debug_toolbar shows, effectively is grouping by "document_date", but I

have just "tipo_documento" in values:
{{{
SELECT `biable_facturasbiable`.`tipo_documento`,

SUM(`biable_facturasbiable`.`venta_neto`) AS `Total` FROM

`biable_facturasbiable` GROUP BY `biable_facturasbiable`.`tipo_documento`,
`biable_facturasbiable`.`document_date` ORDER BY
`biable_facturasbiable`.`document_date` DESC LIMIT 21
}}}

this repeat the type ('tipo_documento'), each Total is per day.

On the other hand, the model without DateTimeField work properly:
{{{
qs2 =
Bills2.objects.values('tipo_documento').annotate(total=Sum('venta_neto'))
print(qs2 )
<QuerySet [{'tipo_documento': 'FV', 'total': Decimal('22367372215.1000')},
{'tipo_documento': 'NV', 'total': Decimal('-38955434.0000')},
{'tipo_documento': 'NI', 'total': Decimal('0.0000')}, {'tipo_documento':
'FY', 'total': Decimal('10155529934.9700')}]>
}}}

qs2 works properly. I have tried to do the same example with another
models and I have got the same issue.

debug_toolbar shows:

{{{
SELECT `biable_movimientoventabiable`.`tipo_documento`,

SUM(`biable_movimientoventabiable`.`venta_neto`) AS `Total` FROM

`biable_movimientoventabiable` GROUP BY
`biable_movimientoventabiable`.`tipo_documento` ORDER BY NULL LIMIT 21
}}}

Thanks

--

--
Ticket URL: <https://code.djangoproject.com/ticket/27812#comment:2>

[Django]

#27812: When there is a DateTimeField django does not group by
-------------------------------------+-------------------------------------

Reporter: Fabio García | Owner: nobody

Sánchez |
Type: Bug | Status: new

Component: Database layer | Version: 1.10
(models, ORM) |
Severity: Normal | Resolution:

Keywords: queryset, group by | Triage Stage:

| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Tim Graham):

Could you please provide the minimal models to reproduce it? Are you using
`Meta.ordering`? If so, you might be tripped up by the issue described in
#14357.

--
Ticket URL: <https://code.djangoproject.com/ticket/27812#comment:3>

[Django]

#27812: When there is a DateTimeField django does not group by
-------------------------------------+-------------------------------------

Reporter: Fabio García | Owner: nobody

Sánchez |
Type: Bug | Status: new

Component: Database layer | Version: 1.10
(models, ORM) |
Severity: Normal | Resolution:

Keywords: queryset, group by | Triage Stage:

| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Fabio García Sánchez):

Replying to [comment:3 Tim Graham]:

> Could you please provide the minimal models to reproduce it? Are you
using `Meta.ordering`? If so, you might be tripped up by the issue
described in #14357.

Tim thanks so much, that was the problem. I took away the ordering and it
works. I have not seen completely the issue #14357 but just with that it
works. Thanks.

--
Ticket URL: <https://code.djangoproject.com/ticket/27812#comment:4>

[Django]

#27812: When there is a DateTimeField django does not group by
-------------------------------------+-------------------------------------

Reporter: Fabio García | Owner: nobody
Sánchez |

Type: Bug | Status: closed

Component: Database layer | Version: 1.10
(models, ORM) |

Severity: Normal | Resolution: fixed

Keywords: queryset, group by | Triage Stage:

| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Fabio García Sánchez):

* status: new => closed
* resolution: => fixed

--
Ticket URL: <https://code.djangoproject.com/ticket/27812#comment:5>

[Django]

#27812: When there is a DateTimeField django does not group by
-------------------------------------+-------------------------------------

Reporter: Fabio García | Owner: nobody
Sánchez |
Type: Bug | Status: closed
Component: Database layer | Version: 1.10
(models, ORM) |

Severity: Normal | Resolution: invalid

Keywords: queryset, group by | Triage Stage:

| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Tim Graham):

* status: new => closed

* resolution: => invalid

--
Ticket URL: <https://code.djangoproject.com/ticket/27812#comment:5>