Below is the list of changes that have just been committed into a local
5.0 repository of bell. When bell does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet
1.1845 05/03/28 15:13:31 bell@stripped +15 -0
fixed mechanism of detection selection from table wich we update
(BUG##9398, BUG#8703)
fixed wrong join view detection in multi-delete which lead to server crash
sql/sql_update.cc
1.153 05/03/28 15:12:29 bell@stripped +13 -10
fixed detection of selection from table which update for multiupdate
sql/sql_select.cc
1.306 05/03/28 15:12:29 bell@stripped +8 -1
added detection os SELECTs processed inside derived tables
sql/sql_prepare.cc
1.109 05/03/28 15:12:29 bell@stripped +3 -0
added detection os SELECTs processed inside derived tables (reset it for reusing in PS/SP)
sql/sql_parse.cc
1.432 05/03/28 15:12:29 bell@stripped +0 -10
removed wrong test of join view (for multidelete in can be not only first table)
sql/sql_lex.h
1.171 05/03/28 15:12:29 bell@stripped +2 -3
added detection os SELECTs processed inside derived tables
removed old mechanism of multidelete/multiupdate table duplication detection (which can't work with views)
sql/sql_lex.cc
1.141 05/03/28 15:12:29 bell@stripped +1 -72
added detection os SELECTs processed inside derived tables
removed old mechanism of multidelete/multiupdate table duplication detection (which can't work with views)
sql/sql_derived.cc
1.70 05/03/28 15:12:29 bell@stripped +6 -3
added derived table procession detection
sql/sql_delete.cc
1.143 05/03/28 15:12:29 bell@stripped +23 -16
fixed detection of selection from table which update for multidelete
sql/sql_class.h
1.228 05/03/28 15:12:29 bell@stripped +2 -0
added derived table procession detection
sql/sql_class.cc
1.173 05/03/28 15:12:29 bell@stripped +2 -1
added derived table procession detection
sql/sql_base.cc
1.231 05/03/28 15:12:28 bell@stripped +33 -31
changed procedure of finding tables
mysql-test/t/view.test
1.63 05/03/28 15:12:28 bell@stripped +135 -3
added new tests of updation and selection from the same table
added test of multidelete command over join view which lead to server crash
test suite from bugs #9398 and #8703
mysql-test/t/lowercase_view.test
1.5 05/03/28 15:12:28 bell@stripped +99 -8
added new tests of updation and selection from the same table
mysql-test/r/view.result
1.72 05/03/28 15:12:28 bell@stripped +135 -1
added new tests of updation and selection from the same table
added test of multidelete command over join view which lead to server crash
test suite from bugs #9398 and #8703
mysql-test/r/lowercase_view.result
1.7 05/03/28 15:12:28 bell@stripped +99 -6
added new tests of updation and selection from the same table
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: bell
# Host: sanja.is.com.ua
# Root: /home/bell/mysql/bk/work-5.0
--- 1.230/sql/sql_base.cc Wed Mar 16 16:10:55 2005
+++ 1.231/sql/sql_base.cc Mon Mar 28 15:12:28 2005
@@ -680,33 +680,12 @@
const char *db_name,
const char *table_name)
{
- if (lower_case_table_names)
+ for (; table; table= *(TABLE_LIST **) ((char*) table + offset))
{
- for (; table; table= *(TABLE_LIST **) ((char*) table + offset))
- {
- if ((table->table == 0 || table->table->s->tmp_table == NO_TMP_TABLE) &&
- ((!strcmp(table->db, db_name) &&
- !strcmp(table->table_name, table_name)) ||
- (table->view &&
- !my_strcasecmp(table_alias_charset,
- table->db, db_name) &&
- !my_strcasecmp(table_alias_charset,
- table->table->alias, table_name))))
- break;
- }
- }
- else
- {
- for (; table; table= *(TABLE_LIST **) ((char*) table + offset))
- {
- if ((table->table == 0 || table->table->s->tmp_table == NO_TMP_TABLE) &&
- ((!strcmp(table->db, db_name) &&
- !strcmp(table->table_name, table_name)) ||
- (table->view &&
- !strcmp(table->table->s->db, db_name) &&
- !strcmp(table->table->alias, table_name))))
- break;
- }
+ if ((table->table == 0 || table->table->s->tmp_table == NO_TMP_TABLE) &&
+ strcmp(table->db, db_name) == 0 &&
+ strcmp(table->table_name, table_name) == 0)
+ break;
}
return table;
}
@@ -717,8 +696,25 @@
SYNOPSIS
unique_table()
- table table which should be chaked
- table_list list of tables
+ table table which should be chaked
+ table_list list of tables
+
+ NOTE: to exclude derived tables from check we use following mechanism:
+ a) during derived table processing set THD::derived_tables_processing
+ b) JOIN::prepare set SELECT::exclude_from_table_unique_test if
+ THD::derived_tables_processing set. (we can't use JOIN::execute
+ because for PS we perform only JOIN::prepare, but we can't set this
+ flag in JOIN::prepare if we are not sure that we are in derived table
+ processing loop, because multi-update call fix_fields() for some its
+ items (which mean JOIN::prepare for subqueries) before unique_table
+ call to detect which tables should be locked for write).
+ c) unique_table skip all tables which belong to SELECT with
+ SELECT::exclude_from_table_unique_test set.
+ Also SELECT::exclude_from_table_unique_test used to exclude from check
+ tables of main SELECT of multi-delete and multi-update
+
+ TODO: when we will have table/view change detection we can do this check
+ only once for PS/SP
RETURN
found duplicate
@@ -758,11 +754,17 @@
for(;;)
{
if (!(res= find_table_in_global_list(table_list, d_name, t_name)) ||
- !res->table || res->table != table->table)
+ (!res->table || res->table != table->table) &&
+ (res->select_lex && !res->select_lex->exclude_from_table_unique_test))
break;
- /* if we found entry of this table try again. */
+ /*
+ If we found entry of this table or or table of SELECT which already
+ processed in derived table or top select of multi-update/multi-delete
+ (exclude_from_table_unique_test).
+ */
table_list= res->next_global;
- DBUG_PRINT("info", ("found same copy of table"));
+ DBUG_PRINT("info",
+ ("found same copy of table or table which we should skip"));
}
DBUG_RETURN(res);
}
--- 1.172/sql/sql_class.cc Wed Mar 23 22:39:10 2005
+++ 1.173/sql/sql_class.cc Mon Mar 28 15:12:29 2005
@@ -161,7 +161,8 @@
:user_time(0), global_read_lock(0), is_fatal_error(0),
rand_used(0), time_zone_used(0),
last_insert_id_used(0), insert_id_used(0), clear_next_insert_id(0),
- in_lock_tables(0), bootstrap(0), spcont(NULL)
+ in_lock_tables(0), bootstrap(0), derived_tables_processing(FALSE),
+ spcont(NULL)
{
current_arena= this;
#ifndef DBUG_OFF
--- 1.227/sql/sql_class.h Sat Mar 19 02:12:22 2005
+++ 1.228/sql/sql_class.h Mon Mar 28 15:12:29 2005
@@ -1207,6 +1207,8 @@
bool no_trans_update, abort_on_warning;
bool got_warning; /* Set on call to push_warning() */
bool no_warnings_for_error; /* no warnings on call to my_error() */
+ /* set during loop of derived table processing */
+ bool derived_tables_processing;
longlong row_count_func; /* For the ROW_COUNT() function */
sp_rcontext *spcont; // SP runtime context
sp_cache *sp_proc_cache;
--- 1.142/sql/sql_delete.cc Wed Mar 16 16:10:55 2005
+++ 1.143/sql/sql_delete.cc Mon Mar 28 15:12:29 2005
@@ -356,12 +356,28 @@
&lex->select_lex.leaf_tables, FALSE, FALSE))
DBUG_RETURN(TRUE);
+
+ /*
+ Multi-delete can't be constructed over-union => we always have
+ single SELECT on top and have to check underlying SELECTs of it
+ */
+ lex->select_lex.exclude_from_table_unique_test= TRUE;
/* Fix tables-to-be-deleted-from list to point at opened tables */
for (target_tbl= (TABLE_LIST*) aux_tables;
target_tbl;
target_tbl= target_tbl->next_local)
{
- target_tbl->table= target_tbl->correspondent_table->table;
+ if (!(target_tbl->table= target_tbl->correspondent_table->table))
+ {
+ DBUG_ASSERT(target_tbl->correspondent_table->view &&
+ target_tbl->correspondent_table->ancestor &&
+ target_tbl->correspondent_table->ancestor->next_local);
+ my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0),
+ target_tbl->correspondent_table->view_db.str,
+ target_tbl->correspondent_table->view_name.str);
+ DBUG_RETURN(TRUE);
+ }
+
if (!target_tbl->correspondent_table->updatable ||
check_key_in_view(thd, target_tbl->correspondent_table))
{
@@ -370,23 +386,14 @@
DBUG_RETURN(TRUE);
}
/*
- Check are deleted table used somewhere inside subqueries.
-
- Multi-delete can't be constructed over-union => we always have
- single SELECT on top and have to check underlying SELECTs of it
+ Check that table from which we delete is not used somewhere
+ inside subqueries/view.
*/
- for (SELECT_LEX_UNIT *un= lex->select_lex.first_inner_unit();
- un;
- un= un->next_unit())
+ if (unique_table(target_tbl->correspondent_table, lex->query_tables))
{
- if (un->first_select()->linkage != DERIVED_TABLE_TYPE &&
- un->check_updateable(target_tbl->correspondent_table->db,
- target_tbl->correspondent_table->table_name))
- {
- my_error(ER_UPDATE_TABLE_USED, MYF(0),
- target_tbl->correspondent_table->table_name);
- DBUG_RETURN(TRUE);
- }
+ my_error(ER_UPDATE_TABLE_USED, MYF(0),
+ target_tbl->correspondent_table->table_name);
+ DBUG_RETURN(TRUE);
}
}
DBUG_RETURN(FALSE);
--- 1.140/sql/sql_lex.cc Fri Mar 4 16:01:01 2005
+++ 1.141/sql/sql_lex.cc Mon Mar 28 15:12:29 2005
@@ -1112,7 +1112,7 @@
first_execution= 1;
first_cond_optimization= 1;
parsing_place= NO_MATTER;
- no_wrap_view_item= 0;
+ exclude_from_table_unique_test= no_wrap_view_item= FALSE;
link_next= 0;
}
@@ -1490,77 +1490,6 @@
(item_list.elements +
select_n_having_items +
order_group_num)* 5)) == 0;
-}
-
-
-/*
- Find db.table which will be updated in this unit
-
- SYNOPSIS
- st_select_lex_unit::check_updateable()
- db - data base name
- table - real table name
-
- RETURN
- 1 - found
- 0 - OK (table did not found)
-*/
-
-bool st_select_lex_unit::check_updateable(char *db, char *table)
-{
- for (SELECT_LEX *sl= first_select(); sl; sl= sl->next_select())
- if (sl->check_updateable(db, table))
- return 1;
- return 0;
-}
-
-
-/*
- Find db.table which will be updated in this select and
- underlying ones (except derived tables)
-
- SYNOPSIS
- st_select_lex::check_updateable()
- db - data base name
- table - real table name
-
- RETURN
- 1 - found
- 0 - OK (table did not found)
-*/
-
-bool st_select_lex::check_updateable(char *db, char *table)
-{
- if (find_table_in_local_list(get_table_list(), db, table))
- return 1;
-
- return check_updateable_in_subqueries(db, table);
-}
-
-/*
- Find db.table which will be updated in underlying subqueries
-
- SYNOPSIS
- st_select_lex::check_updateable_in_subqueries()
- db - data base name
- table - real table name
-
- RETURN
- 1 - found
- 0 - OK (table did not found)
-*/
-
-bool st_select_lex::check_updateable_in_subqueries(char *db, char *table)
-{
- for (SELECT_LEX_UNIT *un= first_inner_unit();
- un;
- un= un->next_unit())
- {
- if (un->first_select()->linkage != DERIVED_TABLE_TYPE &&
- un->check_updateable(db, table))
- return 1;
- }
- return 0;
}
--- 1.170/sql/sql_lex.h Sat Mar 19 02:12:22 2005
+++ 1.171/sql/sql_lex.h Mon Mar 28 15:12:29 2005
@@ -442,7 +442,6 @@
inline void unclean() { cleaned= 0; }
void reinit_exec_mechanism();
- bool check_updateable(char *db, char *table);
void print(String *str);
ulong init_prepare_fake_select_lex(THD *thd);
@@ -525,6 +524,8 @@
bool first_cond_optimization;
/* do not wrap view fields with Item_ref */
bool no_wrap_view_item;
+ /* exclude this select from check of unique_table() */
+ bool exclude_from_table_unique_test;
/*
SELECT for SELECT command st_select_lex. Used to privent scaning
@@ -615,8 +616,6 @@
init_select();
}
bool setup_ref_array(THD *thd, uint order_group_num);
- bool check_updateable(char *db, char *table);
- bool check_updateable_in_subqueries(char *db, char *table);
void print(THD *thd, String *str);
static void print_order(String *str, ORDER *order);
void print_limit(THD *thd, String *str);
--- 1.431/sql/sql_parse.cc Thu Mar 24 19:15:54 2005
+++ 1.432/sql/sql_parse.cc Mon Mar 28 15:12:29 2005
@@ -3234,16 +3234,6 @@
if ((res= open_and_lock_tables(thd, all_tables)))
break;
- if (!first_table->table)
- {
- DBUG_ASSERT(first_table->view &&
- first_table->ancestor && first_table->ancestor->next_local);
- my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0),
- first_table->view_db.str, first_table->view_name.str);
- res= FALSE;
- break;
- }
-
if ((res= mysql_multi_delete_prepare(thd)))
goto error;
--- 1.305/sql/sql_select.cc Fri Mar 25 12:33:18 2005
+++ 1.306/sql/sql_select.cc Mon Mar 28 15:12:29 2005
@@ -316,6 +316,13 @@
join_list= &select_lex->top_join_list;
union_part= (unit_arg->first_select()->next_select() != 0);
+ /*
+ If we have already executed SELECT, then it have not sense to prevent
+ its table from update (see unique_table())
+ */
+ if (thd->derived_tables_processing)
+ select_lex->exclude_from_table_unique_test= TRUE;
+
/* Check that all tables, fields, conds and order are ok */
if ((!(select_options & OPTION_SETUP_TABLES_DONE) &&
@@ -1157,7 +1164,7 @@
{
int tmp_error;
DBUG_ENTER("JOIN::exec");
-
+
error= 0;
if (procedure)
{
--- 1.152/sql/sql_update.cc Sat Mar 19 02:12:22 2005
+++ 1.153/sql/sql_update.cc Mon Mar 28 15:12:29 2005
@@ -691,16 +691,6 @@
DBUG_RETURN(TRUE);
}
- /*
- Multi-update can't be constructed over-union => we always have
- single SELECT on top and have to check underlying SELECTs of it
- */
- if (lex->select_lex.check_updateable_in_subqueries(tl->db,
- tl->table_name))
- {
- my_error(ER_UPDATE_TABLE_USED, MYF(0), tl->table_name);
- DBUG_RETURN(TRUE);
- }
DBUG_PRINT("info",("setting table `%s` for update", tl->alias));
tl->lock_type= lex->multi_lock_option;
tl->updating= 1;
@@ -781,6 +771,11 @@
DBUG_RETURN(TRUE);
}
+ /*
+ Check that we are not using table that we are updating, but we should
+ skip all tables of UPDATE SELECT itself
+ */
+ lex->select_lex.exclude_from_table_unique_test= TRUE;
/* We only need SELECT privilege for columns in the values list */
for (tl= leaves; tl; tl= tl->next_leaf)
{
@@ -794,11 +789,19 @@
}
DBUG_PRINT("info", ("table: %s want_privilege: %u", tl->alias,
(uint) table->grant.want_privilege));
+ if (tl->lock_type != TL_READ &&
+ tl->lock_type != TL_READ_NO_INSERT &&
+ unique_table(tl, table_list))
+ {
+ my_error(ER_UPDATE_TABLE_USED, MYF(0), table_list->table_name);
+ DBUG_RETURN(TRUE);
+ }
}
if (thd->fill_derived_tables() &&
mysql_handle_derived(lex, &mysql_derived_filling))
DBUG_RETURN(TRUE);
+
DBUG_RETURN (FALSE);
}
--- 1.69/sql/sql_derived.cc Tue Feb 22 15:46:56 2005
+++ 1.70/sql/sql_derived.cc Mon Mar 28 15:12:29 2005
@@ -43,8 +43,10 @@
int
mysql_handle_derived(LEX *lex, int (*processor)(THD*, LEX*, TABLE_LIST*))
{
+ int res= 0;
if (lex->derived_tables)
{
+ lex->thd->derived_tables_processing= TRUE;
for (SELECT_LEX *sl= lex->all_selects_list;
sl;
sl= sl->next_select_in_list())
@@ -53,9 +55,8 @@
cursor;
cursor= cursor->next_local)
{
- int res;
if ((res= (*processor)(lex->thd, lex, cursor)))
- return res;
+ goto out;
}
if (lex->describe)
{
@@ -68,7 +69,9 @@
}
}
}
- return 0;
+out:
+ lex->thd->derived_tables_processing= FALSE;
+ return res;
}
--- 1.6/mysql-test/r/lowercase_view.result Wed Nov 24 19:48:25 2004
+++ 1.7/mysql-test/r/lowercase_view.result Mon Mar 28 15:12:28 2005
@@ -11,16 +11,109 @@
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);
+create table t2aA (col1 int);
+create view v1Aa as select * from t1aA;
+create view v2aA as select * from v1aA;
+create view v3Aa as select v2Aa.col1 from v2aA,t2Aa where v2Aa.col1 = t2aA.col1;
+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);
+update v2Aa set col1 = (select max(col1) from t1Aa);
ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause
+update v2aA set col1 = (select max(col1) from v2Aa);
+ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause
+update v2aA,t2Aa set v2Aa.col1 = (select max(col1) from v1aA) where v2aA.col1 = t2aA.col1;
+ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause
+update t1aA,t2Aa set t1Aa.col1 = (select max(col1) from v1Aa) where t1aA.col1 = t2aA.col1;
+ERROR HY000: You can't specify target table 't1aa' for update in FROM clause
+update v1aA,t2Aa set v1Aa.col1 = (select max(col1) from v1aA) where v1Aa.col1 = t2aA.col1;
+ERROR HY000: You can't specify target table 'v1aa' for update in FROM clause
+update t2Aa,v2Aa set v2aA.col1 = (select max(col1) from v1aA) where v2Aa.col1 = t2aA.col1;
+ERROR HY000: You can't specify target table 't2aa' for update in FROM clause
+update t2Aa,t1Aa set t1aA.col1 = (select max(col1) from v1Aa) where t1Aa.col1 = t2aA.col1;
+ERROR HY000: You can't specify target table 't2aa' for update in FROM clause
+update t2Aa,v1aA set v1Aa.col1 = (select max(col1) from v1aA) where v1Aa.col1 = t2aA.col1;
+ERROR HY000: You can't specify target table 't2aa' for update in FROM clause
+update v2aA,t2Aa set v2Aa.col1 = (select max(col1) from t1aA) where v2aA.col1 = t2aA.col1;
+ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause
+update t1Aa,t2Aa set t1aA.col1 = (select max(col1) from t1Aa) where t1aA.col1 = t2aA.col1;
+ERROR HY000: You can't specify target table 't1aa' for update in FROM clause
+update v1aA,t2Aa set v1Aa.col1 = (select max(col1) from t1Aa) where v1aA.col1 = t2aA.col1;
+ERROR HY000: You can't specify target table 'v1aa' for update in FROM clause
+update t2Aa,v2Aa set v2aA.col1 = (select max(col1) from t1aA) where v2Aa.col1 = t2aA.col1;
+ERROR HY000: You can't specify target table 't2aa' for update in FROM clause
+update t2Aa,t1Aa set t1aA.col1 = (select max(col1) from t1Aa) where t1aA.col1 = t2aA.col1;
+ERROR HY000: You can't specify target table 't2aa' for update in FROM clause
+update t2Aa,v1Aa set v1aA.col1 = (select max(col1) from t1Aa) where v1Aa.col1 = t2aA.col1;
+ERROR HY000: You can't specify target table 't2aa' for update in FROM clause
+update v2aA,t2Aa set v2Aa.col1 = (select max(col1) from v2aA) where v2Aa.col1 = t2aA.col1;
+ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause
+update t1aA,t2Aa set t1Aa.col1 = (select max(col1) from v2aA) where t1aA.col1 = t2aA.col1;
+ERROR HY000: You can't specify target table 't1aa' for update in FROM clause
+update v1aA,t2Aa set v1Aa.col1 = (select max(col1) from v2Aa) where v1aA.col1 = t2aA.col1;
+ERROR HY000: You can't specify target table 'v1aa' for update in FROM clause
+update t2Aa,v2aA set v2Aa.col1 = (select max(col1) from v2aA) where v2Aa.col1 = t2aA.col1;
+ERROR HY000: You can't specify target table 't2aa' for update in FROM clause
+update t2Aa,t1Aa set t1aA.col1 = (select max(col1) from v2aA) where t1Aa.col1 = t2aA.col1;
+ERROR HY000: You can't specify target table 't2aa' for update in FROM clause
+update t2Aa,v1Aa set v1aA.col1 = (select max(col1) from v2Aa) where v1Aa.col1 = t2aA.col1;
+ERROR HY000: You can't specify target table 't2aa' for update in FROM clause
+update v3aA set v3Aa.col1 = (select max(col1) from v1aA);
+ERROR HY000: You can't specify target table 'v3aa' for update in FROM clause
+update v3aA set v3Aa.col1 = (select max(col1) from t1aA);
+ERROR HY000: You can't specify target table 'v3aa' for update in FROM clause
+update v3aA set v3Aa.col1 = (select max(col1) from v2aA);
+ERROR HY000: You can't specify target table 'v3aa' for update in FROM clause
+update v3aA set v3Aa.col1 = (select max(col1) from v3aA);
+ERROR HY000: You can't specify target table 'v3aa' 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
+delete from v2aA where col1 = (select max(col1) from t1Aa);
+ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause
+delete from v2Aa where col1 = (select max(col1) from v2aA);
+ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause
+delete v2Aa from v2aA,t2Aa where (select max(col1) from v1aA) > 0 and v2Aa.col1 = t2aA.col1;
+ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause
+delete t1aA from t1Aa,t2Aa where (select max(col1) from v1Aa) > 0 and t1aA.col1 = t2aA.col1;
+ERROR HY000: You can't specify target table 't1aa' for update in FROM clause
+delete v1aA from v1Aa,t2Aa where (select max(col1) from v1aA) > 0 and v1Aa.col1 = t2aA.col1;
+ERROR HY000: You can't specify target table 'v1aa' for update in FROM clause
+delete v2aA from v2Aa,t2Aa where (select max(col1) from t1Aa) > 0 and v2aA.col1 = t2aA.col1;
+ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause
+delete t1aA from t1Aa,t2Aa where (select max(col1) from t1aA) > 0 and t1Aa.col1 = t2aA.col1;
+ERROR HY000: You can't specify target table 't1aa' for update in FROM clause
+delete v1aA from v1Aa,t2Aa where (select max(col1) from t1aA) > 0 and v1aA.col1 = t2aA.col1;
+ERROR HY000: You can't specify target table 'v1aa' for update in FROM clause
+delete v2Aa from v2aA,t2Aa where (select max(col1) from v2Aa) > 0 and v2aA.col1 = t2aA.col1;
+ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause
+delete t1Aa from t1aA,t2Aa where (select max(col1) from v2Aa) > 0 and t1Aa.col1 = t2aA.col1;
+ERROR HY000: You can't specify target table 't1aa' for update in FROM clause
+delete v1Aa from v1aA,t2Aa where (select max(col1) from v2aA) > 0 and v1Aa.col1 = t2aA.col1;
+ERROR HY000: You can't specify target table 'v1aa' 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
+insert into t1aA values ((select max(col1) from v1Aa));
+ERROR HY000: You can't specify target table 't1aa' 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;
+insert into v2Aa values ((select max(col1) from t1Aa));
+ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause
+insert into t1aA values ((select max(col1) from t1Aa));
+ERROR HY000: You can't specify target table 't1aa' for update in FROM clause
+insert into v2aA values ((select max(col1) from t1aA));
+ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause
+insert into v2Aa values ((select max(col1) from v2aA));
+ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause
+insert into t1Aa values ((select max(col1) from v2Aa));
+ERROR HY000: You can't specify target table 't1aa' for update in FROM clause
+insert into v2aA values ((select max(col1) from v2Aa));
+ERROR HY000: You can't specify target table 'v2aa' for update in FROM clause
+insert into v3Aa (col1) values ((select max(col1) from v1Aa));
+ERROR HY000: You can't specify target table 'v3aa' for update in FROM clause
+insert into v3aA (col1) values ((select max(col1) from t1aA));
+ERROR HY000: You can't specify target table 'v3aa' for update in FROM clause
+insert into v3Aa (col1) values ((select max(col1) from v2aA));
+ERROR HY000: You can't specify target table 'v3aa' for update in FROM clause
+drop view v3aA,v2Aa,v1aA;
drop table t1Aa,t2Aa;
create table t1Aa (col1 int);
create view v1Aa as select col1 from t1Aa as AaA;
--- 1.71/mysql-test/r/view.result Wed Mar 23 10:32:40 2005
+++ 1.72/mysql-test/r/view.result Mon Mar 28 15:12:28 2005
@@ -1176,13 +1176,121 @@
create table t2 (col1 int);
create view v1 as select * from t1;
create view v2 as select * from v1;
+create view v3 as select v2.col1 from v2,t2 where v2.col1 = t2.col1;
update v2 set col1 = (select max(col1) from v1);
ERROR HY000: You can't specify target table 'v2' for update in FROM clause
+update v2 set col1 = (select max(col1) from t1);
+ERROR HY000: You can't specify target table 'v2' for update in FROM clause
+update v2 set col1 = (select max(col1) from v2);
+ERROR HY000: You can't specify target table 'v2' for update in FROM clause
+update v2,t2 set v2.col1 = (select max(col1) from v1) where v2.col1 = t2.col1;
+ERROR HY000: You can't specify target table 'v2' for update in FROM clause
+update t1,t2 set t1.col1 = (select max(col1) from v1) where t1.col1 = t2.col1;
+ERROR HY000: You can't specify target table 't1' for update in FROM clause
+update v1,t2 set v1.col1 = (select max(col1) from v1) where v1.col1 = t2.col1;
+ERROR HY000: You can't specify target table 'v1' for update in FROM clause
+update t2,v2 set v2.col1 = (select max(col1) from v1) where v2.col1 = t2.col1;
+ERROR HY000: You can't specify target table 't2' for update in FROM clause
+update t2,t1 set t1.col1 = (select max(col1) from v1) where t1.col1 = t2.col1;
+ERROR HY000: You can't specify target table 't2' for update in FROM clause
+update t2,v1 set v1.col1 = (select max(col1) from v1) where v1.col1 = t2.col1;
+ERROR HY000: You can't specify target table 't2' for update in FROM clause
+update v2,t2 set v2.col1 = (select max(col1) from t1) where v2.col1 = t2.col1;
+ERROR HY000: You can't specify target table 'v2' for update in FROM clause
+update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1;
+ERROR HY000: You can't specify target table 't1' for update in FROM clause
+update v1,t2 set v1.col1 = (select max(col1) from t1) where v1.col1 = t2.col1;
+ERROR HY000: You can't specify target table 'v1' for update in FROM clause
+update t2,v2 set v2.col1 = (select max(col1) from t1) where v2.col1 = t2.col1;
+ERROR HY000: You can't specify target table 't2' for update in FROM clause
+update t2,t1 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1;
+ERROR HY000: You can't specify target table 't2' for update in FROM clause
+update t2,v1 set v1.col1 = (select max(col1) from t1) where v1.col1 = t2.col1;
+ERROR HY000: You can't specify target table 't2' for update in FROM clause
+update v2,t2 set v2.col1 = (select max(col1) from v2) where v2.col1 = t2.col1;
+ERROR HY000: You can't specify target table 'v2' for update in FROM clause
+update t1,t2 set t1.col1 = (select max(col1) from v2) where t1.col1 = t2.col1;
+ERROR HY000: You can't specify target table 't1' for update in FROM clause
+update v1,t2 set v1.col1 = (select max(col1) from v2) where v1.col1 = t2.col1;
+ERROR HY000: You can't specify target table 'v1' for update in FROM clause
+update t2,v2 set v2.col1 = (select max(col1) from v2) where v2.col1 = t2.col1;
+ERROR HY000: You can't specify target table 't2' for update in FROM clause
+update t2,t1 set t1.col1 = (select max(col1) from v2) where t1.col1 = t2.col1;
+ERROR HY000: You can't specify target table 't2' for update in FROM clause
+update t2,v1 set v1.col1 = (select max(col1) from v2) where v1.col1 = t2.col1;
+ERROR HY000: You can't specify target table 't2' for update in FROM clause
+update v3 set v3.col1 = (select max(col1) from v1);
+ERROR HY000: You can't specify target table 'v3' for update in FROM clause
+update v3 set v3.col1 = (select max(col1) from t1);
+ERROR HY000: You can't specify target table 'v3' for update in FROM clause
+update v3 set v3.col1 = (select max(col1) from v2);
+ERROR HY000: You can't specify target table 'v3' for update in FROM clause
+update v3 set v3.col1 = (select max(col1) from v3);
+ERROR HY000: You can't specify target table 'v3' 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
+delete from v2 where col1 = (select max(col1) from t1);
+ERROR HY000: You can't specify target table 'v2' for update in FROM clause
+delete from v2 where col1 = (select max(col1) from v2);
+ERROR HY000: You can't specify target table 'v2' for update in FROM clause
+delete v2 from v2,t2 where (select max(col1) from v1) > 0 and v2.col1 = t2.col1;
+ERROR HY000: You can't specify target table 'v2' for update in FROM clause
+delete t1 from t1,t2 where (select max(col1) from v1) > 0 and t1.col1 = t2.col1;
+ERROR HY000: You can't specify target table 't1' for update in FROM clause
+delete v1 from v1,t2 where (select max(col1) from v1) > 0 and v1.col1 = t2.col1;
+ERROR HY000: You can't specify target table 'v1' for update in FROM clause
+delete v2 from v2,t2 where (select max(col1) from t1) > 0 and v2.col1 = t2.col1;
+ERROR HY000: You can't specify target table 'v2' for update in FROM clause
+delete t1 from t1,t2 where (select max(col1) from t1) > 0 and t1.col1 = t2.col1;
+ERROR HY000: You can't specify target table 't1' for update in FROM clause
+delete v1 from v1,t2 where (select max(col1) from t1) > 0 and v1.col1 = t2.col1;
+ERROR HY000: You can't specify target table 'v1' for update in FROM clause
+delete v2 from v2,t2 where (select max(col1) from v2) > 0 and v2.col1 = t2.col1;
+ERROR HY000: You can't specify target table 'v2' for update in FROM clause
+delete t1 from t1,t2 where (select max(col1) from v2) > 0 and t1.col1 = t2.col1;
+ERROR HY000: You can't specify target table 't1' for update in FROM clause
+delete v1 from v1,t2 where (select max(col1) from v2) > 0 and v1.col1 = t2.col1;
+ERROR HY000: You can't specify target table 'v1' 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;
+insert into t1 values ((select max(col1) from v1));
+ERROR HY000: You can't specify target table 't1' 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
+insert into v2 values ((select max(col1) from t1));
+ERROR HY000: You can't specify target table 'v2' for update in FROM clause
+insert into t1 values ((select max(col1) from t1));
+ERROR HY000: You can't specify target table 't1' for update in FROM clause
+insert into v2 values ((select max(col1) from t1));
+ERROR HY000: You can't specify target table 'v2' for update in FROM clause
+insert into v2 values ((select max(col1) from v2));
+ERROR HY000: You can't specify target table 'v2' for update in FROM clause
+insert into t1 values ((select max(col1) from v2));
+ERROR HY000: You can't specify target table 't1' for update in FROM clause
+insert into v2 values ((select max(col1) from v2));
+ERROR HY000: You can't specify target table 'v2' for update in FROM clause
+insert into v3 (col1) values ((select max(col1) from v1));
+ERROR HY000: You can't specify target table 'v3' for update in FROM clause
+insert into v3 (col1) values ((select max(col1) from t1));
+ERROR HY000: You can't specify target table 'v3' for update in FROM clause
+insert into v3 (col1) values ((select max(col1) from v2));
+ERROR HY000: You can't specify target table 'v3' for update in FROM clause
+insert into v3 (col1) values ((select CONVERT_TZ('20050101000000','UTC','MET') from v2));
+ERROR HY000: You can't specify target table 'v3' for update in FROM clause
+insert into v3 (col1) values ((select CONVERT_TZ('20050101000000','UTC','MET') from t2));
+insert into mysql.time_zone values ('', (select CONVERT_TZ('20050101000000','UTC','MET') from t2));
+ERROR 23000: Column 'Use_leap_seconds' cannot be null
+create algorithm=temptable view v4 as select * from t1;
+insert into t1 values (1),(2),(3);
+insert into t1 (col1) values ((select max(col1) from v4));
+select * from t1;
+col1
+NULL
+1
+2
+3
+3
+drop view v4,v3,v2,v1;
drop table t1,t2;
create table t1 (s1 int);
create view v1 as select * from t1;
@@ -1687,6 +1795,8 @@
ERROR HY000: Can not delete from join view 'test.v3'
delete v3,t1 from v3,t1;
ERROR HY000: Can not delete from join view 'test.v3'
+delete t1,v3 from t1,v3;
+ERROR HY000: Can not delete from join view 'test.v3'
delete from t1;
prepare stmt1 from "insert into v3(a) values (?);";
set @a= 100;
@@ -1778,3 +1888,27 @@
s2
drop view v1;
drop table t1;
+CREATE TABLE t1 (a1 int);
+CREATE TABLE t2 (a2 int);
+INSERT INTO t1 VALUES (1), (2), (3), (4);
+INSERT INTO t2 VALUES (1), (2), (3);
+CREATE VIEW v1(a,b) AS SELECT a1,a2 FROM t1 JOIN t2 ON a1=a2 WHERE a1>1;
+SELECT * FROM v1;
+a b
+2 2
+3 3
+CREATE TABLE t3 SELECT * FROM v1;
+SELECT * FROM t3;
+a b
+2 2
+3 3
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+create table t1 (a int);
+create table t2 like t1;
+create table t3 like t1;
+create view v1 as select t1.a x, t2.a y from t1 join t2 where t1.a=t2.a;
+insert into t3 select x from v1;
+insert into t2 select x from v1;
+drop view v1;
+drop table t1,t2,t3;
--- 1.4/mysql-test/t/lowercase_view.test Wed Nov 24 19:48:25 2004
+++ 1.5/mysql-test/t/lowercase_view.test Mon Mar 28 15:12:28 2005
@@ -19,18 +19,109 @@
# 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;
+create table t2aA (col1 int);
+create view v1Aa as select * from t1aA;
+create view v2aA as select * from v1aA;
+create view v3Aa as select v2Aa.col1 from v2aA,t2Aa where v2Aa.col1 = t2aA.col1;
-- 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;
+update v2aA set col1 = (select max(col1) from v1Aa);
-- 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;
+update v2Aa set col1 = (select max(col1) from t1Aa);
+-- error 1093
+update v2aA set col1 = (select max(col1) from v2Aa);
+-- error 1093
+update v2aA,t2Aa set v2Aa.col1 = (select max(col1) from v1aA) where v2aA.col1 = t2aA.col1;
+-- error 1093
+update t1aA,t2Aa set t1Aa.col1 = (select max(col1) from v1Aa) where t1aA.col1 = t2aA.col1;
+-- error 1093
+update v1aA,t2Aa set v1Aa.col1 = (select max(col1) from v1aA) where v1Aa.col1 = t2aA.col1;
+-- error 1093
+update t2Aa,v2Aa set v2aA.col1 = (select max(col1) from v1aA) where v2Aa.col1 = t2aA.col1;
+-- error 1093
+update t2Aa,t1Aa set t1aA.col1 = (select max(col1) from v1Aa) where t1Aa.col1 = t2aA.col1;
+-- error 1093
+update t2Aa,v1aA set v1Aa.col1 = (select max(col1) from v1aA) where v1Aa.col1 = t2aA.col1;
+-- error 1093
+update v2aA,t2Aa set v2Aa.col1 = (select max(col1) from t1aA) where v2aA.col1 = t2aA.col1;
+-- error 1093
+update t1Aa,t2Aa set t1aA.col1 = (select max(col1) from t1Aa) where t1aA.col1 = t2aA.col1;
+-- error 1093
+update v1aA,t2Aa set v1Aa.col1 = (select max(col1) from t1Aa) where v1aA.col1 = t2aA.col1;
+-- error 1093
+update t2Aa,v2Aa set v2aA.col1 = (select max(col1) from t1aA) where v2Aa.col1 = t2aA.col1;
+-- error 1093
+update t2Aa,t1Aa set t1aA.col1 = (select max(col1) from t1Aa) where t1aA.col1 = t2aA.col1;
+-- error 1093
+update t2Aa,v1Aa set v1aA.col1 = (select max(col1) from t1Aa) where v1Aa.col1 = t2aA.col1;
+-- error 1093
+update v2aA,t2Aa set v2Aa.col1 = (select max(col1) from v2aA) where v2Aa.col1 = t2aA.col1;
+-- error 1093
+update t1aA,t2Aa set t1Aa.col1 = (select max(col1) from v2aA) where t1aA.col1 = t2aA.col1;
+-- error 1093
+update v1aA,t2Aa set v1Aa.col1 = (select max(col1) from v2Aa) where v1aA.col1 = t2aA.col1;
+-- error 1093
+update t2Aa,v2aA set v2Aa.col1 = (select max(col1) from v2aA) where v2Aa.col1 = t2aA.col1;
+-- error 1093
+update t2Aa,t1Aa set t1aA.col1 = (select max(col1) from v2aA) where t1Aa.col1 = t2aA.col1;
+-- error 1093
+update t2Aa,v1Aa set v1aA.col1 = (select max(col1) from v2Aa) where v1Aa.col1 = t2aA.col1;
+-- error 1093
+update v3aA set v3Aa.col1 = (select max(col1) from v1aA);
+-- error 1093
+update v3aA set v3Aa.col1 = (select max(col1) from t1aA);
+-- error 1093
+update v3aA set v3Aa.col1 = (select max(col1) from v2aA);
+-- error 1093
+update v3aA set v3Aa.col1 = (select max(col1) from v3aA);
+-- error 1093
+delete from v2Aa where col1 = (select max(col1) from v1Aa);
+-- error 1093
+delete from v2aA where col1 = (select max(col1) from t1Aa);
+-- error 1093
+delete from v2Aa where col1 = (select max(col1) from v2aA);
+-- error 1093
+delete v2Aa from v2aA,t2Aa where (select max(col1) from v1aA) > 0 and v2Aa.col1 = t2aA.col1;
+-- error 1093
+delete t1aA from t1Aa,t2Aa where (select max(col1) from v1Aa) > 0 and t1aA.col1 = t2aA.col1;
+-- error 1093
+delete v1aA from v1Aa,t2Aa where (select max(col1) from v1aA) > 0 and v1Aa.col1 = t2aA.col1;
+-- error 1093
+delete v2aA from v2Aa,t2Aa where (select max(col1) from t1Aa) > 0 and v2aA.col1 = t2aA.col1;
+-- error 1093
+delete t1aA from t1Aa,t2Aa where (select max(col1) from t1aA) > 0 and t1Aa.col1 = t2aA.col1;
+-- error 1093
+delete v1aA from v1Aa,t2Aa where (select max(col1) from t1aA) > 0 and v1aA.col1 = t2aA.col1;
+-- error 1093
+delete v2Aa from v2aA,t2Aa where (select max(col1) from v2Aa) > 0 and v2aA.col1 = t2aA.col1;
+-- error 1093
+delete t1Aa from t1aA,t2Aa where (select max(col1) from v2Aa) > 0 and t1Aa.col1 = t2aA.col1;
+-- error 1093
+delete v1Aa from v1aA,t2Aa where (select max(col1) from v2aA) > 0 and v1Aa.col1 = t2aA.col1;
+-- error 1093
+insert into v2Aa values ((select max(col1) from v1aA));
+-- error 1093
+insert into t1aA values ((select max(col1) from v1Aa));
-- error 1093
insert into v2aA values ((select max(col1) from v1aA));
-drop view v2Aa,v1Aa;
+-- error 1093
+insert into v2Aa values ((select max(col1) from t1Aa));
+-- error 1093
+insert into t1aA values ((select max(col1) from t1Aa));
+-- error 1093
+insert into v2aA values ((select max(col1) from t1aA));
+-- error 1093
+insert into v2Aa values ((select max(col1) from v2aA));
+-- error 1093
+insert into t1Aa values ((select max(col1) from v2Aa));
+-- error 1093
+insert into v2aA values ((select max(col1) from v2Aa));
+-- error 1093
+insert into v3Aa (col1) values ((select max(col1) from v1Aa));
+-- error 1093
+insert into v3aA (col1) values ((select max(col1) from t1aA));
+-- error 1093
+insert into v3Aa (col1) values ((select max(col1) from v2aA));
+drop view v3aA,v2Aa,v1aA;
drop table t1Aa,t2Aa;
#
--- 1.62/mysql-test/t/view.test Tue Mar 15 19:54:39 2005
+++ 1.63/mysql-test/t/view.test Mon Mar 28 15:12:28 2005
@@ -1198,15 +1198,118 @@
create table t2 (col1 int);
create view v1 as select * from t1;
create view v2 as select * from v1;
+create view v3 as select v2.col1 from v2,t2 where v2.col1 = t2.col1;
-- 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
+update v2 set col1 = (select max(col1) from t1);
+-- error 1093
+update v2 set col1 = (select max(col1) from v2);
+-- error 1093
+update v2,t2 set v2.col1 = (select max(col1) from v1) where v2.col1 = t2.col1;
+-- error 1093
+update t1,t2 set t1.col1 = (select max(col1) from v1) where t1.col1 = t2.col1;
+-- error 1093
+update v1,t2 set v1.col1 = (select max(col1) from v1) where v1.col1 = t2.col1;
+-- error 1093
+update t2,v2 set v2.col1 = (select max(col1) from v1) where v2.col1 = t2.col1;
+-- error 1093
+update t2,t1 set t1.col1 = (select max(col1) from v1) where t1.col1 = t2.col1;
+-- error 1093
+update t2,v1 set v1.col1 = (select max(col1) from v1) where v1.col1 = t2.col1;
+-- error 1093
+update v2,t2 set v2.col1 = (select max(col1) from t1) where v2.col1 = t2.col1;
+-- error 1093
+update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1;
+-- error 1093
+update v1,t2 set v1.col1 = (select max(col1) from t1) where v1.col1 = t2.col1;
+-- error 1093
+update t2,v2 set v2.col1 = (select max(col1) from t1) where v2.col1 = t2.col1;
+-- error 1093
+update t2,t1 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1;
+-- error 1093
+update t2,v1 set v1.col1 = (select max(col1) from t1) where v1.col1 = t2.col1;
+-- error 1093
+update v2,t2 set v2.col1 = (select max(col1) from v2) where v2.col1 = t2.col1;
+-- error 1093
+update t1,t2 set t1.col1 = (select max(col1) from v2) where t1.col1 = t2.col1;
+-- error 1093
+update v1,t2 set v1.col1 = (select max(col1) from v2) where v1.col1 = t2.col1;
+-- error 1093
+update t2,v2 set v2.col1 = (select max(col1) from v2) where v2.col1 = t2.col1;
+-- error 1093
+update t2,t1 set t1.col1 = (select max(col1) from v2) where t1.col1 = t2.col1;
+-- error 1093
+update t2,v1 set v1.col1 = (select max(col1) from v2) where v1.col1 = t2.col1;
+-- error 1093
+update v3 set v3.col1 = (select max(col1) from v1);
+-- error 1093
+update v3 set v3.col1 = (select max(col1) from t1);
+-- error 1093
+update v3 set v3.col1 = (select max(col1) from v2);
+-- error 1093
+update v3 set v3.col1 = (select max(col1) from v3);
-- 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
+delete from v2 where col1 = (select max(col1) from t1);
+-- error 1093
+delete from v2 where col1 = (select max(col1) from v2);
+-- error 1093
+delete v2 from v2,t2 where (select max(col1) from v1) > 0 and v2.col1 = t2.col1;
+-- error 1093
+delete t1 from t1,t2 where (select max(col1) from v1) > 0 and t1.col1 = t2.col1;
+-- error 1093
+delete v1 from v1,t2 where (select max(col1) from v1) > 0 and v1.col1 = t2.col1;
+-- error 1093
+delete v2 from v2,t2 where (select max(col1) from t1) > 0 and v2.col1 = t2.col1;
+-- error 1093
+delete t1 from t1,t2 where (select max(col1) from t1) > 0 and t1.col1 = t2.col1;
+-- error 1093
+delete v1 from v1,t2 where (select max(col1) from t1) > 0 and v1.col1 = t2.col1;
+-- error 1093
+delete v2 from v2,t2 where (select max(col1) from v2) > 0 and v2.col1 = t2.col1;
+-- error 1093
+delete t1 from t1,t2 where (select max(col1) from v2) > 0 and t1.col1 = t2.col1;
+-- error 1093
+delete v1 from v1,t2 where (select max(col1) from v2) > 0 and v1.col1 = t2.col1;
-- error 1093
insert into v2 values ((select max(col1) from v1));
-drop view v2,v1;
+-- error 1093
+insert into t1 values ((select max(col1) from v1));
+-- error 1093
+insert into v2 values ((select max(col1) from v1));
+-- error 1093
+insert into v2 values ((select max(col1) from t1));
+-- error 1093
+insert into t1 values ((select max(col1) from t1));
+-- error 1093
+insert into v2 values ((select max(col1) from t1));
+-- error 1093
+insert into v2 values ((select max(col1) from v2));
+-- error 1093
+insert into t1 values ((select max(col1) from v2));
+-- error 1093
+insert into v2 values ((select max(col1) from v2));
+-- error 1093
+insert into v3 (col1) values ((select max(col1) from v1));
+-- error 1093
+insert into v3 (col1) values ((select max(col1) from t1));
+-- error 1093
+insert into v3 (col1) values ((select max(col1) from v2));
+#check with TZ tables in list
+-- error 1093
+insert into v3 (col1) values ((select CONVERT_TZ('20050101000000','UTC','MET') from v2));
+insert into v3 (col1) values ((select CONVERT_TZ('20050101000000','UTC','MET') from t2));
+-- error 1048
+insert into mysql.time_zone values ('', (select CONVERT_TZ('20050101000000','UTC','MET') from t2));
+# temporary table algorithm view should be equal to subquery in the from clause
+create algorithm=temptable view v4 as select * from t1;
+insert into t1 values (1),(2),(3);
+insert into t1 (col1) values ((select max(col1) from v4));
+select * from t1;
+
+drop view v4,v3,v2,v1;
drop table t1,t2;
#
@@ -1637,6 +1740,8 @@
delete from v3;
-- error 1395
delete v3,t1 from v3,t1;
+-- error 1395
+delete t1,v3 from t1,v3;
# delete from t1 just to reduce result set size
delete from t1;
# prepare statement with insert join view
@@ -1716,3 +1821,30 @@
drop view v1;
drop table t1;
+#
+# Test case for bug #9398 CREATE TABLE with SELECT from a multi-table view
+#
+CREATE TABLE t1 (a1 int);
+CREATE TABLE t2 (a2 int);
+INSERT INTO t1 VALUES (1), (2), (3), (4);
+INSERT INTO t2 VALUES (1), (2), (3);
+CREATE VIEW v1(a,b) AS SELECT a1,a2 FROM t1 JOIN t2 ON a1=a2 WHERE a1>1;
+
+SELECT * FROM v1;
+CREATE TABLE t3 SELECT * FROM v1;
+SELECT * FROM t3;
+
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+
+#
+# Test for BUG#8703 "insert into table select from view crashes"
+#
+create table t1 (a int);
+create table t2 like t1;
+create table t3 like t1;
+create view v1 as select t1.a x, t2.a y from t1 join t2 where t1.a=t2.a;
+insert into t3 select x from v1;
+insert into t2 select x from v1;
+drop view v1;
+drop table t1,t2,t3;
--- 1.108/sql/sql_prepare.cc Thu Mar 24 14:17:32 2005
+++ 1.109/sql/sql_prepare.cc Mon Mar 28 15:12:29 2005
@@ -1871,6 +1871,9 @@
/* remove option which was put by mysql_explain_union() */
sl->options&= ~SELECT_DESCRIBE;
+ /* see unique_table() */
+ sl->exclude_from_table_unique_test= FALSE;
+
/*
Copy WHERE clause pointers to avoid damaging they by optimisation
*/
| Thread |
|---|
| • bk commit into 5.0 tree (bell:1.1845) BUG#8703 | sanja | 28 Mar |
