Description:
Problems with (SELECT ..) UNION (SElECT ..).
Difficult to explain, but example should say enough.
How to repeat:
CREATE TABLE test (
id int(11) NOT NULL default '0',
value int(11) default NULL
) TYPE=MyISAM;
INSERT INTO test VALUES (1, 5);
INSERT INTO test VALUES (3, 6);
INSERT INTO test VALUES (2, NULL);
INSERT INTO test VALUES (5, NULL);
A simple query works as expected..
mysql> select * from test left join test as test2 on test.id=test2.value order by test.id;
+----+-------+------+-------+
| id | value | id | value |
+----+-------+------+-------+
| 1 | 5 | NULL | NULL |
| 2 | NULL | NULL | NULL |
| 3 | 6 | NULL | NULL |
| 5 | NULL | 1 | 5 |
+----+-------+------+-------+
4 rows in set (0.00 sec)
But this is weird..
mysql> (select * from test left join test as test2 on test.id=test2.value where test.id in (1,2)) union (select * from test left join test as test2 on test.id=test2.value where test.id in (3,5));
+----+-------+----+-------+
| id | value | id | value |
+----+-------+----+-------+
| 1 | 5 | 0 | NULL |
| 2 | NULL | 0 | NULL |
| 3 | 6 | 0 | NULL |
| 5 | NULL | 1 | 5 |
+----+-------+----+-------+
4 rows in set (0.00 sec)
This is even weirder! :-)
mysql> select test.id,test2.id,test.value,test2.value from test left join test as test2 on test.id=test2.value order by test.id;
+----+------+-------+-------+
| id | id | value | value |
+----+------+-------+-------+
| 1 | NULL | 5 | NULL |
| 2 | NULL | NULL | NULL |
| 3 | NULL | 6 | NULL |
| 5 | 1 | NULL | 5 |
+----+------+-------+-------+
4 rows in set (0.00 sec)
Looks okay, but this...
mysql> (select test.id,test2.id,test.value,test2.value from test left join test as test2 on test.id=test2.value where test.id in (1,2)) union (select test.id,test2.id,test.value,test2.value from test left join test as test2 on test.id=test2.value where test.id in (3,5));
But gives..
+----+----+-------+-------+
| id | id | value | value |
+----+----+-------+-------+
| 1 | 0 | NULL | NULL |
| 2 | 0 | NULL | NULL |
| 3 | 0 | NULL | NULL |
| 5 | 1 | NULL | 5 |
+----+----+-------+-------+
4 rows in set (0.01 sec)
