Description:
next queries show problem:
------------------------
DROP TABLE IF EXISTS `goodtree`;
CREATE TABLE `goodtree` (
`node` int(11) NOT NULL default '0',
`maxchild` int(11) NOT NULL default '0',
PRIMARY KEY (`node`),
KEY `maxchild` (`maxchild`)
);
INSERT INTO `goodtree` (`node`, `maxchild`) VALUES (4,4),(5,5),(1,244);
select * from goodtree g1, goodtree g2
where g1.node <= g2.node and g2.node <= g1.maxchild and g2.node = g2.maxchild;
===========
Empty set (0.00 sec)
===========
select * from goodtree g1, goodtree g2
where g1.node <= g2.node and g2.node <= g1.maxchild and g2.node = g2.maxchild
and g1.node=1;
===========
+------+----------+------+----------+
| node | maxchild | node | maxchild |
+------+----------+------+----------+
| 1 | 244 | 4 | 4 |
| 1 | 244 | 5 | 5 |
+------+----------+------+----------+
2 rows in set (0.00 sec)
===========
select * from goodtree g1, goodtree g2
where g1.node <= g2.node and g2.node <= g1.maxchild and g2.node = g2.maxchild;
===========
+------+----------+------+----------+
| node | maxchild | node | maxchild |
+------+----------+------+----------+
| 5 | 5 | 5 | 5 |
| 1 | 244 | 5 | 5 |
+------+----------+------+----------+
2 rows in set (0.00 sec)
===========
------------------------
as you see 1-st and 3-rd select equal, but result different and both times wrong.
but if query will be this:
------------------------
select * from goodtree g2, goodtree g1
where g1.node <= g2.node and g2.node <= g1.maxchild and g2.node = g2.maxchild;
=========
+------+----------+------+----------+
| node | maxchild | node | maxchild |
+------+----------+------+----------+
| 4 | 4 | 1 | 244 |
| 4 | 4 | 4 | 4 |
| 5 | 5 | 5 | 5 |
| 5 | 5 | 1 | 244 |
+------+----------+------+----------+
4 rows in set (0.00 sec)
=========
all ok.
How to repeat:
DROP TABLE IF EXISTS `goodtree`;
CREATE TABLE `goodtree` (
`node` int(11) NOT NULL default '0',
`maxchild` int(11) NOT NULL default '0',
PRIMARY KEY (`node`),
KEY `maxchild` (`maxchild`)
);
INSERT INTO `goodtree` (`node`, `maxchild`) VALUES (4,4),(5,5),(1,244);
select * from goodtree g1, goodtree g2
where g1.node <= g2.node and g2.node <= g1.maxchild and g2.node = g2.maxchild;
select * from goodtree g1, goodtree g2
where g1.node <= g2.node and g2.node <= g1.maxchild and g2.node = g2.maxchild
and g1.node=1;
select * from goodtree g1, goodtree g2
where g1.node <= g2.node and g2.node <= g1.maxchild and g2.node = g2.maxchild;
select * from goodtree g2, goodtree g1
where g1.node <= g2.node and g2.node <= g1.maxchild and g2.node = g2.maxchild;
