diff options
| author | unknown <monty@mysql.com> | 2004-09-09 06:59:26 +0300 |
|---|---|---|
| committer | unknown <monty@mysql.com> | 2004-09-09 06:59:26 +0300 |
| commit | 33efc9677d6f6a68d3dba69f4c036856ac4af5fe (patch) | |
| tree | 705389bcc2173e115a186211d2a5890721c9f5c7 /mysql-test | |
| parent | 4ad51359c1b7b5ed854f1af8df06fd6912068d28 (diff) | |
| download | mariadb-git-33efc9677d6f6a68d3dba69f4c036856ac4af5fe.tar.gz | |
After merge fixes of merge with 4.1 that included the new arena code.
Fixed (together with Guilhem) bugs in mysqlbinlog regarding --offset
Prefix addresses with 0x for easier comparisons of debug logs
Fixed problem where MySQL choosed index-read even if there would be a much better range on the same index
This fix changed some 'index' queries to 'range' queries in the test suite
Don't create 'dummy' WHERE clause for trivial WHERE clauses where we can remove the WHERE clause.
This fix removed of a lot of 'Using where' notes in the test suite.
Give NOTE instead of WARNING if table/function doesn't exists when using DROP IF EXISTS
Give NOTE instead of WARNING for safe field-type conversions
Makefile.am:
Don't automaticly update files from bk
client/mysqlbinlog.cc:
Merge with 4.1 (+ apply bug fixes for --offset and --start-position)
include/my_sys.h:
Faster clear_alloc_root()
mysql-test/r/bdb.result:
Updated results after merge
mysql-test/r/create.result:
Updated results after merge
mysql-test/r/func_group.result:
Updated results after merge
mysql-test/r/func_if.result:
Updated results after merge
mysql-test/r/heap_btree.result:
Updated results after merge
mysql-test/r/index_merge.result:
Updated results after merge
mysql-test/r/index_merge_ror.result:
Updated results after merge
mysql-test/r/innodb.result:
Updated results after merge
mysql-test/r/join_outer.result:
Updated results after merge
mysql-test/r/mysqlbinlog2.result:
Updated results after merge
mysql-test/r/negation_elimination.result:
Updated results after merge
mysql-test/r/null.result:
Updated results after merge
Added more tests
mysql-test/r/null_key.result:
Updated results after merge
Added more tests
mysql-test/r/order_by.result:
Updated results after merge
mysql-test/r/range.result:
Updated results after merge
Added more tests
mysql-test/r/rpl_charset.result:
Updated results after merge
mysql-test/r/sp-error.result:
Updated results after merge
mysql-test/r/sp.result:
Updated results after merge
Added delete of some stored procedures in an attempt to be able to re-run test even if it aborts in the middle
mysql-test/r/type_blob.result:
Updated results after merge
(Some warnings are now notes)
mysql-test/r/user_var.result:
Updated results after merge
Added more tests
mysql-test/r/variables.result:
Updated results after merge
mysql-test/r/view.result:
Updated results after merge
mysql-test/t/mysqlbinlog2.test:
Updated tests to use new positions
mysql-test/t/null.test:
More tests
mysql-test/t/null_key.test:
More tests
mysql-test/t/range.test:
More tests
mysql-test/t/rpl_charset.test:
Avoid big diffs in the future if tests changes
mysql-test/t/sp-error.test:
Updated error numbers
mysql-test/t/sp-security.test:
Updated error numbers
mysql-test/t/sp.test:
Updated results after merge
Added delete of some stored procedures in an attempt to be able to re-run test even if it aborts in the middle
mysql-test/t/user_var.test:
More tests
mysql-test/t/view.test:
Updated error numbers
mysys/my_alloc.c:
Write into debug log the address of the allocated area
sql/ha_isam.cc:
Prefix addresses with 0x for easier comparisons of debug logs
sql/ha_myisam.cc:
Prefix addresses with 0x for easier comparisons of debug logs
sql/ha_ndbcluster.cc:
Add missing enum to switch
sql/handler.cc:
remove compiler warning
sql/item.cc:
More debugging
Simple cleanup
sql/item.h:
Move Item::cleanup() to item.cc
sql/item_cmpfunc.cc:
Fix arena code
sql/item_subselect.cc:
After merge fixes
sql/item_subselect.h:
After merge fixes
sql/item_sum.cc:
Updated comment
sql/log_event.cc:
Remove wrong test
sql/mysql_priv.h:
Indentation fixes
sql/mysqld.cc:
After merge fixes
Added 0x to pointers in debug log
sql/opt_range.cc:
Fixed problem where MySQL choosed index-read even if there would be a much better range on the same index
This fix changed some 'index' queries to 'range' queries in the test suite
sql/set_var.cc:
Indentation fixes
sql/sp_head.cc:
Set state to INITIALIZED to make SP work with new arena code
sql/sql_base.cc:
After merge fixes
sql/sql_class.cc:
More debugging
Use clear_alloc_root() instead of init_alloc_root() as the former is faster
sql/sql_class.h:
New method 'only_prepare()'
sql/sql_lex.cc:
After merge fixes
sql/sql_lex.h:
After merge fixes
sql/sql_parse.cc:
Fix for timezone tables. (The old way to add timezone tables to global list in 'create_total_list' doesn't work anymore)
Give NOTE instead of WARNING if table/function doesn't exists when using DROP IF EXISTS
sql/sql_prepare.cc:
After merge fixes
sql/sql_select.cc:
Don't create 'dummy' WHERE clause for trivial WHERE clauses where we can remove the WHERE clause.
This fix removed of a lot of 'Using where' notes in the test suite
sql/sql_table.cc:
Give NOTE instead of WARNING if table/function doesn't exists when using DROP IF EXISTS
sql/sql_union.cc:
After merge fix
sql/sql_view.cc:
After merge fix
sql/table.cc:
After merge fix
sql/tztime.cc:
Update timezone table handling to use new table lists structure
sql/tztime.h:
Update timezone table handling to use new table lists structure
sql/unireg.cc:
Use 0x before pointers
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; |