Table handler error with self-join multi-table DELETE

>Description:
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.

>How-To-Repeat:
-- 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;

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

>Originator: Aaron Brown
>Organization:
Oak Adaptive
>MySQL support: none
>Synopsis: Table handler error w/ self-join DELETE; depends on order of deletion
>Category: mysql
>Class:  sw-bug
>Release: mysql-4.0.14-standard (Official MySQL-standard binary)
>Server: /usr/local/bin/mysqladmin  Ver 8.40 Distrib 4.0.14, 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.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
>C compiler:    2.95.3
>C++ compiler:  2.95.3
>Environment:
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'



-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]