diff options
Diffstat (limited to 'mysql-test')
32 files changed, 437 insertions, 306 deletions
diff --git a/mysql-test/r/bdb.result b/mysql-test/r/bdb.result index d510ec53dfc..6713d90e4f9 100644 --- a/mysql-test/r/bdb.result +++ b/mysql-test/r/bdb.result @@ -146,7 +146,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref level level 1 const 1 Using index explain select level,id,parent_id from t1 where level=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref level level 1 const 1 Using where +1 SIMPLE t1 ref level level 1 const 1 select level,id from t1 where level=1; level id 1 1002 diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 92c825f547d..8131399872a 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -298,7 +298,7 @@ select * from t3; id name create table if not exists t3 like t1; Warnings: -Warning 1050 Table 't3' already exists +Note 1050 Table 't3' already exists select @@warning_count; @@warning_count 1 diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 2ec389032f7..9af8fa8c8cc 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -586,7 +586,7 @@ id select_type table type possible_keys key key_len ref rows Extra explain select max(t1.a3), min(t2.a2) from t1, t2 where t1.a2 = 2 and t1.a3 < 'MIN' and t2.a3 > 'CA'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref k1 k1 3 const 2 Using where; Using index +1 SIMPLE t1 range k1 k1 7 NULL 1 Using where; Using index 1 SIMPLE t2 range k1 k1 3 NULL 4 Using where; Using index explain select min(a4 - 0.01) from t1; diff --git a/mysql-test/r/func_if.result b/mysql-test/r/func_if.result index 307d1021cdb..83e103fe57d 100644 --- a/mysql-test/r/func_if.result +++ b/mysql-test/r/func_if.result @@ -43,7 +43,7 @@ explain extended select if(u=1,st,binary st) s from t1 where st like "%a%" order id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where; Using filesort Warnings: -Note 1003 select if((test.t1.u = 1),test.t1.st,cast(test.t1.st as char charset binary)) AS `s` from test.t1 where (test.t1.st like _latin1'%a%') order by if((test.t1.u = 1),test.t1.st,cast(test.t1.st as char charset binary)) +Note 1003 select if((`test`.`t1`.`u` = 1),`test`.`t1`.`st`,cast(`test`.`t1`.`st` as char charset binary)) AS `s` from `test`.`t1` where (`test`.`t1`.`st` like _latin1'%a%') order by if((`test`.`t1`.`u` = 1),`test`.`t1`.`st`,cast(`test`.`t1`.`st` as char charset binary)) select nullif(u=0, 'test') from t1; nullif(u=0, 'test') NULL diff --git a/mysql-test/r/heap_btree.result b/mysql-test/r/heap_btree.result index 03625d19d04..8f9349ce316 100644 --- a/mysql-test/r/heap_btree.result +++ b/mysql-test/r/heap_btree.result @@ -73,7 +73,7 @@ engine=heap; insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6); explain select * from t1 where x=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref x x 4 const 1 Using where +1 SIMPLE t1 ref x x 4 const 1 select * from t1 where x=1; x y 1 1 @@ -134,7 +134,7 @@ a b 1 1 explain select * from tx where b=x; id select_type table type possible_keys key key_len ref rows Extra -x SIMPLE tx ref b b x const x Using where +x SIMPLE tx ref b b x const x drop table t1; create table t1 (id int unsigned not null, primary key using BTREE (id)) engine=HEAP; insert into t1 values(1); diff --git a/mysql-test/r/index_merge.result b/mysql-test/r/index_merge.result index f3654db030c..cb4b285430c 100644 --- a/mysql-test/r/index_merge.result +++ b/mysql-test/r/index_merge.result @@ -264,11 +264,11 @@ select * from t0,t1 where (t0.key1=t1.key1) and (t0.key1=3 or t0.key2<4) and t1.key1=2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 ref i1,i2 i1 4 const 1 Using where -1 SIMPLE t1 ref i1 i1 4 const 1 Using where +1 SIMPLE t1 ref i1 i1 4 const 1 explain select * from t0,t1 where t0.key1 = 5 and (t1.key1 = t0.key1 or t1.key8 = t0.key1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ref i1 i1 4 const 1 Using where +1 SIMPLE t0 ref i1 i1 4 const 1 1 SIMPLE t1 index_merge i1,i8 i1,i8 4,4 NULL 2 Using union(i1,i8); Using where explain select * from t0,t1 where t0.key1 < 3 and (t1.key1 = t0.key1 or t1.key8 = t0.key1); diff --git a/mysql-test/r/index_merge_ror.result b/mysql-test/r/index_merge_ror.result index 15ad1026ca0..6fc9dddef88 100644 --- a/mysql-test/r/index_merge_ror.result +++ b/mysql-test/r/index_merge_ror.result @@ -130,7 +130,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,stb_swt1a_2b,stb_swt1b,st_b st_b 4 const 14720 Using where explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a sta_swt12a 12 const,const,const 958 Using where +1 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a sta_swt12a 12 const,const,const 958 explain select * from t1 where st_b=1 and swt1b=1 and swt2b=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref stb_swt1a_2b,stb_swt1b,st_b stb_swt1b 8 const,const 3757 Using where diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index b407c1537bc..b46626d0322 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -141,13 +141,13 @@ id parent_id level 1015 102 2 explain select level from t1 where level=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref level level 1 const # Using where; Using index +1 SIMPLE t1 ref level level 1 const # Using index explain select level,id from t1 where level=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref level level 1 const # Using where; Using index +1 SIMPLE t1 ref level level 1 const # Using index explain select level,id,parent_id from t1 where level=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref level level 1 const # Using where +1 SIMPLE t1 ref level level 1 const # select level,id from t1 where level=1; level id 1 1002 @@ -609,7 +609,7 @@ id parent_id level 1016 102 2 explain select level from t1 where level=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref level level 1 const # Using where; Using index +1 SIMPLE t1 ref level level 1 const # Using index select level,id from t1 where level=1; level id 1 1004 diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index ec377b92371..6bafe533fac 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -628,7 +628,7 @@ insert into t2 values (10,1),(20,2),(30,3); explain select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL PRIMARY 4 NULL 3 Using index -1 SIMPLE t1 const PRIMARY PRIMARY 2 const 1 Using where; Using index +1 SIMPLE t1 const PRIMARY PRIMARY 2 const 1 Using index select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30; fooID barID fooID 10 1 NULL @@ -703,13 +703,13 @@ explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from order by m.match_id desc; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE s ALL NULL NULL NULL NULL 10 -1 SIMPLE m const match_id,match_id_2 match_id 1 const 1 Using where +1 SIMPLE m const match_id,match_id_2 match_id 1 const 1 explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from (t2 s left join t1 m on m.match_id = 1) order by UUX desc; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE s ALL NULL NULL NULL NULL 10 Using temporary; Using filesort -1 SIMPLE m const match_id,match_id_2 match_id 1 const 1 Using where +1 SIMPLE m const match_id,match_id_2 match_id 1 const 1 select s.*, '*', m.*, (s.match_1_h - m.home) UUX from (t2 s left join t1 m on m.match_id = 1) order by UUX desc; @@ -729,7 +729,7 @@ t2 s straight_join t1 m where m.match_id = 1 order by UUX desc; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE s ALL NULL NULL NULL NULL 10 Using temporary; Using filesort -1 SIMPLE m const match_id,match_id_2 match_id 1 const 1 Using where +1 SIMPLE m const match_id,match_id_2 match_id 1 const 1 select s.*, '*', m.*, (s.match_1_h - m.home) UUX from t2 s straight_join t1 m where m.match_id = 1 order by UUX desc; diff --git a/mysql-test/r/mysqlbinlog2.result b/mysql-test/r/mysqlbinlog2.result index 3c1b85e05a1..9fe4edf7b82 100644 --- a/mysql-test/r/mysqlbinlog2.result +++ b/mysql-test/r/mysqlbinlog2.result @@ -18,6 +18,8 @@ insert into t1 values(null, "f"); /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; create table t1 (a int auto_increment not null primary key, b char(3)); SET INSERT_ID=1; SET TIMESTAMP=1579609942; @@ -40,6 +42,8 @@ insert into t1 values(null, "e"); SET INSERT_ID=1; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "a"); SET INSERT_ID=2; SET TIMESTAMP=1579609942; @@ -58,6 +62,8 @@ insert into t1 values(null, "e"); /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609946; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "d"); SET INSERT_ID=5; SET TIMESTAMP=1579609946; @@ -67,6 +73,8 @@ insert into t1 values(null, "e"); /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; create table t1 (a int auto_increment not null primary key, b char(3)); SET INSERT_ID=1; SET TIMESTAMP=1579609942; @@ -84,6 +92,8 @@ SET INSERT_ID=4; SET INSERT_ID=3; use test; SET TIMESTAMP=1579609944; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "c"); SET INSERT_ID=4; SET TIMESTAMP=1579609946; @@ -96,6 +106,8 @@ insert into t1 values(null, "e"); /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; create table t1 (a int auto_increment not null primary key, b char(3)); SET INSERT_ID=1; SET TIMESTAMP=1579609942; @@ -108,6 +120,8 @@ insert into t1 values(null, "b"); /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; create table t1 (a int auto_increment not null primary key, b char(3)); SET INSERT_ID=1; SET TIMESTAMP=1579609942; @@ -127,6 +141,8 @@ insert into t1 values(null, "e"); SET INSERT_ID=6; use test; SET TIMESTAMP=1579609943; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "f"); --- offset -- @@ -134,6 +150,8 @@ insert into t1 values(null, "f"); SET INSERT_ID=1; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "a"); SET INSERT_ID=2; SET TIMESTAMP=1579609942; @@ -150,12 +168,16 @@ insert into t1 values(null, "e"); SET INSERT_ID=6; use test; SET TIMESTAMP=1579609943; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "f"); --- start-position -- /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609946; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "d"); SET INSERT_ID=5; SET TIMESTAMP=1579609946; @@ -163,12 +185,16 @@ insert into t1 values(null, "e"); SET INSERT_ID=6; use test; SET TIMESTAMP=1579609943; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "f"); --- stop-position -- /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; create table t1 (a int auto_increment not null primary key, b char(3)); SET INSERT_ID=1; SET TIMESTAMP=1579609942; @@ -192,6 +218,8 @@ SET INSERT_ID=6; SET INSERT_ID=3; use test; SET TIMESTAMP=1579609944; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "c"); SET INSERT_ID=4; SET TIMESTAMP=1579609946; @@ -202,12 +230,16 @@ insert into t1 values(null, "e"); SET INSERT_ID=6; use test; SET TIMESTAMP=1579609943; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "f"); --- stop-datetime -- /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; create table t1 (a int auto_increment not null primary key, b char(3)); SET INSERT_ID=1; SET TIMESTAMP=1579609942; @@ -220,6 +252,8 @@ insert into t1 values(null, "b"); /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; create table t1 (a int auto_increment not null primary key, b char(3)); SET INSERT_ID=1; SET TIMESTAMP=1579609942; @@ -242,6 +276,8 @@ insert into t1 values(null, "e"); SET INSERT_ID=1; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "a"); SET INSERT_ID=2; SET TIMESTAMP=1579609942; @@ -260,6 +296,8 @@ insert into t1 values(null, "e"); /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609946; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "d"); SET INSERT_ID=5; SET TIMESTAMP=1579609946; @@ -269,6 +307,8 @@ insert into t1 values(null, "e"); /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; create table t1 (a int auto_increment not null primary key, b char(3)); SET INSERT_ID=1; SET TIMESTAMP=1579609942; @@ -286,6 +326,8 @@ SET INSERT_ID=4; SET INSERT_ID=3; use test; SET TIMESTAMP=1579609944; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "c"); SET INSERT_ID=4; SET TIMESTAMP=1579609946; @@ -298,6 +340,8 @@ insert into t1 values(null, "e"); /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; create table t1 (a int auto_increment not null primary key, b char(3)); SET INSERT_ID=1; SET TIMESTAMP=1579609942; @@ -310,6 +354,8 @@ insert into t1 values(null, "b"); /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; create table t1 (a int auto_increment not null primary key, b char(3)); SET INSERT_ID=1; SET TIMESTAMP=1579609942; @@ -329,6 +375,8 @@ insert into t1 values(null, "e"); SET INSERT_ID=6; use test; SET TIMESTAMP=1579609943; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "f"); --- offset -- @@ -336,6 +384,8 @@ insert into t1 values(null, "f"); SET INSERT_ID=1; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "a"); SET INSERT_ID=2; SET TIMESTAMP=1579609942; @@ -352,12 +402,16 @@ insert into t1 values(null, "e"); SET INSERT_ID=6; use test; SET TIMESTAMP=1579609943; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "f"); --- start-position -- /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609946; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "d"); SET INSERT_ID=5; SET TIMESTAMP=1579609946; @@ -365,12 +419,16 @@ insert into t1 values(null, "e"); SET INSERT_ID=6; use test; SET TIMESTAMP=1579609943; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "f"); --- stop-position -- /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; create table t1 (a int auto_increment not null primary key, b char(3)); SET INSERT_ID=1; SET TIMESTAMP=1579609942; @@ -394,6 +452,8 @@ SET INSERT_ID=6; SET INSERT_ID=3; use test; SET TIMESTAMP=1579609944; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "c"); SET INSERT_ID=4; SET TIMESTAMP=1579609946; @@ -404,12 +464,16 @@ insert into t1 values(null, "e"); SET INSERT_ID=6; use test; SET TIMESTAMP=1579609943; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; insert into t1 values(null, "f"); --- stop-datetime -- /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; create table t1 (a int auto_increment not null primary key, b char(3)); SET INSERT_ID=1; SET TIMESTAMP=1579609942; @@ -422,6 +486,8 @@ insert into t1 values(null, "b"); /*!40019 SET @@session.max_insert_delayed_threads=0*/; use test; SET TIMESTAMP=1579609942; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1; +SET @@session.sql_mode=0; create table t1 (a int auto_increment not null primary key, b char(3)); SET INSERT_ID=1; SET TIMESTAMP=1579609942; diff --git a/mysql-test/r/negation_elimination.result b/mysql-test/r/negation_elimination.result index 9193a125cd1..a7dbe0d9052 100644 --- a/mysql-test/r/negation_elimination.result +++ b/mysql-test/r/negation_elimination.result @@ -387,5 +387,5 @@ explain extended select a, not(not(a)), not(a <= 2 and not(a)), not(a not like " id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 5 NULL 5 Using where; Using index Warnings: -Note 1003 select test.t1.a AS `a`,(test.t1.a <> 0) AS `not(not(a))`,((test.t1.a > 2) or test.t1.a) AS `not(a <= 2 and not(a))`,(test.t1.a like _latin1'1') AS `not(a not like "1")`,(test.t1.a in (1,2)) AS `not (a not in (1,2))`,(test.t1.a = 2) AS `not(a != 2)` from test.t1 where test.t1.a having test.t1.a +Note 1003 select `test`.`t1`.`a` AS `a`,(`test`.`t1`.`a` <> 0) AS `not(not(a))`,((`test`.`t1`.`a` > 2) or `test`.`t1`.`a`) AS `not(a <= 2 and not(a))`,(`test`.`t1`.`a` like _latin1'1') AS `not(a not like "1")`,(`test`.`t1`.`a` in (1,2)) AS `not (a not in (1,2))`,(`test`.`t1`.`a` = 2) AS `not(a != 2)` from `test`.`t1` where `test`.`t1`.`a` having `test`.`t1`.`a` drop table t1; diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result index bd90b3fe3f3..ced80a033e7 100644 --- a/mysql-test/r/null.result +++ b/mysql-test/r/null.result @@ -167,11 +167,20 @@ insert into t1 select i*2 from t1; insert into t1 select i*2 from t1; insert into t1 select i*2 from t1; insert into t1 select i*2 from t1; +insert into t1 values(null); explain select * from t1 where i=2 or i is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref_or_null i i 5 const 10 Using where; Using index +select count(*) from t1 where i=2 or i is null; +count(*) +10 alter table t1 change i i int not null; +Warnings: +Warning 1265 Data truncated for column 'i' at row 513 explain select * from t1 where i=2 or i is null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref i i 4 const 7 Using where; Using index +1 SIMPLE t1 ref i i 4 const 8 Using index +select count(*) from t1 where i=2 or i is null; +count(*) +9 drop table t1; diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result index 3044b188d06..7980c3f7f91 100644 --- a/mysql-test/r/null_key.result +++ b/mysql-test/r/null_key.result @@ -33,10 +33,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a,b a 5 const 2 Using where; Using index explain select * from t1 where a > 1 and a < 3 limit 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index a a 9 NULL 12 Using where; Using index +1 SIMPLE t1 range a a 5 NULL 1 Using where; Using index explain select * from t1 where a > 8 and a < 9; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index a a 9 NULL 12 Using where; Using index +1 SIMPLE t1 range a a 5 NULL 1 Using where; Using index select * from t1 where a is null; a b NULL 7 @@ -66,6 +66,11 @@ a b NULL 7 NULL 9 NULL 9 +select * from t1 where a > 1 and a < 3 limit 1; +a b +2 2 +select * from t1 where a > 8 and a < 9; +a b create table t2 like t1; insert into t2 select * from t1; alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10)); @@ -153,12 +158,12 @@ a b 7 NULL explain select * from t1 where (a = 7 or a is null) and (b=7 or b is null); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a,b a 10 NULL 3 Using where; Using index +1 SIMPLE t1 ref_or_null a,b a 5 const 4 Using where; Using index select * from t1 where (a = 7 or a is null) and (b=7 or b is null); a b -NULL 7 7 NULL 7 7 +NULL 7 explain select * from t1 where (a = 7 or a is null) and (a = 7 or a is null); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref_or_null a a 5 const 5 Using where; Using index diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index a431807a560..e010f4a42e2 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -328,7 +328,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 9 NULL 5 Using where; Using index explain select * from t1 where a = 2 and b < 2 order by a desc,b desc; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a a 4 const 2 Using where; Using index +1 SIMPLE t1 range a a 9 NULL 2 Using where; Using index explain select * from t1 where a = 1 order by b desc; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a a 4 const 5 Using where; Using index diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 01ee018a434..31c65f64256 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -257,6 +257,11 @@ explain select * from t1, t2 where (t1.key1 <t2.keya + 1) and t2.keya=3; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref j1 j1 4 const 1 Using index 1 SIMPLE t1 ALL i1 NULL NULL NULL 4 Range checked for each record (index map: 0x1) +explain select * from t1 force index(i1), t2 force index(j1) where +(t1.key1 <t2.keya + 1) and t2.keya=3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ref j1 j1 4 const 1 Using index +1 SIMPLE t1 ALL i1 NULL NULL NULL 4 Range checked for each record (index map: 0x1) DROP TABLE t1,t2; CREATE TABLE t1 ( a int(11) default NULL, @@ -533,8 +538,7 @@ count(*) 0 drop table t1; create table t1 (x bigint unsigned not null primary key) engine=innodb; -insert into t1(x) values (0xfffffffffffffff0); -insert into t1(x) values (0xfffffffffffffff1); +insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1); select * from t1; x 18446744073709551600 @@ -554,6 +558,9 @@ count(*) select count(*) from t1 where x = -16; count(*) 0 +explain select count(*) from t1 where x > -16; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL 1 Using where; Using index select count(*) from t1 where x > -16; count(*) 1 diff --git a/mysql-test/r/rpl_charset.result b/mysql-test/r/rpl_charset.result index d961173f849..a427e77a18d 100644 --- a/mysql-test/r/rpl_charset.result +++ b/mysql-test/r/rpl_charset.result @@ -105,62 +105,62 @@ drop database mysqltest2; drop database mysqltest3; show binlog events from 95; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 95 Query 1 181 use `test`; drop database if exists mysqltest2 -master-bin.000001 181 Query 1 267 use `test`; drop database if exists mysqltest3 -master-bin.000001 267 Query 1 366 use `test`; create database mysqltest2 character set latin2 -master-bin.000001 366 Query 1 522 use `test`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=8,COLLATION_SERVER=30 -master-bin.000001 522 Query 1 600 use `test`; create database mysqltest3 -master-bin.000001 600 Query 1 756 use `test`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=8,COLLATION_SERVER=64 -master-bin.000001 756 Query 1 832 use `test`; drop database mysqltest3 -master-bin.000001 832 Query 1 988 use `test`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=8,COLLATION_SERVER=64 -master-bin.000001 988 Query 1 1066 use `test`; create database mysqltest3 -master-bin.000001 1066 Query 1 1223 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 1223 Query 1 1347 use `mysqltest2`; create table t1 (a int auto_increment primary key, b varchar(100)) -master-bin.000001 1347 Query 1 1505 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 1505 Intvar 1 1533 INSERT_ID=1 -master-bin.000001 1533 Query 1 1640 use `mysqltest2`; insert into t1 (b) values(@@character_set_server) -master-bin.000001 1640 Query 1 1798 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 1798 Intvar 1 1826 INSERT_ID=2 -master-bin.000001 1826 Query 1 1929 use `mysqltest2`; insert into t1 (b) values(@@collation_server) -master-bin.000001 1929 Query 1 2087 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 2087 Intvar 1 2115 INSERT_ID=3 -master-bin.000001 2115 Query 1 2222 use `mysqltest2`; insert into t1 (b) values(@@character_set_client) -master-bin.000001 2222 Query 1 2380 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 2380 Intvar 1 2408 INSERT_ID=4 -master-bin.000001 2408 Query 1 2519 use `mysqltest2`; insert into t1 (b) values(@@character_set_connection) -master-bin.000001 2519 Query 1 2677 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 2677 Intvar 1 2705 INSERT_ID=5 -master-bin.000001 2705 Query 1 2812 use `mysqltest2`; insert into t1 (b) values(@@collation_connection) -master-bin.000001 2812 Query 1 2969 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 2969 Query 1 3044 use `mysqltest2`; truncate table t1 -master-bin.000001 3044 Query 1 3201 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 3201 Intvar 1 3229 INSERT_ID=1 -master-bin.000001 3229 Query 1 3336 use `mysqltest2`; insert into t1 (b) values(@@collation_connection) -master-bin.000001 3336 Query 1 3493 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 3493 Intvar 1 3521 INSERT_ID=2 -master-bin.000001 3521 Query 1 3631 use `mysqltest2`; insert into t1 (b) values(LEAST("Müller","Muffler")) -master-bin.000001 3631 Query 1 3789 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 3789 Intvar 1 3817 INSERT_ID=3 -master-bin.000001 3817 Query 1 3924 use `mysqltest2`; insert into t1 (b) values(@@collation_connection) -master-bin.000001 3924 Query 1 4082 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 4082 Intvar 1 4110 INSERT_ID=4 -master-bin.000001 4110 Query 1 4220 use `mysqltest2`; insert into t1 (b) values(LEAST("Müller","Muffler")) -master-bin.000001 4220 Query 1 4378 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 4378 Intvar 1 4406 INSERT_ID=74 -master-bin.000001 4406 Create_file 1 5074 db=mysqltest2;table=t1;file_id=1;block_len=581 -master-bin.000001 5074 Query 1 5232 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 5232 Intvar 1 5260 INSERT_ID=5 -master-bin.000001 5260 Exec_load 1 5283 ;file_id=1 -master-bin.000001 5283 Query 1 5441 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 5441 Query 1 5516 use `mysqltest2`; truncate table t1 -master-bin.000001 5516 Query 1 5674 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 5674 Intvar 1 5702 INSERT_ID=1 -master-bin.000001 5702 User var 1 5742 @`a`=_cp850 0x4DFC6C6C6572 COLLATE cp850_general_ci -master-bin.000001 5742 Query 1 5840 use `mysqltest2`; insert into t1 (b) values(collation(@a)) -master-bin.000001 5840 Query 1 5998 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 5998 Query 1 6075 use `mysqltest2`; drop database mysqltest2 -master-bin.000001 6075 Query 1 6228 SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 6228 Query 1 6300 drop database mysqltest3 +master-bin.000001 # Query 1 # use `test`; drop database if exists mysqltest2 +master-bin.000001 # Query 1 # use `test`; drop database if exists mysqltest3 +master-bin.000001 # Query 1 # use `test`; create database mysqltest2 character set latin2 +master-bin.000001 # Query 1 # use `test`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=8,COLLATION_SERVER=30 +master-bin.000001 # Query 1 # use `test`; create database mysqltest3 +master-bin.000001 # Query 1 # use `test`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=8,COLLATION_SERVER=64 +master-bin.000001 # Query 1 # use `test`; drop database mysqltest3 +master-bin.000001 # Query 1 # use `test`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=8,COLLATION_SERVER=64 +master-bin.000001 # Query 1 # use `test`; create database mysqltest3 +master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Query 1 # use `mysqltest2`; create table t1 (a int auto_increment primary key, b varchar(100)) +master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Intvar 1 # INSERT_ID=1 +master-bin.000001 # Query 1 # use `mysqltest2`; insert into t1 (b) values(@@character_set_server) +master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Intvar 1 # INSERT_ID=2 +master-bin.000001 # Query 1 # use `mysqltest2`; insert into t1 (b) values(@@collation_server) +master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Intvar 1 # INSERT_ID=3 +master-bin.000001 # Query 1 # use `mysqltest2`; insert into t1 (b) values(@@character_set_client) +master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Intvar 1 # INSERT_ID=4 +master-bin.000001 # Query 1 # use `mysqltest2`; insert into t1 (b) values(@@character_set_connection) +master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Intvar 1 # INSERT_ID=5 +master-bin.000001 # Query 1 # use `mysqltest2`; insert into t1 (b) values(@@collation_connection) +master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Query 1 # use `mysqltest2`; truncate table t1 +master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Intvar 1 # INSERT_ID=1 +master-bin.000001 # Query 1 # use `mysqltest2`; insert into t1 (b) values(@@collation_connection) +master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Intvar 1 # INSERT_ID=2 +master-bin.000001 # Query 1 # use `mysqltest2`; insert into t1 (b) values(LEAST("Müller","Muffler")) +master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Intvar 1 # INSERT_ID=3 +master-bin.000001 # Query 1 # use `mysqltest2`; insert into t1 (b) values(@@collation_connection) +master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Intvar 1 # INSERT_ID=4 +master-bin.000001 # Query 1 # use `mysqltest2`; insert into t1 (b) values(LEAST("Müller","Muffler")) +master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Intvar 1 # INSERT_ID=74 +master-bin.000001 # Create_file 1 # db=mysqltest2;table=t1;file_id=1;block_len=581 +master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Intvar 1 # INSERT_ID=5 +master-bin.000001 # Exec_load 1 # ;file_id=1 +master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Query 1 # use `mysqltest2`; truncate table t1 +master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Intvar 1 # INSERT_ID=1 +master-bin.000001 # User var 1 # @`a`=_cp850 0x4DFC6C6C6572 COLLATE cp850_general_ci +master-bin.000001 # Query 1 # use `mysqltest2`; insert into t1 (b) values(collation(@a)) +master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Query 1 # use `mysqltest2`; drop database mysqltest2 +master-bin.000001 # Query 1 # SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 # Query 1 # drop database mysqltest3 set global character_set_server=latin2; ERROR HY000: Binary logging and replication forbid changing the global server character set or collation set global character_set_server=latin2; diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result index 6b36b3bb2bf..46fe452d9e4 100644 --- a/mysql-test/r/sp-error.result +++ b/mysql-test/r/sp-error.result @@ -44,7 +44,7 @@ call foo()| ERROR 42000: PROCEDURE test.foo does not exist drop procedure if exists foo| Warnings: -Warning 1304 PROCEDURE foo does not exist +Note 1305 PROCEDURE foo does not exist show create procedure foo| ERROR 42000: PROCEDURE foo does not exist create procedure foo() @@ -121,7 +121,7 @@ declare y int; set x = y; end| Warnings: -Warning 1310 Referring to uninitialized variable y +Warning 1311 Referring to uninitialized variable y drop procedure foo| create procedure foo() return 42| diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index afd8c3430b3..7ea95a067c4 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -1,6 +1,28 @@ use test; -drop table if exists t1; -drop table if exists t2; +drop table if exists t1,t2,t3,t4; +drop procedure if exists goto1; +drop procedure if exists goto2; +drop procedure if exists goto3; +drop procedure if exists goto4; +drop procedure if exists goto5; +drop procedure if exists goto6; +drop procedure if exists into_outfile; +drop procedure if exists into_dumpfile; +drop procedure if exists create_select; +drop procedure if exists bar; +drop procedure if exists hndlr1; +drop procedure if exists hndlr2; +drop procedure if exists hndlr3; +drop procedure if exists hndlr4; +drop procedure if exists cur1; +drop procedure if exists cur2; +drop procedure if exists chistics; +drop procedure if exists chistics2; +drop procedure if exists modes; +drop procedure if exists dummy; +drop procedure if exists bug; +drop procedure if exists bug2; +drop function if exists fac; create table t1 ( id char(16) not null, data int not null @@ -78,7 +100,6 @@ foo 1 kaka 3 delete from t1| drop procedure setcontext| -drop table if exists t3| create table t3 ( d date, i int, f double, s varchar(32) )| create procedure nullset() begin @@ -962,7 +983,7 @@ select row_count()| row_count() -1 drop procedure rc| -create procedure bug822(a_id char(16), a_data int) +create procedure bug(a_id char(16), a_data int) begin declare n int; select count(*) into n from t1 where id = a_id and data = a_data; @@ -970,16 +991,16 @@ if n = 0 then insert into t1 (id, data) values (a_id, a_data); end if; end| -call bug822('foo', 42)| -call bug822('foo', 42)| -call bug822('bar', 666)| +call bug('foo', 42)| +call bug('foo', 42)| +call bug('bar', 666)| select * from t1| id data foo 42 bar 666 delete from t1| -drop procedure bug822| -create procedure bug1495() +drop procedure bug| +create procedure bug() begin declare x int; select data into x from t1 order by id limit 1; @@ -990,18 +1011,18 @@ insert into t1 values ("more", x+10); end if; end| insert into t1 values ('foo', 12)| -call bug1495()| +call bug()| delete from t1 where id='foo'| insert into t1 values ('bar', 7)| -call bug1495()| +call bug()| delete from t1 where id='bar'| select * from t1| id data less 2 more 17 delete from t1| -drop procedure bug1495| -create procedure bug1547(s char(16)) +drop procedure bug| +create procedure bug(s char(16)) begin declare x int; select data into x from t1 where s = id limit 1; @@ -1012,8 +1033,8 @@ insert into t1 values ("more", x+10); end if; end| insert into t1 values ("foo", 12), ("bar", 7)| -call bug1547("foo")| -call bug1547("bar")| +call bug("foo")| +call bug("bar")| select * from t1| id data foo 12 @@ -1021,34 +1042,34 @@ bar 7 less 2 more 17 delete from t1| -drop procedure bug1547| +drop procedure bug| drop table if exists t70| create table t70 (s1 int,s2 int)| insert into t70 values (1,2)| -create procedure bug1656(out p1 int, out p2 int) +create procedure bug(out p1 int, out p2 int) select * into p1, p1 from t70| -call bug1656(@1, @2)| +call bug(@1, @2)| select @1, @2| @1 @2 2 NULL drop table t70| -drop procedure bug1656| +drop procedure bug| drop table if exists t3| create table t3(a int)| -create procedure bug1862() +create procedure bug() begin insert into t3 values(2); flush tables; end| -call bug1862()| -call bug1862()| +call bug()| +call bug()| select * from t3| a 2 2 drop table t3| -drop procedure bug1862| -create procedure bug1874() +drop procedure bug| +create procedure bug() begin declare x int; declare y double; @@ -1062,7 +1083,7 @@ select avg(data) into y from t1; insert into t2 values ("avg", 0, y); end| insert into t1 (data) values (3), (1), (5), (9), (4)| -call bug1874()| +call bug()| select * from t2| s i d max 9 0 @@ -1071,8 +1092,8 @@ sum 22 0 avg 0 4.4 delete from t1| delete from t2| -drop procedure bug1874| -create procedure bug2260() +drop procedure bug| +create procedure bug() begin declare v1 int; declare c1 cursor for select data from t1; @@ -1082,11 +1103,11 @@ fetch c1 into v1; set @x2 = 2; close c1; end| -call bug2260()| +call bug()| select @x2| @x2 2 -drop procedure bug2260| +drop procedure bug| create procedure bug2267_1() begin show procedure status; @@ -1133,35 +1154,35 @@ drop procedure bug2267_1| drop procedure bug2267_2| drop procedure bug2267_3| drop procedure bug2267_4| -create procedure bug2227(x int) +create procedure bug(x int) begin declare y float default 2.6; declare z char(16) default "zzz"; select 1.3, x, y, 42, z; end| -call bug2227(9)| +call bug(9)| 1.3 x y 42 z 1.3 9 2.6 42 zzz -drop procedure bug2227| -create procedure bug2614() +drop procedure bug| +create procedure bug() begin drop table if exists t3; create table t3 (id int default '0' not null); insert into t3 select 12; insert into t3 select * from t3; end| -call bug2614()| -call bug2614()| +call bug()| +call bug()| drop table t3| -drop procedure bug2614| -create function bug2674 () returns int +drop procedure bug| +create function bug () returns int return @@sort_buffer_size| set @osbs = @@sort_buffer_size| set @@sort_buffer_size = 262000| -select bug2674()| -bug2674() +select bug()| +bug() 262000 -drop function bug2674| +drop function bug| set @@sort_buffer_size = @osbs| create procedure bug3259_1 () begin end| create procedure BUG3259_2 () begin end| @@ -1175,48 +1196,48 @@ call bUG3259_3()| drop procedure bUg3259_1| drop procedure BuG3259_2| drop procedure BUG3259_3| -create function bug2772() returns char(10) character set latin2 +create function bug() returns char(10) character set latin2 return 'a'| -select bug2772()| -bug2772() +select bug()| +bug() a -drop function bug2772| -create procedure bug2776_1(out x int) +drop function bug| +create procedure bug(out x int) begin declare v int; set v = default; set x = v; end| -create procedure bug2776_2(out x int) +create procedure bug2(out x int) begin declare v int default 42; set v = default; set x = v; end| set @x = 1| -call bug2776_1(@x)| +call bug(@x)| select @x| @x NULL -call bug2776_2(@x)| +call bug2(@x)| select @x| @x 42 -drop procedure bug2776_1| -drop procedure bug2776_2| +drop procedure bug| +drop procedure bug2| drop table if exists t3| create table t3 (s1 smallint)| insert into t3 values (123456789012)| Warnings: Warning 1264 Data truncated; out of range for column 's1' at row 1 -create procedure bug2780() +create procedure bug() begin declare exit handler for sqlwarning set @x = 1; set @x = 0; insert into t3 values (123456789012); insert into t3 values (0); end| -call bug2780()| +call bug()| select @x| @x 1 @@ -1224,13 +1245,11 @@ select * from t3| s1 32767 32767 -drop procedure bug2780| +drop procedure bug| drop table t3| -drop table if exists t3| create table t3 (content varchar(10) )| insert into t3 values ("test1")| insert into t3 values ("test2")| -drop table if exists t4| create table t4 (f1 int, rc int, t3 int)| create procedure bug1863(in1 int) begin @@ -1406,7 +1425,6 @@ select @a| @a 5 drop procedure bug3863| -drop table if exists t3| create table t3 ( id int(10) unsigned not null default 0, rid int(10) unsigned not null default 0, diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result index 95bba1d4ec7..2f564112e40 100644 --- a/mysql-test/r/type_blob.result +++ b/mysql-test/r/type_blob.result @@ -9,9 +9,9 @@ d mediumtext YES NULL e longtext YES NULL CREATE TABLE t2 (a char(257), b varbinary(70000), c varchar(70000000)); Warnings: -Warning 1246 Converting column 'a' from CHAR to TEXT -Warning 1246 Converting column 'b' from CHAR to BLOB -Warning 1246 Converting column 'c' from CHAR to TEXT +Note 1246 Converting column 'a' from CHAR to TEXT +Note 1246 Converting column 'b' from CHAR to BLOB +Note 1246 Converting column 'c' from CHAR to TEXT show columns from t2; Field Type Null Key Default Extra a text YES NULL diff --git a/mysql-test/r/user_var.result b/mysql-test/r/user_var.result index 9bbfefeb34a..80d8af095cb 100644 --- a/mysql-test/r/user_var.result +++ b/mysql-test/r/user_var.result @@ -23,7 +23,11 @@ i @vv1:=if(sv1.i,1,0) @vv2:=if(sv2.i,1,0) @vv3:=if(sv3.i,1,0) @vv1+@vv2+@vv3 2 1 0 0 1 explain select * from t1 where i=@vv1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref i i 4 const 1 Using where +1 SIMPLE t1 ref i i 4 const 1 +select @vv1,i,v from t1 where i=@vv1; +@vv1 i v +1 1 1 +1 1 3 explain select * from t1 where @vv1:=@vv1+1 and i=@vv1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where @@ -32,7 +36,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL i 4 NULL 3 Using where; Using index explain select * from t1 where i=@vv1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref i i 4 const 1 Using where +1 SIMPLE t1 ref i i 4 const 1 drop table t1,t2; set @a=0,@b=0; select @a:=10, @b:=1, @a > @b, @a < @b; diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index a84cf733f21..399e6af848c 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -364,7 +364,7 @@ set sql_log_bin=1; set sql_log_off=1; set sql_log_update=1; Warnings: -Note 1314 The update log is deprecated and replaced by the binary log; SET SQL_LOG_UPDATE has been ignored +Note 1315 The update log is deprecated and replaced by the binary log; SET SQL_LOG_UPDATE has been ignored set sql_low_priority_updates=1; set sql_max_join_size=200; select @@sql_max_join_size,@@max_join_size; diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 18da4882894..ef3743877c6 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -761,7 +761,7 @@ set sql_updatable_view_key=LIMIT1; update v1 set x=x+1; update v2 set x=x+1; Warnings: -Note 1354 View being updated does not have complete key of underlying table in it +Note 1355 View being updated does not have complete key of underlying table in it update v1 set x=x+1 limit 1; update v2 set x=x+1 limit 1; ERROR HY000: The target table v2 of the UPDATE is not updatable @@ -769,7 +769,7 @@ set sql_updatable_view_key=NO; update v1 set x=x+1 limit 1; update v2 set x=x+1 limit 1; Warnings: -Note 1354 View being updated does not have complete key of underlying table in it +Note 1355 View being updated does not have complete key of underlying table in it set sql_updatable_view_key=DEFAULT; select * from t1; a b c @@ -979,7 +979,7 @@ create view v1 (a,a) as select 'a','a'; ERROR 42S21: Duplicate column name 'a' create procedure p1 () begin declare v int; create view v1 as select v; end;// Warnings: -Warning 1310 Referring to uninitialized variable v +Warning 1311 Referring to uninitialized variable v call p1(); ERROR HY000: View's SELECT contains a variable or parameter drop procedure p1; diff --git a/mysql-test/t/mysqlbinlog2.test b/mysql-test/t/mysqlbinlog2.test index c6cff7558d4..6a4c99c746d 100644 --- a/mysql-test/t/mysqlbinlog2.test +++ b/mysql-test/t/mysqlbinlog2.test @@ -46,11 +46,11 @@ select "--- offset --" as ""; --disable_query_log select "--- start-position --" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --start-position=497 $MYSQL_TEST_DIR/var/log/master-bin.000001 +--exec $MYSQL_BINLOG --short-form --start-position=601 $MYSQL_TEST_DIR/var/log/master-bin.000001 --disable_query_log select "--- stop-position --" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --stop-position=497 $MYSQL_TEST_DIR/var/log/master-bin.000001 +--exec $MYSQL_BINLOG --short-form --stop-position=601 $MYSQL_TEST_DIR/var/log/master-bin.000001 --disable_query_log select "--- start-datetime --" as ""; --enable_query_log @@ -75,11 +75,11 @@ select "--- offset --" as ""; --disable_query_log select "--- start-position --" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --start-position=497 $MYSQL_TEST_DIR/var/log/master-bin.000001 $MYSQL_TEST_DIR/var/log/master-bin.000002 +--exec $MYSQL_BINLOG --short-form --start-position=601 $MYSQL_TEST_DIR/var/log/master-bin.000001 $MYSQL_TEST_DIR/var/log/master-bin.000002 --disable_query_log select "--- stop-position --" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --stop-position=32 $MYSQL_TEST_DIR/var/log/master-bin.000001 $MYSQL_TEST_DIR/var/log/master-bin.000002 +--exec $MYSQL_BINLOG --short-form --stop-position=123 $MYSQL_TEST_DIR/var/log/master-bin.000001 $MYSQL_TEST_DIR/var/log/master-bin.000002 --disable_query_log select "--- start-datetime --" as ""; --enable_query_log @@ -102,11 +102,11 @@ select "--- offset --" as ""; --disable_query_log select "--- start-position --" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --start-position=497 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 +--exec $MYSQL_BINLOG --short-form --start-position=601 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 --disable_query_log select "--- stop-position --" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --stop-position=497 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 +--exec $MYSQL_BINLOG --short-form --stop-position=601 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 --disable_query_log select "--- start-datetime --" as ""; --enable_query_log @@ -129,11 +129,11 @@ select "--- offset --" as ""; --disable_query_log select "--- start-position --" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --start-position=497 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 master-bin.000002 +--exec $MYSQL_BINLOG --short-form --start-position=601 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 master-bin.000002 --disable_query_log select "--- stop-position --" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --stop-position=32 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 master-bin.000002 +--exec $MYSQL_BINLOG --short-form --stop-position=123 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 master-bin.000002 --disable_query_log select "--- start-datetime --" as ""; --enable_query_log diff --git a/mysql-test/t/null.test b/mysql-test/t/null.test index 7d30fd06ba7..027443c485a 100644 --- a/mysql-test/t/null.test +++ b/mysql-test/t/null.test @@ -100,9 +100,9 @@ drop table t1; select cast(NULL as signed); # -# Test case for bug #4256 +# IS NULL is unable to use index in range if column is declared not null +# (Bug #4256) # - create table t1(i int, key(i)); insert into t1 values(1); insert into t1 select i*2 from t1; @@ -114,8 +114,11 @@ insert into t1 select i*2 from t1; insert into t1 select i*2 from t1; insert into t1 select i*2 from t1; insert into t1 select i*2 from t1; +insert into t1 values(null); explain select * from t1 where i=2 or i is null; +select count(*) from t1 where i=2 or i is null; alter table t1 change i i int not null; explain select * from t1 where i=2 or i is null; +select count(*) from t1 where i=2 or i is null; drop table t1; diff --git a/mysql-test/t/null_key.test b/mysql-test/t/null_key.test index 9b346a181bf..d7f6a634d1e 100644 --- a/mysql-test/t/null_key.test +++ b/mysql-test/t/null_key.test @@ -26,6 +26,8 @@ select * from t1 where (a is null or a > 0 and a < 3) and b < 5 limit 3; select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3; select * from t1 where (a is null or a = 7) and b=7; select * from t1 where a is null and b=9 or a is null and b=7 limit 3; +select * from t1 where a > 1 and a < 3 limit 1; +select * from t1 where a > 8 and a < 9; create table t2 like t1; insert into t2 select * from t1; alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10)); diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index 61886221fcf..0059cd5bd37 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -195,13 +195,15 @@ explain select count(*) from t1 where x in (1,2); drop table t1; # -# bug #1172 +# bug #1172: "Force index" option caused server crash # CREATE TABLE t1 (key1 int(11) NOT NULL default '0', KEY i1 (key1)); INSERT INTO t1 VALUES (0),(0),(1),(1); CREATE TABLE t2 (keya int(11) NOT NULL default '0', KEY j1 (keya)); INSERT INTO t2 VALUES (0),(0),(1),(1),(2),(2); explain select * from t1, t2 where (t1.key1 <t2.keya + 1) and t2.keya=3; +explain select * from t1 force index(i1), t2 force index(j1) where + (t1.key1 <t2.keya + 1) and t2.keya=3; DROP TABLE t1,t2; # @@ -415,14 +417,17 @@ drop table t1; --disable_warnings create table t1 (x bigint unsigned not null primary key) engine=innodb; --enable_warnings -insert into t1(x) values (0xfffffffffffffff0); -insert into t1(x) values (0xfffffffffffffff1); +insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1); select * from t1; select count(*) from t1 where x>0; select count(*) from t1 where x=0; select count(*) from t1 where x<0; select count(*) from t1 where x < -16; select count(*) from t1 where x = -16; +explain select count(*) from t1 where x > -16; + +# The following result should be (2). To be fixed when we add 'unsigned flag' to +# Field::store(longlong) select count(*) from t1 where x > -16; select count(*) from t1 where x = 18446744073709551601; diff --git a/mysql-test/t/rpl_charset.test b/mysql-test/t/rpl_charset.test index ba2ebe03a12..74112ac44fe 100644 --- a/mysql-test/t/rpl_charset.test +++ b/mysql-test/t/rpl_charset.test @@ -106,6 +106,7 @@ select * from mysqltest2.t1 order by a; connection master; drop database mysqltest2; drop database mysqltest3; +--replace_column 2 # 5 # show binlog events from 95; sync_slave_with_master; diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test index f8abab0e7e3..d07c866ff79 100644 --- a/mysql-test/t/sp-error.test +++ b/mysql-test/t/sp-error.test @@ -49,18 +49,18 @@ create function func1() returns int return 42| # Can't create recursively ---error 1302 +--error 1303 create procedure foo() create procedure bar() set @x=3| ---error 1302 +--error 1303 create procedure foo() create function bar() returns double return 2.3| # Already exists ---error 1303 +--error 1304 create procedure proc1() set @x = 42| ---error 1303 +--error 1304 create function func1() returns int return 42| @@ -68,42 +68,42 @@ drop procedure proc1| drop function func1| # Does not exist ---error 1304 +--error 1305 alter procedure foo| ---error 1304 +--error 1305 alter function foo| ---error 1304 +--error 1305 drop procedure foo| ---error 1304 +--error 1305 drop function foo| ---error 1304 +--error 1305 call foo()| drop procedure if exists foo| ---error 1304 +--error 1305 show create procedure foo| # LEAVE/ITERATE/GOTO with no match ---error 1307 +--error 1308 create procedure foo() foo: loop leave bar; end loop| ---error 1307 +--error 1308 create procedure foo() foo: loop iterate bar; end loop| ---error 1307 +--error 1308 create procedure foo() foo: begin iterate foo; end| ---error 1307 +--error 1308 create procedure foo() begin goto foo; end| ---error 1307 +--error 1308 create procedure foo() begin begin @@ -111,7 +111,7 @@ begin end; goto foo; end| ---error 1307 +--error 1308 create procedure foo() begin goto foo; @@ -119,7 +119,7 @@ begin label foo; end; end| ---error 1307 +--error 1308 create procedure foo() begin begin @@ -129,7 +129,7 @@ begin label foo; end; end| ---error 1307 +--error 1308 create procedure foo() begin begin @@ -141,7 +141,7 @@ begin end| # Redefining label ---error 1308 +--error 1309 create procedure foo() foo: loop foo: loop @@ -150,7 +150,7 @@ foo: loop end loop foo| # End label mismatch ---error 1309 +--error 1310 create procedure foo() foo: loop set @x=2; @@ -165,12 +165,12 @@ end| drop procedure foo| # RETURN in FUNCTION only ---error 1312 +--error 1313 create procedure foo() return 42| # Doesn't allow queries in FUNCTIONs (for now :-( ) ---error 1313 +--error 1314 create function foo() returns int begin declare x int; @@ -184,19 +184,19 @@ create procedure p(x int) create function f(x int) returns int return x+42| ---error 1317 +--error 1318 call p()| ---error 1317 +--error 1318 call p(1, 2)| ---error 1317 +--error 1318 select f()| ---error 1317 +--error 1318 select f(1, 2)| drop procedure p| drop function f| ---error 1318 +--error 1319 create procedure p(val int, out res int) begin declare x int default 0; @@ -210,7 +210,7 @@ begin end if; end| ---error 1318 +--error 1319 create procedure p(val int, out res int) begin declare x int default 0; @@ -225,7 +225,7 @@ begin end if; end| ---error 1319 +--error 1320 create function f(val int) returns int begin declare x int; @@ -243,12 +243,12 @@ begin end if; end| ---error 1320 +--error 1321 select f(10)| drop function f| ---error 1321 +--error 1322 create procedure p() begin declare c cursor for insert into test.t1 values ("foo", 42); @@ -257,7 +257,7 @@ begin close c; end| ---error 1322 +--error 1323 create procedure p() begin declare x int; @@ -267,7 +267,7 @@ begin close c; end| ---error 1323 +--error 1324 create procedure p() begin declare c cursor for select * from test.t; @@ -289,7 +289,7 @@ begin open c; close c; end| ---error 1324 +--error 1325 call p()| drop procedure p| @@ -301,11 +301,11 @@ begin close c; close c; end| ---error 1325 +--error 1326 call p()| drop procedure p| ---error 1304 +--error 1305 alter procedure bar3 sql security invoker| --error 1059 alter procedure bar3 name @@ -319,7 +319,7 @@ drop table if exists t1| create table t1 (val int, x float)| insert into t1 values (42, 3.1), (19, 1.2)| ---error 1326 +--error 1327 create procedure p() begin declare x int; @@ -339,7 +339,7 @@ begin fetch c into x; close c; end| ---error 1327 +--error 1328 call p()| drop procedure p| @@ -354,34 +354,34 @@ begin fetch c into x, y, z; close c; end| ---error 1327 +--error 1328 call p()| drop procedure p| ---error 1329 +--error 1330 create procedure p(in x int, x char(10)) begin end| ---error 1329 +--error 1330 create function p(x int, x char(10)) begin end| ---error 1330 +--error 1331 create procedure p() begin declare x float; declare x int; end| ---error 1331 +--error 1332 create procedure p() begin declare c condition for 1064; declare c condition for 1065; end| ---error 1332 +--error 1333 create procedure p() begin declare c cursor for select * from t1; @@ -389,18 +389,18 @@ begin end| # USE is not allowed ---error 1335 +--error 1336 create procedure u() use sptmp| # Enforced standard order of declarations ---error 1336 +--error 1337 create procedure p() begin declare c cursor for select * from t1; declare x int; end| ---error 1336 +--error 1337 create procedure p() begin declare x int; @@ -408,7 +408,7 @@ begin declare foo condition for sqlstate '42S99'; end| ---error 1337 +--error 1338 create procedure p() begin declare x int; @@ -416,7 +416,7 @@ begin declare c cursor for select * from t1; end| ---error 1357 +--error 1358 create procedure p() begin declare continue handler for sqlexception @@ -445,13 +445,13 @@ drop procedure bug1965| # # BUG#1966 # ---error 1326 +--error 1327 select 1 into a| # # BUG#1654 # ---error 1313 +--error 1314 create function bug1654() returns int return (select sum(t.data) from test.t2 t)| @@ -489,7 +489,7 @@ begin fetch c1 into v1; end| ---error 1325 +--error 1326 call bug2259()| drop procedure bug2259| @@ -545,7 +545,7 @@ begin end case; return 2; end| ---error 1338 +--error 1339 select bug3287()| drop function bug3287| @@ -556,7 +556,7 @@ when 0 then when 1 then insert into test.t1 values (x, 1.1); end case| ---error 1338 +--error 1339 call bug3287(2)| drop procedure bug3287| @@ -622,9 +622,9 @@ drop procedure bug2653_2| # # BUG#4344 # ---error 1356 +--error 1357 create procedure bug4344() drop procedure bug4344| ---error 1356 +--error 1357 create procedure bug4344() drop function bug4344| diff --git a/mysql-test/t/sp-security.test b/mysql-test/t/sp-security.test index d1c9c6c639d..4804d44e63a 100644 --- a/mysql-test/t/sp-security.test +++ b/mysql-test/t/sp-security.test @@ -60,7 +60,7 @@ select * from db1_secret.t1; # ...and not this --error 1049 create procedure db1_secret.dummy() begin end; ---error 1304 +--error 1305 drop procedure db1_secret.dummy; @@ -80,7 +80,7 @@ select * from db1_secret.t1; # ...and not this --error 1049 create procedure db1_secret.dummy() begin end; ---error 1304 +--error 1305 drop procedure db1_secret.dummy; diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 92d6110cf7a..bc811607ef1 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -8,8 +8,30 @@ use test; --disable_warnings -drop table if exists t1; -drop table if exists t2; +drop table if exists t1,t2,t3,t4; +drop procedure if exists goto1; +drop procedure if exists goto2; +drop procedure if exists goto3; +drop procedure if exists goto4; +drop procedure if exists goto5; +drop procedure if exists goto6; +drop procedure if exists into_outfile; +drop procedure if exists into_dumpfile; +drop procedure if exists create_select; +drop procedure if exists bar; +drop procedure if exists hndlr1; +drop procedure if exists hndlr2; +drop procedure if exists hndlr3; +drop procedure if exists hndlr4; +drop procedure if exists cur1; +drop procedure if exists cur2; +drop procedure if exists chistics; +drop procedure if exists chistics2; +drop procedure if exists modes; +drop procedure if exists dummy; +drop procedure if exists bug; +drop procedure if exists bug2; +drop function if exists fac; --enable_warnings create table t1 ( @@ -114,9 +136,6 @@ drop procedure setcontext| # Set things to null ---disable_warnings -drop table if exists t3| ---enable_warnings create table t3 ( d date, i int, f double, s varchar(32) )| create procedure nullset() @@ -1076,7 +1095,7 @@ drop procedure rc| # # BUG#822 # -create procedure bug822(a_id char(16), a_data int) +create procedure bug(a_id char(16), a_data int) begin declare n int; select count(*) into n from t1 where id = a_id and data = a_data; @@ -1085,17 +1104,17 @@ begin end if; end| -call bug822('foo', 42)| -call bug822('foo', 42)| -call bug822('bar', 666)| +call bug('foo', 42)| +call bug('foo', 42)| +call bug('bar', 666)| select * from t1| delete from t1| -drop procedure bug822| +drop procedure bug| # # BUG#1495 # -create procedure bug1495() +create procedure bug() begin declare x int; @@ -1108,19 +1127,19 @@ begin end| insert into t1 values ('foo', 12)| -call bug1495()| +call bug()| delete from t1 where id='foo'| insert into t1 values ('bar', 7)| -call bug1495()| +call bug()| delete from t1 where id='bar'| select * from t1| delete from t1| -drop procedure bug1495| +drop procedure bug| # # BUG#1547 # -create procedure bug1547(s char(16)) +create procedure bug(s char(16)) begin declare x int; @@ -1133,11 +1152,11 @@ begin end| insert into t1 values ("foo", 12), ("bar", 7)| -call bug1547("foo")| -call bug1547("bar")| +call bug("foo")| +call bug("bar")| select * from t1| delete from t1| -drop procedure bug1547| +drop procedure bug| # # BUG#1656 @@ -1148,13 +1167,13 @@ drop table if exists t70| create table t70 (s1 int,s2 int)| insert into t70 values (1,2)| -create procedure bug1656(out p1 int, out p2 int) +create procedure bug(out p1 int, out p2 int) select * into p1, p1 from t70| -call bug1656(@1, @2)| +call bug(@1, @2)| select @1, @2| drop table t70| -drop procedure bug1656| +drop procedure bug| # # BUG#1862 @@ -1164,23 +1183,23 @@ drop table if exists t3| --enable_warnings create table t3(a int)| -create procedure bug1862() +create procedure bug() begin insert into t3 values(2); flush tables; end| -call bug1862()| +call bug()| # the second call caused a segmentation -call bug1862()| +call bug()| select * from t3| drop table t3| -drop procedure bug1862| +drop procedure bug| # # BUG#1874 # -create procedure bug1874() +create procedure bug() begin declare x int; declare y double; @@ -1195,16 +1214,16 @@ begin end| insert into t1 (data) values (3), (1), (5), (9), (4)| -call bug1874()| +call bug()| select * from t2| delete from t1| delete from t2| -drop procedure bug1874| +drop procedure bug| # # BUG#2260 # -create procedure bug2260() +create procedure bug() begin declare v1 int; declare c1 cursor for select data from t1; @@ -1216,9 +1235,9 @@ begin close c1; end| -call bug2260()| +call bug()| select @x2| -drop procedure bug2260| +drop procedure bug| # # BUG#2267 @@ -1258,7 +1277,7 @@ drop procedure bug2267_4| # # BUG#2227 # -create procedure bug2227(x int) +create procedure bug(x int) begin declare y float default 2.6; declare z char(16) default "zzz"; @@ -1266,13 +1285,13 @@ begin select 1.3, x, y, 42, z; end| -call bug2227(9)| -drop procedure bug2227| +call bug(9)| +drop procedure bug| # # BUG#2614 # -create procedure bug2614() +create procedure bug() begin drop table if exists t3; create table t3 (id int default '0' not null); @@ -1281,22 +1300,22 @@ begin end| --disable_warnings -call bug2614()| +call bug()| --enable_warnings -call bug2614()| +call bug()| drop table t3| -drop procedure bug2614| +drop procedure bug| # # BUG#2674 # -create function bug2674 () returns int +create function bug () returns int return @@sort_buffer_size| set @osbs = @@sort_buffer_size| set @@sort_buffer_size = 262000| -select bug2674()| -drop function bug2674| +select bug()| +drop function bug| set @@sort_buffer_size = @osbs| # @@ -1320,16 +1339,16 @@ drop procedure BUG3259_3| # # BUG##2772 # -create function bug2772() returns char(10) character set latin2 +create function bug() returns char(10) character set latin2 return 'a'| -select bug2772()| -drop function bug2772| +select bug()| +drop function bug| # # BUG#2776 # -create procedure bug2776_1(out x int) +create procedure bug(out x int) begin declare v int; @@ -1337,7 +1356,7 @@ begin set x = v; end| -create procedure bug2776_2(out x int) +create procedure bug2(out x int) begin declare v int default 42; @@ -1346,12 +1365,12 @@ begin end| set @x = 1| -call bug2776_1(@x)| +call bug(@x)| select @x| -call bug2776_2(@x)| +call bug2(@x)| select @x| -drop procedure bug2776_1| -drop procedure bug2776_2| +drop procedure bug| +drop procedure bug2| # # BUG#2780 @@ -1363,7 +1382,7 @@ create table t3 (s1 smallint)| insert into t3 values (123456789012)| -create procedure bug2780() +create procedure bug() begin declare exit handler for sqlwarning set @x = 1; @@ -1372,26 +1391,19 @@ begin insert into t3 values (0); end| -call bug2780()| +call bug()| select @x| select * from t3| -drop procedure bug2780| +drop procedure bug| drop table t3| # # BUG#1863 # ---disable_warnings -drop table if exists t3| ---enable_warnings create table t3 (content varchar(10) )| insert into t3 values ("test1")| insert into t3 values ("test2")| - ---disable_warnings -drop table if exists t4| ---enable_warnings create table t4 (f1 int, rc int, t3 int)| create procedure bug1863(in1 int) @@ -1591,9 +1603,7 @@ drop procedure bug3863| # # BUG#2460 # ---disable_warnings -drop table if exists t3| ---enable_warnings + create table t3 ( id int(10) unsigned not null default 0, rid int(10) unsigned not null default 0, diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test index 412003aa547..48ca9e0ba32 100644 --- a/mysql-test/t/user_var.test +++ b/mysql-test/t/user_var.test @@ -20,6 +20,7 @@ insert into t2 select distinct i from t1; select * from t2; select distinct t2.i,@vv1:=if(sv1.i,1,0),@vv2:=if(sv2.i,1,0),@vv3:=if(sv3.i,1,0), @vv1+@vv2+@vv3 from t2 left join t1 as sv1 on sv1.i=t2.i and sv1.v=1 left join t1 as sv2 on sv2.i=t2.i and sv2.v=2 left join t1 as sv3 on sv3.i=t2.i and sv3.v=3; explain select * from t1 where i=@vv1; +select @vv1,i,v from t1 where i=@vv1; explain select * from t1 where @vv1:=@vv1+1 and i=@vv1; explain select @vv1:=i from t1 where i=@vv1; explain select * from t1 where i=@vv1; diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index e73133afa67..51e81987cd9 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -15,7 +15,7 @@ create view v1 (c,d) as select a,b from t1; create temporary table t1 (a int, b int); #view on temporary table --- error 1351 +-- error 1352 create view v1 (c) as select b+1 from t1; drop table t1; @@ -23,7 +23,7 @@ create table t1 (a int, b int); insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10); #view with variable --- error 1350 +-- error 1351 create view v1 (c,d) as select a,b+@@global.max_user_connections from t1; # simple view @@ -38,7 +38,7 @@ select * from t1; select c from v1; show create table v1; show create view v1; --- error 1346 +-- error 1347 show create view t1; drop table t1; @@ -131,7 +131,7 @@ show grants for test@localhost; drop view v100; #try to drop table with DROP VIEW --- error 1346 +-- error 1347 drop view t1; #try to drop VIEW with DROP TABLE @@ -265,21 +265,21 @@ select c from mysqltest.v4; show columns from mysqltest.v1; show columns from mysqltest.v2; # but explain/show do not --- error 1344 +-- error 1345 explain select c from mysqltest.v1; --- error 1344 +-- error 1345 show create table mysqltest.v1; --- error 1344 +-- error 1345 explain select c from mysqltest.v2; --- error 1344 +-- error 1345 show create table mysqltest.v2; --- error 1344 +-- error 1345 explain select c from mysqltest.v3; --- error 1344 +-- error 1345 show create table mysqltest.v3; --- error 1344 +-- error 1345 explain select c from mysqltest.v4; --- error 1344 +-- error 1345 show create table mysqltest.v4; # allow to see one of underlaing table @@ -292,13 +292,13 @@ show create table mysqltest.v1; explain select c from mysqltest.v2; show create table mysqltest.v2; # but other EXPLAINs do not --- error 1344 +-- error 1345 explain select c from mysqltest.v3; --- error 1344 +-- error 1345 show create table mysqltest.v3; --- error 1344 +-- error 1345 explain select c from mysqltest.v4; --- error 1344 +-- error 1345 show create table mysqltest.v4; # allow to see any view in mysqltest database @@ -445,7 +445,7 @@ insert into t1 values (10,2), (20,3), (30,4), (40,5), (50,10); create view v1 (a,c) as select a, b+1 from t1; create algorithm=temptable view v2 (a,c) as select a, b+1 from t1; # try to update expression --- error 1347 +-- error 1348 update v1 set c=a+c; # try to update VIEW with forced TEMPORARY TABLE algorithm -- error 1288 @@ -467,7 +467,7 @@ insert into t2 values (10), (20); create view v1 (a,c) as select a, b+1 from t1; create algorithm=temptable view v2 (a,c) as select a, b+1 from t1; # try to update expression --- error 1347 +-- error 1348 update t2,v1 set v1.c=v1.a+v1.c where t2.x=v1.a; # try to update VIEW with forced TEMPORARY TABLE algorithm -- error 1288 @@ -798,7 +798,7 @@ connection user1; create view v1 as select * from mysqltest.t1; create view v2 as select b from mysqltest.t2; # There are not rights on mysqltest.v1 ---error 1142 +-- error 1142 create view mysqltest.v1 as select * from mysqltest.t1; # There are not any rights on mysqltest.t2.a -- error 1143 @@ -888,10 +888,10 @@ create table t1 (col1 char(5),col2 char(5)); create view v1 as select * from t1; drop table t1; create table t1 (col1 char(5),newcol2 char(5)); --- error 1355 +-- error 1356 insert into v1 values('a','aa'); drop table t1; --- error 1355 +-- error 1356 select * from v1; drop view v1; @@ -907,7 +907,7 @@ create view v1 (a,a) as select 'a','a'; delimiter //; create procedure p1 () begin declare v int; create view v1 as select v; end;// delimiter ;// --- error 1350 +-- error 1351 call p1(); drop procedure p1; @@ -993,7 +993,7 @@ drop view v1; # create table t1 (col1 int,col2 char(22)); create view v1 as select * from t1; --- error 1346 +-- error 1347 create index i1 on v1 (col1); drop view v1; drop table t1; |