Description:
Use of a USING clause allows ambiguous column references for columns that did not participate in the USING clause.
The manual states, "[...] in cases when MySQL formerly issued an error that some column is ambiguous, the query now is handled correctly. This is due to the fact that MySQL now treats the common columns of NATURAL or USING joins as a single column, so when a query refers to such columns, the query compiler does not consider them as ambiguous."
It is not clear that this behavior was intended to apply to common columns (in the joined tables) that did not participate in the USING clause, and the ambiguous reference is disallowed in other clauses.
If the ambiguous column reference occurs in the SELECT list, the additional "common" columns are actually removed from the result set.
The behavior appears to be a partial mix of USING and NATURAL JOIN.
How to repeat:
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 ( a INT, b INT );
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
CREATE TABLE t2 LIKE t1;
INSERT INTO t2 SELECT a,b*100 FROM t1;
--error 1052
SELECT * FROM t1 INNER JOIN t2 USING (a) WHERE b=1;
--error 1052
SELECT * FROM t1 INNER JOIN t2 USING (a) WHERE b=1 HAVING b=1;
--error 1052
SELECT a, b FROM t1 INNER JOIN t2 USING (a) WHERE b=1 HAVING b=1;
--error 1052
SELECT b FROM t1 INNER JOIN t2 USING (a);
--error 1052
SELECT b FROM t1 INNER JOIN t2 USING (a) WHERE b=100;
...
mysql> SELECT * FROM t1 INNER JOIN t2 USING (a) WHERE b=1;
+------+------+------+
| a | b | b |
+------+------+------+
| 1 | 1 | 100 |
+------+------+------+
1 row in set (0.00 sec)
mysql> SELECT * FROM t1 INNER JOIN t2 USING (a) WHERE b=1 HAVING b=1;
ERROR 1052 (23000): Column 'b' in having clause is ambiguous
mysql> SELECT a, b FROM t1 INNER JOIN t2 USING (a) WHERE b=1 HAVING b=1;
+------+------+
| a | b |
+------+------+
| 1 | 1 |
+------+------+
1 row in set (0.00 sec)
mysql> SELECT b FROM t1 INNER JOIN t2 USING (a);
+------+
| b |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql> SELECT b FROM t1 INNER JOIN t2 USING (a) WHERE b=100;
Empty set (0.00 sec)
Suggested fix:
I assume ER_NON_UNIQ_ERROR should be raised. If not, these behaviors should be documented.
