| Bug #31176 | UPDATE with subquery gives wrong result | ||
|---|---|---|---|
| Submitted: | 24 Sep 2007 14:53 | Modified: | 12 Oct 2007 14:56 |
| Reporter: | Andreas Vogt | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.1.20-community-nt | OS: | Windows (XP, Linux) |
| Assigned to: | CPU Architecture: | Any | |
[24 Sep 2007 14:54]
Andreas Vogt
database dump required to run commands
Attachment: t_sub_db.sql.txt (text/plain), 3.95 KiB.
[24 Sep 2007 15:15]
MySQL Verification Team
Thank you for the bug report. This bug not affects >= 5.0
[miguel@skybr 4.1]$ bin/mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.24-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> source /home/miguel/a/t_sub_db.sql.txt
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
<cut>
mysql> UPDATE tbl_course_registration SET int_registration =
-> (SELECT COUNT(*) FROM tbl_course tc
-> JOIN tbl_person_course tpc ON tpc.id_course = tc.id
-> WHERE tc.b_active = 1 AND tpc.b_active = 1 AND
-> tpc.int_priority = 1 AND
-> tc.id = tbl_course_registration.id_course);
Query OK, 1 row affected (0.01 sec)
Rows matched: 15 Changed: 1 Warnings: 0
mysql> select * from tbl_course_registration;
+-----------+------------------+
| id_course | int_registration |
+-----------+------------------+
| 1 | 2 |
| 2 | 5 |
| 3 | 0 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
| 7 | 0 |
| 8 | 0 |
| 9 | 0 |
| 10 | 0 |
| 11 | 0 |
| 12 | 0 |
| 13 | 0 |
| 14 | 0 |
| 15 | 0 |
+-----------+------------------+
15 rows in set (0.00 sec)
**************************************************
miguel@skybr 5.0]$ bin/mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.50-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> source /home/miguel/a/t_sub_db.sql.txt
Query OK, 0 rows affected (0.00 sec)
<cut>
mysql> UPDATE tbl_course_registration SET int_registration =
-> (SELECT COUNT(*) FROM tbl_course tc
-> JOIN tbl_person_course tpc ON tpc.id_course = tc.id
-> WHERE tc.b_active = 1 AND tpc.b_active = 1 AND
-> tpc.int_priority = 1 AND
-> tc.id = tbl_course_registration.id_course);
Query OK, 0 rows affected (0.04 sec)
Rows matched: 15 Changed: 0 Warnings: 0
mysql> select * from tbl_course_registration;
+-----------+------------------+
| id_course | int_registration |
+-----------+------------------+
| 1 | 2 |
| 2 | 5 |
| 3 | 0 |
| 4 | 0 |
| 5 | 1 |
| 6 | 0 |
| 7 | 0 |
| 8 | 0 |
| 9 | 0 |
| 10 | 0 |
| 11 | 0 |
| 12 | 0 |
| 13 | 0 |
| 14 | 0 |
| 15 | 0 |
+-----------+------------------+
15 rows in set (0.00 sec)
[12 Oct 2007 14:56]
Konstantin Osipov
Fixed in 5.0.
Description: Im a using a subquery to UPDATE table tbl_course_registration. With the first version of the subquery, the result is wrong: for id_course 5, the value int_registration is 0, but should be 1. With the second version, the value is 1, as expected. The two versions of the subquery differ only in the where clause of the subquery: The first version has tc.id = tbl_course_registration.id_course while the second version has tpc.id_course = tbl_course_registration.id_course The results should be the same because the tables are joined ON tpc.id_course = tc.id Here are the queries and results: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 131 to server version: 4.1.20-commun Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use t_sub_db; Database changed mysql> UPDATE tbl_course_registration SET int_registration = -> (SELECT COUNT(*) FROM tbl_course tc -> JOIN tbl_person_course tpc ON tpc.id_course = tc.id -> WHERE tc.b_active = 1 AND tpc.b_active = 1 AND -> tpc.int_priority = 1 AND -> tc.id = tbl_course_registration.id_course); Query OK, 1 row affected (0.02 sec) Rows matched: 15 Changed: 1 Warnings: 0 mysql> select * from tbl_course_registration; +-----------+------------------+ | id_course | int_registration | +-----------+------------------+ | 1 | 2 | | 2 | 5 | | 3 | 0 | | 4 | 0 | | 5 | 0 | | 6 | 0 | | 7 | 0 | | 8 | 0 | | 9 | 0 | | 10 | 0 | | 11 | 0 | | 12 | 0 | | 13 | 0 | | 14 | 0 | | 15 | 0 | +-----------+------------------+ 15 rows in set (0.00 sec) mysql> UPDATE tbl_course_registration SET int_registration = -> (SELECT COUNT(*) FROM tbl_course tc -> JOIN tbl_person_course tpc ON tpc.id_course = tc.id -> WHERE tc.b_active = 1 AND tpc.b_active = 1 AND -> tpc.int_priority = 1 AND -> tpc.id_course = tbl_course_registration.id_course); Query OK, 1 row affected (0.03 sec) Rows matched: 15 Changed: 1 Warnings: 0 mysql> select * from tbl_course_registration; +-----------+------------------+ | id_course | int_registration | +-----------+------------------+ | 1 | 2 | | 2 | 5 | | 3 | 0 | | 4 | 0 | | 5 | 1 | | 6 | 0 | | 7 | 0 | | 8 | 0 | | 9 | 0 | | 10 | 0 | | 11 | 0 | | 12 | 0 | | 13 | 0 | | 14 | 0 | | 15 | 0 | +-----------+------------------+ 15 rows in set (0.00 sec) How to repeat: Import database from dump (will attach file). Execute commands above. Note: The discrepancy did not occur in server version 4.1.10a running on Suse 9.3. On that machine, both versions of the command gave the expected result.