List:Commits« Previous MessageNext Message »
From:Evgeny Potemkin Date:December 15 2010 4:59pm
Subject:Re: bzr commit into mysql-trunk-bugfixing branch (jorgen.loland:3427)
Bug#58456
View as plain text  
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
>
>
>
>

Thread
bzr commit into mysql-trunk-bugfixing branch (jorgen.loland:3427) Bug#58456Jorgen Loland8 Dec
  • Re: bzr commit into mysql-trunk-bugfixing branch (jorgen.loland:3427)Bug#58456Olav Sandstaa8 Dec
  • Re: bzr commit into mysql-trunk-bugfixing branch (jorgen.loland:3427)Bug#58456Evgeny Potemkin15 Dec
    • Re: bzr commit into mysql-trunk-bugfixing branch (jorgen.loland:3427)Bug#58456Jorgen Loland16 Dec