MySQL Bugs: #5651: subquery not working with locked table

Bug #5651	subquery not working with locked table
Submitted:	19 Sep 2004 15:18	Modified:	7 Oct 2004 4:30
Reporter:	Ilkka Prusi	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.3, 4.1.4	OS:	Windows (Windows (2000 and XP))
Assigned to:	Lachlan Mulcahy	CPU Architecture:	Any

Description:
When running a query with subselect on a locked MyISAM table server returns error:
[localhost] ERROR 1100: Table 'lock_test' was not locked with LOCK TABLES

How to repeat:
On a MyISAM table 'lock_test' with field 'id' (primary key not null auto_increment) following SQL-script:

LOCK TABLE lock_test READ;
SELECT id
FROM lock_test
WHERE id IN 
( SELECT id FROM lock_test );

Same behavior with LOCK TABLE WRITE.
Not using subquery works.

(Note that while this query does not make much sense it repeats the error.)

This is not a bug.. see this extract from the manual:

"When you use LOCK TABLES, you must lock all tables that you are going to use in your queries. 
While the locks obtained with a LOCK TABLES statement are in effect, you cannot access any 
tables that were not locked by the statement. Also, you cannot use a locked table multiple times 
in one query - use aliases for that. Note that in that case you must get a lock for each alias 
separately."

Please read more here:
http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html

Bug #25180 was marked as duplicate of this bug.