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