MySQL Bugs: #15229: Unknown column when mixing ON and USING

Bug #15229	Unknown column when mixing ON and USING
Submitted:	24 Nov 2005 15:02	Modified:	14 Mar 2006 16:52
Reporter:	Hervé Guillemet	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.16	OS:	Linux (Linux)
Assigned to:	Timour Katchaounov	CPU Architecture:	Any

Description:
I'm not sure to have fully understood the subtilities of the change in USING since 5.0.12, but it seems to me that the following error is a bug.
Replacing a2 by a.a2 works.
Rewriting the request:
select a2 from a join (b join c on b=c1 join d using (c2)) using (a1);
also works.

How to repeat:
mysql> create temporary table a (a1 int, a2 int);
mysql> create temporary table b (a1 int, b int);
mysql> create temporary table c (c1 int, c2 int);
mysql> create temporary table d (c2 int);
mysql> select a2 from a join b using (a1) join c on b=c1 join d using (c2);
ERROR 1054 (42S22): Unknown column 'a1' in 'field list'

Sorry the error message is 
Unknown column 'a2' in 'field list'
not
Unknown column 'a1' in 'field list'

Hi!

I marked this bug as a duplicated because bug #9067 addresses this same problem.

Hi,

Thanks for your quick feedback.
This bug may be related to #9067, but I'd like to precise that the error does not show up in 4.1 (tested against 4.1.9), while #9067 was reported for 4.1 and commented as being fixed in 5.

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/internals/33152

Fixed in 5.0.19

sorry - not yet pushed

Approved with a few review notes oveer email.

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/internals/33460

Fixed in 5.0.20.

Closed in 5.0.20 changelog:

    <listitem>
        <para>
          Certain combinations of joins with mixed <literal>ON</literal>
          and <literal>USING</literal> clauses caused unknown column
          errors. (Bug #15229)
        </para>
      </listitem>

MySQL 5.0.20: Now mixing USING and ON causes an error...

...
FROM `Tasks`
	INNER JOIN `Users`
		ON (`Users`.`userId` = `Tasks`.`executorId`)
	INNER JOIN `Persons`
		USING (`personId`)
...

ERROR: Column 'personId' in from clause is ambiguous

but that works:

...
FROM `Tasks`
	INNER JOIN `Users`
		ON (`Users`.`userId` = `Tasks`.`executorId`)
	INNER JOIN `Persons`
		ON (`Persons`.`personId` = `Users`.`personId`)
...

Hi Andrius,

Depending on the schema of the tables, this may or may not be a bug.
Could you please post the complete schema of the tables in the problem
query, and the complete query that triggers the problem, so we can
reproduce it.

CREATE TABLE `Tasks` (
  `taskId` int(11) unsigned NOT NULL auto_increment,
  `authorId` int(11) unsigned default NULL,
  `executorId` int(11) unsigned default NULL,
  `task` text collate utf8_lithuanian_ci NOT NULL,
  PRIMARY KEY (`taskId`),
  KEY `authorId` (`authorId`),
  KEY `executorId` (`executorId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_lithuanian_ci;

CREATE TABLE `Users` (
  `userId` int(11) unsigned NOT NULL auto_increment,
  `login` varchar(255) collate utf8_lithuanian_ci NOT NULL default '',
  `password` varchar(255) collate utf8_lithuanian_ci NOT NULL default '',
  `personId` int(11) unsigned default NULL,
  PRIMARY KEY (`userId`),
  KEY `personId` (`personId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_lithuanian_ci;

CREATE TABLE `Persons` (
  `personId` int(11) unsigned NOT NULL auto_increment,
  `firstName` varchar(255) collate utf8_lithuanian_ci NOT NULL default '',
  `lastName` varchar(255) collate utf8_lithuanian_ci NOT NULL default '',
  `birthDate` date default NULL,
  PRIMARY KEY  (`personId`),
  KEY `firstName` (`firstName`),
  KEY `lastName` (`lastName`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_lithuanian_ci;

SELECT SQL_CALC_FOUND_ROWS `Tasks`.*
  , CONCAT(`Authors`.`firstName`, ' ', `Authors`.`lastName`) AS `author`
  , CONCAT(`Executors`.`firstName`, ' ', `Executors`.`lastName`) AS `executor`
FROM `Tasks`
  INNER JOIN `Users` AS `AU` ON (`AU`.`userId` = `Tasks`.`authorId`)
  INNER JOIN `Persons` AS `Authors` USING (`personId`)
  INNER JOIN `Users` AS `EU` ON (`EU`.`userId` = `Tasks`.`executorId`)
  INNER JOIN `Persons` AS `Executors` USING (`personId`)
LIMIT 100;

Hi Andrius,

The error you get in your query is according to ANSI SQL. The reason is that
the result of joining all tables but the last one contains two columns with the
name "personId", and when that table is joined with the last table via the
USING clause, the compiler correctly tells you that "personId" is ambiguous
since there is no way for the query compiler to know which one would you
like to join with.

FYI the result columns of the join of all tables but the last one are:

| personId | taskId | authorId | executorId | task  | userId | login | password | 
firstName | lastName | birthDate  | userId | login | password | personId |

Retrived by the query:

SELECT *
FROM Tasks
       JOIN
     Users AS AU ON (AU.userId = Tasks.authorId)
       JOIN
     Persons AS Authors USING (personId)
       JOIN
     Users AS EU ON (EU.userId = Tasks.executorId);

As you see "personId" occurs twice, which causes the error.