MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Guilhem Bichot Date:May 10 2010 12:41pm
Subject:bzr commit into mysql-next-mr-bugfixing branch (guilhem:3161) Bug#52540
View as plain text  
#At file:///home/mysql_src/bzrrepos/mysql-next-mr-opt-backporting2/ based on revid:guilhem@stripped

 3161 Guilhem Bichot	2010-05-10
      Fix for BUG#52540 "Crash in JOIN_CACHE::set_match_flag_if_none () at sql_join_cache.cc:1883"
      (Backporting of guilhem@stripped )
     @ mysql-test/t/join_cache.test
        minimal test case for bug
     @ sql/sql_select.cc
        As said in a comment in this file:
        "For a nested outer join/semi-join, currently, we either use join
        buffers for all inner tables or for none of them.".
        This is enforced by check_join_cache_usage():
          for (JOIN_TAB *first_inner= tab->first_inner; first_inner;
               first_inner= first_inner->first_upper)
          {
            if (first_inner != tab && !first_inner->use_join_cache)
              goto no_join_cache;
          }
        i.e. if the first table in this join nest doesn't use join cache, or
        the first table of the upper containing join nest doesn't, go to label
        "no_join_cache" and thus:
        1) don't use join cache for the current table 2) disable join cache for
        all previous tables of this nest and upper containing nests (via
        revise_cache_usage()).
        In the bug's scenario, before the fix, EXPLAIN showed this plan:
        t2,  t1(with join buffering),  t3,  t4(with join buffering).
        * join buffering was decided for t1
        * it was rejected for t3, because t3 is accessed with EQ_REF (join
        buffering with EQ_REF requires BKA, which we don't have at levels <=4):
        check_join_cache_usage() did "return 0", forgetting to disable join
        buffering for previous tables of the join nest i.e. t1
        * join buffering was decided for t4, and allowed as it was observed
        that the first table of the upper nest (t1) had join buffering (so it
        was believed that all tables in between had join buffering).
        * at execution time, JOIN_CACHE:set_match_flag_if_none() would walk
        back the list of join buffers from t4 to t1:
          while (cache->join_tab != first_inner)
          {
            cache= cache->prev_cache;
            DBUG_ASSERT(cache);
        But t4 had prev_cache==0 (as previous table t1 wasn't doing join
        buffering), hence the assertion failure. This loop above assumed again
        that all previous tables should be doing join buffering.
        The fix is to make sure that when join buffering is rejected because of
        EQ_REF, we disable join buffering for previous tables, so that
        assumptions are true.

    modified:
      mysql-test/r/join_cache.result
      mysql-test/t/join_cache.test
      sql/sql_select.cc
=== modified file 'mysql-test/r/join_cache.result'
--- a/mysql-test/r/join_cache.result	2010-05-08 13:48:14 +0000
+++ b/mysql-test/r/join_cache.result	2010-05-10 12:40:54 +0000
@@ -4068,3 +4068,35 @@ SELECT 1 FROM C,D,E WHERE D.a = E.a AND 
 1
 DROP TABLE C,D,E;
 SET optimizer_join_cache_level=default;
+#
+# BUG#52540 Crash in JOIN_CACHE::set_match_flag_if_none () at sql_join_cache.cc:1883
+#
+SET optimizer_join_cache_level=4;
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (2);
+CREATE TABLE t2 (a varchar(10));
+INSERT INTO t2 VALUES ('f'),('x');
+CREATE TABLE t3 (pk int(11) PRIMARY KEY);
+INSERT INTO t3 VALUES (2);
+CREATE TABLE t4 (a varchar(10));
+EXPLAIN SELECT 1
+FROM t2 LEFT JOIN
+((t1 JOIN t3 ON t1.a = t3.pk)
+LEFT JOIN t4 ON 1 )
+ON 1 ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using index
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	0	Using where
+SELECT 1
+FROM t2 LEFT JOIN
+((t1 JOIN t3 ON t1.a = t3.pk)
+LEFT JOIN t4 ON 1 )
+ON 1 ;
+1
+1
+1
+SET optimizer_join_cache_level=default;
+DROP TABLE t1,t2,t3,t4;
+

=== modified file 'mysql-test/t/join_cache.test'
--- a/mysql-test/t/join_cache.test	2010-05-08 13:48:14 +0000
+++ b/mysql-test/t/join_cache.test	2010-05-10 12:40:54 +0000
@@ -1742,3 +1742,33 @@ INSERT INTO E VALUES
 SELECT 1 FROM C,D,E WHERE D.a = E.a AND D.b = E.b;
 DROP TABLE C,D,E;
 SET optimizer_join_cache_level=default;
+
+--echo #
+--echo # BUG#52540 Crash in JOIN_CACHE::set_match_flag_if_none () at sql_join_cache.cc:1883
+--echo #
+
+SET optimizer_join_cache_level=4;
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (2);
+CREATE TABLE t2 (a varchar(10));
+INSERT INTO t2 VALUES ('f'),('x');
+CREATE TABLE t3 (pk int(11) PRIMARY KEY);
+INSERT INTO t3 VALUES (2);
+CREATE TABLE t4 (a varchar(10));
+
+EXPLAIN SELECT 1
+FROM t2 LEFT JOIN
+       ((t1 JOIN t3 ON t1.a = t3.pk)
+        LEFT JOIN t4 ON 1 )
+     ON 1 ;
+
+SELECT 1
+FROM t2 LEFT JOIN
+       ((t1 JOIN t3 ON t1.a = t3.pk)
+        LEFT JOIN t4 ON 1 )
+     ON 1 ;
+
+SET optimizer_join_cache_level=default;
+DROP TABLE t1,t2,t3,t4;
+--echo

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-05-10 09:37:24 +0000
+++ b/sql/sql_select.cc	2010-05-10 12:40:54 +0000
@@ -9944,7 +9944,7 @@ uint check_join_cache_usage(JOIN_TAB *ta
   case JT_REF:
   case JT_EQ_REF:
     if (cache_level <= 4)
-      return 0;
+      goto no_join_cache;
     flags= HA_MRR_NO_NULL_ENDPOINTS;
     if (tab->table->covering_keys.is_set(tab->ref.key))
       flags|= HA_MRR_INDEX_ONLY;


Attachment: [text/bzr-bundle] bzr/guilhem@mysql.com-20100510124054-mkjy03bz0oyinc4i.bundle
Thread
bzr commit into mysql-next-mr-bugfixing branch (guilhem:3161) Bug#52540Guilhem Bichot14 May