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