Bug #9067 USING cannot match columns on second JOIN

• Submitted: 9 Mar 2005 8:31
• Modified: 14 Mar 2005 12:42
• Reporter: Emanuele Dolis
• Status: To be fixed later
• Category: Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 4.1.9
• OS: Microsoft Windows (Windows XP SP2)
• Assigned to: Timour Katchaounov

[9 Mar 2005 8:31] Emanuele Dolis

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>

*/

[14 Mar 2005 12:42] Timour Katchaounov

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.