Hi Jorgen,
The fix is good, but should be slightly adjusted, IMO. Please see the
explanation below.
Regards, Evgen.
On 12/08/10 12:58, Jorgen Loland wrote:
> #At file:///export/home/jl208045/mysql/mysql-trunk-bugfixing/ based on
> revid:marc.alff@stripped
>
> 3427 Jorgen Loland 2010-12-08
> 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 2010-04-20 07:22:51 +0000
> +++ b/mysql-test/r/join_outer_innodb.result 2010-12-08 09:58:55 +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-08 09:58:55 +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-06 13:12:51 +0000
> +++ b/sql/sql_select.cc 2010-12-08 09:58:55 +0000
> @@ -9653,8 +9653,8 @@ static bool make_join_select(JOIN *join,
> /* 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))
> + && tab->type != JT_FT&& tab->type != JT_CONST&&
> + (tab->type != JT_REF || (uint)tab->ref.key ==
> tab->quick->index))
Originally the chosen AM was REF and REF is actually allowed to use tab->quick
under some circumstances.
Same should be applied to CONST.
> {
It would be good to add an assert here that the quick AM is a valid one:
DBUG_ASSERT(tab->quick->index != MAX_KEY);
> sel->quick=tab->quick; // Use value from get_quick_...
> sel->quick_keys.clear_all();
> @@ -20050,7 +20050,7 @@ use_filesort:
> 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
> @@ -20060,8 +20060,8 @@ use_filesort:
>
>
> 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
>
>
>
>
