List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:April 30 1999 6:21pm
Subject:Re: LOCK TABLES doesn't resolve table aliases
View as plain text  
gordonb@stripped wrote:
> 
> >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

Hi gordonb

This is no bug.
This is the described behaviour for LOCK TABLES.
Just give aliases to the LOCK command.

Tschau
Christian

Thread
LOCK TABLES doesn't resolve table aliasesgordonb30 Apr
  • Re: LOCK TABLES doesn't resolve table aliasesChristian Mack30 Apr