MySQL Bugs: #2296: insert ... select: Column 'xxxx' specified twice

Bug #2296	insert ... select: Column 'xxxx' specified twice
Submitted:	6 Jan 2004 7:28	Modified:	3 Feb 2004 10:37
Reporter:	Corin Langosch	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.17	OS:	Linux (debian linux)
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
the following query and error occoures using mysql 4.1.1-alpha:

MySQL_Query: Column 'konto_id' specified twice
Query-String: INSERT INTO konto_abbuchungen (abo_ref_id,amount,inhaber,kto,blz,institut,status,konto_id,create_time) SELECT id,amount,inhaber,kto,blz,institut,1,konto_id,NOW() FROM konto_abbuchungen WHERE abo<>0 AND status=6 AND (exec_time=0 OR ADDDATE(exec_time, INTERVAL abo DAY)<=NOW())

the query works without any erros using mysql 4.0.17. as you can see, the column isn specified twice, removing the column konto_id doesn't help. mysql then complains about the column status....

How to repeat:
should be easy, but don't have actual example to paste here. reading the sql shows already shows, that no error should occur.

additional info:

the problem also occurs with mysql 4.0.17!!!
BUT: the problem only occurs on both servers when the table konto_abbuchungen is locked. when i omit the write lock, no error occurs....

Verified against 4.0.18 and 4.1.2.  Thank you.

CREATE TABLE testlock ( a int, b int );
LOCK TABLE testlock WRITE;
INSERT INTO testlock ( a, b ) SELECT a, b FROM testlock;
UNLOCK TABLES;
INSERT INTO testlock ( a, b ) SELECT a, b FROM testlock;
DROP TABLE testlock;

The error message is a bit confusing but MySQL works as documented :(

The issue here is that if you use LOCK TABLES, you must lock every instance of a table separately.

For example, the provided testcase will work if you do:

LOCK TABLE testlock WRITE, testlock testlock2 READ;
INSERT INTO testlock ( a, b ) SELECT a, b FROM testlock as testlock2;

...

In other words, the fix is to always lock every table instance separately. This is documented in the LOCK TABLES section in the manual

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

(The reason for this is to ensure that the table locking code is deadlock free = fast and secure)

hello,

i dont' think this is the reason and mysql doesnt work as documented. 
the error is "column xxx specified twice", not "table xxx not locked properly" or something like that as if you didnt lock the tables correctly.

also i locked the table correctly, as i didn't use an alias but the exactly some name for the table. this query also is always dealock free.

if this really is supposed behavior, the error-code should be changed and the documentation of insert .. select should be updated, so that the the same tablename should not be used for the insert and the select at the some time, but an alias has to be used instead.

When closing this bug I did also update MySQL 4.0 to issue the error message:

Table 'xxx' was not locked with LOCK TABLES.

I did also clearify this problem in the manual.

A last note about this:

The problem is that to do

"INSERT INTO testlock ( a, b ) SELECT a, b FROM testlock"

MySQL opens two instances of the table 'testlock', each one that needs a separate lock.