Hi!

Pete> Hi, Pete> I uploaded the file artselectbug.tar.gz to Pete> ftp://support.mysql.com/pub/mysql/secret. I believe MySQL has a bug Pete> that is produced by the data and query there. Please let me know if I Pete> can be of further help.

Pete> The tarfile contains a README that explains the problem. I'll include Pete> the contents of that file at the end of this email.

Thanks for the bug report!

This enabled me to find a very unlikely, but possible bug in the executioner when it used caching of rows for later usage.

Here is a patch for this. (It will be in 3.23.46 and 4.0.1)

===== sql/sql_select.cc 1.111 vs edited ===== *** /tmp/sql_select.cc-1.111-4566 Wed Nov 21 19:05:11 2001 --- edited/sql/sql_select.cc Tue Nov 27 01:17:43 2001 *************** *** 1881,1932 **** ** Find how much space the prevous read not const tables takes in cache */

static uint cache_record_length(JOIN *join,uint idx) { ! uint length; JOIN_TAB **pos,**end; THD *thd=join->thd;

- length=0; for (pos=join->best_ref+join->const_tables,end=join->best_ref+idx ; pos != end ; pos++) { JOIN_TAB *join_tab= *pos; ! if (!join_tab->used_fieldlength) ! { /* Not calced yet */ ! uint null_fields,blobs,fields,rec_length; ! null_fields=blobs=fields=rec_length=0; ! ! Field **f_ptr,*field; ! for (f_ptr=join_tab->table->field ; (field= *f_ptr) ; f_ptr++) ! { ! if (field->query_id == thd->query_id) ! { ! uint flags=field->flags; ! fields++; ! rec_length+=field->pack_length(); ! if (flags & BLOB_FLAG) ! blobs++; ! if (!(flags & NOT_NULL_FLAG)) ! null_fields++; ! } ! } ! if (null_fields) ! rec_length+=(join_tab->table->null_fields+7)/8; ! if (join_tab->table->maybe_null) ! rec_length+=sizeof(my_bool); ! if (blobs) ! { ! uint blob_length=(uint) (join_tab->table->file->mean_rec_length- ! (join_tab->table->reclength- rec_length)); ! rec_length+=(uint) max(4,blob_length); ! } ! join_tab->used_fields=fields; ! join_tab->used_fieldlength=rec_length; ! join_tab->used_blobs=blobs; ! } length+=join_tab->used_fieldlength; } return length; --- 1881,1935 ---- ** Find how much space the prevous read not const tables takes in cache */

+ static void calc_used_field_length(THD *thd, JOIN_TAB *join_tab) + { + uint null_fields,blobs,fields,rec_length; + null_fields=blobs=fields=rec_length=0; + + Field **f_ptr,*field; + for (f_ptr=join_tab->table->field ; (field= *f_ptr) ; f_ptr++) + { + if (field->query_id == thd->query_id) + { + uint flags=field->flags; + fields++; + rec_length+=field->pack_length(); + if (flags & BLOB_FLAG) + blobs++; + if (!(flags & NOT_NULL_FLAG)) + null_fields++; + } + } + if (null_fields) + rec_length+=(join_tab->table->null_fields+7)/8; + if (join_tab->table->maybe_null) + rec_length+=sizeof(my_bool); + if (blobs) + { + uint blob_length=(uint) (join_tab->table->file->mean_rec_length- + (join_tab->table->reclength- rec_length)); + rec_length+=(uint) max(4,blob_length); + } + join_tab->used_fields=fields; + join_tab->used_fieldlength=rec_length; + join_tab->used_blobs=blobs; + } + + static uint cache_record_length(JOIN *join,uint idx) { ! uint length=0; JOIN_TAB **pos,**end; THD *thd=join->thd;

for (pos=join->best_ref+join->const_tables,end=join->best_ref+idx ; pos != end ; pos++) { JOIN_TAB *join_tab= *pos; ! if (!join_tab->used_fieldlength) /* Not calced yet */ ! calc_used_field_length(thd, join_tab); length+=join_tab->used_fieldlength; } return length; *************** *** 2248,2253 **** --- 2251,2257 ---- used_tables|=current_map;

if (tab->type == JT_REF && tab->quick && + tab->ref.key == tab->quick->index && tab->ref.key_length < tab->quick->max_used_key_length) { /* Range uses longer key; Use this instead of ref on key */ *************** *** 5631,5645 **** uint length,blobs,size; CACHE_FIELD *copy,**blob_ptr; JOIN_CACHE *cache; DBUG_ENTER("join_init_cache");

cache= &tables[table_count].cache; cache->fields=blobs=0;

! for (i=0 ; i < table_count ; i++) { ! cache->fields+=tables[i].used_fields; ! blobs+=tables[i].used_blobs; } if (!(cache->field=(CACHE_FIELD*) sql_alloc(sizeof(CACHE_FIELD)*(cache->fields+table_count*2)+(blobs+1)* --- 5635,5653 ---- uint length,blobs,size; CACHE_FIELD *copy,**blob_ptr; JOIN_CACHE *cache; + JOIN_TAB *join_tab; DBUG_ENTER("join_init_cache");

cache= &tables[table_count].cache; cache->fields=blobs=0;

! join_tab=tables; ! for (i=0 ; i < table_count ; i++,join_tab++) { ! if (!join_tab->used_fieldlength) /* Not calced yet */ ! calc_used_field_length(thd, join_tab); ! cache->fields+=join_tab->used_fields; ! blobs+=join_tab->used_blobs; } if (!(cache->field=(CACHE_FIELD*) sql_alloc(sizeof(CACHE_FIELD)*(cache->fields+table_count*2)+(blobs+1)*

Regards, Monty