List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:December 16 2010 11:25am
Subject:bzr commit into mysql-5.5-bugteam branch (jorgen.loland:3203) Bug#58456
View as plain text  
#At file:///export/home/jl208045/mysql/mysql-5.5-bugteam/ based on revid:jon.hauglid@stripped

 3203 Jorgen Loland	2010-12-16
      BUG#58456 - Assertion 0 in QUICK_INDEX_MERGE_SELECT::need_sorted_output 
                  in opt_range.h
      
      In this bug, there are two alternative access plans: 
       * Index merge range access
       * Const ref access
      
      best_access_path() decided that the ref access was preferrable, 
      but make_join_select() still decided to point 
      SQL_SELECT::quick to the index merge because the table had 
      type==JT_CONST which was not handled. 
      
      At the same time the table's ref.key still referred to the 
      index the ref access would use indicating that ref access 
      should be used. In this state, different parts of the 
      optimizer code have different perceptions of which access path
      is in use (ref or range).
      
      test_if_skip_sort_order() was called to check if the ref access
      needed ordering, but test_if_skip_sort_order() got confused and
      requested the index merge to return records in sorted order. 
      Index merge cannot do this, and fired an ASSERT.
      
      The fix is to take join_tab->type==JT_CONST into concideration
      when make_join_select() decides whether or not to use the 
      range access method.
     @ mysql-test/r/join_outer_innodb.result
        Add test for BUG#58456
     @ mysql-test/t/join_outer_innodb.test
        Add test for BUG#58456

    modified:
      mysql-test/r/join_outer_innodb.result
      mysql-test/t/join_outer_innodb.test
      sql/sql_select.cc
=== modified file 'mysql-test/r/join_outer_innodb.result'
--- a/mysql-test/r/join_outer_innodb.result	2008-05-07 05:58:21 +0000
+++ b/mysql-test/r/join_outer_innodb.result	2010-12-16 11:25:02 +0000
@@ -17,3 +17,38 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t2	index	NULL	fkey	5	NULL	5	Using index
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.fkey	1	Using where
 DROP TABLE t1,t2;
+#
+# BUG#58456: Assertion 0 in QUICK_INDEX_MERGE_SELECT::need_sorted_output
+#            in opt_range.h
+#
+CREATE TABLE t1 (
+col_int INT,
+col_int_key INT,
+pk INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (NULL,1,1), (6,2,2), (5,3,3), (NULL,4,4);
+INSERT INTO t1 VALUES (1,NULL,6), (8,5,7), (NULL,8,8), (8,NULL,5);
+CREATE TABLE t2 (
+pk INT PRIMARY KEY
+) ENGINE=InnoDB;
+
+EXPLAIN SELECT t1.pk
+FROM t2 LEFT JOIN t1 ON t2.pk = t1.col_int
+WHERE t1.col_int_key BETWEEN 5 AND 6 
+AND t1.pk IS NULL OR t1.pk IN (5)
+ORDER BY pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY,col_int_key	PRIMARY	4	const	2	Using where
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.col_int	1	Using index
+
+SELECT t1.pk
+FROM t2 LEFT JOIN t1 ON t2.pk = t1.col_int
+WHERE t1.col_int_key BETWEEN 5 AND 6 
+AND t1.pk IS NULL OR t1.pk IN (5)
+ORDER BY pk;
+pk
+
+DROP TABLE t1,t2;
+# End BUG#58456

=== modified file 'mysql-test/t/join_outer_innodb.test'
--- a/mysql-test/t/join_outer_innodb.test	2006-05-22 11:57:32 +0000
+++ b/mysql-test/t/join_outer_innodb.test	2010-12-16 11:25:02 +0000
@@ -24,3 +24,40 @@ SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON 
   WHERE t1.name LIKE 'A%' OR FALSE;
 
 DROP TABLE t1,t2;
+
+--echo #
+--echo # BUG#58456: Assertion 0 in QUICK_INDEX_MERGE_SELECT::need_sorted_output
+--echo #            in opt_range.h
+--echo #
+
+CREATE TABLE t1 (
+  col_int INT,
+  col_int_key INT,
+  pk INT NOT NULL,
+  PRIMARY KEY (pk),
+  KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES (NULL,1,1), (6,2,2), (5,3,3), (NULL,4,4);
+INSERT INTO t1 VALUES (1,NULL,6), (8,5,7), (NULL,8,8), (8,NULL,5);
+
+CREATE TABLE t2 (
+  pk INT PRIMARY KEY
+) ENGINE=InnoDB;
+
+let $query=
+SELECT t1.pk
+FROM t2 LEFT JOIN t1 ON t2.pk = t1.col_int
+WHERE t1.col_int_key BETWEEN 5 AND 6 
+      AND t1.pk IS NULL OR t1.pk IN (5)
+ORDER BY pk;
+
+--echo
+--eval EXPLAIN $query
+--echo
+--eval $query
+--echo
+
+DROP TABLE t1,t2;
+
+--echo # End BUG#58456

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-12-14 10:46:00 +0000
+++ b/sql/sql_select.cc	2010-12-16 11:25:02 +0000
@@ -6499,10 +6499,12 @@ make_join_select(JOIN *join,SQL_SELECT *
 	{
 	  /* Use quick key read if it's a constant and it's not used
 	     with key reading */
-	  if (tab->needed_reg.is_clear_all() && tab->type != JT_EQ_REF
-	      && tab->type != JT_FT && (tab->type != JT_REF ||
-               (uint) tab->ref.key == tab->quick->index))
-	  {
+          if (tab->needed_reg.is_clear_all() && tab->type != JT_EQ_REF &&
+              tab->type != JT_FT &&
+              ((tab->type != JT_CONST && tab->type != JT_REF) ||
+               (uint)tab->ref.key == tab->quick->index))
+          {
+            DBUG_ASSERT(tab->quick->index != MAX_KEY);
 	    sel->quick=tab->quick;		// Use value from get_quick_...
 	    sel->quick_keys.clear_all();
 	    sel->needed_reg.clear_all();
@@ -13857,7 +13859,7 @@ check_reverse_order:                  
   SYNOPSIS
    create_sort_index()
      thd		Thread handler
-     tab		Table to sort (in join structure)
+     join		Join with table to sort
      order		How table should be sorted
      filesort_limit	Max number of rows that needs to be sorted
      select_limit	Max number of rows in final output
@@ -13867,8 +13869,8 @@ check_reverse_order:                  
 
 
   IMPLEMENTATION
-   - If there is an index that can be used, 'tab' is modified to use
-     this index.
+   - If there is an index that can be used, the first non-const join_tab in
+     'join' is modified to use this index.
    - If no index, create with filesort() an index file that can be used to
      retrieve rows in order (should be done with 'read_record').
      The sorted data is stored in tab->table and will be freed when calling


Attachment: [text/bzr-bundle] bzr/jorgen.loland@oracle.com-20101216112502-w7bind6txs0ii8pv.bundle
Thread
bzr commit into mysql-5.5-bugteam branch (jorgen.loland:3203) Bug#58456Jorgen Loland16 Dec