MySQL Bugs: #4135: INSERT...SELECT into same table fails if table has been write-locked

Bug #4135	INSERT...SELECT into same table fails if table has been write-locked
Submitted:	14 Jun 2004 23:54	Modified:	16 Jun 2004 10:08
Reporter:	Alex Villacis	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.20	OS:	Linux (Linux)
Assigned to:	Sergei Golubchik	CPU Architecture:	Any

Description:
If a table has been locked with a write lock, and the INSERT...SELECT statement
is used, with the locked table as both source and destination, the operation
(incorrectly) fails with the message:

ERROR 1100: Table '<sometable>' was not locked with LOCK TABLES

where <sometable> is the very same table that was used in the LOCK TABLES 
statement, and later in the INSERT...SELECT statement.

The expected outcome is that the INSERT should work correctly, since the table
was listed for locking in a previous statement.

How to repeat:
The following script replicates the bug:

use test;
create table lock_test (a int not null primary key, b int not null);
insert into lock_test (a, b) values (1, 2);
lock tables lock_test write;
insert into lock_test (b) select b from lock_test where a = 1;
unlock tables;

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

You cannot use a table locked with LOCK TABLES in the query more than once.
The solution is to use aliases.

LOCK TABLES t1 WRITE, t1 t2 WRITE;
INSERT t1 SELECT * FROM t1 t2;

http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html