MySQL Bugs: #33710: Spurious error 1060 from subquery

Bug #33710	Spurious error 1060 from subquery
Submitted:	6 Jan 2008 6:35	Modified:	11 Jan 2008 17:48
Reporter:	Peter Brawley (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S2 (Serious)
Version:	5.1.22	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
The parser properly accepts queries referencing qualified column names which are identical without their table qualifications, but mistakenly rejects the same query when it occurs in a subquery:

drop table t,u;
create table t(i int);
create table u(i int);
select t.i,u.i from t join u using (i);
select count(*) from (select t.i,u.i from t join u using (i)) as tmp;
ERROR 1060 (42S21): Duplicate column name 'i'

How to repeat:
As above

This is not a bug. Please, read the manual, http://dev.mysql.com/doc/refman/5.0/en/unnamed-views.html:

"Any columns in the subquery select list must have unique names. You can find this syntax described elsewhere in this manual, where the term used is “derived tables.”"

When the qualified names differ, and when they correlate with nothing in the outer query, that rule, documented or not, is a bug.

I think, you searched this syntax:

select count(*) as temp from t1 join t2 using(i);

Thanks, yes other syntaxes can retrieve a count, but that's relevant neither to the illogic of requiring different column names when they're not needed for correlation, nor to the scenario that revealed this bug (queries known only at runtime, and for which we want a precount).

Indeed we can get round the precount problem by using found_rows() instead of count(*), but that still leaves the illogic.

Peter,

the problem with: select count(*) from (select t.i,u.i from t join u using (i)) as tmp;
is, that you want to select two columns.

This will work too:
mysql> select count(*) from (select t.i from t join u using (i)) as tmp;

Agreed there are workarounds. None works particularly well for the problem at hand--getting a row count from a query specified by a user at runtime, and none satisfies the not-unreasonable expectation that an uncorrelated subquery should not throw irrelevant errors.