bzr commit into mysql-6.0-codebase-bugfixing branch (epotemkin:3695) Bug#42742

[Evgeny Potemkin]

#At file:///work/bzrroot/mysql-6.0-codebase-bugfixing/ based on revid:vvaintroub@stripped

 3695 Evgeny Potemkin	2009-11-03 [merge]
      Auto-merged fix for the bug#42742.

    modified:
      mysql-test/r/subselect_sj2.result
      mysql-test/r/subselect_sj2_jcl6.result
      mysql-test/t/subselect_sj2.test
      sql/sql_select.cc
=== 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-10-22 10:45:44 +0000
@@ -689,3 +689,19 @@ explain select 1 from t2 where c2 = any 
 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-10-22 10:45:44 +0000
@@ -693,6 +693,22 @@ explain select 1 from t2 where c2 = any 
 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-10-22 10:45:44 +0000
@@ -872,3 +872,15 @@ create table t2 (c2 double) engine=innod
 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-10-28 12:48:31 +0000
+++ b/sql/sql_select.cc	2009-11-03 13:03:25 +0000
@@ -8062,7 +8062,11 @@ static void fix_semijoin_strategies_for_
     
     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;


Attachment: [text/bzr-bundle] bzr/[e-mail]