Bug #5224 Error i aggrgate functions using sub selects
Submitted: 26 Aug 2004 10:55 Modified: 26 Aug 2004 15:24
Reporter: Kasper Pedersen Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: ISAM storage engine Severity:S3 (Non-critical)
Version:5.0.0 (max) OS:Microsoft Windows (Windows 2000)
Assigned to: CPU Architecture:Any

[26 Aug 2004 10:55] Kasper Pedersen
Description:
The sum function seems to calculate a wrong amount of records in this case:

Making an UPDATE using af sub SELECT with a parameter value from the UPDATE set value

This however is working fine with 4.1 :-)

Please contact me for more informationen if needed

Kasper Pedersen

How to repeat:
UPDATE temp_firma SET  temp_firma.calcTopProcent =
	(SELECT SUM(firmaOvenfor.calcSumBeloeb) FROM firma AS firmaOvenfor WHERE firmaOvenfor.calcSumBeloeb >= temp_firma.calcSumBeloeb)
;

... the above describes the simplest expression which fails

Suggested fix:
?
[26 Aug 2004 11:50] Victoria Reznichenko
Kasper,

thank you for report!

I tested your query with my test data and got correct result on version 5.0.1:

mysql> select * from temp_firma;
+----------------+---------------+
| calcTopProcent | calcSumBeloeb |
+----------------+---------------+
|           NULL |             1 |
|           NULL |             2 |
|           NULL |             3 |
+----------------+---------------+
3 rows in set (0.01 sec)

mysql> select * from firma;
+---------------+
| calcSumBeloeb |
+---------------+
|             2 |
|             3 |
|             4 |
|             5 |
+---------------+
4 rows in set (0.00 sec)

mysql> UPDATE temp_firma SET  temp_firma.calcTopProcent =
    ->         (SELECT SUM(firmaOvenfor.calcSumBeloeb) FROM firma AS firmaOvenfor WHERE
    -> firmaOvenfor.calcSumBeloeb >= temp_firma.calcSumBeloeb);
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from temp_firma;
+----------------+---------------+
| calcTopProcent | calcSumBeloeb |
+----------------+---------------+
|             14 |             1 |
|             14 |             2 |
|             12 |             3 |
+----------------+---------------+
3 rows in set (0.00 sec)

Could you please provide tables structure and data for testing?
[26 Aug 2004 12:46] Kasper Pedersen
Test files have been uploaded
[26 Aug 2004 15:24] Victoria Reznichenko
Hi Kasper,

Thank you for info!

I tested the above on 4.1.3, 5.0.0 and 5.0.1 and got correct result on both 4.1.3 and 5.0.1. So, please, upgrade MySQL server to the recent version.