rollback vs. left join problem ?

[Peter Blokland]

hi,

stumbled upon something i can't explain :

mysql> select * from a;
+------+--------+
| id_a | text_a |
+------+--------+
| 1 | text |
| 2 | text2 |

mysql> select * from b;
+------+-------+
| id_a | int_b |
+------+-------+
| 1 | 1 |

id_a is a foreign key in table b.

mysql> select * from a left join b on (a.id_a=b.id_a);
+------+--------+------+-------+
| id_a | text_a | id_a | int_b |
+------+--------+------+-------+
| 1 | text | 1 | 1 |
| 2 | text2 | NULL | NULL |

everything as expected so far. however, now i do this :

mysql> start transaction; // or set autocommit=0, no difference
mysql> delete from b;
mysql> rollback;

now, i correctly see the data in the table b. however :

mysql> select * from a left join b on (a.id_a=b.id_a);
+------+--------+------+-------+
| id_a | text_a | id_a | int_b |
+------+--------+------+-------+
| 1 | text | NULL | NULL |
| 2 | text2 | NULL | NULL |

the left join doesn't see the data in table b anymore. a normal join does, btw. only a complete restart of mysql solves this problem. can someone explain this behaviour, or is it a bug ? checked on 4.1.10, 4.1.12 and 4.1.13...

complete create statements :

CREATE TABLE `a` (
`id_a` int(10) unsigned NOT NULL auto_increment,
`text_a` varchar(20) default NULL,
PRIMARY KEY (`id_a`),
UNIQUE KEY `text_a_index` (`text_a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `b` (
`id_a` int(10) unsigned NOT NULL default '0',
`int_b` int(10) unsigned default NULL,
PRIMARY KEY (`id_a`),
KEY `b_FKIndex1` (`id_a`),
CONSTRAINT `b_ibfk_1` FOREIGN KEY (`id_a`) REFERENCES `a` (`id_a`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8