List:Internals« Previous MessageNext Message »
From:Paul Cadach Date:February 26 2001 6:34pm
Subject:Bug in make_join_statistics()?
View as plain text  
Hi,

Looks like little bug in query optimizer.

The query:    explain    select count(*) from company c, street cs, street s, c_division
d, street ds    where (cs.code = c.street and c.code = 5160 and s.name like '%STREET%'
and    s.code = if(d.street=0,c.street,d.street) and ds.code = d.street and c.code    =
d.code) limit 2;gets 100% CPU load in 'statistics' stage.
After some looks into sql_select.cc sources I've found dead loop is initiated in fragment
of make_join_statistics() pointed below.
Because the query have un-resolvable reference (s.code=if(d.street=0,c.street,d.street))
this function loops forever because it seesconstant reference which cannot made
referencer table as constant.
This is a modified part of make_join_statistics() (bold font used to highlight changes):
  /* loop until no more const tables are found */  int ref_changed;  do  {    ref_changed
= 0;    found_ref=0;    for (JOIN_TAB **pos=stat_vector+const_count; (s= *pos) ; pos++)  
 {      if (s->dependent)    // If dependent on some table      {        if
(s->dependent & ~(const_table_map)) // All dep. must be constants          
continue;        if (s->table->file->records <= 1L &&            
!(s->table->file->option_flag() & HA_NOT_EXACT_COUNT))        {     //
system table           s->type=JT_SYSTEM;          
const_table_map|=s->table->map;          
set_position(join,const_count++,s,(KEYUSE*) 0);           continue;        }      }     
/* check if table can be read by key or table only uses const refs */      if
((keyuse=s->keyuse))      {        TABLE *table=s->table;        s->type=
JT_REF;        while (keyuse->table == table)        {           start_keyuse=keyuse; 
         key=keyuse->key;           s->keys|= (key_map) 1 << key;  // QQ:
remove this ?
           refs=const_ref=eq_part=0;           do           {             if
(keyuse->val->type() != Item::NULL_ITEM)             {               if
(!((~const_table_map) & keyuse->used_tables))                    const_ref|=
(key_map) 1 << keyuse->keypart;               else                   
refs|=keyuse->used_tables;               eq_part|= (uint) 1 <<
keyuse->keypart;             }             keyuse++;           } while
(keyuse->table == table && keyuse->key == key);
           if (eq_part == PREV_BITS(uint,table->key_info[key].key_parts) &&    
          (table->key_info[key].flags & HA_NOSAME))           {             if
(const_ref == eq_part)             {     // Found everything for ref.              
s->type=JT_CONST;               const_table_map|=table->map;              
set_position(join,const_count++,s,start_keyuse);               ref_changed = 1;          
    break;             }             else               found_ref|= refs;  // Table is
const if all refs are const           }         }      }    }  } while (const_table_map
& found_ref && ref_changed);

WBR,Paul.


Thread
Bug in make_join_statistics()?Paul Cadach26 Feb
  • Re: Bug in make_join_statistics()?Thimble Smith26 Feb