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]
