Bug #34664 Failed assertion on UPDATE with sub-query
Submitted: 19 Feb 2008 13:44 Modified: 19 Feb 2008 14:20
Reporter: Paul McCullagh (Basic Quality Contributor) (OCA) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.1.22 OS:Any
Assigned to: CPU Architecture:Any
Tags: assertion, failed, modified_non_trans_table, trans_safe, updated

[19 Feb 2008 13:44] Paul McCullagh 
Description:
When executing a complex UPDATE with a sub-query on a transactional table the following assertion fails in sql_update.cc:

sql_update.cc:1974: failed assertion 'trans_safe || !updated || thd->transaction.stmt.modified_non_trans_table'

How to repeat:
CREATE TABLE t3 (N int, M tinyint) engine=innodb; 
INSERT INTO t3 VALUES (1,0),(1,0),(2,0),(2,0),(3,0);
UPDATE t3 AS P1 INNER JOIN (SELECT N FROM t3 GROUP BY N HAVING COUNT(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2;
DROP TABLE t3;

Note, this test comes from 'ps.test', and is run with binlog enabled.

Suggested fix:
Although this probably does not fix the problem, the table is correctly updated when the assertion is removed.
[19 Feb 2008 14:09] MySQL Verification Team 
looks like bug #30763 ?
[19 Feb 2008 14:20] Paul McCullagh 
Yes, looks like the same bug. I will test with 5.1.23...