From: Date: November 24 2008 8:11pm Subject: bzr commit into mysql-6.0-opt-subqueries branch (sergefp:2734) WL#3985 List-Archive: http://lists.mysql.com/commits/59712 Message-Id: <20081124191124.94E0115A1EB@pslp2.localdomain> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7BIT #At file:///home/spetrunia/dev/mysql-6.0-subq-r17/ 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 per-file messages: mysql-test/r/subselect3.result 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 sql/sql_select.cc 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 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;