From: Jan Dvorak Date: March 7 2000 3:21pm Subject: Long enum list field vs. select ... group by List-Archive: http://lists.mysql.com/bugs/36 Message-Id: <38C51E6D.7C85F668@veda.cz> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit The (*#&$ mysqlbug sent the bug report to the general list! Should be updated to reflect this shiny new bugs list. >Description: A table with an enum field with a sufficiently long enumeration list behaves incorrectly in a group-by select like: select field from ZZ group by field; This select returns just a subset of the correct result. However, select count(distinct field) from ZZ; works correctly, and so does the group-by select if I cast to strings: select concat(field) as field from ZZ group by field; The behaviour occurs in MySQL 3.23; 3.22 worked fine. It occurrs with all table types (MyISAM, ISAM, HEAP), steady or temporary tables alike. It appears that it doesn't depend on whether there is an index on the column or not. The fields that are missing from the result set seem to be quite random. Nothing like "where field > 255". >How-To-Repeat: create table ZZ ( field enum ( /* list of 2500 string literals */ ) not null, index ( field ) ); /* put some data into the table */ /* I uploaded the dump of this table as ftp://www.mysql.com/pub/mysql/secret/long-enum-fields.sql.gz */ select field from ZZ group by field; /* returns 1164 rows, whereas */ select count(distinct field) from ZZ; /* returns the correct value: 1615. And */ select concat(field) as field from ZZ group by field; /* returns the 1615 rows. */ >Fix: Work-around: Cast the fields to strings in group-by queries. >Submitter-Id: jan.dvorak@stripped >Originator: jan.dvorak@stripped >Organization: MathAn Praha, Ltd; CZ. >MySQL support: none >Synopsis: Long enum list field vs. select ... group by >Severity: non-critical >Priority: low >Category: mysql >Class: sw-bug >Release: mysql-3.23.12c-alpha (Official MySQL RPM) >Server: /usr/bin/mysqladmin Ver 8.0 Distrib 3.23.11-alpha, for pc-linux-gnu on i686 TCX Datakonsult AB, by Monty Server version 3.23.12c-alpha-log Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 21 hours 2 min 8 sec Threads: 5 Questions: 1395 Slow queries: 0 Opens: 483 Flush tables: 143 Open tables: 31 >Environment: System: Linux medved.mathan.cz 2.2.13 #2 SMP Thu Dec 16 09:47:45 CET 1999 i686 unknown 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/egcs-2.91.66/specs gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release) Compilation info: CC='egcs' CFLAGS='-O6 -fomit-frame-pointer -mpentium' CXX='egcs' CXXFLAGS='-O6 -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Jun 9 1999 /lib/libc.so.6 -> libc-2.1.1.so -rwxr-xr-x 1 root root 4016683 Apr 17 1999 /lib/libc-2.1.1.so -rw-r--r-- 1 root root 19533408 Apr 17 1999 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Apr 17 1999 /usr/lib/libc.so Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --enable-assembler --with-mysqld-user=mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/ --exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql --infodir=/usr/info --includedir=/usr/include --mandir=/usr/man '--with-comment=Official MySQL RPM' Perl: This is perl, version 5.005_03 built for i386-linux