Re: or-ing to QuerySets turns INNER into LEFT OUTER joins?

On Thu, Jul 25, 2013 at 4:42 PM, Carsten Fuchs <carsten.fuchs@cafu.de> wrote:
> Hi Tom,
>
> thank you very much for your reply!
>
> Am 25.07.2013 12:17, schrieb Tom Evans:
>
>> Isn't this to be expected? You've asked Django to OR the querysets.
>> This means that you are looking for tuples from STAFF where either the
>> join+conditions to ERFASST match or the join+conditions to
>> STAFF_BEREICHE match. If these joins were performed with an INNER
>> JOIN, then the query would only include tuples from STAFF that match
>> both joins and conditions.
>
>
> Well, I must foreclose that I'm by no means an SQL expert, and rather
> consider myself as a beginner -- but won't the joins augment the STAFF table
> independently of each other?
>
> That is, if I understand things correctly, the join types INNER vs. LEFT
> OUTER are mostly about the handling of NULL values on the "other" side of
> the relation, but as only non-NULL values are involved here, I'd expect a
> query to provide the same augmented table no matter if INNER or LEFT OUTER
> joins are used.

This query is all about joining tuples from the STAFF table to the
other two tables. If the joins to both ERFASST and STAFF_BEREICHE are
inner joins, then the only tuples from STAFF under consideration are
ones which have valid references to both tables. This is not the same
as OR'ing two independent queries.

Now, if your data obeys that criteria, then there would be no
difference between the two results, but the meaning of the queries are
very different. With inner joins, the query means "Find me staff that
have department 1, AND have logs* in the specified date range from
department 1", but with outer joins the query means "Find me staff
that have either department 1, or have logs from the specified date
range from department 1".

Put another way, if you AND'ed the Q objects instead, your query would
be the fast one you are looking for with inner joins throughout, but
you would not see users who do not have log entries, nor users with
log entries not in departments - but those conditions sound like an
impossible situation given your models.

As an example you can play around with, pick one user who has log
entries, and remove all their departments from STAFF_BEREICHE. This
user should then be found with the slow query with LEFT OUTER joins,
but not with the fast query with INNER joins - they are excluded due
to the INNER join to STAFF_BEREICHE fails for that tuple in STAFF, and
so it is not considered from that point onwards.

Cheers

Tom

* Erfasst is meaning logging here, right? Reaching the limits of my German :)

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
For more options, visit https://groups.google.com/groups/opt_out.