Opened 5 years ago

Closed 5 years ago

Last modified 5 years ago

#13123 closed (duplicate)

Extra params + aggregation creates incorrect SQL.

Reported by: anentropic 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: UI/UX:

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 anentropic 5 years ago.

Download all attachments as: .zip

Change History (9)

Changed 5 years ago by anentropic

comment:1 Changed 5 years ago by anentropic

  • Cc ego@… added
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

comment:2 Changed 5 years ago by kmtracey

  • Resolution set to duplicate
  • Status changed from new to closed

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 Changed 5 years ago by anentropic

Ok, sorry, have done.

comment:4 Changed 5 years ago by russellm

  • Resolution changed from duplicate to fixed

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

comment:5 Changed 5 years ago by Alex

  • Resolution fixed deleted
  • Status changed from closed to reopened

This is almost certainly the wrong ticket.

comment:6 Changed 5 years ago by Alex

  • Resolution set to duplicate
  • Status changed from reopened to closed

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

comment:7 Changed 5 years ago by anentropic

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

comment:8 Changed 5 years ago by russellm

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

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