List:Bugs« Previous MessageNext Message »
From:Michael Widenius Date:April 23 2000 1:05am
Subject:Long enum list field vs. select ... group by
View as plain text  
Hi!

Sorry for the delay; Because of a bug in my procmail filter, I didn't
notice the following mail until now :(

>>>>> "Jan" == Jan Dvorak <jan.dvorak@stripped> writes:

Jan> The (*#&$ mysqlbug sent the bug report to the general list!
Jan> Should be updated to reflect this shiny new bugs list.

We haven't updated mysqlbug to use this list as most users doesn't
send full, repeatable bug reports as yours.

>> Description:

Jan> A table with an enum field with a sufficiently long enumeration list
Jan> behaves incorrectly in a group-by select like:

Jan>    select field from ZZ group by field;

Jan> This select returns just a subset of the correct result.
Jan> However, 

Jan>    select count(distinct field) from ZZ;

Jan> works correctly,
Jan> and so does the group-by select if I cast to strings:

Jan>    select concat(field) as field from ZZ group by field;

Jan> The behaviour occurs in MySQL 3.23; 3.22 worked fine.
Jan> It occurrs with all table types (MyISAM, ISAM, HEAP),
Jan> steady or temporary tables alike.  It appears that
Jan> it doesn't depend on whether there is an index
Jan> on the column or not.

Jan> The fields that are missing from the result set
Jan> seem to be quite random.
Jan> Nothing like "where field > 255".

>> How-To-Repeat:

Jan> create table ZZ (
Jan>    field enum ( /* list of 2500 string literals */ ) not null,
Jan>    index ( field )
Jan> );

Jan> /* put some data into the table */
Jan> /* I uploaded the dump of this table as
Jan>     ftp://www.mysql.com/pub/mysql/secret/long-enum-fields.sql.gz
Jan> */

Jan> select field from ZZ group by field;

Jan> /* returns 1164 rows, whereas */

<cut>

The problem was that when using enum's in GROUP BY mysqld used it as
a char column. When converting all bytes to sort order characters some
different values was mapped to the same values and this caused the
wrong results.

Here is a patch for this:

*** /my/monty/master/mysql-3.23.13a-alpha/sql/sql_select.cc	Tue Mar 14 02:52:05 2000
--- ./sql_select.cc	Sun Apr 23 04:01:51 2000
***************
*** 3080,3087 ****
        key_part_info->field=  field;
        key_part_info->offset= field->offset();
        key_part_info->length= (uint16) field->pack_length();
-       key_part_info->key_type=(field->binary() ? FIELDFLAG_BINARY : 0);
        key_part_info->type=   (uint8) field->key_type();
        if (!using_unique_constraint)
        {
  	group->buff=(char*) group_buff;
--- 3156,3164 ----
        key_part_info->field=  field;
        key_part_info->offset= field->offset();
        key_part_info->length= (uint16) field->pack_length();
        key_part_info->type=   (uint8) field->key_type();
+       key_part_info->key_type = (ha_base_keytype) key_part_info->type ==
+ 	HA_KEYTYPE_TEXT ? 0 : FIELDFLAG_BINARY;
        if (!using_unique_constraint)
        {
  	group->buff=(char*) group_buff;

Regards,
Monty
Thread
Long enum list field vs. select ... group byJan Dvorak7 Mar
  • Long enum list field vs. select ... group byMichael Widenius23 Apr