Code

Opened 5 years ago

Closed 5 years ago

Last modified 5 years ago

#10113 closed (fixed)

aggregation annotations on related tables do not work with ordering on field in different related table

Reported by: Koen Biermans <koen.biermans@…> Owned by: russellm
Component: Database layer (models, ORM) Version: master
Severity: Keywords: aggregation
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

Using annotate on a related table in combination with ordering on a field from a different related table results in failures.

There seem to be two problems:

  • the related orderby field is not included in the GROUP BY, which does not work on Postgres
  • the related table (of the orderby field) is included with an inner join, which comes after the left outer join of the annotation tables, which leads to incorrect results.

This is easier to explain with an example, so I am including a diff that contains a small testcase demonstrating the problem.

Attachments (1)

aggr_joins.diff (5.9 KB) - added by Koen Biermans <koen.biermans@…> 5 years ago.
small testcase demonstrating the problem

Download all attachments as: .zip

Change History (7)

Changed 5 years ago by Koen Biermans <koen.biermans@…>

small testcase demonstrating the problem

comment:1 Changed 5 years ago by russellm

  • Needs documentation unset
  • Needs tests unset
  • Owner changed from nobody to russellm
  • Patch needs improvement unset
  • Status changed from new to assigned
  • Triage Stage changed from Unreviewed to Accepted
  • Version changed from 1.0 to SVN

The ordering problem definitely exists - thanks for the report and the helpful test case (even better would be a test case using the existing test data, but I can't have everything I suppose :-). I've got an initial patch - I'll test it a bit more and then commit.

However, I can't reproduce the second problem. Once I fixed the grouping problem for Postgres, all the tests you provide pass as-is for me on MySQL, Postgres and SQLite. In particular, the problem test case highlighted on lines 59-69 gives me exactly the result that the test expects. Are you certain that this is a test failure for you?

comment:2 Changed 5 years ago by russellm

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

(In [9788]) Fixed #10113 -- Ensured that joined fields mentioned in order_by() are included in the GROUP_BY clause on those backends that require it. Thanks to Koen Biermans <koen.biermans@…> for the report.

comment:3 Changed 5 years ago by russellm

Regarding the second problem - if this is still a problem and you can reproduce the failure on [9788] or later (preferably with a test case drawn from the existing test data), please open another ticket. We try to stick to one issue per ticket so that we don't need to keep track of which tickets are partially fixed.

comment:4 Changed 5 years ago by Koen Biermans <koen.biermans@…>

Thanks Russel, for the fast resolution. All running perfect on Postgres now.

I do still see bad results on sqlite, but since I am using python 2.5 on windows, I am beginning to think it might be a specific sqlite version problem.

The reason I created that special testcase is that it took me a while to find a configuration that produced the bad result (on sqlite).

I will try whether upgrading my sqlite version solves it. If not, I will create a new ticket.

comment:5 Changed 5 years ago by russellm

(In [9791]) Refs #10113 -- Modified the generated SQL to remove redundant GROUP BY elements, and modified the test added in [9788] to remove a test result that depended on a potentially ambiguous database ordering.

comment:6 Changed 5 years ago by Koen Biermans <koen.biermans@…>

For future reference:

the wrong results from my testcase were obtained with sqlite v3.3.4. However, with v3.6.2 the test passes.

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.