Bug #4025 where exists fails
Submitted: 6 Jun 2004 18:30 Modified: 16 Jun 2004 12:45
Reporter: William Choy Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.0.0-alpha-max-debug OS:Windows (winXP)
Assigned to: CPU Architecture:Any

[6 Jun 2004 18:30] William Choy
Description:
A simple "exists" sql running on innoDB didn't give correct result.

e.g. 
select aid from ta where exists
(select * from tb where ta.aid = tb.bid);

How to repeat:
--begin--
drop table if exists ta;
Create table ta (aid integer unsigned) engine=InnoDB default charset=big5;	
drop table if exists tb;
Create table tb (bid integer unsigned) engine=InnoDB default charset=big5;

insert into ta values (1);
insert into ta values (2);
insert into ta values (3);
insert into ta values (4);

insert into tb values (1);
insert into tb values (2);

commit;

-- this sql is problematic ---
select aid from ta
where exists
(select * from tb
where ta.aid = tb.bid);

---- end ----

I got wrong result when running on innoDB: 
+------+
| aid  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

When create table statements are changed to:
Create table ta (aid integer unsigned);	
Create table tb (bid integer unsigned);
The sql is fine.

Suggested fix:
Nil
[6 Jun 2004 18:57] William Choy
oh!  found it! 
mine is a duplicated entry of 2089.

so ... hope too see your fix soon.  

work hard!
:)
[16 Jun 2004 12:45] Alexander Keremidarski
Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

Additional info:

Duplicate of http://bugs.mysql.com/bug.php?id=2089