diff options
Diffstat (limited to 'mysql-test/r/view.result')
| -rw-r--r-- | mysql-test/r/view.result | 389 |
1 files changed, 336 insertions, 53 deletions
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index ca1a1dfa48a..8ab215fd99a 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -253,7 +253,7 @@ drop view v1; drop table t1; create table t1 (a int); create view v1 as select distinct a from t1 WITH CHECK OPTION; -ERROR HY000: CHECK OPTION on non-updatable view 'test.v1' +ERROR HY000: CHECK OPTION on non-updatable view `test`.`v1` create view v1 as select a from t1 WITH CHECK OPTION; create view v2 as select a from t1 WITH CASCADED CHECK OPTION; create view v3 as select a from t1 WITH LOCAL CHECK OPTION; @@ -930,7 +930,7 @@ create table t1 (a int); create view v1 as select a from t1 procedure analyse(); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'procedure analyse()' at line 1 create view v1 as select 1 from (select 1) as d1; -ERROR HY000: View's SELECT contains a subquery in the FROM clause +drop view v1; drop table t1; create table t1 (s1 int, primary key (s1)); create view v1 as select * from t1; @@ -1134,11 +1134,11 @@ create table t1 (a int); create view v1 as select * from t1 where a < 2 with check option; insert into v1 values(1); insert into v1 values(3); -ERROR HY000: CHECK OPTION failed 'test.v1' +ERROR 44000: CHECK OPTION failed `test`.`v1` insert ignore into v1 values (2),(3),(0); Warnings: -Warning 1369 CHECK OPTION failed 'test.v1' -Warning 1369 CHECK OPTION failed 'test.v1' +Warning 1369 CHECK OPTION failed `test`.`v1` +Warning 1369 CHECK OPTION failed `test`.`v1` select * from t1; a 1 @@ -1146,20 +1146,20 @@ a delete from t1; insert into v1 SELECT 1; insert into v1 SELECT 3; -ERROR HY000: CHECK OPTION failed 'test.v1' +ERROR 44000: CHECK OPTION failed `test`.`v1` create table t2 (a int); insert into t2 values (2),(3),(0); insert ignore into v1 SELECT a from t2; Warnings: -Warning 1369 CHECK OPTION failed 'test.v1' -Warning 1369 CHECK OPTION failed 'test.v1' +Warning 1369 CHECK OPTION failed `test`.`v1` +Warning 1369 CHECK OPTION failed `test`.`v1` select * from t1 order by a desc; a 1 0 update v1 set a=-1 where a=0; update v1 set a=2 where a=1; -ERROR HY000: CHECK OPTION failed 'test.v1' +ERROR 44000: CHECK OPTION failed `test`.`v1` select * from t1 order by a desc; a 1 @@ -1174,7 +1174,7 @@ a update v1 set a=a+1; update ignore v1,t2 set v1.a=v1.a+1 where v1.a=t2.a; Warnings: -Warning 1369 CHECK OPTION failed 'test.v1' +Warning 1369 CHECK OPTION failed `test`.`v1` select * from t1; a 1 @@ -1188,12 +1188,12 @@ create view v3 as select * from v1 where a > 0 with cascaded check option; insert into v2 values (1); insert into v3 values (1); insert into v2 values (0); -ERROR HY000: CHECK OPTION failed 'test.v2' +ERROR 44000: CHECK OPTION failed `test`.`v2` insert into v3 values (0); -ERROR HY000: CHECK OPTION failed 'test.v3' +ERROR 44000: CHECK OPTION failed `test`.`v3` insert into v2 values (2); insert into v3 values (2); -ERROR HY000: CHECK OPTION failed 'test.v3' +ERROR 44000: CHECK OPTION failed `test`.`v3` select * from t1; a 1 @@ -1205,10 +1205,10 @@ create table t1 (a int, primary key (a)); create view v1 as select * from t1 where a < 2 with check option; insert into v1 values (1) on duplicate key update a=2; insert into v1 values (1) on duplicate key update a=2; -ERROR HY000: CHECK OPTION failed 'test.v1' +ERROR 44000: CHECK OPTION failed `test`.`v1` insert ignore into v1 values (1) on duplicate key update a=2; Warnings: -Warning 1369 CHECK OPTION failed 'test.v1' +Warning 1369 CHECK OPTION failed `test`.`v1` select * from t1; a 1 @@ -1263,7 +1263,7 @@ s1 alter view v2 as select * from t2 where s1 in (select s1 from t1) with check option; insert into v2 values (5); update v2 set s1 = 1; -ERROR HY000: CHECK OPTION failed 'test.v2' +ERROR 44000: CHECK OPTION failed `test`.`v2` insert into t1 values (1); update v2 set s1 = 1; select * from v2; @@ -1300,16 +1300,16 @@ create table t1 (s1 tinyint); create view v1 as select * from t1 where s1 <> 0 with local check option; create view v2 as select * from v1 with cascaded check option; insert into v2 values (0); -ERROR HY000: CHECK OPTION failed 'test.v2' +ERROR 44000: CHECK OPTION failed `test`.`v2` drop view v2, v1; drop table t1; create table t1 (s1 int); create view v1 as select * from t1 where s1 < 5 with check option; insert ignore into v1 values (6); -ERROR HY000: CHECK OPTION failed 'test.v1' +ERROR 44000: CHECK OPTION failed `test`.`v1` insert ignore into v1 values (6),(3); Warnings: -Warning 1369 CHECK OPTION failed 'test.v1' +Warning 1369 CHECK OPTION failed `test`.`v1` select * from t1; s1 3 @@ -1319,7 +1319,7 @@ create table t1 (s1 tinyint); create trigger t1_bi before insert on t1 for each row set new.s1 = 500; create view v1 as select * from t1 where s1 <> 127 with check option; insert into v1 values (0); -ERROR HY000: CHECK OPTION failed 'test.v1' +ERROR 44000: CHECK OPTION failed `test`.`v1` select * from v1; s1 select * from t1; @@ -1331,7 +1331,7 @@ create table t1 (s1 tinyint); create view v1 as select * from t1 where s1 <> 0; create view v2 as select * from v1 where s1 <> 1 with cascaded check option; insert into v2 values (0); -ERROR HY000: CHECK OPTION failed 'test.v2' +ERROR 44000: CHECK OPTION failed `test`.`v2` select * from v2; s1 select * from t1; @@ -1341,7 +1341,7 @@ drop table t1; create table t1 (a int, b char(10)); create view v1 as select * from t1 where a != 0 with check option; load data infile '../../std_data/loaddata3.dat' into table v1 fields terminated by '' enclosed by '' ignore 1 lines; -ERROR HY000: CHECK OPTION failed 'test.v1' +ERROR 44000: CHECK OPTION failed `test`.`v1` select * from t1; a b 1 row 1 @@ -1356,10 +1356,10 @@ Warnings: Note 1265 Data truncated for column 'a' at row 1 Note 1265 Data truncated for column 'a' at row 2 Warning 1366 Incorrect integer value: 'error ' for column 'a' at row 3 -Warning 1369 CHECK OPTION failed 'test.v1' +Warning 1369 CHECK OPTION failed `test`.`v1` Note 1265 Data truncated for column 'a' at row 3 Warning 1366 Incorrect integer value: 'wrong end ' for column 'a' at row 4 -Warning 1369 CHECK OPTION failed 'test.v1' +Warning 1369 CHECK OPTION failed `test`.`v1` select * from t1 order by a,b; a b 1 row 1 @@ -1375,7 +1375,7 @@ drop table t1; create table t1 (a text, b text); create view v1 as select * from t1 where a <> 'Field A' with check option; load data infile '../../std_data/loaddata2.dat' into table v1 fields terminated by ',' enclosed by ''''; -ERROR HY000: CHECK OPTION failed 'test.v1' +ERROR 44000: CHECK OPTION failed `test`.`v1` select concat('|',a,'|'), concat('|',b,'|') from t1; concat('|',a,'|') concat('|',b,'|') select concat('|',a,'|'), concat('|',b,'|') from v1; @@ -1383,7 +1383,7 @@ concat('|',a,'|') concat('|',b,'|') delete from t1; load data infile '../../std_data/loaddata2.dat' ignore into table v1 fields terminated by ',' enclosed by ''''; Warnings: -Warning 1369 CHECK OPTION failed 'test.v1' +Warning 1369 CHECK OPTION failed `test`.`v1` Warning 1261 Row 2 doesn't contain data for all columns select concat('|',a,'|'), concat('|',b,'|') from t1; concat('|',a,'|') concat('|',b,'|') @@ -1494,7 +1494,7 @@ a create view v2 (a,b) as select t1.b as a, t2.a as b from t1, t2; set updatable_views_with_limit=NO; update v2 set a= 10 where a=200 limit 1; -ERROR HY000: The target table t1 of the UPDATE is not updatable +ERROR HY000: The target table v2 of the UPDATE is not updatable set updatable_views_with_limit=DEFAULT; select * from v3; a b @@ -3047,9 +3047,9 @@ CREATE VIEW v1 AS SELECT id, val FROM t1 WHERE val >= 1 AND val <= 5 WITH CHECK INSERT INTO v1 (val) VALUES (2); INSERT INTO v1 (val) VALUES (4); INSERT INTO v1 (val) VALUES (6); -ERROR HY000: CHECK OPTION failed 'test.v1' +ERROR 44000: CHECK OPTION failed `test`.`v1` UPDATE v1 SET val=6 WHERE id=2; -ERROR HY000: CHECK OPTION failed 'test.v1' +ERROR 44000: CHECK OPTION failed `test`.`v1` DROP VIEW v1; DROP TABLE t1; DROP VIEW IF EXISTS v1, v2; @@ -3135,7 +3135,7 @@ b 1 2 UPDATE v1 SET b=3; -ERROR HY000: CHECK OPTION failed 'test.v1' +ERROR 44000: CHECK OPTION failed `test`.`v1` SELECT * FROM v1; b 1 @@ -3209,15 +3209,11 @@ code COUNT(DISTINCT country) DROP VIEW v1; DROP TABLE t1; DROP VIEW IF EXISTS v1; -SELECT * FROM (SELECT 1) AS t; -1 -1 -CREATE VIEW v1 AS SELECT * FROM (SELECT 1) AS t; -ERROR HY000: View's SELECT contains a subquery in the FROM clause +SELECT * FROM (SELECT 1) AS t into @w; +CREATE VIEW v1 AS SELECT * FROM (SELECT 1) AS t into @w; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'into @w' at line 1 # Previously the following would fail. -SELECT * FROM (SELECT 1) AS t; -1 -1 +SELECT * FROM (SELECT 1) AS t into @w; drop view if exists view_24532_a; drop view if exists view_24532_b; drop table if exists table_24532; @@ -3471,14 +3467,14 @@ a1 c 1 0 2 0 UPDATE v1 SET c=3; -ERROR HY000: CHECK OPTION failed 'test.v1' +ERROR 44000: CHECK OPTION failed `test`.`v1` PREPARE t FROM 'UPDATE v1 SET c=3'; EXECUTE t; -ERROR HY000: CHECK OPTION failed 'test.v1' +ERROR 44000: CHECK OPTION failed `test`.`v1` EXECUTE t; -ERROR HY000: CHECK OPTION failed 'test.v1' +ERROR 44000: CHECK OPTION failed `test`.`v1` INSERT INTO v1(a1, c) VALUES (3, 3); -ERROR HY000: CHECK OPTION failed 'test.v1' +ERROR 44000: CHECK OPTION failed `test`.`v1` UPDATE v1 SET c=1 WHERE a1=1; SELECT * FROM v1; a1 c @@ -3497,14 +3493,14 @@ a1 c 1 1 2 0 UPDATE v2 SET c=3; -ERROR HY000: CHECK OPTION failed 'test.v2' +ERROR 44000: CHECK OPTION failed `test`.`v2` PREPARE t FROM 'UPDATE v2 SET c=3'; EXECUTE t; -ERROR HY000: CHECK OPTION failed 'test.v2' +ERROR 44000: CHECK OPTION failed `test`.`v2` EXECUTE t; -ERROR HY000: CHECK OPTION failed 'test.v2' +ERROR 44000: CHECK OPTION failed `test`.`v2` INSERT INTO v2(a1, c) VALUES (3, 3); -ERROR HY000: CHECK OPTION failed 'test.v2' +ERROR 44000: CHECK OPTION failed `test`.`v2` UPDATE v2 SET c=2 WHERE a1=1; SELECT * FROM v2; a1 c @@ -4100,7 +4096,7 @@ ERROR HY000: The target table v1 of the INSERT is not insertable-into DELETE from v1; ERROR HY000: The target table v1 of the DELETE is not updatable UPDATE v3 SET b= 10; -ERROR HY000: The target table v2 of the UPDATE is not updatable +ERROR HY000: The target table v3 of the UPDATE is not updatable REPLACE v3 SET b= 10; ERROR HY000: The target table v3 of the INSERT is not insertable-into INSERT into v3(b) values (20); @@ -4408,7 +4404,7 @@ SELECT * FROM v1 WHERE a > -1 OR a > 6 AND a = 3; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 7 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > <cache>(-(1))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > -1) SELECT * FROM v1 WHERE a > -1 OR a AND a = 0; a 2 @@ -4423,7 +4419,7 @@ SELECT * FROM v1 WHERE a > -1 OR a AND a = 0; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 7 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > <cache>(-(1))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > -1) CREATE VIEW v2 AS SELECT * FROM v1; SELECT * FROM v2 WHERE a > -1 OR a AND a = 0; a @@ -4439,7 +4435,7 @@ SELECT * FROM v2 WHERE a > -1 OR a AND a = 0; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 7 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > <cache>(-(1))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > -1) DROP VIEW v1,v2; DROP TABLE t1; CREATE TABLE t1 (a varchar(10), KEY (a)) ; @@ -4750,13 +4746,13 @@ drop view if exists v_9801; create table t_9801 (s1 int); create view v_9801 as select sum(s1) from t_9801 with check option; -ERROR HY000: CHECK OPTION on non-updatable view 'test.v_9801' +ERROR HY000: CHECK OPTION on non-updatable view `test`.`v_9801` create view v_9801 as select sum(s1) from t_9801 group by s1 with check option; -ERROR HY000: CHECK OPTION on non-updatable view 'test.v_9801' +ERROR HY000: CHECK OPTION on non-updatable view `test`.`v_9801` create view v_9801 as select sum(s1) from t_9801 group by s1 with rollup with check option; -ERROR HY000: CHECK OPTION on non-updatable view 'test.v_9801' +ERROR HY000: CHECK OPTION on non-updatable view `test`.`v_9801` drop table t_9801; # # Bug #47335 assert in get_table_share @@ -5922,6 +5918,57 @@ drop table t1; # # Start of 10.2 tests # +# Checking that SHOW CREATE VIEW preserve parentheses +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (10),(20),(30); +CREATE VIEW v1 AS SELECT 1 AS a UNION SELECT a FROM t1; +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `a` union select `t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci +SELECT * FROM v1; +a +1 +10 +20 +30 +DROP VIEW v1; +CREATE VIEW v1 AS SELECT 1 AS a UNION SELECT a FROM t1 LIMIT 1; +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `a` union select `t1`.`a` AS `a` from `t1` limit 1 latin1 latin1_swedish_ci +SELECT * FROM v1; +a +1 +DROP VIEW v1; +CREATE VIEW v1 AS SELECT 1 AS a UNION (SELECT a FROM t1); +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `a` union (select `t1`.`a` AS `a` from `t1`) latin1 latin1_swedish_ci +SELECT * FROM v1; +a +1 +10 +20 +30 +DROP VIEW v1; +CREATE VIEW v1 AS SELECT 1 AS a UNION (SELECT a FROM t1 LIMIT 1); +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `a` union (select `t1`.`a` AS `a` from `t1` limit 1) latin1 latin1_swedish_ci +SELECT * FROM v1; +a +1 +10 +DROP VIEW v1; +CREATE VIEW v1 AS SELECT 1 AS a UNION (SELECT a FROM t1) LIMIT 1; +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `a` union (select `t1`.`a` AS `a` from `t1`) limit 1 latin1 latin1_swedish_ci +SELECT * FROM v1; +a +1 +DROP VIEW v1; +DROP TABLE t1; # # MDEV-9408 CREATE TABLE SELECT MAX(int_column) creates different columns for table vs view # @@ -5953,5 +6000,241 @@ t3 CREATE TABLE `t3` ( DROP VIEW v1; DROP TABLE t1,t2,t3; # +# MDEV-3944: Allow derived tables in VIEWS +# +create table t1 (s1 int); +insert into t1 values (1),(2),(3); +CREATE VIEW v1 AS SELECT * FROM (SELECT s1 FROM t1 WHERE s1>1) AS x; +CREATE VIEW v2 AS SELECT * FROM (SELECT s1 FROM t1 WHERE s1<3) AS x; +select * from v1; +s1 +2 +3 +select * from v2; +s1 +1 +2 +select * from v1 natural join v2; +s1 +2 +select * from v1 natural join t1; +s1 +2 +3 +select * from v1 natural join (SELECT s1 FROM t1 WHERE s1<3) as x; +s1 +2 +select * from v1 left join v2 on (v1.s1=v2.s1); +s1 s1 +2 2 +3 NULL +select * from v1 left join t1 on (v1.s1=t1.s1); +s1 s1 +2 2 +3 3 +select * from t1 left join v2 on (t1.s1=v2.s1); +s1 s1 +1 1 +2 2 +3 NULL +select * from v1 left join (SELECT s1 FROM t1 WHERE s1<3) as x on (v1.s1=x.s1); +s1 s1 +2 2 +3 NULL +select * from (SELECT s1 FROM t1 WHERE s1>1) AS x left join v2 on (x.s1=v2.s1); +s1 s1 +2 2 +3 NULL +drop view v1,v2; +CREATE VIEW v1 AS SELECT * FROM (SELECT s1 FROM (SELECT s1 FROM t1 WHERE s1 +< 100) as xx WHERE s1>1) AS x; +CREATE VIEW v2 AS SELECT * FROM (SELECT s1 FROM (SELECT s1 FROM t1 WHERE s1 +> -100) as xx WHERE s1<3) AS x; +insert into t1 values (200),(-200); +select * from t1; +s1 +-200 +1 +2 +200 +3 +select * from v1; +s1 +2 +3 +select * from v2; +s1 +1 +2 +select * from v1 natural join v2; +s1 +2 +select * from v1 natural join t1; +s1 +2 +3 +select * from v1 natural join (SELECT s1 FROM t1 WHERE s1<3) as x; +s1 +2 +select * from v1 left join v2 on (v1.s1=v2.s1); +s1 s1 +2 2 +3 NULL +select * from v1 left join t1 on (v1.s1=t1.s1); +s1 s1 +2 2 +3 3 +select * from t1 left join v2 on (t1.s1=v2.s1); +s1 s1 +-200 NULL +1 1 +2 2 +200 NULL +3 NULL +select * from v1 left join (SELECT s1 FROM t1 WHERE s1<3) as x on (v1.s1=x.s1); +s1 s1 +2 2 +3 NULL +select * from (SELECT s1 FROM t1 WHERE s1>1) AS x left join v2 on (x.s1=v2.s1); +s1 s1 +2 2 +200 NULL +3 NULL +drop view v1,v2; +CREATE algorithm=temptable VIEW v1 AS SELECT * FROM (SELECT s1 FROM (SELECT s1 FROM t1 WHERE s1 +< 100) as xx WHERE s1>1) AS x; +CREATE algorithm=temptable VIEW v2 AS SELECT * FROM (SELECT s1 FROM (SELECT s1 FROM t1 WHERE s1 +> -100) as xx WHERE s1<3) AS x; +select * from t1; +s1 +-200 +1 +2 +200 +3 +select * from v1; +s1 +2 +3 +select * from v2; +s1 +1 +2 +select * from v1 natural join v2; +s1 +2 +select * from v1 natural join t1; +s1 +2 +3 +select * from v1 natural join (SELECT s1 FROM t1 WHERE s1<3) as x; +s1 +2 +select * from v1 left join v2 on (v1.s1=v2.s1); +s1 s1 +2 2 +3 NULL +select * from v1 left join t1 on (v1.s1=t1.s1); +s1 s1 +2 2 +3 3 +select * from t1 left join v2 on (t1.s1=v2.s1); +s1 s1 +-200 NULL +1 1 +2 2 +200 NULL +3 NULL +select * from v1 left join (SELECT s1 FROM t1 WHERE s1<3) as x on (v1.s1=x.s1); +s1 s1 +2 2 +3 NULL +select * from (SELECT s1 FROM t1 WHERE s1>1) AS x left join v2 on (x.s1=v2.s1); +s1 s1 +2 2 +200 NULL +3 NULL +drop view v1,v2; +CREATE VIEW v1 AS SELECT * FROM (SELECT s1 FROM (SELECT s1 FROM t1 WHERE s1 +< 100) as xx WHERE s1>1) AS x; +insert into v1 values (-300); +ERROR HY000: The target table v1 of the INSERT is not insertable-into +update v1 set s1=s1+1; +ERROR HY000: The target table v1 of the UPDATE is not updatable +drop view v1; +CREATE VIEW v1 AS SELECT s1,s2 FROM (SELECT s1 as s2 FROM t1 WHERE s1 < +100) x, t1 WHERE t1.s1=x.s2; +select * from v1; +s1 s2 +1 1 +2 2 +3 3 +-200 -200 +insert into v1 (s1) values (-300); +update v1 set s1=s1+1; +select * from v1; +s1 s2 +2 2 +3 3 +4 4 +-199 -199 +-299 -299 +select * from t1; +s1 +2 +3 +4 +200 +-199 +-299 +insert into v1(s2) values (-300); +ERROR HY000: The target table v1 of the INSERT is not insertable-into +update v1 set s2=s2+1; +ERROR HY000: The target table v1 of the UPDATE is not updatable +drop view v1; +CREATE VIEW v1 AS SELECT * FROM (SELECT s1 FROM t1 WHERE s1 +< 100) AS x; +insert into v1 values (-300); +ERROR HY000: The target table v1 of the INSERT is not insertable-into +update v1 set s1=s1+1; +ERROR HY000: The target table v1 of the UPDATE is not updatable +drop view v1; +CREATE VIEW v1 AS SELECT * FROM (SELECT s1 FROM (SELECT s1 FROM t1 WHERE s1 +< 100) as xx WHERE s1>1) AS x; +insert into v1 values (-300); +ERROR HY000: The target table v1 of the INSERT is not insertable-into +update v1 set s1=s1+1; +ERROR HY000: The target table v1 of the UPDATE is not updatable +create view v2 as select * from v1; +insert into v2 values (-300); +ERROR HY000: The target table v2 of the INSERT is not insertable-into +update v2 set s1=s1+1; +ERROR HY000: The target table v2 of the UPDATE is not updatable +drop view v1, v2; +drop table t1; +# +# MDEV-9671:Wrong result upon select from a view with a FROM subquery +# +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (3),(2); +CREATE TABLE t2 (j INT); +INSERT INTO t2 VALUES (8),(3),(3); +CREATE TABLE t3 (k INT); +INSERT INTO t3 VALUES (1),(8); +CREATE VIEW v1 AS SELECT * FROM t1 LEFT JOIN ( SELECT t2.* FROM t2 INNER JOIN t3 ON ( k = j ) ) AS alias1 ON ( i = j ); +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`i` AS `i`,`alias1`.`j` AS `j` from (`test`.`t1` left join (select `test`.`t2`.`j` AS `j` from (`test`.`t2` join `test`.`t3` on((`test`.`t3`.`k` = `test`.`t2`.`j`)))) `alias1` on((`test`.`t1`.`i` = `alias1`.`j`))) latin1 latin1_swedish_ci +SELECT * FROM t1 LEFT JOIN ( SELECT t2.* FROM t2 INNER JOIN t3 ON ( k = j ) ) AS alias1 ON ( i = j ); +i j +3 NULL +2 NULL +SELECT * FROM v1; +i j +3 NULL +2 NULL +DROP VIEW v1; +DROP TABLE t1, t2, t3; +# # End of 10.2 tests # |