[Commits] c88ca2c: MDEV-8701 Crash on derived query

sanja at mariadb.com sanja at mariadb.com
Fri Nov 6 18:56:58 EET 2015


revision-id: c88ca2c22739dc2327c7b1082df79a93370662ba (mariadb-5.5.46-3-gc88ca2c)
parent(s): df804208657dfae10e24d656d9228f05e57b14a5
committer: Oleksandr Byelkin
timestamp: 2015-11-06 17:56:56 +0100
message:

MDEV-8701 Crash on derived query
MDEV-8938 Server Crash on Update with joins

Make unique table check after setup_fields of update because unique table can materialize table and we do not need field resolving after materialization.

---
 mysql-test/r/update.result | 59 +++++++++++++++++++++++++++++++++++++
 mysql-test/t/update.test   | 73 ++++++++++++++++++++++++++++++++++++++++++++++
 sql/sql_prepare.cc         |  3 +-
 sql/sql_update.cc          | 32 ++++++++++++++------
 sql/sql_update.h           |  1 +
 5 files changed, 158 insertions(+), 10 deletions(-)

diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result
index 3ea5855..0b036ca 100644
--- a/mysql-test/r/update.result
+++ b/mysql-test/r/update.result
@@ -618,3 +618,62 @@ Variable_name	Value
 Handler_update	5
 ROLLBACK;
 DROP TABLE t1, t2;
+#
+# MDEV-8938: Server Crash on Update with joins
+#
+CREATE TABLE `t1` (
+`name` varchar(255) NOT NULL,
+`value` varchar(4095) DEFAULT NULL,
+PRIMARY KEY (`name`)
+);
+UPDATE `t1` SET value = CONCAT("*.",(SELECT `temptable`.`value` FROM (SELECT * FROM `t1` WHERE `name`="consoleproxy.url.domain") AS `temptable` WHERE `temptable`.`name`="consoleproxy.url.domain")) WHERE `name`="consoleproxy.url.domain";
+drop table t1;
+CREATE TABLE `t1` (
+`name` varchar(255) NOT NULL,
+`value` varchar(4095) DEFAULT NULL,
+PRIMARY KEY (`name`)
+);
+create table t2 (
+`name` varchar(255) NOT NULL,
+`value` varchar(4095) DEFAULT NULL,
+PRIMARY KEY (`name`)
+);
+UPDATE t1
+SET value = (SELECT value FROM t2 WHERE `name`= t1.name) 
+WHERE value is null ;
+drop table t1,t2;
+#
+#MDEV-8701: Crash on derived query
+#
+CREATE TABLE t1 (
+data_exit_entry_id int(11) NOT NULL,
+data_entry_id int(11) NOT NULL,
+data_entry_exit_id int(11) NOT NULL,
+data_exit_entry_quantity double NOT NULL
+) DEFAULT CHARSET=utf8;
+CREATE TABLE t2 (
+data_entry_id int(11) NOT NULL,
+data_entry_cost double NOT NULL,
+data_entry_quantity double NOT NULL
+) DEFAULT CHARSET=utf8;
+create  algorithm=temptable view v1 as SELECT data_entry_exit_id, data_exit_entry_quantity, data_entry_cost 
+FROM t1 INNER JOIN t2 as dt ON dt.data_entry_id = t1.data_entry_id;
+UPDATE t2 
+SET data_entry_cost
+= ( ( SELECT SUM(data_exit_entry_quantity * data_entry_cost)
+FROM 
+v1 AS query 
+WHERE data_entry_exit_id = t2.data_entry_id
+)
+);
+UPDATE t2 
+SET data_entry_cost
+= ( ( SELECT SUM(data_exit_entry_quantity * data_entry_cost)
+FROM 
+( SELECT data_entry_exit_id, data_exit_entry_quantity, data_entry_cost 
+FROM t1 INNER JOIN t2 as dt ON dt.data_entry_id = t1.data_entry_id) AS query 
+WHERE data_entry_exit_id = t2.data_entry_id
+)
+);
+drop view v1;
+drop table t1, t2;
diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test
index be97dd0..e9d7ff2 100644
--- a/mysql-test/t/update.test
+++ b/mysql-test/t/update.test
@@ -559,3 +559,76 @@ SHOW STATUS LIKE 'HANDLER_UPDATE';
 ROLLBACK;
 DROP TABLE t1, t2;
 
+--echo #
+--echo # MDEV-8938: Server Crash on Update with joins
+--echo #
+
+CREATE TABLE `t1` (
+  `name` varchar(255) NOT NULL,
+  `value` varchar(4095) DEFAULT NULL,
+  PRIMARY KEY (`name`)
+);
+
+UPDATE `t1` SET value = CONCAT("*.",(SELECT `temptable`.`value` FROM (SELECT * FROM `t1` WHERE `name`="consoleproxy.url.domain") AS `temptable` WHERE `temptable`.`name`="consoleproxy.url.domain")) WHERE `name`="consoleproxy.url.domain";
+
+drop table t1;
+
+CREATE TABLE `t1` (
+  `name` varchar(255) NOT NULL,
+  `value` varchar(4095) DEFAULT NULL,
+  PRIMARY KEY (`name`)
+);
+
+create table t2 (
+  `name` varchar(255) NOT NULL,
+  `value` varchar(4095) DEFAULT NULL,
+  PRIMARY KEY (`name`)
+);
+
+UPDATE t1
+SET value = (SELECT value FROM t2 WHERE `name`= t1.name) 
+WHERE value is null ;
+
+drop table t1,t2;
+
+--echo #
+--echo #MDEV-8701: Crash on derived query
+--echo #
+
+CREATE TABLE t1 (
+  data_exit_entry_id int(11) NOT NULL,
+  data_entry_id int(11) NOT NULL,
+  data_entry_exit_id int(11) NOT NULL,
+  data_exit_entry_quantity double NOT NULL
+) DEFAULT CHARSET=utf8;
+
+CREATE TABLE t2 (
+  data_entry_id int(11) NOT NULL,
+  data_entry_cost double NOT NULL,
+  data_entry_quantity double NOT NULL
+) DEFAULT CHARSET=utf8;
+
+create  algorithm=temptable view v1 as SELECT data_entry_exit_id, data_exit_entry_quantity, data_entry_cost 
+          FROM t1 INNER JOIN t2 as dt ON dt.data_entry_id = t1.data_entry_id;
+
+UPDATE t2 
+SET data_entry_cost
+  = ( ( SELECT SUM(data_exit_entry_quantity * data_entry_cost)
+	FROM 
+        v1 AS query 
+        WHERE data_entry_exit_id = t2.data_entry_id
+      )
+    );
+
+UPDATE t2 
+SET data_entry_cost
+  = ( ( SELECT SUM(data_exit_entry_quantity * data_entry_cost)
+	FROM 
+        ( SELECT data_entry_exit_id, data_exit_entry_quantity, data_entry_cost 
+          FROM t1 INNER JOIN t2 as dt ON dt.data_entry_id = t1.data_entry_id) AS query 
+        WHERE data_entry_exit_id = t2.data_entry_id
+      )
+    );
+
+drop view v1;
+drop table t1, t2; 
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 4fcc007..c61c910 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -1408,7 +1408,8 @@ static int mysql_test_update(Prepared_statement *stmt,
     (SELECT_ACL & ~table_list->table->grant.privilege);
   table_list->register_want_access(SELECT_ACL);
 #endif
-  if (setup_fields(thd, 0, stmt->lex->value_list, MARK_COLUMNS_NONE, 0, 0))
+  if (setup_fields(thd, 0, stmt->lex->value_list, MARK_COLUMNS_NONE, 0, 0) ||
+      check_unique_table(thd, table_list))
     goto error;
   /* TODO: here we should send types of placeholders to the client. */
   DBUG_RETURN(0);
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 23e17b0..c8a7990 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -367,6 +367,9 @@ int mysql_update(THD *thd,
     DBUG_RETURN(1);				/* purecov: inspected */
   }
 
+  if (check_unique_table(thd, table_list))
+    DBUG_RETURN(TRUE);
+
   /* Apply the IN=>EXISTS transformation to all subqueries and optimize them. */
   if (select_lex->optimize_unflattened_subqueries(false))
     DBUG_RETURN(TRUE);
@@ -1036,19 +1039,30 @@ bool mysql_prepare_update(THD *thd, TABLE_LIST *table_list,
       setup_ftfuncs(select_lex))
     DBUG_RETURN(TRUE);
 
-  /* Check that we are not using table that we are updating in a sub select */
-  {
-    TABLE_LIST *duplicate;
-    if ((duplicate= unique_table(thd, table_list, table_list->next_global, 0)))
-    {
-      update_non_unique_table_error(table_list, "UPDATE", duplicate);
-      DBUG_RETURN(TRUE);
-    }
-  }
   select_lex->fix_prepare_information(thd, conds, &fake_conds);
   DBUG_RETURN(FALSE);
 }
 
+/**
+  Check that we are not using table that we are updating in a sub select
+
+  @param thd             Thread handle
+  @param table_list      List of table with first to check
+
+  @retval TRUE  Error
+  @retval FALSE OK
+*/
+bool check_unique_table(THD *thd, TABLE_LIST *table_list)
+{
+  TABLE_LIST *duplicate;
+  DBUG_ENTER("check_unique_table");
+  if ((duplicate= unique_table(thd, table_list, table_list->next_global, 0)))
+  {
+    update_non_unique_table_error(table_list, "UPDATE", duplicate);
+    DBUG_RETURN(TRUE);
+  }
+  DBUG_RETURN(FALSE);
+}
 
 /***************************************************************************
   Update multiple tables from join 
diff --git a/sql/sql_update.h b/sql/sql_update.h
index 64029c5..4c6f89d 100644
--- a/sql/sql_update.h
+++ b/sql/sql_update.h
@@ -27,6 +27,7 @@ typedef class st_select_lex_unit SELECT_LEX_UNIT;
 
 bool mysql_prepare_update(THD *thd, TABLE_LIST *table_list,
                           Item **conds, uint order_num, ORDER *order);
+bool check_unique_table(THD *thd, TABLE_LIST *table_list);
 int mysql_update(THD *thd,TABLE_LIST *tables,List<Item> &fields,
 		 List<Item> &values,COND *conds,
 		 uint order_num, ORDER *order, ha_rows limit,


More information about the commits mailing list