diff options
Diffstat (limited to 'mysql-test')
55 files changed, 2169 insertions, 97 deletions
diff --git a/mysql-test/include/query_cache.inc b/mysql-test/include/query_cache.inc index 0cc1ebb21d6..29a82f112c3 100644 --- a/mysql-test/include/query_cache.inc +++ b/mysql-test/include/query_cache.inc @@ -4,6 +4,9 @@ # $engine_type -- storage engine to be tested # $test_foreign_keys -- 0, skip foreign key tests # -- 1, do not skip foreign key tests +# $partitions_a -- partition by column 'a' +# $partitions_id -- partition by column 'id' +# $partitions_s1 -- partition by column 's1' # have to be set before sourcing this script. # # Last update: @@ -19,47 +22,61 @@ eval SET SESSION STORAGE_ENGINE = $engine_type; drop table if exists t1,t2,t3; --enable_warnings +set @save_query_cache_size = @@global.query_cache_size; +set GLOBAL query_cache_size = 1355776; + # # Without auto_commit. # flush status; set autocommit=0; -create table t1 (a int not null); +eval create table t1 (a int not null)$partitions_a; insert into t1 values (1),(2),(3); +--sorted_result select * from t1; show status like "Qcache_queries_in_cache"; drop table t1; commit; set autocommit=1; begin; -create table t1 (a int not null); +eval create table t1 (a int not null)$partitions_a; insert into t1 values (1),(2),(3); +--sorted_result select * from t1; show status like "Qcache_queries_in_cache"; drop table t1; commit; -create table t1 (a int not null); -create table t2 (a int not null); -create table t3 (a int not null); +eval create table t1 (a int not null)$partitions_a; +eval create table t2 (a int not null)$partitions_a; +eval create table t3 (a int not null)$partitions_a; insert into t1 values (1),(2); insert into t2 values (1),(2); insert into t3 values (1),(2); +--sorted_result select * from t1; +--sorted_result select * from t2; +--sorted_result select * from t3; show status like "Qcache_queries_in_cache"; show status like "Qcache_hits"; begin; +--sorted_result select * from t1; +--sorted_result select * from t2; +--sorted_result select * from t3; show status like "Qcache_queries_in_cache"; show status like "Qcache_hits"; insert into t1 values (3); insert into t2 values (3); insert into t1 values (4); +--sorted_result select * from t1; +--sorted_result select * from t2; +--sorted_result select * from t3; show status like "Qcache_queries_in_cache"; show status like "Qcache_hits"; @@ -67,7 +84,7 @@ commit; show status like "Qcache_queries_in_cache"; drop table t3,t2,t1; -CREATE TABLE t1 (id int(11) NOT NULL auto_increment, PRIMARY KEY (id)); +eval CREATE TABLE t1 (id int(11) NOT NULL auto_increment, PRIMARY KEY (id))$partitions_id; select count(*) from t1; insert into t1 (id) values (0); select count(*) from t1; @@ -78,8 +95,6 @@ if ($test_foreign_keys) # # one statement roll back inside transation # -let $save_query_cache_size=`select @@global.query_cache_size`; -set GLOBAL query_cache_size=1355776; CREATE TABLE t1 ( id int(10) NOT NULL auto_increment, a varchar(25) default NULL, PRIMARY KEY (id), UNIQUE KEY a (a)); CREATE TABLE t2 ( id int(10) NOT NULL auto_increment, b varchar(25) default NULL, PRIMARY KEY (id), UNIQUE KEY b (b)); CREATE TABLE t3 ( id int(10) NOT NULL auto_increment, t1_id int(10) NOT NULL default '0', t2_id int(10) NOT NULL default '0', state int(11) default NULL, PRIMARY KEY (id), UNIQUE KEY t1_id (t1_id,t2_id), KEY t2_id (t2_id,t1_id), CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`), CONSTRAINT `t3_ibfk_2` FOREIGN KEY (`t2_id`) REFERENCES `t2` (`id`)); @@ -95,9 +110,6 @@ insert into t3 VALUES ( NULL, 1, 1, 2 ); commit; select t1.* from t1, t2, t3 where t3.state & 1 = 0 and t3.t1_id = t1.id and t3.t2_id = t2.id and t1.id = 1 order by t1.a asc; drop table t3,t2,t1; ---disable_query_log -eval set GLOBAL query_cache_size=$save_query_cache_size; ---enable_query_log } # @@ -118,7 +130,7 @@ SET GLOBAL query_cache_size = 204800; flush status; SET @@autocommit=1; eval SET SESSION STORAGE_ENGINE = $engine_type; -CREATE TABLE t2 (s1 int, s2 varchar(1000), key(s1)); +eval CREATE TABLE t2 (s1 int, s2 varchar(1000), key(s1))$partitions_s1; INSERT INTO t2 VALUES (1,repeat('a',10)),(2,repeat('a',10)),(3,repeat('a',10)),(4,repeat('a',10)); COMMIT; START TRANSACTION; @@ -176,8 +188,8 @@ show status like "Qcache_queries_in_cache"; show status like "Qcache_hits"; # Final cleanup -eval set GLOBAL query_cache_size=$save_query_cache_size; disconnect connection1; --source include/wait_until_disconnected.inc connection default; +set @@global.query_cache_size = @save_query_cache_size; drop table t2; diff --git a/mysql-test/include/show_slave_hosts.inc b/mysql-test/include/show_slave_hosts.inc new file mode 100644 index 00000000000..e27c1bfb09c --- /dev/null +++ b/mysql-test/include/show_slave_hosts.inc @@ -0,0 +1,3 @@ +--replace_result $SLAVE_MYPORT SLAVE_PORT $DEFAULT_MASTER_PORT DEFAULT_PORT +--replace_regex /[0-9, a-f]{8}-[0-9, a-f]{4}-[0-9, a-f]{4}-[0-9, a-f]{4}-[0-9, a-f]{12}/SLAVE_UUID/ +SHOW SLAVE HOSTS; diff --git a/mysql-test/r/cache_innodb.result b/mysql-test/r/cache_innodb.result index 293d7a3f412..85eef0b3418 100644 --- a/mysql-test/r/cache_innodb.result +++ b/mysql-test/r/cache_innodb.result @@ -1,5 +1,7 @@ SET SESSION STORAGE_ENGINE = InnoDB; drop table if exists t1,t2,t3; +set @save_query_cache_size = @@global.query_cache_size; +set GLOBAL query_cache_size = 1355776; flush status; set autocommit=0; create table t1 (a int not null); @@ -100,7 +102,7 @@ show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 2 drop table t3,t2,t1; -CREATE TABLE t1 (id int(11) NOT NULL auto_increment, PRIMARY KEY (id)); +CREATE TABLE t1 (id int(11) NOT NULL auto_increment, PRIMARY KEY (id)); select count(*) from t1; count(*) 0 @@ -109,7 +111,6 @@ select count(*) from t1; count(*) 1 drop table t1; -set GLOBAL query_cache_size=1355776; CREATE TABLE t1 ( id int(10) NOT NULL auto_increment, a varchar(25) default NULL, PRIMARY KEY (id), UNIQUE KEY a (a)); CREATE TABLE t2 ( id int(10) NOT NULL auto_increment, b varchar(25) default NULL, PRIMARY KEY (id), UNIQUE KEY b (b)); CREATE TABLE t3 ( id int(10) NOT NULL auto_increment, t1_id int(10) NOT NULL default '0', t2_id int(10) NOT NULL default '0', state int(11) default NULL, PRIMARY KEY (id), UNIQUE KEY t1_id (t1_id,t2_id), KEY t2_id (t2_id,t1_id), CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`), CONSTRAINT `t3_ibfk_2` FOREIGN KEY (`t2_id`) REFERENCES `t2` (`id`)); @@ -218,7 +219,7 @@ Qcache_queries_in_cache 1 show status like "Qcache_hits"; Variable_name Value Qcache_hits 1 -set GLOBAL query_cache_size=1048576; +set @@global.query_cache_size = @save_query_cache_size; drop table t2; CREATE TABLE t1 (a INT) ENGINE=InnoDB; BEGIN; diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result index b482e3d542c..501b9cb611b 100644 --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@ -4157,5 +4157,16 @@ DROP TABLE t1; SET sql_mode=default; SET NAMES latin1; # +# Bug #13832953 MY_STRNXFRM_UNICODE: ASSERTION `SRC' FAILED +# +CREATE TABLE t1 (c1 SET('','') CHARACTER SET ucs2); +Warnings: +Note 1291 Column 'c1' has duplicated value '' in SET +INSERT INTO t1 VALUES (''); +SELECT COALESCE(c1) FROM t1 ORDER BY 1; +COALESCE(c1) + +DROP TABLE t1; +# # End of 5.5 tests # diff --git a/mysql-test/r/filesort_debug.result b/mysql-test/r/filesort_debug.result index 218af99f5d0..6d6eb817259 100644 --- a/mysql-test/r/filesort_debug.result +++ b/mysql-test/r/filesort_debug.result @@ -31,3 +31,46 @@ SET DEBUG_SYNC='now SIGNAL filesort_killed'; # connection default SET DEBUG_SYNC= "RESET"; DROP TABLE t1; +# +# Bug#13832772 ASSERTION `THD->IS_ERROR() || KILL_ERRNO' +# FAILED IN FILESORT/MYSQL_DELETE +# +CREATE TABLE t1 ( +c1 BLOB, +c2 TEXT, +c3 TEXT, +c4 TEXT, +c5 TEXT, +c6 TEXT, +c7 TEXT, +c8 BLOB, +c9 TEXT, +c19 TEXT, +pk INT, +c20 TEXT, +c21 BLOB, +c22 TEXT, +c23 TEXT, +c24 TEXT, +c25 TEXT, +c26 BLOB, +c27 TEXT, +c28 TEXT, +primary key (pk) +); +CALL mtr.add_suppression("Out of sort memory"); +DELETE IGNORE FROM t1 ORDER BY c26,c7,c23,c4,c25,c5,c20, +c19,c21,c8,c1,c27,c28,c3,c9,c22,c24,c6,c2,pk LIMIT 2; +ERROR HY001: Out of sort memory, consider increasing server sort buffer size +SHOW WARNINGS; +Level Code Message +Error 1038 Out of sort memory, consider increasing server sort buffer size +Error 1028 Sort aborted: Out of sort memory, consider increasing server sort buffer size +DELETE FROM t1 ORDER BY c26,c7,c23,c4,c25,c5,c20, +c19,c21,c8,c1,c27,c28,c3,c9,c22,c24,c6,c2,pk LIMIT 2; +ERROR HY001: Out of sort memory, consider increasing server sort buffer size +SHOW WARNINGS; +Level Code Message +Error 1038 Out of sort memory, consider increasing server sort buffer size +Error 1028 Sort aborted: Out of sort memory, consider increasing server sort buffer size +DROP TABLE t1; diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index 2c20b77a7c4..0a87f57726d 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -778,4 +778,14 @@ SELECT 1 IN (YEAR(FROM_UNIXTIME(NULL)) ,1); 1 IN (YEAR(FROM_UNIXTIME(NULL)) ,1) 1 # +# +# Bug#13012483: EXPLAIN EXTENDED, PREPARED STATEMENT, CRASH IN CHECK_SIMPLE_EQUALITY +# +CREATE TABLE t1 (a INT); +PREPARE s FROM "SELECT 1 FROM t1 WHERE 1 < ALL (SELECT @:= (1 IN (SELECT 1 FROM t1)) FROM t1)"; +EXECUTE s; +1 +DROP TABLE t1; +# End of test BUG#13012483 +# End of 5.1 tests diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index f9b64e88f68..fcadee7aced 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -1036,7 +1036,7 @@ replicate-events-marked-for-skip replicate replicate-same-server-id FALSE report-host (No default value) report-password (No default value) -report-port 3306 +report-port 0 report-user (No default value) rowid-merge-buff-size 8388608 rpl-recovery-rank 0 diff --git a/mysql-test/r/partition_cache.result b/mysql-test/r/partition_cache.result new file mode 100644 index 00000000000..ac2da9bb78a --- /dev/null +++ b/mysql-test/r/partition_cache.result @@ -0,0 +1,205 @@ +SET SESSION STORAGE_ENGINE = InnoDB; +drop table if exists t1,t2,t3; +set @save_query_cache_size = @@global.query_cache_size; +set GLOBAL query_cache_size = 1355776; +flush status; +set autocommit=0; +create table t1 (a int not null) PARTITION BY KEY (a) PARTITIONS 3; +insert into t1 values (1),(2),(3); +select * from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +drop table t1; +commit; +set autocommit=1; +begin; +create table t1 (a int not null) PARTITION BY KEY (a) PARTITIONS 3; +insert into t1 values (1),(2),(3); +select * from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +drop table t1; +commit; +create table t1 (a int not null) PARTITION BY KEY (a) PARTITIONS 3; +create table t2 (a int not null) PARTITION BY KEY (a) PARTITIONS 3; +create table t3 (a int not null) PARTITION BY KEY (a) PARTITIONS 3; +insert into t1 values (1),(2); +insert into t2 values (1),(2); +insert into t3 values (1),(2); +select * from t1; +a +1 +2 +select * from t2; +a +1 +2 +select * from t3; +a +1 +2 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 0 +begin; +select * from t1; +a +1 +2 +select * from t2; +a +1 +2 +select * from t3; +a +1 +2 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 0 +insert into t1 values (3); +insert into t2 values (3); +insert into t1 values (4); +select * from t1; +a +1 +2 +3 +4 +select * from t2; +a +1 +2 +3 +select * from t3; +a +1 +2 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 0 +commit; +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +drop table t3,t2,t1; +CREATE TABLE t1 (id int(11) NOT NULL auto_increment, PRIMARY KEY (id)) PARTITION BY HASH (id) PARTITIONS 3; +select count(*) from t1; +count(*) +0 +insert into t1 (id) values (0); +select count(*) from t1; +count(*) +1 +drop table t1; +SET SESSION STORAGE_ENGINE = InnoDB; +SET @@autocommit=1; +connection default +SHOW VARIABLES LIKE 'have_query_cache'; +Variable_name Value +have_query_cache YES +SET GLOBAL query_cache_size = 204800; +flush status; +SET @@autocommit=1; +SET SESSION STORAGE_ENGINE = InnoDB; +CREATE TABLE t2 (s1 int, s2 varchar(1000), key(s1)) PARTITION BY KEY (s1) PARTITIONS 3; +INSERT INTO t2 VALUES (1,repeat('a',10)),(2,repeat('a',10)),(3,repeat('a',10)),(4,repeat('a',10)); +COMMIT; +START TRANSACTION; +SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w'; +count(*) +0 +UPDATE t2 SET s2 = 'w' WHERE s1 = 3; +SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w'; +count(*) +1 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +connection connection1 +START TRANSACTION; +SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w'; +count(*) +0 +INSERT INTO t2 VALUES (5,'w'); +SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w'; +count(*) +1 +COMMIT; +SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w'; +count(*) +1 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +connection default +SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w'; +count(*) +1 +COMMIT; +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w'; +count(*) +2 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +connection connection1 +SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w'; +count(*) +2 +START TRANSACTION; +SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w'; +count(*) +2 +INSERT INTO t2 VALUES (6,'w'); +SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w'; +count(*) +3 +connection default +SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w'; +count(*) +2 +START TRANSACTION; +SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w'; +count(*) +2 +DELETE from t2 WHERE s1=3; +SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w'; +count(*) +1 +COMMIT; +connection connection1 +COMMIT; +SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w'; +count(*) +2 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 0 +set @@global.query_cache_size = @save_query_cache_size; +drop table t2; diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result index fc7e229603c..dd403f64b5c 100644 --- a/mysql-test/r/partition_innodb.result +++ b/mysql-test/r/partition_innodb.result @@ -2,6 +2,35 @@ set global default_storage_engine='innodb'; set session default_storage_engine='innodb'; drop table if exists t1, t2; # +# Bug#13694811: THE OPTIMIZER WRONGLY USES THE FIRST +# INNODB PARTITION STATISTICS +# +CREATE TABLE t1 +(a INT, +b varchar(64), +PRIMARY KEY (a), +KEY (b)) +ENGINE = InnoDB +PARTITION BY RANGE (a) +SUBPARTITION BY HASH (a) SUBPARTITIONS 10 +(PARTITION pNeg VALUES LESS THAN (0), +PARTITION p0 VALUES LESS THAN (1000), +PARTITION pMAX VALUES LESS THAN MAXVALUE); +# Only one row in the first 10 subpartitions +INSERT INTO t1 VALUES (-1, 'Only negative pk value'); +INSERT INTO t1 VALUES (0, 'Mod Zero'), (1, 'One'), (2, 'Two'), (3, 'Three'), +(10, 'Zero'), (11, 'Mod One'), (12, 'Mod Two'), (13, 'Mod Three'), +(20, '0'), (21, '1'), (22, '2'), (23, '3'), +(4, '4'), (5, '5'), (6, '6'), (7, '7'), (8, '8'), (9, '9'); +INSERT INTO t1 SELECT a + 30, b FROM t1 WHERE a >= 0; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +EXPLAIN SELECT b FROM t1 WHERE b between 'L' and 'N' AND a > -100; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY,b b 67 NULL 18 Using where; Using index +DROP TABLE t1; +# # Bug#56287: crash when using Partition datetime in sub in query # CREATE TABLE t1 diff --git a/mysql-test/r/partition_pruning.result b/mysql-test/r/partition_pruning.result index 32c0cd34942..4d48b70d26a 100644 --- a/mysql-test/r/partition_pruning.result +++ b/mysql-test/r/partition_pruning.result @@ -18,7 +18,7 @@ id select_type table partitions type possible_keys key key_len ref rows Extra # # # # # # # # # 3 # EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 7; id select_type table partitions type possible_keys key key_len ref rows Extra -# # # # # # # # # 9 # +# # # # # # # # # 10 # EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1; id select_type table partitions type possible_keys key key_len ref rows Extra # # # # # # # # # 3 # @@ -105,7 +105,7 @@ a 6 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 7; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max range PRIMARY PRIMARY 4 NULL 9 Using where; Using index +1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 10 Using where; Using index SELECT * FROM t1 WHERE a <= 1; a -1 @@ -168,7 +168,7 @@ a 6 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 6; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max range PRIMARY PRIMARY 4 NULL 9 Using where; Using index +1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 10 Using where; Using index SELECT * FROM t1 WHERE a <= 7; a -1 @@ -182,7 +182,7 @@ a 7 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 7; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max range PRIMARY PRIMARY 4 NULL 9 Using where; Using index +1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 10 Using where; Using index SELECT * FROM t1 WHERE a = 1; a 1 @@ -424,7 +424,7 @@ a 5 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 6; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0,p1,p2,p3,p4,max range PRIMARY PRIMARY 4 NULL 8 Using where; Using index +1 SIMPLE t1 p0,p1,p2,p3,p4,max index PRIMARY PRIMARY 4 NULL 9 Using where; Using index SELECT * FROM t1 WHERE a <= 1; a -1 @@ -474,7 +474,7 @@ a 5 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 5; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0,p1,p2,p3,p4,max range PRIMARY PRIMARY 4 NULL 8 Using where; Using index +1 SIMPLE t1 p0,p1,p2,p3,p4,max index PRIMARY PRIMARY 4 NULL 9 Using where; Using index SELECT * FROM t1 WHERE a <= 6; a -1 @@ -487,7 +487,7 @@ a 6 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 6; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0,p1,p2,p3,p4,max range PRIMARY PRIMARY 4 NULL 8 Using where; Using index +1 SIMPLE t1 p0,p1,p2,p3,p4,max index PRIMARY PRIMARY 4 NULL 9 Using where; Using index SELECT * FROM t1 WHERE a = 1; a 1 @@ -744,13 +744,13 @@ a 1001-01-01 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index +1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p2001-01-01 range a a 4 NULL 3 Using where; Using index @@ -759,26 +759,26 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1001-01-01 system a NULL NULL NULL 1 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index +1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index +1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL ref a a 4 const 1 Using index # Disabling warnings for the invalid date EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 5 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 5 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p2001-01-01 index a a 4 NULL 4 Using where; Using index @@ -790,16 +790,16 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 5 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 3 Using where; Using index +1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 index a a 4 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index # test without index ALTER TABLE t1 DROP KEY a; SELECT * FROM t1 WHERE a < '1001-01-01'; @@ -1076,7 +1076,7 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 range a a 4 NULL 4 Using where; Using index @@ -1104,10 +1104,10 @@ id select_type table partitions type possible_keys key key_len ref rows Extra # Disabling warnings for the invalid date EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL index a a 4 NULL 4 Using where; Using index @@ -1119,10 +1119,10 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p1001-01-01 index a a 4 NULL 4 Using where; Using index @@ -1405,7 +1405,7 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 range a a 4 NULL 4 Using where; Using index @@ -1433,10 +1433,10 @@ id select_type table partitions type possible_keys key key_len ref rows Extra # Disabling warnings for the invalid date EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL index a a 4 NULL 4 Using where; Using index @@ -1448,10 +1448,10 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p1001-01-01 index a a 4 NULL 4 Using where; Using index @@ -2867,18 +2867,18 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1,p2 ALL NULL NULL NULL NULL 510 Using where explain partitions select * from t2 where b = 4; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 76 +1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 96 explain extended select * from t2 where b = 6; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ref b b 5 const 76 100.00 +1 SIMPLE t2 ref b b 5 const 96 100.00 Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = 6) explain partitions select * from t2 where b = 6; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 76 +1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 96 explain extended select * from t2 where b in (1,3,5); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL b NULL NULL NULL 910 40.66 Using where +1 SIMPLE t2 ALL b NULL NULL NULL 910 51.65 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` in (1,3,5)) explain partitions select * from t2 where b in (1,3,5); @@ -2886,7 +2886,7 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where explain extended select * from t2 where b in (2,4,6); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL b NULL NULL NULL 910 25.05 Using where +1 SIMPLE t2 ALL b NULL NULL NULL 910 31.65 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` in (2,4,6)) explain partitions select * from t2 where b in (2,4,6); @@ -2894,7 +2894,7 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where explain extended select * from t2 where b in (7,8,9); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL b NULL NULL NULL 910 36.70 Using where +1 SIMPLE t2 ALL b NULL NULL NULL 910 19.12 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` in (7,8,9)) explain partitions select * from t2 where b in (7,8,9); @@ -2902,7 +2902,7 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where explain extended select * from t2 where b > 5; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL b NULL NULL NULL 910 44.84 Using where +1 SIMPLE t2 ALL b NULL NULL NULL 910 29.23 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` > 5) explain partitions select * from t2 where b > 5; @@ -2910,7 +2910,7 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where explain extended select * from t2 where b > 5 and b < 8; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL b NULL NULL NULL 910 22.09 Using where +1 SIMPLE t2 ALL b NULL NULL NULL 910 28.13 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`b` > 5) and (`test`.`t2`.`b` < 8)) explain partitions select * from t2 where b > 5 and b < 8; @@ -2918,15 +2918,15 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where explain extended select * from t2 where b > 5 and b < 7; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range b b 5 NULL 76 100.00 Using where +1 SIMPLE t2 range b b 5 NULL 96 100.00 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`b` > 5) and (`test`.`t2`.`b` < 7)) explain partitions select * from t2 where b > 5 and b < 7; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 p0,p1,p2,p3,p4 range b b 5 NULL 76 Using where +1 SIMPLE t2 p0,p1,p2,p3,p4 range b b 5 NULL 96 Using where explain extended select * from t2 where b > 0 and b < 5; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL b NULL NULL NULL 910 41.65 Using where +1 SIMPLE t2 ALL b NULL NULL NULL 910 53.19 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`b` > 0) and (`test`.`t2`.`b` < 5)) explain partitions select * from t2 where b > 0 and b < 5; @@ -2960,10 +2960,10 @@ flush status; delete from t2 where b = 7; show status like 'Handler_read_rnd_next'; Variable_name Value -Handler_read_rnd_next 0 +Handler_read_rnd_next 915 show status like 'Handler_read_key'; Variable_name Value -Handler_read_key 5 +Handler_read_key 0 flush status; delete from t2 where b > 5; show status like 'Handler_read_rnd_next'; diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index 5f04544e0fb..ce0926c6bc1 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -3755,6 +3755,109 @@ FROM (SELECT 1 UNION SELECT 2) t; 1 1 2 + +# Bug#13805127: Stored program cache produces wrong result in same THD + +PREPARE s1 FROM +" +SELECT c1, t2.c2, count(c3) +FROM + ( + SELECT 3 as c2 FROM dual WHERE @x = 1 + UNION + SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 + ) AS t1, + ( + SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual + UNION + SELECT '2012-03-01 02:00:00', 3, 2 FROM dual + UNION + SELECT '2012-03-01 01:00:00', 2, 1 FROM dual + ) AS t2 +WHERE t2.c2 = t1.c2 +GROUP BY c1, c2 +"; + +SET @x = 1; +SELECT c1, t2.c2, count(c3) +FROM +( +SELECT 3 as c2 FROM dual WHERE @x = 1 +UNION +SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 +) AS t1, +( +SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual +UNION +SELECT '2012-03-01 02:00:00', 3, 2 FROM dual +UNION +SELECT '2012-03-01 01:00:00', 2, 1 FROM dual +) AS t2 +WHERE t2.c2 = t1.c2 +GROUP BY c1, c2; +c1 c2 count(c3) +2012-03-01 01:00:00 2 1 +2012-03-01 01:00:00 3 1 +2012-03-01 02:00:00 3 1 + +EXECUTE s1; +c1 c2 count(c3) +2012-03-01 01:00:00 2 1 +2012-03-01 01:00:00 3 1 +2012-03-01 02:00:00 3 1 + +SET @x = 2; +SELECT c1, t2.c2, count(c3) +FROM +( +SELECT 3 as c2 FROM dual WHERE @x = 1 +UNION +SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 +) AS t1, +( +SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual +UNION +SELECT '2012-03-01 02:00:00', 3, 2 FROM dual +UNION +SELECT '2012-03-01 01:00:00', 2, 1 FROM dual +) AS t2 +WHERE t2.c2 = t1.c2 +GROUP BY c1, c2; +c1 c2 count(c3) +2012-03-01 01:00:00 2 1 + +EXECUTE s1; +c1 c2 count(c3) +2012-03-01 01:00:00 2 1 + +SET @x = 1; +SELECT c1, t2.c2, count(c3) +FROM +( +SELECT 3 as c2 FROM dual WHERE @x = 1 +UNION +SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 +) AS t1, +( +SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual +UNION +SELECT '2012-03-01 02:00:00', 3, 2 FROM dual +UNION +SELECT '2012-03-01 01:00:00', 2, 1 FROM dual +) AS t2 +WHERE t2.c2 = t1.c2 +GROUP BY c1, c2; +c1 c2 count(c3) +2012-03-01 01:00:00 2 1 +2012-03-01 01:00:00 3 1 +2012-03-01 02:00:00 3 1 + +EXECUTE s1; +c1 c2 count(c3) +2012-03-01 01:00:00 2 1 +2012-03-01 01:00:00 3 1 +2012-03-01 02:00:00 3 1 +DEALLOCATE PREPARE s1; # # End of 5.5 tests. prepare stmt from "select date('2010-10-10') between '2010-09-09' and ?"; diff --git a/mysql-test/r/sp-bugs.result b/mysql-test/r/sp-bugs.result index 7d08ffb2f71..9d9deaebcc3 100644 --- a/mysql-test/r/sp-bugs.result +++ b/mysql-test/r/sp-bugs.result @@ -111,6 +111,7 @@ DROP FUNCTION db1.f1; DROP TABLE db1.t1; DROP DATABASE db1; DROP DATABASE db2; +USE test; # # Bug#13105873:valgrind warning:possible crash in foreign # key handling on subsequent create table if not exists @@ -130,4 +131,94 @@ CALL p1(); Warnings: Note 1050 Table 't2' already exists DROP DATABASE testdb; +USE test; End of 5.1 tests +# +# Bug#11763507 - 56224: FUNCTION NAME IS CASE-SENSITIVE +# +SET @@SQL_MODE = ''; +CREATE FUNCTION testf_bug11763507() RETURNS INT +BEGIN +RETURN 0; +END +$ +CREATE PROCEDURE testp_bug11763507() +BEGIN +SELECT "PROCEDURE testp_bug11763507"; +END +$ +SELECT testf_bug11763507(); +testf_bug11763507() +0 +SELECT TESTF_bug11763507(); +TESTF_bug11763507() +0 +SHOW FUNCTION STATUS LIKE 'testf_bug11763507'; +Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation +test testf_bug11763507 FUNCTION root@localhost # # DEFINER latin1 latin1_swedish_ci latin1_swedish_ci +SHOW FUNCTION STATUS WHERE NAME='testf_bug11763507'; +Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation +test testf_bug11763507 FUNCTION root@localhost # # DEFINER latin1 latin1_swedish_ci latin1_swedish_ci +SHOW FUNCTION STATUS LIKE 'TESTF_bug11763507'; +Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation +test testf_bug11763507 FUNCTION root@localhost # # DEFINER latin1 latin1_swedish_ci latin1_swedish_ci +SHOW FUNCTION STATUS WHERE NAME='TESTF_bug11763507'; +Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation +test testf_bug11763507 FUNCTION root@localhost # # DEFINER latin1 latin1_swedish_ci latin1_swedish_ci +SHOW CREATE FUNCTION testf_bug11763507; +Function sql_mode Create Function character_set_client collation_connection Database Collation +testf_bug11763507 CREATE DEFINER=`root`@`localhost` FUNCTION `testf_bug11763507`() RETURNS int(11) +BEGIN +RETURN 0; +END latin1 latin1_swedish_ci latin1_swedish_ci +SHOW CREATE FUNCTION TESTF_bug11763507; +Function sql_mode Create Function character_set_client collation_connection Database Collation +testf_bug11763507 CREATE DEFINER=`root`@`localhost` FUNCTION `testf_bug11763507`() RETURNS int(11) +BEGIN +RETURN 0; +END latin1 latin1_swedish_ci latin1_swedish_ci +CALL testp_bug11763507(); +PROCEDURE testp_bug11763507 +PROCEDURE testp_bug11763507 +CALL TESTP_bug11763507(); +PROCEDURE testp_bug11763507 +PROCEDURE testp_bug11763507 +SHOW PROCEDURE STATUS LIKE 'testp_bug11763507'; +Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation +test testp_bug11763507 PROCEDURE root@localhost # # DEFINER latin1 latin1_swedish_ci latin1_swedish_ci +SHOW PROCEDURE STATUS WHERE NAME='testp_bug11763507'; +Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation +test testp_bug11763507 PROCEDURE root@localhost # # DEFINER latin1 latin1_swedish_ci latin1_swedish_ci +SHOW PROCEDURE STATUS LIKE 'TESTP_bug11763507'; +Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation +test testp_bug11763507 PROCEDURE root@localhost # # DEFINER latin1 latin1_swedish_ci latin1_swedish_ci +SHOW PROCEDURE STATUS WHERE NAME='TESTP_bug11763507'; +Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation +test testp_bug11763507 PROCEDURE root@localhost # # DEFINER latin1 latin1_swedish_ci latin1_swedish_ci +SHOW CREATE PROCEDURE testp_bug11763507; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +testp_bug11763507 CREATE DEFINER=`root`@`localhost` PROCEDURE `testp_bug11763507`() +BEGIN +SELECT "PROCEDURE testp_bug11763507"; +END latin1 latin1_swedish_ci latin1_swedish_ci +SHOW CREATE PROCEDURE TESTP_bug11763507; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +testp_bug11763507 CREATE DEFINER=`root`@`localhost` PROCEDURE `testp_bug11763507`() +BEGIN +SELECT "PROCEDURE testp_bug11763507"; +END latin1 latin1_swedish_ci latin1_swedish_ci +SELECT specific_name FROM INFORMATION_SCHEMA.ROUTINES WHERE specific_name LIKE 'testf_bug11763507'; +specific_name +testf_bug11763507 +SELECT specific_name FROM INFORMATION_SCHEMA.ROUTINES WHERE specific_name LIKE 'TESTF_bug11763507'; +specific_name +testf_bug11763507 +SELECT specific_name FROM INFORMATION_SCHEMA.ROUTINES WHERE specific_name='testf_bug11763507'; +specific_name +testf_bug11763507 +SELECT specific_name FROM INFORMATION_SCHEMA.ROUTINES WHERE specific_name='TESTF_bug11763507'; +specific_name +testf_bug11763507 +DROP PROCEDURE testp_bug11763507; +DROP FUNCTION testf_bug11763507; +#END OF BUG#11763507 test. diff --git a/mysql-test/r/sp-code.result b/mysql-test/r/sp-code.result index fd2e79dff29..67651294498 100644 --- a/mysql-test/r/sp-code.result +++ b/mysql-test/r/sp-code.result @@ -945,3 +945,32 @@ Error 1329 No data - zero rows fetched, selected, or processed SET SESSION debug_dbug="-d,bug23032_emit_warning"; DROP PROCEDURE p1; DROP TABLE t1; +# +# Bug#11763507 - 56224: FUNCTION NAME IS CASE-SENSITIVE +# +SET @@SQL_MODE = ''; +CREATE FUNCTION testf_bug11763507() RETURNS INT +BEGIN +RETURN 0; +END +$ +CREATE PROCEDURE testp_bug11763507() +BEGIN +SELECT "PROCEDURE testp_bug11763507"; +END +$ +SHOW FUNCTION CODE testf_bug11763507; +Pos Instruction +0 freturn 3 0 +SHOW FUNCTION CODE TESTF_bug11763507; +Pos Instruction +0 freturn 3 0 +SHOW PROCEDURE CODE testp_bug11763507; +Pos Instruction +0 stmt 0 "SELECT "PROCEDURE testp_bug11763507"" +SHOW PROCEDURE CODE TESTP_bug11763507; +Pos Instruction +0 stmt 0 "SELECT "PROCEDURE testp_bug11763507"" +DROP PROCEDURE testp_bug11763507; +DROP FUNCTION testf_bug11763507; +#END OF BUG#11763507 test. diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index ad54f9c0fdf..d52318c6c5b 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -7745,6 +7745,44 @@ CALL p2(); DROP PROCEDURE p1; DROP PROCEDURE p2; DROP TABLE t1; + +# Bug#13805127: Stored program cache produces wrong result in same THD + +CREATE PROCEDURE p1(x INT UNSIGNED) +BEGIN +SELECT c1, t2.c2, count(c3) +FROM +( +SELECT 3 as c2 FROM dual WHERE x = 1 +UNION +SELECT 2 FROM dual WHERE x = 1 OR x = 2 +) AS t1, +( +SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual +UNION +SELECT '2012-03-01 02:00:00', 3, 2 FROM dual +UNION +SELECT '2012-03-01 01:00:00', 2, 1 FROM dual +) AS t2 +WHERE t2.c2 = t1.c2 +GROUP BY c1, c2 +; +END| + +CALL p1(1); +c1 c2 count(c3) +2012-03-01 01:00:00 2 1 +2012-03-01 01:00:00 3 1 +2012-03-01 02:00:00 3 1 +CALL p1(2); +c1 c2 count(c3) +2012-03-01 01:00:00 2 1 +CALL p1(1); +c1 c2 count(c3) +2012-03-01 01:00:00 2 1 +2012-03-01 01:00:00 3 1 +2012-03-01 02:00:00 3 1 +DROP PROCEDURE p1; # End of 5.5 test # # Bug#12663165 SP DEAD CODE REMOVAL DOESN'T UNDERSTAND CONTINUE HANDLERS diff --git a/mysql-test/r/sp_notembedded.result b/mysql-test/r/sp_notembedded.result index c4f276774d5..0b4b1726e22 100644 --- a/mysql-test/r/sp_notembedded.result +++ b/mysql-test/r/sp_notembedded.result @@ -258,4 +258,25 @@ SELECT `my.db`.f1(2); 2 # Switching to default connection. DROP DATABASE `my.db`; +USE test; set @@global.concurrent_insert= @old_concurrent_insert; +# +# Bug#11763507 - 56224: FUNCTION NAME IS CASE-SENSITIVE +# +SET @@SQL_MODE = ''; +CREATE EVENT teste_bug11763507 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR +DO SELECT 1 $ +SHOW EVENTS LIKE 'teste_bug11763507'; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +test teste_bug11763507 root@localhost SYSTEM ONE TIME # # # # NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +SHOW EVENTS LIKE 'TESTE_bug11763507'; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +test teste_bug11763507 root@localhost SYSTEM ONE TIME # # # # NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +SHOW CREATE EVENT teste_bug11763507; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +teste_bug11763507 SYSTEM # latin1 latin1_swedish_ci latin1_swedish_ci +SHOW CREATE EVENT TESTE_bug11763507; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +teste_bug11763507 SYSTEM # latin1 latin1_swedish_ci latin1_swedish_ci +DROP EVENT teste_bug11763507; +#END OF BUG#11763507 test. diff --git a/mysql-test/r/sql_mode.result b/mysql-test/r/sql_mode.result index e83879274a1..6736ca7f541 100644 --- a/mysql-test/r/sql_mode.result +++ b/mysql-test/r/sql_mode.result @@ -533,3 +533,207 @@ SELECT * FROM mysql.columns_priv WHERE Host = 'localhost' AND User LIKE 'user_%P Host Db User Table_name Column_name Timestamp Column_priv DROP TABLE t1; DROP TABLE t2; + +# +# Test for Bug#12601974 - STORED PROCEDURE SQL_MODE=NO_BACKSLASH_ESCAPES +# IGNORED AND BREAKS REPLICATION +# +DROP TABLE IF EXISTS test_table; +DROP FUNCTION IF EXISTS test_function; +CREATE TABLE test_table (c1 CHAR(50)); +SET @org_mode=@@sql_mode; +SET @@sql_mode=''; +PREPARE insert_stmt FROM 'INSERT INTO test_table VALUES (?)'; +PREPARE update_stmt FROM 'UPDATE test_table SET c1= ? WHERE c1= ?'; +CREATE FUNCTION test_function(var CHAR(50)) RETURNS CHAR(50) +BEGIN +DECLARE char_val CHAR(50); +SELECT c1 INTO char_val FROM test_table WHERE c1=var; +RETURN char_val; +END +$ +SET @var1='abcd\'ef'; +SET @var2='abcd\"ef'; +SET @var3='abcd\bef'; +SET @var4='abcd\nef'; +SET @var5='abcd\ref'; +SET @var6='abcd\tef'; +SET @var7='abcd\\ef'; +SET @var8='abcd\%ef'; +SET @var9='abcd\_ef'; +SET @to_var1='wxyz\'ef'; +SET @to_var2='wxyz\"ef'; +SET @to_var3='wxyz\bef'; +SET @to_var4='wxyz\nef'; +SET @to_var5='wxyz\ref'; +SET @to_var6='wxyz\tef'; +SET @to_var7='wxyz\\ef'; +SET @to_var8='wxyz\%ef'; +SET @to_var9='wxyz\_ef'; +# STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT +EXECUTE insert_stmt USING @var1; +EXECUTE insert_stmt USING @var2; +EXECUTE insert_stmt USING @var3; +EXECUTE insert_stmt USING @var4; +EXECUTE insert_stmt USING @var5; +EXECUTE insert_stmt USING @var6; +EXECUTE insert_stmt USING @var7; +EXECUTE insert_stmt USING @var8; +EXECUTE insert_stmt USING @var9; +SELECT * FROM test_table; +c1 +abcd'ef +abcd"ef +abcdef +abcd +ef +abcd
ef +abcd ef +abcd\ef +abcd\%ef +abcd\_ef +EXECUTE update_stmt USING @to_var1, @var1; +EXECUTE update_stmt USING @to_var2, @var2; +EXECUTE update_stmt USING @to_var3, @var3; +EXECUTE update_stmt USING @to_var4, @var4; +EXECUTE update_stmt USING @to_var5, @var5; +EXECUTE update_stmt USING @to_var6, @var6; +EXECUTE update_stmt USING @to_var7, @var7; +EXECUTE update_stmt USING @to_var8, @var8; +EXECUTE update_stmt USING @to_var9, @var9; +SELECT * FROM test_table; +c1 +wxyz'ef +wxyz"ef +wxyzef +wxyz +ef +wxyz
ef +wxyz ef +wxyz\ef +wxyz\%ef +wxyz\_ef + +# END OF CASE - STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT +# STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING +select test_function(@to_var1); +test_function(@to_var1) +wxyz'ef +SELECT test_function(@to_var2); +test_function(@to_var2) +wxyz"ef +SELECT test_function(@to_var3); +test_function(@to_var3) +wxyzef +SELECT test_function(@to_var4); +test_function(@to_var4) +wxyz +ef +SELECT test_function(@to_var5); +test_function(@to_var5) +wxyz
ef +SELECT test_function(@to_var6); +test_function(@to_var6) +wxyz ef +SELECT test_function(@to_var7); +test_function(@to_var7) +wxyz\ef +SELECT test_function(@to_var8); +test_function(@to_var8) +wxyz\%ef +SELECT test_function(@to_var9); +test_function(@to_var9) +wxyz\_ef + +# END OF CASE - STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING +DELETE FROM test_table; +DROP FUNCTION test_function; +SET @@sql_mode='NO_BACKSLASH_ESCAPES'; +CREATE FUNCTION test_function(var CHAR(50)) RETURNS CHAR(50) +BEGIN +DECLARE char_val CHAR(50); +SELECT c1 INTO char_val FROM test_table WHERE c1=var; +RETURN char_val; +END +$ +# STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT +EXECUTE insert_stmt USING @var1; +EXECUTE insert_stmt USING @var2; +EXECUTE insert_stmt USING @var3; +EXECUTE insert_stmt USING @var4; +EXECUTE insert_stmt USING @var5; +EXECUTE insert_stmt USING @var6; +EXECUTE insert_stmt USING @var7; +EXECUTE insert_stmt USING @var8; +EXECUTE insert_stmt USING @var9; +SELECT * FROM test_table; +c1 +abcd'ef +abcd"ef +abcdef +abcd +ef +abcd
ef +abcd ef +abcd\ef +abcd\%ef +abcd\_ef +EXECUTE update_stmt USING @to_var1, @var1; +EXECUTE update_stmt USING @to_var2, @var2; +EXECUTE update_stmt USING @to_var3, @var3; +EXECUTE update_stmt USING @to_var4, @var4; +EXECUTE update_stmt USING @to_var5, @var5; +EXECUTE update_stmt USING @to_var6, @var6; +EXECUTE update_stmt USING @to_var7, @var7; +EXECUTE update_stmt USING @to_var8, @var8; +EXECUTE update_stmt USING @to_var9, @var9; +SELECT * FROM test_table; +c1 +wxyz'ef +wxyz"ef +wxyzef +wxyz +ef +wxyz
ef +wxyz ef +wxyz\ef +wxyz\%ef +wxyz\_ef + +# END OF CASE - STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT +# STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING +select test_function(@to_var1); +test_function(@to_var1) +wxyz'ef +SELECT test_function(@to_var2); +test_function(@to_var2) +wxyz"ef +SELECT test_function(@to_var3); +test_function(@to_var3) +wxyzef +SELECT test_function(@to_var4); +test_function(@to_var4) +wxyz +ef +SELECT test_function(@to_var5); +test_function(@to_var5) +wxyz
ef +SELECT test_function(@to_var6); +test_function(@to_var6) +wxyz ef +SELECT test_function(@to_var7); +test_function(@to_var7) +wxyz\ef +SELECT test_function(@to_var8); +test_function(@to_var8) +wxyz\%ef +SELECT test_function(@to_var9); +test_function(@to_var9) +wxyz\_ef + +# END OF CASE - STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING +DROP TABLE test_table; +DROP FUNCTION test_function; +SET @@sql_mode= @org_mode; + +#End of Test for Bug#12601974 diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 26faf465749..29b8c41e515 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -4645,6 +4645,55 @@ ERROR 21000: Subquery returns more than 1 row SET SESSION sql_mode=@old_sql_mode; DEALLOCATE PREPARE stmt; DROP TABLE t1; +# +# Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER +# +CREATE TABLE t1(a1 int); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2(a1 int); +INSERT INTO t2 VALUES (3); +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2); +1 +1 +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2); +1 +1 +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2); +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2); +1 +1 +1 +SET SESSION sql_mode=@old_sql_mode; +DROP TABLE t1, t2; +# +# Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER +# +create table t2(i int); +insert into t2 values(0); +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; +CREATE VIEW v1 AS +SELECT 'f' FROM t2 UNION SELECT 'x' FROM t2 +; +CREATE TABLE t1 ( +pk int NOT NULL, +col_varchar_key varchar(1) DEFAULT NULL, +PRIMARY KEY (pk), +KEY col_varchar_key (col_varchar_key) +); +SELECT t1.pk +FROM t1 +WHERE t1.col_varchar_key < ALL ( SELECT * FROM v1 ) +; +pk +SET SESSION sql_mode=@old_sql_mode; +drop table t2, t1; +drop view v1; End of 5.0 tests. create table t_out (subcase char(3), a1 char(2), b1 char(2), c1 char(2)); diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 8a43852a159..e8086b53ead 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -4647,6 +4647,55 @@ ERROR 21000: Subquery returns more than 1 row SET SESSION sql_mode=@old_sql_mode; DEALLOCATE PREPARE stmt; DROP TABLE t1; +# +# Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER +# +CREATE TABLE t1(a1 int); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2(a1 int); +INSERT INTO t2 VALUES (3); +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2); +1 +1 +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2); +1 +1 +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2); +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2); +1 +1 +1 +SET SESSION sql_mode=@old_sql_mode; +DROP TABLE t1, t2; +# +# Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER +# +create table t2(i int); +insert into t2 values(0); +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; +CREATE VIEW v1 AS +SELECT 'f' FROM t2 UNION SELECT 'x' FROM t2 +; +CREATE TABLE t1 ( +pk int NOT NULL, +col_varchar_key varchar(1) DEFAULT NULL, +PRIMARY KEY (pk), +KEY col_varchar_key (col_varchar_key) +); +SELECT t1.pk +FROM t1 +WHERE t1.col_varchar_key < ALL ( SELECT * FROM v1 ) +; +pk +SET SESSION sql_mode=@old_sql_mode; +drop table t2, t1; +drop view v1; End of 5.0 tests. create table t_out (subcase char(3), a1 char(2), b1 char(2), c1 char(2)); diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 3079868bbf6..12b1e45c92c 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -4643,6 +4643,55 @@ ERROR 21000: Subquery returns more than 1 row SET SESSION sql_mode=@old_sql_mode; DEALLOCATE PREPARE stmt; DROP TABLE t1; +# +# Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER +# +CREATE TABLE t1(a1 int); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2(a1 int); +INSERT INTO t2 VALUES (3); +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2); +1 +1 +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2); +1 +1 +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2); +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2); +1 +1 +1 +SET SESSION sql_mode=@old_sql_mode; +DROP TABLE t1, t2; +# +# Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER +# +create table t2(i int); +insert into t2 values(0); +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; +CREATE VIEW v1 AS +SELECT 'f' FROM t2 UNION SELECT 'x' FROM t2 +; +CREATE TABLE t1 ( +pk int NOT NULL, +col_varchar_key varchar(1) DEFAULT NULL, +PRIMARY KEY (pk), +KEY col_varchar_key (col_varchar_key) +); +SELECT t1.pk +FROM t1 +WHERE t1.col_varchar_key < ALL ( SELECT * FROM v1 ) +; +pk +SET SESSION sql_mode=@old_sql_mode; +drop table t2, t1; +drop view v1; End of 5.0 tests. create table t_out (subcase char(3), a1 char(2), b1 char(2), c1 char(2)); diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index d8169de9130..b9a4f9b23a9 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -4651,6 +4651,55 @@ ERROR 21000: Subquery returns more than 1 row SET SESSION sql_mode=@old_sql_mode; DEALLOCATE PREPARE stmt; DROP TABLE t1; +# +# Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER +# +CREATE TABLE t1(a1 int); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2(a1 int); +INSERT INTO t2 VALUES (3); +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2); +1 +1 +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2); +1 +1 +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2); +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2); +1 +1 +1 +SET SESSION sql_mode=@old_sql_mode; +DROP TABLE t1, t2; +# +# Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER +# +create table t2(i int); +insert into t2 values(0); +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; +CREATE VIEW v1 AS +SELECT 'f' FROM t2 UNION SELECT 'x' FROM t2 +; +CREATE TABLE t1 ( +pk int NOT NULL, +col_varchar_key varchar(1) DEFAULT NULL, +PRIMARY KEY (pk), +KEY col_varchar_key (col_varchar_key) +); +SELECT t1.pk +FROM t1 +WHERE t1.col_varchar_key < ALL ( SELECT * FROM v1 ) +; +pk +SET SESSION sql_mode=@old_sql_mode; +drop table t2, t1; +drop view v1; End of 5.0 tests. create table t_out (subcase char(3), a1 char(2), b1 char(2), c1 char(2)); diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 6a84070c2cd..ad6d61e9ec9 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -4643,6 +4643,55 @@ ERROR 21000: Subquery returns more than 1 row SET SESSION sql_mode=@old_sql_mode; DEALLOCATE PREPARE stmt; DROP TABLE t1; +# +# Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER +# +CREATE TABLE t1(a1 int); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2(a1 int); +INSERT INTO t2 VALUES (3); +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2); +1 +1 +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2); +1 +1 +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2); +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2); +1 +1 +1 +SET SESSION sql_mode=@old_sql_mode; +DROP TABLE t1, t2; +# +# Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER +# +create table t2(i int); +insert into t2 values(0); +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; +CREATE VIEW v1 AS +SELECT 'f' FROM t2 UNION SELECT 'x' FROM t2 +; +CREATE TABLE t1 ( +pk int NOT NULL, +col_varchar_key varchar(1) DEFAULT NULL, +PRIMARY KEY (pk), +KEY col_varchar_key (col_varchar_key) +); +SELECT t1.pk +FROM t1 +WHERE t1.col_varchar_key < ALL ( SELECT * FROM v1 ) +; +pk +SET SESSION sql_mode=@old_sql_mode; +drop table t2, t1; +drop view v1; End of 5.0 tests. create table t_out (subcase char(3), a1 char(2), b1 char(2), c1 char(2)); diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 8939baf1864..ae4c61a1dc7 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -4609,6 +4609,13 @@ UNLOCK TABLES; DROP PROCEDURE p1; DROP TABLE t1; # +# Bug#12626844: WRONG ERROR MESSAGE WHILE CREATING A VIEW ON A +# NON EXISTING DATABASE +# +DROP DATABASE IF EXISTS nodb; +CREATE VIEW nodb.a AS SELECT 1; +ERROR 42000: Unknown database 'nodb' +# # lp:833600 Wrong result with view + outer join + uncorrelated subquery (non-semijoin) # CREATE TABLE t1 ( a int, b int ); diff --git a/mysql-test/r/xa.result b/mysql-test/r/xa.result index 3799e9417af..6c242d950ab 100644 --- a/mysql-test/r/xa.result +++ b/mysql-test/r/xa.result @@ -174,7 +174,6 @@ CREATE TABLE t1 (a INT) engine=InnoDB; XA START 'a'; INSERT INTO t1 VALUES (1); SAVEPOINT savep; -ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the ACTIVE state XA END 'a'; SELECT * FROM t1; ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the IDLE state diff --git a/mysql-test/suite/binlog/r/binlog_sql_mode.result b/mysql-test/suite/binlog/r/binlog_sql_mode.result index 4477c94a95e..1aea77c4a4b 100644 --- a/mysql-test/suite/binlog/r/binlog_sql_mode.result +++ b/mysql-test/suite/binlog/r/binlog_sql_mode.result @@ -38,3 +38,114 @@ DROP VIEW testView; DROP TABLE t1; SET @@global.sql_mode= @old_sql_mode; SET @@session.binlog_format=@old_binlog_format; + +# +# Test for Bug#12601974 - STORED PROCEDURE SQL_MODE=NO_BACKSLASH_ESCAPES +# IGNORED AND BREAKS REPLICATION +# +DROP DATABASE IF EXISTS mysqltest_db; +DROP TABLE IF EXISTS test_table; +CREATE DATABASE mysqltest_db; +USE mysqltest_db; +CREATE TABLE test_table (c1 CHAR(50)); +SET @org_mode=@@sql_mode; +SET @@sql_mode=''; +CREATE PROCEDURE proc_without_sql_mode (IN param1 CHAR(50), IN param2 CHAR(50)) +BEGIN +DECLARE var1 CHAR(50) DEFAULT param1; +DECLARE var2 CHAR(50) DEFAULT param2; +DECLARE var3 CHAR(50) DEFAULT 'abcd\bef'; +DECLARE var4 CHAR(50) DEFAULT 'abcd\nef'; +DECLARE var5 CHAR(50) DEFAULT 'abcd\ref'; +DECLARE var6 CHAR(50) DEFAULT 'abcd\tef'; +DECLARE var7 CHAR(50) DEFAULT 'abcd\\ef'; +DECLARE var8 CHAR(50) DEFAULT 'abcd\%ef'; +DECLARE var9 CHAR(50) DEFAULT 'abcd\_ef'; +INSERT INTO test_table VALUES (var1); +INSERT INTO test_table VALUES (var2); +INSERT INTO test_table VALUES (var3); +INSERT INTO test_table VALUES (var4); +INSERT INTO test_table VALUES (var5); +INSERT INTO test_table VALUES (var6); +INSERT INTO test_table VALUES (var7); +INSERT INTO test_table VALUES (var8); +INSERT INTO test_table VALUES (var9); +END +$ +SET @@sql_mode='NO_BACKSLASH_ESCAPES'$ +CREATE PROCEDURE proc_with_sql_mode (IN param1 CHAR(50), IN param2 CHAR(50)) +BEGIN +DECLARE var1 CHAR(50) DEFAULT param1; +DECLARE var2 CHAR(50) DEFAULT param2; +DECLARE var3 CHAR(50) DEFAULT 'wxyz\bef'; +DECLARE var4 CHAR(50) DEFAULT 'wxyz\nef'; +DECLARE var5 CHAR(50) DEFAULT 'wxyz\ref'; +DECLARE var6 CHAR(50) DEFAULT 'wxyz\tef'; +DECLARE var7 CHAR(50) DEFAULT 'wxyz\\ef'; +DECLARE var8 CHAR(50) DEFAULT 'wxyz\%ef'; +DECLARE var9 CHAR(50) DEFAULT 'wxyz\_ef'; +INSERT INTO test_table VALUES (var1); +INSERT INTO test_table VALUES (var2); +INSERT INTO test_table VALUES (var3); +INSERT INTO test_table VALUES (var4); +INSERT INTO test_table VALUES (var5); +INSERT INTO test_table VALUES (var6); +INSERT INTO test_table VALUES (var7); +INSERT INTO test_table VALUES (var8); +INSERT INTO test_table VALUES (var9); +END +$ +SET @@sql_mode=''; +CALL proc_without_sql_mode('abcd\'ef', 'abcd\"ef'); +CALL proc_with_sql_mode('wxyz\'ef', 'wxyz\"ef'); +SELECT * FROM test_table; +c1 +abcd'ef +abcd"ef +abcdef +abcd +ef +abcd
ef +abcd ef +abcd\ef +abcd\%ef +abcd\_ef +wxyz'ef +wxyz"ef +wxyz\bef +wxyz\nef +wxyz\ref +wxyz\tef +wxyz\\ef +wxyz\%ef +wxyz\_ef +"Dropping table test_table" +DROP TABLE test_table; +#"test_table" content after replaying the binlog +SELECT * FROM test_table; +c1 +abcd'ef +abcd"ef +abcdef +abcd +ef +abcd
ef +abcd ef +abcd\ef +abcd\%ef +abcd\_ef +wxyz'ef +wxyz"ef +wxyz\bef +wxyz\nef +wxyz\ref +wxyz\tef +wxyz\\ef +wxyz\%ef +wxyz\_ef +#Clean up +DROP DATABASE mysqltest_db; +SET @@sql_mode= @org_mode; +use test; + +#End of Test for Bug#12601974 diff --git a/mysql-test/suite/binlog/t/binlog_sql_mode.test b/mysql-test/suite/binlog/t/binlog_sql_mode.test index ab4f6450543..167c8f5a96d 100644 --- a/mysql-test/suite/binlog/t/binlog_sql_mode.test +++ b/mysql-test/suite/binlog/t/binlog_sql_mode.test @@ -73,3 +73,99 @@ DROP TABLE t1; SET @@global.sql_mode= @old_sql_mode; SET @@session.binlog_format=@old_binlog_format; + +--echo +--echo # +--echo # Test for Bug#12601974 - STORED PROCEDURE SQL_MODE=NO_BACKSLASH_ESCAPES +--echo # IGNORED AND BREAKS REPLICATION +--echo # + +--disable_warnings +DROP DATABASE IF EXISTS mysqltest_db; +DROP TABLE IF EXISTS test_table; +--enable_warnings + +CREATE DATABASE mysqltest_db; +USE mysqltest_db; +CREATE TABLE test_table (c1 CHAR(50)); + +SET @org_mode=@@sql_mode; + +SET @@sql_mode=''; +DELIMITER $; +CREATE PROCEDURE proc_without_sql_mode (IN param1 CHAR(50), IN param2 CHAR(50)) +BEGIN + DECLARE var1 CHAR(50) DEFAULT param1; + DECLARE var2 CHAR(50) DEFAULT param2; + DECLARE var3 CHAR(50) DEFAULT 'abcd\bef'; + DECLARE var4 CHAR(50) DEFAULT 'abcd\nef'; + DECLARE var5 CHAR(50) DEFAULT 'abcd\ref'; + DECLARE var6 CHAR(50) DEFAULT 'abcd\tef'; + DECLARE var7 CHAR(50) DEFAULT 'abcd\\ef'; + DECLARE var8 CHAR(50) DEFAULT 'abcd\%ef'; + DECLARE var9 CHAR(50) DEFAULT 'abcd\_ef'; + + INSERT INTO test_table VALUES (var1); + INSERT INTO test_table VALUES (var2); + INSERT INTO test_table VALUES (var3); + INSERT INTO test_table VALUES (var4); + INSERT INTO test_table VALUES (var5); + INSERT INTO test_table VALUES (var6); + INSERT INTO test_table VALUES (var7); + INSERT INTO test_table VALUES (var8); + INSERT INTO test_table VALUES (var9); +END +$ + +SET @@sql_mode='NO_BACKSLASH_ESCAPES'$ +CREATE PROCEDURE proc_with_sql_mode (IN param1 CHAR(50), IN param2 CHAR(50)) +BEGIN + DECLARE var1 CHAR(50) DEFAULT param1; + DECLARE var2 CHAR(50) DEFAULT param2; + DECLARE var3 CHAR(50) DEFAULT 'wxyz\bef'; + DECLARE var4 CHAR(50) DEFAULT 'wxyz\nef'; + DECLARE var5 CHAR(50) DEFAULT 'wxyz\ref'; + DECLARE var6 CHAR(50) DEFAULT 'wxyz\tef'; + DECLARE var7 CHAR(50) DEFAULT 'wxyz\\ef'; + DECLARE var8 CHAR(50) DEFAULT 'wxyz\%ef'; + DECLARE var9 CHAR(50) DEFAULT 'wxyz\_ef'; + + INSERT INTO test_table VALUES (var1); + INSERT INTO test_table VALUES (var2); + INSERT INTO test_table VALUES (var3); + INSERT INTO test_table VALUES (var4); + INSERT INTO test_table VALUES (var5); + INSERT INTO test_table VALUES (var6); + INSERT INTO test_table VALUES (var7); + INSERT INTO test_table VALUES (var8); + INSERT INTO test_table VALUES (var9); +END +$ + +DELIMITER ;$ +SET @@sql_mode=''; +CALL proc_without_sql_mode('abcd\'ef', 'abcd\"ef'); +CALL proc_with_sql_mode('wxyz\'ef', 'wxyz\"ef'); +SELECT * FROM test_table; + +let $MYSQLD_DATADIR= `select @@datadir`; +--exec $MYSQL_BINLOG --force-if-open -d mysqltest_db $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_bug12601974.binlog + +--echo "Dropping table test_table" +DROP TABLE test_table; + +--exec $MYSQL -e "source $MYSQLTEST_VARDIR/tmp/mysqlbinlog_bug12601974.binlog" + +--echo #"test_table" content after replaying the binlog +SELECT * FROM test_table; + +--echo #Clean up +--remove_file $MYSQLTEST_VARDIR/tmp/mysqlbinlog_bug12601974.binlog +DROP DATABASE mysqltest_db; +SET @@sql_mode= @org_mode; +use test; + +--echo +--echo #End of Test for Bug#12601974 + + diff --git a/mysql-test/suite/innodb/include/innodb-index.inc b/mysql-test/suite/innodb/include/innodb-index.inc deleted file mode 100644 index 37de3162abe..00000000000 --- a/mysql-test/suite/innodb/include/innodb-index.inc +++ /dev/null @@ -1,26 +0,0 @@ ---eval create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb default charset=$charset -insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,2,'ad','ad'),(4,4,'afe','afe'); -commit; ---error ER_DUP_ENTRY -alter table t1 add unique index (b); -insert into t1 values(8,9,'fff','fff'); -select * from t1; -show create table t1; -alter table t1 add index (b); -insert into t1 values(10,10,'kkk','iii'); -select * from t1; -select * from t1 force index(b) order by b; -explain select * from t1 force index(b) order by b; -show create table t1; -alter table t1 add unique index (c), add index (d); -insert into t1 values(11,11,'aaa','mmm'); -select * from t1; -select * from t1 force index(b) order by b; -select * from t1 force index(c) order by c; -select * from t1 force index(d) order by d; -explain select * from t1 force index(b) order by b; -explain select * from t1 force index(c) order by c; -explain select * from t1 force index(d) order by d; -show create table t1; -check table t1; -drop table t1; diff --git a/mysql-test/suite/innodb/r/innodb-autoinc.result b/mysql-test/suite/innodb/r/innodb-autoinc.result index fa821372337..9eb89bead74 100644 --- a/mysql-test/suite/innodb/r/innodb-autoinc.result +++ b/mysql-test/suite/innodb/r/innodb-autoinc.result @@ -366,7 +366,7 @@ c1 310 400 1000 -1110 +1010 DROP TABLE t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; SET @@INSERT_ID=1; @@ -648,7 +648,7 @@ t2 CREATE TABLE `t2` ( `n` int(10) unsigned NOT NULL, `o` enum('FALSE','TRUE') DEFAULT NULL, PRIMARY KEY (`m`) -) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 +) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1 INSERT INTO t1 (b,c) SELECT n,o FROM t2 ; SHOW CREATE TABLE t1; Table Create Table @@ -1251,3 +1251,21 @@ t1 CREATE TABLE `t1` ( PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=latin1 DROP TABLE t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=256; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 1 +auto_increment_offset 256 +CREATE TABLE t1 (c1 TINYINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` tinyint(4) NOT NULL AUTO_INCREMENT, + `c2` varchar(10) DEFAULT NULL, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 +SELECT * FROM t1; +c1 c2 +1 NULL +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/innodb_bug11766634.result b/mysql-test/suite/innodb/r/innodb_bug11766634.result new file mode 100644 index 00000000000..7594d512638 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_bug11766634.result @@ -0,0 +1,5 @@ +create table t1 (f1 char(255)) engine innodb; +drop table t1; +create table t1 (f1 char(255)) engine innodb; +1 +drop table t1; diff --git a/mysql-test/suite/innodb/t/innodb-autoinc.test b/mysql-test/suite/innodb/t/innodb-autoinc.test index 4b3c3b2e56d..4f54a8ff957 100644 --- a/mysql-test/suite/innodb/t/innodb-autoinc.test +++ b/mysql-test/suite/innodb/t/innodb-autoinc.test @@ -637,3 +637,14 @@ INSERT INTO t1 VALUES (18446744073709551615); -- source include/restart_mysqld.inc SHOW CREATE TABLE t1; DROP TABLE t1; + + +# Check if we handl offset > column max value properly +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=256; +SHOW VARIABLES LIKE "%auto_inc%"; +# TINYINT +CREATE TABLE t1 (c1 TINYINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/innodb_bug11766634.test b/mysql-test/suite/innodb/t/innodb_bug11766634.test new file mode 100644 index 00000000000..f360c4b1ce1 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug11766634.test @@ -0,0 +1,59 @@ +# Bug 11766634 59783: InnoDB data grows unexpectedly when inserting, +# truncating, inserting the same set of rows. +# +# Scenario: +# create table t1. Insert $recs records. check size of ibdata1. +# drop table t1. create table t1. Insert the same set of $recs +# records. The size of ibdata1 must not increase. +# + +-- source include/not_embedded.inc +-- source include/have_innodb.inc + +create table t1 (f1 char(255)) engine innodb; +let $MYSQLD_DATADIR=`select @@datadir`; +let IBDATA1=$MYSQLD_DATADIR/ibdata1; + +let $recs = 36262; + +--disable_query_log +let $c = $recs; +start transaction; +while ($c) +{ + insert into t1 values ('Hello World'); + dec $c; +} +commit work; +--enable_query_log + +perl; +my $filesize = -s $ENV{'IBDATA1'}; +my $filename = $ENV{MYSQL_TMP_DIR} . '/innodb_bug11766634.txt'; +open F, '>', $filename or die "open(>$filename): $!"; +print F $filesize; +EOF + +drop table t1; +create table t1 (f1 char(255)) engine innodb; + +--disable_query_log +let $c = $recs; +start transaction; +while ($c) +{ + insert into t1 values ('Hello World'); + dec $c; +} +commit work; +--enable_query_log + +perl; +my $filesize = -s $ENV{'IBDATA1'}; +my $filename = $ENV{MYSQL_TMP_DIR} . '/innodb_bug11766634.txt'; +$_=do $filename; +print $_ == $filesize, "\n"; +EOF + +drop table t1; + diff --git a/mysql-test/suite/innodb/t/innodb_bug34300.test b/mysql-test/suite/innodb/t/innodb_bug34300.test index 7331c1233fc..11682ad7828 100644 --- a/mysql-test/suite/innodb/t/innodb_bug34300.test +++ b/mysql-test/suite/innodb/t/innodb_bug34300.test @@ -8,6 +8,7 @@ -- disable_result_log call mtr.add_suppression("InnoDB: Warning: a long semaphore wait:"); +call mtr.add_suppression("the age of the last checkpoint is"); # set packet size and reconnect let $max_packet=`select @@global.max_allowed_packet`; diff --git a/mysql-test/suite/rpl/r/rpl_report_port.result b/mysql-test/suite/rpl/r/rpl_report_port.result new file mode 100644 index 00000000000..ebdd3f889f3 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_report_port.result @@ -0,0 +1,8 @@ +include/master-slave.inc +[connection master] +include/assert.inc [The default value shown for the slave's port number is the actual port number of the slave.] +include/rpl_restart_server.inc [server_number=2 parameters: --report-port=9000] +include/start_slave.inc +[Slave restarted with the report-port set to some value] +include/assert.inc [The value shown for the slave's port number is 9000 which is the value set for report-port.] +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_show_slave_hosts.result b/mysql-test/suite/rpl/r/rpl_show_slave_hosts.result index 2ada5670e04..16d71e125ab 100644 --- a/mysql-test/suite/rpl/r/rpl_show_slave_hosts.result +++ b/mysql-test/suite/rpl/r/rpl_show_slave_hosts.result @@ -6,7 +6,7 @@ START SLAVE IO_THREAD; include/wait_for_slave_io_to_start.inc SHOW SLAVE HOSTS; Server_id Host Port Master_id -3 slave2 DEFAULT_PORT 1 +3 slave2 SLAVE_PORT 1 2 SLAVE_PORT 1 include/stop_slave_io.inc SHOW SLAVE HOSTS; diff --git a/mysql-test/suite/rpl/r/rpl_stm_relay_ign_space.result b/mysql-test/suite/rpl/r/rpl_stm_relay_ign_space.result new file mode 100644 index 00000000000..7820f1ef97f --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_stm_relay_ign_space.result @@ -0,0 +1,5 @@ +include/master-slave.inc +[connection master] +include/assert.inc [Assert that relay log space is close to the limit] +include/diff_tables.inc [master:test.t1,slave:test.t1] +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_report_port-slave.opt b/mysql-test/suite/rpl/t/rpl_report_port-slave.opt new file mode 100644 index 00000000000..ffe2b2e7016 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_report_port-slave.opt @@ -0,0 +1 @@ +--report-port= diff --git a/mysql-test/suite/rpl/t/rpl_report_port.test b/mysql-test/suite/rpl/t/rpl_report_port.test new file mode 100644 index 00000000000..b1b71a2982c --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_report_port.test @@ -0,0 +1,49 @@ +# +#BUG#13333431 : INCORRECT DEFAULT PORT IN 'SHOW SLAVE HOSTS' OUTPUT +# +# ==== Purpose ==== +# +# The test show the default value printed for the slave's port number if the +# --report-port= <some value> is not set on the slave. This is different from +# the present scenario which show 3306 as the default value if the report-port +# is not set on the slave. +# +#====Method==== +# +# Start replication with report port not set.This will give the actual port +# number of the slave (ie. SLAVE_PORT) for the on doing SHOW SLAVE HOSTS on +# the master. +# Restart the slave server with report port set to 9000 and start the slave. +# In this case on doing SHOW SLAVE HOSTS on the master, we get the port number +# of the slave to be 9000. + +source include/master-slave.inc; + +connection master; + +--let $report_port= query_get_value(SHOW SLAVE HOSTS, Port, 1) +--let assert_text= The default value shown for the slave's port number is the actual port number of the slave. +--let assert_cond= $report_port = "$SLAVE_MYPORT" +--source include/assert.inc + +# Start the server with some value being passed to the report_port= <option> +# this will be used incase we have to mask the value of the slave's port +# number in certain situations. + +--let $rpl_server_number= 2 +--let $rpl_server_parameters= --report-port=9000 +--source include/rpl_restart_server.inc + +connection slave; +--source include/start_slave.inc + +--echo [Slave restarted with the report-port set to some value] +connection master; + +# 9000 is the value of the port we should get. +--let $report_port= query_get_value(SHOW SLAVE HOSTS, Port, 1) +--let assert_text= The value shown for the slave's port number is 9000 which is the value set for report-port. +--let assert_cond= $report_port = "9000" +--source include/assert.inc + +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_show_slave_hosts.test b/mysql-test/suite/rpl/t/rpl_show_slave_hosts.test index 105f1873659..7ba5e90b96d 100644 --- a/mysql-test/suite/rpl/t/rpl_show_slave_hosts.test +++ b/mysql-test/suite/rpl/t/rpl_show_slave_hosts.test @@ -25,6 +25,7 @@ let $field= Server_id; # 3 is server_id of slave2. let $condition= ='3'; source include/wait_show_condition.inc; +--replace_column 3 'SLAVE_PORT' --replace_result $SLAVE_MYPORT SLAVE_PORT $DEFAULT_MASTER_PORT DEFAULT_PORT SHOW SLAVE HOSTS; diff --git a/mysql-test/suite/rpl/t/rpl_stm_relay_ign_space-slave.opt b/mysql-test/suite/rpl/t/rpl_stm_relay_ign_space-slave.opt new file mode 100644 index 00000000000..f780540aba8 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_stm_relay_ign_space-slave.opt @@ -0,0 +1 @@ +--relay-log-space-limit=8192 --relay-log-purge --max-relay-log-size=4096 diff --git a/mysql-test/suite/rpl/t/rpl_stm_relay_ign_space.test b/mysql-test/suite/rpl/t/rpl_stm_relay_ign_space.test new file mode 100644 index 00000000000..db6e6bd14bf --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_stm_relay_ign_space.test @@ -0,0 +1,101 @@ +# +# BUG#12400313 / BUG#64503 test case +# +# +# Description +# ----------- +# +# This test case starts the slave server with: +# --relay-log-space-limit=8192 --relay-log-purge --max-relay-log-size=4096 +# +# Then it issues some queries that will cause the slave to reach +# relay-log-space-limit. We lock the table so that the SQL thread is +# not able to purge the log and then we issue some more statements. +# +# The purpose is to show that the IO thread will honor the limits +# while the SQL thread is not able to purge the relay logs, which did +# not happen before this patch. In addition we assert that while +# ignoring the limit (SQL thread needs to rotate before purging), the +# IO thread does not do it in an uncontrolled manner. + +--source include/have_binlog_format_statement.inc +--source include/master-slave.inc +--source include/have_innodb.inc + +--disable_query_log +CREATE TABLE t1 (c1 TEXT) engine=InnoDB; + +INSERT INTO t1 VALUES ('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'); +INSERT INTO t1 VALUES ('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'); +INSERT INTO t1 VALUES ('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'); +INSERT INTO t1 VALUES ('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'); + +--sync_slave_with_master + +# wait for the SQL thread to sleep +--let $show_statement= SHOW PROCESSLIST +--let $field= State +--let $condition= = 'Slave has read all relay log; waiting for the slave I/O thread to update it' +--source include/wait_show_condition.inc + +# now the io thread has set rli->ignore_space_limit +# lets lock the table so that once the SQL thread awakes +# it blocks there and does not set rli->ignore_space_limit +# back to zero +LOCK TABLE t1 WRITE; + +# now issue more statements that will overflow the +# rli->log_space_limit (in this case ~10K) +--connection master + +INSERT INTO t1 VALUES ('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'); +INSERT INTO t1 VALUES ('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'); +INSERT INTO t1 VALUES ('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'); +INSERT INTO t1 VALUES ('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'); +INSERT INTO t1 VALUES ('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'); +INSERT INTO t1 VALUES ('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'); +INSERT INTO t1 VALUES ('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'); +INSERT INTO t1 VALUES ('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'); +INSERT INTO t1 VALUES ('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'); +INSERT INTO t1 VALUES ('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'); + +--connection slave + +# ASSERT that the IO thread waits for the SQL thread to release some +# space before continuing +--let $show_statement= SHOW PROCESSLIST +--let $field= State +--let $condition= LIKE 'Waiting for %' +# before the patch (IO would have transfered everything) +#--let $condition= = 'Waiting for master to send event' +# after the patch (now it waits for space to be freed) +#--let $condition= = 'Waiting for the slave SQL thread to free enough relay log space' +--source include/wait_show_condition.inc + +# without the patch we can uncomment the following two lines and +# watch the IO thread synchronize with the master, thus writing +# relay logs way over the space limit +#--connection master +#--source include/sync_slave_io_with_master.inc + +## ASSERT that the IO thread has honored the limit+few bytes required to be able to purge +--let $relay_log_space_while_sql_is_executing = query_get_value(SHOW SLAVE STATUS, Relay_Log_Space, 1) +--let $relay_log_space_limit = query_get_value(SHOW VARIABLES LIKE "relay_log_space_limit", Value, 1) +--let $assert_text= Assert that relay log space is close to the limit +--let $assert_cond= $relay_log_space_while_sql_is_executing <= $relay_log_space_limit * 1.15 +--source include/assert.inc + +# unlock the table and let SQL thread continue applying events +UNLOCK TABLES; + +--connection master +--sync_slave_with_master +--let $diff_tables=master:test.t1,slave:test.t1 +--source include/diff_tables.inc + +--connection master +DROP TABLE t1; +--enable_query_log +--sync_slave_with_master + +--source include/rpl_end.inc diff --git a/mysql-test/suite/sys_vars/t/report_port_basic.test b/mysql-test/suite/sys_vars/t/report_port_basic.test index 122c92c5446..903289230bd 100644 --- a/mysql-test/suite/sys_vars/t/report_port_basic.test +++ b/mysql-test/suite/sys_vars/t/report_port_basic.test @@ -2,18 +2,19 @@ # # only global # ---replace_result $DEFAULT_MASTER_PORT DEFAULT_MASTER_PORT +--replace_regex s/[0-9]+/DEFAULT_MASTER_PORT/ select @@global.report_port; --error ER_INCORRECT_GLOBAL_LOCAL_VAR select @@session.report_port; ---replace_result $DEFAULT_MASTER_PORT DEFAULT_MASTER_PORT +--replace_column 2 'DEFAULT_MASTER_PORT' show global variables like 'report_port'; ---replace_result $DEFAULT_MASTER_PORT DEFAULT_MASTER_PORT +--replace_column 2 'DEFAULT_MASTER_PORT' show session variables like 'report_port'; ---replace_result $DEFAULT_MASTER_PORT DEFAULT_MASTER_PORT +--replace_column 2 'DEFAULT_MASTER_PORT' select * from information_schema.global_variables where variable_name='report_port'; ---replace_result $DEFAULT_MASTER_PORT DEFAULT_MASTER_PORT +--replace_column 2 'DEFAULT_MASTER_PORT' select * from information_schema.session_variables where variable_name='report_port'; +--replace_column 2 'DEFAULT_MASTER_PORT' # # show that it's read-only diff --git a/mysql-test/t/cache_innodb-master.opt b/mysql-test/t/cache_innodb-master.opt deleted file mode 100644 index 6672b4ddce1..00000000000 --- a/mysql-test/t/cache_innodb-master.opt +++ /dev/null @@ -1 +0,0 @@ ---query_cache_size=1M diff --git a/mysql-test/t/ctype_ucs.test b/mysql-test/t/ctype_ucs.test index e93a2ab0fbf..f3557e36a58 100644 --- a/mysql-test/t/ctype_ucs.test +++ b/mysql-test/t/ctype_ucs.test @@ -796,5 +796,14 @@ SET collation_connection=ucs2_general_ci; SET NAMES latin1; --echo # +--echo # Bug #13832953 MY_STRNXFRM_UNICODE: ASSERTION `SRC' FAILED +--echo # +CREATE TABLE t1 (c1 SET('','') CHARACTER SET ucs2); +INSERT INTO t1 VALUES (''); +SELECT COALESCE(c1) FROM t1 ORDER BY 1; +DROP TABLE t1; + + +--echo # --echo # End of 5.5 tests --echo # diff --git a/mysql-test/t/filesort_debug.test b/mysql-test/t/filesort_debug.test index 817b41ab16d..f89d46a7bd0 100644 --- a/mysql-test/t/filesort_debug.test +++ b/mysql-test/t/filesort_debug.test @@ -57,3 +57,46 @@ disconnect con2; --source include/wait_until_count_sessions.inc SET DEBUG_SYNC= "RESET"; DROP TABLE t1; + +--echo # +--echo # Bug#13832772 ASSERTION `THD->IS_ERROR() || KILL_ERRNO' +--echo # FAILED IN FILESORT/MYSQL_DELETE +--echo # + +CREATE TABLE t1 ( + c1 BLOB, + c2 TEXT, + c3 TEXT, + c4 TEXT, + c5 TEXT, + c6 TEXT, + c7 TEXT, + c8 BLOB, + c9 TEXT, + c19 TEXT, + pk INT, + c20 TEXT, + c21 BLOB, + c22 TEXT, + c23 TEXT, + c24 TEXT, + c25 TEXT, + c26 BLOB, + c27 TEXT, + c28 TEXT, + primary key (pk) +); + +CALL mtr.add_suppression("Out of sort memory"); + +--error ER_OUT_OF_SORTMEMORY +DELETE IGNORE FROM t1 ORDER BY c26,c7,c23,c4,c25,c5,c20, +c19,c21,c8,c1,c27,c28,c3,c9,c22,c24,c6,c2,pk LIMIT 2; +SHOW WARNINGS; + +--error ER_OUT_OF_SORTMEMORY +DELETE FROM t1 ORDER BY c26,c7,c23,c4,c25,c5,c20, +c19,c21,c8,c1,c27,c28,c3,c9,c22,c24,c6,c2,pk LIMIT 2; +SHOW WARNINGS; + +DROP TABLE t1; diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test index 9e6628a1972..97bd628e2f9 100644 --- a/mysql-test/t/func_in.test +++ b/mysql-test/t/func_in.test @@ -562,5 +562,18 @@ SELECT 1 IN (YEAR(FROM_UNIXTIME(NULL)) ,1); --echo # +--echo # +--echo # Bug#13012483: EXPLAIN EXTENDED, PREPARED STATEMENT, CRASH IN CHECK_SIMPLE_EQUALITY +--echo # + +CREATE TABLE t1 (a INT); +PREPARE s FROM "SELECT 1 FROM t1 WHERE 1 < ALL (SELECT @:= (1 IN (SELECT 1 FROM t1)) FROM t1)"; +EXECUTE s; + +DROP TABLE t1; + +--echo # End of test BUG#13012483 + +--echo # --echo End of 5.1 tests diff --git a/mysql-test/t/partition_cache.test b/mysql-test/t/partition_cache.test new file mode 100644 index 00000000000..b5d19796b7c --- /dev/null +++ b/mysql-test/t/partition_cache.test @@ -0,0 +1,21 @@ +# t/cache_innodb.test +# +# Last update: +# 2006-07-26 ML test refactored (MySQL 5.1) +# main code t/innodb_cache.test --> include/query_cache.inc +# new wrapper t/cache_innodb.test +# + +--source include/have_query_cache.inc + +--source include/have_innodb.inc +--source include/have_partition.inc +let $engine_type= InnoDB; +# Using SELECT to get a space as first character. +let $partitions_a= `SELECT ' PARTITION BY KEY (a) PARTITIONS 3'`; +let $partitions_id= `SELECT ' PARTITION BY HASH (id) PARTITIONS 3'`; +let $partitions_s1= `SELECT ' PARTITION BY KEY (s1) PARTITIONS 3'`; +# partitioning does not support FOREIGN KEYs +let $test_foreign_keys= 0; + +--source include/query_cache.inc diff --git a/mysql-test/t/partition_innodb.test b/mysql-test/t/partition_innodb.test index 28a9c6e2e64..2756f7d1ebb 100644 --- a/mysql-test/t/partition_innodb.test +++ b/mysql-test/t/partition_innodb.test @@ -12,6 +12,35 @@ drop table if exists t1, t2; let $MYSQLD_DATADIR= `SELECT @@datadir`; --echo # +--echo # Bug#13694811: THE OPTIMIZER WRONGLY USES THE FIRST +--echo # INNODB PARTITION STATISTICS +--echo # + +CREATE TABLE t1 +(a INT, + b varchar(64), + PRIMARY KEY (a), + KEY (b)) +ENGINE = InnoDB +PARTITION BY RANGE (a) +SUBPARTITION BY HASH (a) SUBPARTITIONS 10 +(PARTITION pNeg VALUES LESS THAN (0), + PARTITION p0 VALUES LESS THAN (1000), + PARTITION pMAX VALUES LESS THAN MAXVALUE); + +--echo # Only one row in the first 10 subpartitions +INSERT INTO t1 VALUES (-1, 'Only negative pk value'); + +INSERT INTO t1 VALUES (0, 'Mod Zero'), (1, 'One'), (2, 'Two'), (3, 'Three'), +(10, 'Zero'), (11, 'Mod One'), (12, 'Mod Two'), (13, 'Mod Three'), +(20, '0'), (21, '1'), (22, '2'), (23, '3'), +(4, '4'), (5, '5'), (6, '6'), (7, '7'), (8, '8'), (9, '9'); +INSERT INTO t1 SELECT a + 30, b FROM t1 WHERE a >= 0; +ANALYZE TABLE t1; +EXPLAIN SELECT b FROM t1 WHERE b between 'L' and 'N' AND a > -100; +DROP TABLE t1; + +--echo # --echo # Bug#56287: crash when using Partition datetime in sub in query --echo # diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index b89c4974b20..b2f64b8172b 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -3377,6 +3377,94 @@ disconnect con1; SELECT * FROM (SELECT 1 UNION SELECT 2) t; +--echo +--echo # Bug#13805127: Stored program cache produces wrong result in same THD +--echo + +PREPARE s1 FROM +" +SELECT c1, t2.c2, count(c3) +FROM + ( + SELECT 3 as c2 FROM dual WHERE @x = 1 + UNION + SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 + ) AS t1, + ( + SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual + UNION + SELECT '2012-03-01 02:00:00', 3, 2 FROM dual + UNION + SELECT '2012-03-01 01:00:00', 2, 1 FROM dual + ) AS t2 +WHERE t2.c2 = t1.c2 +GROUP BY c1, c2 +"; + +--echo +SET @x = 1; +SELECT c1, t2.c2, count(c3) +FROM + ( + SELECT 3 as c2 FROM dual WHERE @x = 1 + UNION + SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 + ) AS t1, + ( + SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual + UNION + SELECT '2012-03-01 02:00:00', 3, 2 FROM dual + UNION + SELECT '2012-03-01 01:00:00', 2, 1 FROM dual + ) AS t2 +WHERE t2.c2 = t1.c2 +GROUP BY c1, c2; +--echo +EXECUTE s1; + +--echo +SET @x = 2; +SELECT c1, t2.c2, count(c3) +FROM + ( + SELECT 3 as c2 FROM dual WHERE @x = 1 + UNION + SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 + ) AS t1, + ( + SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual + UNION + SELECT '2012-03-01 02:00:00', 3, 2 FROM dual + UNION + SELECT '2012-03-01 01:00:00', 2, 1 FROM dual + ) AS t2 +WHERE t2.c2 = t1.c2 +GROUP BY c1, c2; +--echo +EXECUTE s1; + +--echo +SET @x = 1; +SELECT c1, t2.c2, count(c3) +FROM + ( + SELECT 3 as c2 FROM dual WHERE @x = 1 + UNION + SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 + ) AS t1, + ( + SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual + UNION + SELECT '2012-03-01 02:00:00', 3, 2 FROM dual + UNION + SELECT '2012-03-01 01:00:00', 2, 1 FROM dual + ) AS t2 +WHERE t2.c2 = t1.c2 +GROUP BY c1, c2; +--echo +EXECUTE s1; + +DEALLOCATE PREPARE s1; --echo # --echo # End of 5.5 tests. diff --git a/mysql-test/t/sp-bugs.test b/mysql-test/t/sp-bugs.test index a23ccea8189..3ab1689e8b2 100644 --- a/mysql-test/t/sp-bugs.test +++ b/mysql-test/t/sp-bugs.test @@ -138,6 +138,7 @@ DROP FUNCTION db1.f1; DROP TABLE db1.t1; DROP DATABASE db1; DROP DATABASE db2; +USE test; --echo # --echo # Bug#13105873:valgrind warning:possible crash in foreign @@ -161,5 +162,69 @@ CALL p1(); --echo # below stmt should not return valgrind warnings CALL p1(); DROP DATABASE testdb; +USE test; --echo End of 5.1 tests + +--echo # +--echo # Bug#11763507 - 56224: FUNCTION NAME IS CASE-SENSITIVE +--echo # +SET @@SQL_MODE = ''; +DELIMITER $; +CREATE FUNCTION testf_bug11763507() RETURNS INT +BEGIN + RETURN 0; +END +$ + +CREATE PROCEDURE testp_bug11763507() +BEGIN + SELECT "PROCEDURE testp_bug11763507"; +END +$ + +DELIMITER ;$ + +# STORED FUNCTIONS +SELECT testf_bug11763507(); +SELECT TESTF_bug11763507(); + +--replace_column 5 # 6 # +SHOW FUNCTION STATUS LIKE 'testf_bug11763507'; +--replace_column 5 # 6 # +SHOW FUNCTION STATUS WHERE NAME='testf_bug11763507'; +--replace_column 5 # 6 # +SHOW FUNCTION STATUS LIKE 'TESTF_bug11763507'; +--replace_column 5 # 6 # +SHOW FUNCTION STATUS WHERE NAME='TESTF_bug11763507'; + +SHOW CREATE FUNCTION testf_bug11763507; +SHOW CREATE FUNCTION TESTF_bug11763507; + +# STORED PROCEDURE +CALL testp_bug11763507(); +CALL TESTP_bug11763507(); + +--replace_column 5 # 6 # +SHOW PROCEDURE STATUS LIKE 'testp_bug11763507'; +--replace_column 5 # 6 # +SHOW PROCEDURE STATUS WHERE NAME='testp_bug11763507'; +--replace_column 5 # 6 # +SHOW PROCEDURE STATUS LIKE 'TESTP_bug11763507'; +--replace_column 5 # 6 # +SHOW PROCEDURE STATUS WHERE NAME='TESTP_bug11763507'; + +SHOW CREATE PROCEDURE testp_bug11763507; +SHOW CREATE PROCEDURE TESTP_bug11763507; + +# INFORMATION SCHEMA +SELECT specific_name FROM INFORMATION_SCHEMA.ROUTINES WHERE specific_name LIKE 'testf_bug11763507'; +SELECT specific_name FROM INFORMATION_SCHEMA.ROUTINES WHERE specific_name LIKE 'TESTF_bug11763507'; + +SELECT specific_name FROM INFORMATION_SCHEMA.ROUTINES WHERE specific_name='testf_bug11763507'; +SELECT specific_name FROM INFORMATION_SCHEMA.ROUTINES WHERE specific_name='TESTF_bug11763507'; + +DROP PROCEDURE testp_bug11763507; +DROP FUNCTION testf_bug11763507; + +--echo #END OF BUG#11763507 test. diff --git a/mysql-test/t/sp-code.test b/mysql-test/t/sp-code.test index 4c1b287769d..d4b63a73920 100644 --- a/mysql-test/t/sp-code.test +++ b/mysql-test/t/sp-code.test @@ -702,3 +702,35 @@ SET SESSION debug_dbug="-d,bug23032_emit_warning"; DROP PROCEDURE p1; DROP TABLE t1; + +--echo # +--echo # Bug#11763507 - 56224: FUNCTION NAME IS CASE-SENSITIVE +--echo # +SET @@SQL_MODE = ''; +DELIMITER $; +CREATE FUNCTION testf_bug11763507() RETURNS INT +BEGIN + RETURN 0; +END +$ + +CREATE PROCEDURE testp_bug11763507() +BEGIN + SELECT "PROCEDURE testp_bug11763507"; +END +$ + +DELIMITER ;$ + +# STORED FUNCTIONS +SHOW FUNCTION CODE testf_bug11763507; +SHOW FUNCTION CODE TESTF_bug11763507; + +# STORED PROCEDURE +SHOW PROCEDURE CODE testp_bug11763507; +SHOW PROCEDURE CODE TESTP_bug11763507; + +DROP PROCEDURE testp_bug11763507; +DROP FUNCTION testf_bug11763507; + +--echo #END OF BUG#11763507 test. diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 02182dbf0c6..ef99a8a521a 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -9039,6 +9039,42 @@ DROP PROCEDURE p1; DROP PROCEDURE p2; DROP TABLE t1; +--echo +--echo # Bug#13805127: Stored program cache produces wrong result in same THD +--echo + +delimiter |; + +CREATE PROCEDURE p1(x INT UNSIGNED) +BEGIN + SELECT c1, t2.c2, count(c3) + FROM + ( + SELECT 3 as c2 FROM dual WHERE x = 1 + UNION + SELECT 2 FROM dual WHERE x = 1 OR x = 2 + ) AS t1, + ( + SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual + UNION + SELECT '2012-03-01 02:00:00', 3, 2 FROM dual + UNION + SELECT '2012-03-01 01:00:00', 2, 1 FROM dual + ) AS t2 + WHERE t2.c2 = t1.c2 + GROUP BY c1, c2 + ; +END| + +delimiter ;| + +--echo +CALL p1(1); +CALL p1(2); +CALL p1(1); + +DROP PROCEDURE p1; + --echo # End of 5.5 test diff --git a/mysql-test/t/sp_notembedded.test b/mysql-test/t/sp_notembedded.test index 87de5063d00..39e7102079c 100644 --- a/mysql-test/t/sp_notembedded.test +++ b/mysql-test/t/sp_notembedded.test @@ -427,6 +427,7 @@ SELECT `my.db`.f1(2); connection default; disconnect addcon; DROP DATABASE `my.db`; +USE test; # # Restore global concurrent_insert value. Keep in the end of the test file. @@ -437,3 +438,26 @@ set @@global.concurrent_insert= @old_concurrent_insert; # Wait till all disconnects are completed --source include/wait_until_count_sessions.inc +--echo # +--echo # Bug#11763507 - 56224: FUNCTION NAME IS CASE-SENSITIVE +--echo # +SET @@SQL_MODE = ''; +DELIMITER $; + +CREATE EVENT teste_bug11763507 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR +DO SELECT 1 $ + +DELIMITER ;$ +# EVENTS +--replace_column 6 # 7 # 8 # 9 # +SHOW EVENTS LIKE 'teste_bug11763507'; +--replace_column 6 # 7 # 8 # 9 # +SHOW EVENTS LIKE 'TESTE_bug11763507'; + +--replace_column 4 # +SHOW CREATE EVENT teste_bug11763507; +--replace_column 4 # +SHOW CREATE EVENT TESTE_bug11763507; + +DROP EVENT teste_bug11763507; +--echo #END OF BUG#11763507 test. diff --git a/mysql-test/t/sql_mode.test b/mysql-test/t/sql_mode.test index 27956bc8877..b46da77554a 100644 --- a/mysql-test/t/sql_mode.test +++ b/mysql-test/t/sql_mode.test @@ -359,3 +359,157 @@ SELECT * FROM mysql.columns_priv WHERE Host = 'localhost' AND User LIKE 'user_%P # Cleanup DROP TABLE t1; DROP TABLE t2; + + +--echo +--echo # +--echo # Test for Bug#12601974 - STORED PROCEDURE SQL_MODE=NO_BACKSLASH_ESCAPES +--echo # IGNORED AND BREAKS REPLICATION +--echo # + +--disable_warnings +DROP TABLE IF EXISTS test_table; +DROP FUNCTION IF EXISTS test_function; +--enable_warnings + +CREATE TABLE test_table (c1 CHAR(50)); + +SET @org_mode=@@sql_mode; + +SET @@sql_mode=''; + +PREPARE insert_stmt FROM 'INSERT INTO test_table VALUES (?)'; +PREPARE update_stmt FROM 'UPDATE test_table SET c1= ? WHERE c1= ?'; +DELIMITER $; +CREATE FUNCTION test_function(var CHAR(50)) RETURNS CHAR(50) +BEGIN + DECLARE char_val CHAR(50); + SELECT c1 INTO char_val FROM test_table WHERE c1=var; + RETURN char_val; +END +$ +DELIMITER ;$ + +SET @var1='abcd\'ef'; +SET @var2='abcd\"ef'; +SET @var3='abcd\bef'; +SET @var4='abcd\nef'; +SET @var5='abcd\ref'; +SET @var6='abcd\tef'; +SET @var7='abcd\\ef'; +SET @var8='abcd\%ef'; +SET @var9='abcd\_ef'; + +SET @to_var1='wxyz\'ef'; +SET @to_var2='wxyz\"ef'; +SET @to_var3='wxyz\bef'; +SET @to_var4='wxyz\nef'; +SET @to_var5='wxyz\ref'; +SET @to_var6='wxyz\tef'; +SET @to_var7='wxyz\\ef'; +SET @to_var8='wxyz\%ef'; +SET @to_var9='wxyz\_ef'; + +--echo # STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT +EXECUTE insert_stmt USING @var1; +EXECUTE insert_stmt USING @var2; +EXECUTE insert_stmt USING @var3; +EXECUTE insert_stmt USING @var4; +EXECUTE insert_stmt USING @var5; +EXECUTE insert_stmt USING @var6; +EXECUTE insert_stmt USING @var7; +EXECUTE insert_stmt USING @var8; +EXECUTE insert_stmt USING @var9; + +SELECT * FROM test_table; + +EXECUTE update_stmt USING @to_var1, @var1; +EXECUTE update_stmt USING @to_var2, @var2; +EXECUTE update_stmt USING @to_var3, @var3; +EXECUTE update_stmt USING @to_var4, @var4; +EXECUTE update_stmt USING @to_var5, @var5; +EXECUTE update_stmt USING @to_var6, @var6; +EXECUTE update_stmt USING @to_var7, @var7; +EXECUTE update_stmt USING @to_var8, @var8; +EXECUTE update_stmt USING @to_var9, @var9; + +SELECT * FROM test_table; + +--echo +--echo # END OF CASE - STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT + +--echo # STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING +select test_function(@to_var1); +SELECT test_function(@to_var2); +SELECT test_function(@to_var3); +SELECT test_function(@to_var4); +SELECT test_function(@to_var5); +SELECT test_function(@to_var6); +SELECT test_function(@to_var7); +SELECT test_function(@to_var8); +SELECT test_function(@to_var9); + +--echo +--echo # END OF CASE - STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING +DELETE FROM test_table; +DROP FUNCTION test_function; + +SET @@sql_mode='NO_BACKSLASH_ESCAPES'; +DELIMITER $; +CREATE FUNCTION test_function(var CHAR(50)) RETURNS CHAR(50) +BEGIN + DECLARE char_val CHAR(50); + SELECT c1 INTO char_val FROM test_table WHERE c1=var; + RETURN char_val; +END +$ +DELIMITER ;$ + +--echo # STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT +EXECUTE insert_stmt USING @var1; +EXECUTE insert_stmt USING @var2; +EXECUTE insert_stmt USING @var3; +EXECUTE insert_stmt USING @var4; +EXECUTE insert_stmt USING @var5; +EXECUTE insert_stmt USING @var6; +EXECUTE insert_stmt USING @var7; +EXECUTE insert_stmt USING @var8; +EXECUTE insert_stmt USING @var9; + +SELECT * FROM test_table; + +EXECUTE update_stmt USING @to_var1, @var1; +EXECUTE update_stmt USING @to_var2, @var2; +EXECUTE update_stmt USING @to_var3, @var3; +EXECUTE update_stmt USING @to_var4, @var4; +EXECUTE update_stmt USING @to_var5, @var5; +EXECUTE update_stmt USING @to_var6, @var6; +EXECUTE update_stmt USING @to_var7, @var7; +EXECUTE update_stmt USING @to_var8, @var8; +EXECUTE update_stmt USING @to_var9, @var9; + +SELECT * FROM test_table; + +--echo +--echo # END OF CASE - STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT + +--echo # STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING +select test_function(@to_var1); +SELECT test_function(@to_var2); +SELECT test_function(@to_var3); +SELECT test_function(@to_var4); +SELECT test_function(@to_var5); +SELECT test_function(@to_var6); +SELECT test_function(@to_var7); +SELECT test_function(@to_var8); +SELECT test_function(@to_var9); + +--echo +--echo # END OF CASE - STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING + +DROP TABLE test_table; +DROP FUNCTION test_function; +SET @@sql_mode= @org_mode; + +--echo +--echo #End of Test for Bug#12601974 diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 458aa88f071..f369f10a5e4 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -3542,6 +3542,61 @@ SET SESSION sql_mode=@old_sql_mode; DEALLOCATE PREPARE stmt; DROP TABLE t1; +--echo # +--echo # Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER +--echo # + +CREATE TABLE t1(a1 int); +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2(a1 int); +INSERT INTO t2 VALUES (3); + +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; + +## All these are subject to the transformation +## '1 < some (...)' => '1 < max(...)' +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2); +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2); +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2); +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2); + +SET SESSION sql_mode=@old_sql_mode; + +DROP TABLE t1, t2; + +--echo # +--echo # Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER +--echo # + +create table t2(i int); +insert into t2 values(0); + +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; + +CREATE VIEW v1 AS +SELECT 'f' FROM t2 UNION SELECT 'x' FROM t2 +; + +CREATE TABLE t1 ( + pk int NOT NULL, + col_varchar_key varchar(1) DEFAULT NULL, + PRIMARY KEY (pk), + KEY col_varchar_key (col_varchar_key) +); + +SELECT t1.pk +FROM t1 +WHERE t1.col_varchar_key < ALL ( SELECT * FROM v1 ) +; + +SET SESSION sql_mode=@old_sql_mode; + +drop table t2, t1; +drop view v1; + --echo End of 5.0 tests. # diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 6208ae7a057..0585073a6f5 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -4521,6 +4521,18 @@ DROP TABLE t1; disconnect con2; disconnect con3; + +--echo # +--echo # Bug#12626844: WRONG ERROR MESSAGE WHILE CREATING A VIEW ON A +--echo # NON EXISTING DATABASE +--echo # + +--disable_warnings +DROP DATABASE IF EXISTS nodb; +--enable_warnings +--error ER_BAD_DB_ERROR +CREATE VIEW nodb.a AS SELECT 1; + # Check that all connections opened by test cases in this file are really # gone so execution of other tests won't be affected by their presence. --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/xa.test b/mysql-test/t/xa.test index ab589ae6727..1709886eb0c 100644 --- a/mysql-test/t/xa.test +++ b/mysql-test/t/xa.test @@ -298,7 +298,6 @@ CREATE TABLE t1 (a INT) engine=InnoDB; XA START 'a'; INSERT INTO t1 VALUES (1); ---error ER_XAER_RMFAIL SAVEPOINT savep; XA END 'a'; |