Hello. 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 returns 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