List:Bugs« Previous MessageNext Message »
From:Jan Dvorak Date:March 7 2000 3:21pm
Subject:Long enum list field vs. select ... group by
View as plain text  
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
Thread
Long enum list field vs. select ... group byJan Dvorak7 Mar
  • Long enum list field vs. select ... group byMichael Widenius23 Apr