summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/lowercase_view.result14
-rw-r--r--mysql-test/r/view.result12
-rw-r--r--mysql-test/t/lowercase_view.test24
-rw-r--r--mysql-test/t/view.test18
-rw-r--r--sql/mysql_priv.h1
-rw-r--r--sql/sql_base.cc72
-rw-r--r--sql/sql_delete.cc3
-rw-r--r--sql/sql_insert.cc3
-rw-r--r--sql/sql_parse.cc41
-rw-r--r--sql/sql_update.cc5
-rw-r--r--sql/sql_view.cc2
-rw-r--r--sql/table.h8
12 files changed, 171 insertions, 32 deletions
diff --git a/mysql-test/r/lowercase_view.result b/mysql-test/r/lowercase_view.result
index 7c5741fe06a..51f7dc758eb 100644
--- a/mysql-test/r/lowercase_view.result
+++ b/mysql-test/r/lowercase_view.result
@@ -1,3 +1,5 @@
+drop table if exists t1Aa,t2Aa,v1Aa,v2Aa;
+drop view if exists t1Aa,t2Aa,v1Aa,v2Aa;
drop database if exists MySQLTest;
create database MySQLTest;
use MySQLTest;
@@ -8,3 +10,15 @@ Table Create Table
vie CREATE VIEW `mysqltest`.`vie` AS select `mysqltest`.`tab`.`Field` AS `Field` from `mysqltest`.`tab`
drop database MySQLTest;
use test;
+create table t1Aa (col1 int);
+create table t2Aa (col1 int);
+create view v1Aa as select * from t1Aa;
+create view v2Aa as select * from v1Aa;
+update v2aA set col1 = (select max(col1) from v1aA);
+ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause
+delete from v2aA where col1 = (select max(col1) from v1aA);
+ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause
+insert into v2aA values ((select max(col1) from v1aA));
+ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause
+drop view v2Aa,v1Aa;
+drop table t1Aa,t2Aa;
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 35b76a8ab4b..7b95966937e 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -1270,3 +1270,15 @@ s1
7
drop view v1;
drop table t1;
+create table t1 (col1 int);
+create table t2 (col1 int);
+create view v1 as select * from t1;
+create view v2 as select * from v1;
+update v2 set col1 = (select max(col1) from v1);
+ERROR HY000: You can't specify target table 'v2' for update in FROM clause
+delete from v2 where col1 = (select max(col1) from v1);
+ERROR HY000: You can't specify target table 'v2' for update in FROM clause
+insert into v2 values ((select max(col1) from v1));
+ERROR HY000: You can't specify target table 'v2' for update in FROM clause
+drop view v2,v1;
+drop table t1,t2;
diff --git a/mysql-test/t/lowercase_view.test b/mysql-test/t/lowercase_view.test
index 5b1be072c69..2a2757650ae 100644
--- a/mysql-test/t/lowercase_view.test
+++ b/mysql-test/t/lowercase_view.test
@@ -1,8 +1,12 @@
--disable_warnings
+drop table if exists t1Aa,t2Aa,v1Aa,v2Aa;
+drop view if exists t1Aa,t2Aa,v1Aa,v2Aa;
drop database if exists MySQLTest;
--enable_warnings
-
+#
+# different cases in VIEW
+#
create database MySQLTest;
use MySQLTest;
create table TaB (Field int);
@@ -10,3 +14,21 @@ create view ViE as select * from TAb;
show create table VIe;
drop database MySQLTest;
use test;
+
+#
+# test of updating and fetching from the same table check
+#
+create table t1Aa (col1 int);
+create table t2Aa (col1 int);
+create view v1Aa as select * from t1Aa;
+create view v2Aa as select * from v1Aa;
+-- error 1093
+update v2aA set col1 = (select max(col1) from v1aA);
+#update v2aA,t2aA set v2aA.col1 = (select max(col1) from v1aA) where v2aA.col1 = t2aA.col1;
+-- error 1093
+delete from v2aA where col1 = (select max(col1) from v1aA);
+#delete v2aA from v2aA,t2aA where (select max(col1) from v1aA) > 0 and v2aA.col1 = t2aA.col1;
+-- error 1093
+insert into v2aA values ((select max(col1) from v1aA));
+drop view v2Aa,v1Aa;
+drop table t1Aa,t2Aa;
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 9464e291e05..de9a49f479d 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -1230,3 +1230,21 @@ insert into v1 values (1) on duplicate key update s1 = 7;
select * from t1;
drop view v1;
drop table t1;
+
+#
+# test of updating and fetching from the same table check
+#
+create table t1 (col1 int);
+create table t2 (col1 int);
+create view v1 as select * from t1;
+create view v2 as select * from v1;
+-- error 1093
+update v2 set col1 = (select max(col1) from v1);
+#update v2,t2 set v2.col1 = (select max(col1) from v1) where v2.col1 = t2.col1;
+-- error 1093
+delete from v2 where col1 = (select max(col1) from v1);
+#delete v2 from v2,t2 where (select max(col1) from v1) > 0 and v2.col1 = t2.col1;
+-- error 1093
+insert into v2 values ((select max(col1) from v1));
+drop view v2,v1;
+drop table t1,t2;
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index a100aa0cd3a..8e9f78e4f0f 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -772,6 +772,7 @@ TABLE_LIST *find_table_in_list(TABLE_LIST *table,
uint offset_to_list,
const char *db_name,
const char *table_name);
+bool unique_table(TABLE_LIST *table, TABLE_LIST *table_list);
TABLE **find_temporary_table(THD *thd, const char *db, const char *table_name);
bool close_temporary_table(THD *thd, const char *db, const char *table_name);
void close_temporary(TABLE *table, bool delete_table=1);
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 5fcfe945f27..1b5ac788929 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -574,16 +574,80 @@ TABLE_LIST *find_table_in_list(TABLE_LIST *table,
const char *db_name,
const char *table_name)
{
- for (; table; table= *(TABLE_LIST **) ((char*) table + offset))
+ if (lower_case_table_names)
{
- if (!strcmp(table->db, db_name) &&
- !strcmp(table->real_name, table_name))
- break;
+ for (; table; table= *(TABLE_LIST **) ((char*) table + offset))
+ {
+ if ((!strcmp(table->db, db_name) &&
+ !strcmp(table->real_name, table_name)) ||
+ (table->view &&
+ !my_strcasecmp(table_alias_charset,
+ table->table->table_cache_key, db_name) &&
+ !my_strcasecmp(table_alias_charset,
+ table->table->table_name, table_name)))
+ break;
+ }
+ }
+ else
+ {
+ for (; table; table= *(TABLE_LIST **) ((char*) table + offset))
+ {
+ if ((!strcmp(table->db, db_name) &&
+ !strcmp(table->real_name, table_name)) ||
+ (table->view &&
+ !strcmp(table->table->table_cache_key, db_name) &&
+ !strcmp(table->table->table_name, table_name)))
+ break;
+ }
}
return table;
}
+/*
+ Test that table is unique
+
+ SYNOPSIS
+ unique_table()
+ table table which should be chaked
+ table_list list of tables
+
+ RETURN
+ TRUE test failed
+ FALSE table is unique
+*/
+
+bool unique_table(TABLE_LIST *table, TABLE_LIST *table_list)
+{
+ char d_name_buff[MAX_ALIAS_NAME], t_name_buff[MAX_ALIAS_NAME];
+ const char *d_name= table->db, *t_name= table->real_name;
+ if (table->view)
+ {
+ /* it is view and table opened */
+ if (lower_case_table_names)
+ {
+ strmov(t_name_buff, table->table->table_name);
+ my_casedn_str(files_charset_info, t_name_buff);
+ t_name= t_name_buff;
+ strmov(d_name_buff, table->table->table_cache_key);
+ my_casedn_str(files_charset_info, d_name_buff);
+ d_name= d_name_buff;
+ }
+ else
+ {
+ d_name= table->table->table_cache_key;
+ t_name= table->table->table_name;
+ }
+ if (d_name == 0)
+ {
+ /* it's temporary table */
+ return FALSE;
+ }
+ }
+ return find_table_in_global_list(table_list, d_name, t_name);
+}
+
+
TABLE **find_temporary_table(THD *thd, const char *db, const char *table_name)
{
char key[MAX_DBKEY_LENGTH];
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index 089c0c00c3b..291504dae88 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -282,8 +282,7 @@ int mysql_prepare_delete(THD *thd, TABLE_LIST *table_list, Item **conds)
my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias, "DELETE");
DBUG_RETURN(-1);
}
- if (find_table_in_global_list(table_list->next_global,
- table_list->db, table_list->real_name))
+ if (unique_table(table_list, table_list->next_independent()))
{
my_error(ER_UPDATE_TABLE_USED, MYF(0), table_list->real_name);
DBUG_RETURN(-1);
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index 857c500f200..71d8efba8cb 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -599,8 +599,7 @@ int mysql_prepare_insert(THD *thd, TABLE_LIST *table_list, TABLE *table,
setup_fields(thd, 0, table_list, update_values, 0, 0, 0))))
DBUG_RETURN(-1);
- if (find_table_in_global_list(table_list->next_global,
- table_list->db, table_list->real_name))
+ if (unique_table(table_list, table_list->next_independent()))
{
my_error(ER_UPDATE_TABLE_USED, MYF(0), table_list->real_name);
DBUG_RETURN(-1);
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 0bfaf940f0e..d3a2d0639a1 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -2393,17 +2393,7 @@ mysql_execute_command(THD *thd)
if (select_lex->item_list.elements) // With select
{
select_result *result;
- /*
- Is table which we are changing used somewhere in other parts
- of query
- */
- if (!(lex->create_info.options & HA_LEX_CREATE_TMP_TABLE) &&
- find_table_in_global_list(select_tables, create_table->db,
- create_table->real_name))
- {
- net_printf(thd, ER_UPDATE_TABLE_USED, create_table->real_name);
- goto create_error;
- }
+
if (select_tables &&
check_table_access(thd, SELECT_ACL, select_tables, 0))
goto create_error; // Error message is given
@@ -2412,6 +2402,17 @@ mysql_execute_command(THD *thd)
if (!(res= open_and_lock_tables(thd, select_tables)))
{
+ /*
+ Is table which we are changing used somewhere in other parts
+ of query
+ */
+ if (!(lex->create_info.options & HA_LEX_CREATE_TMP_TABLE) &&
+ unique_table(create_table, select_tables))
+ {
+ net_printf(thd, ER_UPDATE_TABLE_USED, create_table->real_name);
+ goto create_error;
+ }
+
if ((result= new select_create(create_table,
&lex->create_info,
lex->create_list,
@@ -2767,16 +2768,18 @@ unsent_create_error:
select_result *result;
unit->set_limit(select_lex, select_lex);
- // is table which we are changing used somewhere in other parts of query
- if (find_table_in_global_list(all_tables->next_global,
- first_table->db, first_table->real_name))
- {
- /* Using same table for INSERT and SELECT */
- select_lex->options |= OPTION_BUFFER_RESULT;
- }
-
if (!(res= open_and_lock_tables(thd, all_tables)))
{
+ /*
+ Is table which we are changing used somewhere in other parts of
+ query
+ */
+ if (unique_table(first_table, all_tables->next_independent()))
+ {
+ /* Using same table for INSERT and SELECT */
+ select_lex->options |= OPTION_BUFFER_RESULT;
+ }
+
if ((res= mysql_insert_select_prepare(thd)))
break;
if ((result= new select_insert(first_table, first_table->table,
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 9d7134aee84..be5da76f380 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -496,8 +496,7 @@ int mysql_prepare_update(THD *thd, TABLE_LIST *table_list,
DBUG_RETURN(-1);
/* Check that we are not using table that we are updating in a sub select */
- if (find_table_in_global_list(table_list->next_global,
- table_list->db, table_list->real_name))
+ if (unique_table(table_list, table_list->next_independent()))
{
my_error(ER_UPDATE_TABLE_USED, MYF(0), table_list->real_name);
DBUG_RETURN(-1);
@@ -788,7 +787,7 @@ int multi_update::prepare(List<Item> &not_used_values,
{
TABLE *table=table_ref->table;
if (!(tables_to_update & table->map) &&
- find_table_in_global_list(update_tables, table_ref->db,
+ find_table_in_local_list(update_tables, table_ref->db,
table_ref->real_name))
table->no_cache= 1; // Disable row cache
}
diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index 2b1971907b3..cddeca33ebf 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -618,7 +618,7 @@ mysql_make_view(File_parser *parser, TABLE_LIST *table)
if (lex->spfuns.array.buffer)
hash_free(&lex->spfuns);
- old_next= table->next_global;
+ old_next= table->old_next= table->next_global;
if ((table->next_global= lex->query_tables))
table->next_global->prev_global= &table->next_global;
diff --git a/sql/table.h b/sql/table.h
index b7cabe21638..acb60b44282 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -217,6 +217,8 @@ typedef struct st_table_list
st_table_list *ancestor;
/* most upper view this table belongs to */
st_table_list *belong_to_view;
+ /* next_global before adding VIEW tables */
+ st_table_list *old_next;
Item *where; /* VIEW WHERE clause condition */
LEX_STRING query; /* text of (CRETE/SELECT) statement */
LEX_STRING md5; /* md5 of query tesxt */
@@ -260,6 +262,12 @@ typedef struct st_table_list
bool setup_ancestor(THD *thd, Item **conds);
bool placeholder() {return derived || view; }
void print(THD *thd, String *str);
+ inline st_table_list *next_independent()
+ {
+ if (view)
+ return old_next;
+ return next_global;
+ }
} TABLE_LIST;
class Item;
View Lorry Controller Status