diff options
Diffstat (limited to 'mysql-test/t/view.test')
| -rw-r--r-- | mysql-test/t/view.test | 223 |
1 files changed, 188 insertions, 35 deletions
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 5a87128f69e..f337bcdaf89 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -353,13 +353,13 @@ create view v3 (x,y,z) as select b, a, b from t1; create view v4 (x,y,z) as select c+1, b, a from t1; create algorithm=temptable view v5 (x,y,z) as select c, b, a from t1; # try insert to VIEW with fields duplicate --- error 1471 +-- error ER_NON_INSERTABLE_TABLE insert into v3 values (-60,4,30); # try insert to VIEW with expression in SELECT list --- error 1471 +-- error ER_NON_INSERTABLE_TABLE insert into v4 values (-60,4,30); # try insert to VIEW using temporary table algorithm --- error 1471 +-- error ER_NON_INSERTABLE_TABLE insert into v5 values (-60,4,30); insert into v1 values (-60,4,30); insert into v1 (z,y,x) values (50,6,-100); @@ -381,13 +381,13 @@ create view v3 (x,y,z) as select b, a, b from t1; create view v4 (x,y,z) as select c+1, b, a from t1; create algorithm=temptable view v5 (x,y,z) as select c, b, a from t1; # try insert to VIEW with fields duplicate --- error 1471 +-- error ER_NON_INSERTABLE_TABLE insert into v3 select c, b, a from t2; # try insert to VIEW with expression in SELECT list --- error 1471 +-- error ER_NON_INSERTABLE_TABLE insert into v4 select c, b, a from t2; # try insert to VIEW using temporary table algorithm --- error 1471 +-- error ER_NON_INSERTABLE_TABLE insert into v5 select c, b, a from t2; insert into v1 select c, b, a from t2; insert into v1 (z,y,x) select a+20,b+2,-100 from t2; @@ -702,11 +702,11 @@ drop function `f``1`; # # tested problem when function name length close to ALIGN_SIZE # -create function x () returns int return 5; -create view v1 as select x (); +create function a() returns int return 5; +create view v1 as select a(); select * from v1; drop view v1; -drop function x; +drop function a; # # VIEW with collation @@ -752,6 +752,11 @@ drop view v1; # # VIEWs with national characters # + +SET @old_cs_client = @@character_set_client; +SET @old_cs_results = @@character_set_results; +SET @old_cs_connection = @@character_set_connection; + set names utf8; create table tü (cü char); create view vü as select cü from tü; @@ -759,7 +764,10 @@ insert into vü values ('ü'); select * from vü; drop view vü; drop table tü; -set names latin1; + +SET character_set_client = @old_cs_client; +SET character_set_results = @old_cs_results; +SET character_set_connection = @old_cs_connection; # # problem with used_tables() of outer reference resolved in VIEW @@ -836,6 +844,7 @@ drop table t1; # create table t1 (col1 int); create table t2 (col1 int); +create table t3 (col1 datetime not null); 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; @@ -942,7 +951,7 @@ insert into v3 (col1) values ((select max(col1) from v2)); 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)); +insert into t3 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); @@ -950,7 +959,7 @@ insert into t1 (col1) values ((select max(col1) from v4)); select * from t1; drop view v4,v3,v2,v1; -drop table t1,t2; +drop table t1,t2,t3; # # HANDLER with VIEW @@ -1033,18 +1042,18 @@ create table t2 (a int); insert into t2 values (2),(3),(0); # INSERT SELECT with ignore test insert ignore into v1 SELECT a from t2; -select * from t1; +select * from t1 order by a desc; #simple UPDATE test update v1 set a=-1 where a=0; -- error 1369 update v1 set a=2 where a=1; -select * from t1; +select * from t1 order by a desc; # prepare data for next check update v1 set a=0 where a=0; insert into t2 values (1); # multiupdate test update v1,t2 set v1.a=v1.a-1 where v1.a=t2.a; -select * from t1; +select * from t1 order by a desc; # prepare data for next check update v1 set a=a+1; # multiupdate with ignore test @@ -1224,8 +1233,8 @@ select * from t1; select * from v1; delete from t1; load data infile '../std_data_ln/loaddata3.dat' ignore into table v1 fields terminated by '' enclosed by '' ignore 1 lines; -select * from t1; -select * from v1; +select * from t1 order by a,b; +select * from v1 order by a,b; drop view v1; drop table t1; # variable length fields @@ -1247,14 +1256,14 @@ drop table t1; # create table t1 (s1 smallint); create view v1 as select * from t1 where 20 < (select (s1) from t1); --- error 1471 +-- error ER_NON_INSERTABLE_TABLE insert into v1 values (30); create view v2 as select * from t1; create view v3 as select * from t1 where 20 < (select (s1) from v2); --- error 1471 +-- error ER_NON_INSERTABLE_TABLE insert into v3 values (30); create view v4 as select * from v2 where 20 < (select (s1) from t1); --- error 1471 +-- error ER_NON_INSERTABLE_TABLE insert into v4 values (30); drop view v4, v3, v2, v1; drop table t1; @@ -1885,15 +1894,6 @@ DROP VIEW v1; DROP TABLE t1; # -# Bug #11335 View redefines column types -# -create table t1 (f1 tinyint(1), f2 char(1), f3 varchar(1), f4 geometry, f5 datetime); -create view v1 as select * from t1; -desc v1; -drop view v1; -drop table t1; - -# # Bug #11760 Typo in Item_func_add_time::print() results in NULLs returned # subtime() in view create table t1(f1 datetime); @@ -2073,6 +2073,7 @@ order by users_names; drop view v1, v2; drop table t1, t2; +# # Bug #6808 - Views: CREATE VIEW v ... FROM t AS v fails # @@ -2438,7 +2439,7 @@ DROP TABLE t1, t2; # # Bug #16069: VIEW does return the same results as underlying SELECT # with WHERE condition containing BETWEEN over dates - +# Dates as strings should be casted to date type CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, td date DEFAULT NULL, KEY idx(td)); @@ -2598,7 +2599,6 @@ SELECT * FROM t2; DROP VIEW v1; DROP TABLE t1,t2; - # # Bug#16110: insert permitted into view col w/o default value # @@ -2858,7 +2858,7 @@ DROP TABLE t1; # create table t1 (s1 int); create view v1 as select s1 as a, s1 as b from t1; ---error 1471 +--error ER_NON_INSERTABLE_TABLE insert into v1 values (1,1); update v1 set a = 5; drop view v1; @@ -2882,7 +2882,6 @@ SHOW CREATE VIEW v1; DROP VIEW v1; DROP TABLE t1, t2; - # # Bug#19111: TRIGGERs selecting from a VIEW on the firing base table # fail @@ -2930,7 +2929,6 @@ UPDATE v1 SET val=6 WHERE id=2; DROP VIEW v1; DROP TABLE t1; - # # BUG#22584: last_insert_id not updated after inserting a record # through a updatable view @@ -3434,7 +3432,6 @@ SELECT * FROM v1 IGNORE KEY(non_existant); DROP VIEW v1; DROP TABLE t1; - # # Bug #28702: VIEWs defined with USE/FORCE KEY ignore that request # @@ -3506,3 +3503,159 @@ DROP TABLE t1; --echo # ----------------------------------------------------------------- --echo # -- End of 5.0 tests. --echo # ----------------------------------------------------------------- + +# +# Bug#21370 View renaming lacks tablename_to_filename encoding +# +--disable_warnings +DROP DATABASE IF EXISTS `d-1`; +--enable_warnings +CREATE DATABASE `d-1`; +USE `d-1`; +CREATE TABLE `t-1` (c1 INT); +CREATE VIEW `v-1` AS SELECT c1 FROM `t-1`; +SHOW TABLES; +RENAME TABLE `t-1` TO `t-2`; +RENAME TABLE `v-1` TO `v-2`; +SHOW TABLES; +DROP TABLE `t-2`; +DROP VIEW `v-2`; +DROP DATABASE `d-1`; +USE test; + +--echo +--echo # +--echo # Bug#26676: VIEW using old table schema in a session. +--echo # +--echo + +--disable_warnings +DROP VIEW IF EXISTS v1; +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1(c1 INT, c2 INT); +INSERT INTO t1 VALUES (1, 2), (3, 4); + +--echo + +SELECT * FROM t1; + +--echo + +CREATE VIEW v1 AS SELECT * FROM t1; + +--echo + +SELECT * FROM v1; + +--echo + +ALTER TABLE t1 ADD COLUMN c3 INT AFTER c2; + +--echo + +SELECT * FROM t1; + +--echo + +SELECT * FROM v1; + +--echo + +SHOW CREATE VIEW v1; + +--echo + +DROP VIEW v1; +DROP TABLE t1; + +--echo +--echo # End of test case for Bug#26676. +--echo + +########################################################################### + +--echo # ----------------------------------------------------------------- +--echo # -- Bug#32538: View definition picks up character set, but not collation +--echo # ----------------------------------------------------------------- +--echo + +--disable_warnings +DROP VIEW IF EXISTS v1; +--enable_warnings + +--echo + +SET collation_connection = latin1_general_ci; +CREATE VIEW v1 AS SELECT _latin1 'text1' AS c1, 'text2' AS c2; + +--echo + +SELECT COLLATION(c1), COLLATION(c2) FROM v1; + +--echo + +SHOW CREATE VIEW v1; + +--echo + +--error ER_CANT_AGGREGATE_2COLLATIONS +SELECT * FROM v1 WHERE c1 = 'text1'; + +--echo + +SELECT * FROM v1 WHERE c2 = 'text2'; + +--echo + +use test; +SET names latin1; + +--echo + +SELECT COLLATION(c1), COLLATION(c2) FROM v1; + +--echo + +SELECT * FROM v1 WHERE c1 = 'text1'; + +--echo + +--error ER_CANT_AGGREGATE_2COLLATIONS +SELECT * FROM v1 WHERE c2 = 'text2'; + +--echo + +DROP VIEW v1; + +--echo +--echo # -- End of test case for Bug#32538. +--echo + +# +# Bug#34587 Creating a view inside a stored procedure leads to a server crash +# + +--disable_warnings +drop view if exists a; +drop procedure if exists p; +--enable_warnings + +delimiter |; +create procedure p() +begin + declare continue handler for sqlexception begin end; + create view a as select 1; +end| +delimiter ;| +call p(); +call p(); +drop view a; +drop procedure p; + +########################################################################### + +--echo # ----------------------------------------------------------------- +--echo # -- End of 5.1 tests. +--echo # ----------------------------------------------------------------- |