MySQL Bugs: #3281: Queries with more than one subquery on locked tables failes

Bug #3281	Queries with more than one subquery on locked tables failes
Submitted:	24 Mar 2004 6:24	Modified:	30 Mar 2004 9:01
Reporter:	Jochen Kokemüller	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	4.1.2-alpha-nightly-20040311-log	OS:	Linux (linux)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
If i perform a delete query depending on more than one subquery on locked tables it returns with the statement "table .. not locked". Whereas the query works if i reduce myself on just on of the subqueries.

How to repeat:
I do not think the table definitions are important, so i omit them

LOCK TABLES docListLogical WRITE, valList WRITE

//This works
DELETE FROM valList WHERE (valDocId != ANY (SELECT docId FROM docListLogical))

//And this aswell works
DELETE FROM valList WHERE (valDocId = ANY (SELECT docId FROM docListLogical WHERE (docExpireDate<now()) AND (docAutoDelete=1)))

//But the combination of the above to does not work
DELETE FROM valList WHERE (valDocId = ANY (SELECT docId FROM docListLogical WHERE (docExpireDate<now()) AND (docAutoDelete=1))) OR (valDocId != ANY (SELECT docId FROM docListLogical))

It returns:
ERROR 1100 (HY000): Table 'docListLogical' was not locked with LOCK TABLES
what is IMHO not true!

If you alias the second instance of the same table, and lock the alias which you will use for it, the query does work as one would expect, so I am going to verify that this occurs.

If the technical behavior is not modified, the manual needs to be updated as it currently does not mention the need for aliasing when locking a table which is referenced multiple times via subqueries.

Thank you for bug reposrt, but it is expected behaviour. Locked table can't be 
used more then one time (it is current limitation and will be removed in 
future versions). It is described in LOCK TABLES chapter in our documentation, 
but this chapter was written when we had not subqueries and I agree that using 
locked table several times with same name in subquery is not obvious according 
our manual. We will change documentation soon. 
 
You can use locking table with alias and use tables with different aliases in 
subqueries that subqueries.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Checked the documentation for LOCK TABLES and think it's ok