On Tue, Feb 27, 2001 at 12:34:27AM +0600, Paul Cadach wrote:
> 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):
Paul, thank you for the report.
Here's a diff of the changes, for those who had a hard time reading
that code:
===== sql/sql_select.cc 1.83 vs edited =====
--- 1.83/sql/sql_select.cc Sat Feb 17 07:18:57 2001
+++ edited/sql/sql_select.cc Mon Feb 26 14:21:59 2001
@@ -945,8 +945,10 @@
DBUG_RETURN(1);
/* 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++)
{
@@ -996,6 +998,7 @@
s->type=JT_CONST;
const_table_map|=table->map;
set_position(join,const_count++,s,start_keyuse);
+ ref_changed = 1;
break;
}
else
@@ -1004,7 +1007,7 @@
}
}
}
- } while (const_table_map & found_ref);
+ } while (const_table_map & found_ref && ref_changed);
/* Calc how many (possible) matched records in each table */
I'm not sure if there's a better way to fix this problem or
not - but at least this does fix it. We'll make sure it's
fixed in the next release.
Tim
--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Tim Smith <tim@stripped>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Development Team
/_/ /_/\_, /___/\___\_\___/ Boone, NC USA
<___/ www.mysql.com
