MySQL Bugs: #2436: SELECT (SELECT) error

Bug #2436	SELECT (SELECT) error
Submitted:	17 Jan 2004 23:02	Modified:	21 Jan 2004 3:54
Reporter:	Andrey	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.1	OS:	Windows (WinXP, Win2K)
Assigned to:	Victor Vagin	CPU Architecture:	Any

Description:
create table t (  col1 int ) type=innoDb;
insert into t values (1),(2),(3);

create table t2(  id int, name varchar(50) ) type=innoDb;
insert into t2 values(1,"first"),(2,"second"),(3,"third");

select t.col1, (select t2.name from t2 where t2.id=t.col1) from t

Result field set is:
  1 first
  2 NULL
  3 NULL
instead of:
  1 first
  2 second
  3 third

If i change t2 type to default, all ok.
I know, this query looks very strange, but in greatly simplified, the real query is very complex.

How to repeat:
see bug description.

Conctinuing example from the point where table t2 is of InnoDB type

select t.col1, (select t2.name from t2 where t2.id=t.col1) from t;
+------+---------------------------------------------+
| col1 | (select t2.name from t2 where t2.id=t.col1) |
+------+---------------------------------------------+
|    1 | first                                       |
|    2 | NULL                                        |
|    3 | NULL                                        |
+------+---------------------------------------------+
3 rows in set (0.00 sec)
 
mysql> alter table t2 type=MyISAM;
Query OK, 3 rows affected, 1 warning (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql> select t.col1, (select t2.name from t2 where t2.id=t.col1) from t;
+------+---------------------------------------------+
| col1 | (select t2.name from t2 where t2.id=t.col1) |
+------+---------------------------------------------+
|    1 | first                                       |
|    2 | second                                      |
|    3 | third                                       |
+------+---------------------------------------------+

I know, that it works with MyISAM tables. It returns wrong result _only_ on InnoDB tables...

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

the bug was fixed by sanja on the day of bug report 
(ChangeSet  1.1712)
use newer version (4.1 tree)