When doing a two-table delete where the two tables are one table joined to itself, I get the error

ERROR 1030: Got error 134 from table handler

when I delete (out of two matching rows) the one with the higher id field rather than the lower. Deleting the higher rather than the lower ID doesn't always cause the error, but deleting the lower rather than the higher seems to dependably avoid it.

I have not tried this on version 4.1. It looks similar to Bug #374, which, however, I was not able to duplicate.

-- A demonstration of bug with a multiple-table delete with -- a self-join. mysql --version gives: -- mysql Ver 12.21 Distrib 4.0.14, for pc-linux (i686)

DROP DATABASE IF EXISTS MultiTableDeleteDemoTemp; CREATE DATABASE MultiTableDeleteDemoTemp; USE MultiTableDeleteDemoTemp;

CREATE TABLE t ( id int not null auto_increment PRIMARY KEY, str char(32));

-- This two-row dataset is the smallest I could find that -- demonstrated the problem for me. Most but not all -- datasets with duplicates are vulnerable to the problem. -- (For instance, "('pineapple'), ('banana'), ('pear'), -- ('apple'), ('apple')" works fine for me.) INSERT INTO t (str) VALUES ('apple'), ('apple');

-- Display duplicates: SELECT * FROM t, t AS t2 WHERE t.str = t2.str AND t.id > t2.id;

-- If < is used, things work fine -- if > is used, the error -- message -- ERROR 1030: Got error 134 from table handler -- is issued. This is presumably because it's assuming two -- different tables rather than one aliased one. DELETE t FROM t, t AS t2 WHERE t.str = t2.str AND t.id > t2.id;

-- Display duplicates again -- should be none, but may be -- more depending on how many duplicates were in the -- original dataset and how far the DELETE got before -- erroring out: SELECT * FROM t, t AS t2 WHERE t.str = t2.str AND t.id > t2.id;

As noted above, deleting rows that were inserted later rather than earlier seems to be a workaround.

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.0.14-standard-log Protocol version 10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 3 days 16 hours 23 min 0 sec

Threads: 5 Questions: 224619 Slow queries: 24 Opens: 21889 Flush tables: 1 Open tables: 64 Queries per second avg: 0.706

System: Linux PINE 2.4.20-28.9 #1 Thu Dec 18 13:45:22 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.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info
--en able-shared --enable-threads=posix --disable-checking --with-system-zlib
--enable -__cxa_atexit --host=i386-redhat-linux Thread model: posix gcc version 3.2.2 20030222 (Red Hat Linux 3.2.2-5) Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc' CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Jan 30 2004 /lib/libc.so.6 -> libc-2.3.2.so -rwxr-xr-x 1 root root 1561228 Nov 12 2003 /lib/libc-2.3.2.so -rw-r--r-- 1 root root 2332200 Nov 12 2003 /usr/lib/libc.a -rw-r--r-- 1 root root 204 Nov 12 2003 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/local/mysql' '--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/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-static' '--with-mysqld-ldflags=-all-static' '--with-innodb' 'CFLAGS=-O2 -mcpu=pentiumpro' 'CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors' 'CXX=gcc'