>Description:
Multi-delete stops working in all forms when used along with table
aliases, it gives an error saying
ERROR 1109 (42S02): Unknown table 'test_base' in MULTI DELETE
Which definately is NOT the case. After trying all kinds of
combinations I reliased that the problem shows
up only when multi-delete is used with table aliases.
>How-To-Repeat:
create table test_base(id INT(11) primary key);
create table test_lead(lead_id INT(11) primary key);
insert into test_base values(1);
insert into test_base values(2);
insert into test_base values(3);
insert into test_base values(4);
insert into test_base values(5);
insert into test_lead values(1);
insert into test_lead values(2);
insert into test_lead values(3);
--PROBLEM--
delete test_base, test_lead from test_base a join test_lead b on
(a.id = b.lead_id);
ERROR 1109 (42S02): Unknown table 'test_base' in MULTI DELETE
delete test_base, test_lead from test_base a, test_lead b where
(a.id = b.lead_id);
ERROR 1109 (42S02): Unknown table 'test_base' in MULTI DELETE
delete from test_base, test_lead using test_base a, test_lead b
where (a.id = b.lead_id);
ERROR 1109 (42S02): Unknown table 'test_base' in MULTI DELETE
show tables;
+----------------+
| Tables_in_test |
+----------------+
| test_base |
| test_lead |
+----------------+
2 rows in set (0.00 sec)
>Fix:
delete test_base, test_lead from test_base, test_lead where
(test_base.id = test_lead.lead_id);
Query OK, 6 rows affected (0.01 sec)
OR
delete test_base, test_lead from test_base join test_lead on
(test_base.id = test_lead.lead_id);
Query OK, 0 rows affected (0.00 sec)
>Submitter-Id: Asim Thakker
>Originator: Asim Thakker
>Organization:
Data Management
>MySQL support: none licence
>Synopsis: BUG in Multi-Delete when table aliases included in query
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-4.1.3-beta-standard (Official MySQL-standard binary)
>Server: /usr/local/mysql/bin/mysqladmin Ver 8.40 Distrib 4.1.3-beta, for
pc-linux on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 4.1.3-beta-standard
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 3 hours 11 min 41 sec
Threads: 2 Questions: 24763 Slow queries: 20 Opens: 465 Flush tables: 1
Open tables: 36 Queries per second avg: 2.153
>C compiler: 2.95.3
>C++ compiler: 2.95.3
>Environment:
<machine, os, target, libraries (multiple lines)>
System: Linux damagecase 2.4.23 #7 SMP Thu Dec 4 14:05:19 EST 2003 i686 i686
i386 GNU/Linux
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man
--infodir=/usr/share/info --enable-shared --enable-thread\
s=posix --disable-checking --host=i386-redhat-linux --with-system-zlib
--enable-__cxa_atexit
Thread model: posix
gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7)
Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc'
CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors' L\
DFLAGS='' ASFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 14 Aug 7 2003 /lib/libc.so.6 ->
libc-2.2.93.so
-rwxr-xr-x 1 root root 1235468 Sep 5 2002 /lib/libc-2.2.93.so
-rw-r--r-- 1 root root 2233342 Sep 5 2002 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Sep 5 2002 /usr/lib/libc.so
Configure command: ./configure '--prefix=/usr/local/mysql'
'--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/m\
ysql/bin' '--with-comment=Official MySQL-standard binary'
'--with-extra-charsets=complex' '--with-server-suffix=-standard' '-\
-enable-thread-safe-client' '--enable-local-infile' '--enable-assembler'
'--disable-shared' '--with-client-ldflags=-all-stati\
c' '--with-mysqld-ldflags=-all-static' '--with-readline'
'--with-embedded-server' '--with-innodb' 'CFLAGS=-O2 -mcpu=pentiumpr\
o' 'CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors' 'CXX=gcc'
If any more information needed, let me know at [EMAIL PROTECTED]
Thanks,
Asim Thakker
[EMAIL PROTECTED]
ext #4176
