summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/create_or_replace.result20
-rw-r--r--mysql-test/t/create_or_replace.test25
-rw-r--r--sql/sql_base.cc46
-rw-r--r--sql/sql_base.h6
-rw-r--r--sql/sql_delete.cc2
-rw-r--r--sql/sql_insert.cc3
-rw-r--r--sql/sql_parse.cc2
7 files changed, 82 insertions, 22 deletions
diff --git a/mysql-test/r/create_or_replace.result b/mysql-test/r/create_or_replace.result
index a43dc2eaca4..0d171f9f87a 100644
--- a/mysql-test/r/create_or_replace.result
+++ b/mysql-test/r/create_or_replace.result
@@ -453,3 +453,23 @@ CREATE OR REPLACE TABLE t1 AS SELECT f1();
UNLOCK TABLES;
DROP FUNCTION f1;
DROP TABLE t1;
+#
+# MDEV-11129
+# CREATE OR REPLACE TABLE t1 AS SELECT spfunc() crashes if spfunc()
+# references t1
+#
+CREATE OR REPLACE TABLE t1(a INT);
+CREATE FUNCTION f1() RETURNS VARCHAR(16383)
+BEGIN
+INSERT INTO t1 VALUES(1);
+RETURN 'test';
+END;
+$$
+CREATE OR REPLACE TABLE t1 AS SELECT f1();
+ERROR HY000: Table 't1' is specified twice, both as a target for 'CREATE' and as a separate source for data
+LOCK TABLE t1 WRITE;
+CREATE OR REPLACE TABLE t1 AS SELECT f1();
+ERROR HY000: Table 't1' was not locked with LOCK TABLES
+UNLOCK TABLES;
+DROP FUNCTION f1;
+DROP TABLE t1;
diff --git a/mysql-test/t/create_or_replace.test b/mysql-test/t/create_or_replace.test
index b37417f39d0..5ebb3031be3 100644
--- a/mysql-test/t/create_or_replace.test
+++ b/mysql-test/t/create_or_replace.test
@@ -398,3 +398,28 @@ CREATE OR REPLACE TABLE t1 AS SELECT f1();
UNLOCK TABLES;
DROP FUNCTION f1;
DROP TABLE t1;
+
+--echo #
+--echo # MDEV-11129
+--echo # CREATE OR REPLACE TABLE t1 AS SELECT spfunc() crashes if spfunc()
+--echo # references t1
+--echo #
+
+CREATE OR REPLACE TABLE t1(a INT);
+DELIMITER $$;
+CREATE FUNCTION f1() RETURNS VARCHAR(16383)
+BEGIN
+ INSERT INTO t1 VALUES(1);
+ RETURN 'test';
+END;
+$$
+DELIMITER ;$$
+--error ER_UPDATE_TABLE_USED
+CREATE OR REPLACE TABLE t1 AS SELECT f1();
+LOCK TABLE t1 WRITE;
+--error ER_TABLE_NOT_LOCKED
+CREATE OR REPLACE TABLE t1 AS SELECT f1();
+UNLOCK TABLES;
+
+DROP FUNCTION f1;
+DROP TABLE t1;
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 554c8cffeaf..bdde123f18c 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -1278,7 +1278,8 @@ TABLE_LIST *find_table_in_list(TABLE_LIST *table,
@param thd thread handle
@param table table which should be checked
@param table_list list of tables
- @param check_alias whether to check tables' aliases
+ @param check_flag whether to check tables' aliases
+ Currently this is only used by INSERT
NOTE: to exclude derived tables from check we use following mechanism:
a) during derived table processing set THD::derived_tables_processing
@@ -1307,9 +1308,9 @@ TABLE_LIST *find_table_in_list(TABLE_LIST *table,
static
TABLE_LIST* find_dup_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list,
- bool check_alias)
+ uint check_flag)
{
- TABLE_LIST *res;
+ TABLE_LIST *res= 0;
const char *d_name, *t_name, *t_alias;
DBUG_ENTER("find_dup_table");
DBUG_PRINT("enter", ("table alias: %s", table->alias));
@@ -1345,17 +1346,15 @@ TABLE_LIST* find_dup_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list,
retry:
DBUG_PRINT("info", ("real table: %s.%s", d_name, t_name));
- for (TABLE_LIST *tl= table_list;;)
+ for (TABLE_LIST *tl= table_list; tl ; tl= tl->next_global, res= 0)
{
- if (tl &&
- tl->select_lex && tl->select_lex->master_unit() &&
+ if (tl->select_lex && tl->select_lex->master_unit() &&
tl->select_lex->master_unit()->executed)
{
/*
There is no sense to check tables of already executed parts
of the query
*/
- tl= tl->next_global;
continue;
}
/*
@@ -1364,21 +1363,29 @@ retry:
*/
if (! (res= find_table_in_global_list(tl, d_name, t_name)))
break;
+ tl= res; // We can continue search after this table
/* Skip if same underlying table. */
if (res->table && (res->table == table->table))
- goto next;
+ continue;
+
+ if (check_flag & CHECK_DUP_FOR_CREATE)
+ DBUG_RETURN(res);
/* Skip if table alias does not match. */
- if (check_alias)
+ if (check_flag & CHECK_DUP_FOR_CREATE)
{
if (my_strcasecmp(table_alias_charset, t_alias, res->alias))
- goto next;
+ continue;
}
/*
- Skip if marked to be excluded (could be a derived table) or if
- entry is a prelocking placeholder.
+ If table is not excluded (could be a derived table) and table is not
+ a prelocking placeholder then we found either a duplicate entry
+ or a table that is part of a derived table (handled below).
+ Examples are:
+ INSERT INTO t1 SELECT * FROM t1;
+ INSERT INTO t1 SELECT * FROM view_containing_t1;
*/
if (res->select_lex &&
!res->select_lex->exclude_from_table_unique_test &&
@@ -1390,14 +1397,17 @@ retry:
processed in derived table or top select of multi-update/multi-delete
(exclude_from_table_unique_test) or prelocking placeholder.
*/
-next:
- tl= res->next_global;
DBUG_PRINT("info",
("found same copy of table or table which we should skip"));
}
if (res && res->belong_to_derived)
{
- /* Try to fix */
+ /*
+ We come here for queries of type:
+ INSERT INTO t1 (SELECT tmp.a FROM (select * FROM t1) as tmp);
+
+ Try to fix by materializing the derived table
+ */
TABLE_LIST *derived= res->belong_to_derived;
if (derived->is_merged_derived())
{
@@ -1429,7 +1439,7 @@ next:
TABLE_LIST*
unique_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list,
- bool check_alias)
+ uint check_flag)
{
TABLE_LIST *dup;
@@ -1443,12 +1453,12 @@ unique_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list,
for (child= table->next_global; child && child->parent_l == table;
child= child->next_global)
{
- if ((dup= find_dup_table(thd, child, child->next_global, check_alias)))
+ if ((dup= find_dup_table(thd, child, child->next_global, check_flag)))
break;
}
}
else
- dup= find_dup_table(thd, table, table_list, check_alias);
+ dup= find_dup_table(thd, table, table_list, check_flag);
return dup;
}
/*
diff --git a/sql/sql_base.h b/sql/sql_base.h
index 04f132ac936..af42a15ab29 100644
--- a/sql/sql_base.h
+++ b/sql/sql_base.h
@@ -61,6 +61,10 @@ enum find_item_error_report_type {REPORT_ALL_ERRORS, REPORT_EXCEPT_NOT_FOUND,
IGNORE_ERRORS, REPORT_EXCEPT_NON_UNIQUE,
IGNORE_EXCEPT_NON_UNIQUE};
+/* Flag bits for unique_table() */
+#define CHECK_DUP_ALLOW_DIFFERENT_ALIAS 1
+#define CHECK_DUP_FOR_CREATE 2
+
uint create_tmp_table_def_key(THD *thd, char *key, const char *db,
const char *table_name);
uint get_table_def_key(const TABLE_LIST *table_list, const char **key);
@@ -254,7 +258,7 @@ void kill_delayed_threads_for_table(TABLE_SHARE *share);
void close_thread_table(THD *thd, TABLE **table_ptr);
bool close_temporary_tables(THD *thd);
TABLE_LIST *unique_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list,
- bool check_alias);
+ uint check_flag);
int drop_temporary_table(THD *thd, TABLE *table, bool *is_trans);
void close_temporary_table(THD *thd, TABLE *table, bool free_share,
bool delete_table);
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index 0780f418c80..609e5472ba6 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -903,7 +903,7 @@ multi_delete::initialize_tables(JOIN *join)
TABLE_LIST *tbl= walk->correspondent_table->find_table_for_update();
tables_to_delete_from|= tbl->table->map;
if (delete_while_scanning &&
- unique_table(thd, tbl, join->tables_list, false))
+ unique_table(thd, tbl, join->tables_list, 0))
{
/*
If the table we are going to delete from appears
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index 6036bbeb8b1..8b03c722898 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -1483,7 +1483,8 @@ bool mysql_prepare_insert(THD *thd, TABLE_LIST *table_list,
{
Item *fake_conds= 0;
TABLE_LIST *duplicate;
- if ((duplicate= unique_table(thd, table_list, table_list->next_global, 1)))
+ if ((duplicate= unique_table(thd, table_list, table_list->next_global,
+ CHECK_DUP_ALLOW_DIFFERENT_ALIAS)))
{
update_non_unique_table_error(table_list, "INSERT", duplicate);
DBUG_RETURN(TRUE);
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 87edf93bb4a..1162aa901d1 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -2970,7 +2970,7 @@ case SQLCOM_PREPARE:
TABLE_LIST *duplicate;
if ((duplicate= unique_table(thd, lex->query_tables,
lex->query_tables->next_global,
- 0)))
+ CHECK_DUP_FOR_CREATE)))
{
update_non_unique_table_error(lex->query_tables, "CREATE",
duplicate);
View Lorry Controller Status