Bug #18080 INSERT ... SELECT ... JOIN results in ambiguous field list error
Submitted: 8 Mar 2006 16:28 Modified: 23 Jul 2006 3:30
Reporter: David Phillips Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.18 OS:Linux (SuSE 9.3)
Assigned to: Georgi Kodinov CPU Architecture:Any

[8 Mar 2006 16:28] David Phillips 
Description:
Performing an INSERT ... SELECT ... JOIN ... USING without qualifying the field names causes ERROR 1052 "column 'x' in field list is ambiguous", even though it's not ambiguous and the same SELECT query works without the INSERT.

How to repeat:
mysql> CREATE TABLE foo (x int, y int);
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE bar (z int, y int);
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE xxx (a int, b int);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO xxx (SELECT x, y FROM foo JOIN bar USING (y) WHERE z = 1);
ERROR 1052 (23000): Column 'y' in field list is ambiguous

mysql> SELECT x, y FROM foo JOIN bar USING (y) WHERE z = 1;
Empty set (0.00 sec)
[9 Mar 2006 10:58] Hartmut Holzgraefe 
Verified on 5.0 and 5.1 using the following statements, 
on 4.1 the SELECT already reports "Column 'y' in field list is ambiguous"

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (x int, y int);
DROP TABLE IF EXISTS bar;
CREATE TABLE t2 (z int, y int);
DROP TABLE IF EXISTS foo;
CREATE TABLE t3 (a int, b int);

               SELECT x, y FROM t1 JOIN t2 USING (y) WHERE z = 1;

INSERT INTO t3 (SELECT x, y FROM t1 JOIN t2 USING (y) WHERE z = 1);
[19 Jun 2006 14:30] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7855
[21 Jun 2006 9:58] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8001
[21 Jun 2006 13:37] Georgi Kodinov 
There was an incomplete reset of the name resolution context, that caused 
  INSERT ... SELECT ... JOIN statements to resolve not by joint row type calculated
  for the join.
  Removed the redundant re-initialization of the context, because 
  mysql_insert_select_prepare() now correctly saves/restores the context.
[6 Jul 2006 15:34] Evgeny Potemkin 
Fixed in 5.0.24, 5.1.12
[23 Jul 2006 3:30] Paul DuBois 
Noted in 5.0.24, 5.1.12 changelogs.