Bug #9084 Select statement which aliases name of a locked table returns not locked error

• Submitted: 9 Mar 2005 23:24
• Modified: 11 Mar 2005 11:43
• Reporter: Craig Sargent
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.9
• OS: Linux (Fedora 3)
• CPU Architecture: Any

[9 Mar 2005 23:24] Craig Sargent

Description:
If you lock a table using 'Lock tables <table> WRITE'
and then do a select on the locked table but aliases the table name,
then you get back error 1100 
(table was not locked with LOCK TABLES)

How to repeat:
mysql> create table xx (x int);
Query OK, 0 rows affected (0.04 sec)

mysql> lock tables xx write;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from xx;
Empty set (0.00 sec)

mysql> select * from xx bn;
ERROR 1100 (HY000): Table 'bn' was not locked with LOCK TABLES

[11 Mar 2005 11:43] MySQL Verification Team

From the MySQL manual:

If your queries refer to a table using an alias, then you must lock the table using that same alias. It does not work to lock the table without specifying the alias: 

mysql> LOCK TABLE t READ;
mysql> SELECT * FROM t AS myalias;
ERROR 1100: Table 'myalias' was not locked with LOCK TABLES