From: Thimble Smith
Date: February 26 2001 7:34pm
Subject: Re: Bug in make_join_statistics()?
List-Archive: http://lists.mysql.com/internals/537
Message-Id: <20010226143403.N991@threads.polyesthetic.msg>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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