List:General Discussion« Previous MessageNext Message »
From:gordonb Date:April 30 1999 4:24pm
Subject:LOCK TABLES doesn't resolve table aliases
View as plain text  
>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
Thread
LOCK TABLES doesn't resolve table aliasesgordonb30 Apr
  • Re: LOCK TABLES doesn't resolve table aliasesChristian Mack30 Apr