Code

Opened 8 years ago

Closed 6 years ago

#1801 closed defect (fixed)

QuerySet.__and__ does not work for joined queries

Reported by: mir@… Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: major Keywords: qs-rf-fixed
Cc: mattimustang@…, yannvr@… Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

Try

# Who has written a and a2?
Reporter.objects.filter(article__id__exact=a1.id) & Reporter.objects.filter(article__id__exact=a2.id)

... this does not work as expected. I've added a patch for the or tests that should make it easy for you to verify this bug.

The underlying problem is that you cannot simply lump together the where conditions in this case. This does only work as long as you don't have joins. Probably using UNION would fix this, but is the overhead tolerable?

Attachments (1)

andbug.diff (3.4 KB) - added by mir@… 8 years ago.
Patch for or tests to find this bug

Download all attachments as: .zip

Change History (14)

Changed 8 years ago by mir@…

Patch for or tests to find this bug

comment:1 Changed 8 years ago by anonymous

I also confirm the bug. It is quite annoying.

comment:2 Changed 8 years ago by lukeplant

This is a hard bug! If you use UNION, then you can't ORDER BY correctly, and I can't see that UNION would fix it actually. One possibility is converting the joins to joins inside sub selects (the sub selects being part of the WHERE clause), and then you should be able to combine them. But, 1) the current code is a long way from making this easy, 2) you would want to avoid sub selects where they aren't needed, making it even harder, 3) I'm not sure which backends support sub selects.

Hmmm.

comment:3 Changed 8 years ago by mir@…

UNION still allows to specify an ordering on the result. At least for postgresql and mysql:

mysql requires that you put both UNION subselects into parenthesis to make the ordering work. And you need mysql 4.0 or later, before 4.0 UNION is not supported at all.

(This is only from the docs)

sqlite docs don't make a statement about it.

How about this approach:

  • check whether both sides contain a join to the same table
  • in this case, use a UNION

comment:4 Changed 8 years ago by mir@…

regarding the question, how UNION would help:

(select reporter.* from reporter, article where reporter.id=article.reporter and article.id=:n1)
UNION
(select reporter.* from reporter, article where reporter.id=article.reporter and article.id=:n2)

A different approach were to alias the tables to get an SQL expression like this:

select reporter.* from reporter, article a1, article a2 
where reporter.id = a1.reporter
and a1.id=:n1
and reporter.id = a2.reporter
and a2.id=:n2

The second approach looks somehow cleaner to me, since that's how I'd usually do it by hand. On the other side, it's probably harder to implement.

What's the real difference between both approaches, anyway?

comment:5 Changed 8 years ago by akaihola

Is this related to #2080?

comment:6 Changed 8 years ago by adrian

I've marked #2080 as a duplicate. This is a hard bug indeed.

comment:7 Changed 8 years ago by adrian

  • Status changed from new to assigned

comment:8 Changed 7 years ago by mir@…

  • Triage Stage changed from Unreviewed to Accepted

-> Accepted, based on Adrian's statement.

comment:9 Changed 7 years ago by axiak@…

Please look at #3691 for a patch to Q objects that allows this. Although, it's not as pretty as a queryset refactor would be...

comment:10 Changed 7 years ago by Matthew Flanagan <mattimustang@…>

  • Cc mattimustang@… added

comment:11 Changed 7 years ago by mtredinnick

  • Keywords qs-rf-fixed added

comment:12 Changed 6 years ago by Yopi <yannvr@…>

  • Cc yannvr@… added

comment:13 Changed 6 years ago by mtredinnick

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

(In [7477]) Merged the queryset-refactor branch into trunk.

This is a big internal change, but mostly backwards compatible with existing
code. Also adds a couple of new features.

Fixed #245, #1050, #1656, #1801, #2076, #2091, #2150, #2253, #2306, #2400, #2430, #2482, #2496, #2676, #2737, #2874, #2902, #2939, #3037, #3141, #3288, #3440, #3592, #3739, #4088, #4260, #4289, #4306, #4358, #4464, #4510, #4858, #5012, #5020, #5261, #5295, #5321, #5324, #5325, #5555, #5707, #5796, #5817, #5987, #6018, #6074, #6088, #6154, #6177, #6180, #6203, #6658

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.