diff options
Diffstat (limited to 'mysql-test/r')
39 files changed, 1135 insertions, 23 deletions
diff --git a/mysql-test/r/analyze.result b/mysql-test/r/analyze.result index 7b476c3cca2..c3dbb846402 100644 --- a/mysql-test/r/analyze.result +++ b/mysql-test/r/analyze.result @@ -56,3 +56,11 @@ show index from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 1 a 1 a A 5 NULL NULL YES BTREE drop table t1; +End of 4.1 tests +create table t1(a int); +analyze table t1 extended; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'extended' at line 1 +optimize table t1 extended; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'extended' at line 1 +drop table t1; +End of 5.0 tests diff --git a/mysql-test/r/archive.result b/mysql-test/r/archive.result index a0b13b14b17..0fc43f58908 100644 --- a/mysql-test/r/archive.result +++ b/mysql-test/r/archive.result @@ -2618,7 +2618,7 @@ auto fld1 companynr fld3 fld4 fld5 fld6 INSERT INTO t2 VALUES (2,011401,37,'breaking','dreaded','Steinberg','W'); INSERT INTO t2 VALUES (3,011402,37,'Romans','scholastics','jarring',''); INSERT INTO t2 VALUES (4,011403,37,'intercepted','audiology','tinily',''); -OPTIMIZE TABLE t2 EXTENDED; +OPTIMIZE TABLE t2; Table Op Msg_type Msg_text test.t2 optimize status OK SELECT * FROM t2; diff --git a/mysql-test/r/bigint.result b/mysql-test/r/bigint.result index 064304b27aa..b0d114f1655 100644 --- a/mysql-test/r/bigint.result +++ b/mysql-test/r/bigint.result @@ -170,6 +170,12 @@ t2.value64=t1.value64; value64 value32 value64 value32 9223372036854775807 2 9223372036854775807 4 drop table t1, t2; +create table t1 (sint64 bigint not null); +insert into t1 values (-9223372036854775808); +select * from t1; +sint64 +-9223372036854775808 +drop table t1; create table t1 select 1 as 'a'; show create table t1; Table Create Table diff --git a/mysql-test/r/binlog_killed.result b/mysql-test/r/binlog_killed.result index ba4f38fb4c1..e04cc192876 100644 --- a/mysql-test/r/binlog_killed.result +++ b/mysql-test/r/binlog_killed.result @@ -9,4 +9,48 @@ insert into t2 values (null, null), (null, get_lock("a", 10)); select @result /* must be zero either way */; @result 0 +select RELEASE_LOCK("a"); +RELEASE_LOCK("a") +1 +delete from t1; +delete from t2; +insert into t1 values (1,1),(2,2); +begin; +update t1 set b=11 where a=2; +begin; +update t1 set b=b+10; +kill query ID; +rollback; +rollback; +select * from t1 order by a /* must be the same as before (1,1),(2,2) */; +a b +1 1 +2 2 +begin; +delete from t1 where a=2; +begin; +delete from t1 where a=2; +kill query ID; +rollback; +rollback; +select * from t1 order by a /* must be the same as before (1,1),(2,2) */; +a b +1 1 +2 2 +drop table if exists t4; +create table t4 (a int, b int) engine=innodb; +insert into t4 values (3, 3); +begin; +insert into t1 values (3, 3); +begin; +insert into t1 select * from t4 for update; +kill query ID; +rollback; +rollback; +select * from t1 /* must be the same as before (1,1),(2,2) */; +a b +1 1 +2 2 +drop table t4; drop table t1,t2,t3; +end of the tests diff --git a/mysql-test/r/binlog_killed_simulate.result b/mysql-test/r/binlog_killed_simulate.result new file mode 100644 index 00000000000..a21ac5b1e32 --- /dev/null +++ b/mysql-test/r/binlog_killed_simulate.result @@ -0,0 +1,33 @@ +drop table if exists t1,t2; +create table t1 (a int) engine=MyISAM; +insert into t1 set a=1; +reset master; +update t1 set a=2 /* will be "killed" after work has been done */; +select +(@a:=load_file("MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog")) +is not null; +(@a:=load_file("MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog")) +is not null +1 +select 1 /* must return 1 as query completed before got killed*/; +1 +1 +create table t2 (a int, b int) ENGINE=MyISAM; +reset master; +load data infile '../std_data_ln/rpl_loaddata.dat' into table t2 /* will be "killed" in the middle */; +ERROR 70100: Query execution was interrupted +show binlog events from 98; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 98 Begin_load_query 1 # ;file_id=1;block_len=12 +master-bin.000001 133 Execute_load_query 1 # use `test`; load data infile '../std_data_ln/rpl_loaddata.dat' into table t2 /* will be "killed" in the middle */ ;file_id=1 +select +(@a:=load_file("MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog")) +is not null; +(@a:=load_file("MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog")) +is not null +1 +select 0 /* must return 0 to mean the killed query is in */; +0 +0 +drop table t1,t2; +end of the tests diff --git a/mysql-test/r/case.result b/mysql-test/r/case.result index cf358e6a404..bc05edcfac1 100644 --- a/mysql-test/r/case.result +++ b/mysql-test/r/case.result @@ -1,4 +1,4 @@ -drop table if exists t1; +drop table if exists t1, t2; select CASE "b" when "a" then 1 when "b" then 2 END; CASE "b" when "a" then 1 when "b" then 2 END 2 @@ -200,3 +200,21 @@ CEMPNUM EMPMUM1 EMPNUM2 0.00 0 0.00 2.00 2 NULL DROP TABLE t1,t2; +End of 4.1 tests +create table t1 (a int, b bigint unsigned); +create table t2 (c int); +insert into t1 (a, b) values (1,4572794622775114594), (2,18196094287899841997), +(3,11120436154190595086); +insert into t2 (c) values (1), (2), (3); +select t1.a, (case t1.a when 0 then 0 else t1.b end) d from t1 +join t2 on t1.a=t2.c order by d; +a d +1 4572794622775114594 +3 11120436154190595086 +2 18196094287899841997 +select t1.a, (case t1.a when 0 then 0 else t1.b end) d from t1 +join t2 on t1.a=t2.c where b=11120436154190595086 order by d; +a d +3 11120436154190595086 +drop table t1, t2; +End of 5.0 tests diff --git a/mysql-test/r/ctype_big5.result b/mysql-test/r/ctype_big5.result index b190273cc64..8103e9b856f 100644 --- a/mysql-test/r/ctype_big5.result +++ b/mysql-test/r/ctype_big5.result @@ -64,6 +64,10 @@ select a sounds like a from t1; a sounds like a 1 1 +select 1 from t1 order by cast(a as char(1)); +1 +1 +1 drop table t1; DROP DATABASE d1; USE test; diff --git a/mysql-test/r/ctype_cp932.result b/mysql-test/r/ctype_cp932.result index e3598f00777..8974a6a8594 100755 --- a/mysql-test/r/ctype_cp932.result +++ b/mysql-test/r/ctype_cp932.result @@ -2,6 +2,80 @@ drop table if exists t1; drop table if exists t2; drop table if exists t3; drop table if exists t4; +SET @test_character_set= 'cp932'; +SET @test_collation= 'cp932_japanese_ci'; +SET @safe_character_set_server= @@character_set_server; +SET @safe_collation_server= @@collation_server; +SET character_set_server= @test_character_set; +SET collation_server= @test_collation; +CREATE DATABASE d1; +USE d1; +CREATE TABLE t1 (c CHAR(10), KEY(c)); +SHOW FULL COLUMNS FROM t1; +Field Type Collation Null Key Default Extra Privileges Comment +c char(10) cp932_japanese_ci YES MUL NULL +INSERT INTO t1 VALUES ('aaa'),('aaaa'),('aaaaa'); +SELECT c as want3results FROM t1 WHERE c LIKE 'aaa%'; +want3results +aaa +aaaa +aaaaa +DROP TABLE t1; +CREATE TABLE t1 (c1 varchar(15), KEY c1 (c1(2))); +SHOW FULL COLUMNS FROM t1; +Field Type Collation Null Key Default Extra Privileges Comment +c1 varchar(15) cp932_japanese_ci YES MUL NULL +INSERT INTO t1 VALUES ('location'),('loberge'),('lotre'),('boabab'); +SELECT c1 as want3results from t1 where c1 like 'l%'; +want3results +location +loberge +lotre +SELECT c1 as want3results from t1 where c1 like 'lo%'; +want3results +location +loberge +lotre +SELECT c1 as want1result from t1 where c1 like 'loc%'; +want1result +location +SELECT c1 as want1result from t1 where c1 like 'loca%'; +want1result +location +SELECT c1 as want1result from t1 where c1 like 'locat%'; +want1result +location +SELECT c1 as want1result from t1 where c1 like 'locati%'; +want1result +location +SELECT c1 as want1result from t1 where c1 like 'locatio%'; +want1result +location +SELECT c1 as want1result from t1 where c1 like 'location%'; +want1result +location +DROP TABLE t1; +create table t1 (a set('a') not null); +insert into t1 values (),(); +Warnings: +Warning 1364 Field 'a' doesn't have a default value +select cast(a as char(1)) from t1; +cast(a as char(1)) + + +select a sounds like a from t1; +a sounds like a +1 +1 +select 1 from t1 order by cast(a as char(1)); +1 +1 +1 +drop table t1; +DROP DATABASE d1; +USE test; +SET character_set_server= @safe_character_set_server; +SET collation_server= @safe_collation_server; set names cp932; set character_set_database = cp932; CREATE TABLE t1(c1 CHAR(1)) DEFAULT CHARACTER SET = cp932; diff --git a/mysql-test/r/ctype_euckr.result b/mysql-test/r/ctype_euckr.result index b9619370d4c..bb1b3f5995b 100644 --- a/mysql-test/r/ctype_euckr.result +++ b/mysql-test/r/ctype_euckr.result @@ -64,6 +64,10 @@ select a sounds like a from t1; a sounds like a 1 1 +select 1 from t1 order by cast(a as char(1)); +1 +1 +1 drop table t1; DROP DATABASE d1; USE test; @@ -178,3 +182,44 @@ hex(a) A2E6 FEF7 DROP TABLE t1; +create table t1 (s1 varchar(5) character set euckr); +insert into t1 values (0xA141); +insert into t1 values (0xA15A); +insert into t1 values (0xA161); +insert into t1 values (0xA17A); +insert into t1 values (0xA181); +insert into t1 values (0xA1FE); +insert into t1 values (0xA140); +Warnings: +Warning 1366 Incorrect string value: '\xA1@' for column 's1' at row 1 +insert into t1 values (0xA15B); +Warnings: +Warning 1366 Incorrect string value: '\xA1[' for column 's1' at row 1 +insert into t1 values (0xA160); +Warnings: +Warning 1366 Incorrect string value: '\xA1`' for column 's1' at row 1 +insert into t1 values (0xA17B); +Warnings: +Warning 1366 Incorrect string value: '\xA1{' for column 's1' at row 1 +insert into t1 values (0xA180); +Warnings: +Warning 1366 Incorrect string value: '\xA1\x80' for column 's1' at row 1 +insert into t1 values (0xA1FF); +Warnings: +Warning 1366 Incorrect string value: '\xA1\xFF' for column 's1' at row 1 +select hex(s1), hex(convert(s1 using utf8)) from t1 order by binary s1; +hex(s1) hex(convert(s1 using utf8)) + + + + + + +A141 ECA2A5 +A15A ECA381 +A161 ECA382 +A17A ECA3A5 +A181 ECA3A6 +A1FE EFBFA2 +drop table t1; +End of 5.0 tests diff --git a/mysql-test/r/ctype_gb2312.result b/mysql-test/r/ctype_gb2312.result index 90c94c3b299..95246525368 100644 --- a/mysql-test/r/ctype_gb2312.result +++ b/mysql-test/r/ctype_gb2312.result @@ -64,6 +64,10 @@ select a sounds like a from t1; a sounds like a 1 1 +select 1 from t1 order by cast(a as char(1)); +1 +1 +1 drop table t1; DROP DATABASE d1; USE test; diff --git a/mysql-test/r/ctype_gbk.result b/mysql-test/r/ctype_gbk.result index fe90c7bff29..8437e34be1e 100644 --- a/mysql-test/r/ctype_gbk.result +++ b/mysql-test/r/ctype_gbk.result @@ -64,6 +64,10 @@ select a sounds like a from t1; a sounds like a 1 1 +select 1 from t1 order by cast(a as char(1)); +1 +1 +1 drop table t1; DROP DATABASE d1; USE test; diff --git a/mysql-test/r/ctype_uca.result b/mysql-test/r/ctype_uca.result index e676d5a5ca0..50be0ca53b2 100644 --- a/mysql-test/r/ctype_uca.result +++ b/mysql-test/r/ctype_uca.result @@ -2599,6 +2599,10 @@ select a sounds like a from t1; a sounds like a 1 1 +select 1 from t1 order by cast(a as char(1)); +1 +1 +1 drop table t1; DROP DATABASE d1; USE test; @@ -2767,4 +2771,49 @@ a c ch drop table t1; +set collation_connection=ucs2_unicode_ci; +drop table if exists t1; +create table t1 as +select repeat(' ', 64) as s1, repeat(' ',64) as s2 +union +select null, null; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `s1` varchar(64) character set ucs2 collate ucs2_unicode_ci default NULL, + `s2` varchar(64) character set ucs2 collate ucs2_unicode_ci default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +delete from t1; +insert into t1 values('aaa','aaa'); +insert into t1 values('aaa|qqq','qqq'); +insert into t1 values('gheis','^[^a-dXYZ]+$'); +insert into t1 values('aab','^aa?b'); +insert into t1 values('Baaan','^Ba*n'); +insert into t1 values('aaa','qqq|aaa'); +insert into t1 values('qqq','qqq|aaa'); +insert into t1 values('bbb','qqq|aaa'); +insert into t1 values('bbb','qqq'); +insert into t1 values('aaa','aba'); +insert into t1 values(null,'abc'); +insert into t1 values('def',null); +insert into t1 values(null,null); +insert into t1 values('ghi','ghi['); +select HIGH_PRIORITY s1 regexp s2 from t1; +s1 regexp s2 +1 +1 +1 +1 +1 +1 +1 +0 +0 +0 +NULL +NULL +NULL +NULL +drop table t1; +set names utf8; End for 5.0 tests diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result index 82f21eb086d..a6d6c604b9b 100644 --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@ -811,6 +811,14 @@ quote(name) ???????? ???????????????? drop table bug20536; +set names ucs2; +ERROR 42000: Variable 'character_set_client' can't be set to the value of 'ucs2' +set names ucs2 collate ucs2_bin; +ERROR 42000: Variable 'character_set_client' can't be set to the value of 'ucs2' +set character_set_client= ucs2; +ERROR 42000: Variable 'character_set_client' can't be set to the value of 'ucs2' +set character_set_client= concat('ucs', substr('2', 1)); +ERROR 42000: Variable 'character_set_client' can't be set to the value of 'ucs2' CREATE TABLE t1(a TEXT CHARSET ucs2 COLLATE ucs2_unicode_ci); INSERT INTO t1 VALUES('abcd'); SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abcd' IN BOOLEAN MODE); @@ -928,6 +936,51 @@ ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (ucs2_gen select * from t1 where a=if(b<10,_ucs2 0x0062,_ucs2 0x00C0); ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (ucs2_general_ci,COERCIBLE) for operation '=' drop table t1; +set collation_connection=ucs2_general_ci; +drop table if exists t1; +create table t1 as +select repeat(' ', 64) as s1, repeat(' ',64) as s2 +union +select null, null; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `s1` varchar(64) character set ucs2 default NULL, + `s2` varchar(64) character set ucs2 default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +delete from t1; +insert into t1 values('aaa','aaa'); +insert into t1 values('aaa|qqq','qqq'); +insert into t1 values('gheis','^[^a-dXYZ]+$'); +insert into t1 values('aab','^aa?b'); +insert into t1 values('Baaan','^Ba*n'); +insert into t1 values('aaa','qqq|aaa'); +insert into t1 values('qqq','qqq|aaa'); +insert into t1 values('bbb','qqq|aaa'); +insert into t1 values('bbb','qqq'); +insert into t1 values('aaa','aba'); +insert into t1 values(null,'abc'); +insert into t1 values('def',null); +insert into t1 values(null,null); +insert into t1 values('ghi','ghi['); +select HIGH_PRIORITY s1 regexp s2 from t1; +s1 regexp s2 +1 +1 +1 +1 +1 +1 +1 +0 +0 +0 +NULL +NULL +NULL +NULL +drop table t1; +set names latin1; select hex(char(0x41 using ucs2)); hex(char(0x41 using ucs2)) 0041 diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index 4a15da71ee2..5ca1d578d2a 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -267,6 +267,51 @@ b select * from t1 where a = 'b' and a != 'b'; a drop table t1; +set collation_connection=utf8_general_ci; +drop table if exists t1; +create table t1 as +select repeat(' ', 64) as s1, repeat(' ',64) as s2 +union +select null, null; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `s1` varchar(64) character set utf8 default NULL, + `s2` varchar(64) character set utf8 default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +delete from t1; +insert into t1 values('aaa','aaa'); +insert into t1 values('aaa|qqq','qqq'); +insert into t1 values('gheis','^[^a-dXYZ]+$'); +insert into t1 values('aab','^aa?b'); +insert into t1 values('Baaan','^Ba*n'); +insert into t1 values('aaa','qqq|aaa'); +insert into t1 values('qqq','qqq|aaa'); +insert into t1 values('bbb','qqq|aaa'); +insert into t1 values('bbb','qqq'); +insert into t1 values('aaa','aba'); +insert into t1 values(null,'abc'); +insert into t1 values('def',null); +insert into t1 values(null,null); +insert into t1 values('ghi','ghi['); +select HIGH_PRIORITY s1 regexp s2 from t1; +s1 regexp s2 +1 +1 +1 +1 +1 +1 +1 +0 +0 +0 +NULL +NULL +NULL +NULL +drop table t1; +set names utf8; set names utf8; select 'вася' rlike '[[:<:]]вася[[:>:]]'; 'вася' rlike '[[:<:]]вася[[:>:]]' diff --git a/mysql-test/r/delayed.result b/mysql-test/r/delayed.result index 3b2a5027af5..e7ee23b3e2a 100644 --- a/mysql-test/r/delayed.result +++ b/mysql-test/r/delayed.result @@ -73,13 +73,13 @@ a 13 DROP TABLE t1; SET @bug20627_old_auto_increment_offset= -@@auto_increment_offset= 2; +@@auto_increment_offset; SET @bug20627_old_auto_increment_increment= -@@auto_increment_increment= 3; +@@auto_increment_increment; SET @bug20627_old_session_auto_increment_offset= -@@session.auto_increment_offset= 4; +@@session.auto_increment_offset; SET @bug20627_old_session_auto_increment_increment= -@@session.auto_increment_increment= 5; +@@session.auto_increment_increment; SET @@auto_increment_offset= 2; SET @@auto_increment_increment= 3; SET @@session.auto_increment_offset= 4; @@ -124,13 +124,13 @@ SET @@session.auto_increment_increment= Warnings: Warning 1292 Truncated incorrect auto-increment-increment value: '0' SET @bug20830_old_auto_increment_offset= -@@auto_increment_offset= 2; +@@auto_increment_offset; SET @bug20830_old_auto_increment_increment= -@@auto_increment_increment= 3; +@@auto_increment_increment; SET @bug20830_old_session_auto_increment_offset= -@@session.auto_increment_offset= 4; +@@session.auto_increment_offset; SET @bug20830_old_session_auto_increment_increment= -@@session.auto_increment_increment= 5; +@@session.auto_increment_increment; SET @@auto_increment_offset= 2; SET @@auto_increment_increment= 3; SET @@session.auto_increment_offset= 4; @@ -271,3 +271,8 @@ CREATE TABLE t2(c1 INT) ENGINE=MERGE UNION=(t1); INSERT DELAYED INTO t2 VALUES(1); ERROR HY000: Table storage engine for 't2' doesn't have this option DROP TABLE t1, t2; +CREATE TABLE t1 (a INT); +INSERT DELAYED INTO t1 SET b= b(); +ERROR 42S22: Unknown column 'b' in 'field list' +DROP TABLE t1; +End of 5.0 tests diff --git a/mysql-test/r/flush.result b/mysql-test/r/flush.result index 7eb7fd16edb..ce64e09c1d3 100644 --- a/mysql-test/r/flush.result +++ b/mysql-test/r/flush.result @@ -55,3 +55,20 @@ flush tables with read lock; insert into t2 values(1); unlock tables; drop table t1, t2; +drop table if exists t1, t2; +set session low_priority_updates=1; +create table t1 (a int); +create table t2 (b int); +lock tables t1 write; +flush tables with read lock; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +unlock tables; +lock tables t1 read, t2 write; +flush tables with read lock; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +unlock tables; +lock tables t1 read; +flush tables with read lock; +unlock tables; +drop table t1, t2; +set session low_priority_updates=default; diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result index d04c22c0c9d..4ba5ee674a9 100644 --- a/mysql-test/r/func_misc.result +++ b/mysql-test/r/func_misc.result @@ -212,4 +212,17 @@ INSERT INTO t1 VALUES (5), (2); SELECT NAME_CONST(x,2) FROM (SELECT a x FROM t1) t; ERROR HY000: The 'NAME_CONST' syntax is reserved for purposes internal to the MySQL server DROP TABLE t1; +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (), (), (); +SELECT NAME_CONST(a, '1') FROM t1; +ERROR HY000: Incorrect arguments to NAME_CONST +SET INSERT_ID= NAME_CONST(a, a); +ERROR HY000: Incorrect arguments to NAME_CONST +DROP TABLE t1; +create table t1 (a int not null); +insert into t1 values (-1), (-2); +select min(a) from t1 group by inet_ntoa(a); +min(a) +-2 +drop table t1; End of 5.0 tests diff --git a/mysql-test/r/func_regexp.result b/mysql-test/r/func_regexp.result index 2366947d2a7..c2dbfc3c331 100644 --- a/mysql-test/r/func_regexp.result +++ b/mysql-test/r/func_regexp.result @@ -1,5 +1,17 @@ drop table if exists t1; -create table t1 (s1 char(64),s2 char(64)); +set names latin1; +drop table if exists t1; +create table t1 as +select repeat(' ', 64) as s1, repeat(' ',64) as s2 +union +select null, null; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `s1` varchar(64) default NULL, + `s2` varchar(64) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +delete from t1; insert into t1 values('aaa','aaa'); insert into t1 values('aaa|qqq','qqq'); insert into t1 values('gheis','^[^a-dXYZ]+$'); diff --git a/mysql-test/r/func_set.result b/mysql-test/r/func_set.result index aa71cee0752..ef5526ca13e 100644 --- a/mysql-test/r/func_set.result +++ b/mysql-test/r/func_set.result @@ -73,3 +73,33 @@ find_in_set(binary 'a', 'A,B,C') select find_in_set('1','3,1,'); find_in_set('1','3,1,') 2 +End of 4.1 tests +SELECT INTERVAL(0.0, NULL); +INTERVAL(0.0, NULL) +1 +SELECT INTERVAL(0.0, CAST(NULL AS DECIMAL)); +INTERVAL(0.0, CAST(NULL AS DECIMAL)) +1 +SELECT INTERVAL(0.0, CAST(DATE(NULL) AS DECIMAL)); +INTERVAL(0.0, CAST(DATE(NULL) AS DECIMAL)) +1 +SELECT INTERVAL(0.0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL); +INTERVAL(0.0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL) +8 +SELECT INTERVAL(0.0, CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL), +CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL), +CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL)); +INTERVAL(0.0, CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL), +CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL), +CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL)) +8 +SELECT INTERVAL(0.0, CAST(DATE(NULL) AS DECIMAL), CAST(DATE(NULL) AS DECIMAL), +CAST(DATE(NULL) AS DECIMAL), CAST(DATE(NULL) AS DECIMAL), +CAST(DATE(NULL) AS DECIMAL), CAST(DATE(NULL) AS DECIMAL), +CAST(DATE(NULL) AS DECIMAL), CAST(DATE(NULL) AS DECIMAL)); +INTERVAL(0.0, CAST(DATE(NULL) AS DECIMAL), CAST(DATE(NULL) AS DECIMAL), +CAST(DATE(NULL) AS DECIMAL), CAST(DATE(NULL) AS DECIMAL), +CAST(DATE(NULL) AS DECIMAL), CAST(DATE(NULL) AS DECIMAL), +CAST(DATE(NULL) AS DECIMAL), CAST(DATE(NULL) AS DECIMAL)) +8 +End of 5.0 tests diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result index 855352a2195..e27ef64af43 100644 --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@ -1105,6 +1105,7 @@ ERROR 42000: SELECT command denied to user 'mysqltest_2'@'localhost' for table ' DROP DATABASE mysqltest1; DROP DATABASE mysqltest2; DROP USER mysqltest_1@localhost; +DROP USER mysqltest_2@localhost; use test; CREATE TABLE t1 (f1 int, f2 int); INSERT INTO t1 VALUES(1,1), (2,2); @@ -1124,6 +1125,7 @@ f1 f2 REVOKE UPDATE (f1) ON `test`.`t1` FROM 'mysqltest_1'@'localhost'; REVOKE SELECT ON `test`.* FROM 'mysqltest_1'@'localhost'; REVOKE ALL ON db27878.* FROM 'mysqltest_1'@'localhost'; +DROP USER mysqltest_1@localhost; DROP DATABASE db27878; use test; DROP TABLE t1; diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index d27bf8df2fa..854712fdb1d 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -1119,6 +1119,19 @@ show master status /* there must be no UPDATE query event */; File Position Binlog_Do_DB Binlog_Ignore_DB master-bin.000001 98 drop table t1, t2; +drop table if exists t1, t2; +CREATE TABLE t1 (a int, PRIMARY KEY (a)); +CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB; +create trigger trg_del_t2 after delete on t2 for each row +insert into t1 values (1); +insert into t1 values (1); +insert into t2 values (1),(2); +delete t2 from t2; +ERROR 23000: Duplicate entry '1' for key 1 +select count(*) from t2 /* must be 2 as restored after rollback caused by the error */; +count(*) +2 +drop table t1, t2; create table t1 (a int, b int) engine=innodb; insert into t1 values(20,null); select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on @@ -1792,10 +1805,10 @@ Variable_name Value Innodb_page_size 16384 show status like "Innodb_rows_deleted"; Variable_name Value -Innodb_rows_deleted 72 +Innodb_rows_deleted 73 show status like "Innodb_rows_inserted"; Variable_name Value -Innodb_rows_inserted 29732 +Innodb_rows_inserted 29734 show status like "Innodb_rows_updated"; Variable_name Value Innodb_rows_updated 29532 diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index 7450d8c349b..2ab463e7f85 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -1225,4 +1225,19 @@ id type d NULL member 3 NULL member 4 DROP TABLE t1; +create table t1(a char(10) not null, unique key aa(a(1)), +b char(4) not null, unique key bb(b(4))) engine=innodb; +desc t1; +Field Type Null Key Default Extra +a char(10) NO UNI NULL +b char(4) NO PRI NULL +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(10) NOT NULL, + `b` char(4) NOT NULL, + UNIQUE KEY `bb` (`b`), + UNIQUE KEY `aa` (`a`(1)) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +drop table t1; End of 5.0 tests diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result index 8a0a3ba848c..2e49bd373d6 100644 --- a/mysql-test/r/insert.result +++ b/mysql-test/r/insert.result @@ -461,4 +461,138 @@ i 2 2 DROP TABLE t1, t2; +CREATE TABLE t1 ( +a char(20) NOT NULL, +b char(7) DEFAULT NULL, +c char(4) DEFAULT NULL +); +INSERT INTO t1(a,b,c) VALUES (9.999999e+0, 9.999999e+0, 9.999e+0); +INSERT INTO t1(a,b,c) VALUES (1.225e-05, 1.225e-05, 1.225e-05); +Warnings: +Warning 1265 Data truncated for column 'c' at row 1 +INSERT INTO t1(a,b) VALUES (1.225e-04, 1.225e-04); +INSERT INTO t1(a,b) VALUES (1.225e-01, 1.225e-01); +INSERT INTO t1(a,b) VALUES (1.225877e-01, 1.225877e-01); +INSERT INTO t1(a,b) VALUES (1.225e+01, 1.225e+01); +INSERT INTO t1(a,b,c) VALUES (1.225e+01, 1.225e+01, 1.225e+01); +INSERT INTO t1(a,b) VALUES (1.225e+05, 1.225e+05); +INSERT INTO t1(a,b) VALUES (1.225e+10, 1.225e+10); +INSERT INTO t1(a,b) VALUES (1.225e+15, 1.225e+15); +INSERT INTO t1(a,b) VALUES (5000000e+0, 5000000e+0); +INSERT INTO t1(a,b) VALUES (1.25e+78, 1.25e+78); +INSERT INTO t1(a,b) VALUES (1.25e-94, 1.25e-94); +INSERT INTO t1(a,b) VALUES (1.25e+203, 1.25e+203); +INSERT INTO t1(a,b) VALUES (1.25e-175, 1.25e-175); +INSERT INTO t1(a,c) VALUES (1.225e+0, 1.225e+0); +INSERT INTO t1(a,c) VALUES (1.37e+0, 1.37e+0); +INSERT INTO t1(a,c) VALUES (-1.37e+0, -1.37e+0); +Warnings: +Warning 1265 Data truncated for column 'c' at row 1 +INSERT INTO t1(a,c) VALUES (1.87e-3, 1.87e-3); +Warnings: +Warning 1265 Data truncated for column 'c' at row 1 +INSERT INTO t1(a,c) VALUES (-1.87e-2, -1.87e-2); +Warnings: +Warning 1265 Data truncated for column 'c' at row 1 +INSERT INTO t1(a,c) VALUES (5000e+0, 5000e+0); +INSERT INTO t1(a,c) VALUES (-5000e+0, -5000e+0); +Warnings: +Warning 1265 Data truncated for column 'c' at row 1 +SELECT * FROM t1; +a b c +9.999999 10 10 +1.225e-05 1.2e-05 1e-0 +0.0001225 0.00012 NULL +0.1225 0.1225 NULL +0.1225877 0.12259 NULL +12.25 12.25 NULL +12.25 12.25 12.2 +122500 122500 NULL +12250000000 1.2e+10 NULL +1.225e+15 1.2e+15 NULL +5000000 5000000 NULL +1.25e+78 1.2e+78 NULL +1.25e-94 1.2e-94 NULL +1.25e+203 1e+203 NULL +1.25e-175 1e-175 NULL +1.225 NULL 1.23 +1.37 NULL 1.37 +-1.37 NULL -1.3 +0.00187 NULL 0.00 +-0.0187 NULL -0.0 +5000 NULL 5000 +-5000 NULL -500 +DROP TABLE t1; +CREATE TABLE t1 ( +a char(20) NOT NULL, +b char(7) DEFAULT NULL, +c char(5) +); +INSERT INTO t1(a,b,c) VALUES (9.999999e+0, 9.999999e+0, 9.999e+0); +INSERT INTO t1(a,b,c) VALUES (1.225e-05, 1.225e-05, 1.225e-05); +INSERT INTO t1(a,b) VALUES (1.225e-04, 1.225e-04); +INSERT INTO t1(a,b) VALUES (1.225e-01, 1.225e-01); +INSERT INTO t1(a,b) VALUES (1.225877e-01, 1.225877e-01); +INSERT INTO t1(a,b) VALUES (1.225e+01, 1.225e+01); +INSERT INTO t1(a,b,c) VALUES (1.225e+01, 1.225e+01, 1.225e+01); +INSERT INTO t1(a,b) VALUES (1.225e+05, 1.225e+05); +INSERT INTO t1(a,b) VALUES (1.225e+10, 1.225e+10); +INSERT INTO t1(a,b) VALUES (1.225e+15, 1.225e+15); +INSERT INTO t1(a,b) VALUES (5000000e+0, 5000000e+0); +INSERT INTO t1(a,b) VALUES (1.25e+78, 1.25e+78); +INSERT INTO t1(a,b) VALUES (1.25e-94, 1.25e-94); +INSERT INTO t1(a,b) VALUES (1.25e+203, 1.25e+203); +INSERT INTO t1(a,b) VALUES (1.25e-175, 1.25e-175); +INSERT INTO t1(a,c) VALUES (1.225e+0, 1.225e+0); +INSERT INTO t1(a,c) VALUES (1.37e+0, 1.37e+0); +INSERT INTO t1(a,c) VALUES (-1.37e+0, -1.37e+0); +INSERT INTO t1(a,c) VALUES (1.87e-3, 1.87e-3); +INSERT INTO t1(a,c) VALUES (-1.87e-2, -1.87e-2); +Warnings: +Warning 1265 Data truncated for column 'c' at row 1 +INSERT INTO t1(a,c) VALUES (5000e+0, 5000e+0); +INSERT INTO t1(a,c) VALUES (-5000e+0, -5000e+0); +SELECT * FROM t1; +a b c +9.999999 10 9.999 +1.225e-05 1.2e-05 1e-05 +0.0001225 0.00012 NULL +0.1225 0.1225 NULL +0.1225877 0.12259 NULL +12.25 12.25 NULL +12.25 12.25 12.25 +122500 122500 NULL +12250000000 1.2e+10 NULL +1.225e+15 1.2e+15 NULL +5000000 5000000 NULL +1.25e+78 1.2e+78 NULL +1.25e-94 1.2e-94 NULL +1.25e+203 1e+203 NULL +1.25e-175 1e-175 NULL +1.225 NULL 1.225 +1.37 NULL 1.37 +-1.37 NULL -1.37 +0.00187 NULL 0.002 +-0.0187 NULL -0.01 +5000 NULL 5000 +-5000 NULL -5000 +DROP TABLE t1; +CREATE TABLE t (a CHAR(10),b INT); +INSERT INTO t VALUES (),(),(); +INSERT INTO t(a) SELECT rand() FROM t; +DROP TABLE t; +CREATE TABLE t1 (c1 INT NOT NULL); +INSERT INTO t1 VALUES(4188.32999999999992724042385816574096679687500), +('4188.32999999999992724042385816574096679687500'), (4188); +SELECT * FROM t1; +c1 +4188 +4188 +4188 +CREATE TABLE t2 (c1 BIGINT); +INSERT INTO t2 VALUES('15449237462.0000000000'); +SELECT * FROM t2; +c1 +15449237462 +DROP TABLE t1, t2; End of 5.0 tests. diff --git a/mysql-test/r/key.result b/mysql-test/r/key.result index 2fc751c63bf..0f1ea46bd61 100644 --- a/mysql-test/r/key.result +++ b/mysql-test/r/key.result @@ -462,4 +462,48 @@ EXPLAIN SELECT MAX(a) FROM t1 FORCE INDEX(a); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 system NULL NULL NULL NULL 1 DROP TABLE t1; +create table t1(a int not null, key aa(a), +b char(10) not null, unique key bb(b(1)), +c char(4) not null, unique key cc(c)); +desc t1; +Field Type Null Key Default Extra +a int(11) NO MUL NULL +b char(10) NO UNI NULL +c char(4) NO PRI NULL +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + `b` char(10) NOT NULL, + `c` char(4) NOT NULL, + UNIQUE KEY `cc` (`c`), + UNIQUE KEY `bb` (`b`(1)), + KEY `aa` (`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1(a int not null, key aa(a), +b char(10) not null, unique key bb(b(1)), +c char(4) not null); +desc t1; +Field Type Null Key Default Extra +a int(11) NO MUL NULL +b char(10) NO UNI NULL +c char(4) NO NULL +alter table t1 add unique key cc(c); +desc t1; +Field Type Null Key Default Extra +a int(11) NO MUL NULL +b char(10) NO UNI NULL +c char(4) NO PRI NULL +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + `b` char(10) NOT NULL, + `c` char(4) NOT NULL, + UNIQUE KEY `cc` (`c`), + UNIQUE KEY `bb` (`b`(1)), + KEY `aa` (`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; End of 5.0 tests. diff --git a/mysql-test/r/lock_multi.result b/mysql-test/r/lock_multi.result index af49e1a27dc..079f92ca420 100644 --- a/mysql-test/r/lock_multi.result +++ b/mysql-test/r/lock_multi.result @@ -88,3 +88,14 @@ alter table t1 auto_increment=0; alter table t1 auto_increment=0; alter table t1 alter table t1 auto_increment=0; alter table t1 auto_increment=0; alter table t1 auto_increment=0; alter table t1 auto_increment=0; alter table t1 auto_increment=0; // unlock tables; drop table t1; +drop table if exists t1; +create table t1 (a int); +connection: locker +lock tables t1 read; +connection: writer +create table t2 like t1;; +connection: default +kill query +ERROR 70100: Query execution was interrupted +unlock tables; +drop table t1; diff --git a/mysql-test/r/mix_innodb_myisam_binlog.result b/mysql-test/r/mix_innodb_myisam_binlog.result index 5777bd890b2..89ee82e9655 100644 --- a/mysql-test/r/mix_innodb_myisam_binlog.result +++ b/mysql-test/r/mix_innodb_myisam_binlog.result @@ -351,7 +351,7 @@ drop function if exists bug27417; drop table if exists t1,t2; CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM; CREATE TABLE t2 (a int NOT NULL auto_increment, PRIMARY KEY (a)); -create function bug27417(n int) +create function bug27417(n int) RETURNS int(11) begin insert into t1 values (null); @@ -365,7 +365,7 @@ insert into t2 values (bug27417(2)); ERROR 23000: Duplicate entry '2' for key 1 show master status; File Position Binlog_Do_DB Binlog_Ignore_DB -master-bin.000001 98 +master-bin.000001 196 /* only (!) with fixes for #23333 will show there is the query */; select count(*) from t1 /* must be 3 */; count(*) @@ -390,6 +390,119 @@ affected rows: 0 select count(*) from t1 /* must be 7 */; count(*) 7 -drop function bug27417; drop table t1,t2; +CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM; +CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB; +CREATE TABLE t3 (a int, PRIMARY KEY (a), b int unique) ENGINE=MyISAM; +CREATE TABLE t4 (a int, PRIMARY KEY (a), b int unique) ENGINE=Innodb; +CREATE TABLE t5 (a int, PRIMARY KEY (a)) ENGINE=InnoDB; +insert into t2 values (1); +reset master; +insert into t2 values (bug27417(1)); +ERROR 23000: Duplicate entry '1' for key 1 +show master status /* the offset must denote there is the query */; +File Position Binlog_Do_DB Binlog_Ignore_DB +master-bin.000001 267 +select count(*) from t1 /* must be 1 */; +count(*) +1 +delete from t1; +delete from t2; +insert into t2 values (2); +reset master; +insert into t2 select bug27417(1) union select bug27417(2); +ERROR 23000: Duplicate entry '2' for key 1 +show master status /* the offset must denote there is the query */; +File Position Binlog_Do_DB Binlog_Ignore_DB +master-bin.000001 290 +select count(*) from t1 /* must be 2 */; +count(*) +2 +delete from t1; +insert into t3 values (1,1),(2,3),(3,4); +reset master; +update t3 set b=b+bug27417(1); +ERROR 23000: Duplicate entry '4' for key 2 +show master status /* the offset must denote there is the query */; +File Position Binlog_Do_DB Binlog_Ignore_DB +master-bin.000001 190 +select count(*) from t1 /* must be 2 */; +count(*) +2 +delete from t3; +delete from t4; +insert into t3 values (1,1); +insert into t4 values (1,1),(2,2); +reset master; +UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */; +ERROR 23000: Duplicate entry '2' for key 1 +show master status /* the offset must denote there is the query */; +File Position Binlog_Do_DB Binlog_Ignore_DB +master-bin.000001 301 +select count(*) from t1 /* must be 4 */; +count(*) +4 +delete from t1; +delete from t3; +delete from t4; +insert into t3 values (1,1),(2,2); +insert into t4 values (1,1),(2,2); +reset master; +UPDATE t3,t4 SET t3.a=t4.a + bug27417(1); +ERROR 23000: Duplicate entry '2' for key 1 +select count(*) from t1 /* must be 1 */; +count(*) +1 +drop table t4; +delete from t1; +delete from t2; +delete from t3; +insert into t2 values (1); +insert into t3 values (1,1); +create trigger trg_del before delete on t2 for each row +insert into t3 values (bug27417(1), 2); +reset master; +delete from t2; +ERROR 23000: Duplicate entry '1' for key 1 +show master status /* the offset must denote there is the query */; +File Position Binlog_Do_DB Binlog_Ignore_DB +master-bin.000001 246 +select count(*) from t1 /* must be 1 */; +count(*) +1 +drop trigger trg_del; +delete from t1; +delete from t2; +delete from t5; +create trigger trg_del_t2 after delete on t2 for each row +insert into t1 values (1); +insert into t2 values (2),(3); +insert into t5 values (1),(2); +reset master; +delete t2.* from t2,t5 where t2.a=t5.a + 1; +ERROR 23000: Duplicate entry '1' for key 1 +show master status /* the offset must denote there is the query */; +File Position Binlog_Do_DB Binlog_Ignore_DB +master-bin.000001 274 +select count(*) from t1 /* must be 1 */; +count(*) +1 +delete from t1; +create table t4 (a int default 0, b int primary key) engine=innodb; +insert into t4 values (0, 17); +reset master; +load data infile '../std_data_ln/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug27417(2); +ERROR 23000: Duplicate entry '17' for key 1 +select * from t4; +a b +0 17 +select count(*) from t1 /* must be 2 */; +count(*) +2 +show master status /* the offset must denote there is the query */; +File Position Binlog_Do_DB Binlog_Ignore_DB +master-bin.000001 376 +drop trigger trg_del_t2; +drop table t1,t2,t3,t4,t5; +drop function bug27417; end of tests diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result index 0f624e3ee8d..d95036090a5 100644 --- a/mysql-test/r/multi_update.result +++ b/mysql-test/r/multi_update.result @@ -545,7 +545,7 @@ a b 4 4 show master status /* there must be the UPDATE query event */; File Position Binlog_Do_DB Binlog_Ignore_DB -master-bin.000001 189 +master-bin.000001 260 delete from t1; delete from t2; insert into t1 values (1,2),(3,4),(4,4); @@ -555,6 +555,26 @@ UPDATE t2,t1 SET t2.a=t2.b where t2.a=t1.a; ERROR 23000: Duplicate entry '4' for key 1 show master status /* there must be the UPDATE query event */; File Position Binlog_Do_DB Binlog_Ignore_DB -master-bin.000001 204 +master-bin.000001 275 drop table t1, t2; +drop table if exists t1, t2, t3; +CREATE TABLE t1 (a int, PRIMARY KEY (a)); +CREATE TABLE t2 (a int, PRIMARY KEY (a)); +CREATE TABLE t3 (a int, PRIMARY KEY (a)) ENGINE=MyISAM; +create trigger trg_del_t3 before delete on t3 for each row insert into t1 values (1); +insert into t2 values (1),(2); +insert into t3 values (1),(2); +reset master; +delete t3.* from t2,t3 where t2.a=t3.a; +ERROR 23000: Duplicate entry '1' for key 1 +select count(*) from t1 /* must be 1 */; +count(*) +1 +select count(*) from t3 /* must be 1 */; +count(*) +1 +show binlog events from 98; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 98 Query 1 # use `test`; delete t3.* from t2,t3 where t2.a=t3.a +drop table t1, t2, t3; end of tests diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index c7d4effcc35..33f64d600bb 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -1830,6 +1830,14 @@ ALTER TABLE t1 ENABLE KEYS; SHOW TABLE STATUS LIKE 't1'; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment t1 MyISAM 10 Fixed 1 # # # 3072 # # # # # # # +# Enable keys with parallel repair +SET @@myisam_repair_threads=2; +ALTER TABLE t1 DISABLE KEYS; +ALTER TABLE t1 ENABLE KEYS; +SET @@myisam_repair_threads=1; +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check status OK DROP TABLE t1; CREATE TABLE t1 (id int NOT NULL, ref int NOT NULL, INDEX (id)) ENGINE=MyISAM; CREATE TABLE t2 LIKE t1; diff --git a/mysql-test/r/myisampack.result b/mysql-test/r/myisampack.result new file mode 100644 index 00000000000..5f39d318234 --- /dev/null +++ b/mysql-test/r/myisampack.result @@ -0,0 +1,29 @@ +CREATE TABLE t1(c1 DOUBLE, c2 DOUBLE, c3 DOUBLE, c4 DOUBLE, c5 DOUBLE, +c6 DOUBLE, c7 DOUBLE, c8 DOUBLE, c9 DOUBLE, a INT PRIMARY KEY); +INSERT INTO t1 VALUES +(-3.31168791059336e-06,-3.19054655887874e-06,-1.06528081684847e-05,-1.227278240089e-06,-1.66718069164799e-06,-2.59038972510885e-06,-2.83145227805303e-06,-4.09678491270648e-07,-2.22610091291797e-06,6), +(0.0030743000272545,2.53222044316438e-05,2.78674650061845e-05,1.95914465544536e-05,1.7347572525984e-05,1.87513810069614e-05,1.69882826885005e-05,2.44449336987598e-05,1.89914629921774e-05,9), +(2.85229319423495e-05,3.05970988282259e-05,3.77161100113133e-05,2.3055238978766e-05,2.08241267364615e-05,2.28009504270553e-05,2.12070165658947e-05,2.84350091565409e-05,2.3366822910704e-05,3), +(0,0,0,0,0,0,0,0,0,12), +(3.24544577570754e-05,3.44619021870993e-05,4.37561613201124e-05,2.57556808726748e-05,2.3195354640561e-05,2.58532400758869e-05,2.34934241667179e-05,3.1621640063232e-05,2.58229982746189e-05,19), +(2.53222044316438e-05,0.00445071933455582,2.97447268116016e-05,2.12379514059868e-05,1.86777776502663e-05,2.0170058676712e-05,1.8946030385445e-05,2.66040037173511e-05,2.09161899668946e-05,20), +(3.03462382611645e-05,3.26517930083994e-05,3.5242025468662e-05,2.53219745106391e-05,2.24384532945004e-05,2.4052346047657e-05,2.23865572957053e-05,3.1634313969082e-05,2.48285463481801e-05,21), +(1.95914465544536e-05,2.12379514059868e-05,2.27808649037128e-05,0.000341724375366877,1.4512761275113e-05,1.56475828693953e-05,1.44372366441415e-05,2.07952121981765e-05,1.61488256935919e-05,28), +(1.7347572525984e-05,1.86777776502663e-05,2.04116907052727e-05,1.4512761275113e-05,0.000432162526082388,1.38116514014465e-05,1.2712914948904e-05,1.82503165178506e-05,1.43043075345922e-05,30), +(1.68339762136661e-05,1.77836497166611e-05,2.36328309295222e-05,1.30183423732016e-05,1.18674654241553e-05,1.32467273128652e-05,1.24581739117775e-05,1.55624190959406e-05,1.33010638508213e-05,31), +(1.89643062824415e-05,2.06997140070717e-05,2.29045490159364e-05,1.57918175731019e-05,1.39864987449492e-05,1.50580274578455e-05,1.45908734129609e-05,1.95329296993327e-05,1.5814709481221e-05,32), +(1.69882826885005e-05,1.8946030385445e-05,2.00820439721439e-05,1.44372366441415e-05,1.2712914948904e-05,1.35209686474184e-05,0.00261563314789896,1.78285095864627e-05,1.46699314500019e-05,34), +(2.0278186540684e-05,2.18923409729654e-05,2.39981539939738e-05,1.71774589459438e-05,1.54654355357383e-05,1.62731485707636e-05,1.49253140625051e-05,2.18229800160297e-05,1.71923561673718e-05,35), +(2.44449336987598e-05,2.66040037173511e-05,2.84860148925308e-05,2.07952121981765e-05,1.82503165178506e-05,1.97667730441441e-05,1.78285095864627e-05,0.00166478601822712,2.0299952103232e-05,36), +(1.89914629921774e-05,2.09161899668946e-05,2.26026841007872e-05,1.61488256935919e-05,1.43043075345922e-05,1.52609063290127e-05,1.46699314500019e-05,2.0299952103232e-05,0.00306670170971682,39), +(0,0,0,0,0,0,0,0,0,41), +(0,0,0,0,0,0,0,0,0,17), +(0,0,0,0,0,0,0,0,0,18), +(2.51880677333017e-05,2.63051795435778e-05,2.79874748974906e-05,2.02888886670845e-05,1.8178636318197e-05,1.91308527003585e-05,1.83260023644133e-05,2.4422300558171e-05,1.96411467520551e-05,44), +(2.22402118719591e-05,2.37546284320705e-05,2.58463051055541e-05,1.83391609130854e-05,1.6300720519646e-05,1.74559091886791e-05,1.63733785575587e-05,2.26616253279828e-05,1.79541237435621e-05,45), +(3.01092775359837e-05,3.23865212934412e-05,4.09444584045994e-05,0,2.15470966302776e-05,2.39082636344032e-05,2.28296706429177e-05,2.9007671511595e-05,2.44201138973326e-05,46); +FLUSH TABLES; +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; diff --git a/mysql-test/r/mysql.result b/mysql-test/r/mysql.result index c6e589a5fb7..eded1a3fc3b 100644 --- a/mysql-test/r/mysql.result +++ b/mysql-test/r/mysql.result @@ -178,4 +178,6 @@ ERROR at line 1: DELIMITER cannot contain a backslash character 1 1 1 +This is a file starting with UTF8 BOM 0xEFBBBF +This is a file starting with UTF8 BOM 0xEFBBBF End of 5.0 tests diff --git a/mysql-test/r/mysqlbinlog.result b/mysql-test/r/mysqlbinlog.result index d16a4c39a11..9f001c293de 100644 --- a/mysql-test/r/mysqlbinlog.result +++ b/mysql-test/r/mysqlbinlog.result @@ -325,4 +325,5 @@ flush logs; drop table t1; 1 drop table t1; +shell> mysqlbinlog std_data/corrupt-relay-bin.000624 > var/tmp/bug31793.sql End of 5.0 tests diff --git a/mysql-test/r/mysqlcheck.result b/mysql-test/r/mysqlcheck.result index b2820df8f4c..f8a28009c42 100644 --- a/mysql-test/r/mysqlcheck.result +++ b/mysql-test/r/mysqlcheck.result @@ -1,4 +1,4 @@ -DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t1, `t``1`, `t 1`; drop view if exists v1; drop database if exists client_test_db; mysql.columns_priv OK @@ -41,4 +41,9 @@ test.t1 OK test.t1 OK drop view v1; drop table t1; +create table `t``1`(a int); +create table `t 1`(a int); +test.t 1 OK +test.t`1 OK +drop table `t``1`, `t 1`; End of 5.0 tests diff --git a/mysql-test/r/olap.result b/mysql-test/r/olap.result index a1d66b11f58..ad04e7304c9 100644 --- a/mysql-test/r/olap.result +++ b/mysql-test/r/olap.result @@ -726,3 +726,11 @@ count(a) 3 drop table t1; ############################################################## +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES(0); +SELECT 1 FROM t1 GROUP BY (DATE(NULL)) WITH ROLLUP; +1 +1 +1 +DROP TABLE t1; +End of 5.0 tests diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index 648d6468c0a..f547654bed1 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -1138,6 +1138,11 @@ a 13 DEALLOCATE PREPARE st1; DROP TABLE t1; +create table t1 (a int, b tinyint); +prepare st1 from 'update t1 set b= (str_to_date(a, a))'; +execute st1; +deallocate prepare st1; +drop table t1; End of 4.1 tests. create table t1 (a varchar(20)); insert into t1 values ('foo'); diff --git a/mysql-test/r/rpl_packet.result b/mysql-test/r/rpl_packet.result index 894bc81b08d..9425724b434 100644 --- a/mysql-test/r/rpl_packet.result +++ b/mysql-test/r/rpl_packet.result @@ -21,6 +21,37 @@ STOP SLAVE; START SLAVE; CREATE TABLe `t1` (`f1` LONGTEXT) ENGINE=MyISAM; INSERT INTO `t1`(`f1`) VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2048'); -SHOW STATUS LIKE 'Slave_running'; -Variable_name Value -Slave_running OFF +show slave status; +Slave_IO_State # +Master_Host 127.0.0.1 +Master_User root +Master_Port MASTER_MYPORT +Connect_Retry 1 +Master_Log_File master-bin.000001 +Read_Master_Log_Pos 2138 +Relay_Log_File # +Relay_Log_Pos # +Relay_Master_Log_File master-bin.000001 +Slave_IO_Running No +Slave_SQL_Running # +Replicate_Do_DB +Replicate_Ignore_DB +Replicate_Do_Table +Replicate_Ignore_Table +Replicate_Wild_Do_Table +Replicate_Wild_Ignore_Table +Last_Errno 0 +Last_Error +Skip_Counter 0 +Exec_Master_Log_Pos 2138 +Relay_Log_Space # +Until_Condition None +Until_Log_File +Until_Log_Pos 0 +Master_SSL_Allowed No +Master_SSL_CA_File +Master_SSL_CA_Path +Master_SSL_Cert +Master_SSL_Cipher +Master_SSL_Key +Seconds_Behind_Master # diff --git a/mysql-test/r/rpl_slave_skip.result b/mysql-test/r/rpl_slave_skip.result new file mode 100644 index 00000000000..a59ac3eb884 --- /dev/null +++ b/mysql-test/r/rpl_slave_skip.result @@ -0,0 +1,144 @@ +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; +**** On Master **** +CREATE TABLE t1 (a INT, b SET('master','slave')) ENGINE=INNODB; +CREATE TABLE t2 (a INT, b SET('master','slave')) ENGINE=MYISAM; +==== Skipping normal transactions ==== +**** On Slave **** +STOP SLAVE; +**** On Master **** +BEGIN; +INSERT INTO t1 VALUES (1, 'master'); +INSERT INTO t1 VALUES (2, 'master'); +INSERT INTO t1 VALUES (3, 'master'); +COMMIT; +BEGIN; +INSERT INTO t1 VALUES (4, 'master,slave'); +INSERT INTO t1 VALUES (5, 'master,slave'); +INSERT INTO t1 VALUES (6, 'master,slave'); +COMMIT; +SELECT * FROM t1 ORDER BY a; +a b +1 master +2 master +3 master +4 master,slave +5 master,slave +6 master,slave +**** On Slave **** +SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; +START SLAVE; +SELECT * FROM t1 ORDER BY a; +a b +4 master,slave +5 master,slave +6 master,slave +**** On Master **** +DELETE FROM t1; +==== Skipping two normal transactions ==== +**** On Slave **** +STOP SLAVE; +**** On Master **** +BEGIN; +INSERT INTO t1 VALUES (1, 'master'); +INSERT INTO t1 VALUES (2, 'master'); +INSERT INTO t1 VALUES (3, 'master'); +COMMIT; +BEGIN; +INSERT INTO t1 VALUES (4, 'master'); +INSERT INTO t1 VALUES (5, 'master'); +INSERT INTO t1 VALUES (6, 'master'); +COMMIT; +BEGIN; +INSERT INTO t1 VALUES (7, 'master,slave'); +INSERT INTO t1 VALUES (8, 'master,slave'); +INSERT INTO t1 VALUES (9, 'master,slave'); +COMMIT; +SELECT * FROM t1 ORDER BY a; +a b +1 master +2 master +3 master +4 master +5 master +6 master +7 master,slave +8 master,slave +9 master,slave +**** On Slave **** +SET GLOBAL SQL_SLAVE_SKIP_COUNTER=8; +START SLAVE; +SELECT * FROM t1 ORDER BY a; +a b +7 master,slave +8 master,slave +9 master,slave +**** On Master **** +DELETE FROM t1; +==== Skipping without autocommit ==== +**** On Slave **** +STOP SLAVE; +**** On Master **** +SET AUTOCOMMIT=0; +INSERT INTO t1 VALUES (1, 'master'); +INSERT INTO t1 VALUES (2, 'master'); +INSERT INTO t1 VALUES (3, 'master'); +COMMIT; +INSERT INTO t1 VALUES (4, 'master,slave'); +INSERT INTO t1 VALUES (5, 'master,slave'); +INSERT INTO t1 VALUES (6, 'master,slave'); +COMMIT; +SELECT * FROM t1 ORDER BY a; +a b +1 master +2 master +3 master +4 master,slave +5 master,slave +6 master,slave +**** On Slave **** +SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; +START SLAVE; +SELECT * FROM t1 ORDER BY a; +a b +4 master,slave +5 master,slave +6 master,slave +==== Rollback of transaction with non-transactional change ==== +**** On Master **** +DELETE FROM t1; +SET AUTOCOMMIT=1; +**** On Slave **** +STOP SLAVE; +**** On Master **** +BEGIN; +INSERT INTO t1 VALUES (1, ''); +INSERT INTO t2 VALUES (2, 'master'); +INSERT INTO t1 VALUES (3, ''); +ROLLBACK; +BEGIN; +INSERT INTO t1 VALUES (4, ''); +INSERT INTO t2 VALUES (5, 'master,slave'); +INSERT INTO t1 VALUES (6, ''); +ROLLBACK; +SELECT * FROM t1 ORDER BY a; +a b +SELECT * FROM t2 ORDER BY a; +a b +2 master +5 master,slave +**** On Slave **** +SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; +START SLAVE; +SELECT * FROM t1 ORDER BY a; +a b +SELECT * FROM t2 ORDER BY a; +a b +5 master,slave +==== Cleanup ==== +**** On Master **** +DROP TABLE t1, t2; diff --git a/mysql-test/r/rpl_sp_effects.result b/mysql-test/r/rpl_sp_effects.result index bf8128d9385..70b2338d187 100644 --- a/mysql-test/r/rpl_sp_effects.result +++ b/mysql-test/r/rpl_sp_effects.result @@ -213,3 +213,44 @@ drop table t1; drop function f1; drop function f2; drop procedure p1; +create table t2 (b BIT(7)); +create procedure sp_bug26199(bitvalue BIT(7)) +begin +insert into t2 set b = bitvalue; +end // +create function sf_bug26199(b BIT(7)) returns int +begin +insert into t2 values(b); +return 0; +end// +call sp_bug26199(b'1110'); +call sp_bug26199('\0'); +select sf_bug26199(b'1111111'); +sf_bug26199(b'1111111') +0 +select sf_bug26199(b'101111111'); +sf_bug26199(b'101111111') +0 +Warnings: +Warning 1264 Out of range value adjusted for column 'b' at row 1 +select sf_bug26199('\''); +sf_bug26199('\'') +0 +select hex(b) from t2; +hex(b) +E +0 +7F +7F +27 +select hex(b) from t2; +hex(b) +E +0 +7F +7F +27 +drop table t2; +drop procedure sp_bug26199; +drop function sf_bug26199; +end of the tests diff --git a/mysql-test/r/sp_trans_log.result b/mysql-test/r/sp_trans_log.result index 96e6f76b23c..9b644798079 100644 --- a/mysql-test/r/sp_trans_log.result +++ b/mysql-test/r/sp_trans_log.result @@ -12,8 +12,10 @@ end| reset master| insert into t2 values (bug23333(),1)| ERROR 23000: Duplicate entry '1' for key 1 -show binlog events from 98 /* with fixes for #23333 will show there is the query */| +show binlog events from 98 /* with fixes for #23333 will show there are 2 queries */| Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query 1 # # +master-bin.000001 # Query 1 # # select count(*),@a from t1 /* must be 1,1 */| count(*) @a 1 1 diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result index 75d9582a23c..b53b49bd0dd 100644 --- a/mysql-test/r/type_newdecimal.result +++ b/mysql-test/r/type_newdecimal.result @@ -1499,4 +1499,24 @@ SELECT 1 FROM t1 GROUP BY @b := @a, @b; 1 1 DROP TABLE t1; +CREATE TABLE t1 SELECT 0.123456789012345678901234567890123456 AS f1; +Warnings: +Note 1265 Data truncated for column 'f1' at row 1 +DESC t1; +Field Type Null Key Default Extra +f1 decimal(31,30) NO 0.000000000000000000000000000000 +SELECT f1 FROM t1; +f1 +0.123456789012345678901234567890 +DROP TABLE t1; +CREATE TABLE t1 SELECT 123451234512345123451234512345123451234512345.678906789067890678906789067890678906789067890 AS f1; +Warnings: +Warning 1264 Out of range value adjusted for column 'f1' at row 1 +DESC t1; +Field Type Null Key Default Extra +f1 decimal(59,30) NO 0.000000000000000000000000000000 +SELECT f1 FROM t1; +f1 +99999999999999999999999999999.999999999999999999999999999999 +DROP TABLE t1; End of 5.0 tests |