| Bug #25575 | ERROR 1052 (Column in from clause is ambiguous) with sub-join | ||
|---|---|---|---|
| Submitted: | 12 Jan 2007 12:08 | Modified: | 9 Feb 2007 21:28 |
| Reporter: | Hervé Guillemet | ||
| Status: | Closed | ||
| Category: | Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.0.34-BK, 5.0.27 | OS: | Linux (Linux) |
| Assigned to: | Georgi Kodinov | Target Version: | |
[12 Jan 2007 12:08]
Hervé Guillemet
[12 Jan 2007 16:50]
Valeriy Kravchuk
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.
[22 Jan 2007 17:16]
Bugs System
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.
[30 Jan 2007 18:23]
Bugs System
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()).
[31 Jan 2007 15:06]
Bugs System
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()).
[3 Feb 2007 7:21]
Igor Babaev
The fix has been pushed into 5.0.36, 5.1.16-beta main trees.
[9 Feb 2007 21:28]
Paul DuBois
Noted in 5.0.36, 5.1.16 changelogs. An error in the name resolution of nested JOIN ... USING constructs was corrected.
[8 Oct 2008 15:09]
Peter Edwards
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
