SubQuery bug again in 4.1

Hi all,

I think you misunderstood me. :-) I try to be more detailed, and I will 
also give an example.

So, I have these two tables:

----------------------------------------
CREATE TABLE main (
   ID int not null,
   Value int,

  primary key (ID)) Type = InnoDB;

CREATE TABLE sub (
   MainID int not null,
   KeyDate date not null,
   SubValue int not null,

   primary key (MainID, KeyDate),

  foreign key (MainID) references main (ID)) Type = InnoDB;
----------------------------------------

In the tables I have these lines:

----------------------------------------
INSERT INTO main VALUES
(1, null),
(2, null),
(3, null),
(4, null),
(5, null);

INSERT INTO sub VALUES
(1, '2001-01-01', 5),
(1, '1999-01-01', 10),
(2, '2001-01-01', 3),
(2, '2001-01-02', 4),
(4, '2001-01-01', 8);
----------------------------------------

Now, I want to update the `Value` fields in the `main` table for ALL 
records to contain the latest `SubValue` from the `sub` table.

So I want this to be in the `main` table:
ID	Value
--	-----
1	5
2	4
3	null
4	8
5	null
--	-----

For this I need to select the most recent `SubValue` from the sub table 
(e.g.: The `SubValue` with the latest `KeyDate` for a specified `MainID`).
I can do that this way (if you have other idea, tell me! :-)):

select SubValue from sub where MainID = xxx order by KeyDate desc limit 1

(xxx means an ID from the `main` table)

This query obviously returns with one record or null so I expect that this 
won't be a problem if I use this query as a subquery.
(By the way, I get the same weird behavior in any subquery expression where 
I use the "limit" parameter.)

Now, I want to update my `main` table to get the result above.

update main set Value = (select SubValue from sub where MainID = main.ID 
order by KeyDate desc limit 1)

This update sequence runs well, but the result in the `main` table will be 
this:
ID	Value
--	-----
1	5
2	4
3	4
4	4
5	4
--	-----

which is absolutelly not what I expected.

Thanks for your help in advance,
	Dani 



-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]