MDEV-6163: Error while executing an update query that has the same table in a sub-query

We have to run the derived table prepare before the unique table check to mark the derived table (in this case the unique table check can turn that table to materialized one).

3 files changed, 40 insertions, 0 deletions

diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result
index 3a3b69f1fc7..d0f42c3d3ec 100644
--- a/mysql-test/r/derived.result
+++ b/mysql-test/r/derived.result
@@ -539,6 +539,7 @@ n	d1	d2	result
 2085	2012-01-01 00:00:00	2013-01-01 00:00:00	0
 2084	2012-02-01 00:00:00	2013-01-01 00:00:00	0
 drop table t1;
+set @save_derived_optimizer_switch_bug=@@optimizer_switch;
 SET optimizer_switch = 'derived_merge=on,derived_with_keys=on,in_to_exists=on';
 CREATE TABLE t1 (a INT) ENGINE=MyISAM;
 INSERT INTO t1 VALUES (8);
@@ -553,4 +554,17 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 Warnings:
 Note	1249	Select 4 was reduced during optimization
 DROP TABLE t1, t2;
+set optimizer_switch=@save_derived_optimizer_switch_bug;
+#
+# MDEV-6163: Error while executing an update query that has the
+# same table in a sub-query
+#
+set @save_derived_optimizer_switch_bug=@@optimizer_switch;
+SET optimizer_switch = 'derived_merge=on';
+create table t1 (balance float, accountId varchar(64), primary key (accountId));
+insert into t1 (accountId,balance) values 
+('dealer-1',199354.0),('dealer-2',0),('dealer-3',0),('dealer-5',0),('FINANCE',-200000),('OPERATOR',0);
+update t1 set balance=(select sum(balance) from (SELECT balance FROM t1 where accountId like 'dealer%') AS copied) where accountId = 'OPERATOR';
+set optimizer_switch=@save_derived_optimizer_switch_bug;
+drop table t1;
 set optimizer_switch=@save_derived_optimizer_switch;
diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test
index 4b1d7604b9d..61ae3695a1f 100644
--- a/mysql-test/t/derived.test
+++ b/mysql-test/t/derived.test
@@ -465,6 +465,7 @@ drop table t1;
 #
 # MDEV-5012 Server crashes in Item_ref::real_item on EXPLAIN with select subqueries or views, constant table, derived_merge+derived_with_keys
 #
+set @save_derived_optimizer_switch_bug=@@optimizer_switch;
 SET optimizer_switch = 'derived_merge=on,derived_with_keys=on,in_to_exists=on';
 CREATE TABLE t1 (a INT) ENGINE=MyISAM;
 INSERT INTO t1 VALUES (8);
@@ -473,5 +474,23 @@ INSERT INTO t2 VALUES (1),(7);
 EXPLAIN SELECT * FROM (SELECT * FROM t1) AS table1,
   (SELECT DISTINCT * FROM t2) AS table2 WHERE b = a AND a <> ANY (SELECT 9);
 DROP TABLE t1, t2;
+set optimizer_switch=@save_derived_optimizer_switch_bug;
+
+--echo #
+--echo # MDEV-6163: Error while executing an update query that has the
+--echo # same table in a sub-query
+--echo #
+
+set @save_derived_optimizer_switch_bug=@@optimizer_switch;
+SET optimizer_switch = 'derived_merge=on';
+create table t1 (balance float, accountId varchar(64), primary key (accountId));
+
+insert into t1 (accountId,balance) values 
+('dealer-1',199354.0),('dealer-2',0),('dealer-3',0),('dealer-5',0),('FINANCE',-200000),('OPERATOR',0);
+
+update t1 set balance=(select sum(balance) from (SELECT balance FROM t1 where accountId like 'dealer%') AS copied) where accountId = 'OPERATOR'; 
+set optimizer_switch=@save_derived_optimizer_switch_bug;
+drop table t1;
+
 
 set optimizer_switch=@save_derived_optimizer_switch;
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index e785b1106cf..300769ef099 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -1017,6 +1017,13 @@ bool mysql_prepare_update(THD *thd, TABLE_LIST *table_list,
 
   thd->lex->allow_sum_func= 0;
 
+  /*
+    We do not call DT_MERGE_FOR_INSERT because it has no sense for simple
+    (not multi-) update
+  */
+  if (mysql_handle_derived(thd->lex, DT_PREPARE))
+    DBUG_RETURN(TRUE);
+
   if (setup_tables_and_check_access(thd, &select_lex->context, 
                                     &select_lex->top_join_list,
                                     table_list,