MySQL Bugs: #20743: Inconsistent behaviour with subqueries, joins, and SELECT *

Bug #20743	Inconsistent behaviour with subqueries, joins, and SELECT *
Submitted:	28 Jun 2006 3:30	Modified:	28 Jun 2006 23:24
Reporter:	[ name withheld ]	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.22	OS:	Windows (Windows)
Assigned to:		CPU Architecture:	Any

Description:
The query:
SELECT * FROM tbl_job j JOIN tbl_client c ON j.cl_id = c.cl_id
is allowable.

But:
SELECT count(1) AS totalrec
FROM (SELECT * FROM tbl_job j JOIN tbl_client c ON j.cl_id = c.cl_id) AS s;

complains about Duplicate column name 'cl_id'

I was "told" that you "can't" SELECT * with a JOIN; because of ambigious columns, which is fine; but if mysql 5 lets me do it in a normal query, it SHOULD let me do it in a subquery. If it doesn't let me do the subquery, it should be because the normal query doesn't work.

What *SHOULD* it be?

How to repeat:
SELECT count(1) AS totalrec
FROM (SELECT * FROM tbl_job j JOIN tbl_client c ON j.cl_id = c.cl_id) AS s;

Suggested fix:
Either allow one or the other, but maintain consistency.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

You can also read about current behaviour here: http://bugs.mysql.com/bug.php?id=6709

I can't find it ANYWHERE in the manual as stating that you can't do this; in particular I'd expect it in any of:
http://dev.mysql.com/doc/refman/5.0/en/unnamed-views.html
http://dev.mysql.com/doc/refman/5.0/en/subqueries.html
http://dev.mysql.com/doc/refman/5.0/en/row-subqueries.html

There's only a vague comment about UNIONs.

Thank you for the feedback. Here is the explanation:

http://dev.mysql.com/doc/refman/5.0/en/unnamed-views.html

13.2.8.8. Subqueries in the FROM clause

Subqueries are legal in a SELECT statement's FROM clause. The actual syntax is:

SELECT ... FROM (subquery) [AS] name ...

The [AS] name clause is mandatory, because every table in a FROM clause must have a name. 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.” 

"Any columns in the subquery select list must have unique names"