>Description:
I am using mysql 4.04.
Here is a quote from the mysql manual that started me on this adventure:
"You can have non-unique keys in a HEAP table (this isn't common for hashed
tables)."
Based on this claim, I designed a database which took advantage of non-unique keys in
HEAP tables. A number of things myseriously failed to work right, however, and
problems could be shown only to surface when the tables in question were of type HEAP
rather than anything else.
The below repro script demonstrates the problem in just about the simplest cast
possible. There is one key and one "value" column. Two rows are inserted, with the
same value for the key. Doing an unqualified row count gives the correct number, 2,
but doing a row count with a traversal by the key only yields a value of 1! Clearly
what is happening is that the underlying hash index implementation does not utilize
any sort of chaining to bin duplicate keys, or if it does, the traversal code stops
after reading the first hashed value.
This problem has deeper implications. I actually first ran into it when doing JOINs
between a table and a heap table with non-unique index: when the non-unique index was
involved in the JOIN condition, less rows were produced in the output than there
should have been. Actually, this means the relational algebra is broken...
Anyway, strictly speaking, the above claim from the documentation is not violated by
these phenomenon; you can indeed have non-unique keys in a HEAP table. Just don't
expect them to work =) I hope this is in fact a bug, and the claim was not put forth
with this trivial meaning (that would seem fairly dishonest).
I checked the changelogs for newer versions and didn't see anything referring to this
bug, so that is why I have not tried anything later than 4.04.
>How-To-Repeat:
create temporary table heaptest (id int default 0, name varchar(32), key(id))
TYPE=Heap;
insert into heaptest values(1, 'foo');
insert into heaptest values(1, 'bar');
select count(*) as unqualified_count from heaptest;
select count(*) as count_by_nonunique_key from heaptest where id=1;
>Fix:
I have no clue, implementation wise (I wish I had time to go look...). I'm
assuming that the HEAP index implementation is either not chained, or the index
traversal code is not aware of this chaining. I'd be surprised if it was the former,
because that would mean someone knowingly broke the relational algebra.
>Submitter-Id: <submitter ID>
>Originator: Aaron Krowne
>Organization:
Digital Library Research Lab
Virginia Tech
Blacksburg, VA, USA
>MySQL support: none
>Synopsis: non-unique indicies in HEAP tables handled incorrectly.
>Severity: critical
>Priority: high
>Category: mysql
>Class: sw-bug
>Release: mysql-4.0.4-beta (Official MySQL RPM)
>Environment:
System: Linux shaun 2.4.18 #1 Tue Dec 3 05:32:59 EST 2002 i686 unknown
Architecture: i686
Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.4/specs
gcc version 2.95.4 20010902 (Debian prerelease)
Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='gcc'
CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors
-fno-exceptions -fno-rtti -mpentium' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 14 Dec 3 05:53 /lib/libc.so.5 ->
libc.so.5.4.46
-rw-r--r-- 1 root root 563068 Feb 4 2002 /lib/libc.so.5.4.46
lrwxrwxrwx 1 root root 13 Dec 3 05:53 /lib/libc.so.6 -> libc-2.3.1.so
-rwxr-xr-x 1 root root 1109068 Nov 19 13:13 /lib/libc-2.3.1.so
-rw-r--r-- 1 root root 2344038 Nov 19 13:14 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Nov 19 13:14 /usr/lib/libc.so
Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static
--with-client-ldflags=-all-static --without-berkeley-db --with-innodb --without-vio
--without-openssl --enable-assembler --enable-local-infile --with-mysqld-user=mysql
--with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/
--with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin
--sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql
--infodir=/usr/share/info --includedir=/usr/include --mandir=/usr/share/man
--with-embedded-server --enable-thread-safe-client '--with-comment=Official MySQL RPM'
CC=gcc 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6
-fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti
-mpentium' CXX=gcc
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
