diff options
| -rw-r--r-- | mysql-test/r/subselect_sj_mat.result | 13 | ||||
| -rw-r--r-- | mysql-test/t/subselect_sj_mat.test | 13 | ||||
| -rw-r--r-- | sql/sql_select.cc | 17 |
3 files changed, 37 insertions, 6 deletions
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index 1f96932309d..45a3e951523 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -2026,6 +2026,19 @@ y z DROP VIEW v2; DROP TABLE t1, t2; +# +# BUG#1000269: Wrong result (extra rows) with semijoin+materialization, IN subqueries, join_cache_level>0 +# +CREATE TABLE t1 (a1 VARCHAR(1), a2 VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('b','b'),('e','e'); +CREATE TABLE t2 (b1 VARCHAR(1), b2 VARCHAR(1), KEY(b1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('v','v'),('s','s'),('l','l'), ('y','y'),('c','c'),('i','i'); +SELECT * FROM t1, t2 WHERE b1 IN ( SELECT b2 FROM t2 WHERE b1 > 'o' ) AND ( b1 < 'l' OR a1 IN ('b','c') ); +a1 a2 b1 b2 +b b v v +b b s s +b b y y +DROP TABLE t1,t2; # This must be at the end: set optimizer_switch=@subselect_sj_mat_tmp; set join_cache_level=@save_join_cache_level; diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index a077e9b5af5..4c40b2b5487 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -1664,6 +1664,19 @@ EXECUTE ps; DROP VIEW v2; DROP TABLE t1, t2; +--echo # +--echo # BUG#1000269: Wrong result (extra rows) with semijoin+materialization, IN subqueries, join_cache_level>0 +--echo # +CREATE TABLE t1 (a1 VARCHAR(1), a2 VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('b','b'),('e','e'); + +CREATE TABLE t2 (b1 VARCHAR(1), b2 VARCHAR(1), KEY(b1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('v','v'),('s','s'),('l','l'), ('y','y'),('c','c'),('i','i'); + +SELECT * FROM t1, t2 WHERE b1 IN ( SELECT b2 FROM t2 WHERE b1 > 'o' ) AND ( b1 < 'l' OR a1 IN ('b','c') ); + +DROP TABLE t1,t2; + --echo # This must be at the end: set optimizer_switch=@subselect_sj_mat_tmp; set join_cache_level=@save_join_cache_level; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 50d1ffbb19c..38c4726a0f8 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -93,7 +93,8 @@ static store_key *get_store_key(THD *thd, uint maybe_null); static bool make_outerjoin_info(JOIN *join); static Item* -make_cond_after_sjm(Item *root_cond, Item *cond, table_map tables, table_map sjm_tables); +make_cond_after_sjm(Item *root_cond, Item *cond, table_map tables, + table_map sjm_tables, bool inside_or_clause); static bool make_join_select(JOIN *join,SQL_SELECT *select,COND *item); static void revise_cache_usage(JOIN_TAB *join_tab); static bool make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after); @@ -8423,7 +8424,8 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) if (tab->bush_children) { // Reached the materialization tab - tmp= make_cond_after_sjm(cond, cond, save_used_tables, used_tables); + tmp= make_cond_after_sjm(cond, cond, save_used_tables, used_tables, + /*inside_or_clause=*/FALSE); used_tables= save_used_tables | used_tables; save_used_tables= 0; } @@ -17458,13 +17460,14 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, */ static COND * make_cond_after_sjm(Item *root_cond, Item *cond, table_map tables, - table_map sjm_tables) + table_map sjm_tables, bool inside_or_clause) { /* We assume that conditions that refer to only join prefix tables or sjm_tables have already been checked. */ - if ((!(cond->used_tables() & ~tables) || + if (!inside_or_clause && + (!(cond->used_tables() & ~tables) || !(cond->used_tables() & ~sjm_tables))) return (COND*) 0; // Already checked @@ -17481,7 +17484,8 @@ make_cond_after_sjm(Item *root_cond, Item *cond, table_map tables, Item *item; while ((item=li++)) { - Item *fix=make_cond_after_sjm(root_cond, item, tables, sjm_tables); + Item *fix=make_cond_after_sjm(root_cond, item, tables, sjm_tables, + inside_or_clause); if (fix) new_cond->argument_list()->push_back(fix); } @@ -17511,7 +17515,8 @@ make_cond_after_sjm(Item *root_cond, Item *cond, table_map tables, Item *item; while ((item=li++)) { - Item *fix= make_cond_after_sjm(root_cond, item, tables, 0L); + Item *fix= make_cond_after_sjm(root_cond, item, tables, sjm_tables, + /*inside_or_clause= */TRUE); if (!fix) return (COND*) 0; // Always true new_cond->argument_list()->push_back(fix); |