Bug #4090 INSERT ... SELECT fails with subquery
Submitted: 10 Jun 2004 18:11 Modified: 22 Jun 2004 14:15
Reporter: Rob Blick Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.3 OS:Linux (Linux)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[10 Jun 2004 18:11] Rob Blick 
Description:
The following SELECT query works:

select Apk AS A_PK, description FROM tabA WHERE length(description) = (select min(length(description)) FROM tabA WHERE Apk = A_PK);

but, when "INSERT INTO tabB" prefixes it, I get an "Unknown column" error:

insert into tabB select Apk AS A_PK, description FROM tabA WHERE length(description) = (select min(length(description)) FROM tabA WHERE Apk = A_PK);
ERROR 1054 (42S22): Unknown column 'A_PK' in 'where clause'

even though A_PK is explicitly aliased.

How to repeat:
I have attached a sql script: 'subqueryTest.sql' that will create two tables and populate one of them.  Run the above SELECT and INSERT...SELECT queries to see the error.

Suggested fix:
If subqueries are not supported in INSERT...SELECT, please indicate in the documentation.  Otherwise, it looks like a problem in the statement parser.
[10 Jun 2004 18:11] Rob Blick 
test .sql script

Attachment: subqueryTest.sql (application/octet-stream, text), 1.66 KiB.
[10 Jun 2004 19:13] Dean Ellis 
Thank you for the report.  Verified against 4.1.3/Linux:

DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 ( a int, b int );
CREATE TABLE t2 ( c int, d int );
INSERT INTO t1 VALUES (1,2), (2,3), (3,4);
SELECT a AS abc, b FROM t1 WHERE b = (SELECT MIN(b) FROM t1 WHERE a=abc);
INSERT INTO t2 SELECT a AS abc, b FROM t1 WHERE b = (SELECT MIN(b) FROM t1 WHERE a=abc);
DROP TABLE t1, t2;

Workaround is to use different table aliases (rather than a column alias), and specify table.column:

INSERT INTO t2 SELECT a, b FROM t1 AS t1a WHERE b = (SELECT MIN(b) FROM t1 WHERE a=t1a.a);
[13 Jun 2004 21:42] Oleksandr Byelkin 
ChangeSet 
  1.1937 04/06/13 22:39:09 bell@sanja.is.com.ua +4 -0 
  fixed field resolving mode fo INSERT/REPLACE and CRETE with SELECT 
(BUG#4090)
[19 Jun 2004 22:16] Oleksandr Byelkin 
(because it is duplicate bug of 4202)
[22 Jun 2004 11:26] Michael Widenius 
ok to push after adding a test case for prep statements.
[22 Jun 2004 14:15] Oleksandr Byelkin 
Thank you for bug report. Bug is fixed, patch is pushed to our source 
repository and will be present in next server release.