Code

Opened 5 years ago

Closed 11 months ago

#11881 closed Bug (fixed)

ORDER BY in aggregate subqueries is not necessary and might cause problems on some SQL servers

Reported by: egenix_viktor Owned by: Anssi Kääriäinen <akaariai@…>
Component: Database layer (models, ORM) Version: 1.1
Severity: Normal Keywords: aggregate subquery order_by optimization incompatibility
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: yes Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Some SQL servers, like MS SQL 2005 does not allow ORDER BY in subqueries without a TOP modifier. (It might apply to other database servers as well.) It is also suboptimal to use ORDER BY in the subquery of an aggregate SELECT, since it does not affect the result.

It might be better to just clear ordering in the subquery in this case. I suggest adding the following line in the BaseQuery.get_aggregation method before the query.add_subquery(obj) call:

obj.clear_ordering(True)

Patch against the db\models\sql\query.py file of the 1.1 release version has been attached.

Attachments (1)

query.py.patch (502 bytes) - added by egenix_viktor 5 years ago.
Patch against db/models/sql/query.py (Django 1.1, release version)

Download all attachments as: .zip

Change History (12)

Changed 5 years ago by egenix_viktor

Patch against db/models/sql/query.py (Django 1.1, release version)

comment:1 Changed 4 years ago by jacob

  • milestone set to 1.2
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

comment:2 Changed 4 years ago by anonymous

  • Summary changed from ORDER BY in aggregate subqueries is not necessary and might causes on some SQL servers to ORDER BY in aggregate subqueries is not necessary and might cause problems on some SQL servers

comment:3 Changed 4 years ago by russellm

  • Component changed from Database layer (models, ORM) to ORM aggregation
  • milestone changed from 1.2 to 1.3
  • Needs tests set
  • Owner nobody deleted

Not critical for 1.2

comment:4 Changed 3 years ago by julien

  • Severity set to Normal
  • Type set to Bug

comment:5 Changed 3 years ago by jacob

  • milestone 1.3 deleted

Milestone 1.3 deleted

comment:11 Changed 2 years ago by aaugustin

  • UI/UX unset

Change UI/UX from NULL to False.

comment:12 Changed 2 years ago by aaugustin

  • Easy pickings unset

Change Easy pickings from NULL to False.

comment:13 Changed 17 months ago by akaariai

  • Component changed from ORM aggregation to Database layer (models, ORM)

comment:14 Changed 11 months ago by akaariai

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

There is a clear_ordering clause in get_aggregation(), line 356 of django/db/models/sql/query.py

comment:15 Changed 11 months ago by akaariai

  • Resolution fixed deleted
  • Status changed from closed to new

The above isn't correct, the clear_ordering is applied to the outer query, not to the inner query. So, back to "accepted".

comment:16 Changed 11 months ago by Anssi Kääriäinen <akaariai@…>

  • Owner set to Anssi Kääriäinen <akaariai@…>
  • Resolution set to fixed
  • Status changed from new to closed

In 7bc57a6d71dd4d00bb09cfa67be547591fd759ce:

Fixed #11881 -- removed junk from aggregation subqueries

There were clauses that weren't needed in the subqueries. These were
ORDER BY, SELECT FOR UPDATE and related selections.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.