Bug #35021 | illegal FOR UPDATE / LOCK IN SHARE MODE syntax in subselects | ||
---|---|---|---|
Submitted: | 4 Mar 2008 2:49 | Modified: | 24 Mar 2008 21:21 |
Reporter: | Marc ALFF | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: Parser | Severity: | S3 (Non-critical) |
Version: | 5.1/6.0 | OS: | Any |
Assigned to: | Martin Hansson | CPU Architecture: | Any |
[4 Mar 2008 2:49]
Marc ALFF
[4 Mar 2008 2:55]
MySQL Verification Team
Thank you for the bug report.
[7 Mar 2008 13:11]
Sergei Golubchik
why do you think this syntax is meaningless in subselects ?
[21 Mar 2008 22:27]
Marc ALFF
At first reading, my impression was that "FOR UPDATE" or "LOCK IN SHARE MODE" is a property global to the statement, similar to "SQL_CACHE" / "SQL_NO_CACHE". This was actually incorrect: "FOR UPDATE" / "LOCK IN SHARE MODE" is in fact a property of a select expression (SELECT_LEX), so that, from a pure syntax point of view, having these clauses in sub selects is valid. From a semantic point of view, I will defer to our SQL experts to clarify whether this syntax should be allowed or not in this cases, (and clarify the semantic of it if allowed), since it's not obvious.
[24 Mar 2008 21:21]
Peter Gulutzan
The MySQL Reference Manual says: "A subquery can contain any of the keywords or clauses that an ordinary SELECT can contain: DISTINCT, GROUP BY, ORDER BY, LIMIT, joins, index hints, UNION constructs, comments, functions, and so on." The phrases "any" or "and so on" conceivably mean "including FOR UPDATE". We could not blame a user for thinking so, or punish a user for acting on that thought. When MySQL introduced views, there was specific discussion of CREATE VIEW v AS SELECT * FROM t FOR UPDATE; and the decision was to allow it, with the understanding that locks would apply "for the table" rather than "for the select". Although MySQL in fact ignores the clause, I believe the decision is a precedent because a view can replace a subquery in a FROM clause. And the situation is different from SELECT ... INTO ..., because it does not affect whether MySQL returns a result set or not. I conclude that this is not a bug.