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.

well, you have to list all the column names and supply and alias for ambiguous columns. — John Woo
– John Woo, Commented Oct 30, 2013 at 12:03
Do you have any kind of grouping/having clause in your inner query? If not, why not just add limit 1000 to your first query? — user359040
– user359040, Commented Oct 30, 2013 at 12:06
People very rarely need to use SELECT *, and using it in production code is never a good idea, and in almost all cases is just laziness. I'm betting in the 42 minutes since you asked this question (with no answers as I comment) that you could have written out all the columns you need. The benefit of this is not just removing the duplicate column error, it also future proofs your column positions against DDL changes such as new columns being added, or columns being removed, it will also reduce I/O on the server and network traffic. — GarethD
– GarethD, Commented Oct 30, 2013 at 12:42
In all my years I've never come across a need to join 50 tables in a single query. — gvee
– gvee, Commented Oct 30, 2013 at 13:35

simo.3792 · Accepted Answer · 2014-10-28 23:31:32Z

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.

AHHP · Accepted Answer · 2013-10-30 16:02:09Z

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.

edited Oct 30, 2013 at 16:02

answered Oct 30, 2013 at 13:32

AHHP

3,0974 gold badges35 silver badges41 bronze badges

Collectives™ on Stack Overflow

MySQL Duplicate column error only when query wrapped as subquery

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related