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: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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.041 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.