MySQL Bugs: #69237: Nonsense limitations on subqueries and/or ORDER BY/LIMIT with JOIN

Bug #69237	Nonsense limitations on subqueries and/or ORDER BY/LIMIT with JOIN
Submitted:	14 May 2013 22:22	Modified:	8 Jan 2014 8:50
Reporter:	matteo sisti sette	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I know this is all documented, but these limitations are nonsense and at least one of them should be relaxed, as the combination of the two obliges you to do mltiple queries in a lot of cases where it should be completely trivial to do the same with one query.

Consider this:

UPDATE user SET preferential=1 WHERE id=(SELECT handful.user_id FROM handful JOIN user ON user.id=handful.user_id WHERE user.discontinuous=0 AND `time`>NOW()-INTERVAL 120 SECOND ORDER BY user.connection_time LIMIT 1)

That can't be done, because you can't mention in the inner query a table that is going updated in the outer query: a completely nonsense limitation at least when the field being updated is not mentioned in the subquery.

In some cases this could be worked around by using joins...

UPDATE user LEFT JOIN handful ON handful.user_id=user.id AND handful.time>NOW()-INTERVAL 120 SECOND SET user.preferential=1 WHERE user.discontinuous=0 AND (handful.id IS NOT NULL OR user.connection_time > NOW()-INTERVAL 120 SECOND) ORDER BY connection_time LIMIT 1

...except this can't be done either, because of the other nonsense limitation, that doesn't allow to use ORDER BY and LIMIT in UPDATE with multiple tables.

How to repeat:
I know this is all documented, but these limitations are nonsense and at least one of them should be relaxed, as the combination of the two obliges you to do mltiple queries in a lot of cases where it should be completely trivial to do the same with one query.

Consider this:

UPDATE user SET preferential=1 WHERE id=(SELECT handful.user_id FROM handful JOIN user ON user.id=handful.user_id WHERE user.discontinuous=0 AND `time`>NOW()-INTERVAL 120 SECOND ORDER BY user.connection_time LIMIT 1)

That can't be done, because you can't mention in the inner query a table that is going updated in the outer query: a completely nonsense limitation at least when the field being updated is not mentioned in the subquery.

In some cases this could be worked around by using joins...

UPDATE user LEFT JOIN handful ON handful.user_id=user.id AND handful.time>NOW()-INTERVAL 120 SECOND SET user.preferential=1 WHERE user.discontinuous=0 AND (handful.id IS NOT NULL OR user.connection_time > NOW()-INTERVAL 120 SECOND) ORDER BY connection_time LIMIT 1

...except this can't be done either, because of the other nonsense limitation, that doesn't allow to use ORDER BY and LIMIT in UPDATE with multiple tables.

This is established/documented behaviour, thus a feature request.

I've tagged this as "To be fixed later" rather than "Won't fix" as we are happy to consider the request and keep it in mind--but please be aware that we currently have no plans (that I'm aware of, at least) to implement the change asked for here, and it is not likely to be done in any current MySQL version.

Thanks!

Sad to hear that, because these are two tremendous design flaws, even though established and documented.

Btw i notice this has been marked as belonging to the "Optimizer"; I don't know if that is the component responsible for this, but this is not about optimizing things, it's about being able vs not being able to do things at all.

Setting to "verified" since we will discontinue the use of "to be fixed
later".