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
• Status: Won't fix
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.13/4.1.16
• OS: Linux (SUSE Linux 10.0)
• 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] MySQL Verification Team

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] MySQL Verification Team

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.