Bug #5224 Error i aggrgate functions using sub selects

• Submitted: 26 Aug 2004 10:55
• Modified: 26 Aug 2004 15:24
• Reporter: Kasper Pedersen
• Status: Not a Bug
• Category: MySQL Server: ISAM storage engine
• Severity: S3 (Non-critical)
• Version: 5.0.0 (max)
• OS: Windows (Windows 2000)
• 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] MySQL Verification Team

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] MySQL Verification Team

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.