diff options
Diffstat (limited to 'mysql-test')
52 files changed, 2490 insertions, 119 deletions
diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index 41d15551eea..75c35daf2bf 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -3012,6 +3012,7 @@ language = $path_language character-sets-dir = $path_charsetsdir basedir = $path_my_basedir server_id = $server_id +shutdown-delay = 10 skip-stack-trace skip-innodb skip-ndbcluster diff --git a/mysql-test/r/ctype_cp932.result b/mysql-test/r/ctype_cp932.result index ed57b87c1ba..10451686e2c 100755 --- a/mysql-test/r/ctype_cp932.result +++ b/mysql-test/r/ctype_cp932.result @@ -11353,3 +11353,15 @@ a a a drop table t1; +set names utf8; +create table t1 (a text) default character set cp932; +insert into t1 values (_utf8 0xE38182); +show warnings; +Level Code Message +select * from t1; +a +ã‚ +select hex(a) from t1; +hex(a) +82A0 +drop table t1; diff --git a/mysql-test/r/ctype_cp932_binlog.result b/mysql-test/r/ctype_cp932_binlog.result index d3d800b7bf0..ff295961008 100644 --- a/mysql-test/r/ctype_cp932_binlog.result +++ b/mysql-test/r/ctype_cp932_binlog.result @@ -8,9 +8,9 @@ SET @var1= x'8300'; EXECUTE stmt1 USING @var1; SHOW BINLOG EVENTS FROM 98; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 98 Query 1 185 use `test`; CREATE TABLE t1(f1 blob) -master-bin.000001 185 User var 1 224 @`var1`=_binary 0x8300 COLLATE binary -master-bin.000001 224 Query 1 317 use `test`; INSERT INTO t1 VALUES(@'var1') +master-bin.000001 98 Query 1 188 use `test`; CREATE TABLE t1(f1 blob) +master-bin.000001 188 User var 1 227 @`var1`=_binary 0x8300 COLLATE binary +master-bin.000001 227 Query 1 323 use `test`; INSERT INTO t1 VALUES(@'var1') SELECT HEX(f1) FROM t1; HEX(f1) 8300 @@ -30,17 +30,17 @@ HEX(s1) HEX(s2) d 466F6F2773206120426172 ED40ED41ED42 47.93 DROP PROCEDURE bug18293| DROP TABLE t4| -SHOW BINLOG EVENTS FROM 393| +SHOW BINLOG EVENTS FROM 402| Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 393 Query 1 556 use `test`; CREATE TABLE t4 (s1 CHAR(50) CHARACTER SET latin1, +master-bin.000001 402 Query 1 568 use `test`; CREATE TABLE t4 (s1 CHAR(50) CHARACTER SET latin1, s2 CHAR(50) CHARACTER SET cp932, d DECIMAL(10,2)) -master-bin.000001 556 Query 1 801 use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE bug18293 (IN ins1 CHAR(50), +master-bin.000001 568 Query 1 816 use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE bug18293 (IN ins1 CHAR(50), IN ins2 CHAR(50) CHARACTER SET cp932, IN ind DECIMAL(10,2)) BEGIN INSERT INTO t4 VALUES (ins1, ins2, ind); END -master-bin.000001 801 Query 1 1017 use `test`; INSERT INTO t4 VALUES ( NAME_CONST('ins1',_latin1 0x466F6F2773206120426172), NAME_CONST('ins2',_cp932 0xED40ED41ED42), NAME_CONST('ind',47.93)) -master-bin.000001 1017 Query 1 1103 use `test`; DROP PROCEDURE bug18293 -master-bin.000001 1103 Query 1 1179 use `test`; DROP TABLE t4 +master-bin.000001 816 Query 1 1035 use `test`; INSERT INTO t4 VALUES ( NAME_CONST('ins1',_latin1 0x466F6F2773206120426172), NAME_CONST('ins2',_cp932 0xED40ED41ED42), NAME_CONST('ind',47.93)) +master-bin.000001 1035 Query 1 1124 use `test`; DROP PROCEDURE bug18293 +master-bin.000001 1124 Query 1 1203 use `test`; DROP TABLE t4 diff --git a/mysql-test/r/ctype_cp932_notembedded.result b/mysql-test/r/ctype_cp932_notembedded.result index d04fce7738c..c58bfc65454 100644 --- a/mysql-test/r/ctype_cp932_notembedded.result +++ b/mysql-test/r/ctype_cp932_notembedded.result @@ -8,9 +8,9 @@ SET @var1= x'8300'; EXECUTE stmt1 USING @var1; SHOW BINLOG EVENTS FROM 98; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 98 Query 1 185 use `test`; CREATE TABLE t1(f1 blob) -master-bin.000001 185 User var 1 224 @`var1`=_binary 0x8300 COLLATE binary -master-bin.000001 224 Query 1 317 use `test`; INSERT INTO t1 VALUES(@'var1') +master-bin.000001 98 Query 1 188 use `test`; CREATE TABLE t1(f1 blob) +master-bin.000001 188 User var 1 227 @`var1`=_binary 0x8300 COLLATE binary +master-bin.000001 227 Query 1 323 use `test`; INSERT INTO t1 VALUES(@'var1') SELECT HEX(f1) FROM t1; HEX(f1) 8300 diff --git a/mysql-test/r/func_if.result b/mysql-test/r/func_if.result index eef380c8f52..c75e37fa0a4 100644 --- a/mysql-test/r/func_if.result +++ b/mysql-test/r/func_if.result @@ -128,3 +128,6 @@ f1 f2 if(f1, 40.0, 5.00) 0 0 5.00 1 1 40.00 drop table t1; +select if(0, 18446744073709551610, 18446744073709551610); +if(0, 18446744073709551610, 18446744073709551610) +18446744073709551610 diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result index 5c6162b7c28..35101e26ff6 100644 --- a/mysql-test/r/func_misc.result +++ b/mysql-test/r/func_misc.result @@ -141,4 +141,48 @@ t1 CREATE TABLE `t1` ( `a` bigint(21) unsigned default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; +drop table if exists table_26093; +drop function if exists func_26093_a; +drop function if exists func_26093_b; +create table table_26093(a int); +insert into table_26093 values +(1), (2), (3), (4), (5), +(6), (7), (8), (9), (10); +create function func_26093_a(x int) returns int +begin +set @invoked := @invoked + 1; +return x; +end// +create function func_26093_b(x int, y int) returns int +begin +set @invoked := @invoked + 1; +return x; +end// +select avg(a) from table_26093; +avg(a) +5.5000 +select benchmark(100, (select avg(a) from table_26093)); +benchmark(100, (select avg(a) from table_26093)) +0 +set @invoked := 0; +select benchmark(100, (select avg(func_26093_a(a)) from table_26093)); +benchmark(100, (select avg(func_26093_a(a)) from table_26093)) +0 +select @invoked; +@invoked +10 +set @invoked := 0; +select benchmark(100, (select avg(func_26093_b(a, rand())) from table_26093)); +benchmark(100, (select avg(func_26093_b(a, rand())) from table_26093)) +0 +select @invoked; +@invoked +1000 +select benchmark(100, (select (a) from table_26093)); +ERROR 21000: Subquery returns more than 1 row +select benchmark(100, (select 1, 1)); +ERROR 21000: Operand should contain 1 column(s) +drop table table_26093; +drop function func_26093_a; +drop function func_26093_b; End of 5.0 tests diff --git a/mysql-test/r/im_daemon_life_cycle.result b/mysql-test/r/im_daemon_life_cycle.result index d80a34f8427..1a0741e7caf 100644 --- a/mysql-test/r/im_daemon_life_cycle.result +++ b/mysql-test/r/im_daemon_life_cycle.result @@ -21,6 +21,6 @@ Success: the process was restarted. Success: server is ready to accept connection on socket. SHOW INSTANCE STATUS mysqld1; instance_name status version -mysqld1 online VERSION +mysqld1 STATE VERSION STOP INSTANCE mysqld2; Success: the process has been stopped. diff --git a/mysql-test/r/information_schema_db.result b/mysql-test/r/information_schema_db.result index 40773a7afc1..47efe1d17ad 100644 --- a/mysql-test/r/information_schema_db.result +++ b/mysql-test/r/information_schema_db.result @@ -87,13 +87,13 @@ where table_schema='test'; table_name table_type table_comment t1 BASE TABLE v1 VIEW VIEW -v2 VIEW View 'test.v2' references invalid table(s) or column(s) or function(s) or define +v2 VIEW VIEW drop table t1; select table_name, table_type, table_comment from information_schema.tables where table_schema='test'; table_name table_type table_comment -v1 VIEW View 'test.v1' references invalid table(s) or column(s) or function(s) or define -v2 VIEW View 'test.v2' references invalid table(s) or column(s) or function(s) or define +v1 VIEW VIEW +v2 VIEW VIEW drop function f1; drop function f2; drop view v1, v2; diff --git a/mysql-test/r/init_file.result b/mysql-test/r/init_file.result index 1569f2c3d68..6394014f3e5 100644 --- a/mysql-test/r/init_file.result +++ b/mysql-test/r/init_file.result @@ -1,3 +1,10 @@ +INSERT INTO init_file.startup VALUES ( NOW() ); +SELECT * INTO @X FROM init_file.startup limit 0,1; +SELECT * INTO @Y FROM init_file.startup limit 1,1; +SELECT YEAR(@X)-YEAR(@Y); +YEAR(@X)-YEAR(@Y) +0 +DROP DATABASE init_file; ok end of 4.1 tests select * from t1; diff --git a/mysql-test/r/innodb-replace.result b/mysql-test/r/innodb-replace.result index b7edcc49e56..77e0aeb38fd 100644 --- a/mysql-test/r/innodb-replace.result +++ b/mysql-test/r/innodb-replace.result @@ -2,11 +2,11 @@ drop table if exists t1; create table t1 (c1 char(5) unique not null, c2 int, stamp timestamp) engine=innodb; select * from t1; c1 c2 stamp -replace delayed into t1 (c1, c2) values ( "text1","11"),( "text2","12"); +replace delayed into t1 (c1, c2) values ( "text1","11"); ERROR HY000: Table storage engine for 't1' doesn't have this option select * from t1; c1 c2 stamp -replace delayed into t1 (c1, c2) values ( "text1","12"),( "text2","13"),( "text3","14", "a" ),( "text4","15", "b" ); +replace delayed into t1 (c1, c2) values ( "text1","12"); ERROR HY000: Table storage engine for 't1' doesn't have this option select * from t1; c1 c2 stamp diff --git a/mysql-test/r/mysqlbinlog.result b/mysql-test/r/mysqlbinlog.result index 18d49dca813..9ee2a1e769a 100644 --- a/mysql-test/r/mysqlbinlog.result +++ b/mysql-test/r/mysqlbinlog.result @@ -272,3 +272,61 @@ call p1(); 1 1 drop procedure p1; +flush logs; +create table t1 (a varchar(64) character set utf8); +load data infile '../std_data_ln/loaddata6.dat' into table t1; +set character_set_database=koi8r; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +set character_set_database=latin1; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +set character_set_database=koi8r; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +set character_set_database=latin1; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +load data infile '../std_data_ln/loaddata6.dat' into table t1 character set koi8r; +select hex(a) from t1; +hex(a) +C3BF +D0AA +C3BF +C3BF +D0AA +C3BF +D0AA +drop table t1; +flush logs; +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; +DELIMITER /*!*/; +use test/*!*/; +SET TIMESTAMP=1000000000/*!*/; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; +SET @@session.sql_mode=0/*!*/; +/*!\C latin1 *//*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +create table t1 (a varchar(64) character set utf8)/*!*/; +SET TIMESTAMP=1000000000/*!*/; +load data LOCAL INFILE 'MYSQL_TMP_DIR/SQL_LOAD_MB-6-0' INTO table t1/*!*/; +SET TIMESTAMP=1000000000/*!*/; +SET @@session.collation_database=7/*!*/; +load data LOCAL INFILE 'MYSQL_TMP_DIR/SQL_LOAD_MB-7-0' INTO table t1/*!*/; +SET TIMESTAMP=1000000000/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; +load data LOCAL INFILE 'MYSQL_TMP_DIR/SQL_LOAD_MB-8-0' INTO table t1/*!*/; +SET TIMESTAMP=1000000000/*!*/; +load data LOCAL INFILE 'MYSQL_TMP_DIR/SQL_LOAD_MB-9-0' INTO table t1/*!*/; +SET TIMESTAMP=1000000000/*!*/; +SET @@session.collation_database=7/*!*/; +load data LOCAL INFILE 'MYSQL_TMP_DIR/SQL_LOAD_MB-a-0' INTO table t1/*!*/; +SET TIMESTAMP=1000000000/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; +load data LOCAL INFILE 'MYSQL_TMP_DIR/SQL_LOAD_MB-b-0' INTO table t1/*!*/; +SET TIMESTAMP=1000000000/*!*/; +load data LOCAL INFILE 'MYSQL_TMP_DIR/SQL_LOAD_MB-c-0' INTO table t1 character set koi8r/*!*/; +SET TIMESTAMP=1000000000/*!*/; +drop table t1/*!*/; +DELIMITER ; +# End of log file +ROLLBACK /* added by mysqlbinlog */; +/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; diff --git a/mysql-test/r/rpl_insert_delayed.result b/mysql-test/r/rpl_insert_delayed.result new file mode 100644 index 00000000000..38e2cddd650 --- /dev/null +++ b/mysql-test/r/rpl_insert_delayed.result @@ -0,0 +1,31 @@ +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; +CREATE TABLE t1 (id INT primary key auto_increment, name VARCHAR(64)); +truncate table t1; +insert delayed into t1 values(10, "my name"); +insert delayed into t1 values(10, "is Bond"), (20, "James Bond"); +ERROR 23000: Duplicate entry '10' for key 1 +flush table t1; +select * from t1; +id name +10 my name +select * from t1; +id name +10 my name +delete from t1 where id!=10; +insert delayed into t1 values(20, "is Bond"), (10, "James Bond"); +ERROR 23000: Duplicate entry '10' for key 1 +flush table t1; +select * from t1; +id name +10 my name +20 is Bond +select * from t1; +id name +10 my name +20 is Bond +drop table t1; diff --git a/mysql-test/r/rpl_insert_id.result b/mysql-test/r/rpl_insert_id.result index d133a2ae8ed..a5c8e17f67e 100644 --- a/mysql-test/r/rpl_insert_id.result +++ b/mysql-test/r/rpl_insert_id.result @@ -234,6 +234,64 @@ n b 2 100 3 350 drop table t1; +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT, +UNIQUE(b)); +INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10; +SELECT * FROM t1; +a b +1 10 +2 2 +SELECT * FROM t1; +a b +1 10 +2 2 +drop table t1; +CREATE TABLE t1 ( +id bigint(20) unsigned NOT NULL auto_increment, +field_1 int(10) unsigned NOT NULL, +field_2 varchar(255) NOT NULL, +field_3 varchar(255) NOT NULL, +PRIMARY KEY (id), +UNIQUE KEY field_1 (field_1, field_2) +); +CREATE TABLE t2 ( +field_a int(10) unsigned NOT NULL, +field_b varchar(255) NOT NULL, +field_c varchar(255) NOT NULL +); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (1, 'a', '1a'); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (2, 'b', '2b'); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (3, 'c', '3c'); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (4, 'd', '4d'); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (5, 'e', '5e'); +INSERT INTO t1 (field_1, field_2, field_3) +SELECT t2.field_a, t2.field_b, t2.field_c +FROM t2 +ON DUPLICATE KEY UPDATE +t1.field_3 = t2.field_c; +INSERT INTO t2 (field_a, field_b, field_c) VALUES (6, 'f', '6f'); +INSERT INTO t1 (field_1, field_2, field_3) +SELECT t2.field_a, t2.field_b, t2.field_c +FROM t2 +ON DUPLICATE KEY UPDATE +t1.field_3 = t2.field_c; +SELECT * FROM t1; +id field_1 field_2 field_3 +1 1 a 1a +2 2 b 2b +3 3 c 3c +4 4 d 4d +5 5 e 5e +6 6 f 6f +SELECT * FROM t1; +id field_1 field_2 field_3 +1 1 a 1a +2 2 b 2b +3 3 c 3c +4 4 d 4d +5 5 e 5e +6 6 f 6f +drop table t1, t2; DROP PROCEDURE IF EXISTS p1; DROP TABLE IF EXISTS t1, t2; SELECT LAST_INSERT_ID(0); diff --git a/mysql-test/r/rpl_known_bugs_detection.result b/mysql-test/r/rpl_known_bugs_detection.result new file mode 100644 index 00000000000..c23586c6f61 --- /dev/null +++ b/mysql-test/r/rpl_known_bugs_detection.result @@ -0,0 +1,133 @@ +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; +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT, +UNIQUE(b)); +INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10; +SELECT * FROM t1; +a b +1 10 +2 2 +show slave status;; +Slave_IO_State # +Master_Host 127.0.0.1 +Master_User root +Master_Port # +Connect_Retry 1 +Master_Log_File master-bin.000001 +Read_Master_Log_Pos # +Relay_Log_File # +Relay_Log_Pos # +Relay_Master_Log_File master-bin.000001 +Slave_IO_Running Yes +Slave_SQL_Running No +Replicate_Do_DB +Replicate_Ignore_DB +Replicate_Do_Table +Replicate_Ignore_Table +Replicate_Wild_Do_Table +Replicate_Wild_Ignore_Table +Last_Errno 1105 +Last_Error Error 'master may suffer from http://bugs.mysql.com/bug.php?id=24432 so slave stops; check error log on slave for more info' on query. Default database: 'test'. Query: 'INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10' +Skip_Counter 0 +Exec_Master_Log_Pos 238 +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 # +SELECT * FROM t1; +a b +stop slave; +reset slave; +reset master; +drop table t1; +start slave; +CREATE TABLE t1 ( +id bigint(20) unsigned NOT NULL auto_increment, +field_1 int(10) unsigned NOT NULL, +field_2 varchar(255) NOT NULL, +field_3 varchar(255) NOT NULL, +PRIMARY KEY (id), +UNIQUE KEY field_1 (field_1, field_2) +); +CREATE TABLE t2 ( +field_a int(10) unsigned NOT NULL, +field_b varchar(255) NOT NULL, +field_c varchar(255) NOT NULL +); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (1, 'a', '1a'); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (2, 'b', '2b'); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (3, 'c', '3c'); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (4, 'd', '4d'); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (5, 'e', '5e'); +INSERT INTO t1 (field_1, field_2, field_3) +SELECT t2.field_a, t2.field_b, t2.field_c +FROM t2 +ON DUPLICATE KEY UPDATE +t1.field_3 = t2.field_c; +INSERT INTO t2 (field_a, field_b, field_c) VALUES (6, 'f', '6f'); +INSERT INTO t1 (field_1, field_2, field_3) +SELECT t2.field_a, t2.field_b, t2.field_c +FROM t2 +ON DUPLICATE KEY UPDATE +t1.field_3 = t2.field_c; +SELECT * FROM t1; +id field_1 field_2 field_3 +1 1 a 1a +2 2 b 2b +3 3 c 3c +4 4 d 4d +5 5 e 5e +6 6 f 6f +show slave status;; +Slave_IO_State # +Master_Host 127.0.0.1 +Master_User root +Master_Port # +Connect_Retry 1 +Master_Log_File master-bin.000001 +Read_Master_Log_Pos # +Relay_Log_File # +Relay_Log_Pos # +Relay_Master_Log_File master-bin.000001 +Slave_IO_Running Yes +Slave_SQL_Running No +Replicate_Do_DB +Replicate_Ignore_DB +Replicate_Do_Table +Replicate_Ignore_Table +Replicate_Wild_Do_Table +Replicate_Wild_Ignore_Table +Last_Errno 1105 +Last_Error Error 'master may suffer from http://bugs.mysql.com/bug.php?id=24432 so slave stops; check error log on slave for more info' on query. Default database: 'test'. Query: 'INSERT INTO t1 (field_1, field_2, field_3) +SELECT t2.field_a, t2.field_b, t2.field_c +FROM t2 +ON DUPLICATE KEY UPDATE +t1.field_3 = t2.field_c' +Skip_Counter 0 +Exec_Master_Log_Pos 1270 +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 # +SELECT * FROM t1; +id field_1 field_2 field_3 +drop table t1, t2; +drop table t1, t2; diff --git a/mysql-test/r/rpl_loaddata_charset.result b/mysql-test/r/rpl_loaddata_charset.result new file mode 100644 index 00000000000..929d06e74cf --- /dev/null +++ b/mysql-test/r/rpl_loaddata_charset.result @@ -0,0 +1,37 @@ +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; +create table t1 (a varchar(10) character set utf8); +load data infile '../std_data_ln/loaddata6.dat' into table t1; +set @@character_set_database=koi8r; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +set @@character_set_database=DEFAULT; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +set @@character_set_database=koi8r; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +set @@character_set_database=DEFAULT; +load data infile '../std_data_ln/loaddata6.dat' into table t1 character set koi8r; +select hex(a) from t1; +hex(a) +C3BF +D0AA +C3BF +C3BF +C3BF +D0AA +D0AA +select hex(a) from t1; +hex(a) +C3BF +D0AA +C3BF +C3BF +C3BF +D0AA +D0AA +drop table t1; diff --git a/mysql-test/r/rpl_replicate_do.result b/mysql-test/r/rpl_replicate_do.result index f79f6305342..9bacffb0609 100644 --- a/mysql-test/r/rpl_replicate_do.result +++ b/mysql-test/r/rpl_replicate_do.result @@ -41,3 +41,37 @@ select * from t1; ts 2005-08-12 00:00:00 drop table t1; +*** master *** +create table t1 (a int, b int); +create trigger trg1 before insert on t1 for each row set new.b=2; +create table t2 (a int, b int); +create trigger trg2 before insert on t2 for each row set new.b=2; +show tables; +Tables_in_test +t1 +t2 +show triggers; +Trigger Event Table Statement Timing Created sql_mode Definer +trg1 INSERT t1 set new.b=2 BEFORE NULL root@localhost +trg2 INSERT t2 set new.b=2 BEFORE NULL root@localhost +*** slave *** +show tables; +Tables_in_test +t1 +show triggers; +Trigger Event Table Statement Timing Created sql_mode Definer +trg1 INSERT t1 set new.b=2 BEFORE NULL root@localhost +*** master *** +drop trigger trg1; +drop trigger trg2; +show triggers; +Trigger Event Table Statement Timing Created sql_mode Definer +*** slave *** +show tables; +Tables_in_test +t1 +show triggers; +Trigger Event Table Statement Timing Created sql_mode Definer +*** master *** +drop table t1; +drop table t2; diff --git a/mysql-test/r/rpl_user_variables.result b/mysql-test/r/rpl_user_variables.result index 45618688a33..05f2b68042e 100644 --- a/mysql-test/r/rpl_user_variables.result +++ b/mysql-test/r/rpl_user_variables.result @@ -108,4 +108,180 @@ slave-bin.000001 # User var 2 # @`a`=NULL slave-bin.000001 # Query 1 # use `test`; insert into t1 values (@a),(@a),(@a*5) insert into t1 select * FROM (select @var1 union select @var2) AS t2; drop table t1; +End of 4.1 tests. +DROP TABLE IF EXISTS t20; +DROP TABLE IF EXISTS t21; +DROP PROCEDURE IF EXISTS test.insert; +CREATE TABLE t20 (a VARCHAR(20)); +CREATE TABLE t21 (a VARCHAR(20)); +CREATE PROCEDURE test.insert() +BEGIN +IF (@VAR) +THEN +INSERT INTO test.t20 VALUES ('SP_TRUE'); +ELSE +INSERT INTO test.t20 VALUES ('SP_FALSE'); +END IF; +END| +CREATE TRIGGER test.insert_bi BEFORE INSERT +ON test.t20 FOR EACH ROW +BEGIN +IF (@VAR) +THEN +INSERT INTO test.t21 VALUES ('TRIG_TRUE'); +ELSE +INSERT INTO test.t21 VALUES ('TRIG_FALSE'); +END IF; +END| +SET @VAR=0; +CALL test.insert(); +SET @VAR=1; +CALL test.insert(); +On master: Check the tables for correct data +SELECT * FROM t20; +a +SP_FALSE +SP_TRUE +SELECT * FROM t21; +a +TRIG_FALSE +TRIG_TRUE +On slave: Check the tables for correct data and it matches master +SELECT * FROM t20; +a +SP_FALSE +SP_TRUE +SELECT * FROM t21; +a +TRIG_FALSE +TRIG_TRUE +DROP TABLE t20; +DROP TABLE t21; +DROP PROCEDURE test.insert; +DROP TABLE IF EXISTS t1; +DROP FUNCTION IF EXISTS test.square; +CREATE TABLE t1 (i INT); +CREATE FUNCTION test.square() RETURNS INTEGER DETERMINISTIC RETURN (@var * @var); +SET @var = 1; +INSERT INTO t1 VALUES (square()); +SET @var = 2; +INSERT INTO t1 VALUES (square()); +SET @var = 3; +INSERT INTO t1 VALUES (square()); +SET @var = 4; +INSERT INTO t1 VALUES (square()); +SET @var = 5; +INSERT INTO t1 VALUES (square()); +On master: Retrieve the values from the table +SELECT * FROM t1; +i +1 +4 +9 +16 +25 +On slave: Retrieve the values from the table and verify they are the same as on master +SELECT * FROM t1; +i +1 +4 +9 +16 +25 +DROP TABLE t1; +DROP FUNCTION test.square; +DROP TABLE IF EXISTS t1; +DROP FUNCTION IF EXISTS f1; +DROP FUNCTION IF EXISTS f2; +CREATE TABLE t1(a int); +CREATE FUNCTION f1() returns int deterministic +BEGIN +return @a; +END | +CREATE FUNCTION f2() returns int deterministic +BEGIN +IF (@b > 0) then +SET @c = (@a + @b); +else +SET @c = (@a - 1); +END if; +return @c; +END | +SET @a=500; +INSERT INTO t1 values(f1()); +SET @b = 125; +SET @c = 1; +INSERT INTO t1 values(f2()); +On master: Retrieve the values from the table +SELECT * from t1; +a +500 +625 +On slave: Check the tables for correct data and it matches master +SELECT * from t1; +a +500 +625 +DROP TABLE t1; +DROP FUNCTION f1; +DROP FUNCTION f2; +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +CREATE TABLE t1 (i int); +CREATE TABLE t2 (k int); +CREATE trigger t1_bi before INSERT on t1 for each row +BEGIN +INSERT INTO t2 values (@a); +SET @a:=42; +INSERT INTO t2 values (@a); +END | +SET @a:=100; +INSERT INTO t1 values (5); +On master: Check to see that data was inserted correctly in both tables +SELECT * from t1; +i +5 +SELECT * from t2; +k +100 +42 +On slave: Check the tables for correct data and it matches master +SELECT * from t1; +i +5 +SELECT * from t2; +k +100 +42 +End of 5.0 tests. +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE IF EXISTS t1; +DROP FUNCTION IF EXISTS f1; +DROP FUNCTION IF EXISTS f2; +CREATE TABLE t1 (i INT); +CREATE FUNCTION f1() RETURNS INT RETURN @a; +CREATE FUNCTION f2() RETURNS INT +BEGIN +INSERT INTO t1 VALUES (10 + @a); +RETURN 0; +END| +SET @a:=123; +SELECT f1(), f2(); +f1() f2() +123 0 +On master: Check to see that data was inserted correctly +INSERT INTO t1 VALUES(f1()); +SELECT * FROM t1; +i +133 +123 +On slave: Check the table for correct data and it matches master +SELECT * FROM t1; +i +133 +123 +DROP FUNCTION f1; +DROP FUNCTION f2; +DROP TABLE t1; stop slave; diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result index e7e387f4348..332d4fa4519 100644 --- a/mysql-test/r/sp-error.result +++ b/mysql-test/r/sp-error.result @@ -1128,9 +1128,9 @@ drop view if exists v1, v2, v3, v4; create function bug11555_1() returns int return (select max(i) from t1); create function bug11555_2() returns int return bug11555_1(); create view v1 as select bug11555_1(); -ERROR 42S02: Table 'test.t1' doesn't exist +drop view v1; create view v2 as select bug11555_2(); -ERROR 42S02: Table 'test.t1' doesn't exist +drop view v2; create table t1 (i int); create view v1 as select bug11555_1(); create view v2 as select bug11555_2(); @@ -1143,8 +1143,7 @@ ERROR HY000: View 'test.v2' references invalid table(s) or column(s) or function select * from v3; ERROR HY000: View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them create view v4 as select * from v1; -ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -drop view v1, v2, v3; +drop view v1, v2, v3, v4; drop function bug11555_1; drop function bug11555_2; create table t1 (i int); @@ -1153,12 +1152,12 @@ create trigger t1_ai after insert on t1 for each row insert into t2 values (new. create view v1 as select * from t1; drop table t2; insert into v1 values (1); -ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +ERROR HY000: Table 't2' was not locked with LOCK TABLES drop trigger t1_ai; create function bug11555_1() returns int return (select max(i) from t2); create trigger t1_ai after insert on t1 for each row set @a:=bug11555_1(); insert into v1 values (2); -ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +ERROR HY000: Table 't2' was not locked with LOCK TABLES drop function bug11555_1; drop table t1; drop view v1; @@ -1269,3 +1268,135 @@ call bug24491(); ERROR 42S22: Unknown column 'y.value' in 'field list' drop procedure bug24491; drop tables t1; +DROP FUNCTION IF EXISTS bug18914_f1; +DROP FUNCTION IF EXISTS bug18914_f2; +DROP PROCEDURE IF EXISTS bug18914_p1; +DROP PROCEDURE IF EXISTS bug18914_p2; +DROP TABLE IF EXISTS t1, t2; +CREATE TABLE t1 (i INT); +CREATE PROCEDURE bug18914_p1() CREATE TABLE t2 (i INT); +CREATE PROCEDURE bug18914_p2() DROP TABLE IF EXISTS no_such_table; +CREATE FUNCTION bug18914_f1() RETURNS INT +BEGIN +CALL bug18914_p1(); +RETURN 1; +END | +CREATE FUNCTION bug18914_f2() RETURNS INT +BEGIN +CALL bug18914_p2(); +RETURN 1; +END | +CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW +CALL bug18914_p1(); +INSERT INTO t1 VALUES (1); +ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger. +SELECT bug18914_f1(); +ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger. +SELECT bug18914_f2(); +ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger. +SELECT * FROM t2; +ERROR 42S02: Table 'test.t2' doesn't exist +DROP FUNCTION bug18914_f1; +DROP FUNCTION bug18914_f2; +DROP PROCEDURE bug18914_p1; +DROP PROCEDURE bug18914_p2; +DROP TABLE t1; +drop table if exists bogus_table_20713; +drop function if exists func_20713_a; +drop function if exists func_20713_b; +create table bogus_table_20713( id int(10) not null primary key); +insert into bogus_table_20713 values (1), (2), (3); +create function func_20713_a() returns int(11) +begin +declare id int; +declare continue handler for sqlexception set id=null; +set @in_func := 1; +set id = (select id from bogus_table_20713 where id = 3); +set @in_func := 2; +return id; +end// +create function func_20713_b() returns int(11) +begin +declare id int; +declare continue handler for sqlstate value '42S02' set id=null; +set @in_func := 1; +set id = (select id from bogus_table_20713 where id = 3); +set @in_func := 2; +return id; +end// +set @in_func := 0; +select func_20713_a(); +func_20713_a() +NULL +select @in_func; +@in_func +2 +set @in_func := 0; +select func_20713_b(); +func_20713_b() +NULL +select @in_func; +@in_func +2 +drop table bogus_table_20713; +set @in_func := 0; +select func_20713_a(); +func_20713_a() +NULL +select @in_func; +@in_func +2 +set @in_func := 0; +select func_20713_b(); +func_20713_b() +NULL +select @in_func; +@in_func +2 +drop function if exists func_20713_a; +drop function if exists func_20713_b; +drop table if exists table_25345_a; +drop table if exists table_25345_b; +drop procedure if exists proc_25345; +drop function if exists func_25345; +drop function if exists func_25345_b; +create table table_25345_a (a int); +create table table_25345_b (b int); +create procedure proc_25345() +begin +declare c1 cursor for select a from table_25345_a; +declare c2 cursor for select b from table_25345_b; +select 1 as result; +end || +create function func_25345() returns int(11) +begin +call proc_25345(); +return 1; +end || +create function func_25345_b() returns int(11) +begin +declare c1 cursor for select a from table_25345_a; +declare c2 cursor for select b from table_25345_b; +return 1; +end || +call proc_25345(); +result +1 +select func_25345(); +ERROR 0A000: Not allowed to return a result set from a function +select func_25345_b(); +func_25345_b() +1 +drop table table_25345_a; +call proc_25345(); +result +1 +select func_25345(); +ERROR 0A000: Not allowed to return a result set from a function +select func_25345_b(); +func_25345_b() +1 +drop table table_25345_b; +drop procedure proc_25345; +drop function func_25345; +drop function func_25345_b; diff --git a/mysql-test/r/sp-vars.result b/mysql-test/r/sp-vars.result index f362187cd14..6090dfdf737 100644 --- a/mysql-test/r/sp-vars.result +++ b/mysql-test/r/sp-vars.result @@ -431,17 +431,17 @@ SELECT HEX(v10); END| CALL p1(); HEX(v1) -01 +1 HEX(v2) -00 +0 HEX(v3) -05 +5 HEX(v4) 5555555555555555 HEX(v5) -07 +7 HEX(v6) -0000000000000005 +5 HEX(v7) 80 HEX(v8) @@ -748,12 +748,60 @@ HEX(b) b = 0 b = FALSE b IS FALSE b = 1 b = TRUE b IS TRUE 1 0 0 0 1 1 1 call p2(); HEX(vb) vb = 0 vb = FALSE vb IS FALSE vb = 1 vb = TRUE vb IS TRUE -00 1 1 1 0 0 0 +0 1 1 1 0 0 0 HEX(vb) vb = 0 vb = FALSE vb IS FALSE vb = 1 vb = TRUE vb IS TRUE -01 0 0 1 1 1 0 +1 0 0 0 1 1 1 DROP TABLE t1; DROP PROCEDURE p1; DROP PROCEDURE p2; +DROP TABLE IF EXISTS table_12976_a; +DROP TABLE IF EXISTS table_12976_b; +DROP PROCEDURE IF EXISTS proc_12976_a; +DROP PROCEDURE IF EXISTS proc_12976_b; +CREATE TABLE table_12976_a (val bit(1)); +CREATE TABLE table_12976_b( +appname varchar(15), +emailperm bit not null default 1, +phoneperm bit not null default 0); +insert into table_12976_b values ('A', b'1', b'1'), ('B', b'0', b'0'); +CREATE PROCEDURE proc_12976_a() +BEGIN +declare localvar bit(1); +SELECT val INTO localvar FROM table_12976_a; +SELECT coalesce(localvar, 1)+1, coalesce(val, 1)+1 FROM table_12976_a; +END|| +CREATE PROCEDURE proc_12976_b( +name varchar(15), +out ep bit, +out msg varchar(10)) +BEGIN +SELECT emailperm into ep FROM table_12976_b where (appname = name); +IF ep is true THEN +SET msg = 'True'; +ELSE +SET msg = 'False'; +END IF; +END|| +INSERT table_12976_a VALUES (0); +call proc_12976_a(); +coalesce(localvar, 1)+1 coalesce(val, 1)+1 +1 1 +UPDATE table_12976_a set val=1; +call proc_12976_a(); +coalesce(localvar, 1)+1 coalesce(val, 1)+1 +2 2 +call proc_12976_b('A', @ep, @msg); +select @ep, @msg; +@ep @msg +1 True +call proc_12976_b('B', @ep, @msg); +select @ep, @msg; +@ep @msg +0 False +DROP TABLE table_12976_a; +DROP TABLE table_12976_b; +DROP PROCEDURE proc_12976_a; +DROP PROCEDURE proc_12976_b; --------------------------------------------------------------- BUG#9572 diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 8e3c057cc62..2f9f4fbdad2 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -1155,9 +1155,13 @@ create function f12_2() returns int return (select count(*) from t3)| drop temporary table t3| select f12_1()| -ERROR 42S02: Table 'test.t3' doesn't exist +f12_1() +3 +Warnings: +Note 1051 Unknown table 't3' select f12_1() from t1 limit 1| -ERROR 42S02: Table 'test.t3' doesn't exist +f12_1() +3 drop function f0| drop function f1| drop function f2| @@ -5741,4 +5745,38 @@ END| CALL bug24117()| DROP PROCEDURE bug24117| DROP TABLE t3| +drop function if exists func_8407_a| +drop function if exists func_8407_b| +create function func_8407_a() returns int +begin +declare x int; +declare continue handler for sqlexception +begin +end; +select 1 from no_such_view limit 1 into x; +return x; +end| +create function func_8407_b() returns int +begin +declare x int default 0; +declare continue handler for sqlstate '42S02' + begin +set x:= x+1000; +end; +case (select 1 from no_such_view limit 1) +when 1 then set x:= x+1; +when 2 then set x:= x+2; +else set x:= x+100; +end case; +set x:=x + 500; +return x; +end| +select func_8407_a()| +func_8407_a() +NULL +select func_8407_b()| +func_8407_b() +1500 +drop function func_8407_a| +drop function func_8407_b| drop table t1,t2; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 7177b4e4432..7ff4b9bcf16 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -3095,7 +3095,7 @@ SELECT a FROM t1 GROUP BY a HAVING IFNULL((SELECT b FROM t2 WHERE b > 4), (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)) > 3; ERROR 21000: Subquery returns more than 1 row -SELECT a FROM t1 +SELECT a FROM t1 ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2), (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)); a @@ -3103,11 +3103,11 @@ a 4 1 3 -SELECT a FROM t1 +SELECT a FROM t1 ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1), (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)); ERROR 21000: Subquery returns more than 1 row -SELECT a FROM t1 +SELECT a FROM t1 ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4), (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)); a @@ -3115,7 +3115,7 @@ a 1 3 4 -SELECT a FROM t1 +SELECT a FROM t1 ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4), (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)); ERROR 21000: Subquery returns more than 1 row @@ -3683,16 +3683,16 @@ CREATE TABLE t1 (id char(4) PRIMARY KEY, c int); CREATE TABLE t2 (c int); INSERT INTO t1 VALUES ('aa', 1); INSERT INTO t2 VALUES (1); -SELECT * FROM t1 +SELECT * FROM t1 WHERE EXISTS (SELECT c FROM t2 WHERE c=1 -UNION +UNION SELECT c from t2 WHERE c=t1.c); id c aa 1 INSERT INTO t1 VALUES ('bb', 2), ('cc', 3), ('dd',1); -SELECT * FROM t1 +SELECT * FROM t1 WHERE EXISTS (SELECT c FROM t2 WHERE c=1 -UNION +UNION SELECT c from t2 WHERE c=t1.c); id c aa 1 @@ -3702,9 +3702,9 @@ dd 1 INSERT INTO t2 VALUES (2); CREATE TABLE t3 (c int); INSERT INTO t3 VALUES (1); -SELECT * FROM t1 +SELECT * FROM t1 WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1 -UNION +UNION SELECT c from t2 WHERE c=t1.c); id c aa 1 @@ -3754,3 +3754,116 @@ a MAX(b) test 2 3 h 3 4 i DROP TABLE t1; +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +DROP TABLE IF EXISTS t1xt2; +CREATE TABLE t1 ( +id_1 int(5) NOT NULL, +t varchar(4) DEFAULT NULL +); +CREATE TABLE t2 ( +id_2 int(5) NOT NULL, +t varchar(4) DEFAULT NULL +); +CREATE TABLE t1xt2 ( +id_1 int(5) NOT NULL, +id_2 int(5) NOT NULL +); +INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'); +INSERT INTO t2 VALUES (2, 'bb'), (3, 'cc'), (4, 'dd'), (12, 'aa'); +INSERT INTO t1xt2 VALUES (2, 2), (3, 3), (4, 4); +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); +id_1 +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))); +id_1 +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)))); +id_1 +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); +id_1 +1 +2 +3 +4 +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1))); +id_1 +1 +2 +3 +4 +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1)))); +id_1 +1 +2 +3 +4 +insert INTO t1xt2 VALUES (1, 12); +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); +id_1 +1 +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))); +id_1 +1 +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)))); +id_1 +1 +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); +id_1 +2 +3 +4 +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))); +id_1 +2 +3 +4 +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)))); +id_1 +2 +3 +4 +insert INTO t1xt2 VALUES (2, 12); +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); +id_1 +1 +2 +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))); +id_1 +1 +2 +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)))); +id_1 +1 +2 +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); +id_1 +3 +4 +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))); +id_1 +3 +4 +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)))); +id_1 +3 +4 +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t1xt2; diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index fe0e444f87d..aa511ca27a7 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -1335,4 +1335,41 @@ SELECT fubar_id FROM t2; fubar_id 1 DROP TABLE t1,t2; +DROP TABLE IF EXISTS bug21825_A; +DROP TABLE IF EXISTS bug21825_B; +CREATE TABLE bug21825_A (id int(10)); +CREATE TABLE bug21825_B (id int(10)); +CREATE TRIGGER trgA AFTER INSERT ON bug21825_A +FOR EACH ROW +BEGIN +INSERT INTO bug21825_B (id) values (1); +END// +INSERT INTO bug21825_A (id) VALUES (10); +INSERT INTO bug21825_A (id) VALUES (20); +DROP TABLE bug21825_B; +DELETE FROM bug21825_A WHERE id = 20; +DROP TABLE bug21825_A; +DROP TABLE IF EXISTS bug22580_t1; +DROP PROCEDURE IF EXISTS bug22580_proc_1; +DROP PROCEDURE IF EXISTS bug22580_proc_2; +CREATE TABLE bug22580_t1 (a INT, b INT); +CREATE PROCEDURE bug22580_proc_2() +BEGIN +DROP TABLE IF EXISTS bug22580_tmp; +CREATE TEMPORARY TABLE bug22580_tmp (a INT); +DROP TABLE bug22580_tmp; +END|| +CREATE PROCEDURE bug22580_proc_1() +BEGIN +CALL bug22580_proc_2(); +END|| +CREATE TRIGGER t1bu BEFORE UPDATE ON bug22580_t1 +FOR EACH ROW +BEGIN +CALL bug22580_proc_1(); +END|| +INSERT INTO bug22580_t1 VALUES (1,1); +DROP TABLE bug22580_t1; +DROP PROCEDURE bug22580_proc_1; +DROP PROCEDURE bug22580_proc_2; End of 5.0 tests diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 50b41e1352f..7f9353d56fe 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -834,14 +834,16 @@ show create view v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 99999999999999999999999999999999999999999999999999999 AS `col1` drop view v1; -create table tü (cü char); -create view vü as select cü from tü; -insert into vü values ('ü'); -select * from vü; -cü -ü -drop view vü; -drop table tü; +set names utf8; +create table tü (cü char); +create view vü as select cü from tü; +insert into vü values ('ü'); +select * from vü; +cü +ü +drop view vü; +drop table tü; +set names latin1; create table t1 (a int, b int); insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10); create view v1(c) as select a+1 from t1 where b >= 4; @@ -1933,11 +1935,11 @@ create function f1 () returns int return (select max(col1) from t1); DROP TABLE t1; CHECK TABLE v1, v2, v3, v4, v5, v6; Table Op Msg_type Msg_text -test.v1 check error View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +test.v1 check status OK test.v2 check status OK -test.v3 check error View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +test.v3 check status OK test.v4 check status OK -test.v5 check error View 'test.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +test.v5 check status OK test.v6 check status OK drop function f1; drop function f2; @@ -3035,7 +3037,7 @@ View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _latin1'The\ZEnd' AS `TheEnd` DROP VIEW v1; CREATE TABLE t1 (mydate DATETIME); -INSERT INTO t1 VALUES +INSERT INTO t1 VALUES ('2007-01-01'), ('2007-01-02'), ('2007-01-30'), ('2007-01-31'); CREATE VIEW v1 AS SELECT mydate from t1; SELECT * FROM t1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31'; @@ -3135,4 +3137,139 @@ code COUNT(DISTINCT country) 100 2 DROP VIEW v1; DROP TABLE t1; +DROP VIEW IF EXISTS v1; +SELECT * FROM (SELECT 1) AS t; +1 +1 +CREATE VIEW v1 AS SELECT * FROM (SELECT 1) AS t; +ERROR HY000: View's SELECT contains a subquery in the FROM clause +# Previously the following would fail. +SELECT * FROM (SELECT 1) AS t; +1 +1 +drop view if exists view_24532_a; +drop view if exists view_24532_b; +drop table if exists table_24532; +create table table_24532 ( +a int, +b bigint, +c int(4), +d bigint(48) +); +create view view_24532_a as +select +a IS TRUE, +a IS NOT TRUE, +a IS FALSE, +a IS NOT FALSE, +a IS UNKNOWN, +a IS NOT UNKNOWN, +a is NULL, +a IS NOT NULL, +ISNULL(a), +b IS TRUE, +b IS NOT TRUE, +b IS FALSE, +b IS NOT FALSE, +b IS UNKNOWN, +b IS NOT UNKNOWN, +b is NULL, +b IS NOT NULL, +ISNULL(b), +c IS TRUE, +c IS NOT TRUE, +c IS FALSE, +c IS NOT FALSE, +c IS UNKNOWN, +c IS NOT UNKNOWN, +c is NULL, +c IS NOT NULL, +ISNULL(c), +d IS TRUE, +d IS NOT TRUE, +d IS FALSE, +d IS NOT FALSE, +d IS UNKNOWN, +d IS NOT UNKNOWN, +d is NULL, +d IS NOT NULL, +ISNULL(d) +from table_24532; +describe view_24532_a; +Field Type Null Key Default Extra +a IS TRUE int(1) NO 0 +a IS NOT TRUE int(1) NO 0 +a IS FALSE int(1) NO 0 +a IS NOT FALSE int(1) NO 0 +a IS UNKNOWN int(1) NO 0 +a IS NOT UNKNOWN int(1) NO 0 +a is NULL int(1) NO 0 +a IS NOT NULL int(1) NO 0 +ISNULL(a) int(1) NO 0 +b IS TRUE int(1) NO 0 +b IS NOT TRUE int(1) NO 0 +b IS FALSE int(1) NO 0 +b IS NOT FALSE int(1) NO 0 +b IS UNKNOWN int(1) NO 0 +b IS NOT UNKNOWN int(1) NO 0 +b is NULL int(1) NO 0 +b IS NOT NULL int(1) NO 0 +ISNULL(b) int(1) NO 0 +c IS TRUE int(1) NO 0 +c IS NOT TRUE int(1) NO 0 +c IS FALSE int(1) NO 0 +c IS NOT FALSE int(1) NO 0 +c IS UNKNOWN int(1) NO 0 +c IS NOT UNKNOWN int(1) NO 0 +c is NULL int(1) NO 0 +c IS NOT NULL int(1) NO 0 +ISNULL(c) int(1) NO 0 +d IS TRUE int(1) NO 0 +d IS NOT TRUE int(1) NO 0 +d IS FALSE int(1) NO 0 +d IS NOT FALSE int(1) NO 0 +d IS UNKNOWN int(1) NO 0 +d IS NOT UNKNOWN int(1) NO 0 +d is NULL int(1) NO 0 +d IS NOT NULL int(1) NO 0 +ISNULL(d) int(1) NO 0 +create view view_24532_b as +select +a IS TRUE, +if(ifnull(a, 0), 1, 0) as old_istrue, +a IS NOT TRUE, +if(ifnull(a, 0), 0, 1) as old_isnottrue, +a IS FALSE, +if(ifnull(a, 1), 0, 1) as old_isfalse, +a IS NOT FALSE, +if(ifnull(a, 1), 1, 0) as old_isnotfalse +from table_24532; +describe view_24532_b; +Field Type Null Key Default Extra +a IS TRUE int(1) NO 0 +old_istrue int(1) NO 0 +a IS NOT TRUE int(1) NO 0 +old_isnottrue int(1) NO 0 +a IS FALSE int(1) NO 0 +old_isfalse int(1) NO 0 +a IS NOT FALSE int(1) NO 0 +old_isnotfalse int(1) NO 0 +show create view view_24532_b; +View Create View +view_24532_b CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_24532_b` AS select (`table_24532`.`a` is true) AS `a IS TRUE`,if(ifnull(`table_24532`.`a`,0),1,0) AS `old_istrue`,(`table_24532`.`a` is not true) AS `a IS NOT TRUE`,if(ifnull(`table_24532`.`a`,0),0,1) AS `old_isnottrue`,(`table_24532`.`a` is false) AS `a IS FALSE`,if(ifnull(`table_24532`.`a`,1),0,1) AS `old_isfalse`,(`table_24532`.`a` is not false) AS `a IS NOT FALSE`,if(ifnull(`table_24532`.`a`,1),1,0) AS `old_isnotfalse` from `table_24532` +insert into table_24532 values (0, 0, 0, 0); +select * from view_24532_b; +a IS TRUE old_istrue a IS NOT TRUE old_isnottrue a IS FALSE old_isfalse a IS NOT FALSE old_isnotfalse +0 0 1 1 1 1 0 0 +update table_24532 set a=1; +select * from view_24532_b; +a IS TRUE old_istrue a IS NOT TRUE old_isnottrue a IS FALSE old_isfalse a IS NOT FALSE old_isnotfalse +1 1 0 0 0 0 1 1 +update table_24532 set a=NULL; +select * from view_24532_b; +a IS TRUE old_istrue a IS NOT TRUE old_isnottrue a IS FALSE old_isfalse a IS NOT FALSE old_isnotfalse +0 0 1 1 0 0 1 1 +drop view view_24532_a; +drop view view_24532_b; +drop table table_24532; End of 5.0 tests. diff --git a/mysql-test/std_data/init_file.dat b/mysql-test/std_data/init_file.dat index 814e968eb31..cb8e0778438 100644 --- a/mysql-test/std_data/init_file.dat +++ b/mysql-test/std_data/init_file.dat @@ -27,3 +27,12 @@ insert into t2 values (11), (13); drop procedure p1; drop function f1; drop view v1; + +# +# Bug#23240 --init-file statements with NOW() reports '1970-01-01 11:00:00'as the date time +# +CREATE DATABASE IF NOT EXISTS init_file; +CREATE TABLE IF NOT EXISTS init_file.startup ( startdate DATETIME ); +INSERT INTO init_file.startup VALUES ( NOW() ); + + diff --git a/mysql-test/std_data/loaddata6.dat b/mysql-test/std_data/loaddata6.dat new file mode 100644 index 00000000000..29e181ebb88 --- /dev/null +++ b/mysql-test/std_data/loaddata6.dat @@ -0,0 +1 @@ +ÿ diff --git a/mysql-test/t/ctype_cp932.test b/mysql-test/t/ctype_cp932.test index 688d06c4dde..c6196b928b0 100644 --- a/mysql-test/t/ctype_cp932.test +++ b/mysql-test/t/ctype_cp932.test @@ -413,3 +413,14 @@ select * from t1; insert into t1 values ('abc'); select * from t1; drop table t1; + +# +# Bug#25815 Data truncated for column TEXT +# +set names utf8; +create table t1 (a text) default character set cp932; +insert into t1 values (_utf8 0xE38182); +show warnings; +select * from t1; +select hex(a) from t1; +drop table t1; diff --git a/mysql-test/t/ctype_cp932_binlog.test b/mysql-test/t/ctype_cp932_binlog.test index 3bbbe94f0e3..ee0e588fdae 100644 --- a/mysql-test/t/ctype_cp932_binlog.test +++ b/mysql-test/t/ctype_cp932_binlog.test @@ -51,7 +51,7 @@ CALL bug18293("Foo's a Bar", _cp932 0xED40ED41ED42, 47.93)| SELECT HEX(s1),HEX(s2),d FROM t4| DROP PROCEDURE bug18293| DROP TABLE t4| -SHOW BINLOG EVENTS FROM 393| +SHOW BINLOG EVENTS FROM 402| delimiter ;| # End of 5.0 tests diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index 99ba55a7e2b..df56165950f 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -10,7 +10,5 @@ # ############################################################################## -im_daemon_life_cycle : Bug#24415 see note: [19 Dec 23:17] Trudy Pelzer ndb_load : Bug#17233 user_limits : Bug#23921 random failure of user_limits.test - diff --git a/mysql-test/t/func_if.test b/mysql-test/t/func_if.test index beaa371f847..5373ca3fec6 100644 --- a/mysql-test/t/func_if.test +++ b/mysql-test/t/func_if.test @@ -97,3 +97,14 @@ create table t1 (f1 int, f2 int); insert into t1 values(1,1),(0,0); select f1, f2, if(f1, 40.0, 5.00) from t1 group by f1 order by f2; drop table t1; + +# +# Bug#24532 (The return data type of IS TRUE is different from similar +# operations) +# +# IF(x, unsigned, unsigned) should be unsigned. +# + +select if(0, 18446744073709551610, 18446744073709551610); + + diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test index 5cac6c45f65..8ff62f68e45 100644 --- a/mysql-test/t/func_misc.test +++ b/mysql-test/t/func_misc.test @@ -132,4 +132,61 @@ set global query_cache_size=default; create table t1 select INET_ATON('255.255.0.1') as `a`; show create table t1; drop table t1; + +# +# Bug#26093 (SELECT BENCHMARK() for SELECT statements does not produce +# valid results) +# + +--disable_warnings +drop table if exists table_26093; +drop function if exists func_26093_a; +drop function if exists func_26093_b; +--enable_warnings + +create table table_26093(a int); +insert into table_26093 values +(1), (2), (3), (4), (5), +(6), (7), (8), (9), (10); + +delimiter //; + +create function func_26093_a(x int) returns int +begin + set @invoked := @invoked + 1; + return x; +end// + +create function func_26093_b(x int, y int) returns int +begin + set @invoked := @invoked + 1; + return x; +end// + +delimiter ;// + +select avg(a) from table_26093; + +select benchmark(100, (select avg(a) from table_26093)); + +set @invoked := 0; +select benchmark(100, (select avg(func_26093_a(a)) from table_26093)); +# Returns only 10, since intermediate results are cached. +select @invoked; + +set @invoked := 0; +select benchmark(100, (select avg(func_26093_b(a, rand())) from table_26093)); +# Returns 1000, due to rand() preventing caching. +select @invoked; + +--error ER_SUBQUERY_NO_1_ROW +select benchmark(100, (select (a) from table_26093)); + +--error ER_OPERAND_COLUMNS +select benchmark(100, (select 1, 1)); + +drop table table_26093; +drop function func_26093_a; +drop function func_26093_b; + --echo End of 5.0 tests diff --git a/mysql-test/t/im_daemon_life_cycle.imtest b/mysql-test/t/im_daemon_life_cycle.imtest index acd615809f3..0ecc3f18f40 100644 --- a/mysql-test/t/im_daemon_life_cycle.imtest +++ b/mysql-test/t/im_daemon_life_cycle.imtest @@ -23,15 +23,20 @@ # - wait for IM-main to start accepting connections before continue test # case; # +# NOTE: timeout is 55 seconds. Timeout should be more than shutdown-delay +# specified for managed MySQL instance. Now shutdown-delay is 10 seconds +# (set in mysql-test-run.pl). So, 55 seconds should be enough to make 5 +# attempts. +# ########################################################################### --exec $MYSQL_TEST_DIR/t/log.sh im_daemon_life_cycle Main-test: starting... --exec $MYSQL_TEST_DIR/t/log.sh im_daemon_life_cycle Killing IM-main... ---exec $MYSQL_TEST_DIR/t/kill_n_check.sh $IM_PATH_PID restarted 30 im_daemon_life_cycle +--exec $MYSQL_TEST_DIR/t/kill_n_check.sh $IM_PATH_PID restarted 55 im_daemon_life_cycle --exec $MYSQL_TEST_DIR/t/log.sh im_daemon_life_cycle Waiting for IM-main to start accepting connections... ---exec $MYSQL_TEST_DIR/t/wait_for_socket.sh $EXE_MYSQL $IM_PATH_SOCK $IM_USERNAME $IM_PASSWORD '' 30 im_daemon_life_cycle +--exec $MYSQL_TEST_DIR/t/wait_for_socket.sh $EXE_MYSQL $IM_PATH_SOCK $IM_USERNAME $IM_PASSWORD '' 55 im_daemon_life_cycle --exec $MYSQL_TEST_DIR/t/log.sh im_daemon_life_cycle Main-test: done. @@ -58,23 +63,23 @@ START INSTANCE mysqld2; --exec $MYSQL_TEST_DIR/t/log.sh im_daemon_life_cycle mysqld2: waiting to start... ---exec $MYSQL_TEST_DIR/t/wait_for_process.sh $IM_MYSQLD2_PATH_PID 30 started im_daemon_life_cycle +--exec $MYSQL_TEST_DIR/t/wait_for_process.sh $IM_MYSQLD2_PATH_PID 55 started im_daemon_life_cycle --exec $MYSQL_TEST_DIR/t/log.sh im_daemon_life_cycle mysqld2: started. # 2. Restart IM-main; --exec $MYSQL_TEST_DIR/t/log.sh im_daemon_life_cycle Killing IM-main... ---exec $MYSQL_TEST_DIR/t/kill_n_check.sh $IM_PATH_PID restarted 30 im_daemon_life_cycle +--exec $MYSQL_TEST_DIR/t/kill_n_check.sh $IM_PATH_PID restarted 55 im_daemon_life_cycle --exec $MYSQL_TEST_DIR/t/log.sh im_daemon_life_cycle Waiting for IM-main to start accepting connections... ---exec $MYSQL_TEST_DIR/t/wait_for_socket.sh $EXE_MYSQL $IM_PATH_SOCK $IM_USERNAME $IM_PASSWORD '' 30 im_daemon_life_cycle +--exec $MYSQL_TEST_DIR/t/wait_for_socket.sh $EXE_MYSQL $IM_PATH_SOCK $IM_USERNAME $IM_PASSWORD '' 55 im_daemon_life_cycle # 3. Issue some statement -- connection should be re-established. --exec $MYSQL_TEST_DIR/t/log.sh im_daemon_life_cycle Checking that IM-main processing commands... ---replace_column 3 VERSION +--replace_column 2 STATE 3 VERSION SHOW INSTANCE STATUS mysqld1; # 4. Stop mysqld2, because it will not be stopped by IM, as it is nonguarded. @@ -85,7 +90,7 @@ SHOW INSTANCE STATUS mysqld1; STOP INSTANCE mysqld2; --exec $MYSQL_TEST_DIR/t/log.sh im_daemon_life_cycle mysqld2: waiting to stop... ---exec $MYSQL_TEST_DIR/t/wait_for_process.sh $IM_MYSQLD2_PATH_PID 30 stopped im_daemon_life_cycle +--exec $MYSQL_TEST_DIR/t/wait_for_process.sh $IM_MYSQLD2_PATH_PID 55 stopped im_daemon_life_cycle --exec $MYSQL_TEST_DIR/t/log.sh im_daemon_life_cycle mysqld2: stopped. ########################################################################### diff --git a/mysql-test/t/init_file.test b/mysql-test/t/init_file.test index 31a6ef5a541..7c580afadda 100644 --- a/mysql-test/t/init_file.test +++ b/mysql-test/t/init_file.test @@ -6,6 +6,15 @@ # mysql-test/t/init_file-master.opt for the actual test # +# +# Bug#23240 --init-file statements with NOW() reports '1970-01-01 11:00:00'as the date time +# +INSERT INTO init_file.startup VALUES ( NOW() ); +SELECT * INTO @X FROM init_file.startup limit 0,1; +SELECT * INTO @Y FROM init_file.startup limit 1,1; +SELECT YEAR(@X)-YEAR(@Y); +DROP DATABASE init_file; + --echo ok --echo end of 4.1 tests # diff --git a/mysql-test/t/innodb-replace.test b/mysql-test/t/innodb-replace.test index 51b70f34b65..d44ede65ce8 100644 --- a/mysql-test/t/innodb-replace.test +++ b/mysql-test/t/innodb-replace.test @@ -12,10 +12,10 @@ drop table if exists t1; create table t1 (c1 char(5) unique not null, c2 int, stamp timestamp) engine=innodb; select * from t1; --error 1031 -replace delayed into t1 (c1, c2) values ( "text1","11"),( "text2","12"); +replace delayed into t1 (c1, c2) values ( "text1","11"); select * from t1; --error 1031 -replace delayed into t1 (c1, c2) values ( "text1","12"),( "text2","13"),( "text3","14", "a" ),( "text4","15", "b" ); +replace delayed into t1 (c1, c2) values ( "text1","12"); select * from t1; drop table t1; diff --git a/mysql-test/t/kill_n_check.sh b/mysql-test/t/kill_n_check.sh index 96c402a638c..6f2a0825dcd 100755 --- a/mysql-test/t/kill_n_check.sh +++ b/mysql-test/t/kill_n_check.sh @@ -53,7 +53,7 @@ pid_path="$1" expected_result="$2" total_timeout="$3" test_id="$4" -log_file="$MYSQLTEST_VARDIR/log/$test_id.log" +log_file="$MYSQLTEST_VARDIR/log/$test_id.script.log" log_debug "-- $basename: starting --" log_debug "pid_path: '$pid_path'" diff --git a/mysql-test/t/log.sh b/mysql-test/t/log.sh index 29cf8d3e1a3..33ef6d6701f 100755 --- a/mysql-test/t/log.sh +++ b/mysql-test/t/log.sh @@ -17,7 +17,7 @@ if [ $# -lt 2 ]; then fi test_id="$1" -log_file="$MYSQLTEST_VARDIR/log/$test_id.log" +log_file="$MYSQLTEST_VARDIR/log/$test_id.script.log" shift diff --git a/mysql-test/t/mysqlbinlog.test b/mysql-test/t/mysqlbinlog.test index 6c54e734006..d35bd16c1f9 100644 --- a/mysql-test/t/mysqlbinlog.test +++ b/mysql-test/t/mysqlbinlog.test @@ -190,4 +190,29 @@ drop procedure p1; --exec $MYSQL_BINLOG --help 2>&1 > /dev/null --enable_query_log +# +# Bug#15126 character_set_database is not replicated +# (LOAD DATA INFILE need it) +# + +flush logs; +create table t1 (a varchar(64) character set utf8); +load data infile '../std_data_ln/loaddata6.dat' into table t1; +set character_set_database=koi8r; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +set character_set_database=latin1; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +set character_set_database=koi8r; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +set character_set_database=latin1; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +load data infile '../std_data_ln/loaddata6.dat' into table t1 character set koi8r; +select hex(a) from t1; +drop table t1; +flush logs; +--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR +--exec $MYSQL_BINLOG --short-form $MYSQLTEST_VARDIR/log/master-bin.000011 + # End of 5.0 tests + diff --git a/mysql-test/t/rpl_insert_delayed.test b/mysql-test/t/rpl_insert_delayed.test new file mode 100644 index 00000000000..3f72f3a3625 --- /dev/null +++ b/mysql-test/t/rpl_insert_delayed.test @@ -0,0 +1,67 @@ +--source include/master-slave.inc +--source include/not_embedded.inc +--source include/not_windows.inc + +connection master; + +let $binlog_format_statement=1; + +CREATE TABLE t1 (id INT primary key auto_increment, name VARCHAR(64)); + +sync_slave_with_master; + +# +# BUG#26116 "If multi-row INSERT DELAYED has errors, +# statement-based binlogging breaks"; +# happened only in statement-based binlogging. +# + +connection master; +truncate table t1; +# first scenario: duplicate on first row +insert delayed into t1 values(10, "my name"); +if ($binlog_format_statement) +{ + # statement below will be converted to non-delayed INSERT and so + # will stop at first error, guaranteeing replication. + --error ER_DUP_ENTRY + insert delayed into t1 values(10, "is Bond"), (20, "James Bond"); +} +if (!$binlog_format_statement) +{ + insert delayed into t1 values(10, "is Bond"), (20, "James Bond"); +} +flush table t1; # to wait for INSERT DELAYED to be done +select * from t1; +sync_slave_with_master; +# when bug existed in statement-based binlogging, t1 on slave had +# different content from on master +select * from t1; + +# second scenario: duplicate on second row +connection master; +delete from t1 where id!=10; +if ($binlog_format_statement) +{ + # statement below will be converted to non-delayed INSERT and so + # will be binlogged with its ER_DUP_ENTRY error code, guaranteeing + # replication (slave will hit the same error code and so be fine). + --error ER_DUP_ENTRY + insert delayed into t1 values(20, "is Bond"), (10, "James Bond"); +} +if (!$binlog_format_statement) +{ + insert delayed into t1 values(20, "is Bond"), (10, "James Bond"); +} +flush table t1; # to wait for INSERT DELAYED to be done +select * from t1; +sync_slave_with_master; +# when bug existed in statement-based binlogging, query was binlogged +# with error_code=0 so slave stopped +select * from t1; + +# clean up +connection master; +drop table t1; +sync_slave_with_master; +connection master; diff --git a/mysql-test/t/rpl_insert_id.test b/mysql-test/t/rpl_insert_id.test index 331a913256c..be2948e9678 100644 --- a/mysql-test/t/rpl_insert_id.test +++ b/mysql-test/t/rpl_insert_id.test @@ -246,6 +246,59 @@ connection master; drop table t1; # +# BUG#24432 "INSERT... ON DUPLICATE KEY UPDATE skips auto_increment values" +# + +# testcase with INSERT VALUES +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT, +UNIQUE(b)); +INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10; +SELECT * FROM t1; +sync_slave_with_master; +SELECT * FROM t1; +connection master; +drop table t1; + +# tescase with INSERT SELECT +CREATE TABLE t1 ( + id bigint(20) unsigned NOT NULL auto_increment, + field_1 int(10) unsigned NOT NULL, + field_2 varchar(255) NOT NULL, + field_3 varchar(255) NOT NULL, + PRIMARY KEY (id), + UNIQUE KEY field_1 (field_1, field_2) +); +CREATE TABLE t2 ( + field_a int(10) unsigned NOT NULL, + field_b varchar(255) NOT NULL, + field_c varchar(255) NOT NULL +); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (1, 'a', '1a'); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (2, 'b', '2b'); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (3, 'c', '3c'); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (4, 'd', '4d'); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (5, 'e', '5e'); +# Updating table t1 based on values from table t2 +INSERT INTO t1 (field_1, field_2, field_3) +SELECT t2.field_a, t2.field_b, t2.field_c +FROM t2 +ON DUPLICATE KEY UPDATE +t1.field_3 = t2.field_c; +# Inserting new record into t2 +INSERT INTO t2 (field_a, field_b, field_c) VALUES (6, 'f', '6f'); +# Updating t1 again +INSERT INTO t1 (field_1, field_2, field_3) +SELECT t2.field_a, t2.field_b, t2.field_c +FROM t2 +ON DUPLICATE KEY UPDATE +t1.field_3 = t2.field_c; +SELECT * FROM t1; +sync_slave_with_master; +SELECT * FROM t1; +connection master; +drop table t1, t2; + +# # BUG#20339: stored procedure using LAST_INSERT_ID() does not # replicate statement-based # diff --git a/mysql-test/t/rpl_known_bugs_detection-master.opt b/mysql-test/t/rpl_known_bugs_detection-master.opt new file mode 100644 index 00000000000..d4ba386a1a0 --- /dev/null +++ b/mysql-test/t/rpl_known_bugs_detection-master.opt @@ -0,0 +1 @@ +--loose-debug=d,pretend_version_50034_in_binlog diff --git a/mysql-test/t/rpl_known_bugs_detection.test b/mysql-test/t/rpl_known_bugs_detection.test new file mode 100644 index 00000000000..4719716d4a1 --- /dev/null +++ b/mysql-test/t/rpl_known_bugs_detection.test @@ -0,0 +1,90 @@ +# Test to see if slave can detect certain known bugs present +# on the master, and appropriately decides to stop +# (assuming the bug is fixed in the slave, slave cannot of course +# imitate the bug, so it has to stop). + +source include/have_debug.inc; +source include/master-slave.inc; + +# +# This is to test that slave properly detects if +# master may suffer from: +# BUG#24432 "INSERT... ON DUPLICATE KEY UPDATE skips auto_increment values" +# (i.e. on master, INSERT ON DUPLICATE KEY UPDATE is used and manipulates +# an auto_increment column, and is binlogged statement-based). +# + +# testcase with INSERT VALUES +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT, +UNIQUE(b)); +sync_slave_with_master; +connection master; +INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10; +SELECT * FROM t1; +connection slave; +wait_for_slave_to_stop; +# show the error message +--replace_column 1 # 4 # 7 # 8 # 9 # 23 # 33 # +--query_vertical show slave status; +# show that it was not replicated +SELECT * FROM t1; + +# restart replication for the next testcase +stop slave; +reset slave; +connection master; +reset master; +drop table t1; +connection slave; +start slave; + +# testcase with INSERT SELECT +connection master; +CREATE TABLE t1 ( + id bigint(20) unsigned NOT NULL auto_increment, + field_1 int(10) unsigned NOT NULL, + field_2 varchar(255) NOT NULL, + field_3 varchar(255) NOT NULL, + PRIMARY KEY (id), + UNIQUE KEY field_1 (field_1, field_2) +); +CREATE TABLE t2 ( + field_a int(10) unsigned NOT NULL, + field_b varchar(255) NOT NULL, + field_c varchar(255) NOT NULL +); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (1, 'a', '1a'); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (2, 'b', '2b'); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (3, 'c', '3c'); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (4, 'd', '4d'); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (5, 'e', '5e'); +sync_slave_with_master; +connection master; +# Updating table t1 based on values from table t2 +INSERT INTO t1 (field_1, field_2, field_3) +SELECT t2.field_a, t2.field_b, t2.field_c +FROM t2 +ON DUPLICATE KEY UPDATE +t1.field_3 = t2.field_c; +# Inserting new record into t2 +INSERT INTO t2 (field_a, field_b, field_c) VALUES (6, 'f', '6f'); +# Updating t1 again +INSERT INTO t1 (field_1, field_2, field_3) +SELECT t2.field_a, t2.field_b, t2.field_c +FROM t2 +ON DUPLICATE KEY UPDATE +t1.field_3 = t2.field_c; +SELECT * FROM t1; +connection slave; +wait_for_slave_to_stop; +# show the error message +--replace_column 1 # 4 # 7 # 8 # 9 # 23 # 33 # +--query_vertical show slave status; +# show that it was not replicated +SELECT * FROM t1; +connection master; +drop table t1, t2; +connection slave; +drop table t1, t2; + +# End of 5.0 tests diff --git a/mysql-test/t/rpl_loaddata_charset.test b/mysql-test/t/rpl_loaddata_charset.test new file mode 100644 index 00000000000..7f2389cb9f6 --- /dev/null +++ b/mysql-test/t/rpl_loaddata_charset.test @@ -0,0 +1,33 @@ +# +# Check LOAD DATA + character sets + replication +# +source include/master-slave.inc; + +# +# Bug#15126 character_set_database is not replicated +# (LOAD DATA INFILE need it) +# +connection master; +create table t1 (a varchar(10) character set utf8); +load data infile '../std_data_ln/loaddata6.dat' into table t1; +set @@character_set_database=koi8r; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +set @@character_set_database=DEFAULT; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +set @@character_set_database=koi8r; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +set @@character_set_database=DEFAULT; +load data infile '../std_data_ln/loaddata6.dat' into table t1 character set koi8r; + +select hex(a) from t1; + +save_master_pos; +connection slave; +sync_with_master; + +select hex(a) from t1; +connection master; +drop table t1; +sync_slave_with_master; diff --git a/mysql-test/t/rpl_replicate_do.test b/mysql-test/t/rpl_replicate_do.test index 9dec8c06c79..0e95d71514b 100644 --- a/mysql-test/t/rpl_replicate_do.test +++ b/mysql-test/t/rpl_replicate_do.test @@ -59,3 +59,35 @@ drop table t1; sync_slave_with_master; # End of 4.1 tests + +# +# Bug#24478 DROP TRIGGER is not caught by replicate-*-table filters +# +--echo *** master *** +connection master; +create table t1 (a int, b int); +create trigger trg1 before insert on t1 for each row set new.b=2; +create table t2 (a int, b int); +create trigger trg2 before insert on t2 for each row set new.b=2; +show tables; +show triggers; +sync_slave_with_master; +--echo *** slave *** +connection slave; +show tables; +show triggers; +--echo *** master *** +connection master; +drop trigger trg1; +drop trigger trg2; +show triggers; +sync_slave_with_master; +--echo *** slave *** +connection slave; +show tables; +show triggers; +--echo *** master *** +connection master; +drop table t1; +drop table t2; +sync_slave_with_master; diff --git a/mysql-test/t/rpl_user_variables.test b/mysql-test/t/rpl_user_variables.test index dbe75a15038..b2f4bfbb29d 100644 --- a/mysql-test/t/rpl_user_variables.test +++ b/mysql-test/t/rpl_user_variables.test @@ -57,8 +57,305 @@ insert into t1 select * FROM (select @var1 union select @var2) AS t2; drop table t1; save_master_pos; -connection slave; -sync_with_master; +--echo End of 4.1 tests. + +# BUG#20141 +# The following tests ensure that if user-defined variables are used in SF/Triggers +# that they are replicated correctly. These tests should be run in both SBR and RBR +# modes. + +# This test uses a procedure that inserts data values based on the value of a +# user-defined variable. It also has a trigger that inserts data based on the +# same variable. Successful test runs show that the @var is replicated +# properly and that the procedure and trigger insert the correct data on the +# slave. +# +# The test of stored procedure was included for completeness. Replication of stored +# procedures was not directly affected by BUG#20141. +# +# This test was constructed for BUG#20141 + +--disable_warnings +DROP TABLE IF EXISTS t20; +DROP TABLE IF EXISTS t21; +DROP PROCEDURE IF EXISTS test.insert; +--enable_warnings + +CREATE TABLE t20 (a VARCHAR(20)); +CREATE TABLE t21 (a VARCHAR(20)); +DELIMITER |; + +# Create a procedure that uses the @var for flow control + +CREATE PROCEDURE test.insert() +BEGIN + IF (@VAR) + THEN + INSERT INTO test.t20 VALUES ('SP_TRUE'); + ELSE + INSERT INTO test.t20 VALUES ('SP_FALSE'); + END IF; +END| + +# Create a trigger that uses the @var for flow control + +CREATE TRIGGER test.insert_bi BEFORE INSERT + ON test.t20 FOR EACH ROW + BEGIN + IF (@VAR) + THEN + INSERT INTO test.t21 VALUES ('TRIG_TRUE'); + ELSE + INSERT INTO test.t21 VALUES ('TRIG_FALSE'); + END IF; + END| +DELIMITER ;| + +sync_slave_with_master; +connection master; + +# Set @var and call the procedure, repeat with different values + +SET @VAR=0; +CALL test.insert(); +SET @VAR=1; +CALL test.insert(); + +--echo On master: Check the tables for correct data + +SELECT * FROM t20; +SELECT * FROM t21; + +sync_slave_with_master; + +--echo On slave: Check the tables for correct data and it matches master + +SELECT * FROM t20; +SELECT * FROM t21; +connection master; + +# Cleanup + +DROP TABLE t20; +DROP TABLE t21; +DROP PROCEDURE test.insert; + +# This test uses a stored function that uses user-defined variables to return data +# This test was constructed for BUG#20141 + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP FUNCTION IF EXISTS test.square; +--enable_warnings + +CREATE TABLE t1 (i INT); + +# Create function that returns a value from @var. In this case, the square function + +CREATE FUNCTION test.square() RETURNS INTEGER DETERMINISTIC RETURN (@var * @var); + +sync_slave_with_master; +connection master; + +# Set the @var to different values and insert them into a table + +SET @var = 1; +INSERT INTO t1 VALUES (square()); +SET @var = 2; +INSERT INTO t1 VALUES (square()); +SET @var = 3; +INSERT INTO t1 VALUES (square()); +SET @var = 4; +INSERT INTO t1 VALUES (square()); +SET @var = 5; +INSERT INTO t1 VALUES (square()); + +--echo On master: Retrieve the values from the table + +SELECT * FROM t1; + +sync_slave_with_master; + +--echo On slave: Retrieve the values from the table and verify they are the same as on master + +SELECT * FROM t1; + +connection master; + +# Cleanup + +DROP TABLE t1; +DROP FUNCTION test.square; + +# This test uses stored functions that uses user-defined variables to return data +# based on the use of @vars inside a function body. +# This test was constructed for BUG#14914 + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP FUNCTION IF EXISTS f1; +DROP FUNCTION IF EXISTS f2; +--enable_warnings + +CREATE TABLE t1(a int); +DELIMITER |; + +# Create a function that simply returns the value of an @var. +# Create a function that uses an @var for flow control, creates and uses another +# @var and sets its value to a value based on another @var. + +CREATE FUNCTION f1() returns int deterministic +BEGIN + return @a; +END | + +CREATE FUNCTION f2() returns int deterministic +BEGIN + IF (@b > 0) then + SET @c = (@a + @b); + else + SET @c = (@a - 1); + END if; + return @c; +END | +DELIMITER ;| + +sync_slave_with_master; +connection master; + +# Set an @var to a value and insert data into a table using the first function. +# Set two more @vars to some values and insert data into a table using the second function. + +SET @a=500; +INSERT INTO t1 values(f1()); +SET @b = 125; +SET @c = 1; +INSERT INTO t1 values(f2()); + +sync_slave_with_master; + +--echo On master: Retrieve the values from the table + +SELECT * from t1; + +--echo On slave: Check the tables for correct data and it matches master + +SELECT * from t1; + +connection master; + +# Cleanup + +DROP TABLE t1; +DROP FUNCTION f1; +DROP FUNCTION f2; + +# This test uses a function that changes a user-defined variable in its body. This test +# will ensure the @vars are replicated when needed and not interrupt the normal execution +# of the function on the slave. This also applies to triggers. +# +# This test was constructed for BUG#25167 + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +--enable_warnings +CREATE TABLE t1 (i int); +CREATE TABLE t2 (k int); +DELIMITER |; + +# Create a trigger that inserts data into another table, changes the @var then inserts +# another row with the modified value. + +CREATE trigger t1_bi before INSERT on t1 for each row +BEGIN + INSERT INTO t2 values (@a); + SET @a:=42; + INSERT INTO t2 values (@a); +END | +DELIMITER ;| + +sync_slave_with_master; +connection master; + +# Set the @var to a value then insert data into first table. + +SET @a:=100; +INSERT INTO t1 values (5); + +--echo On master: Check to see that data was inserted correctly in both tables + +SELECT * from t1; +SELECT * from t2; + +sync_slave_with_master; + +--echo On slave: Check the tables for correct data and it matches master + +SELECT * from t1; +SELECT * from t2; + +connection master; + +--echo End of 5.0 tests. + +# Cleanup + +DROP TABLE t1; +DROP TABLE t2; + +# This test uses a stored function that uses user-defined variables to return data +# The test ensures the value of the user-defined variable is replicated correctly +# and in the correct order of assignment. +# This test was constructed for BUG#20141 + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP FUNCTION IF EXISTS f1; +DROP FUNCTION IF EXISTS f2; +--enable_warnings + +CREATE TABLE t1 (i INT); + +# Create two functions. One simply returns the user-defined variable. The other +# returns a value based on the user-defined variable. + +CREATE FUNCTION f1() RETURNS INT RETURN @a; +DELIMITER |; +CREATE FUNCTION f2() RETURNS INT +BEGIN + INSERT INTO t1 VALUES (10 + @a); + RETURN 0; +END| +DELIMITER ;| + +sync_slave_with_master; +connection master; + +# Set the variable and execute the functions. + +SET @a:=123; +SELECT f1(), f2(); + +--echo On master: Check to see that data was inserted correctly + +INSERT INTO t1 VALUES(f1()); +SELECT * FROM t1; + +sync_slave_with_master; + +--echo On slave: Check the table for correct data and it matches master + +SELECT * FROM t1; + +connection master; + +# Cleanup + +DROP FUNCTION f1; +DROP FUNCTION f2; +DROP TABLE t1; + +sync_slave_with_master; stop slave; -# End of 4.1 tests diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test index e77e3df8301..396c1552e37 100644 --- a/mysql-test/t/sp-error.test +++ b/mysql-test/t/sp-error.test @@ -1607,10 +1607,12 @@ create function bug11555_1() returns int return (select max(i) from t1); create function bug11555_2() returns int return bug11555_1(); # It is OK to report name of implicitly used table which is missing # when we create view. ---error ER_NO_SUCH_TABLE +# For stored functions however, because of exceptions handlers, there is +# no easy way to find out if a missing table makes the view invalid. create view v1 as select bug11555_1(); ---error ER_NO_SUCH_TABLE +drop view v1; create view v2 as select bug11555_2(); +drop view v2; # But we should hide name of missing implicitly used table when we use view create table t1 (i int); create view v1 as select bug11555_1(); @@ -1625,9 +1627,8 @@ select * from v2; select * from v3; # Note that creation of view which depends on broken view is yet # another form of view usage. ---error ER_VIEW_INVALID create view v4 as select * from v1; -drop view v1, v2, v3; +drop view v1, v2, v3, v4; # We also should hide details about broken triggers which are # invoked for view. drop function bug11555_1; @@ -1637,12 +1638,14 @@ create table t2 (i int); create trigger t1_ai after insert on t1 for each row insert into t2 values (new.i); create view v1 as select * from t1; drop table t2; ---error ER_VIEW_INVALID +# Limitation, the desired error is ER_VIEW_INVALID +--error ER_TABLE_NOT_LOCKED insert into v1 values (1); drop trigger t1_ai; create function bug11555_1() returns int return (select max(i) from t2); create trigger t1_ai after insert on t1 for each row set @a:=bug11555_1(); ---error ER_VIEW_INVALID +# Limitation, the desired error is ER_VIEW_INVALID +--error ER_TABLE_NOT_LOCKED insert into v1 values (2); drop function bug11555_1; drop table t1; @@ -1839,6 +1842,184 @@ call bug24491(); drop procedure bug24491; drop tables t1; +# +# BUG#18914: Calling certain SPs from triggers fail +# +# Failing to call a procedure that does implicit commit from a trigger +# is a correct behaviour, however the error message was misleading. +# +# DROP TABLE IF EXISTS is also fixed to give correct error instead of +# "Table doesn't exist". +# +--disable_warnings +DROP FUNCTION IF EXISTS bug18914_f1; +DROP FUNCTION IF EXISTS bug18914_f2; +DROP PROCEDURE IF EXISTS bug18914_p1; +DROP PROCEDURE IF EXISTS bug18914_p2; +DROP TABLE IF EXISTS t1, t2; +--enable_warnings + +CREATE TABLE t1 (i INT); + +CREATE PROCEDURE bug18914_p1() CREATE TABLE t2 (i INT); +CREATE PROCEDURE bug18914_p2() DROP TABLE IF EXISTS no_such_table; + +delimiter |; +CREATE FUNCTION bug18914_f1() RETURNS INT +BEGIN + CALL bug18914_p1(); + RETURN 1; +END | + +CREATE FUNCTION bug18914_f2() RETURNS INT +BEGIN + CALL bug18914_p2(); + RETURN 1; +END | +delimiter ;| + +CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW + CALL bug18914_p1(); + +--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +INSERT INTO t1 VALUES (1); + +--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +SELECT bug18914_f1(); + +--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +SELECT bug18914_f2(); + +--error ER_NO_SUCH_TABLE +SELECT * FROM t2; + +DROP FUNCTION bug18914_f1; +DROP FUNCTION bug18914_f2; +DROP PROCEDURE bug18914_p1; +DROP PROCEDURE bug18914_p2; +DROP TABLE t1; + +# +# Bug#20713 (Functions will not not continue for SQLSTATE VALUE '42S02') +# + +--disable_warnings +drop table if exists bogus_table_20713; +drop function if exists func_20713_a; +drop function if exists func_20713_b; +--enable_warnings + +create table bogus_table_20713( id int(10) not null primary key); +insert into bogus_table_20713 values (1), (2), (3); + +delimiter //; + +create function func_20713_a() returns int(11) +begin + declare id int; + + declare continue handler for sqlexception set id=null; + + set @in_func := 1; + set id = (select id from bogus_table_20713 where id = 3); + set @in_func := 2; + + return id; +end// + +create function func_20713_b() returns int(11) +begin + declare id int; + + declare continue handler for sqlstate value '42S02' set id=null; + + set @in_func := 1; + set id = (select id from bogus_table_20713 where id = 3); + set @in_func := 2; + + return id; +end// + +delimiter ;// + +set @in_func := 0; +select func_20713_a(); +select @in_func; + +set @in_func := 0; +select func_20713_b(); +select @in_func; + +drop table bogus_table_20713; + +set @in_func := 0; +select func_20713_a(); +select @in_func; + +set @in_func := 0; +select func_20713_b(); +select @in_func; + +drop function if exists func_20713_a; +drop function if exists func_20713_b; + +# +# Bug#25345 (Cursors from Functions) +# + +--disable_warnings +drop table if exists table_25345_a; +drop table if exists table_25345_b; +drop procedure if exists proc_25345; +drop function if exists func_25345; +drop function if exists func_25345_b; +--enable_warnings + +create table table_25345_a (a int); +create table table_25345_b (b int); + +delimiter ||; + +create procedure proc_25345() +begin + declare c1 cursor for select a from table_25345_a; + declare c2 cursor for select b from table_25345_b; + + select 1 as result; +end || + +create function func_25345() returns int(11) +begin + call proc_25345(); + return 1; +end || + +create function func_25345_b() returns int(11) +begin + declare c1 cursor for select a from table_25345_a; + declare c2 cursor for select b from table_25345_b; + + return 1; +end || + +delimiter ;|| + +call proc_25345(); +--error ER_SP_NO_RETSET +select func_25345(); +select func_25345_b(); + +drop table table_25345_a; + +call proc_25345(); +--error ER_SP_NO_RETSET +select func_25345(); +select func_25345_b(); + +drop table table_25345_b; +drop procedure proc_25345; +drop function func_25345; +drop function func_25345_b; # # BUG#NNNN: New bug synopsis diff --git a/mysql-test/t/sp-vars.test b/mysql-test/t/sp-vars.test index 7cf92dc5d0d..0014dc1f6af 100644 --- a/mysql-test/t/sp-vars.test +++ b/mysql-test/t/sp-vars.test @@ -500,8 +500,6 @@ DROP PROCEDURE p1; # # Test case for BUG#12976: Boolean values reversed in stored procedures? # -# TODO: test case failed. -# ########################################################################### --echo @@ -566,13 +564,8 @@ BEGIN END| delimiter ;| -# The expected and correct result. - call p1(); -# The wrong result. Note that only hex(vb) works, but is printed with two -# digits for some reason in this case. - call p2(); # @@ -583,6 +576,64 @@ DROP TABLE t1; DROP PROCEDURE p1; DROP PROCEDURE p2; +# Additional tests for Bug#12976 + +--disable_warnings +DROP TABLE IF EXISTS table_12976_a; +DROP TABLE IF EXISTS table_12976_b; +DROP PROCEDURE IF EXISTS proc_12976_a; +DROP PROCEDURE IF EXISTS proc_12976_b; +--enable_warnings + +CREATE TABLE table_12976_a (val bit(1)); + +CREATE TABLE table_12976_b( + appname varchar(15), + emailperm bit not null default 1, + phoneperm bit not null default 0); + +insert into table_12976_b values ('A', b'1', b'1'), ('B', b'0', b'0'); + +delimiter ||; +CREATE PROCEDURE proc_12976_a() +BEGIN + declare localvar bit(1); + SELECT val INTO localvar FROM table_12976_a; + SELECT coalesce(localvar, 1)+1, coalesce(val, 1)+1 FROM table_12976_a; +END|| + +CREATE PROCEDURE proc_12976_b( + name varchar(15), + out ep bit, + out msg varchar(10)) +BEGIN + SELECT emailperm into ep FROM table_12976_b where (appname = name); + IF ep is true THEN + SET msg = 'True'; + ELSE + SET msg = 'False'; + END IF; +END|| + +delimiter ;|| + +INSERT table_12976_a VALUES (0); +call proc_12976_a(); +UPDATE table_12976_a set val=1; +call proc_12976_a(); + +call proc_12976_b('A', @ep, @msg); +select @ep, @msg; + +call proc_12976_b('B', @ep, @msg); +select @ep, @msg; + +DROP TABLE table_12976_a; +DROP TABLE table_12976_b; +DROP PROCEDURE proc_12976_a; +DROP PROCEDURE proc_12976_b; + + ########################################################################### # # Test case for BUG#9572: Stored procedures: variable type declarations diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index cfa4937e050..157d0b7c118 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -1368,7 +1368,7 @@ end| select f11()| --error ER_CANT_REOPEN_TABLE select f11() from t1| -# We don't handle temporary tables used by nested functions well +# Test that using a single table instance at a time works create function f12_1() returns int begin drop temporary table if exists t3; @@ -1378,11 +1378,9 @@ begin end| create function f12_2() returns int return (select count(*) from t3)| -# We need clean start to get error + drop temporary table t3| ---error ER_NO_SUCH_TABLE select f12_1()| ---error ER_NO_SUCH_TABLE select f12_1() from t1 limit 1| # Cleanup @@ -6715,6 +6713,53 @@ DROP PROCEDURE bug24117| DROP TABLE t3| # +# Bug#8407(Stored functions/triggers ignore exception handler) +# + +--disable_warnings +drop function if exists func_8407_a| +drop function if exists func_8407_b| +--enable_warnings + +create function func_8407_a() returns int +begin + declare x int; + + declare continue handler for sqlexception + begin + end; + + select 1 from no_such_view limit 1 into x; + + return x; +end| + +create function func_8407_b() returns int +begin + declare x int default 0; + + declare continue handler for sqlstate '42S02' + begin + set x:= x+1000; + end; + + case (select 1 from no_such_view limit 1) + when 1 then set x:= x+1; + when 2 then set x:= x+2; + else set x:= x+100; + end case; + set x:=x + 500; + + return x; +end| + +select func_8407_a()| +select func_8407_b()| + +drop function func_8407_a| +drop function func_8407_b| + +# # NOTE: The delimiter is `|`, and not `;`. It is changed to `;` # at the end of the file! # diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 11cec291d8c..1deda2a4382 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -2002,7 +2002,7 @@ EXPLAIN SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL; DROP TABLE t1; # -# Bug 24653: sorting by expressions containing subselects +# Bug 24653: sorting by expressions containing subselects # that return more than one row # @@ -2014,12 +2014,12 @@ INSERT INTO t2 VALUES (2,1), (1,3), (2,1), (4,4), (2,2), (1,4); SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 ); ---error 1242 -SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1); -SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2), a; ---error 1242 +--error 1242 +SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1); +SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2), a; +--error 1242 SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1), a; - + SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 2); --error 1242 SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 1); @@ -2036,28 +2036,28 @@ SELECT a FROM t1 GROUP BY a SELECT a FROM t1 GROUP BY a HAVING IFNULL((SELECT b FROM t2 WHERE b > 4), (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3; ---error 1242 +--error 1242 SELECT a FROM t1 GROUP BY a HAVING IFNULL((SELECT b FROM t2 WHERE b > 4), (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)) > 3; -SELECT a FROM t1 +SELECT a FROM t1 ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2), (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)); --error 1242 -SELECT a FROM t1 +SELECT a FROM t1 ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1), (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)); -SELECT a FROM t1 +SELECT a FROM t1 ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4), (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)); --error 1242 -SELECT a FROM t1 +SELECT a FROM t1 ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4), (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)); -DROP TABLE t1,t2; +DROP TABLE t1,t2; # End of 4.1 tests @@ -2571,7 +2571,7 @@ DROP TABLE t1,t2; # # Bug #25219: EXIST subquery with UNION over a mix of # correlated and uncorrelated selects -# +# CREATE TABLE t1 (id char(4) PRIMARY KEY, c int); CREATE TABLE t2 (c int); @@ -2579,28 +2579,29 @@ CREATE TABLE t2 (c int); INSERT INTO t1 VALUES ('aa', 1); INSERT INTO t2 VALUES (1); -SELECT * FROM t1 +SELECT * FROM t1 WHERE EXISTS (SELECT c FROM t2 WHERE c=1 - UNION + UNION SELECT c from t2 WHERE c=t1.c); INSERT INTO t1 VALUES ('bb', 2), ('cc', 3), ('dd',1); -SELECT * FROM t1 +SELECT * FROM t1 WHERE EXISTS (SELECT c FROM t2 WHERE c=1 - UNION + UNION SELECT c from t2 WHERE c=t1.c); INSERT INTO t2 VALUES (2); CREATE TABLE t3 (c int); INSERT INTO t3 VALUES (1); -SELECT * FROM t1 +SELECT * FROM t1 WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1 - UNION + UNION SELECT c from t2 WHERE c=t1.c); -DROP TABLE t1,t2,t3; +DROP TABLE t1,t2,t3; + # # Bug#23800: Outer fields in correlated subqueries is used in a temporary # table created for sorting. @@ -2628,3 +2629,103 @@ SELECT a, MAX(b), (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b)) AS test FROM t1 GROUP BY a; DROP TABLE t1; + +# +# Bug#21904 (parser problem when using IN with a double "(())") +# + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +DROP TABLE IF EXISTS t1xt2; +--enable_warnings + +CREATE TABLE t1 ( + id_1 int(5) NOT NULL, + t varchar(4) DEFAULT NULL +); + +CREATE TABLE t2 ( + id_2 int(5) NOT NULL, + t varchar(4) DEFAULT NULL +); + +CREATE TABLE t1xt2 ( + id_1 int(5) NOT NULL, + id_2 int(5) NOT NULL +); + +INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'); + +INSERT INTO t2 VALUES (2, 'bb'), (3, 'cc'), (4, 'dd'), (12, 'aa'); + +INSERT INTO t1xt2 VALUES (2, 2), (3, 3), (4, 4); + +# subselect returns 0 rows + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))); + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)))); + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1))); + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1)))); + +insert INTO t1xt2 VALUES (1, 12); + +# subselect returns 1 row + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))); + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)))); + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))); + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)))); + +insert INTO t1xt2 VALUES (2, 12); + +# subselect returns more than 1 row + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))); + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)))); + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))); + +SELECT DISTINCT t1.id_1 FROM t1 WHERE +(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)))); + +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t1xt2; + diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test index 825f1fec6b9..5c95004c901 100644 --- a/mysql-test/t/trigger.test +++ b/mysql-test/t/trigger.test @@ -1625,4 +1625,78 @@ SELECT fubar_id FROM t2; DROP TABLE t1,t2; +# +# Bug#21285 (Incorrect message error deleting records in a table with a +# trigger for inserting) +# + +--disable_warnings +DROP TABLE IF EXISTS bug21825_A; +DROP TABLE IF EXISTS bug21825_B; +--enable_warnings + +CREATE TABLE bug21825_A (id int(10)); +CREATE TABLE bug21825_B (id int(10)); + +delimiter //; + +CREATE TRIGGER trgA AFTER INSERT ON bug21825_A +FOR EACH ROW +BEGIN + INSERT INTO bug21825_B (id) values (1); +END// +delimiter ;// + +INSERT INTO bug21825_A (id) VALUES (10); +INSERT INTO bug21825_A (id) VALUES (20); + +DROP TABLE bug21825_B; + +# Must pass, the missing table in the insert trigger should not matter. +DELETE FROM bug21825_A WHERE id = 20; + +DROP TABLE bug21825_A; + +# +# Bug#22580 (DROP TABLE in nested stored procedure causes strange dependancy +# error) +# + +--disable_warnings +DROP TABLE IF EXISTS bug22580_t1; +DROP PROCEDURE IF EXISTS bug22580_proc_1; +DROP PROCEDURE IF EXISTS bug22580_proc_2; +--enable_warnings + +CREATE TABLE bug22580_t1 (a INT, b INT); + +DELIMITER ||; + +CREATE PROCEDURE bug22580_proc_2() +BEGIN + DROP TABLE IF EXISTS bug22580_tmp; + CREATE TEMPORARY TABLE bug22580_tmp (a INT); + DROP TABLE bug22580_tmp; +END|| + +CREATE PROCEDURE bug22580_proc_1() +BEGIN + CALL bug22580_proc_2(); +END|| + +CREATE TRIGGER t1bu BEFORE UPDATE ON bug22580_t1 +FOR EACH ROW +BEGIN + CALL bug22580_proc_1(); +END|| + +DELIMITER ;|| + +# Must pass, the actions of the update trigger should not matter +INSERT INTO bug22580_t1 VALUES (1,1); + +DROP TABLE bug22580_t1; +DROP PROCEDURE bug22580_proc_1; +DROP PROCEDURE bug22580_proc_2; + --echo End of 5.0 tests diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 33e381af476..90a3c6a1e2d 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -748,12 +748,14 @@ drop view v1; # # VIEWs with national characters # -create table tü (cü char); -create view vü as select cü from tü; -insert into vü values ('ü'); -select * from vü; -drop view vü; -drop table tü; +set names utf8; +create table tü (cü char); +create view vü as select cü from tü; +insert into vü values ('ü'); +select * from vü; +drop view vü; +drop table tü; +set names latin1; # # problem with used_tables() of outer reference resolved in VIEW @@ -2991,7 +2993,7 @@ DROP VIEW v1; # CREATE TABLE t1 (mydate DATETIME); -INSERT INTO t1 VALUES +INSERT INTO t1 VALUES ('2007-01-01'), ('2007-01-02'), ('2007-01-30'), ('2007-01-31'); CREATE VIEW v1 AS SELECT mydate from t1; @@ -3039,7 +3041,7 @@ drop view v1; drop table t1; # -# Bug#26209: queries with GROUP BY and ORDER BY using views +# Bug#26209: queries with GROUP BY and ORDER BY using views # CREATE TABLE t1 ( @@ -3058,4 +3060,107 @@ SELECT code, COUNT(DISTINCT country) FROM v1 GROUP BY code ORDER BY MAX(id); DROP VIEW v1; DROP TABLE t1; +# +# BUG#25897: Some queries are no longer possible after a CREATE VIEW +# fails +# +--disable_warnings +DROP VIEW IF EXISTS v1; +--enable_warnings + +let $query = SELECT * FROM (SELECT 1) AS t; + +eval $query; +--error ER_VIEW_SELECT_DERIVED +eval CREATE VIEW v1 AS $query; +--echo # Previously the following would fail. +eval $query; + +# +# Bug#24532: The return data type of IS TRUE is different from similar +# operations +# + +--disable_warnings +drop view if exists view_24532_a; +drop view if exists view_24532_b; +drop table if exists table_24532; +--enable_warnings + +create table table_24532 ( + a int, + b bigint, + c int(4), + d bigint(48) +); + +create view view_24532_a as +select + a IS TRUE, + a IS NOT TRUE, + a IS FALSE, + a IS NOT FALSE, + a IS UNKNOWN, + a IS NOT UNKNOWN, + a is NULL, + a IS NOT NULL, + ISNULL(a), + b IS TRUE, + b IS NOT TRUE, + b IS FALSE, + b IS NOT FALSE, + b IS UNKNOWN, + b IS NOT UNKNOWN, + b is NULL, + b IS NOT NULL, + ISNULL(b), + c IS TRUE, + c IS NOT TRUE, + c IS FALSE, + c IS NOT FALSE, + c IS UNKNOWN, + c IS NOT UNKNOWN, + c is NULL, + c IS NOT NULL, + ISNULL(c), + d IS TRUE, + d IS NOT TRUE, + d IS FALSE, + d IS NOT FALSE, + d IS UNKNOWN, + d IS NOT UNKNOWN, + d is NULL, + d IS NOT NULL, + ISNULL(d) +from table_24532; + +describe view_24532_a; + +create view view_24532_b as +select + a IS TRUE, + if(ifnull(a, 0), 1, 0) as old_istrue, + a IS NOT TRUE, + if(ifnull(a, 0), 0, 1) as old_isnottrue, + a IS FALSE, + if(ifnull(a, 1), 0, 1) as old_isfalse, + a IS NOT FALSE, + if(ifnull(a, 1), 1, 0) as old_isnotfalse +from table_24532; + +describe view_24532_b; + +show create view view_24532_b; + +insert into table_24532 values (0, 0, 0, 0); +select * from view_24532_b; +update table_24532 set a=1; +select * from view_24532_b; +update table_24532 set a=NULL; +select * from view_24532_b; + +drop view view_24532_a; +drop view view_24532_b; +drop table table_24532; + --echo End of 5.0 tests. diff --git a/mysql-test/t/wait_for_process.sh b/mysql-test/t/wait_for_process.sh index 4c2d89cfea6..2143ab2002f 100755 --- a/mysql-test/t/wait_for_process.sh +++ b/mysql-test/t/wait_for_process.sh @@ -63,7 +63,7 @@ pid_path="$1" total_attempts="$2" event="$3" test_id="$4" -log_file="$MYSQLTEST_VARDIR/log/$test_id.log" +log_file="$MYSQLTEST_VARDIR/log/$test_id.script.log" log_debug "-- $basename: starting --" log_debug "pid_path: '$pid_path'" diff --git a/mysql-test/t/wait_for_socket.sh b/mysql-test/t/wait_for_socket.sh index 1bce74dfd3a..8c17c8ac0ac 100755 --- a/mysql-test/t/wait_for_socket.sh +++ b/mysql-test/t/wait_for_socket.sh @@ -30,7 +30,7 @@ password="$4" db="$5" total_timeout="$6" test_id="$7" -log_file="$MYSQLTEST_VARDIR/log/$test_id.log" +log_file="$MYSQLTEST_VARDIR/log/$test_id.script.log" log_debug "-- $basename: starting --" log_debug "client_exe: '$client_exe'" diff --git a/mysql-test/valgrind.supp b/mysql-test/valgrind.supp index f7eb9baa4c7..e71f4541cfd 100644 --- a/mysql-test/valgrind.supp +++ b/mysql-test/valgrind.supp @@ -346,3 +346,20 @@ fun:_ZN19TransporterRegistry11performSendEv fun:_ZN19TransporterRegistry14forceSendCheckEi } + +# +# BUG#25463: Probable cause - slave thread doesn't have time to terminate properly +# because rest of the server shuts down before that. The server shutdown code has +# been fixed in 5.1 and there this leak should not happen. Suppression has been +# approved by Monty. +# +# Important: do not propagate this rule to 5.1 tree! +# + +{ + Slave thread shutdown (BUG#25463) + Memcheck:Leak + fun:calloc + fun:my_thread_init + fun:handle_slave_io +} |