QuerySet.__and__ does not work for joined queries

[mir@…]

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?

[mir@…]

Patch for or tests to find this bug

[anonymous]

I also confirm the bug. It is quite annoying.

[Luke Plant]

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.

[mir@…]

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

• ​http://www.postgresql.org/docs/8.1/interactive/sql-select.html#SQL-UNION
• ​http://dev.mysql.com/doc/refman/4.1/en/union.html

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.

• ​http://www.sqlite.org/lang_select.html

How about this approach:

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

[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?

[Antti Kaihola]

Is this related to #2080?

[Adrian Holovaty]

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

[mir@…]

-> Accepted, based on Adrian's statement.

[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...

[Malcolm Tredinnick]

(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