Strange MySQL Results

Question

I was wondering why the request

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

causes an error Duplicate column name? Although the inner query returns a result set with repeating columns marked _1, the outer one shows the column from the table.

Answer 1

This is the correct behavior because any columns in the subquery selection list must have a unique name ( Subqueries in the From section ). You can also check here , it was a bug in older versions of mysql that allowed you to do this.

Answer 2

columns in a subquery may 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 the column names that I composed