Strange MySQL results

I was wondering, why does the query

select * from (select * from users join users a) b

cause the Duplicate column name error? While the inner query return开发者_C百科s a result set with duplicate columns, postfixed with _1, the outer one reveals a column from the table.

It's the proper behavior because any columns in the subquery select list must have unique name(Subqueries in the From Clause). You can also check here, it was a bug in old mysql versions that allowed you to do this.

the columns in the sub query might have unique names so do this

select a.id, b.id, a.col1, b.col2, b.col3 from (select a.col1, a.id  from users join users a) b

where id,col1,col2,col3 are column names I made up