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
