Bug #16163 UPDATE Statement used in V 4.1.x fails in V5.0.x

• Submitted: 3 Jan 2006 19:02
• Modified: 4 Jan 2006 15:48
• Reporter: Scott Lindstrom
• Status: Closed
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 5.0.x
• OS: Windows (Windows XP SP2)
• CPU Architecture: Any

[3 Jan 2006 19:02] Scott Lindstrom

Description:
5.0.17-nt-max-log on WinXP w/ 512MB

Here is the query that works fine in 4.0.12

(bu_pl_tmp@et-cdc-mpl-db)>UPDATE item_master INNER JOIN irtw_items USING(item)
-> SET item_master.supplier = irtw_items.supplier
-> WHERE item_master.dbcode='1492' AND irtw_items.dbcode = '1850'
-> AND item_master.supplier='NL90' AND irtw_items.planning_site = '189000';

Query OK, 9820 rows affected (3.13 sec)
Rows matched: 9847 Changed: 9820 Warnings: 0

The same query does the following in 5.0.17.

(bu_pl@localhost)>UPDATE item_master INNER JOIN irtw_items USING(item)
-> SET item_master.supplier = irtw_items.supplier
-> WHERE item_master.dbcode='1492' AND irtw_items.dbcode = '1850'
-> AND item_master.supplier='NL90' AND irtw_items.planning_site = '189000';

Query OK, 1 row affected (4.55 sec)
Rows matched: 1 Changed: 1 Warnings: 0

How to repeat:
Tried changing INNER JOIN to JOIN. Added USE INDEX to the item_master table. No change. Rewriting the UPDATE to use the COMMA join operator fails due to the new explicit JOIN syntax of the 5.0.x server.

Here is the equivalent SELECT statement showing that there ARE valid records meeting the WHERE conditions.

(bu_pl@localhost)>SELECT COUNT(*) from item_master INNER JOIN irtw_items USING(
item) WHERE item_master.dbcode='1492' AND irtw_items.dbcode = '1850'
-> AND item_master.supplier='NL90' AND irtw_items.planning_site = '189000';

+----------+
| COUNT(*) |
+----------+
| 9842 |
+----------+
1 row in set (0.00 sec)

Suggested fix:
Unknown

[4 Jan 2006 8:24] Valeriy Kravchuk

Thank you for a problem report. Please, send (or upload using the File tab) table definitions and the smallest amount of data that demonstrates the problem.

[4 Jan 2006 15:48] Scott Lindstrom

Intermittent - while getting data and script files ready for submission to the development team, the query is now working. Incredible, considering nothing has changed! :-)

Sorry for wasting your time.