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

[Sergey Petrunia]

 2734 Sergey Petrunia	2008-11-24
      WL#3985: Subqueries: smart choice between semi-join and materialization
      - Don't do flattening if either parent or child select have STRAIGHT_JOIN modifier,
as we can't 
        provide any handling for STRAIGHT_JOIN that would different from non-flattening
and practically useful
modified:
  mysql-test/r/subselect3.result
  mysql-test/t/subselect3.test
  sql/sql_select.cc

 2733 Sergey Petrunia	2008-11-24
      WL#3985: Subqueries: Smart choice between semi-join and materialization
      - More purecov annotations
      - Fixed a crash in fix_semijoin_strategies_for_picked_join_order() which occurred
for certain join orders
        when SJ-Materialization was applied to a range of tables in the middle of the join
order 
      - More test coverage
modified:
  mysql-test/r/subselect3.result
  mysql-test/t/subselect3.test
  sql/sql_select.cc

=== modified file 'mysql-test/r/subselect3.result'
--- a/mysql-test/r/subselect3.result	2008-11-23 22:59:21 +0000
+++ b/mysql-test/r/subselect3.result	2008-11-24 19:11:10 +0000
@@ -1034,4 +1034,26 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Materialize; Scan
 1	PRIMARY	t3	ref	a	a	5	test.t2.a	10	
-drop table t1,t2,t3;
+explain select straight_join * from t1 A, t1 B where A.a in (select a from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	PRIMARY	B	ALL	NULL	NULL	NULL	NULL	10	Using join buffer
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	10	
+explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where
+2	SUBQUERY	A	ALL	NULL	NULL	NULL	NULL	10	
+2	SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	Using join buffer
+explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where
+2	SUBQUERY	A	ALL	NULL	NULL	NULL	NULL	10	
+2	SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	Using join buffer
+explain select straight_join * from t2 X, t2 Y 
+where X.a in (select straight_join A.a from t1 A, t1 B);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	X	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	PRIMARY	Y	ALL	NULL	NULL	NULL	NULL	10	Using join buffer
+2	SUBQUERY	A	ALL	NULL	NULL	NULL	NULL	10	
+2	SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	Using join buffer
+drop table t1, t2, t3;

=== modified file 'mysql-test/t/subselect3.test'
--- a/mysql-test/t/subselect3.test	2008-11-23 22:59:21 +0000
+++ b/mysql-test/t/subselect3.test	2008-11-24 19:11:10 +0000
@@ -843,5 +843,15 @@ create table t2 as select * from t1;
 create table t3 (a int, filler char(100), key(a));
 insert into t3 select A.a + 10*B.a, 'filler' from t1 A, t1 B, t1 C;
 explain select * from t1, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a
>30) and t1.a =3;
-drop table t1,t2,t3;
+
+#
+# Verify that straight_join modifier in parent or child prevents flattening
+#
+explain select straight_join * from t1 A, t1 B where A.a in (select a from t2);
+explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
+explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
+explain select straight_join * from t2 X, t2 Y 
+where X.a in (select straight_join A.a from t1 A, t1 B);
+
+drop table t1, t2, t3;
 

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2008-11-23 22:59:21 +0000
+++ b/sql/sql_select.cc	2008-11-24 19:11:10 +0000
@@ -599,6 +599,7 @@ JOIN::prepare(Item ***rref_pointer_array
         7. We're not in a confluent table-less subquery, like "SELECT 1".
         8. No execution method was already chosen (by a prepared statement)
         9. Parent select is not a confluent table-less select
+        10. Neither parent nor child select have STRAIGHT_JOIN option.
     */
     if (!optimizer_flag(thd, OPTIMIZER_SWITCH_NO_SEMIJOIN) &&
         in_subs &&                                                    // 1
@@ -609,7 +610,9 @@ JOIN::prepare(Item ***rref_pointer_array
         select_lex->outer_select()->join &&                           // 6
         select_lex->master_unit()->first_select()->leaf_tables &&     //
7
         in_subs->exec_method == Item_in_subselect::NOT_TRANSFORMED && // 8
-        select_lex->outer_select()->leaf_tables)                      // 9
+        select_lex->outer_select()->leaf_tables &&                    // 9
+        !((select_options | select_lex->outer_select()->join->select_options)
+          & SELECT_STRAIGHT_JOIN))                                    // 10
     {
       DBUG_PRINT("info", ("Subquery is semi-join conversion candidate"));
       in_subs->types_allow_materialization= 
@@ -6627,6 +6630,7 @@ choose_plan(JOIN *join, table_map join_t
             jtab_sort_func, (void*)join->emb_sjm_nest);
   join->cur_sj_inner_tables= 0;
 
+#if 0
   if (!join->emb_sjm_nest && straight_join)
   {
     /* Put all sj-inner tables right after their last outer table table.  */
@@ -6663,6 +6667,7 @@ choose_plan(JOIN *join, table_map join_t
       inner += n_tables;
     }
   }
+#endif
 
   if (straight_join)
   {
@@ -6754,19 +6759,13 @@ join_tab_cmp_straight(const void *dummy,
   JOIN_TAB *jt1= *(JOIN_TAB**) ptr1;
   JOIN_TAB *jt2= *(JOIN_TAB**) ptr2;
  
-  
-  /* Put SJ-inner tables at the end */
-  if (jt1->emb_sj_nest && !jt2->emb_sj_nest)
-    return -1;
-  if (!jt1->emb_sj_nest && jt2->emb_sj_nest)
-    return 1;
-
-  /* Group SJ-inner tables by their embedding nest */
-  if (jt1->emb_sj_nest && jt2->emb_sj_nest)
-  {
-    ptrdiff_t diff=  jt1->emb_sj_nest - jt2->emb_sj_nest;
-    return diff > 0 ? 1 : (diff < 0 ? -1 : 0);
-  }
+  /*
+    We don't do subquery flattening if the parent or child select has
+    STRAIGHT_JOIN modifier. It is complicated to implement and the semantics
+    is hardly useful.
+  */
+  DBUG_ASSERT(!jt1->emb_sj_nest);
+  DBUG_ASSERT(!jt2->emb_sj_nest);
 
   if (jt1->dependent & jt2->table->map)
     return 1;