List:Internals« Previous MessageNext Message »
From:Brian Aker Date:October 20 2009 2:37am
Subject:Re: Contribution: fix for BUG#42742
View as plain text  
Hi!

Just out of curiosity, which SCA?

Cheers,
	-Brian

On Oct 19, 2009, at 3:45 PM, sca@stripped wrote:

> Hello,
>
> We're pleased to offer the following contribution under the terms of  
> SCA:
>
> At file:///home/psergey/dev/mysql-next-fix-subq/
> ------------------------------------------------------------
> revno: 2817
> revision-id: psergey@stripped
> parent: psergey@stripped
> committer: Sergey Petrunya <psergey@stripped>
> branch nick: mysql-next-fix-subq
> timestamp: Mon 2009-07-06 18:33:29 +0400
> message:
>  BUG#42742: crash in setup_sj_materialization, Copy_field::set
>  - If a semi-join strategy covers certain [first_table; last_table]
>    range in join order, do reset the sj_strategy member for all tables
>    within the range, except the first one.
>    Failure to do so caused EXPLAIN/execution code to try applying two
>    strategies at once which would cause all kinds of undesired  
> effects.
> === modified file 'mysql-test/r/subselect_sj2.result'
> --- a/mysql-test/r/subselect_sj2.result	2009-03-21 15:31:38 +0000
> +++ b/mysql-test/r/subselect_sj2.result	2009-07-06 14:33:29 +0000
> @@ -689,3 +689,19 @@
> id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE  
> noticed after reading const tables
> drop table t1, t2;
> +#
> +# BUG#42742: crash in setup_sj_materialization, Copy_field::set
> +#
> +create table t3 ( c1 year) engine=innodb;
> +insert into t3 values (2135),(2142);
> +create table t2 (c1 tinytext,c2 text,c6 timestamp) engine=innodb;
> +# The following must not crash, EXPLAIN should show one SJ  
> strategy, not a mix:
> +explain select 1 from t2 where
> +c2 in (select 1 from t3, t2) and
> +c1 in (select convert(c6,char(1)) from t2);
> +id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> +1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
> +1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	
> +1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer
> +1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	FirstMatch(t2); Using join  
> buffer
> +drop table t2, t3;
>
> === modified file 'mysql-test/r/subselect_sj2_jcl6.result'
> --- a/mysql-test/r/subselect_sj2_jcl6.result	2009-06-19 09:12:06 +0000
> +++ b/mysql-test/r/subselect_sj2_jcl6.result	2009-07-06 14:33:29 +0000
> @@ -693,6 +693,22 @@
> id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE  
> noticed after reading const tables
> drop table t1, t2;
> +#
> +# BUG#42742: crash in setup_sj_materialization, Copy_field::set
> +#
> +create table t3 ( c1 year) engine=innodb;
> +insert into t3 values (2135),(2142);
> +create table t2 (c1 tinytext,c2 text,c6 timestamp) engine=innodb;
> +# The following must not crash, EXPLAIN should show one SJ  
> strategy, not a mix:
> +explain select 1 from t2 where
> +c2 in (select 1 from t3, t2) and
> +c1 in (select convert(c6,char(1)) from t2);
> +id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> +1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
> +1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using join buffer
> +1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer
> +1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	FirstMatch(t2); Using join  
> buffer
> +drop table t2, t3;
> set join_cache_level=default;
> show variables like 'join_cache_level';
> Variable_name	Value
>
> === modified file 'mysql-test/t/subselect_sj2.test'
> --- a/mysql-test/t/subselect_sj2.test	2009-03-21 15:31:38 +0000
> +++ b/mysql-test/t/subselect_sj2.test	2009-07-06 14:33:29 +0000
> @@ -872,3 +872,15 @@
> explain select 1 from t2 where c2 = any (select log10(null) from t1  
> where c6 <null)  ;
> drop table t1, t2;
>
> +--echo #
> +--echo # BUG#42742: crash in setup_sj_materialization,  
> Copy_field::set
> +--echo #
> +create table t3 ( c1 year) engine=innodb;
> +insert into t3 values (2135),(2142);
> +create table t2 (c1 tinytext,c2 text,c6 timestamp) engine=innodb;
> +-- echo # The following must not crash, EXPLAIN should show one SJ  
> strategy, not a mix:
> +explain select 1 from t2 where
> +  c2 in (select 1 from t3, t2) and
> +  c1 in (select convert(c6,char(1)) from t2);
> +drop table t2, t3;
> +
>
> === modified file 'sql/sql_select.cc'
> --- a/sql/sql_select.cc	2009-07-06 07:57:39 +0000
> +++ b/sql/sql_select.cc	2009-07-06 14:33:29 +0000
> @@ -7916,7 +7916,11 @@
>
>     uint i_end= first + join->best_positions[first].n_sj_tables;
>     for (uint i= first; i < i_end; i++)
> +    {
> +      if (i != first)
> +        join->best_positions[i].sj_strategy= SJ_OPT_NONE;
>       handled_tabs |= join->best_positions[i].table->table->map;
> +    }
>
>     if (tablenr != first)
>       pos->sj_strategy= SJ_OPT_NONE;
>
>
>
> -- 
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe:    http://lists.mysql.com/internals?unsub=1
>

Thread
Contribution: fix for BUG#42742sca20 Oct
  • Re: Contribution: fix for BUG#42742Brian Aker20 Oct
  • Re: Contribution: fix for BUG#42742Masood Mortazavi20 Oct
    • Re: Contribution: fix for BUG#42742Michael Widenius21 Oct
      • Re: Contribution: fix for BUG#42742Masood Mortazavi21 Oct
        • Re: Contribution: fix for BUG#42742Michael Widenius21 Oct
          • Re: Contribution: fix for BUG#42742Masood Mortazavi21 Oct
      • Re: Contribution: fix for BUG#42742Giuseppe Maxia21 Oct
Re: Contribution: fix for BUG#42742sca21 Oct
Re: Contribution: fix for BUG#42742sca21 Oct