diff options
| -rw-r--r-- | mysql-test/r/table_elim.result | 28 | ||||
| -rw-r--r-- | mysql-test/t/table_elim.test | 29 | ||||
| -rw-r--r-- | sql/opt_table_elimination.cc | 15 |
3 files changed, 72 insertions, 0 deletions
diff --git a/mysql-test/r/table_elim.result b/mysql-test/r/table_elim.result index 35acd30d76d..1ac46a21cce 100644 --- a/mysql-test/r/table_elim.result +++ b/mysql-test/r/table_elim.result @@ -609,4 +609,32 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where; Using index drop view v1; DROP TABLE t1,t2,t3; +# +# MDEV-5344: LEFT OUTER JOIN table data is lost in ON DUPLICATE KEY UPDATE section +# +create table t1 ( +id int(10) unsigned NOT NULL DEFAULT '0', +v int(10) unsigned DEFAULT '0', +PRIMARY KEY (id) +); +create table t2 ( +id int(10) unsigned NOT NULL DEFAULT '0', +PRIMARY KEY (id) +) ; +create table t3 ( +id int(10) unsigned NOT NULL DEFAULT '0', +v int(10) unsigned DEFAULT '0', +PRIMARY KEY (id) +); +insert into t1 values (1, 10), (2, 10); +insert into t2 values (1), (2); +insert into t3 values (1, 20); +insert into t1 +select t2.id, 5 from t2 LEFT OUTER JOIN t3 ON t2.id = t3.id +on duplicate key update t1.v = t3.v; +select * from t1; +id v +1 20 +2 NULL +drop table t1,t2,t3; SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/table_elim.test b/mysql-test/t/table_elim.test index a957e24e393..5269cb1bda9 100644 --- a/mysql-test/t/table_elim.test +++ b/mysql-test/t/table_elim.test @@ -543,6 +543,35 @@ EXPLAIN SELECT alias1.* FROM t3 LEFT JOIN v1 as alias1 ON ( t3.a = alias1.b ); drop view v1; DROP TABLE t1,t2,t3; +--echo # +--echo # MDEV-5344: LEFT OUTER JOIN table data is lost in ON DUPLICATE KEY UPDATE section +--echo # +create table t1 ( + id int(10) unsigned NOT NULL DEFAULT '0', + v int(10) unsigned DEFAULT '0', + PRIMARY KEY (id) +); +create table t2 ( + id int(10) unsigned NOT NULL DEFAULT '0', + PRIMARY KEY (id) +) ; + +create table t3 ( + id int(10) unsigned NOT NULL DEFAULT '0', + v int(10) unsigned DEFAULT '0', + PRIMARY KEY (id) +); + +insert into t1 values (1, 10), (2, 10); +insert into t2 values (1), (2); +insert into t3 values (1, 20); + +insert into t1 +select t2.id, 5 from t2 LEFT OUTER JOIN t3 ON t2.id = t3.id +on duplicate key update t1.v = t3.v; + +select * from t1; +drop table t1,t2,t3; SET optimizer_switch=@save_optimizer_switch; diff --git a/sql/opt_table_elimination.cc b/sql/opt_table_elimination.cc index 545001c9df1..1df35e93e45 100644 --- a/sql/opt_table_elimination.cc +++ b/sql/opt_table_elimination.cc @@ -594,6 +594,21 @@ void eliminate_tables(JOIN *join) /* Find the tables that are referred to from WHERE/HAVING */ used_tables= (join->conds? join->conds->used_tables() : 0) | (join->having? join->having->used_tables() : 0); + + /* + For "INSERT ... SELECT ... ON DUPLICATE KEY UPDATE column = val" + we should also take into account tables mentioned in "val". + */ + if (join->thd->lex->sql_command == SQLCOM_INSERT_SELECT && + join->select_lex == &thd->lex->select_lex) + { + List_iterator<Item> val_it(thd->lex->value_list); + while ((item= val_it++)) + { + DBUG_ASSERT(item->fixed); + used_tables |= item->used_tables(); + } + } /* Add tables referred to from the select list */ List_iterator<Item> it(join->fields_list); |