MySQL Bugs: #42491: INSERT on temporary table may pass incorrect lock type to storage engine

Bug #42491	INSERT on temporary table may pass incorrect lock type to storage engine
Submitted:	30 Jan 2009 19:44	Modified:	30 Jan 2009 20:28
Reporter:	Tim Clark	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Storage Engines	Severity:	S2 (Serious)
Version:	5.1.30	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	external_lock, insert, temporary table

Description:
The following sequence of statements will cause a lock_type of F_RDLCK to be passed to the handler::external_lock() function for the final INSERT statement. The correct value for lock_type should be F_WRLCK, since the statement is modifying table t4. The incorrect value is a problem for the IBMDB2I storage engine, since it relies on lock_type to optimize its I/O.

CREATE TABLE t3 (a int(10) unsigned, key(A), b text);
INSERT INTO t3 VALUES (1,'1');
create temporary table t4 select * from t3;
insert into t3 select * from t4;
insert into t4 values(1,"blah");

How to repeat:
Set the default storage engine to InnoDB.
Set a breakpoint in ha_innodb::external_lock().
Execute the statements given above.
Observe that the final INSERT statement calls external_lock with lock_type=F_RDLCK.

Suggested fix:
Always pass F_WRLCK to external_lock when inserting into a table.

As far as I can tell, this bug does not appear in 5.1.26.

Duplicate of Bug#41348?

Ah, yes... somehow I missed that one when I searched. This can be closed as a dup of Bug#41348.