MySQL Bugs: #25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join

Bug #25575	ERROR 1052 (Column in from clause is ambiguous) with sub-join
Submitted:	12 Jan 2007 11:08	Modified:	9 Feb 2007 20:28
Reporter:	Hervé Guillemet	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.34-BK, 5.0.27	OS:	Linux (Linux)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
Case below reports 
ERROR 1052 (23000): Column 'tid' in from clause is ambiguous
while tid does not appear anywhere in the request.

The request does not fail if any one of the joins is removed.

How to repeat:
CREATE TEMPORARY TABLE f (fid INT, iid INT);
CREATE TEMPORARY TABLE i (iid INT);
CREATE TEMPORARY TABLE i1 (iid INT, tid INT);
CREATE TEMPORARY TABLE i2 (iid INT, tid INT);
CREATE TEMPORARY TABLE i3 (iid INT, tid INT);

SELECT fid FROM f JOIN (i
LEFT JOIN i1 USING (iid)
LEFT JOIN i2 USING (iid)
LEFT JOIN i3 USING (iid)
) USING (iid);

Thank you for a bug report. Verified just as described with 5.0.34-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.34-debug Source distribution

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

mysql> CREATE TEMPORARY TABLE f (fid INT, iid INT);
CRQuery OK, 0 rows affected (0.01 sec)

mysql> CREATE TEMPORARY TABLE i (iid INT);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TEMPORARY TABLE i1 (iid INT, tid INT);
CQuery OK, 0 rows affected (0.01 sec)

mysql> CREATE TEMPORARY TABLE i2 (iid INT, tid INT);
CQuery OK, 0 rows affected (0.01 sec)

mysql> CREATE TEMPORARY TABLE i3 (iid INT, tid INT);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT fid FROM f JOIN (i
    -> LEFT JOIN i1 USING (iid)
    -> LEFT JOIN i2 USING (iid)
    -> LEFT JOIN i3 USING (iid)
    -> ) USING (iid);
ERROR 1052 (23000): Column 'tid' in from clause is ambiguous
mysql> SELECT fid FROM f JOIN (i LEFT JOIN i1 USING (iid) LEFT JOIN i2 USING (i
id) ) USING (iid);
Empty set (0.00 sec)

Let's check EXPLAIN results:

mysql> explain extended SELECT fid FROM f JOIN (i LEFT JOIN i1 USING (iid) LEFT
 JOIN i2 USING (iid)) USING (iid);
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows
 | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL
 | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-----------------------------------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `test`.`f`.`fid` AS `fid` from `test`.`f` join `test`.`i` left j
oin `test`.`i1` on(multiple equal(`test`.`f`.`iid`)) left join `test`.`i2` on((m
ultiple equal(`test`.`f`.`iid`) and multiple equal(`test`.`i1`.`tid`))) where (`
test`.`i`.`iid` = `test`.`f`.`iid`)
1 row in set (0.00 sec)

While:

mysql> explain extended SELECT fid FROM f JOIN (i LEFT JOIN i1 USING (iid) LEFT
 JOIN i2 USING (iid) LEFT JOIN i3 USING (iid) ) USING (iid);
ERROR 1052 (23000): Column 'tid' in from clause is ambiguous
mysql> show warnings\G
*************************** 1. row ***************************
  Level: Error
   Code: 1052
Message: Column 'tid' in from clause is ambiguous
*************************** 2. row ***************************
  Level: Note
   Code: 1003
Message: select `fid` AS `fid` from (`test`.`f` join (((`test`.`i` left join `te
st`.`i1` on((`test`.`i`.`iid` = `test`.`i1`.`iid`))) left join `test`.`i2` on((`
test`.`i`.`iid` = `test`.`i2`.`iid`))) left join `test`.`i3` on(((`test`.`i`.`ii
d` = `test`.`i3`.`iid`) and (`test`.`i1`.`tid` = `test`.`i3`.`tid`)))))
2 rows in set (0.00 sec)

I think, it is a bug. I see no obvious reasons for absolutely different internal representation of query, and error message.

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/commits/18559

ChangeSet@1.2385, 2007-01-22 18:15:57+02:00, gkodinov@macbook.gmz +6 -0
  BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join
   Two problems here:
  
   Problem 1:
  
   While constructing the join columns list the optimizer does as follows:
    1. Sets the join_using_fields/natural_join members of the rightmost 
     table
    2. Makes a "table" (TABLE_LIST) to wrap the two tables.
    3. Assigns the join_using_fields/is_natural_join of the wrapper table
     using join_using_fields/natural_join of the rightmost table
    4. Sets join_using_fields to NULL for the rightmost table
    5. Passes the wrapping table up to the same procedure on the upper 
     level.
  
   Step 1 overrides the the join_using_fields that are set for a nested 
   join wrapping table in step 4.
   Fixed by making a designated variable join_using to pass the data from 
   step 1 to step 4 without destroying the wrapping table data.
  
   Problem 2:
  
   The optimizer checks for ambiguous columns while transforming 
   NATURAL JOIN/JOIN USING to JOIN ON. While doing that there was no
   distinction between columns that are used in the generated join
   condition (where ambiguity can be checked) and the other columns
   (where ambiguity can be checked only when resolving references
   coming from outside the JOIN construct itself).
   Fixed by allowing the non-USING columns to be present in multiple 
   copies in both sides of the join and moving the ambiguity check 
   to the place where unqualified references to the join columns are
   resolved.

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/commits/19024

ChangeSet@1.2385, 2007-01-30 19:22:30+02:00, gkodinov@macbook.gmz +8 -0
  BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join
   Two problems here:
  
   Problem 1:
  
   While constructing the join columns list the optimizer does as follows:
    1. Sets the join_using_fields/natural_join members of the right JOIN 
     operand.
    2. Makes a "table reference" (TABLE_LIST) to parent the two tables.
    3. Assigns the join_using_fields/is_natural_join of the wrapper table
     using join_using_fields/natural_join of the rightmost table
    4. Sets join_using_fields to NULL for the right JOIN operand.
    5. Passes the parent table up to the same procedure on the upper 
     level.
  
   Step 1 overrides the the join_using_fields that are set for a nested 
   join wrapping table in step 4.
   Fixed by making a designated variable SELECT_LEX::prev_join_using to 
   pass the data from step 1 to step 4 without destroying the wrapping 
   table data.
  
   Problem 2:
  
   The optimizer checks for ambiguous columns while transforming 
   NATURAL JOIN/JOIN USING to JOIN ON. While doing that there was no
   distinction between columns that are used in the generated join
   condition (where ambiguity can be checked) and the other columns
   (where ambiguity can be checked only when resolving references
   coming from outside the JOIN construct itself).
   Fixed by allowing the non-USING columns to be present in multiple 
   copies in both sides of the join and moving the ambiguity check 
   to the place where unqualified references to the join columns are
   resolved (find_field_in_natural_join()).

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/commits/19085

ChangeSet@1.2385, 2007-01-31 16:04:38+02:00, gkodinov@macbook.gmz +8 -0
  BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join
   Two problems here:
  
   Problem 1:
  
   While constructing the join columns list the optimizer does as follows:
    1. Sets the join_using_fields/natural_join members of the right JOIN 
     operand.
    2. Makes a "table reference" (TABLE_LIST) to parent the two tables.
    3. Assigns the join_using_fields/is_natural_join of the wrapper table
     using join_using_fields/natural_join of the rightmost table
    4. Sets join_using_fields to NULL for the right JOIN operand.
    5. Passes the parent table up to the same procedure on the upper 
     level.
  
   Step 1 overrides the the join_using_fields that are set for a nested 
   join wrapping table in step 4.
   Fixed by making a designated variable SELECT_LEX::prev_join_using to 
   pass the data from step 1 to step 4 without destroying the wrapping 
   table data.
  
   Problem 2:
  
   The optimizer checks for ambiguous columns while transforming 
   NATURAL JOIN/JOIN USING to JOIN ON. While doing that there was no
   distinction between columns that are used in the generated join
   condition (where ambiguity can be checked) and the other columns
   (where ambiguity can be checked only when resolving references
   coming from outside the JOIN construct itself).
   Fixed by allowing the non-USING columns to be present in multiple 
   copies in both sides of the join and moving the ambiguity check 
   to the place where unqualified references to the join columns are
   resolved (find_field_in_natural_join()).

The fix has been pushed into 5.0.36, 5.1.16-beta main trees.

Noted in 5.0.36, 5.1.16 changelogs.

An error in the name resolution of nested JOIN ... USING constructs
was corrected.

Here is an alternative test case that had it's behaviour corrected between 5.0.33 and 5.0.37.

CREATE TABLE `a` (
  `c` bigint(20),
  `d` varchar(5)
);
CREATE TABLE `b` (
  `c` bigint(20),
  `d` varchar(5)
);
select d from a inner join b using (c);

5.0.27 to 5.0.33 does not report an error.

5.0.37 to 5.0.67 correctly reports:
  ERROR 1052 (23000): Column 'd' in field list is ambiguous

Cheers,
Peter (Stig) Edwards

SELECT 
    emp_no,
    first_name,
    last_name,
    CASE
        WHEN dm.emp_no IS NOT NULL THEN 'manager'
        ELSE 'employee'
    END AS is_manager
FROM
    employees e
    LEFT JOIN dept_manager dm on dm.emp_no=e.emp_no
    where e.emp_no>109990;                                  
    

I got an error here 
error 1052 column emp_no field is ambiguous

SELECT 
    emp_no,
    first_name,
    last_name,
    CASE
        WHEN dm.emp_no IS NOT NULL THEN 'manager'
        ELSE 'employee'
    END AS is_manager
FROM
    employees e
    LEFT JOIN dept_manager dm on dm.emp_no=e.emp_no
    where e.emp_no>109990;                                  
    

if I run this code without having emp_no in the Select Statement it's working but what do I do if I need employee_no as well.