MySQL Duplicate column error only when query wrapped as subquery

Question

I have a select query like below:

SELECT * FROM A
LEFT JOIN B ON B.x = A.y
LEFT JOIN C ...
WHERE ....
GROUP BY ...
ORDER BY ...;

All tables have id column and query works well. Result have many id columns without error and driver handles ambiguous issues. But i need to have LIMIT on results so i wrap it with another select query like this:

SELECT * FROM (
    SELECT * FROM A
    LEFT JOIN B ON B.x = A.y
    LEFT JOIN C ...
    WHERE ....
    GROUP BY ...
    ORDER BY ...
) AS x WHERE 1 LIMIT 1000;

And now i get Duplicate column name 'id' error!

PS: The full query is complicated and i need to use * (listing column names is not possible), and i can't use limit in main query because of joins, group by order by and etc.

Answer 1

The reason you got the error is because of the aliasing AS X. But the problem really is that you used *, instead of listing the fields you want.

In the first query, the SELECT * actually produces fields such as:

A.id, A.name, A.description, B.id, B.name, B.date_started, C.id, C.name, C.isDeleted

This works fine, unless you try to reference a field by its name directly and don't use it's alias. Either way your SQL engine won't have a problem with this, whatever you are doing with the result set might still have a problem though.

However when you move your query into a subquery and alias the results AS X, then you end up with:

X.id, X.name, X.description, X.id, X.name, X.date_started, X.id, X.name, X.isDeleted

Now you can see why it's complaining. You can see why it's also bad to use*, because this combination may work for some period of time, and then you add a new field to an existing table, that is the same as another table, and bang, every query you have written with both of these tables, now needs to be rewritten.

Answer 2

I fixed it by self-join:

SELECT * FROM A
LEFT JOIN B ON B.x = A.y
LEFT JOIN C ...
JOIN (
    SELECT id FROM (
        SELECT id FROM A 
        WHERE ....
        GROUP BY ... 
        ORDER BY ...
    ) AS A1 WHERE 1 LIMIT 1000
) AS A2 ON A2.id = A.id
WHERE 1

MySQL executes it very fast.