>Description:
If you have LOCK TABLES in effect and use table aliases (a
from clause like "from names a, addresses b", then refer to
columns by a.name or b.street) on a query, MYSQL complains
that the table alias isn't locked. However, if you try to lock
it, MYSQL says the table alias doesn't exist. This gets
really annoying when you need aliases to join a table against
itself.
>How-To-Repeat:
mysql> describe names;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | | PRI | 0 | |
| name | char(25) | | | | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql> describe addresses;
+--------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+-------+
| id | int(11) | | PRI | 0 | |
| street | char(60) | | | | |
| citystatezip | char(60) | | | | |
+--------------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> lock tables names write, addresses write;
Query OK, 0 rows affected (0.02 sec)
mysql> select a.name, b.street from names a, addresses b
-> where a.id = b.id and a.name = 'Bill Clinton';
ERROR 1100: Table 'a' was not locked with LOCK TABLES
mysql> unlock tables;
Query OK, 0 rows affected (0.01 sec)
mysql> lock tables names write, addresses write, a write, b write;
ERROR 1146: Table 'test.a' doesn't exist
mysql> quit
>Fix:
You can usually use the table name instead of aliases, which makes
for long queries. This is a problem if you want to join a table
against itself.
>Submitter-Id: <submitter ID>
>Originator: Gordon Burditt
>Organization:
Internet America
>MySQL support: licence
>Synopsis: LOCK TABLES doesn't resolve table aliases
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-3.22.19b (Source distribution)
>Server: /usr/local/bin/mysqladmin Ver 7.8 Distrib 3.22.19b, for unknown-freebsd3.1 on
> i386
TCX Datakonsult AB, by Monty
Server version 3.22.19b-log
Protocol version 10
Connection hammy.lonestar.org via TCP/IP
TCP port 3306
Uptime: 5 days 13 hours 49 min 29 sec
Threads: 1 Questions: 565364 Slow queries: 12 Opens: 231 Flush tables: 4 Open tables:
19
>Environment:
System: FreeBSD hammy.lonestar.org 3.1-RELEASE FreeBSD 3.1-RELEASE #2: Sun Apr 4 22:30:24
CDT 1999 gordon@stripped:/usr/src/sys/compile/HAMMY i386
Some paths: /usr/local/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: gcc version 2.7.2.1
Compilation info: CC='gcc' CFLAGS='' CXX='gcc' CXXFLAGS='' LDFLAGS=''
Configure command: ./configure --with-unix-socket-path=/var/tmp/mysql.sock
--with-low-memory --with-mit-threads=yes
Perl: This is perl, version 5.005_02 built for i386-freebsd
