Opened 14 years ago

Closed 14 years ago

Last modified 14 years ago

#13123 closed (duplicate)

Extra params + aggregation creates incorrect SQL.

Reported by: Paul Garner Owned by: nobody
Component: Uncategorized Version: 1.2-beta
Severity: Keywords:
Cc: ego@… Triage Stage: Unreviewed
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

If you have a query with both annotation/aggregation (and thus a GROUP BY clause) *and* an extra(select) part ...the generated sql copies the whole subselect from the extra field into the group by portion of the query, when just the column alias would be correct.

To illustrate, a queryset like this:

qs.annotate(stock=Sum('variants__stock')).filter(Q(stock__gt=0)).extra(
    select={'op_option_group_id': '''
        SELECT op.option_group_id FROM store_optionedproduct op, store_product WHERE op.product_ptr_id=store_product.id LIMIT 1
    '''})

generates this sql:

SELECT
    (SELECT op.option_group_id FROM store_optionedproduct op, store_product WHERE op.product_ptr_id=store_product.id) AS `op_option_group_id`
    FROM `store_product`
    LEFT OUTER JOIN `store_productvariant` ON
        (`store_product`.`id` = `store_productvariant`.`product_id`)
        WHERE (`store_product`.`status` IN (1))
        GROUP BY `store_product`.`id`,
-->     SELECT op.option_group_id FROM store_optionedproduct op, store_product WHERE op.product_ptr_id=store_product.id
        HAVING SUM(`store_productvariant`.`stock`) > 0 ORDER BY NULL

when it should be:

SELECT
    (SELECT op.option_group_id FROM store_optionedproduct op, store_product WHERE op.product_ptr_id=store_product.id) AS `op_option_group_id`
    FROM `store_product`
    LEFT OUTER JOIN `store_productvariant` ON
        (`store_product`.`id` = `store_productvariant`.`product_id`)
        WHERE (`store_product`.`status` IN (1))
        GROUP BY `store_product`.`id`,
-->     `op_option_group_id`
        HAVING SUM(`store_productvariant`.`stock`) > 0 ORDER BY NULL

This is the same as #11916 but I've made this duplicate specifically for Django 1.2, as a different patch is needed (attached).

Attachments (1)

compiler.diff (772 bytes ) - added by Paul Garner 14 years ago.

Download all attachments as: .zip

Change History (9)

by Paul Garner, 14 years ago

Attachment: compiler.diff added

comment:1 by Paul Garner, 14 years ago

Cc: ego@… added

comment:2 by Karen Tracey, 14 years ago

Resolution: duplicate
Status: newclosed

If it's the same problem as another already open one, just attach the patch to the open ticket. A committer will take care of ensuring the ticket is fixed on current trunk and the most recent release branch, if applicable and possible.

comment:3 by Paul Garner, 14 years ago

Ok, sorry, have done.

comment:4 by Russell Keith-Magee, 14 years ago

Resolution: duplicatefixed

(In [12805]) Fixed #13123 -- Cleaned up template loader for PyPy compatibility. Thanks to avostryakov for the report.

comment:5 by Alex Gaynor, 14 years ago

Resolution: fixed
Status: closedreopened

This is almost certainly the wrong ticket.

comment:6 by Alex Gaynor, 14 years ago

Resolution: duplicate
Status: reopenedclosed

But it's still a dupe(I couldn't change the resolution).

comment:7 by Paul Garner, 14 years ago

I agree, that changeset has nothing to do with this bug or the one it's a dupe of.

comment:8 by Russell Keith-Magee, 14 years ago

Apologies - that was a checkin message typo. [12805] closed #13124, not this ticket.

Note: See TracTickets for help on using tickets.
Back to Top