Bug #70472 SQL_SAFE_UPDATES does not work properly with WHERE primary_key IN clauses

• Submitted: 30 Sep 2013 19:50
• Modified: 1 Oct 2013 11:31
• Reporter: JF Im
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.5.32-0ubuntu0.12.04.1, 5.5.34, 5.6.14
• OS: Linux
• CPU Architecture: Any

[30 Sep 2013 19:50] JF Im

Description:
When executing an update statement that includes a WHERE IN clause that itself refers to a subselect, the error "ERROR 1175 (HY000) at line 16: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column" appears, even though the WHERE clauses includes the primary key.

How to repeat:
Execute the following SQL statements:

DROP TABLE IF EXISTS foo;
CREATE TABLE foo (
	id int(11) PRIMARY KEY,
	value INT(11) DEFAULT NULL
);

CREATE TEMPORARY TABLE bar(
	other_id int(11) PRIMARY KEY
);

INSERT INTO foo VALUES (1, 1), (2, 2), (3, 3);
INSERT INTO bar VALUES (3);

UPDATE foo SET value = 0 WHERE id = 1; -- works
UPDATE foo SET value = 0 WHERE id IN (SELECT 2); -- works
UPDATE foo SET value = 0 WHERE id IN (SELECT other_id FROM bar); -- doesn't work

DROP TABLE foo;

Suggested fix:
A workaround would be to disable SQL_SAFE_UPDATES, ie. SET SQL_SAFE_UPDATES=0;

[1 Oct 2013 11:31] MySQL Verification Team

Hello JF,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh