bzr push into mysql-6.0-opt-subqueries branch (sergefp:2683 to 2684) WL#3985

[Sergey Petrunia]

 2684 Sergey Petrunia	2008-07-28
      WL#3985:
      - Fix cost caclulations error when considering SJ-Materialization.
      - Let FirstMatch re-optimization step not use garbage for costs when 
        the other optimization option wasn't using join buffering.
modified:
  mysql-test/r/subselect.result
  mysql-test/r/subselect_no_mat.result
  mysql-test/r/subselect_sj2.result
  sql/sql_select.cc

 2683 Sergey Petrunia	2008-07-27
      WL#3985:
      - Fix spurious "Variable may be used unitialized" warnings
      - Disable LEFT JOIN + semi-join as they have problems together
      - Fix a crash bug in WL#3985 code that could occur when multi-table sj-materialization was used
modified:
  mysql-test/r/subselect2.result
  mysql-test/r/subselect_sj.result
  mysql-test/r/subselect_sj2.result
  sql/sql_select.cc

=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2008-07-26 19:37:13 +0000
+++ b/mysql-test/r/subselect.result	2008-07-28 10:05:21 +0000
@@ -1356,9 +1356,9 @@ a
 3
 explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t2	index	a	a	5	NULL	4	100.00	Using index
-1	PRIMARY	t3	index	a	a	5	NULL	3	100.00	Using index
-1	PRIMARY	t1	ref	a	a	10	test.t2.a,test.t3.a	116	100.00	Using index; FirstMatch(t2)
+1	PRIMARY	t2	index	a	a	5	NULL	4	100.00	Using index; Start temporary
+1	PRIMARY	t3	index	a	a	5	NULL	3	100.00	Using index; Using join buffer
+1	PRIMARY	t1	ref	a	a	10	test.t2.a,test.t3.a	116	100.00	Using index; End temporary
 Warnings:
 Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1` join `test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` = `test`.`t3`.`a`))
 insert into t1 values (3,31);

=== modified file 'mysql-test/r/subselect_no_mat.result'
--- a/mysql-test/r/subselect_no_mat.result	2008-07-26 19:37:13 +0000
+++ b/mysql-test/r/subselect_no_mat.result	2008-07-28 10:05:21 +0000
@@ -1360,9 +1360,9 @@ a
 3
 explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t2	index	a	a	5	NULL	4	100.00	Using index
-1	PRIMARY	t3	index	a	a	5	NULL	3	100.00	Using index
-1	PRIMARY	t1	ref	a	a	10	test.t2.a,test.t3.a	116	100.00	Using index; FirstMatch(t2)
+1	PRIMARY	t2	index	a	a	5	NULL	4	100.00	Using index; Start temporary
+1	PRIMARY	t3	index	a	a	5	NULL	3	100.00	Using index; Using join buffer
+1	PRIMARY	t1	ref	a	a	10	test.t2.a,test.t3.a	116	100.00	Using index; End temporary
 Warnings:
 Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1` join `test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` = `test`.`t3`.`a`))
 insert into t1 values (3,31);

=== modified file 'mysql-test/r/subselect_sj2.result'
--- a/mysql-test/r/subselect_sj2.result	2008-07-27 19:17:41 +0000
+++ b/mysql-test/r/subselect_sj2.result	2008-07-28 10:05:21 +0000
@@ -98,7 +98,7 @@ set max_heap_table_size= @save_max_heap_
 explain select * from t1 where a in (select b from t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
-1	PRIMARY	t2	ref	b	b	5	test.t1.a	2	Using index; FirstMatch(t1)
+1	PRIMARY	t2	index	b	b	5	NULL	10	Using index; Materialize
 select * from t1;
 a	b
 1	1
@@ -342,7 +342,7 @@ WHERE t1.Code IN (
 SELECT t2.CountryCode FROM t2 WHERE Population > 5000000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	31	
-1	PRIMARY	t2	ref	CountryCode	CountryCode	3	test.t1.Code	18	Using where; FirstMatch(t1)
+1	PRIMARY	t2	ALL	CountryCode	NULL	NULL	NULL	545	Using where; Materialize
 SELECT Name FROM t1 
 WHERE t1.Code IN (
 SELECT t2.CountryCode FROM t2 WHERE Population > 5000000);
@@ -676,7 +676,7 @@ The following must use loose index scan 
 explain select count(a) from t2 where a in ( SELECT  a FROM t3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	index	a	a	5	NULL	1000	Using index
-1	PRIMARY	t3	ref	a	a	5	test.t2.a	30	Using index; FirstMatch(t2)
+1	PRIMARY	t3	index	a	a	5	NULL	30000	Using index; Materialize
 select count(a) from t2 where a in ( SELECT  a FROM t3);
 count(a)
 1000

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2008-07-27 19:17:41 +0000
+++ b/sql/sql_select.cc	2008-07-28 10:05:21 +0000
@@ -12203,6 +12203,9 @@ void optimize_wo_join_buffering(JOIN *jo
                        test(i < no_jbuf_before), rec_count,
                        &pos, &loose_scan_pos);
     }
+    else 
+      pos= join->positions[i];
+
     if ((i == first_tab && first_alt))
       pos= loose_scan_pos;
 
@@ -12446,13 +12449,19 @@ void advance_sj_state(JOIN *join, table_
     {
       /* This is SJ-Materialization with lookups */
       COST_VECT prefix_cost; 
-      int first_tab= idx - mat_info->n_tables;
-      if (idx == join->const_tables)
+      signed int first_tab= (int)idx - mat_info->n_tables;
+      double prefix_rec_count;
+      if (first_tab < (int)join->const_tables)
+      {
         prefix_cost.zero();
+        prefix_rec_count= 1.0;
+      }
       else
+      {
         prefix_cost= join->positions[first_tab].prefix_cost;
+        prefix_rec_count= join->positions[first_tab].prefix_record_count;
+      }
 
-      double prefix_rec_count= join->positions[first_tab].prefix_record_count;
       double mat_read_time= prefix_cost.total_cost();
       mat_read_time += mat_info->materialization_cost.total_cost() +
                        prefix_rec_count * mat_info->lookup_cost.total_cost();