MySQL Bugs: #35021: illegal FOR UPDATE / LOCK IN SHARE MODE syntax in subselects

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:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.1/6.0	OS:	Any
Assigned to:	Martin Hansson	CPU Architecture:	Any

Description:
The parser accepts FOR UPDATE and LOCK IN SHARE MODE clauses in sub selects.

This syntax is only meaningful for select statements, and should not be
accepted in sub selects query expressions.

See related bug#33204, for a similar issue with INTO in subselects.

How to repeat:
create table t1 (a int);

Should be illegal:
set @broken = (select * from t1 for update);
set @broken = (select * from t1 lock in share mode);

Should be illegal:
select a from t1 where a in (select a from t1 for update);
select a from t1 where a in (select a from t1 lock in share mode);

Thank you for the bug report.

why do you think this syntax is meaningless in subselects ?

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.

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.