Bug #15607 Unambigous JOIN USING causes ERROR 1052: Column in field list is ambiguous
Submitted: 8 Dec 2005 23:15 Modified: 9 Dec 2005 11:31
Reporter: Bryce Nesbitt Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.13/4.1.16 OS:Linux (SUSE Linux 10.0)
Assigned to: CPU Architecture:Any

[8 Dec 2005 23:15] Bryce Nesbitt
Description:
The SQL
"select member_id,userid from CCS_RESERVATION join CCS_MEMBER using (member_id)"

Returns "ERROR 1052 (23000): Column 'member_id' in field list is ambiguous".  But in fact the member_id is not ambiguous.  Since it is a join field, it will be identical in both tables.

Postgres accepts the above construct.

How to repeat:
mysql> select member_id,userid from CCS_RESERVATION join CCS_MEMBER using (member_id) limit 2;
ERROR 1052 (23000): Column 'member_id' in field list is ambiguous

mysql> select CCS_RESERVATION.member_id,userid from CCS_RESERVATION join CCS_MEMBER using (member_id) limit 2;
+-----------+--------+
| member_id | userid |
+-----------+--------+
|         31 |      1 |
|         11 |      2 |
+-----------+--------+
2 rows in set (0.04 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.13    |
+-----------+
[8 Dec 2005 23:32] Miguel Solorzano
Could you please provide the complete test case with create tables and
inserts.

Thanks in advance.
[8 Dec 2005 23:49] Bryce Nesbitt
-- MySQL dump 10.9
CREATE TABLE `one` (
  `member_id` int(11) default NULL,
  `junk` varchar(128) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `one` VALUES (100,'xxx'),(200,'yyy'),(300,'zzzz');

CREATE TABLE `two` (
  `member_id` int(11) default NULL,
  `username` varchar(128) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `two` VALUES (100,'Fred'),(200,'Joe'),(300,'Sally');

========================================================

mysql> select member_id,username from one join two using (member_id);
ERROR 1052 (23000): Column 'member_id' in field list is ambiguous

mysql> select one.member_id,username from one join two using (member_id);
+-----------+----------+
| member_id | username |
+-----------+----------+
|       100 | Fred     |
|       200 | Joe      |
|       300 | Sally    |
+-----------+----------+
3 rows in set (0.00 sec)
[8 Dec 2005 23:58] Miguel Solorzano
This bug not affects 5.0.XX.

miguel@hegel:~/dbs/4.1> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.17-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `one` (
    ->   `member_id` int(11) default NULL,
    ->   `junk` varchar(128) default NULL
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO `one` VALUES (100,'xxx'),(200,'yyy'),(300,'zzzz');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> 
mysql> CREATE TABLE `two` (
    ->   `member_id` int(11) default NULL,
    ->   `username` varchar(128) default NULL
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `two` VALUES (100,'Fred'),(200,'Joe'),(300,'Sally');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select member_id,username from one join two using (member_id);
ERROR 1052 (23000): Column 'member_id' in field list is ambiguous

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.17-debug |
+--------------+
1 row in set (0.00 sec)

mysql> select member_id,username from one join two using (member_id);
+-----------+----------+
| member_id | username |
+-----------+----------+
|       100 | Fred     |
|       200 | Joe      |
|       300 | Sally    |
+-----------+----------+
3 rows in set (0.00 sec)
[9 Dec 2005 11:31] Sergei Golubchik
This was reported many times in bugdb, and this bug is fixed in 5.0.
It's one of the big changes in 5.0, and cannot be backported to 4.1.