diff options
38 files changed, 716 insertions, 101 deletions
diff --git a/include/mysqld_error.h b/include/mysqld_error.h index cf032380e2f..1f66eb54df5 100644 --- a/include/mysqld_error.h +++ b/include/mysqld_error.h @@ -380,4 +380,7 @@ #define ER_TRG_ON_VIEW_OR_TEMP_TABLE 1361 #define ER_TRG_CANT_CHANGE_ROW 1362 #define ER_TRG_NO_SUCH_ROW_IN_TRG 1363 -#define ER_ERROR_MESSAGES 364 +#define ER_VIEW_MULTIUPDATE 1364 +#define ER_VIEW_NO_INSERT_FIELD_LIST 1365 +#define ER_VIEW_DELETE_MERGE_VIEW 1366 +#define ER_ERROR_MESSAGES 367 diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index c1462bf8c74..43b90c0e779 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -1359,3 +1359,152 @@ a a b deallocate prepare stmt1; drop view v4,v3,v2,v1; drop tables t1,t2,t3; +create table t1 (a int, primary key (a), b int); +create table t2 (a int, primary key (a)); +insert into t1 values (1,100), (2,200); +insert into t2 values (1), (3); +create view v3 (a,b) as select t1.a as a, t2.a as b from t1, t2; +update v3 set a= 10 where a=1; +select * from t1; +a b +10 100 +2 200 +select * from t2; +a +1 +3 +create view v2 (a,b) as select t1.b as a, t2.a as b from t1, t2; +update v2 set a= 10 where a=200; +ERROR HY000: The target table v2 of the UPDATE is not updatable +select * from v3; +a b +2 1 +10 1 +2 3 +10 3 +select * from v2; +a b +100 1 +200 1 +100 3 +200 3 +set @a= 10; +set @b= 100; +prepare stmt1 from "update v3 set a= ? where a=?"; +execute stmt1 using @a,@b; +select * from v3; +a b +2 1 +10 1 +2 3 +10 3 +set @a= 300; +set @b= 10; +execute stmt1 using @a,@b; +select * from v3; +a b +2 1 +300 1 +2 3 +300 3 +deallocate prepare stmt1; +drop view v3,v2; +drop tables t1,t2; +create table t1 (a int, primary key (a), b int); +create table t2 (a int, primary key (a), b int); +insert into t2 values (1000, 2000); +create view v3 (a,b) as select t1.a as a, t2.a as b from t1, t2; +insert into v3 values (1,2); +ERROR HY000: Can not insert into join view 'test.v3' without fields list +insert into v3 select * from t2; +ERROR HY000: Can not insert into join view 'test.v3' without fields list +insert into v3(a,b) values (1,2); +ERROR HY000: Can not modify more than one base table through a join view 'test.v3' +insert into v3(a,b) select * from t2; +ERROR HY000: Can not modify more than one base table through a join view 'test.v3' +insert into v3(a) values (1); +insert into v3(b) values (10); +insert into v3(a) select a from t2; +insert into v3(b) select b from t2; +Warnings: +Warning 1263 Data truncated; NULL supplied to NOT NULL column 'a' at row 2 +insert into v3(a) values (1) on duplicate key update a=a+10000+VALUES(a); +select * from t1; +a b +10002 NULL +10 NULL +1000 NULL +select * from t2; +a b +1000 2000 +10 NULL +2000 NULL +0 NULL +create view v2 (a,b) as select t1.b as a, t2.a as b from t1, t2; +insert into v2(a) values (10); +ERROR HY000: The target table v2 of the INSERT is not updatable +select * from v3; +a b +10 1000 +1000 1000 +10002 1000 +10 10 +1000 10 +10002 10 +10 2000 +1000 2000 +10002 2000 +10 0 +1000 0 +10002 0 +select * from v2; +a b +NULL 1000 +NULL 1000 +NULL 1000 +NULL 10 +NULL 10 +NULL 10 +NULL 2000 +NULL 2000 +NULL 2000 +NULL 0 +NULL 0 +NULL 0 +delete from v3; +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 from t1; +prepare stmt1 from "insert into v3(a) values (?);"; +set @a= 100; +execute stmt1 using @a; +set @a= 300; +execute stmt1 using @a; +deallocate prepare stmt1; +prepare stmt1 from "insert into v3(a) select ?;"; +set @a= 101; +execute stmt1 using @a; +set @a= 301; +execute stmt1 using @a; +deallocate prepare stmt1; +select * from v3; +a b +100 1000 +101 1000 +300 1000 +301 1000 +100 10 +101 10 +300 10 +301 10 +100 2000 +101 2000 +300 2000 +301 2000 +100 0 +101 0 +300 0 +301 0 +drop view v3,v2; +drop tables t1,t2; diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 9dbb0facf40..4fe4bcc6df2 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -1298,3 +1298,93 @@ execute stmt1; deallocate prepare stmt1; drop view v4,v3,v2,v1; drop tables t1,t2,t3; + +# +# updating of join view +# +create table t1 (a int, primary key (a), b int); +create table t2 (a int, primary key (a)); +insert into t1 values (1,100), (2,200); +insert into t2 values (1), (3); +# legal view for update +create view v3 (a,b) as select t1.a as a, t2.a as b from t1, t2; +update v3 set a= 10 where a=1; +select * from t1; +select * from t2; +# view without primary key +create view v2 (a,b) as select t1.b as a, t2.a as b from t1, t2; +-- error 1288 +update v2 set a= 10 where a=200; +# just view selects +select * from v3; +select * from v2; +# prepare statement with updating join view +set @a= 10; +set @b= 100; +prepare stmt1 from "update v3 set a= ? where a=?"; +execute stmt1 using @a,@b; +select * from v3; +set @a= 300; +set @b= 10; +execute stmt1 using @a,@b; +select * from v3; +deallocate prepare stmt1; +drop view v3,v2; +drop tables t1,t2; + +# +# inserting/deleting join view +# +create table t1 (a int, primary key (a), b int); +create table t2 (a int, primary key (a), b int); +insert into t2 values (1000, 2000); +create view v3 (a,b) as select t1.a as a, t2.a as b from t1, t2; +# inserting into join view without field list +-- error 1365 +insert into v3 values (1,2); +-- error 1365 +insert into v3 select * from t2; +# inserting in several tables of join view +-- error 1364 +insert into v3(a,b) values (1,2); +-- error 1364 +insert into v3(a,b) select * from t2; +# correct inserts into join view +insert into v3(a) values (1); +insert into v3(b) values (10); +insert into v3(a) select a from t2; +insert into v3(b) select b from t2; +insert into v3(a) values (1) on duplicate key update a=a+10000+VALUES(a); +select * from t1; +select * from t2; +# view without primary key +create view v2 (a,b) as select t1.b as a, t2.a as b from t1, t2; +-- error 1288 +insert into v2(a) values (10); +# just view selects +select * from v3; +select * from v2; +# try delete from join view +-- error 1366 +delete from v3; +-- error 1366 +delete v3,t1 from v3,t1; +# delete from t1 just to reduce result set size +delete from t1; +# prepare statement with insert join view +prepare stmt1 from "insert into v3(a) values (?);"; +set @a= 100; +execute stmt1 using @a; +set @a= 300; +execute stmt1 using @a; +deallocate prepare stmt1; +prepare stmt1 from "insert into v3(a) select ?;"; +set @a= 101; +execute stmt1 using @a; +set @a= 301; +execute stmt1 using @a; +deallocate prepare stmt1; +select * from v3; + +drop view v3,v2; +drop tables t1,t2; diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index a43ec6f00ed..b84ecdb2296 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -611,7 +611,8 @@ int mysql_multi_update(THD *thd, TABLE_LIST *table_list, List<Item> *fields, List<Item> *values, COND *conds, ulong options, enum enum_duplicates handle_duplicates, - SELECT_LEX_UNIT *unit, SELECT_LEX *select_lex); + SELECT_LEX_UNIT *unit, SELECT_LEX *select_lex, + bool converted); int mysql_prepare_insert(THD *thd, TABLE_LIST *table_list, TABLE *table, List<Item> &fields, List_item *values, List<Item> &update_fields, diff --git a/sql/share/czech/errmsg.txt b/sql/share/czech/errmsg.txt index c613a22088a..3565343a012 100644 --- a/sql/share/czech/errmsg.txt +++ b/sql/share/czech/errmsg.txt @@ -392,3 +392,6 @@ character-set=latin2 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Can not modify more than one base table through a join view '%-.64s.%-.64s'" +"Can not insert into join view '%-.64s.%-.64s' without fields list" +"Can not delete from join view '%-.64s.%-.64s'" diff --git a/sql/share/danish/errmsg.txt b/sql/share/danish/errmsg.txt index 0f3a8f6ffdb..1f861dbc08e 100644 --- a/sql/share/danish/errmsg.txt +++ b/sql/share/danish/errmsg.txt @@ -383,3 +383,6 @@ character-set=latin1 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Can not modify more than one base table through a join view '%-.64s.%-.64s'" +"Can not insert into join view '%-.64s.%-.64s' without fields list" +"Can not delete from join view '%-.64s.%-.64s'" diff --git a/sql/share/dutch/errmsg.txt b/sql/share/dutch/errmsg.txt index d0d86a07b7e..2c9e5830a66 100644 --- a/sql/share/dutch/errmsg.txt +++ b/sql/share/dutch/errmsg.txt @@ -392,3 +392,6 @@ character-set=latin1 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Can not modify more than one base table through a join view '%-.64s.%-.64s'" +"Can not insert into join view '%-.64s.%-.64s' without fields list" +"Can not delete from join view '%-.64s.%-.64s'" diff --git a/sql/share/english/errmsg.txt b/sql/share/english/errmsg.txt index 0b502244a64..a6ca0bdda99 100644 --- a/sql/share/english/errmsg.txt +++ b/sql/share/english/errmsg.txt @@ -380,3 +380,6 @@ character-set=latin1 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Can not modify more than one base table through a join view '%-.64s.%-.64s'" +"Can not insert into join view '%-.64s.%-.64s' without fields list" +"Can not delete from join view '%-.64s.%-.64s'" diff --git a/sql/share/estonian/errmsg.txt b/sql/share/estonian/errmsg.txt index 7886c785c40..65bdb7ce354 100644 --- a/sql/share/estonian/errmsg.txt +++ b/sql/share/estonian/errmsg.txt @@ -385,3 +385,6 @@ character-set=latin7 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Can not modify more than one base table through a join view '%-.64s.%-.64s'" +"Can not insert into join view '%-.64s.%-.64s' without fields list" +"Can not delete from join view '%-.64s.%-.64s'" diff --git a/sql/share/french/errmsg.txt b/sql/share/french/errmsg.txt index 9668cbf3c9f..116dec98742 100644 --- a/sql/share/french/errmsg.txt +++ b/sql/share/french/errmsg.txt @@ -380,3 +380,6 @@ character-set=latin1 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Can not modify more than one base table through a join view '%-.64s.%-.64s'" +"Can not insert into join view '%-.64s.%-.64s' without fields list" +"Can not delete from join view '%-.64s.%-.64s'" diff --git a/sql/share/german/errmsg.txt b/sql/share/german/errmsg.txt index c2c82443f91..dea95536212 100644 --- a/sql/share/german/errmsg.txt +++ b/sql/share/german/errmsg.txt @@ -393,3 +393,6 @@ character-set=latin1 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Can not modify more than one base table through a join view '%-.64s.%-.64s'" +"Can not insert into join view '%-.64s.%-.64s' without fields list" +"Can not delete from join view '%-.64s.%-.64s'" diff --git a/sql/share/greek/errmsg.txt b/sql/share/greek/errmsg.txt index 3aac21ec481..5e1fb04c392 100644 --- a/sql/share/greek/errmsg.txt +++ b/sql/share/greek/errmsg.txt @@ -380,3 +380,6 @@ character-set=greek "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Can not modify more than one base table through a join view '%-.64s.%-.64s'" +"Can not insert into join view '%-.64s.%-.64s' without fields list" +"Can not delete from join view '%-.64s.%-.64s'" diff --git a/sql/share/hungarian/errmsg.txt b/sql/share/hungarian/errmsg.txt index 8f96a0bf183..1a63fcc68aa 100644 --- a/sql/share/hungarian/errmsg.txt +++ b/sql/share/hungarian/errmsg.txt @@ -385,3 +385,6 @@ character-set=latin2 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Can not modify more than one base table through a join view '%-.64s.%-.64s'" +"Can not insert into join view '%-.64s.%-.64s' without fields list" +"Can not delete from join view '%-.64s.%-.64s'" diff --git a/sql/share/italian/errmsg.txt b/sql/share/italian/errmsg.txt index 8d63656bac2..4817281f0cd 100644 --- a/sql/share/italian/errmsg.txt +++ b/sql/share/italian/errmsg.txt @@ -380,3 +380,6 @@ character-set=latin1 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Can not modify more than one base table through a join view '%-.64s.%-.64s'" +"Can not insert into join view '%-.64s.%-.64s' without fields list" +"Can not delete from join view '%-.64s.%-.64s'" diff --git a/sql/share/japanese/errmsg.txt b/sql/share/japanese/errmsg.txt index 30eb8daffa4..ab7bc51a31a 100644 --- a/sql/share/japanese/errmsg.txt +++ b/sql/share/japanese/errmsg.txt @@ -384,3 +384,6 @@ character-set=ujis "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Can not modify more than one base table through a join view '%-.64s.%-.64s'" +"Can not insert into join view '%-.64s.%-.64s' without fields list" +"Can not delete from join view '%-.64s.%-.64s'" diff --git a/sql/share/korean/errmsg.txt b/sql/share/korean/errmsg.txt index dac28683a78..3c67464b51d 100644 --- a/sql/share/korean/errmsg.txt +++ b/sql/share/korean/errmsg.txt @@ -380,3 +380,6 @@ character-set=euckr "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Can not modify more than one base table through a join view '%-.64s.%-.64s'" +"Can not insert into join view '%-.64s.%-.64s' without fields list" +"Can not delete from join view '%-.64s.%-.64s'" diff --git a/sql/share/norwegian-ny/errmsg.txt b/sql/share/norwegian-ny/errmsg.txt index 58661f16bcf..b6e8b173078 100644 --- a/sql/share/norwegian-ny/errmsg.txt +++ b/sql/share/norwegian-ny/errmsg.txt @@ -382,3 +382,6 @@ character-set=latin1 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Can not modify more than one base table through a join view '%-.64s.%-.64s'" +"Can not insert into join view '%-.64s.%-.64s' without fields list" +"Can not delete from join view '%-.64s.%-.64s'" diff --git a/sql/share/norwegian/errmsg.txt b/sql/share/norwegian/errmsg.txt index ea204145d54..ec92d713d74 100644 --- a/sql/share/norwegian/errmsg.txt +++ b/sql/share/norwegian/errmsg.txt @@ -382,3 +382,6 @@ character-set=latin1 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Can not modify more than one base table through a join view '%-.64s.%-.64s'" +"Can not insert into join view '%-.64s.%-.64s' without fields list" +"Can not delete from join view '%-.64s.%-.64s'" diff --git a/sql/share/polish/errmsg.txt b/sql/share/polish/errmsg.txt index db0d4914c19..412977f0de8 100644 --- a/sql/share/polish/errmsg.txt +++ b/sql/share/polish/errmsg.txt @@ -385,3 +385,6 @@ character-set=latin2 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Can not modify more than one base table through a join view '%-.64s.%-.64s'" +"Can not insert into join view '%-.64s.%-.64s' without fields list" +"Can not delete from join view '%-.64s.%-.64s'" diff --git a/sql/share/portuguese/errmsg.txt b/sql/share/portuguese/errmsg.txt index d3508f120db..46e2e598e82 100644 --- a/sql/share/portuguese/errmsg.txt +++ b/sql/share/portuguese/errmsg.txt @@ -382,3 +382,6 @@ character-set=latin1 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Can not modify more than one base table through a join view '%-.64s.%-.64s'" +"Can not insert into join view '%-.64s.%-.64s' without fields list" +"Can not delete from join view '%-.64s.%-.64s'" diff --git a/sql/share/romanian/errmsg.txt b/sql/share/romanian/errmsg.txt index 65ab66e1256..76b74c89cf4 100644 --- a/sql/share/romanian/errmsg.txt +++ b/sql/share/romanian/errmsg.txt @@ -385,3 +385,6 @@ character-set=latin2 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Can not modify more than one base table through a join view '%-.64s.%-.64s'" +"Can not insert into join view '%-.64s.%-.64s' without fields list" +"Can not delete from join view '%-.64s.%-.64s'" diff --git a/sql/share/russian/errmsg.txt b/sql/share/russian/errmsg.txt index 6501598383c..6d39ddb6cef 100644 --- a/sql/share/russian/errmsg.txt +++ b/sql/share/russian/errmsg.txt @@ -376,7 +376,7 @@ character-set=koi8r "View SELECT содержит ссылку на временную таблицу '%-.64s'" "View SELECT и список полей view имеют разное количество столбцов" "Алгоритм слияния view не может быть использован сейчас (алгоритм будет неопеределенным)" -"Обновляемый view не содержит ключа использованной в нем таблиц(ы)" +"Обновляемый view не содержит ключа использованных(ой) в нем таблиц(ы)" "View '%-.64s.%-.64s' ссылается на несуществующие таблицы или столбцы" "Can't drop a %s from within another stored routine" "GOTO is not allowed in a stored procedure handler" @@ -385,3 +385,6 @@ character-set=koi8r "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Нельзя изменить больше чем одну базовую таблицу используя многотабличный VIEW '%-.64s.%-.64s'" +"Нельзя вставлять записи в многотабличный VIEW '%-.64s.%-.64s' без списка полей" +"Нельзя удалять из многотабличного VIEW '%-.64s.%-.64s'" diff --git a/sql/share/serbian/errmsg.txt b/sql/share/serbian/errmsg.txt index 6e7826e33d6..7ad12dcee77 100644 --- a/sql/share/serbian/errmsg.txt +++ b/sql/share/serbian/errmsg.txt @@ -373,3 +373,6 @@ character-set=cp1250 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Can not modify more than one base table through a join view '%-.64s.%-.64s'" +"Can not insert into join view '%-.64s.%-.64s' without fields list" +"Can not delete from join view '%-.64s.%-.64s'" diff --git a/sql/share/slovak/errmsg.txt b/sql/share/slovak/errmsg.txt index 4c82ae5e3af..cfbfeff587b 100644 --- a/sql/share/slovak/errmsg.txt +++ b/sql/share/slovak/errmsg.txt @@ -388,3 +388,6 @@ character-set=latin2 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Can not modify more than one base table through a join view '%-.64s.%-.64s'" +"Can not insert into join view '%-.64s.%-.64s' without fields list" +"Can not delete from join view '%-.64s.%-.64s'" diff --git a/sql/share/spanish/errmsg.txt b/sql/share/spanish/errmsg.txt index c9a1d7229ea..e663492f010 100644 --- a/sql/share/spanish/errmsg.txt +++ b/sql/share/spanish/errmsg.txt @@ -384,3 +384,6 @@ character-set=latin1 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Can not modify more than one base table through a join view '%-.64s.%-.64s'" +"Can not insert into join view '%-.64s.%-.64s' without fields list" +"Can not delete from join view '%-.64s.%-.64s'" diff --git a/sql/share/swedish/errmsg.txt b/sql/share/swedish/errmsg.txt index e80cb48d157..bbf8bddea1a 100644 --- a/sql/share/swedish/errmsg.txt +++ b/sql/share/swedish/errmsg.txt @@ -380,3 +380,6 @@ character-set=latin1 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Can not modify more than one base table through a join view '%-.64s.%-.64s'" +"Can not insert into join view '%-.64s.%-.64s' without fields list" +"Can not delete from join view '%-.64s.%-.64s'" diff --git a/sql/share/ukrainian/errmsg.txt b/sql/share/ukrainian/errmsg.txt index 2a71aa088f4..00cfb02a95d 100644 --- a/sql/share/ukrainian/errmsg.txt +++ b/sql/share/ukrainian/errmsg.txt @@ -386,3 +386,6 @@ character-set=koi8u "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Неможливо оновити б╕льш ниж одну базову таблицю выкористовуючи VIEW '%-.64s.%-.64s', що м╕ст╕ть дек╕лька таблиць" +"Неможливо уставити рядки у VIEW '%-.64s.%-.64s', що м╕стить дек╕лька таблиць, без списку стовбц╕в" +"Неможливо видалити рядки у VIEW '%-.64s.%-.64s', що м╕стить дек╕лька таблиць" diff --git a/sql/sql_base.cc b/sql/sql_base.cc index c0deeba6148..c68f3b36284 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -2296,7 +2296,7 @@ find_field_in_tables(THD *thd, Item_ident *item, TABLE_LIST *tables, bool allow_rowid= tables && !tables->next_local; // Only one table for (; tables ; tables= tables->next_local) { - if (!tables->table) + if (!tables->table && !tables->ancestor) { if (report_error) my_printf_error(ER_BAD_FIELD_ERROR,ER(ER_BAD_FIELD_ERROR),MYF(0), diff --git a/sql/sql_class.h b/sql/sql_class.h index d1a8366653f..7bcc3bea447 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -1504,7 +1504,9 @@ public: class multi_update :public select_result { - TABLE_LIST *all_tables, *update_tables, *table_being_updated; + TABLE_LIST *all_tables; /* query/update command tables */ + TABLE_LIST *leaves; /* list of leves of join table tree */ + TABLE_LIST *update_tables, *table_being_updated; THD *thd; TABLE **tmp_tables, *main_table, *table_to_update; TMP_TABLE_PARAM *tmp_table_param; @@ -1517,8 +1519,9 @@ class multi_update :public select_result bool do_update, trans_safe, transactional_tables, log_delayed; public: - multi_update(THD *thd_arg, TABLE_LIST *ut, List<Item> *fields, - List<Item> *values, enum_duplicates handle_duplicates); + multi_update(THD *thd_arg, TABLE_LIST *ut, TABLE_LIST *leaves_list, + List<Item> *fields, List<Item> *values, + enum_duplicates handle_duplicates); ~multi_update(); int prepare(List<Item> &list, SELECT_LEX_UNIT *u); bool send_fields(List<Item> &list, uint flags) { return 0; } diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index 4885470846b..753efb29b36 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -43,7 +43,14 @@ int mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, SQL_LIST *order, if ((error= open_and_lock_tables(thd, table_list))) DBUG_RETURN(error); - table= table_list->table; + if (!(table= table_list->table)) + { + DBUG_ASSERT(table_list->view && + table_list->ancestor && table_list->ancestor->next_local); + my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0), + table_list->view_db.str, table_list->view_name.str); + DBUG_RETURN(-1); + } table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK); thd->proc_info="init"; table->map=1; diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 03028a13d19..9690b9289ba 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -21,6 +21,7 @@ #include "sql_acl.h" #include "sp_head.h" #include "sql_trigger.h" +#include "sql_select.h" static int check_null_fields(THD *thd,TABLE *entry); #ifndef EMBEDDED_LIBRARY @@ -31,6 +32,7 @@ static void end_delayed_insert(THD *thd); extern "C" pthread_handler_decl(handle_delayed_insert,arg); static void unlink_blobs(register TABLE *table); #endif +static bool check_view_insertability(TABLE_LIST *view, ulong query_id); /* Define to force use of my_malloc() if the allocated memory block is big */ @@ -54,8 +56,22 @@ check_insert_fields(THD *thd, TABLE_LIST *table_list, List<Item> &fields, { TABLE *table= table_list->table; + if (!table_list->updatable) + { + my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias, "INSERT"); + return -1; + } + if (fields.elements == 0 && values.elements != 0) { + if (!table) + { + DBUG_ASSERT(table_list->view && + table_list->ancestor && table_list->ancestor->next_local); + my_error(ER_VIEW_NO_INSERT_FIELD_LIST, MYF(0), + table_list->view_db.str, table_list->view_name.str); + return -1; + } if (values.elements != table->fields) { my_printf_error(ER_WRONG_VALUE_COUNT_ON_ROW, @@ -97,6 +113,23 @@ check_insert_fields(THD *thd, TABLE_LIST *table_list, List<Item> &fields, thd->lex->select_lex.no_wrap_view_item= 0; if (res) return -1; + if (table == 0) + { + /* it is join view => we need to find table for update */ + List_iterator_fast<Item> it(fields); + Item *item; + TABLE_LIST *tbl= 0; + table_map map= 0; + while (item= it++) + map|= item->used_tables(); + if (table_list->check_single_table(&tbl, map) || tbl == 0) + { + my_error(ER_VIEW_MULTIUPDATE, MYF(0), + table_list->view_db.str, table_list->view_name.str); + return -1; + } + table_list->table= table= tbl->table; + } if (check_unique && thd->dupp_field) { @@ -111,6 +144,15 @@ check_insert_fields(THD *thd, TABLE_LIST *table_list, List<Item> &fields, #ifndef NO_EMBEDDED_ACCESS_CHECKS table->grant.want_privilege=(SELECT_ACL & ~table->grant.privilege); #endif + + if (check_key_in_view(thd, table_list) || + (table_list->view && + check_view_insertability(table_list, thd->query_id))) + { + my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias, "INSERT"); + return -1; + } + return 0; } @@ -134,7 +176,7 @@ int mysql_insert(THD *thd,TABLE_LIST *table_list, ulong counter = 1; ulonglong id; COPY_INFO info; - TABLE *table; + TABLE *table= 0; List_iterator_fast<List_item> its(values_list); List_item *values; #ifndef EMBEDDED_LIBRARY @@ -201,17 +243,14 @@ int mysql_insert(THD *thd,TABLE_LIST *table_list, if (res || thd->is_fatal_error) DBUG_RETURN(-1); - table= table_list->table; thd->proc_info="init"; thd->used_tables=0; values= its++; - if (duplic == DUP_UPDATE && !table->insert_values) + if (duplic == DUP_UPDATE) { /* it should be allocated before Item::fix_fields() */ - table->insert_values= - (byte *)alloc_root(&thd->mem_root, table->rec_buff_length); - if (!table->insert_values) + if (table_list->set_insert_values(&thd->mem_root)) goto abort; } @@ -219,6 +258,9 @@ int mysql_insert(THD *thd,TABLE_LIST *table_list, update_fields, update_values, duplic)) goto abort; + /* mysql_prepare_insert set table_list->table if it was not set */ + table= table_list->table; + // is table which we are changing used somewhere in other parts of query value_count= values->elements; while ((values= its++)) @@ -437,7 +479,7 @@ int mysql_insert(THD *thd,TABLE_LIST *table_list, ::send_ok(thd, (ulong) thd->row_count_func, id, buff); } free_underlaid_joins(thd, &thd->lex->select_lex); - table->insert_values=0; + table_list->clear_insert_values(); DBUG_RETURN(0); abort: @@ -446,7 +488,7 @@ abort: end_delayed_insert(thd); #endif free_underlaid_joins(thd, &thd->lex->select_lex); - table->insert_values=0; + table_list->clear_insert_values(); DBUG_RETURN(-1); } @@ -542,11 +584,12 @@ static bool check_view_insertability(TABLE_LIST *view, ulong query_id) where Pointer to where clause RETURN - 0 ok - 1 ERROR + 0 ok + 1 ERROR and message sent to client + -1 ERROR but message is not sent to client */ -static bool mysql_prepare_insert_check_table(THD *thd, TABLE_LIST *table_list, +static int mysql_prepare_insert_check_table(THD *thd, TABLE_LIST *table_list, List<Item> &fields, COND **where) { bool insert_into_view= (table_list->view != 0); @@ -554,22 +597,22 @@ static bool mysql_prepare_insert_check_table(THD *thd, TABLE_LIST *table_list, if (setup_tables(thd, table_list, where, &thd->lex->select_lex.leaf_tables, 0)) - DBUG_RETURN(1); + DBUG_RETURN(thd->net.report_error ? -1 : 1); if (insert_into_view && !fields.elements) { thd->lex->empty_field_list_on_rset= 1; - insert_view_fields(&fields, table_list); + if (!table_list->table) + { + DBUG_ASSERT(table_list->view && + table_list->ancestor && table_list->ancestor->next_local); + my_error(ER_VIEW_NO_INSERT_FIELD_LIST, MYF(0), + table_list->view_db.str, table_list->view_name.str); + DBUG_RETURN(-1); + } + DBUG_RETURN(insert_view_fields(&fields, table_list)); } - if (!table_list->updatable || - check_key_in_view(thd, table_list) || - (insert_into_view && - check_view_insertability(table_list, thd->query_id))) - { - my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias, "INSERT"); - DBUG_RETURN(1); - } DBUG_RETURN(0); } @@ -598,8 +641,9 @@ int mysql_prepare_insert(THD *thd, TABLE_LIST *table_list, TABLE *table, int res; DBUG_ENTER("mysql_prepare_insert"); - if (mysql_prepare_insert_check_table(thd, table_list, fields, &unused_conds)) - DBUG_RETURN(-1); + if ((res= mysql_prepare_insert_check_table(thd, table_list, + fields, &unused_conds))) + DBUG_RETURN(res); if (check_insert_fields(thd, table_list, fields, *values, 1, !insert_into_view) || @@ -1607,21 +1651,43 @@ bool delayed_insert::handle_inserts(void) RETURN 0 OK - -1 Error + 1 Error sent to client + -1 Error is not sent to client */ int mysql_insert_select_prepare(THD *thd) { LEX *lex= thd->lex; + TABLE_LIST* first_select_table= + (TABLE_LIST*)lex->select_lex.table_list.first; + TABLE_LIST* first_select_leaf_table; + int res; DBUG_ENTER("mysql_insert_select_prepare"); - if (mysql_prepare_insert_check_table(thd, lex->query_tables, - lex->field_list, - &lex->select_lex.where)) - DBUG_RETURN(-1); - /* exclude first table from leaf tables list, because it belong to INSERT */ + if ((res= mysql_prepare_insert_check_table(thd, lex->query_tables, + lex->field_list, + &lex->select_lex.where))) + DBUG_RETURN(res); + /* + setup was done in mysql_insert_select_prepare, but we have to mark + first local table + */ + if (first_select_table) + first_select_table->setup_is_done= 1; + /* + exclude first table from leaf tables list, because it belong to + INSERT + */ DBUG_ASSERT(lex->select_lex.leaf_tables); lex->leaf_tables_insert= lex->select_lex.leaf_tables; - lex->select_lex.leaf_tables= lex->select_lex.leaf_tables->next_leaf; + /* skip all leaf tables belonged to view where we are insert */ + for (first_select_leaf_table= lex->select_lex.leaf_tables->next_leaf; + first_select_leaf_table && + first_select_leaf_table->belong_to_view && + first_select_leaf_table->belong_to_view == + lex->leaf_tables_insert->belong_to_view; + first_select_leaf_table= first_select_leaf_table->next_leaf) + {} + lex->select_lex.leaf_tables= first_select_leaf_table; DBUG_RETURN(0); } @@ -1635,6 +1701,23 @@ select_insert::prepare(List<Item> &values, SELECT_LEX_UNIT *u) if (check_insert_fields(thd, table_list, *fields, values, 1, !insert_into_view)) DBUG_RETURN(1); + /* + if it is INSERT into join view then check_insert_fields already found + real table for insert + */ + table= table_list->table; + + /* + Is table which we are changing used somewhere in other parts of + query + */ + if (!(thd->lex->current_select->options & OPTION_BUFFER_RESULT) && + unique_table(table_list, table_list->next_independent())) + { + /* Using same table for INSERT and SELECT */ + thd->lex->current_select->options|= OPTION_BUFFER_RESULT; + thd->lex->current_select->join->select_options|= OPTION_BUFFER_RESULT; + } restore_record(table,default_values); // Get empty record table->next_number_field=table->found_next_number_field; diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 115dda49666..f9a6275abeb 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -2747,18 +2747,29 @@ unsent_create_error: lex->duplicates); if (thd->net.report_error) res= -1; - break; + if (res != 2) + break; case SQLCOM_UPDATE_MULTI: { + bool converted= 0; DBUG_ASSERT(first_table == all_tables && first_table != 0); - if ((res= multi_update_precheck(thd, all_tables))) - break; + if (res != 2) + { + if ((res= multi_update_precheck(thd, all_tables))) + break; + } + else + { + res= 0; + converted= 1; + } res= mysql_multi_update(thd, all_tables, &select_lex->item_list, &lex->value_list, select_lex->where, select_lex->options, - lex->duplicates, unit, select_lex); + lex->duplicates, unit, select_lex, + converted); break; } case SQLCOM_REPLACE: @@ -2796,31 +2807,26 @@ unsent_create_error: 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())) + /* Skip first table, which is the table we are inserting in */ + lex->select_lex.table_list.first= (byte*)first_table->next_local; + + res= mysql_insert_select_prepare(thd); + if (!res && (result= new select_insert(first_table, first_table->table, + &lex->field_list, + lex->duplicates))) { - /* Using same table for INSERT and SELECT */ - select_lex->options |= OPTION_BUFFER_RESULT; - } + TABLE_LIST *first_select_table; - if ((res= mysql_insert_select_prepare(thd))) - break; - if ((result= new select_insert(first_table, first_table->table, - &lex->field_list, lex->duplicates))) - /* Skip first table, which is the table we are inserting in */ - lex->select_lex.table_list.first= (byte*) first_table->next_local; /* insert/replace from SELECT give its SELECT_LEX for SELECT, and item_list belong to SELECT */ lex->select_lex.resolve_mode= SELECT_LEX::SELECT_MODE; res= handle_select(thd, lex, result); - /* revert changes for SP */ - lex->select_lex.table_list.first= (byte*) first_table; lex->select_lex.resolve_mode= SELECT_LEX::INSERT_MODE; + } + /* revert changes for SP */ + lex->select_lex.table_list.first= (byte*) first_table; if (thd->net.report_error) res= -1; } @@ -2884,6 +2890,16 @@ unsent_create_error: 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= -1; + break; + } + if ((res= mysql_multi_delete_prepare(thd))) break; diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index 99510d900bc..12a69c359bb 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -959,6 +959,7 @@ error: RETURN VALUE 0 success + 2 convert to multi_update 1 error, sent to client -1 error, not sent to client */ @@ -975,6 +976,15 @@ static int mysql_test_update(Prepared_statement *stmt, if (!(res=open_and_lock_tables(thd, table_list))) { + if (table_list->table == 0) + { + DBUG_ASSERT(table_list->view && + table_list->ancestor && table_list->ancestor->next_local); + stmt->lex->sql_command= SQLCOM_UPDATE_MULTI; + DBUG_PRINT("info", ("Switch to multi-update (command replaced)")); + /* convert to multiupdate */ + return 2; + } if (!(res= mysql_prepare_update(thd, table_list, &select->where, select->order_list.elements, @@ -1027,6 +1037,15 @@ static int mysql_test_delete(Prepared_statement *stmt, if (!(res=open_and_lock_tables(thd, table_list))) { + if (!table_list->table) + { + DBUG_ASSERT(table_list->view && + table_list->ancestor && table_list->ancestor->next_local); + my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0), + table_list->view_db.str, table_list->view_name.str); + DBUG_RETURN(-1); + } + res= mysql_prepare_delete(thd, table_list, &lex->select_lex.where); lex->unit.cleanup(); } @@ -1219,7 +1238,10 @@ static int select_like_statement_test(Prepared_statement *stmt, LEX *lex= stmt->lex; int res= 0; - if (tables && (res= open_and_lock_tables(thd, tables))) + /* check that tables was not opened during conversion from usual update */ + if (tables && + (!tables->table && !tables->view) && + (res= open_and_lock_tables(thd, tables))) goto end; if (specific_prepare && (res= (*specific_prepare)(thd))) @@ -1285,6 +1307,7 @@ static int mysql_test_create_table(Prepared_statement *stmt) mysql_test_multiupdate() stmt prepared statemen handler tables list of tables queries + converted converted to multi-update from usual update RETURN VALUE 0 success @@ -1292,10 +1315,11 @@ static int mysql_test_create_table(Prepared_statement *stmt) -1 error, not sent to client */ static int mysql_test_multiupdate(Prepared_statement *stmt, - TABLE_LIST *tables) + TABLE_LIST *tables, + bool converted) { int res; - if ((res= multi_update_precheck(stmt->thd, tables))) + if (!converted && (res= multi_update_precheck(stmt->thd, tables))) return res; return select_like_statement_test(stmt, tables, &mysql_multi_update_prepare); } @@ -1325,7 +1349,19 @@ static int mysql_test_multidelete(Prepared_statement *stmt, uint fake_counter; if ((res= multi_delete_precheck(stmt->thd, tables, &fake_counter))) return res; - return select_like_statement_test(stmt, tables, &mysql_multi_delete_prepare); + if ((res= select_like_statement_test(stmt, tables, + &mysql_multi_delete_prepare))) + return res; + if (!tables->table) + { + DBUG_ASSERT(tables->view && + tables->ancestor && tables->ancestor->next_local); + my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0), + tables->view_db.str, tables->view_name.str); + return -1; + } + return 0; + } @@ -1404,6 +1440,11 @@ static int send_prepare_results(Prepared_statement *stmt, bool text_protocol) case SQLCOM_UPDATE: res= mysql_test_update(stmt, tables); + if (res != 2) + break; + + case SQLCOM_UPDATE_MULTI: + res= mysql_test_multiupdate(stmt, tables, res == 2); break; case SQLCOM_DELETE: @@ -1431,10 +1472,6 @@ static int send_prepare_results(Prepared_statement *stmt, bool text_protocol) case SQLCOM_DELETE_MULTI: res= mysql_test_multidelete(stmt, tables); break; - - case SQLCOM_UPDATE_MULTI: - res= mysql_test_multiupdate(stmt, tables); - break; case SQLCOM_INSERT_SELECT: res= mysql_test_insert_select(stmt, tables); diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 79f3950e21f..1e8cb73fa92 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -87,6 +87,28 @@ static bool check_fields(THD *thd, List<Item> &items) } +/* + Process usual UPDATE + + SYNOPSIS + mysql_update() + thd thread handler + fields fields for update + values values of fields for update + conds WHERE clause expression + order_num number of elemen in ORDER BY clause + order ORDER BY clause list + limit limit clause + handle_duplicates how to handle duplicates + + RETURN + 0 - OK + 2 - privilege check and openning table passed, but we need to convert to + multi-update because of view substitution + 1 - error and error sent to client + -1 - error and error is not sent to client +*/ + int mysql_update(THD *thd, TABLE_LIST *table_list, List<Item> &fields, @@ -96,7 +118,7 @@ int mysql_update(THD *thd, ha_rows limit, enum enum_duplicates handle_duplicates) { - bool using_limit=limit != HA_POS_ERROR; + bool using_limit=limit != HA_POS_ERROR; bool safe_update= thd->options & OPTION_SAFE_UPDATES; bool used_key_is_modified, transactional_table, log_delayed; int error=0; @@ -117,6 +139,15 @@ int mysql_update(THD *thd, if ((error= open_and_lock_tables(thd, table_list))) DBUG_RETURN(error); + + if (table_list->table == 0) + { + DBUG_ASSERT(table_list->view && + table_list->ancestor && table_list->ancestor->next_local); + DBUG_PRINT("info", ("Switch to multi-update")); + /* convert to multiupdate */ + return 2; + } thd->proc_info="init"; table= table_list->table; table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK); @@ -626,12 +657,30 @@ int mysql_multi_update_prepare(THD *thd) if (tables_for_update & readonly_tables) { // find readonly table/view which cause error - for (tl= leaves; tl; tl= tl->next_local) + for (tl= leaves; tl; tl= tl->next_leaf) { if ((readonly_tables & tl->table->map) && (tables_for_update & tl->table->map)) { - my_error(ER_NON_UPDATABLE_TABLE, MYF(0), tl->alias, "UPDATE"); + TABLE_LIST *table= tl->belong_to_view ? tl->belong_to_view : tl; + my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table->alias, "UPDATE"); + DBUG_RETURN(-1); + } + } + } + + /* check single table update for view compound from several tables */ + for (tl= table_list; tl; tl= tl->next_local) + { + if (tl->table == 0) + { + DBUG_ASSERT(tl->view && + tl->ancestor && tl->ancestor->next_local); + TABLE_LIST *for_update= 0; + if (tl->check_single_table(&for_update, tables_for_update)) + { + my_error(ER_VIEW_MULTIUPDATE, MYF(0), + tl->view_db.str, tl->view_name.str); DBUG_RETURN(-1); } } @@ -647,19 +696,22 @@ int mysql_multi_update(THD *thd, COND *conds, ulong options, enum enum_duplicates handle_duplicates, - SELECT_LEX_UNIT *unit, SELECT_LEX *select_lex) + SELECT_LEX_UNIT *unit, SELECT_LEX *select_lex, + bool converted) { - int res; + int res= 0; multi_update *result; DBUG_ENTER("mysql_multi_update"); - if ((res= open_and_lock_tables(thd, table_list))) + if (!converted && (res= open_and_lock_tables(thd, table_list))) DBUG_RETURN(res); if ((res= mysql_multi_update_prepare(thd))) DBUG_RETURN(res); - if (!(result= new multi_update(thd, table_list, fields, values, + if (!(result= new multi_update(thd, table_list, + thd->lex->select_lex.leaf_tables, + fields, values, handle_duplicates))) DBUG_RETURN(-1); @@ -677,12 +729,14 @@ int mysql_multi_update(THD *thd, multi_update::multi_update(THD *thd_arg, TABLE_LIST *table_list, + TABLE_LIST *leaves_list, List<Item> *field_list, List<Item> *value_list, enum enum_duplicates handle_duplicates_arg) - :all_tables(table_list), update_tables(0), thd(thd_arg), tmp_tables(0), - updated(0), found(0), fields(field_list), values(value_list), - table_count(0), copy_field(0), handle_duplicates(handle_duplicates_arg), - do_update(1), trans_safe(0), transactional_tables(1) + :all_tables(table_list), leaves(leaves_list), update_tables(0), + thd(thd_arg), tmp_tables(0), updated(0), found(0), fields(field_list), + values(value_list), table_count(0), copy_field(0), + handle_duplicates(handle_duplicates_arg), do_update(1), trans_safe(0), + transactional_tables(1) {} @@ -730,7 +784,7 @@ int multi_update::prepare(List<Item> ¬_used_values, */ update.empty(); - for (table_ref= all_tables; table_ref; table_ref= table_ref->next_local) + for (table_ref= leaves; table_ref; table_ref= table_ref->next_leaf) { /* TODO: add support of view of join support */ TABLE *table=table_ref->table; @@ -800,7 +854,7 @@ int multi_update::prepare(List<Item> ¬_used_values, which will cause an error when reading a row. (This issue is mostly relevent for MyISAM tables) */ - for (table_ref= all_tables; table_ref; table_ref= table_ref->next_local) + for (table_ref= leaves; table_ref; table_ref= table_ref->next_leaf) { TABLE *table=table_ref->table; if (!(tables_to_update & table->map) && diff --git a/sql/sql_view.cc b/sql/sql_view.cc index 12923af0ecf..3e950f6d3a0 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -465,23 +465,27 @@ static int mysql_register_view(THD *thd, TABLE_LIST *view, if ((view->updatable_view= (can_be_merged && view->algorithm != VIEW_ALGORITHM_TMPTABLE))) { - if (thd->lex->select_lex.table_list.elements > 1) - view->updatable_view= 0; - else + // TODO: change here when we will support UNIONs + for (TABLE_LIST *tbl= (TABLE_LIST *)thd->lex->select_lex.table_list.first; + tbl; + tbl= tbl->next_local) { - // TODO: change here when we will support UNIONs - for (TABLE_LIST *tbl= (TABLE_LIST *)thd->lex->select_lex.table_list.first; - tbl; - tbl= tbl->next_local) + if (tbl->view && !tbl->updatable_view) { - if (tbl->view && !tbl->updatable_view) - { - view->updatable_view= 0; - break; - } + view->updatable_view= 0; + break; + } + for (TABLE_LIST *up= tbl; up; up= up->embedding) + { + if (up->outer_join) + { + view->updatable_view= 0; + goto loop_out; + } } } } +loop_out: if (sql_create_definition_file(&dir, &file, view_file_type, (gptr)view, view_parameters, 3)) { @@ -701,9 +705,6 @@ mysql_make_view(File_parser *parser, TABLE_LIST *table) old_lex->can_use_merged()) && !old_lex->can_not_use_merged()) { - /* - TODO: support multi tables substitutions - */ /* lex should contain at least one table */ DBUG_ASSERT(view_tables != 0); @@ -736,7 +737,6 @@ mysql_make_view(File_parser *parser, TABLE_LIST *table) /* multi table view */ if (view_tables->next_local) { - table->updatable= 0; /* make nested join structure for view tables */ NESTED_JOIN *nested_join; if (!(nested_join= table->nested_join= @@ -919,14 +919,16 @@ bool check_key_in_view(THD *thd, TABLE_LIST *view) uint i, elements_in_view; DBUG_ENTER("check_key_in_view"); - if (!view->view) + if (!view->view && !view->belong_to_view) DBUG_RETURN(FALSE); /* it is normal table */ table= view->table; + if (view->belong_to_view) + view= view->belong_to_view; trans= view->field_translation; key_info_end= (key_info= table->key_info)+ table->keys; elements_in_view= view->view->select_lex.item_list.elements; - DBUG_ASSERT(view->table != 0 && view->field_translation != 0); + DBUG_ASSERT(table != 0 && view->field_translation != 0); /* Loop over all keys to see if a unique-not-null key is used */ for (;key_info != key_info_end ; key_info++) @@ -1002,21 +1004,30 @@ bool check_key_in_view(THD *thd, TABLE_LIST *view) insert_view_fields() list list for insertion view view for processing + + RETURN + 0 - OK + -1 - error (is not sent to cliet) */ -void insert_view_fields(List<Item> *list, TABLE_LIST *view) +int insert_view_fields(List<Item> *list, TABLE_LIST *view) { uint elements_in_view= view->view->select_lex.item_list.elements; Field_translator *trans; DBUG_ENTER("insert_view_fields"); if (!(trans= view->field_translation)) - DBUG_VOID_RETURN; + DBUG_RETURN(0); for (uint i= 0; i < elements_in_view; i++) { if (trans[i].item->type() == Item::FIELD_ITEM) list->push_back(trans[i].item); + else + { + my_error(ER_NON_UPDATABLE_TABLE, MYF(0), view->alias, "INSERT"); + DBUG_RETURN(-1); + } } - DBUG_VOID_RETURN; + DBUG_RETURN(0); } diff --git a/sql/sql_view.h b/sql/sql_view.h index 431f82a5bb8..e76cf13a4c4 100644 --- a/sql/sql_view.h +++ b/sql/sql_view.h @@ -25,7 +25,7 @@ int mysql_drop_view(THD *thd, TABLE_LIST *view, enum_drop_mode drop_mode); bool check_key_in_view(THD *thd, TABLE_LIST * view); -void insert_view_fields(List<Item> *list, TABLE_LIST *view); +int insert_view_fields(List<Item> *list, TABLE_LIST *view); frm_type_enum mysql_frm_type(char *path); diff --git a/sql/table.cc b/sql/table.cc index 851e99ef4f6..40d20fadec3 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -1744,6 +1744,95 @@ err: } +/* + Find table in underlaying tables by mask and check that only this + table sbelong to given mask + + SYNOPSIS + st_table_list::check_single_table() + table reference on variable where to store found table + (should be 0 on call, to find table, or point to table for + unique test) + map bit mask of tables + + RETURN + 0 table not found or found only one + 1 found several tables +*/ + +bool st_table_list::check_single_table(st_table_list **table, table_map map) +{ + for (TABLE_LIST *tbl= ancestor; tbl; tbl= tbl->next_local) + { + if (tbl->table) + { + if (tbl->table->map & map) + { + if (*table) + return 1; + else + *table= tbl; + } + } + else + if (tbl->check_single_table(table, map)) + return 1; + } +} + + +/* + Set insert_values buffer + + SYNOPSIS + set_insert_values() + mem_root memory pool for allocating + + RETURN + FALSE - OK + TRUE - out of memory +*/ + +bool st_table_list::set_insert_values(MEM_ROOT *mem_root) +{ + if (table) + { + if (!table->insert_values && + !(table->insert_values= (byte *)alloc_root(mem_root, + table->rec_buff_length))) + return TRUE; + } + else + { + DBUG_ASSERT(view && ancestor && ancestor->next_local); + for (TABLE_LIST *tbl= ancestor; tbl; tbl= tbl->next_local) + if (tbl->set_insert_values(mem_root)) + return TRUE; + } + return FALSE; +} + + +/* + clear insert_values reference + + SYNOPSIS + clear_insert_values() +*/ + +void st_table_list::clear_insert_values() +{ + if (table) + table->insert_values= 0; + else + { + DBUG_ASSERT(view && ancestor && ancestor->next_local); + for (TABLE_LIST *tbl= ancestor; tbl; tbl= tbl->next_local) + tbl->clear_insert_values(); + } +} + + void Field_iterator_view::set(TABLE_LIST *table) { ptr= table->field_translation; diff --git a/sql/table.h b/sql/table.h index b5139919889..5f6222f3343 100644 --- a/sql/table.h +++ b/sql/table.h @@ -277,6 +277,9 @@ typedef struct st_table_list void print(THD *thd, String *str); void save_and_clear_want_privilege(); void restore_want_privilege(); + bool check_single_table(st_table_list **table, table_map map); + bool set_insert_values(MEM_ROOT *mem_root); + void clear_insert_values(); inline st_table_list *next_independent() { if (view) |