diff options
| -rw-r--r-- | mysql-test/r/subselect_sj2_mat.result | 52 | ||||
| -rw-r--r-- | mysql-test/r/view.result | 2 | ||||
| -rw-r--r-- | mysql-test/t/subselect_sj2_mat.test | 42 | ||||
| -rw-r--r-- | sql/item_cmpfunc.cc | 2 | ||||
| -rw-r--r-- | sql/item_cmpfunc.h | 4 | ||||
| -rw-r--r-- | sql/sql_select.cc | 2 |
6 files changed, 103 insertions, 1 deletions
diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result index bfae3e57338..900aaf119ff 100644 --- a/mysql-test/r/subselect_sj2_mat.result +++ b/mysql-test/r/subselect_sj2_mat.result @@ -1281,3 +1281,55 @@ g y y y g y y y DROP TABLE t1,t2,t3,t4; set optimizer_switch=@save_optimizer_switch; +# +# mdev-4172: LEFT JOIN with materialized multi-table IN subquery in WHERE +# and OR in ON condition +# +set @save_optimizer_switch=@@optimizer_switch; +CREATE TABLE t1 (a1 int, c1 varchar(1)); +INSERT t1 VALUES (7,'v'), (3,'y'); +CREATE TABLE t2 (c2 varchar(1)); +INSERT INTO t2 VALUES ('y'), ('y'); +CREATE TABLE t3 (c3 varchar(1)); +INSERT INTO t3 VALUES +('j'), ('v'), ('c'), ('m'), ('d'), +('d'), ('y'), ('t'), ('d'), ('s'); +CREATE TABLE t4 (a4 int, c4 varchar(1)); +INSERT INTO t4 SELECT * FROM t1; +set optimizer_switch='materialization=off'; +EXPLAIN EXTENDED +SELECT * FROM t1 LEFT JOIN t2 ON (c2 = c1 OR c1 > 'z') +WHERE c1 IN ( SELECT c4 FROM t3,t4 WHERE c3 = c4); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Start temporary; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 10 100.00 Using where; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2` from `test`.`t1` semi join (`test`.`t3` join `test`.`t4`) left join `test`.`t2` on(((`test`.`t2`.`c2` = `test`.`t1`.`c1`) or (`test`.`t1`.`c1` > 'z'))) where ((`test`.`t4`.`c4` = `test`.`t1`.`c1`) and (`test`.`t3`.`c3` = `test`.`t1`.`c1`)) +SELECT * FROM t1 LEFT JOIN t2 ON (c2 = c1 OR c1 > 'z') +WHERE c1 IN ( SELECT c4 FROM t3,t4 WHERE c3 = c4); +a1 c1 c2 +3 y y +3 y y +7 v NULL +set optimizer_switch='materialization=on'; +EXPLAIN EXTENDED +SELECT * FROM t1 LEFT JOIN t2 ON (c2 = c1 OR c1 > 'z') +WHERE c1 IN ( SELECT c4 FROM t3,t4 WHERE c3 = c4); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t4 ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 10 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2` from `test`.`t1` semi join (`test`.`t3` join `test`.`t4`) left join `test`.`t2` on(((`test`.`t2`.`c2` = `test`.`t1`.`c1`) or (`test`.`t1`.`c1` > 'z'))) where (`test`.`t3`.`c3` = `test`.`t4`.`c4`) +SELECT * FROM t1 LEFT JOIN t2 ON (c2 = c1 OR c1 > 'z') +WHERE c1 IN ( SELECT c4 FROM t3,t4 WHERE c3 = c4); +a1 c1 c2 +3 y y +3 y y +7 v NULL +DROP TABLE t1,t2,t3,t4; +set optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index ffda4a64447..ee6c235d09e 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -1437,7 +1437,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `b` from `test`.`t3` left join (`test`.`t1` left join (`test`.`t2`) on(((`test`.`t1`.`a` = `test`.`t3`.`a`) and (`test`.`t2`.`a` = `test`.`t3`.`a`)))) on((`test`.`t1`.`a` = `test`.`t3`.`a`)) where 1 +Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `b` from `test`.`t3` left join (`test`.`t1` left join (`test`.`t2`) on((`test`.`t2`.`a` = `test`.`t3`.`a`))) on((`test`.`t1`.`a` = `test`.`t3`.`a`)) where 1 prepare stmt1 from "select * from t3 left join v4 on (t3.a = v4.a);"; execute stmt1; a a b diff --git a/mysql-test/t/subselect_sj2_mat.test b/mysql-test/t/subselect_sj2_mat.test index 84e60a649b1..90d8e4d5467 100644 --- a/mysql-test/t/subselect_sj2_mat.test +++ b/mysql-test/t/subselect_sj2_mat.test @@ -177,3 +177,45 @@ SELECT * FROM t1 LEFT JOIN t2 ON ( b1 = a2 ) DROP TABLE t1,t2,t3,t4; set optimizer_switch=@save_optimizer_switch; + +--echo # +--echo # mdev-4172: LEFT JOIN with materialized multi-table IN subquery in WHERE +--echo # and OR in ON condition +--echo # + +set @save_optimizer_switch=@@optimizer_switch; + +CREATE TABLE t1 (a1 int, c1 varchar(1)); +INSERT t1 VALUES (7,'v'), (3,'y'); + +CREATE TABLE t2 (c2 varchar(1)); +INSERT INTO t2 VALUES ('y'), ('y'); + +CREATE TABLE t3 (c3 varchar(1)); +INSERT INTO t3 VALUES + ('j'), ('v'), ('c'), ('m'), ('d'), + ('d'), ('y'), ('t'), ('d'), ('s'); + +CREATE TABLE t4 (a4 int, c4 varchar(1)); +INSERT INTO t4 SELECT * FROM t1; + +set optimizer_switch='materialization=off'; + +EXPLAIN EXTENDED +SELECT * FROM t1 LEFT JOIN t2 ON (c2 = c1 OR c1 > 'z') + WHERE c1 IN ( SELECT c4 FROM t3,t4 WHERE c3 = c4); +SELECT * FROM t1 LEFT JOIN t2 ON (c2 = c1 OR c1 > 'z') + WHERE c1 IN ( SELECT c4 FROM t3,t4 WHERE c3 = c4); + +set optimizer_switch='materialization=on'; + +EXPLAIN EXTENDED +SELECT * FROM t1 LEFT JOIN t2 ON (c2 = c1 OR c1 > 'z') + WHERE c1 IN ( SELECT c4 FROM t3,t4 WHERE c3 = c4); +SELECT * FROM t1 LEFT JOIN t2 ON (c2 = c1 OR c1 > 'z') + WHERE c1 IN ( SELECT c4 FROM t3,t4 WHERE c3 = c4); + +DROP TABLE t1,t2,t3,t4; + +set optimizer_switch=@save_optimizer_switch; + diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index fa86f8af024..b6ffc03a794 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -5458,6 +5458,7 @@ Item_equal::Item_equal(Item *f1, Item *f2, bool with_const_item) equal_items.push_back(f1); equal_items.push_back(f2); compare_as_dates= with_const_item && f2->cmp_type() == TIME_RESULT; + upper_levels= NULL; } @@ -5486,6 +5487,7 @@ Item_equal::Item_equal(Item_equal *item_equal) with_const= item_equal->with_const; compare_as_dates= item_equal->compare_as_dates; cond_false= item_equal->cond_false; + upper_levels= item_equal->upper_levels; } diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 2dc70b7add7..36d992edc32 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -1739,7 +1739,11 @@ class Item_equal: public Item_bool_func used in the original equality. */ Item_field *context_field; + public: + + COND_EQUAL *upper_levels; /* multiple equalities of upper and levels */ + inline Item_equal() : Item_bool_func(), with_const(FALSE), eval_item(0), cond_false(0), context_field(NULL) diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 4d8b2b4a370..041df9d0558 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -11438,6 +11438,7 @@ static COND *build_equal_items_for_cond(THD *thd, COND *cond, item_equal->update_used_tables(); set_if_bigger(thd->lex->current_select->max_equal_elems, item_equal->n_field_items()); + item_equal->upper_levels= inherited; return item_equal; } @@ -12121,6 +12122,7 @@ static COND* substitute_for_best_equal_field(JOIN_TAB *context_tab, { item_equal= (Item_equal *) cond; item_equal->sort(&compare_fields_by_table_order, table_join_idx); + cond_equal= item_equal->upper_levels; if (cond_equal && cond_equal->current_level.head() == item_equal) cond_equal= cond_equal->upper_levels; cond= eliminate_item_equal(0, cond_equal, item_equal); |