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
2682 Sergey Petrunia 2008-07-27
WL#3985: Fix pushbuild failure: handle the case where the entire subquery predicate is uncorrelated
and cost optimization selected duplicate weedout over materialization.
modified:
mysql-test/r/subselect_sj2.result
sql/sql_select.cc
sql/sql_select.h
=== modified file 'mysql-test/r/subselect2.result'
--- a/mysql-test/r/subselect2.result 2008-05-01 22:41:35 +0000
+++ b/mysql-test/r/subselect2.result 2008-07-27 19:17:41 +0000
@@ -123,16 +123,16 @@ DOCID DOCNAME DOCTYPEID FOLDERID AUTHOR
c373e9f5ad07993f3859444553544200 Last Discussion c373e9f5ad079174ff17444553544200 c373e9f5ad0796c0eca4444553544200 Goldilocks 2003-06-09 11:21:06 Title: Last Discussion NULL Setting new abstract and keeping doc checked out 2003-06-09 10:51:26 2003-06-09 10:51:26 NULL NULL NULL 03eea05112b845949f3fd03278b5fe43 2003-06-09 11:21:06 admin 0 NULL Discussion NULL NULL
EXPLAIN EXTENDED SELECT t2.*, t4.DOCTYPENAME, t1.CONTENTSIZE,t1.MIMETYPE FROM t2 INNER JOIN t4 ON t2.DOCTYPEID = t4.DOCTYPEID LEFT OUTER JOIN t1 ON t2.DOCID = t1.DOCID WHERE t2.FOLDERID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t3.FOLDERNAME = 'Level1') AND t3.FOLDERNAME = 'Level2') AND t3.FOLDERNAME = 'Level3') AND t3.FOLDERNAME = 'CopiedFolder') AND t3.FOLDERNAME = 'Movie Reviews') AND t2.DOCNAME = 'Last Discussion';
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t3 ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX CMFLDRPARNT_IDX 35 const 6 100.00 Using index condition; Using where
-1 PRIMARY t3 ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX CMFLDRPARNT_IDX 35 test.t3.FOLDERID 1 100.00 Using where
-1 PRIMARY t3 ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX CMFLDRPARNT_IDX 35 test.t3.FOLDERID 1 100.00 Using where
-1 PRIMARY t3 ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX CMFLDRPARNT_IDX 35 test.t3.FOLDERID 1 100.00 Using where
-1 PRIMARY t3 ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX CMFLDRPARNT_IDX 35 test.t3.FOLDERID 1 100.00 Using where
-1 PRIMARY t2 ALL DDOCTYPEID_IDX,DFOLDERID_IDX NULL NULL NULL 9 77.78 Using where; Using join buffer
+1 PRIMARY t2 ALL DDOCTYPEID_IDX NULL NULL NULL 9 100.00 Using where
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 34 test.t2.DOCID 1 100.00
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 34 test.t2.DOCTYPEID 1 100.00
+2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 func 1 100.00 Using where
+2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 100.00 Using where
+2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 100.00 Using where
+2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 100.00 Using where
+2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 100.00 Using where
Warnings:
-Note 1003 select `test`.`t2`.`DOCID` AS `DOCID`,`test`.`t2`.`DOCNAME` AS `DOCNAME`,`test`.`t2`.`DOCTYPEID` AS `DOCTYPEID`,`test`.`t2`.`FOLDERID` AS `FOLDERID`,`test`.`t2`.`AUTHOR` AS `AUTHOR`,`test`.`t2`.`CREATED` AS `CREATED`,`test`.`t2`.`TITLE` AS `TITLE`,`test`.`t2`.`SUBTITLE` AS `SUBTITLE`,`test`.`t2`.`DOCABSTRACT` AS `DOCABSTRACT`,`test`.`t2`.`PUBLISHDATE` AS `PUBLISHDATE`,`test`.`t2`.`EXPIRATIONDATE` AS `EXPIRATIONDATE`,`test`.`t2`.`LOCKEDBY` AS `LOCKEDBY`,`test`.`t2`.`STATUS` AS `STATUS`,`test`.`t2`.`PARENTDOCID` AS `PARENTDOCID`,`test`.`t2`.`REPID` AS `REPID`,`test`.`t2`.`MODIFIED` AS `MODIFIED`,`test`.`t2`.`MODIFIER` AS `MODIFIER`,`test`.`t2`.`PUBLISHSTATUS` AS `PUBLISHSTATUS`,`test`.`t2`.`ORIGINATOR` AS `ORIGINATOR`,`test`.`t4`.`DOCTYPENAME` AS `DOCTYPENAME`,`test`.`t1`.`CONTENTSIZE` AS `CONTENTSIZE`,`test`.`t1`.`MIMETYPE` AS `MIMETYPE` from `test`.`t3` join `test`.`t3` join `test`.`t3` join `test`.`t3` join `test`.`t3` join `test`.`t2` join `test`.`t4` left join `
test`.`t1` on((`test`.`t1`.`DOCID` = `test`.`t2`.`DOCID`)) where ((`test`.`t4`.`DOCTYPEID` = `test`.`t2`.`DOCTYPEID`) and (`test`.`t2`.`FOLDERID` = `test`.`t3`.`FOLDERID`) and (`test`.`t3`.`PARENTID` = `test`.`t3`.`FOLDERID`) and (`test`.`t3`.`PARENTID` = `test`.`t3`.`FOLDERID`) and (`test`.`t3`.`PARENTID` = `test`.`t3`.`FOLDERID`) and (`test`.`t3`.`PARENTID` = `test`.`t3`.`FOLDERID`) and (`test`.`t3`.`FOLDERNAME` = 'Level1') and (`test`.`t3`.`PARENTID` = '2f6161e879db43c1a5b82c21ddc49089') and (`test`.`t3`.`FOLDERNAME` = 'Level2') and (`test`.`t3`.`FOLDERNAME` = 'Level3') and (`test`.`t3`.`FOLDERNAME` = 'CopiedFolder') and (`test`.`t3`.`FOLDERNAME` = 'Movie Reviews') and (`test`.`t2`.`DOCNAME` = 'Last Discussion'))
+Note 1003 select `test`.`t2`.`DOCID` AS `DOCID`,`test`.`t2`.`DOCNAME` AS `DOCNAME`,`test`.`t2`.`DOCTYPEID` AS `DOCTYPEID`,`test`.`t2`.`FOLDERID` AS `FOLDERID`,`test`.`t2`.`AUTHOR` AS `AUTHOR`,`test`.`t2`.`CREATED` AS `CREATED`,`test`.`t2`.`TITLE` AS `TITLE`,`test`.`t2`.`SUBTITLE` AS `SUBTITLE`,`test`.`t2`.`DOCABSTRACT` AS `DOCABSTRACT`,`test`.`t2`.`PUBLISHDATE` AS `PUBLISHDATE`,`test`.`t2`.`EXPIRATIONDATE` AS `EXPIRATIONDATE`,`test`.`t2`.`LOCKEDBY` AS `LOCKEDBY`,`test`.`t2`.`STATUS` AS `STATUS`,`test`.`t2`.`PARENTDOCID` AS `PARENTDOCID`,`test`.`t2`.`REPID` AS `REPID`,`test`.`t2`.`MODIFIED` AS `MODIFIED`,`test`.`t2`.`MODIFIER` AS `MODIFIER`,`test`.`t2`.`PUBLISHSTATUS` AS `PUBLISHSTATUS`,`test`.`t2`.`ORIGINATOR` AS `ORIGINATOR`,`test`.`t4`.`DOCTYPENAME` AS `DOCTYPENAME`,`test`.`t1`.`CONTENTSIZE` AS `CONTENTSIZE`,`test`.`t1`.`MIMETYPE` AS `MIMETYPE` from `test`.`t2` join `test`.`t4` left join `test`.`t1` on((`test`.`t1`.`DOCID` = `test`.`t2`.`DOCID`)) where ((`test`.`t4`.`DOCTY
PEID` = `test`.`t2`.`DOCTYPEID`) and (`test`.`t2`.`DOCNAME` = 'Last Discussion') and <in_optimizer>(`test`.`t2`.`FOLDERID`,<exists>(select 1 AS `Not_used` from `test`.`t3` join `test`.`t3` join `test`.`t3` join `test`.`t3` join `test`.`t3` where ((`test`.`t3`.`FOLDERID` = `test`.`t3`.`PARENTID`) and (`test`.`t3`.`FOLDERID` = `test`.`t3`.`PARENTID`) and (`test`.`t3`.`FOLDERID` = `test`.`t3`.`PARENTID`) and (`test`.`t3`.`FOLDERID` = `test`.`t3`.`PARENTID`) and (`test`.`t3`.`FOLDERNAME` = 'Level1') and (`test`.`t3`.`PARENTID` = '2f6161e879db43c1a5b82c21ddc49089') and (`test`.`t3`.`FOLDERNAME` = 'Level2') and (`test`.`t3`.`FOLDERNAME` = 'Level3') and (`test`.`t3`.`FOLDERNAME` = 'CopiedFolder') and (`test`.`t3`.`FOLDERNAME` = 'Movie Reviews') and (<cache>(`test`.`t2`.`FOLDERID`) = `test`.`t3`.`FOLDERID`)))))
drop table t1, t2, t3, t4;
CREATE TABLE t1 (a int(10) , PRIMARY KEY (a)) Engine=InnoDB;
INSERT INTO t1 VALUES (1),(2);
=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result 2008-06-11 23:16:53 +0000
+++ b/mysql-test/r/subselect_sj.result 2008-07-27 19:17:41 +0000
@@ -73,18 +73,18 @@ id select_type tABle type possiBle_keys
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
1 PRIMARY A ALL NULL NULL NULL NULL 3 100.00
1 PRIMARY B ALL NULL NULL NULL NULL 3 100.00
-1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.B.A 1 100.00 Using index
+2 DEPENDENT SUBQUERY t10 unique_suBquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
-Note 1003 select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`A`.`A` AS `A`,`test`.`A`.`B` AS `B`,`test`.`B`.`A` AS `A`,`test`.`B`.`B` AS `B` from `test`.`t1` left join (`test`.`t10` join `test`.`t2` `A` join `test`.`t2` `B`) on(((`test`.`A`.`A` = `test`.`t1`.`A`) And 1 And (`test`.`B`.`A` = `test`.`t10`.`pk`))) where 1
+Note 1003 select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`A`.`A` AS `A`,`test`.`A`.`B` AS `B`,`test`.`B`.`A` AS `A`,`test`.`B`.`B` AS `B` from `test`.`t1` left join (`test`.`t2` `A` join `test`.`t2` `B`) on(((`test`.`A`.`A` = `test`.`t1`.`A`) And <in_optimizer>(`test`.`B`.`A`,<exists>(<primAry_index_lookup>(<cAche>(`test`.`B`.`A`) in t10 on PRIMARY))))) where 1
t2 should be wrapped into OJ-nest, so we have "t1 LJ (t2 J t10)"
explAin extended
select * from t1 left join t2 on (t2.A= t1.A And t2.A in (select pk from t10));
id select_type tABle type possiBle_keys key key_len ref rows filtered ExtrA
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00
-1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t2.A 1 100.00 Using index
+2 DEPENDENT SUBQUERY t10 unique_suBquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
-Note 1003 select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`t2`.`A` AS `A`,`test`.`t2`.`B` AS `B` from `test`.`t1` left join (`test`.`t10` join `test`.`t2`) on(((`test`.`t2`.`A` = `test`.`t1`.`A`) And 1 And (`test`.`t2`.`A` = `test`.`t10`.`pk`))) where 1
+Note 1003 select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`t2`.`A` AS `A`,`test`.`t2`.`B` AS `B` from `test`.`t1` left join `test`.`t2` on(((`test`.`t2`.`A` = `test`.`t1`.`A`) And <in_optimizer>(`test`.`t2`.`A`,<exists>(<primAry_index_lookup>(<cAche>(`test`.`t2`.`A`) in t10 on PRIMARY))))) where 1
we shouldn't flatten if we're going to get a join of > MAX_TABLES.
explain select * from
t1 s00, t1 s01, t1 s02, t1 s03, t1 s04,t1 s05,t1 s06,t1 s07,t1 s08,t1 s09,
=== modified file 'mysql-test/r/subselect_sj2.result'
--- a/mysql-test/r/subselect_sj2.result 2008-07-27 12:55:23 +0000
+++ b/mysql-test/r/subselect_sj2.result 2008-07-27 19:17:41 +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 index b b 5 NULL 10 Using index; Materialize
+1 PRIMARY t2 ref b b 5 test.t1.a 2 Using index; FirstMatch(t1)
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 ALL CountryCode NULL NULL NULL 545 Using where; Materialize
+1 PRIMARY t2 ref CountryCode CountryCode 3 test.t1.Code 18 Using where; FirstMatch(t1)
SELECT Name FROM t1
WHERE t1.Code IN (
SELECT t2.CountryCode FROM t2 WHERE Population > 5000000);
@@ -563,7 +563,7 @@ select * from t1 left join t2 on (t2.a=
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 ALL NULL NULL NULL NULL 3
-1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 Using index
+2 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY PRIMARY 4 func 1 Using index
drop table t0, t1, t2, t3;
create table t1 (a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -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 index a a 5 NULL 30000 Using index; Materialize
+1 PRIMARY t3 ref a a 5 test.t2.a 30 Using index; FirstMatch(t2)
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 12:55:23 +0000
+++ b/sql/sql_select.cc 2008-07-27 19:17:41 +0000
@@ -3683,6 +3683,20 @@ bool JOIN::flatten_subqueries()
(*in_subq)->sj_convert_priority=
(*in_subq)->is_correlated * MAX_TABLES + child_join->outer_tables;
}
+
+ // Temporary measure: disable semi-joins when they are together with outer
+ // joins.
+ for (TABLE_LIST *tbl= select_lex->leaf_tables; tbl; tbl=tbl->next_leaf)
+ {
+ TABLE_LIST *embedding= tbl->embedding;
+ if (tbl->on_expr || (tbl->embedding && !(embedding->sj_on_expr &&
+ !embedding->embedding)))
+ {
+ in_subq= sj_subselects.front();
+ arena= thd->activate_stmt_arena_if_needed(&backup);
+ goto skip_conversion;
+ }
+ }
//dump_TABLE_LIST_struct(select_lex, select_lex->leaf_tables);
/*
@@ -3715,10 +3729,9 @@ bool JOIN::flatten_subqueries()
if (convert_subq_to_sj(this, *in_subq))
DBUG_RETURN(TRUE);
}
- if (arena)
- thd->restore_active_arena(arena, &backup);
-
+skip_conversion:
/* 3. Finalize those we didn't convert */
+ bool converted= FALSE;
for (; in_subq!= in_subq_end; in_subq++)
{
JOIN *child_join= (*in_subq)->unit->first_select()->join;
@@ -3728,6 +3741,7 @@ bool JOIN::flatten_subqueries()
SELECT_LEX *save_select_lex= thd->lex->current_select;
thd->lex->current_select= (*in_subq)->unit->first_select();
+ converted= TRUE;
res= (*in_subq)->select_transformer(child_join);
@@ -3746,7 +3760,22 @@ bool JOIN::flatten_subqueries()
if (replace_where_subcondition(this, tree, *in_subq, substitute,
do_fix_fields))
DBUG_RETURN(TRUE);
+ /* psergey-todo: remove this hack: */
+ (*in_subq)->substitution= NULL;
+
+ if (!thd->stmt_arena->is_conventional())
+ {
+ tree= ((*in_subq)->emb_on_expr_nest == (TABLE_LIST*)1)?
+ &select_lex->prep_where : &((*in_subq)->emb_on_expr_nest->prep_on_expr);
+
+ if (replace_where_subcondition(this, tree, *in_subq, substitute,
+ FALSE))
+ DBUG_RETURN(TRUE);
+ }
}
+
+ if (arena)
+ thd->restore_active_arena(arena, &backup);
sj_subselects.clear();
DBUG_RETURN(FALSE);
}
@@ -5843,6 +5872,11 @@ public:
quick_uses_applicable_index(FALSE)
{
LINT_INIT(quick_max_loose_keypart); // Protected by sj_insideout_quick_*
+ /* Protected by best_loose_scan_cost!= DBL_MAX */
+ LINT_INIT(best_loose_scan_key);
+ LINT_INIT(best_loose_scan_records);
+ LINT_INIT(best_max_loose_keypart);
+ LINT_INIT(best_loose_scan_start_key);
}
void init(JOIN *join, JOIN_TAB *s, table_map remaining_tables)
@@ -6018,11 +6052,14 @@ public:
void save_to_position(POSITION *pos)
{
- pos->records_read= best_loose_scan_records;
pos->read_time= best_loose_scan_cost;
- pos->key= best_loose_scan_start_key;
- pos->loosescan_key= best_loose_scan_key;
- pos->loosescan_parts= best_max_loose_keypart + 1;
+ if (best_loose_scan_cost != DBL_MAX)
+ {
+ pos->records_read= best_loose_scan_records;
+ pos->key= best_loose_scan_start_key;
+ pos->loosescan_key= best_loose_scan_key;
+ pos->loosescan_parts= best_max_loose_keypart + 1;
+ }
}
};
@@ -7741,6 +7778,7 @@ get_best_combination(JOIN *join, table_m
POSITION *pos= join->best_positions + tablenr;
JOIN_TAB *s= pos->table;
uint first;
+ LINT_INIT(first); // Set by every branch except SJ_OPT_NONE which doesn't use it
if ((handled_tabs & s->table->map) || pos->sj_strategy == SJ_OPT_NONE)
continue;
@@ -7754,6 +7792,7 @@ get_best_combination(JOIN *join, table_m
sizeof(POSITION) * sjm->n_tables);
first= tablenr - sjm->n_tables + 1;
join->best_positions[first].n_sj_tables= sjm->n_tables;
+ join->best_positions[first].sj_strategy= SJ_OPT_MATERIALIZE;
}
else if (pos->sj_strategy == SJ_OPT_MATERIALIZE_SCAN)
{
@@ -7764,6 +7803,7 @@ get_best_combination(JOIN *join, table_m
first= pos->sjm_scan_last_inner - sjm->n_tables + 1;
memcpy(join->best_positions + first,
sjm->positions, sizeof(POSITION) * sjm->n_tables);
+ join->best_positions[first].sj_strategy= SJ_OPT_MATERIALIZE_SCAN;
join->best_positions[first].use_sj_mat |= SJ_MAT_SCAN;
join->best_positions[first].n_sj_tables= sjm->n_tables;
}
| Thread |
|---|
| • bzr push into mysql-6.0-opt-subqueries branch (sergefp:2682 to 2683) WL#3985 | Sergey Petrunia | 27 Jul |
