diff options
Diffstat (limited to 'mysql-test/r/view.result')
| -rw-r--r-- | mysql-test/r/view.result | 134 |
1 files changed, 73 insertions, 61 deletions
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 8d9d802949d..d1098020b25 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -51,8 +51,8 @@ ERROR 42S22: Unknown column 'b' in 'field list' select v1.b from v1; ERROR 42S22: Unknown column 'v1.b' in 'field list' explain extended select c from v1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 5 +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Warnings: Note 1003 select (`test`.`t1`.`b` + 1) AS `c` from `test`.`t1` create algorithm=temptable view v2 (c) as select b+1 from t1; @@ -67,9 +67,9 @@ c 6 11 explain extended select c from v2; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 -2 DERIVED t1 ALL NULL NULL NULL NULL 5 +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 100.00 +2 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00 Warnings: Note 1003 select `v2`.`c` AS `c` from `test`.`v2` create view v3 (c) as select a+1 from v1; @@ -85,8 +85,8 @@ c 7 12 explain extended select c from v3; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 5 +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Warnings: Note 1003 select ((`test`.`t1`.`b` + 1) + 1) AS `c` from `test`.`t1` create algorithm=temptable view v4 (c) as select c+1 from v2; @@ -98,10 +98,10 @@ c 7 12 explain extended select c from v4; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 -2 DERIVED <derived3> ALL NULL NULL NULL NULL 5 -3 DERIVED t1 ALL NULL NULL NULL NULL 5 +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 100.00 +2 DERIVED <derived3> ALL NULL NULL NULL NULL 5 100.00 +3 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00 Warnings: Note 1003 select `v4`.`c` AS `c` from `test`.`v4` create view v5 (c) as select c+1 from v2; @@ -113,9 +113,9 @@ c 7 12 explain extended select c from v5; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 -3 DERIVED t1 ALL NULL NULL NULL NULL 5 +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 100.00 +3 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00 Warnings: Note 1003 select (`v2`.`c` + 1) AS `c` from `test`.`v2` create algorithm=temptable view v6 (c) as select c+1 from v1; @@ -127,9 +127,9 @@ c 7 12 explain extended select c from v6; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 -2 DERIVED t1 ALL NULL NULL NULL NULL 5 +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 100.00 +2 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00 Warnings: Note 1003 select `v6`.`c` AS `c` from `test`.`v6` show tables; @@ -378,8 +378,8 @@ c 20 30 explain extended select * from v1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`b` AS `c` from `test`.`t1` where (`test`.`t1`.`a` < 3) update v1 set c=c+1; @@ -612,7 +612,7 @@ drop table t1; create table t1 (a int, b int); create view v1 as select a, sum(b) from t1 group by a; select b from v1 use index (some_index) where b=1; -ERROR HY000: Key 'some_index' doesn't exist in table 'v1' +ERROR 42000: Key 'some_index' doesn't exist in table 'v1' drop view v1; drop table t1; create table t1 (col1 char(5),col2 char(5)); @@ -791,13 +791,13 @@ test.`f``1` () 5 drop view v1; drop function `f``1`; -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; -x () +a() 5 drop view v1; -drop function x; +drop function a; create table t2 (col1 char collate latin1_german2_ci); create view v2 as select col1 collate latin1_german1_ci from t2; show create view v2; @@ -900,6 +900,7 @@ drop view v1; 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; @@ -1004,8 +1005,8 @@ ERROR HY000: The definition of table 'v2' prevents operation INSERT on table 'v3 insert into v3 (col1) values ((select CONVERT_TZ('20050101000000','UTC','MET') from v2)); ERROR HY000: The definition of table 'v2' prevents operation INSERT on table 'v3'. 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 +insert into t3 values ((select CONVERT_TZ('20050101000000','UTC','MET') from t2)); +ERROR 23000: Column 'col1' 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)); @@ -1017,7 +1018,7 @@ NULL 3 3 drop view v4,v3,v2,v1; -drop table t1,t2; +drop table t1,t2,t3; create table t1 (s1 int); create view v1 as select * from t1; handler v1 open as xx; @@ -1108,21 +1109,21 @@ insert ignore into v1 SELECT a from t2; Warnings: Error 1369 CHECK OPTION failed 'test.v1' Error 1369 CHECK OPTION failed 'test.v1' -select * from t1; +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' -select * from t1; +select * from t1 order by a desc; a 1 -1 update v1 set a=0 where a=0; insert into t2 values (1); update v1,t2 set v1.a=v1.a-1 where v1.a=t2.a; -select * from t1; +select * from t1 order by a desc; a 0 -1 @@ -1312,12 +1313,12 @@ Warning 1366 Incorrect integer value: 'error ' for column 'a' at row 3 Error 1369 CHECK OPTION failed 'test.v1' Warning 1366 Incorrect integer value: 'wrong end ' for column 'a' at row 4 Error 1369 CHECK OPTION failed 'test.v1' -select * from t1; +select * from t1 order by a,b; a b 1 row 1 2 row 2 3 row 3 -select * from v1; +select * from v1 order by a,b; a b 1 row 1 2 row 2 @@ -1392,10 +1393,10 @@ a a b 2 2 NULL 4 NULL NULL explain extended select * from t3 left join v3 on (t3.a = v3.a); -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 -1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Warnings: Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `b` from `test`.`t3` left join (`test`.`t1` left join `test`.`t2` on((`test`.`t1`.`a` = `test`.`t2`.`a`))) on((`test`.`t3`.`a` = `test`.`t1`.`a`)) where 1 create view v1 (a) as select a from t1; @@ -1407,10 +1408,10 @@ a a b 2 2 NULL 4 NULL NULL explain extended select * from t3 left join v4 on (t3.a = v4.a); -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 -1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Warnings: Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `b` from `test`.`t3` left join (`test`.`t1` left join (`test`.`t2`) on((`test`.`t1`.`a` = `test`.`t2`.`a`))) on((`test`.`t3`.`a` = `test`.`t1`.`a`)) where 1 prepare stmt1 from "select * from t3 left join v4 on (t3.a = v4.a);"; @@ -1497,7 +1498,7 @@ 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 Column was set to data type implicit default; NULL supplied for NOT NULL column 'a' at row 2 +Warning 1048 Column 'a' cannot be null insert into v3(a) values (1) on duplicate key update a=a+10000+VALUES(a); select * from t1; a b @@ -2062,17 +2063,6 @@ CALL p1(); DROP PROCEDURE p1; DROP VIEW v1; DROP TABLE t1; -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; -Field Type Null Key Default Extra -f1 tinyint(1) YES NULL -f2 char(1) YES NULL -f3 varchar(1) YES NULL -f4 geometry YES NULL -f5 datetime YES NULL -drop view v1; -drop table t1; create table t1(f1 datetime); insert into t1 values('2005.01.01 12:0:0'); create view v1 as select f1, subtime(f1, '1:1:1') as sb from t1; @@ -2327,7 +2317,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref a a 10 const,test.t1.b 2 Using where; Using index EXPLAIN SELECT * FROM v2 WHERE a=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a a 5 const 1 Using where; Using index +1 SIMPLE t1 ref a a 5 const 1 Using where; Using index; Using join cache 1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where DROP VIEW v1,v2; DROP TABLE t1,t2,t3; @@ -2696,14 +2686,16 @@ FROM t1 HAVING Age < 75; SHOW CREATE VIEW v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select (year(now()) - year(`t1`.`DOB`)) AS `Age` from `t1` having (`Age` < 75) +set timestamp=1136066400; SELECT (year(now())-year(DOB)) AS Age FROM t1 HAVING Age < 75; Age -43 -39 +42 +38 +set timestamp=1136066400; SELECT * FROM v1; Age -43 -39 +42 +38 DROP VIEW v1; DROP TABLE t1; CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a char(6) DEFAULT 'xxx'); @@ -3110,8 +3102,8 @@ f1 f2 1 3 2 3 explain extended select * from v1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 6 100.00 Using filesort Warnings: Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` order by `test`.`t1`.`f2` select * from v1 order by f1; @@ -3123,8 +3115,8 @@ f1 f2 2 2 2 3 explain extended select * from v1 order by f1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 6 100.00 Using filesort Warnings: Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` order by `test`.`t1`.`f1`,`test`.`t1`.`f2` drop view v1; @@ -3368,3 +3360,23 @@ View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(1.23456789 as decimal(8,0)) AS `col` DROP VIEW v1; End of 5.0 tests. +DROP DATABASE IF EXISTS `d-1`; +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; +Tables_in_d-1 +t-1 +v-1 +RENAME TABLE `t-1` TO `t-2`; +RENAME TABLE `v-1` TO `v-2`; +SHOW TABLES; +Tables_in_d-1 +t-2 +v-2 +DROP TABLE `t-2`; +DROP VIEW `v-2`; +DROP DATABASE `d-1`; +USE test; +End of 5.1 tests. |