Description:
If subquery 'where' expression use external query data on InnoDB tables, then result is wrong. This bug in MySQL 4.1.1 too.
How to repeat:
CREATE TABLE `test2` (
`id` int(11) NOT NULL auto_increment,
`val1` int(11) default NULL,
`val2` char(255) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=koi8r;
insert into test2 values (1,10,'aaa');
insert into test2 values (2,50,'bbb');
insert into test2 values (3,999,'zzz');
mysql> select * from test2;
+----+------+------+
| id | val1 | val2 |
+----+------+------+
| 1 | 10 | aaa |
| 2 | 50 | bbb |
| 3 | 999 | zzz |
+----+------+------+
3 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select t1.id,(select val1 from test2 t2 where t1.id=t2.id) from test2 t1;
+----+-----------------------------------------------+
| id | (select val1 from test2 t2 where t1.id=t2.id) |
+----+-----------------------------------------------+
| 1 | 10 |
| 2 | NULL |
| 3 | NULL |
+----+-----------------------------------------------+
3 rows in set (0.01 sec)
If I use 'ENGINE=MyISAM', then result is:
+----+-----------------------------------------------+
| id | (select val1 from test1 t2 where t1.id=t2.id) |
+----+-----------------------------------------------+
| 1 | 10 |
| 2 | 50 |
| 3 | 999 |
+----+-----------------------------------------------+
3 rows in set (0.00 sec)
