I disagree with your LEFT JOIN/RIGHT JOIN results.
SELECT
activenodes.name,lrsrc.lid,activelayers.rsrcc,lrsrc.rsrc,nrsrc.rsrc
FROM activelayers
LEFT JOIN lrsrc ON lrsrc.id=activelayers.id AND
lrsrc.lid=activelayers.lid
RIGHT JOIN nrsrc ON lrsrc.rsrc=nrsrc.rsrc
RIGHT JOIN activenodes ON nrsrc.id=activenodes.id
ORDER BY activelayers.lid DESC;
Since you RIGHT JOINed "nrsrc" and "activenodes" to your query, neither
your 1st nor your 5th columns should contain any null values. So, the
columns that could contain null values are the 2nd, 3rd, and 4th (as both
"activelayers" and "lrsrc" are optional tables
However, only your 2nd and 4th columns contain nulls. So, where are the
optional (non-matching) rows for column 3? Where are those null values?
There should be nulls there for everywhere you have a null in columns 2
and 4, right?
...OR...
Are the tables "activelayers", "nrsrc", and "activenodes" participating in
a Cartesian product and only "lrsrc" is actually optional?
Which method of looking at this query is correct? Can you be certain that
the same style query will respond with the same decisions about which
tables are optional and which ones aren't (because of the mix of LEFT and
RIGHT joins in the same query)? The only way to avoid this kind of
"order-of-operations" dilemma is to use parentheses to group (nest) your
joins so that they evaluate in the correct order. However, last time I
checked, that is still on the TODO list to fix.
http://dev.mysql.com/doc/mysql/en/TODO_sometime.html (see third from last)
So to get back to the original issue.
To recap: from http://lists.mysql.com/mysql/174702
<snip>
Conceptually, here's what I'm trying to do: I've got a set of tasks to
execute
("frames"). The frames which are ready to execute are in "wait" mode.
These
frames are associated with "layers" (in the table "layers"), and these
layers
have 0 or more "layer resource requirements" (in the table "lrsrc"). I
also
have a table of compute nodes ("nodes"). Each of these nodes has 0 or
more
"node resources" (in the table "nrsrc"). If a layer requires "linux" and
"perl" resources, frames in that layer will only run on compute nodes
which
have "linux" and "perl" resources.
<snip>
What was the question are you trying to answer? I know it was something
about matching layers and nodes but I am not perfectly clear on what
results you wanted.
I really do want to help and I don't want to argue over what I think is a
bug (or an under-developed section) in the program. You may have received
the results you wanted this time but I am not confident that this query is
correct for the question you are asking nor am I confident that it will
continue to return correct results in the future.
Thanks for your patience,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Laszlo Thoth <[EMAIL PROTECTED]> wrote on 10/30/2004 04:21:15 AM:
>
> On Oct 29, 2004, at 6:26 AM, [EMAIL PROTECTED] wrote:
>
> > I think it may be because of your mixed left and right joins. There
> > are several bugs listed that show that the optimizer mishandles
> > certain combinations of left and right joins.
> >
> > SELECT
> >
> >
activelayers.id,activelayers.lid,activelayers.rsrcc,COUNT(lrsrc.rsrc=nr
> > src.rsrc)
> > as matchcount,activenodes.name,activenodes.rsrcc
> > FROM activelayers
> > LEFT JOIN lrsrc ON lrsrc.id=activelayers.id AND
> > lrsrc.lid=activelayers.lid
> > INNER JOIN nrsrc ON lrsrc.rsrc=nrsrc.rsrc
> > INNER JOIN activenodes ON nrsrc.id=activenodes.id
> > GROUP BY activelayers.id,activelayers.lid,activenodes.id
> > HAVING matchcount=activelayers.rsrcc
> > ORDER BY activelayers.lid DESC;
>
> This actually didn't produce the same result. I'm doing a RIGHT JOIN
> rather than a LEFT or INNER JOIN to catch node resources (nrsrc) which
> do not match layer resources (lrsrc), or nodes with no layer resources
> at all. This example makes the difference a little clearer:
>
> SELECT
> activenodes.name,lrsrc.lid,activelayers.rsrcc,lrsrc.rsrc,nrsrc.rsrc
> FROM activelayers
> LEFT JOIN lrsrc ON lrsrc.id=activelayers.id AND
> lrsrc.lid=activelayers.lid
> RIGHT JOIN nrsrc ON lrsrc.rsrc=nrsrc.rsrc
> RIGHT JOIN activenodes ON nrsrc.id=activenodes.id
> ORDER BY activelayers.lid DESC;
>
> +-------+------+-------+------+------+
> | name | lid | rsrcc | rsrc | rsrc |
> +-------+------+-------+------+------+
> | node1 | NULL | 1 | NULL | 1 |
> | node2 | NULL | 1 | NULL | 2 |
> | node3 | NULL | 1 | NULL | 1 |
> | node3 | NULL | 1 | NULL | 2 |
> | node0 | NULL | 1 | NULL | NULL |
> | node1 | 4 | 2 | 1 | 1 |
> | node2 | 4 | 2 | 2 | 2 |
> | node3 | 4 | 2 | 1 | 1 |
> | node3 | 4 | 2 | 2 | 2 |
> | node0 | NULL | 2 | NULL | NULL |
> | node1 | 3 | 2 | 1 | 1 |
> | node2 | 3 | 2 | 2 | 2 |
> | node3 | 3 | 2 | 1 | 1 |
> | node3 | 3 | 2 | 2 | 2 |
> | node0 | NULL | 2 | NULL | NULL |
> | node1 | NULL | 1 | NULL | 1 |
> | node2 | 2 | 1 | 2 | 2 |
> | node3 | NULL | 1 | NULL | 1 |
> | node3 | 2 | 1 | 2 | 2 |
> | node0 | NULL | 1 | NULL | NULL |
> | node1 | 1 | 1 | 1 | 1 |
> | node2 | NULL | 1 | NULL | 2 |
> | node3 | 1 | 1 | 1 | 1 |
> | node3 | NULL | 1 | NULL | 2 |
> | node0 | NULL | 1 | NULL | NULL |
> | node1 | NULL | 0 | NULL | 1 |
> | node2 | NULL | 0 | NULL | 2 |
> | node3 | NULL | 0 | NULL | 1 |
> | node3 | NULL | 0 | NULL | 2 |
> | node0 | NULL | 0 | NULL | NULL |
> +-------+------+-------+------+------+
>
> SELECT
> activenodes.name,lrsrc.lid,activelayers.rsrcc,lrsrc.rsrc,nrsrc.rsrc
> FROM activelayers
> LEFT JOIN lrsrc ON lrsrc.id=activelayers.id AND
> lrsrc.lid=activelayers.lid
> INNER JOIN nrsrc ON lrsrc.rsrc=nrsrc.rsrc
> INNER JOIN activenodes ON nrsrc.id=activenodes.id
> ORDER BY activelayers.lid DESC;
>
> +-------+------+-------+------+------+
> | name | lid | rsrcc | rsrc | rsrc |
> +-------+------+-------+------+------+
> | node1 | 4 | 2 | 1 | 1 |
> | node3 | 4 | 2 | 1 | 1 |
> | node2 | 4 | 2 | 2 | 2 |
> | node3 | 4 | 2 | 2 | 2 |
> | node1 | 3 | 2 | 1 | 1 |
> | node3 | 3 | 2 | 1 | 1 |
> | node2 | 3 | 2 | 2 | 2 |
> | node3 | 3 | 2 | 2 | 2 |
> | node2 | 2 | 1 | 2 | 2 |
> | node3 | 2 | 1 | 2 | 2 |
> | node1 | 1 | 1 | 1 | 1 |
> | node3 | 1 | 1 | 1 | 1 |
> +-------+------+-------+------+------+
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
