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

Bug #2296	insert ... select: Column 'xxxx' specified twice
Submitted:	6 Jan 2004 8:28	Modified:	3 Feb 2004 11:37
Reporter:	Corin Langosch
Status:	Closed
Category:	Server	Severity:	S2 (Serious)
Version:	4.0.17	OS:	Linux (debian linux)
Assigned to:	Bugs System	Target Version:

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.