diff options
Diffstat (limited to '')
| -rw-r--r-- | mysql-test/main/view.result | 69 |
1 files changed, 42 insertions, 27 deletions
diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result index 9cab1b5f..04049fbe 100644 --- a/mysql-test/main/view.result +++ b/mysql-test/main/view.result @@ -1228,10 +1228,17 @@ 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 44000: CHECK OPTION failed `test`.`v1` +Warnings: +Warning 1369 CHECK OPTION failed `test`.`v1` +SHOW WARNINGS; +Level Code Message +Warning 1369 CHECK OPTION failed `test`.`v1` insert ignore into v1 values (6),(3); Warnings: Warning 1369 CHECK OPTION failed `test`.`v1` +SHOW WARNINGS; +Level Code Message +Warning 1369 CHECK OPTION failed `test`.`v1` select * from t1; s1 3 @@ -2445,8 +2452,6 @@ SELECT Meaning FROM v1 INTO retn; RETURN retn; END // -Warnings: -Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead CREATE VIEW v2 AS SELECT f1(); select * from v2; f1() @@ -2618,8 +2623,6 @@ declare mx int; select max(a) from t1 into mx; return mx; end// -Warnings: -Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead create view v1 as select f1() as a; create view v2 as select * from v1; drop table t1; @@ -3162,14 +3165,10 @@ DROP VIEW v1; DROP TABLE t1; DROP VIEW IF EXISTS v1; SELECT * FROM (SELECT 1) AS t into @w; -Warnings: -Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead 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 into @w; -Warnings: -Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead drop view if exists view_24532_a; drop view if exists view_24532_b; drop table if exists table_24532; @@ -3966,8 +3965,6 @@ BEGIN SELECT a FROM v2 INTO @a; RETURN @a; END// -Warnings: -Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead # Trigger pre-locking when opening v2. CREATE VIEW v1 AS SELECT f1() FROM t1; SHOW CREATE VIEW v1; @@ -4213,10 +4210,10 @@ INSERT INTO t2 VALUES EXPLAIN EXTENDED SELECT t1.a,t2.c FROM t1,t2 WHERE t2.pk = t1.a AND t2.pk > 8; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Using where -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 12 75.00 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Using where; Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`pk` = `test`.`t1`.`a` and `test`.`t1`.`a` > 8 +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`pk` and `test`.`t2`.`pk` > 8 FLUSH STATUS; SELECT t1.a,t2.c FROM t1,t2 WHERE t2.pk = t1.a AND t2.pk > 8; a c @@ -4225,22 +4222,34 @@ a c SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 -Handler_read_key 1 +Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 -Handler_read_rnd_next 17 +Handler_read_rnd_next 30 +analyze table t1,t2; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +explain extended SELECT t1.a,t2.c FROM t1,t2 WHERE t2.pk = t1.a AND t2.pk > 8; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 16 12.50 Using where +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`pk` = `test`.`t1`.`a` and `test`.`t1`.`a` > 8 CREATE VIEW v AS SELECT * FROM t2; EXPLAIN EXTENDED SELECT t1.a,v.c FROM t1,v WHERE v.pk = t1.a AND v.pk > 8; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Using where -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 12 75.00 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Using where; Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`pk` = `test`.`t1`.`a` and `test`.`t1`.`a` > 8 +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`pk` and `test`.`t2`.`pk` > 8 FLUSH STATUS; SELECT t1.a,v.c FROM t1,v WHERE v.pk = t1.a AND v.pk > 8; a c @@ -4249,14 +4258,20 @@ a c SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 -Handler_read_key 1 +Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 -Handler_read_rnd_next 17 +Handler_read_rnd_next 30 +set statement optimizer_where_cost=100 FOR explain extended SELECT t1.a,v.c FROM t1,v WHERE v.pk = t1.a AND v.pk > 8; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Using where +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`pk` = `test`.`t1`.`a` and `test`.`t1`.`a` > 8 DROP VIEW v; DROP TABLE t1, t2; # @@ -5485,8 +5500,8 @@ from t1 left join v1 on v1.c=t1.b where t1.a < 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range a a 5 NULL 3 100.00 Using index condition -1 SIMPLE t2 ref c c 5 test.t1.b 2 100.00 Using where -1 SIMPLE t3 ref f,e e 5 test.t2.d 2 100.00 Using where +1 SIMPLE t2 ref c c 5 test.t1.b 1 100.00 Using where +1 SIMPLE t3 ref f,e e 5 test.t2.d 1 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t2`.`c` = `test`.`t1`.`b` and `test`.`t3`.`e` = `test`.`t2`.`d` and `test`.`t3`.`f` is not null and `test`.`t1`.`b` is not null and `test`.`t2`.`d` is not null) where `test`.`t1`.`a` < 5 explain extended @@ -5496,8 +5511,8 @@ on t2.c=t1.b and t3.f is not null where t1.a < 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range a a 5 NULL 3 100.00 Using index condition -1 SIMPLE t2 ref c c 5 test.t1.b 2 100.00 Using where -1 SIMPLE t3 ref f,e e 5 test.t2.d 2 100.00 Using where +1 SIMPLE t2 ref c c 5 test.t1.b 1 100.00 Using where +1 SIMPLE t3 ref f,e e 5 test.t2.d 1 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t2`.`c` = `test`.`t1`.`b` and `test`.`t3`.`e` = `test`.`t2`.`d` and `test`.`t3`.`f` is not null and `test`.`t1`.`b` is not null and `test`.`t2`.`d` is not null) where `test`.`t1`.`a` < 5 explain extended @@ -5507,7 +5522,7 @@ where t1.a < 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range a a 5 NULL 3 100.00 Using index condition 1 SIMPLE t3 eq_ref f,e f 4 test.t1.a 1 100.00 Using where -1 SIMPLE t2 ref c c 5 test.t1.b 2 100.00 Using where +1 SIMPLE t2 ref c c 5 test.t1.b 1 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t2`.`c` = `test`.`t1`.`b` and `test`.`t3`.`f` = `test`.`t1`.`a` and `test`.`t2`.`d` = `test`.`t3`.`e` and `test`.`t1`.`a` is not null and `test`.`t1`.`a` is not null and `test`.`t1`.`b` is not null) where `test`.`t1`.`a` < 5 explain extended @@ -5518,7 +5533,7 @@ where t1.a < 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range a a 5 NULL 3 100.00 Using index condition 1 SIMPLE t3 eq_ref f,e f 4 test.t1.a 1 100.00 Using where -1 SIMPLE t2 ref c c 5 test.t1.b 2 100.00 Using where +1 SIMPLE t2 ref c c 5 test.t1.b 1 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t2`.`c` = `test`.`t1`.`b` and `test`.`t3`.`f` = `test`.`t1`.`a` and `test`.`t2`.`d` = `test`.`t3`.`e` and `test`.`t1`.`a` is not null and `test`.`t1`.`a` is not null and `test`.`t1`.`b` is not null) where `test`.`t1`.`a` < 5 drop view v1; |