Merge 5.3 -> 5.5

pending merges:
  Sergey Petrunya 2013-11-27 MDEV-5344: LEFT OUTER JOIN table data is lost...

3 files changed, 74 insertions, 0 deletions

diff --git a/mysql-test/r/table_elim.result b/mysql-test/r/table_elim.result
index 0b19b6b4eaf..3b4d64c1bfa 100644
--- a/mysql-test/r/table_elim.result
+++ b/mysql-test/r/table_elim.result
@@ -610,6 +610,34 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 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;
+#
 # BUG#919878: Assertion `!eliminated_tables...
 #
 CREATE TABLE t1 ( a INT );
diff --git a/mysql-test/t/table_elim.test b/mysql-test/t/table_elim.test
index 357953290c4..0d42dca57b7 100644
--- a/mysql-test/t/table_elim.test
+++ b/mysql-test/t/table_elim.test
@@ -544,6 +544,37 @@ 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;
+
+--echo #
 --echo # BUG#919878: Assertion `!eliminated_tables...
 --echo #
 CREATE TABLE t1 ( a INT );
diff --git a/sql/opt_table_elimination.cc b/sql/opt_table_elimination.cc
index 4a10d402fac..9f304ad043b 100644
--- a/sql/opt_table_elimination.cc
+++ b/sql/opt_table_elimination.cc
@@ -609,6 +609,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);