Hi, I just want to confirm MySQL 4.0.8 seems to be broken too. However MySQL 4.1 is working fine with this testcase.
Regards, Jocelyn ----- Original Message ----- From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, January 09, 2003 6:32 AM Subject: non-unique indicies in HEAP tables handled incorrectly. > >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' '-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 --wit hout-openssl --enable-assembler --enable-local-infile --with-mysqld-user=mys ql --with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/ --with-extra -charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/et c --datadir=/usr/share --localstatedir=/var/lib/mysql --infodir=/usr/share/i nfo --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-ex ceptions -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 > > --------------------------------------------------------------------- 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
