Bug #3614 INSERT ... SELECT

• Submitted: 30 Apr 2004 18:41
• Modified: 2 May 2004 14:29
• Reporter: Naro SCHWERDTFEGER
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: MySQL 4.0.15-nt-log
• OS: Windows (Microsoft Windows 2000 SP4)
• CPU Architecture: Any

[30 Apr 2004 18:41] Naro SCHWERDTFEGER

Description:
"INSERT ... SELECT ..." don't work with table aliases

How to repeat:
CREATE TABLE Table (
id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT ,
name VARCHAR(255) NOT NULL ,
cat TINYINT UNSIGNED NOT NULL ,
PRIMARY KEY ( id ) 
);

CREATE TABLE TmpTable (
id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT ,
name VARCHAR(255) NOT NULL ,
PRIMARY KEY ( id ) 
);

INSERT INTO TmpTable (id,name) 
SELECT (TB1.id, TB2.name) 
FROM Table AS TB1, Table AS TB2 
WHERE TB1.cat = 1 AND TB2.cat = 3;
 > Result in an Syntax error on TB2.name (or an other one if called in second place...)

but:

INSERT INTO TmpTable (id) 
SELECT (TB1.id) 
FROM `Table` AS TB1
WHERE TB1.cat = 1;
 > No error

[2 May 2004 14:29] Alexander Keremidarski

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

mysql> INSERT INTO TmpTable (id,name) 
    -> SELECT TB1.id, TB2.name 
    -> FROM TestTable AS TB1, TestTable AS TB2 
    -> WHERE TB1.cat = 1 AND TB2.cat = 3;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Table is reserved word so it is not wise to use it as a name. I have changed it to TestTable and query works without any problem.

I don't see how this can be client related so I am changing category to Server.