MySQL Bugs: #9067: USING cannot match columns on second JOIN

Bug #9067	USING cannot match columns on second JOIN
Submitted:	9 Mar 2005 7:31	Modified:	2 Nov 2008 18:49
Reporter:	Emanuele Dolis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	4.1.9	OS:	Windows (Windows XP SP2)
Assigned to:	Timour Katchaounov	CPU Architecture:	Any

Description:

If you try to link one table (say a) to two addictional tables (say b,c) with a join statement and USING clause, 
the first JOIN b USING(matchfldB) works, whilst 
the snd one JOIN c USING(matchfldC) doesn't - an error "ERROR 1054 (42S22): Unknown column" arires.

If you use the ON clause, on the contrary, everything works fine (see example)

How to repeat:
# creates three tables with TRTCA and TRVOC foreign keys in repbug1main

DROP TABLE IF EXISTS `repbug1det1`;
CREATE TABLE `repbug1det1` (
  `TRTCA` char(2) NOT NULL default '',
  `DxTCA` varchar(40) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `repbug1det1` VALUES ('06','Q.LE TASS.(ARR.QLE.SUP)'),('16','TIPO COLLO/CONTAINER'),('71','CHILOMETRO');

DROP TABLE IF EXISTS `repbug1det2`;
CREATE TABLE `repbug1det2` (
  `TRVOC` varchar(4) NOT NULL default '',
  `DxVOC` varchar(30) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `repbug1det2` VALUES ('0011','spese consegna mater. italia'),('0037','urgenze'),('0304','trasporto contenitori vuoti');
UNLOCK TABLES;

DROP TABLE IF EXISTS `repbug1main`;
CREATE TABLE `repbug1main` (
  `CstBill` mediumint(6) unsigned zerofill NOT NULL default '000000',
  `TRVOC` varchar(4) NOT NULL default '',
  `TRTCA` char(2) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `repbug1main` VALUES (000064,'0304','16'),(000064,'0011','06'),(000064,'0037','71');

#Using command line client
/*_______________________________________________
mysql> SELECT * FROM test.repbug1main r
    -> LEFT JOIN repbug1det1 USING (TRTCA)
    -> LEFT JOIN repbug1det2 s ON (r.TRVOC=s.TRVOC)
    -> ;
+---------+-------+-------+-------+-------------------------+-------+------------------------------+
| CstBill | TRVOC | TRTCA | TRTCA | DxTCA                   | TRVOC | DxVOC                        |
+---------+-------+-------+-------+-------------------------+-------+------------------------------+
|  000064 | 0304  | 16    | 16    | TIPO COLLO/CONTAINER    | 0304  | trasporto contenitori vuoti  |
|  000064 | 0011  | 06    | 06    | Q.LE TASS.(ARR.QLE.SUP) | 0011  | spese consegna mater. italia |
|  000064 | 0037  | 71    | 71    | CHILOMETRO              | 0037  | urgenze                      |
+---------+-------+-------+-------+-------------------------+-------+------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM test.repbug1main r
    -> LEFT JOIN repbug1det1 USING (TRTCA)
    -> LEFT JOIN repbug1det2 s USING ( TRVOC);
ERROR 1054 (42S22): Unknown column 'test.repbug1det1.TRVOC' in 'on clause'
mysql>

*/

This bug is very similar to BUG#6495 - "Illogical requirement for column
qualification in NATURAL join" in the sense that both bugs result from
the current name resolution procedure in MySQL.

The reason that things work in the first test query with the ON condition
is that the columns in the condition are qualified with a table name, while
in the second query with the USING clause the column is not qualified (and
cant't be).

This is a known problem which is currently being corrected in MySQL
v. 5.0 as part of a bigger task. Since correcting this problem is considered
as a new feature, the problem will not be addressed in v. 4.1.

Fixed since 5.0.12, I think:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -P3308 -uroot -proot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.70-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)

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

mysql> DROP TABLE IF EXISTS `repbug1det1`;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> CREATE TABLE `repbug1det1` (
    ->   `TRTCA` char(2) NOT NULL default '',
    ->   `DxTCA` varchar(40) default NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.22 sec)

mysql> INSERT INTO `repbug1det1` VALUES ('06','Q.LE TASS.(ARR.QLE.SUP)'),('16','
TIPO
    '> COLLO/CONTAINER'),('71','CHILOMETRO');
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> DROP TABLE IF EXISTS `repbug1det2`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `repbug1det2` (
    ->   `TRVOC` varchar(4) NOT NULL default '',
    ->   `DxVOC` varchar(30) default NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO `repbug1det2` VALUES ('0011','spese consegna mater.
    '> italia'),('0037','urgenze'),('0304','trasporto contenitori vuoti');
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS `repbug1main`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `repbug1main` (
    ->   `CstBill` mediumint(6) unsigned zerofill NOT NULL default '000000',
    ->   `TRVOC` varchar(4) NOT NULL default '',
    ->   `TRTCA` char(2) default NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.16 sec)

mysql> INSERT INTO `repbug1main` VALUES
    -> (000064,'0304','16'),(000064,'0011','06'),(000064,'0037','71');
Query OK, 3 rows affected (0.13 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test.repbug1main r
    -> LEFT JOIN repbug1det1 USING (TRTCA)
    -> LEFT JOIN repbug1det2 s ON (r.TRVOC=s.TRVOC);
+-------+---------+-------+-------------------------+-------+-------------------
-----------+
| TRTCA | CstBill | TRVOC | DxTCA                   | TRVOC | DxVOC
           |
+-------+---------+-------+-------------------------+-------+-------------------
-----------+
| 16    |  000064 | 0304  | TIPO
COLLO/CONTAINER    | 0304  | trasporto contenitori vuoti  |
| 06    |  000064 | 0011  | Q.LE TASS.(ARR.QLE.SUP) | 0011  | spese consegna mat
er.
italia |
| 71    |  000064 | 0037  | CHILOMETRO              | 0037  | urgenze
           |
+-------+---------+-------+-------------------------+-------+-------------------
-----------+
3 rows in set (0.13 sec)

mysql> SELECT * FROM test.repbug1main r
    -> LEFT JOIN repbug1det1 USING (TRTCA)
    -> LEFT JOIN repbug1det2 s USING ( TRVOC);
+-------+-------+---------+-------------------------+---------------------------
---+
| TRVOC | TRTCA | CstBill | DxTCA                   | DxVOC
   |
+-------+-------+---------+-------------------------+---------------------------
---+
| 0304  | 16    |  000064 | TIPO
COLLO/CONTAINER    | trasporto contenitori vuoti  |
| 0011  | 06    |  000064 | Q.LE TASS.(ARR.QLE.SUP) | spese consegna mater.
italia |
| 0037  | 71    |  000064 | CHILOMETRO              | urgenze
   |
+-------+-------+---------+-------------------------+---------------------------
---+
3 rows in set (0.00 sec)