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
3202 Jon Olav Hauglid 2010-12-16
Bug #58730 Assertion failed: table->key_read == 0 in close_thread_table,
temptable views
The TABLE::key_read field indicates if the optimizer has found that row
retrieval only should access the index tree. The triggered assert
inside close_thread_table() checks that this field has been reset when
the table is about to be closed.
During normal execution, these fields are reset right before tables are
closed at the end of mysql_execute_command(). But in the case of errors,
tables are closed earlier. The patch for Bug#52044 refactored the open
tables code so that close_thread_tables() is called immediately if
opening of tables fails. At this point in the execution, it could
happend that all TABLE::key_read fields had not been properly reset,
therefore triggering the assert.
The problematic statement in this case was EXPLAIN where the query
accessed two derived tables and where the first derived table was
processed successfully while the second derived table was not.
Since it was an EXPLAIN, TABLE::key_read fields were not reset after
successful derived table processing since the state needs to be
accessible afterwards. When processing of the second derived table
failed, it's corresponding SELECT_LEX_UNIT was cleaned, which caused
it's TABLE::key_read fields to be reset. Since processing failed,
the error path of open_and_lock_tables() was entered and
close_thread_tables() was called. The assert was then triggered due
to the TABLE::key_read fields set during processing of the first
derived table.
This patch fixes the problem by adding a new derived table processor,
mysql_derived_cleanup() that is called after mysql_derived_filling().
It causes cleanup of all SELECT_LEX_UNITs to be called, resetting
all relevant TABLE::key_read fields.
Test case added to derived.test.
modified:
mysql-test/r/derived.result
mysql-test/t/derived.test
sql/sql_base.cc
sql/sql_derived.cc
sql/sql_derived.h
sql/sql_update.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
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-5.5-bugteam branch (jorgen.loland:3202 to 3203) Bug#58456 | Jorgen Loland | 16 Dec |
