Bug #1616 wrong way in correlated subquery
Submitted: 21 Oct 2003 14:09 Modified: 23 Oct 2003 3:04
Reporter: [ name withheld ] Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.0 Alpha OS:Microsoft Windows (Windows 2K)
Assigned to: Assigned Account CPU Architecture:Any

[21 Oct 2003 14:09] [ name withheld ]
Description:
Simple correlated subquery using max(), see "how to hepeat".

How to repeat:
create table a (id int, dt datetime)
create table b (id int, item int)

insert into a values (1, '2003-10-01')
insert into a values (2, '2003-12-01')

insert into b values (1, 1)
insert into b values (1, 2)
insert into b values (2, 2)

SELECT item, dt
  FROM a, b 
 WHERE a.id = b.id 
   AND a.dt = ( SELECT MAX ( dt ) 
                  FROM a aa, b bb 
                 WHERE aa.id = bb.id 
                   AND bb.item = b.item ) 
========================
EXPECTED result:
========================
item        dt                          
----------- -----------
1           2003-10-01 
2           2003-12-01 

========================
Result found:
========================
item        dt                          
----------- -----------
2           2003-12-01 

Suggested fix:
In correlated subqueries, it should "run" the subquery for each row found in the main query, and not resolve the subquery first and then restrict the main query.
[21 Oct 2003 14:20] [ name withheld ]
"how to hepeat"?? I mean "<R>epeat",xcuse.
[22 Oct 2003 11:43] Dean Ellis
Behaviour verified in version 4.1.1
[23 Oct 2003 3:04] Oleksandr Byelkin
Thank you for bug report! 
 
Cause of this bug is same as for BUG#1444. Please use bug#1444 record to see 
progress in fixing this bug.