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:
<machine, os, target, libraries (multiple lines)>
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
