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.
