diff options
Diffstat (limited to 'mysql-test')
107 files changed, 3242 insertions, 231 deletions
diff --git a/mysql-test/mysql-test-run.sh b/mysql-test/mysql-test-run.sh index 137e99f48b3..8800055a3bb 100644 --- a/mysql-test/mysql-test-run.sh +++ b/mysql-test/mysql-test-run.sh @@ -323,11 +323,15 @@ while test $# -gt 0; do $ECHO "Note: you will get more meaningful output on a source distribution compiled with debugging option when running tests with --client-gdb option" fi DO_CLIENT_GDB=1 + EXTRA_MASTER_MYSQLD_OPT="$EXTRA_MASTER_MYSQLD_OPT --gdb" + EXTRA_SLAVE_MYSQLD_OPT="$EXTRA_SLAVE_MYSQLD_OPT --gdb" ;; --manual-gdb ) DO_GDB=1 MANUAL_GDB=1 USE_RUNNING_SERVER="" + EXTRA_MASTER_MYSQLD_OPT="$EXTRA_MASTER_MYSQLD_OPT --gdb" + EXTRA_SLAVE_MYSQLD_OPT="$EXTRA_SLAVE_MYSQLD_OPT --gdb" ;; --ddd ) if [ x$BINARY_DIST = x1 ] ; then @@ -335,6 +339,8 @@ while test $# -gt 0; do fi DO_DDD=1 USE_RUNNING_SERVER="" + EXTRA_MASTER_MYSQLD_OPT="$EXTRA_MASTER_MYSQLD_OPT --gdb" + EXTRA_SLAVE_MYSQLD_OPT="$EXTRA_SLAVE_MYSQLD_OPT --gdb" ;; --valgrind) VALGRIND="valgrind --alignment=8 --leak-check=yes --num-callers=16" @@ -567,7 +573,9 @@ error () { } error_is () { - $CAT < $TIMEFILE | $SED -e 's/.* At line \(.*\)\: \(.*\)/ \>\> Error at line \1: \2<\</' | $HEAD -1 + $ECHO "Errors are (from $TIMEFILE) :" + $CAT < $TIMEFILE + $ECHO "(the last line(s) may be the ones that caused the die() in mysqltest)" } prefix_to_8() { @@ -1068,6 +1076,16 @@ stop_slave () fi } +stop_slave_threads () +{ + eval "this_slave_running=\$SLAVE$1_RUNNING" + slave_ident="slave$1" + if [ x$this_slave_running = x1 ] + then + $MYSQLADMIN --no-defaults -uroot --socket=$MYSQL_TMP_DIR/$slave_ident.sock stop-slave > /dev/null 2>&1 + fi +} + stop_master () { if [ x$MASTER_RUNNING = x1 ] @@ -1161,6 +1179,12 @@ run_testcase () return fi + # Stop all slave threads, so that we don't have useless reconnection attempts + # and error messages in case the slave and master servers restart. + stop_slave_threads + stop_slave_threads 1 + stop_slave_threads 2 + if [ -z "$USE_RUNNING_SERVER" ] ; then if [ -f $master_opt_file ] ; diff --git a/mysql-test/r/alias.result b/mysql-test/r/alias.result index 3954c2d0170..5ed10b58929 100644 --- a/mysql-test/r/alias.result +++ b/mysql-test/r/alias.result @@ -59,3 +59,15 @@ SELECT ELT(FIELD(kundentyp,'PP','PPA','PG','PGA','FK','FKA','FP','FPA','K','KA', Kundentyp kategorie Privat (Private Nutzung) Mobilfunk drop table t1; +CREATE TABLE t1 ( +AUFNR varchar(12) NOT NULL default '', +PLNFL varchar(6) NOT NULL default '', +VORNR varchar(4) NOT NULL default '', +xstatus_vor smallint(5) unsigned NOT NULL default '0', +); +INSERT INTO t1 VALUES ('40004712','000001','0010',9); +INSERT INTO t1 VALUES ('40004712','000001','0020',0); +UPDATE t1 SET t1.xstatus_vor = Greatest(t1.xstatus_vor,1) WHERE t1.aufnr = +"40004712" AND t1.plnfl = "000001" AND t1.vornr > "0010" ORDER BY t1.vornr +ASC LIMIT 1; +drop table t1; diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index 7fa5a0f601f..9d3a0461f9f 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -320,3 +320,72 @@ select a,hex(a) from t1; a hex(a) ÔÅÓÔ D4C5D3D4 drop table t1; +CREATE TABLE t1 ( +Host varchar(16) binary NOT NULL default '', +User varchar(16) binary NOT NULL default '', +PRIMARY KEY (Host,User) +) TYPE=MyISAM; +ALTER TABLE t1 DISABLE KEYS; +LOCK TABLES t1 WRITE; +INSERT INTO t1 VALUES ('localhost','root'),('localhost',''),('games','monty'); +SHOW INDEX FROM t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE +t1 0 PRIMARY 2 User A 3 NULL NULL BTREE +ALTER TABLE t1 ENABLE KEYS; +UNLOCK TABLES; +CHECK TABLES t1; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; +CREATE TABLE t1 ( +Host varchar(16) binary NOT NULL default '', +User varchar(16) binary NOT NULL default '', +PRIMARY KEY (Host,User), +KEY (Host) +) TYPE=MyISAM; +ALTER TABLE t1 DISABLE KEYS; +SHOW INDEX FROM t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE +t1 0 PRIMARY 2 User A 0 NULL NULL BTREE +t1 1 Host 1 Host A NULL NULL NULL BTREE disabled +LOCK TABLES t1 WRITE; +INSERT INTO t1 VALUES ('localhost','root'),('localhost',''); +SHOW INDEX FROM t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE +t1 0 PRIMARY 2 User A 2 NULL NULL BTREE +t1 1 Host 1 Host A NULL NULL NULL BTREE disabled +ALTER TABLE t1 ENABLE KEYS; +SHOW INDEX FROM t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE +t1 0 PRIMARY 2 User A 2 NULL NULL BTREE +t1 1 Host 1 Host A 1 NULL NULL BTREE +UNLOCK TABLES; +CHECK TABLES t1; +Table Op Msg_type Msg_text +test.t1 check status OK +LOCK TABLES t1 WRITE; +ALTER TABLE t1 RENAME t2; +UNLOCK TABLES; +select * from t2; +Host User +localhost +localhost root +DROP TABLE t2; +CREATE TABLE t1 ( +Host varchar(16) binary NOT NULL default '', +User varchar(16) binary NOT NULL default '', +PRIMARY KEY (Host,User), +KEY (Host) +) TYPE=MyISAM; +LOCK TABLES t1 WRITE; +ALTER TABLE t1 DISABLE KEYS; +SHOW INDEX FROM t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE +t1 0 PRIMARY 2 User A 0 NULL NULL BTREE +t1 1 Host 1 Host A NULL NULL NULL BTREE disabled +DROP TABLE t1; diff --git a/mysql-test/r/convert.result b/mysql-test/r/convert.result new file mode 100644 index 00000000000..f8dad8c69ba --- /dev/null +++ b/mysql-test/r/convert.result @@ -0,0 +1,17 @@ +select @@convert_character_set; +@@convert_character_set + +select @@global.convert_character_set; +@@global.convert_character_set + +show variables like "%convert_character_set%"; +Variable_name Value +convert_character_set +SET CHARACTER SET cp1251_koi8; +select @@convert_character_set; +@@convert_character_set +cp1251_koi8 +SET CHARACTER SET DEFAULT; +select @@convert_character_set; +@@convert_character_set + diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 9a0eca52b84..2f907880cbe 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -310,3 +310,5 @@ t1 CREATE TABLE `t1` ( ) TYPE=MyISAM CHARSET=latin1 SET SESSION table_type=default; drop table t1; +create table t1 select x'4132'; +drop table t1; diff --git a/mysql-test/r/ctype_cp1251.result b/mysql-test/r/ctype_cp1251.result new file mode 100644 index 00000000000..a1f860e1f83 --- /dev/null +++ b/mysql-test/r/ctype_cp1251.result @@ -0,0 +1,24 @@ +drop table if exists t1; +create table t1 (a varchar(10) not null); +insert into t1 values ("a"),("ab"),("abc"); +select * from t1; +a +a +ab +abc +select a, left(a,1) as b from t1; +a b +a a +ab a +abc a +select a, left(a,1) as b from t1 group by a; +a b +a a +ab a +abc a +SELECT DISTINCT RIGHT(a,1) from t1; +RIGHT(a,1) +a +b +c +drop table t1; diff --git a/mysql-test/r/ctype_latin1_de.result b/mysql-test/r/ctype_latin1_de.result index 7503c16a85e..7f116c1fc52 100644 --- a/mysql-test/r/ctype_latin1_de.result +++ b/mysql-test/r/ctype_latin1_de.result @@ -196,6 +196,9 @@ strcmp('ßa','ss') strcmp('ssa','ß') strcmp('sssb','sßa') strcmp('ß','s') select strcmp('u','öa'),strcmp('u','ö'); strcmp('u','öa') strcmp('u','ö') 1 1 +select strcmp('sä', 'ßa'), strcmp('aä', 'äx'); +strcmp('sä', 'ßa') strcmp('aä', 'äx') +-1 -1 create table t1 (a varchar(10), key(a), fulltext (a)); insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test"); select * from t1 where a like "abc%"; diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index c5841f28830..3c6769e797b 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -161,7 +161,7 @@ SELECT DISTINCT UserId FROM t1 WHERE UserId=22; UserId drop table t1; CREATE TABLE t1 (a int(10) unsigned not null primary key,b int(10) unsigned); -INSERT INTO t1 VALUES (1,1),(2,1); +INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1); CREATE TABLE t2 (a int(10) unsigned not null, key (A)); INSERT INTO t2 VALUES (1),(2); CREATE TABLE t3 (a int(10) unsigned, key(A), b text); @@ -189,7 +189,7 @@ insert into t4 select * from t3; insert into t3 select * from t4; explain select distinct t1.a from t1,t3 where t1.a=t3.a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 2 Using index; Using temporary +1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 4 Using index; Using temporary 1 SIMPLE t3 ref a a 5 test.t1.a 10 Using where; Using index; Distinct select distinct t1.a from t1,t3 where t1.a=t3.a; a @@ -200,16 +200,16 @@ select distinct 1 from t1,t3 where t1.a=t3.a; 1 explain SELECT distinct t1.a from t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index +1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index explain SELECT distinct t1.a from t1 order by a desc; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index +1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index explain SELECT t1.a from t1 group by a order by a desc; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index +1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index explain SELECT distinct t1.a from t1 order by a desc limit 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index +1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index explain SELECT distinct a from t3 order by a desc limit 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 index NULL a 5 NULL 204 Using index @@ -418,3 +418,12 @@ SELECT DISTINCT t1.a, t2.b FROM t1, t2 WHERE t1.a=1 ORDER BY t2.c; a b 1 4 DROP TABLE t1,t2; +CREATE table t1 ( `id` int(11) NOT NULL auto_increment, `name` varchar(50) NOT NULL default '', PRIMARY KEY (`id`)) TYPE=MyISAM AUTO_INCREMENT=3 ; +INSERT INTO t1 VALUES (1, 'aaaaa'); +INSERT INTO t1 VALUES (3, 'aaaaa'); +INSERT INTO t1 VALUES (2, 'eeeeeee'); +select distinct left(name,1) as name from t1; +name +a +e +drop table t1; diff --git a/mysql-test/r/drop.result b/mysql-test/r/drop.result index 9555ea452ca..6029cc90d1d 100644 --- a/mysql-test/r/drop.result +++ b/mysql-test/r/drop.result @@ -25,7 +25,6 @@ n drop database if exists mysqltest; create database mysqltest; drop database mysqltest; -drop database if exists mysqltest; Warnings: Note 1008 Can't drop database 'mysqltest'; database doesn't exist flush tables with read lock; diff --git a/mysql-test/r/fulltext_left_join.result b/mysql-test/r/fulltext_left_join.result index abc63358dbe..6875a517718 100644 --- a/mysql-test/r/fulltext_left_join.result +++ b/mysql-test/r/fulltext_left_join.result @@ -31,3 +31,14 @@ match(t1.texte,t1.sujet,t1.motsclefs) against('droit' IN BOOLEAN MODE) 1 0 drop table t1, t2; +create table t1 (venue_id int(11) default null, venue_text varchar(255) default null, dt datetime default null) type=myisam; +insert into t1 (venue_id, venue_text, dt) values (1, 'a1', '2003-05-23 19:30:00'),(null, 'a2', '2003-05-23 19:30:00'); +create table t2 (name varchar(255) not null default '', entity_id int(11) not null auto_increment, primary key (entity_id), fulltext key name (name)) type=myisam; +insert into t2 (name, entity_id) values ('aberdeen town hall', 1), ('glasgow royal concert hall', 2), ('queen\'s hall, edinburgh', 3); +select * from t1 left join t2 on venue_id = entity_id where match(name) against('aberdeen' in boolean mode) and dt = '2003-05-23 19:30:00'; +venue_id venue_text dt name entity_id +1 a1 2003-05-23 19:30:00 aberdeen town hall 1 +select * from t1 left join t2 on venue_id = entity_id where match(name) against('aberdeen') and dt = '2003-05-23 19:30:00'; +venue_id venue_text dt name entity_id +1 a1 2003-05-23 19:30:00 aberdeen town hall 1 +drop table t1,t2; diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result index 8d05adcc1ba..d51bea020ed 100644 --- a/mysql-test/r/func_misc.result +++ b/mysql-test/r/func_misc.result @@ -13,3 +13,6 @@ NULL 255.255.1.255 0.0.1.255 select length(format('nan', 2)) > 0; length(format('nan', 2)) > 0 1 +select concat("$",format(2500,2)); +concat("$",format(2500,2)) +$2,500.00 diff --git a/mysql-test/r/func_set.result b/mysql-test/r/func_set.result index ab72e42eca9..86608e7c247 100644 --- a/mysql-test/r/func_set.result +++ b/mysql-test/r/func_set.result @@ -25,6 +25,9 @@ find_in_set("","a,b,c") find_in_set("","a,b,c,") find_in_set("",",a,b,c") select find_in_set("abc","abc"),find_in_set("ab","abc"),find_in_set("abcd","abc"); find_in_set("abc","abc") find_in_set("ab","abc") find_in_set("abcd","abc") 1 0 0 +select interval(null, 1, 10, 100); +interval(null, 1, 10, 100) +-1 drop table if exists t1,t2; create table t1 (id int(10) not null unique); create table t2 (id int(10) not null primary key, diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index 929ab0535d8..06f63b525cd 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -558,3 +558,26 @@ select * from t7 where concat(s1 collate latin1_general_ci,s1 collate latin1_swedish_ci) = 'AA'; ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_swedish_ci,EXPLICIT) for operation 'concat' drop table t7; +CREATE TABLE t1 ( +wid int(10) unsigned NOT NULL auto_increment, +data_podp date default NULL, +status_wnio enum('nowy','podp','real','arch') NOT NULL default 'nowy', +PRIMARY KEY(wid), +); +INSERT INTO t1 VALUES (8,NULL,'real'); +INSERT INTO t1 VALUES (9,NULL,'nowy'); +SELECT elt(status_wnio,data_podp) FROM t1 GROUP BY wid; +elt(status_wnio,data_podp) +NULL +NULL +DROP TABLE t1; +CREATE TABLE t1 ( +title text +) TYPE=MyISAM; +INSERT INTO t1 VALUES ('Congress reconvenes in September to debate welfare and adult education'); +INSERT INTO t1 VALUES ('House passes the CAREERS bill'); +SELECT CONCAT("</a>",RPAD("",(55 - LENGTH(title)),".")) from t1; +CONCAT("</a>",RPAD("",(55 - LENGTH(title)),".")) +NULL +</a>.......................... +DROP TABLE t1; diff --git a/mysql-test/r/func_test.result b/mysql-test/r/func_test.result index be0e3144fd6..9ae7b0c3ef1 100644 --- a/mysql-test/r/func_test.result +++ b/mysql-test/r/func_test.result @@ -109,6 +109,11 @@ select _koi8r'a' COLLATE koi8r_general_ci LIKE _koi8r'A' COLLATE koi8r_bin; ERROR HY000: Illegal mix of collations (koi8r_general_ci,EXPLICIT) and (koi8r_bin,EXPLICIT) for operation 'like' select _koi8r'a' LIKE _latin1'A'; ERROR HY000: Illegal mix of collations (koi8r_general_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation 'like' +create table t1 (a int); +insert t1 values (1); +select * from t1 where 1 xor 1; +a +drop table t1; select 5 between 0 and 10 between 0 and 1,(5 between 0 and 10) between 0 and 1; 5 between 0 and 10 between 0 and 1 (5 between 0 and 10) between 0 and 1 0 1 diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 38fe97a79a6..e6673ace227 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -334,6 +334,9 @@ insert into t1 values ('2001-01-12 12:23:40'); select ctime, hour(ctime) from t1; ctime hour(ctime) 2001-01-12 12:23:40 12 +select ctime from t1 where extract(MONTH FROM ctime) = 1 AND extract(YEAR FROM ctime) = 2001; +ctime +2001-01-12 12:23:40 drop table t1; create table t1 (id int); create table t2 (id int, date date); diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result index 6b33f6a7536..09c2b2042b3 100644 --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@ -66,41 +66,46 @@ GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, CREATE TE revoke all privileges on mysqltest.* from mysqltest_1@localhost; delete from mysql.user where user='mysqltest_1'; flush privileges; -grant usage on test.* to user@localhost with grant option; -show grants for user@localhost; -Grants for user@localhost -GRANT USAGE ON *.* TO 'user'@'localhost' -GRANT USAGE ON `test`.* TO 'user'@'localhost' WITH GRANT OPTION -grant ALL PRIVILEGES on *.* to drop_user2@localhost with GRANT OPTION; -show grants for drop_user2@localhost; -Grants for drop_user2@localhost -GRANT ALL PRIVILEGES ON *.* TO 'drop_user2'@'localhost' WITH GRANT OPTION -revoke all privileges, grant from drop_user2@localhost; -drop user drop_user2@localhost; -grant ALL PRIVILEGES on *.* to drop_user@localhost with GRANT OPTION; -grant ALL PRIVILEGES on test.* to drop_user@localhost with GRANT OPTION; -grant select(a) on test.t1 to drop_user@localhost; -show grants for drop_user@localhost; -Grants for drop_user@localhost -GRANT ALL PRIVILEGES ON *.* TO 'drop_user'@'localhost' WITH GRANT OPTION -GRANT ALL PRIVILEGES ON `test`.* TO 'drop_user'@'localhost' WITH GRANT OPTION -GRANT SELECT (a) ON `test`.`t1` TO 'drop_user'@'localhost' -revoke all privileges, grant from drop_user@localhost; -show grants for drop_user@localhost; -Grants for drop_user@localhost -GRANT USAGE ON *.* TO 'drop_user'@'localhost' -drop user drop_user@localhost; -revoke all privileges, grant from drop_user@localhost; -ERROR HY000: Can't revoke all privileges, grant for one or more of the requested users -grant select(a) on test.t1 to drop_user1@localhost; -grant select on test.t1 to drop_user2@localhost; -grant select on test.* to drop_user3@localhost; -grant select on *.* to drop_user4@localhost; -drop user drop_user1@localhost, drop_user2@localhost, drop_user3@localhost, -drop_user4@localhost; -ERROR HY000: Can't drop one or more of the requested users -revoke all privileges, grant from drop_user1@localhost, drop_user2@localhost, -drop_user3@localhost, drop_user4@localhost; -drop user drop_user1@localhost, drop_user2@localhost, drop_user3@localhost, -drop_user4@localhost; +grant usage on test.* to mysqltest_1@localhost with grant option; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' +GRANT USAGE ON `mysqltest`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION +GRANT USAGE ON `test`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION +delete from mysql.user where user='mysqltest_1'; +delete from mysql.db where user='mysqltest_1'; +delete from mysql.tables_priv where user='mysqltest_1'; +delete from mysql.columns_priv where user='mysqltest_1'; +flush privileges; +create table t1 (a int); +GRANT select,update,insert on t1 to mysqltest_1@localhost; +GRANT select (a), update (a),insert(a), references(a) on t1 to mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' +GRANT SELECT, SELECT (a), INSERT, INSERT (a), UPDATE, UPDATE (a), REFERENCES (a) ON `test`.`t1` TO 'mysqltest_1'@'localhost' +select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1"; +table_priv column_priv +Select,Insert,Update Select,Insert,Update,References +REVOKE select (a), update on t1 from mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' +GRANT SELECT, INSERT, INSERT (a), REFERENCES (a) ON `test`.`t1` TO 'mysqltest_1'@'localhost' +REVOKE insert,insert (a) on t1 from mysqltest_1@localhost; +GRANT references on t1 to mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' +GRANT SELECT, REFERENCES, REFERENCES (a) ON `test`.`t1` TO 'mysqltest_1'@'localhost' +select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1"; +table_priv column_priv +Select,References References +delete from mysql.user where user='mysqltest_1'; +delete from mysql.db where user='mysqltest_1'; +delete from mysql.tables_priv where user='mysqltest_1'; +delete from mysql.columns_priv where user='mysqltest_1'; +flush privileges; drop table t1; +GRANT FILE on mysqltest.* to mysqltest_1@localhost; +Wrong usage of DB GRANT and GLOBAL PRIVILEGES diff --git a/mysql-test/r/grant_cache.result b/mysql-test/r/grant_cache.result index 3edaa003b60..f49db67a9f7 100644 --- a/mysql-test/r/grant_cache.result +++ b/mysql-test/r/grant_cache.result @@ -1,4 +1,5 @@ drop table if exists test.t1,mysqltest.t1,mysqltest.t2; +drop database if exists mysqltest; reset query cache; flush status; create database if not exists mysqltest; diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 23f82532d06..de0407a8907 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -593,11 +593,11 @@ a b explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort -1 SIMPLE t2 ALL a NULL NULL NULL 4 Using where +1 SIMPLE t2 ALL a NULL NULL NULL 3 Using where explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary -1 SIMPLE t2 ALL a NULL NULL NULL 4 Using where +1 SIMPLE t2 ALL a NULL NULL NULL 3 Using where drop table t1,t2; create table t1 (a int, b int); insert into t1 values (1, 4),(10, 40),(1, 4),(10, 43),(1, 4),(10, 41),(1, 4),(10, 43),(1, 4); diff --git a/mysql-test/r/handler.result b/mysql-test/r/handler.result index 4e0153006f8..bf68057a9aa 100644 --- a/mysql-test/r/handler.result +++ b/mysql-test/r/handler.result @@ -167,3 +167,30 @@ handler t1 read first; a 6 drop table t1; +create table t1 (a int); +insert into t1 values (1),(2),(3),(4),(5),(6); +delete from t1 limit 2; +handler t1 open; +handler t1 read first; +a +3 +handler t1 read first limit 1,1; +a +4 +handler t1 read first limit 2,2; +a +5 +6 +delete from t1 limit 3; +handler t1 read first; +a +6 +drop table t1; +create table t1(a int, index(a)); +insert into t1 values (1), (2), (3); +handler t1 open; +handler t1 read a=(W); +Unknown column 'W' in 'field list' +handler t1 read a=(a); +Wrong arguments to HANDLER ... READ +drop table t1; diff --git a/mysql-test/r/heap.result b/mysql-test/r/heap.result index 7d929f50801..4f73cfb6d72 100644 --- a/mysql-test/r/heap.result +++ b/mysql-test/r/heap.result @@ -172,7 +172,7 @@ Warning 1263 Data truncated for column 'new_col' at row 4 Warning 1263 Data truncated for column 'new_col' at row 5 explain select * from t1 where btn="a"; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL btn NULL NULL NULL 14 Using where +1 SIMPLE t1 ALL btn NULL NULL NULL 11 Using where explain select * from t1 where btn="a" and new_col="a"; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref btn btn 11 const,const 10 Using where @@ -203,6 +203,20 @@ a b INSERT INTO t1 VALUES (1,3); ERROR 23000: Duplicate entry '3' for key 1 DROP TABLE t1; +CREATE TABLE t1 ( +a int default NULL, +key a (a) +) TYPE=HEAP; +INSERT INTO t1 VALUES (10), (10), (10); +EXPLAIN SELECT * FROM t1 WHERE a=10; +table type possible_keys key key_len ref rows Extra +t1 ref a a 5 const 10 Using where +SELECT * FROM t1 WHERE a=10; +a +10 +10 +10 +DROP TABLE t1; CREATE TABLE t1 (a int not null, primary key(a)) type=heap; INSERT into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11); DELETE from t1 where a < 100; diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result index ebd34dd7668..59c598265c0 100644 --- a/mysql-test/r/insert.result +++ b/mysql-test/r/insert.result @@ -58,6 +58,16 @@ skr 2 test 1 test 2 drop table t1; +create table t1 (id int NOT NULL DEFAULT 8); +insert into t1 values(NULL); +Column 'id' cannot be null +insert into t1 values (1), (NULL), (2); +select * from t1; +id +1 +0 +2 +drop table t1; drop database if exists test_$1; create database test_$1; use test_$1; diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result index 04c3bc9027d..d5b9b9a2117 100644 --- a/mysql-test/r/insert_select.result +++ b/mysql-test/r/insert_select.result @@ -76,3 +76,518 @@ show binlog events; Log_name Pos Event_type Server_id Orig_log_pos Info master-bin.000001 4 Start 1 4 Server ver: VERSION, Binlog ver: 3 drop table t1, t2; +drop table if exists t1, t2; +create table t1 (a int not null); +create table t2 (a int not null); +insert into t1 values (1); +insert into t1 values (a+2); +insert into t1 values (a+3); +insert into t1 values (4),(a+5); +insert into t1 select * from t1; +select * from t1; +a +1 +2 +3 +4 +5 +1 +2 +3 +4 +5 +insert into t1 select * from t1 as t2; +select * from t1; +a +1 +2 +3 +4 +5 +1 +2 +3 +4 +5 +1 +2 +3 +4 +5 +1 +2 +3 +4 +5 +insert into t2 select * from t1 as t2; +select * from t1; +a +1 +2 +3 +4 +5 +1 +2 +3 +4 +5 +1 +2 +3 +4 +5 +1 +2 +3 +4 +5 +insert into t1 select t2.a from t1,t2; +select * from t1; +a +1 +2 +3 +4 +5 +1 +2 +3 +4 +5 +1 +2 +3 +4 +5 +1 +2 +3 +4 +5 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +insert into t1 select * from t1,t1; +Not unique table/alias: 't1' +drop table t1,t2; +create table t1 (a int not null primary key, b char(10)); +create table t2 (a int not null, b char(10)); +insert into t1 values (1,"t1:1"),(3,"t1:3"); +insert into t2 values (2,"t2:2"), (3,"t2:3"); +insert into t1 select * from t2; +Duplicate entry '3' for key 1 +select * from t1; +a b +1 t1:1 +3 t1:3 +2 t2:2 +replace into t1 select * from t2; +select * from t1; +a b +1 t1:1 +3 t2:3 +2 t2:2 +drop table t1,t2; +CREATE TABLE t1 ( USID INTEGER UNSIGNED, ServerID TINYINT UNSIGNED, State ENUM ('unknown', 'Access-Granted', 'Session-Active', 'Session-Closed' ) NOT NULL DEFAULT 'unknown', SessionID CHAR(32), User CHAR(32) NOT NULL DEFAULT '<UNKNOWN>', NASAddr INTEGER UNSIGNED, NASPort INTEGER UNSIGNED, NASPortType INTEGER UNSIGNED, ConnectSpeed INTEGER UNSIGNED, CarrierType CHAR(32), CallingStationID CHAR(32), CalledStationID CHAR(32), AssignedAddr INTEGER UNSIGNED, SessionTime INTEGER UNSIGNED, PacketsIn INTEGER UNSIGNED, OctetsIn INTEGER UNSIGNED, PacketsOut INTEGER UNSIGNED, OctetsOut INTEGER UNSIGNED, TerminateCause INTEGER UNSIGNED, UnauthTime TINYINT UNSIGNED, AccessRequestTime DATETIME, AcctStartTime DATETIME, AcctLastTime DATETIME, LastModification TIMESTAMP NOT NULL); +CREATE TABLE t2 ( USID INTEGER UNSIGNED AUTO_INCREMENT, ServerID TINYINT UNSIGNED, State ENUM ('unknown', 'Access-Granted', 'Session-Active', 'Session-Closed' ) NOT NULL DEFAULT 'unknown', SessionID CHAR(32), User TEXT NOT NULL, NASAddr INTEGER UNSIGNED, NASPort INTEGER UNSIGNED, NASPortType INTEGER UNSIGNED, ConnectSpeed INTEGER UNSIGNED, CarrierType CHAR(32), CallingStationID CHAR(32), CalledStationID CHAR(32), AssignedAddr INTEGER UNSIGNED, SessionTime INTEGER UNSIGNED, PacketsIn INTEGER UNSIGNED, OctetsIn INTEGER UNSIGNED, PacketsOut INTEGER UNSIGNED, OctetsOut INTEGER UNSIGNED, TerminateCause INTEGER UNSIGNED, UnauthTime TINYINT UNSIGNED, AccessRequestTime DATETIME, AcctStartTime DATETIME, AcctLastTime DATETIME, LastModification TIMESTAMP NOT NULL, INDEX(USID,ServerID,NASAddr,SessionID), INDEX(AssignedAddr)); +INSERT INTO t1 VALUES (39,42,'Access-Granted','46','491721000045',2130706433,17690,NULL,NULL,'Localnet','491721000045','49172200000',754974766,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2003-07-18 00:11:21',NULL,NULL,20030718001121); +INSERT INTO t2 SELECT USID, ServerID, State, SessionID, User, NASAddr, NASPort, NASPortType, ConnectSpeed, CarrierType, CallingStationID, CalledStationID, AssignedAddr, SessionTime, PacketsIn, OctetsIn, PacketsOut, OctetsOut, TerminateCause, UnauthTime, AccessRequestTime, AcctStartTime, AcctLastTime, LastModification from t1 LIMIT 1; +drop table t1,t2; diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index 05dac389ada..24b272a1985 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -104,9 +104,7 @@ KEY category (category,county,state) INSERT INTO t2 VALUES (3,2,11,12,5400,7800); INSERT INTO t2 VALUES (4,2,25,12,6500,11200); INSERT INTO t2 VALUES (5,1,37,6,10000,12000); -select a.id, b.category as catid, b.state as stateid, b.county as -countyid from t1 a, t2 b where (a.token = -'a71250b7ed780f6ef3185bfffe027983') and (a.count = b.id); +select a.id, b.category as catid, b.state as stateid, b.county as countyid from t1 a, t2 b ignore index (primary) where (a.token ='a71250b7ed780f6ef3185bfffe027983') and (a.count = b.id); id catid stateid countyid 27 2 12 11 28 2 12 11 diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 9e494fc88c2..b7d3f94cb33 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -657,3 +657,11 @@ i i i 1 NULL NULL 2 2 2 drop table t1,t2,t3; +create table t1 (f1 integer,f2 integer,f3 integer); +create table t2 (f2 integer,f4 integer); +create table t3 (f3 integer,f5 integer); +select * from t1 +left outer join t2 using (f2) +left outer join t3 using (f3); +Unknown column 'test.t2.f3' in 'on clause' +drop table t1,t2,t3; diff --git a/mysql-test/r/key_diff.result b/mysql-test/r/key_diff.result index 8097186bde1..c3f4f743967 100644 --- a/mysql-test/r/key_diff.result +++ b/mysql-test/r/key_diff.result @@ -36,7 +36,7 @@ a a a a explain select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL a NULL NULL NULL 5 -1 SIMPLE t2 ALL b NULL NULL NULL 5 Using where +1 SIMPLE t2 ALL b NULL NULL NULL 4 Using where select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B order by binary t1.a,t2.a; a b a b A B a a diff --git a/mysql-test/r/lock_tables_lost_commit.result b/mysql-test/r/lock_tables_lost_commit.result new file mode 100644 index 00000000000..ccf56793f45 --- /dev/null +++ b/mysql-test/r/lock_tables_lost_commit.result @@ -0,0 +1,8 @@ +drop table if exists t1; +create table t1(a int) type=innodb; +lock tables t1 write; +insert into t1 values(10); +select * from t1; +a +10 +drop table t1; diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index 507ceb8951c..9a99d1c64fb 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -610,3 +610,17 @@ AND file_code = '0000000115' LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 const PRIMARY,files PRIMARY 33 const,const 1 DROP TABLE t2, t1; +create table t1 (x int, y int, index xy(x, y)); +create table t2 (x int, y int, index xy(x, y)); +create table t3 (x int, y int, index xy(x, y)) type=merge union=(t1,t2); +insert into t1 values(1, 2); +insert into t2 values(1, 3); +select * from t3 where x = 1 and y < 5 order by y; +x y +1 2 +1 3 +select * from t3 where x = 1 and y < 5 order by y desc; +x y +1 3 +1 2 +drop table t1,t2,t3; diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index fd9ff2c90cf..59af9b3e7f3 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -361,12 +361,12 @@ t1 1 c_2 1 c A 5 NULL NULL YES BTREE t1 1 c_2 2 a A 5 NULL NULL BTREE explain select * from t1,t2 where t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL a NULL NULL NULL 5 1 SIMPLE t2 ALL a NULL NULL NULL 2 Using where +1 SIMPLE t1 ALL a NULL NULL NULL 4 explain select * from t1,t2 force index(a) where t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL a NULL NULL NULL 2 -1 SIMPLE t1 ALL a NULL NULL NULL 5 Using where +1 SIMPLE t1 ALL a NULL NULL NULL 4 Using where explain select * from t1 force index(a),t2 force index(a) where t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL a NULL NULL NULL 2 @@ -377,8 +377,8 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref b b 5 test.t2.b 1 Using where explain select * from t1,t2 force index(c) where t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL a NULL NULL NULL 5 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t1 ALL a NULL NULL NULL 4 explain select * from t1 where a=0 or a=2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL a NULL NULL NULL 5 Using where diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 1e940351a4a..5470b9387e3 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -306,17 +306,17 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 9 NULL 8 Using where; Using index explain select * from t1 where a = 2 and b >0 order by a desc,b desc; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 9 NULL 5 Using where; Using index +1 SIMPLE t1 range a a 9 NULL 4 Using where; Using index explain select * from t1 where a = 2 and b is null order by a desc,b desc; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a a 9 const,const 1 Using where; Using index; Using filesort explain select * from t1 where a = 2 and (b is null or b > 0) order by a desc,b desc; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 9 NULL 6 Using where; Using index +1 SIMPLE t1 range a a 9 NULL 5 Using where; Using index explain select * from t1 where a = 2 and b > 0 order by a desc,b desc; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 9 NULL 5 Using where; Using index +1 SIMPLE t1 range a a 9 NULL 4 Using where; Using index explain select * from t1 where a = 2 and b < 2 order by a desc,b desc; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 9 NULL 2 Using where; Using index diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index 68ddb5a377b..420281b7ef2 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -2,8 +2,8 @@ flush query cache; flush query cache; reset query cache; flush status; +drop table if exists t0,t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16,t17,t18,t19,t20,t21,t22,t23,t24,t25,t26,t27,t28,t29,t30,t31,t32,t33,t34,t35,t36,t37,t38,t39,t40,t41,t42,t43,t44,t45,t46,t47,t48,t49,t50,t51,t52,t53,t54,t55,t56,t57,t58,t59,t60,t61,t62,t63,t64,t65,t66,t67,t68,t69,t70,t71,t72,t73,t74,t75,t76,t77,t78,t79,t80,t81,t82,t83,t84,t85,t86,t87,t88,t89,t90,t91,t92,t93,t94,t95,t96,t97,t98,t99,t100,t101,t102,t103,t104,t105,t106,t107,t108,t109,t110,t111,t112,t113,t114,t115,t116,t117,t118,t119,t120,t121,t122,t123,t124,t125,t126,t127,t128,t129,t130,t131,t132,t133,t134,t135,t136,t137,t138,t139,t140,t141,t142,t143,t144,t145,t146,t147,t148,t149,t150,t151,t152,t153,t154,t155,t156,t157,t158,t159,t160,t161,t162,t163,t164,t165,t166,t167,t168,t169,t170,t171,t172,t173,t174,t175,t176,t177,t178,t179,t180,t181,t182,t183,t184,t185,t186,t187,t188,t189,t190,t191,t192,t193,t194,t195,t196,t197,t198,t199,t200,t201,t202,t203,t204,t205,t206,t207,t208,t209,t210,t211,t212,t213,t214,t215,t216,t217,t218,t219,t220,t221,t222,t223,t224,t225,t226,t227,t228,t229,t230,t231,t232,t233,t234,t235,t236,t237,t238,t239,t240,t241,t242,t243,t244,t245,t246,t247,t248,t249,t250,t251,t252,t253,t254,t255,t256,t00,mysqltest.t1; drop database if exists mysqltest; -drop table if exists t1,t2,t3,t11,t21, mysqltest.t1; create table t1 (a int not null); insert into t1 values (1),(2),(3); select * from t1; @@ -358,7 +358,7 @@ show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 drop table t1,t2; -create database if not exists mysqltest; +create database mysqltest; create table mysqltest.t1 (i int not null auto_increment, a int, primary key (i)); insert into mysqltest.t1 (a) values (1); select * from mysqltest.t1 where i is null; @@ -709,3 +709,536 @@ Variable_name Value Qcache_queries_in_cache 2 SET OPTION SQL_SELECT_LIMIT=DEFAULT; drop table t1; +flush status; +create table t0(a int); +create table t1(a int); +create table t2(a int); +create table t3(a int); +create table t4(a int); +create table t5(a int); +create table t6(a int); +create table t7(a int); +create table t8(a int); +create table t9(a int); +create table t10(a int); +create table t11(a int); +create table t12(a int); +create table t13(a int); +create table t14(a int); +create table t15(a int); +create table t16(a int); +create table t17(a int); +create table t18(a int); +create table t19(a int); +create table t20(a int); +create table t21(a int); +create table t22(a int); +create table t23(a int); +create table t24(a int); +create table t25(a int); +create table t26(a int); +create table t27(a int); +create table t28(a int); +create table t29(a int); +create table t30(a int); +create table t31(a int); +create table t32(a int); +create table t33(a int); +create table t34(a int); +create table t35(a int); +create table t36(a int); +create table t37(a int); +create table t38(a int); +create table t39(a int); +create table t40(a int); +create table t41(a int); +create table t42(a int); +create table t43(a int); +create table t44(a int); +create table t45(a int); +create table t46(a int); +create table t47(a int); +create table t48(a int); +create table t49(a int); +create table t50(a int); +create table t51(a int); +create table t52(a int); +create table t53(a int); +create table t54(a int); +create table t55(a int); +create table t56(a int); +create table t57(a int); +create table t58(a int); +create table t59(a int); +create table t60(a int); +create table t61(a int); +create table t62(a int); +create table t63(a int); +create table t64(a int); +create table t65(a int); +create table t66(a int); +create table t67(a int); +create table t68(a int); +create table t69(a int); +create table t70(a int); +create table t71(a int); +create table t72(a int); +create table t73(a int); +create table t74(a int); +create table t75(a int); +create table t76(a int); +create table t77(a int); +create table t78(a int); +create table t79(a int); +create table t80(a int); +create table t81(a int); +create table t82(a int); +create table t83(a int); +create table t84(a int); +create table t85(a int); +create table t86(a int); +create table t87(a int); +create table t88(a int); +create table t89(a int); +create table t90(a int); +create table t91(a int); +create table t92(a int); +create table t93(a int); +create table t94(a int); +create table t95(a int); +create table t96(a int); +create table t97(a int); +create table t98(a int); +create table t99(a int); +create table t100(a int); +create table t101(a int); +create table t102(a int); +create table t103(a int); +create table t104(a int); +create table t105(a int); +create table t106(a int); +create table t107(a int); +create table t108(a int); +create table t109(a int); +create table t110(a int); +create table t111(a int); +create table t112(a int); +create table t113(a int); +create table t114(a int); +create table t115(a int); +create table t116(a int); +create table t117(a int); +create table t118(a int); +create table t119(a int); +create table t120(a int); +create table t121(a int); +create table t122(a int); +create table t123(a int); +create table t124(a int); +create table t125(a int); +create table t126(a int); +create table t127(a int); +create table t128(a int); +create table t129(a int); +create table t130(a int); +create table t131(a int); +create table t132(a int); +create table t133(a int); +create table t134(a int); +create table t135(a int); +create table t136(a int); +create table t137(a int); +create table t138(a int); +create table t139(a int); +create table t140(a int); +create table t141(a int); +create table t142(a int); +create table t143(a int); +create table t144(a int); +create table t145(a int); +create table t146(a int); +create table t147(a int); +create table t148(a int); +create table t149(a int); +create table t150(a int); +create table t151(a int); +create table t152(a int); +create table t153(a int); +create table t154(a int); +create table t155(a int); +create table t156(a int); +create table t157(a int); +create table t158(a int); +create table t159(a int); +create table t160(a int); +create table t161(a int); +create table t162(a int); +create table t163(a int); +create table t164(a int); +create table t165(a int); +create table t166(a int); +create table t167(a int); +create table t168(a int); +create table t169(a int); +create table t170(a int); +create table t171(a int); +create table t172(a int); +create table t173(a int); +create table t174(a int); +create table t175(a int); +create table t176(a int); +create table t177(a int); +create table t178(a int); +create table t179(a int); +create table t180(a int); +create table t181(a int); +create table t182(a int); +create table t183(a int); +create table t184(a int); +create table t185(a int); +create table t186(a int); +create table t187(a int); +create table t188(a int); +create table t189(a int); +create table t190(a int); +create table t191(a int); +create table t192(a int); +create table t193(a int); +create table t194(a int); +create table t195(a int); +create table t196(a int); +create table t197(a int); +create table t198(a int); +create table t199(a int); +create table t200(a int); +create table t201(a int); +create table t202(a int); +create table t203(a int); +create table t204(a int); +create table t205(a int); +create table t206(a int); +create table t207(a int); +create table t208(a int); +create table t209(a int); +create table t210(a int); +create table t211(a int); +create table t212(a int); +create table t213(a int); +create table t214(a int); +create table t215(a int); +create table t216(a int); +create table t217(a int); +create table t218(a int); +create table t219(a int); +create table t220(a int); +create table t221(a int); +create table t222(a int); +create table t223(a int); +create table t224(a int); +create table t225(a int); +create table t226(a int); +create table t227(a int); +create table t228(a int); +create table t229(a int); +create table t230(a int); +create table t231(a int); +create table t232(a int); +create table t233(a int); +create table t234(a int); +create table t235(a int); +create table t236(a int); +create table t237(a int); +create table t238(a int); +create table t239(a int); +create table t240(a int); +create table t241(a int); +create table t242(a int); +create table t243(a int); +create table t244(a int); +create table t245(a int); +create table t246(a int); +create table t247(a int); +create table t248(a int); +create table t249(a int); +create table t250(a int); +create table t251(a int); +create table t252(a int); +create table t253(a int); +create table t254(a int); +create table t255(a int); +create table t256(a int); +create table t00 (a int) type=MERGE UNION=(t0,t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16,t17,t18,t19,t20,t21,t22,t23,t24,t25,t26,t27,t28,t29,t30,t31,t32,t33,t34,t35,t36,t37,t38,t39,t40,t41,t42,t43,t44,t45,t46,t47,t48,t49,t50,t51,t52,t53,t54,t55,t56,t57,t58,t59,t60,t61,t62,t63,t64,t65,t66,t67,t68,t69,t70,t71,t72,t73,t74,t75,t76,t77,t78,t79,t80,t81,t82,t83,t84,t85,t86,t87,t88,t89,t90,t91,t92,t93,t94,t95,t96,t97,t98,t99,t100,t101,t102,t103,t104,t105,t106,t107,t108,t109,t110,t111,t112,t113,t114,t115,t116,t117,t118,t119,t120,t121,t122,t123,t124,t125,t126,t127,t128,t129,t130,t131,t132,t133,t134,t135,t136,t137,t138,t139,t140,t141,t142,t143,t144,t145,t146,t147,t148,t149,t150,t151,t152,t153,t154,t155,t156,t157,t158,t159,t160,t161,t162,t163,t164,t165,t166,t167,t168,t169,t170,t171,t172,t173,t174,t175,t176,t177,t178,t179,t180,t181,t182,t183,t184,t185,t186,t187,t188,t189,t190,t191,t192,t193,t194,t195,t196,t197,t198,t199,t200,t201,t202,t203,t204,t205,t206,t207,t208,t209,t210,t211,t212,t213,t214,t215,t216,t217,t218,t219,t220,t221,t222,t223,t224,t225,t226,t227,t228,t229,t230,t231,t232,t233,t234,t235,t236,t237,t238,t239,t240,t241,t242,t243,t244,t245,t246,t247,t248,t249,t250,t251,t252,t253,t254,t255,t256) INSERT_METHOD=FIRST; +insert into t0 values (1),(2); +insert into t1 values (1),(2); +insert into t2 values (1),(2); +insert into t3 values (1),(2); +insert into t4 values (1),(2); +insert into t5 values (1),(2); +insert into t6 values (1),(2); +insert into t7 values (1),(2); +insert into t8 values (1),(2); +insert into t9 values (1),(2); +insert into t10 values (1),(2); +insert into t11 values (1),(2); +insert into t12 values (1),(2); +insert into t13 values (1),(2); +insert into t14 values (1),(2); +insert into t15 values (1),(2); +insert into t16 values (1),(2); +insert into t17 values (1),(2); +insert into t18 values (1),(2); +insert into t19 values (1),(2); +insert into t20 values (1),(2); +insert into t21 values (1),(2); +insert into t22 values (1),(2); +insert into t23 values (1),(2); +insert into t24 values (1),(2); +insert into t25 values (1),(2); +insert into t26 values (1),(2); +insert into t27 values (1),(2); +insert into t28 values (1),(2); +insert into t29 values (1),(2); +insert into t30 values (1),(2); +insert into t31 values (1),(2); +insert into t32 values (1),(2); +insert into t33 values (1),(2); +insert into t34 values (1),(2); +insert into t35 values (1),(2); +insert into t36 values (1),(2); +insert into t37 values (1),(2); +insert into t38 values (1),(2); +insert into t39 values (1),(2); +insert into t40 values (1),(2); +insert into t41 values (1),(2); +insert into t42 values (1),(2); +insert into t43 values (1),(2); +insert into t44 values (1),(2); +insert into t45 values (1),(2); +insert into t46 values (1),(2); +insert into t47 values (1),(2); +insert into t48 values (1),(2); +insert into t49 values (1),(2); +insert into t50 values (1),(2); +insert into t51 values (1),(2); +insert into t52 values (1),(2); +insert into t53 values (1),(2); +insert into t54 values (1),(2); +insert into t55 values (1),(2); +insert into t56 values (1),(2); +insert into t57 values (1),(2); +insert into t58 values (1),(2); +insert into t59 values (1),(2); +insert into t60 values (1),(2); +insert into t61 values (1),(2); +insert into t62 values (1),(2); +insert into t63 values (1),(2); +insert into t64 values (1),(2); +insert into t65 values (1),(2); +insert into t66 values (1),(2); +insert into t67 values (1),(2); +insert into t68 values (1),(2); +insert into t69 values (1),(2); +insert into t70 values (1),(2); +insert into t71 values (1),(2); +insert into t72 values (1),(2); +insert into t73 values (1),(2); +insert into t74 values (1),(2); +insert into t75 values (1),(2); +insert into t76 values (1),(2); +insert into t77 values (1),(2); +insert into t78 values (1),(2); +insert into t79 values (1),(2); +insert into t80 values (1),(2); +insert into t81 values (1),(2); +insert into t82 values (1),(2); +insert into t83 values (1),(2); +insert into t84 values (1),(2); +insert into t85 values (1),(2); +insert into t86 values (1),(2); +insert into t87 values (1),(2); +insert into t88 values (1),(2); +insert into t89 values (1),(2); +insert into t90 values (1),(2); +insert into t91 values (1),(2); +insert into t92 values (1),(2); +insert into t93 values (1),(2); +insert into t94 values (1),(2); +insert into t95 values (1),(2); +insert into t96 values (1),(2); +insert into t97 values (1),(2); +insert into t98 values (1),(2); +insert into t99 values (1),(2); +insert into t100 values (1),(2); +insert into t101 values (1),(2); +insert into t102 values (1),(2); +insert into t103 values (1),(2); +insert into t104 values (1),(2); +insert into t105 values (1),(2); +insert into t106 values (1),(2); +insert into t107 values (1),(2); +insert into t108 values (1),(2); +insert into t109 values (1),(2); +insert into t110 values (1),(2); +insert into t111 values (1),(2); +insert into t112 values (1),(2); +insert into t113 values (1),(2); +insert into t114 values (1),(2); +insert into t115 values (1),(2); +insert into t116 values (1),(2); +insert into t117 values (1),(2); +insert into t118 values (1),(2); +insert into t119 values (1),(2); +insert into t120 values (1),(2); +insert into t121 values (1),(2); +insert into t122 values (1),(2); +insert into t123 values (1),(2); +insert into t124 values (1),(2); +insert into t125 values (1),(2); +insert into t126 values (1),(2); +insert into t127 values (1),(2); +insert into t128 values (1),(2); +insert into t129 values (1),(2); +insert into t130 values (1),(2); +insert into t131 values (1),(2); +insert into t132 values (1),(2); +insert into t133 values (1),(2); +insert into t134 values (1),(2); +insert into t135 values (1),(2); +insert into t136 values (1),(2); +insert into t137 values (1),(2); +insert into t138 values (1),(2); +insert into t139 values (1),(2); +insert into t140 values (1),(2); +insert into t141 values (1),(2); +insert into t142 values (1),(2); +insert into t143 values (1),(2); +insert into t144 values (1),(2); +insert into t145 values (1),(2); +insert into t146 values (1),(2); +insert into t147 values (1),(2); +insert into t148 values (1),(2); +insert into t149 values (1),(2); +insert into t150 values (1),(2); +insert into t151 values (1),(2); +insert into t152 values (1),(2); +insert into t153 values (1),(2); +insert into t154 values (1),(2); +insert into t155 values (1),(2); +insert into t156 values (1),(2); +insert into t157 values (1),(2); +insert into t158 values (1),(2); +insert into t159 values (1),(2); +insert into t160 values (1),(2); +insert into t161 values (1),(2); +insert into t162 values (1),(2); +insert into t163 values (1),(2); +insert into t164 values (1),(2); +insert into t165 values (1),(2); +insert into t166 values (1),(2); +insert into t167 values (1),(2); +insert into t168 values (1),(2); +insert into t169 values (1),(2); +insert into t170 values (1),(2); +insert into t171 values (1),(2); +insert into t172 values (1),(2); +insert into t173 values (1),(2); +insert into t174 values (1),(2); +insert into t175 values (1),(2); +insert into t176 values (1),(2); +insert into t177 values (1),(2); +insert into t178 values (1),(2); +insert into t179 values (1),(2); +insert into t180 values (1),(2); +insert into t181 values (1),(2); +insert into t182 values (1),(2); +insert into t183 values (1),(2); +insert into t184 values (1),(2); +insert into t185 values (1),(2); +insert into t186 values (1),(2); +insert into t187 values (1),(2); +insert into t188 values (1),(2); +insert into t189 values (1),(2); +insert into t190 values (1),(2); +insert into t191 values (1),(2); +insert into t192 values (1),(2); +insert into t193 values (1),(2); +insert into t194 values (1),(2); +insert into t195 values (1),(2); +insert into t196 values (1),(2); +insert into t197 values (1),(2); +insert into t198 values (1),(2); +insert into t199 values (1),(2); +insert into t200 values (1),(2); +insert into t201 values (1),(2); +insert into t202 values (1),(2); +insert into t203 values (1),(2); +insert into t204 values (1),(2); +insert into t205 values (1),(2); +insert into t206 values (1),(2); +insert into t207 values (1),(2); +insert into t208 values (1),(2); +insert into t209 values (1),(2); +insert into t210 values (1),(2); +insert into t211 values (1),(2); +insert into t212 values (1),(2); +insert into t213 values (1),(2); +insert into t214 values (1),(2); +insert into t215 values (1),(2); +insert into t216 values (1),(2); +insert into t217 values (1),(2); +insert into t218 values (1),(2); +insert into t219 values (1),(2); +insert into t220 values (1),(2); +insert into t221 values (1),(2); +insert into t222 values (1),(2); +insert into t223 values (1),(2); +insert into t224 values (1),(2); +insert into t225 values (1),(2); +insert into t226 values (1),(2); +insert into t227 values (1),(2); +insert into t228 values (1),(2); +insert into t229 values (1),(2); +insert into t230 values (1),(2); +insert into t231 values (1),(2); +insert into t232 values (1),(2); +insert into t233 values (1),(2); +insert into t234 values (1),(2); +insert into t235 values (1),(2); +insert into t236 values (1),(2); +insert into t237 values (1),(2); +insert into t238 values (1),(2); +insert into t239 values (1),(2); +insert into t240 values (1),(2); +insert into t241 values (1),(2); +insert into t242 values (1),(2); +insert into t243 values (1),(2); +insert into t244 values (1),(2); +insert into t245 values (1),(2); +insert into t246 values (1),(2); +insert into t247 values (1),(2); +insert into t248 values (1),(2); +insert into t249 values (1),(2); +insert into t250 values (1),(2); +insert into t251 values (1),(2); +insert into t252 values (1),(2); +insert into t253 values (1),(2); +insert into t254 values (1),(2); +insert into t255 values (1),(2); +insert into t256 values (1),(2); +select count(*) from t00; +count(*) +514 +select count(*) from t00; +count(*) +514 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 1 +delete from t256; +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +drop table t0,t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16,t17,t18,t19,t20,t21,t22,t23,t24,t25,t26,t27,t28,t29,t30,t31,t32,t33,t34,t35,t36,t37,t38,t39,t40,t41,t42,t43,t44,t45,t46,t47,t48,t49,t50,t51,t52,t53,t54,t55,t56,t57,t58,t59,t60,t61,t62,t63,t64,t65,t66,t67,t68,t69,t70,t71,t72,t73,t74,t75,t76,t77,t78,t79,t80,t81,t82,t83,t84,t85,t86,t87,t88,t89,t90,t91,t92,t93,t94,t95,t96,t97,t98,t99,t100,t101,t102,t103,t104,t105,t106,t107,t108,t109,t110,t111,t112,t113,t114,t115,t116,t117,t118,t119,t120,t121,t122,t123,t124,t125,t126,t127,t128,t129,t130,t131,t132,t133,t134,t135,t136,t137,t138,t139,t140,t141,t142,t143,t144,t145,t146,t147,t148,t149,t150,t151,t152,t153,t154,t155,t156,t157,t158,t159,t160,t161,t162,t163,t164,t165,t166,t167,t168,t169,t170,t171,t172,t173,t174,t175,t176,t177,t178,t179,t180,t181,t182,t183,t184,t185,t186,t187,t188,t189,t190,t191,t192,t193,t194,t195,t196,t197,t198,t199,t200,t201,t202,t203,t204,t205,t206,t207,t208,t209,t210,t211,t212,t213,t214,t215,t216,t217,t218,t219,t220,t221,t222,t223,t224,t225,t226,t227,t228,t229,t230,t231,t232,t233,t234,t235,t236,t237,t238,t239,t240,t241,t242,t243,t244,t245,t246,t247,t248,t249,t250,t251,t252,t253,t254,t255,t256,t00; diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 86a93398a9f..247221a92f3 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -5,7 +5,24 @@ type int(11) DEFAULT '0' NOT NULL, event_id int(11) DEFAULT '0' NOT NULL, PRIMARY KEY (event_date,type,event_id) ); -INSERT INTO t1 VALUES ('1999-07-10',100100,24),('1999-07-11',100100,25),('1999-07-13',100600,0),('1999-07-13',100600,4),('1999-07-13',100600,26),('1999-07-14',100600,10),('1999-07-15',100600,16),('1999-07-15',100800,45),('1999-07-15',101000,47),('1999-07-16',100800,46),('1999-07-20',100600,5),('1999-07-20',100600,27),('1999-07-21',100600,11),('1999-07-22',100600,17),('1999-07-23',100100,39),('1999-07-24',100100,39),('1999-07-24',100500,40),('1999-07-25',100100,39),('1999-07-27',100600,1),('1999-07-27',100600,6),('1999-07-27',100600,28),('1999-07-28',100600,12),('1999-07-29',100500,41),('1999-07-29',100600,18),('1999-07-30',100500,41),('1999-07-31',100500,41),('1999-08-01',100700,34),('1999-08-03',100600,7),('1999-08-03',100600,29),('1999-08-04',100600,13),('1999-08-05',100500,42),('1999-08-05',100600,19),('1999-08-06',100500,42),('1999-08-07',100500,42),('1999-08-08',100500,42),('1999-08-10',100600,2),('1999-08-10',100600,9),('1999-08-10',100600,30),('1999-08-11',100600,14),('1999-08-12',100600,20),('1999-08-17',100500,8),('1999-08-17',100600,31),('1999-08-18',100600,15),('1999-08-19',100600,22),('1999-08-24',100600,3),('1999-08-24',100600,32),('1999-08-27',100500,43),('1999-08-31',100600,33),('1999-09-17',100100,37),('1999-09-18',100100,37),('1999-09-19',100100,37),('2000-12-18',100700,38); +INSERT INTO t1 VALUES ('1999-07-10',100100,24), ('1999-07-11',100100,25), +('1999-07-13',100600,0), ('1999-07-13',100600,4), ('1999-07-13',100600,26), +('1999-07-14',100600,10), ('1999-07-15',100600,16), ('1999-07-15',100800,45), +('1999-07-15',101000,47), ('1999-07-16',100800,46), ('1999-07-20',100600,5), +('1999-07-20',100600,27), ('1999-07-21',100600,11), ('1999-07-22',100600,17), +('1999-07-23',100100,39), ('1999-07-24',100100,39), ('1999-07-24',100500,40), +('1999-07-25',100100,39), ('1999-07-27',100600,1), ('1999-07-27',100600,6), +('1999-07-27',100600,28), ('1999-07-28',100600,12), ('1999-07-29',100500,41), +('1999-07-29',100600,18), ('1999-07-30',100500,41), ('1999-07-31',100500,41), +('1999-08-01',100700,34), ('1999-08-03',100600,7), ('1999-08-03',100600,29), +('1999-08-04',100600,13), ('1999-08-05',100500,42), ('1999-08-05',100600,19), +('1999-08-06',100500,42), ('1999-08-07',100500,42), ('1999-08-08',100500,42), +('1999-08-10',100600,2), ('1999-08-10',100600,9), ('1999-08-10',100600,30), +('1999-08-11',100600,14), ('1999-08-12',100600,20), ('1999-08-17',100500,8), +('1999-08-17',100600,31), ('1999-08-18',100600,15), ('1999-08-19',100600,22), +('1999-08-24',100600,3), ('1999-08-24',100600,32), ('1999-08-27',100500,43), +('1999-08-31',100600,33), ('1999-09-17',100100,37), ('1999-09-18',100100,37), +('1999-09-19',100100,37), ('2000-12-18',100700,38); select event_date,type,event_id from t1 WHERE event_date >= "1999-07-01" AND event_date < "1999-07-15" AND (type=100600 OR type=100100) ORDER BY event_date; event_date type event_id 1999-07-10 100100 24 @@ -35,35 +52,21 @@ CLOSED tinyint(4) DEFAULT '0' NOT NULL, ISS_DATE date DEFAULT '0000-00-00' NOT NULL, PRIMARY KEY (PAPER_ID,YEAR,ISSUE) ); -INSERT INTO t1 VALUES (3,1999,34,0,'1999-07-12'); -INSERT INTO t1 VALUES (1,1999,111,0,'1999-03-23'); -INSERT INTO t1 VALUES (1,1999,222,0,'1999-03-23'); -INSERT INTO t1 VALUES (3,1999,33,0,'1999-07-12'); -INSERT INTO t1 VALUES (3,1999,32,0,'1999-07-12'); -INSERT INTO t1 VALUES (3,1999,31,0,'1999-07-12'); -INSERT INTO t1 VALUES (3,1999,30,0,'1999-07-12'); -INSERT INTO t1 VALUES (3,1999,29,0,'1999-07-12'); -INSERT INTO t1 VALUES (3,1999,28,0,'1999-07-12'); -INSERT INTO t1 VALUES (1,1999,40,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,41,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,42,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,46,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,47,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,48,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,49,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,50,0,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,51,0,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,200,0,'1999-06-28'); -INSERT INTO t1 VALUES (1,1999,52,0,'1999-06-28'); -INSERT INTO t1 VALUES (1,1999,53,0,'1999-06-28'); -INSERT INTO t1 VALUES (1,1999,54,0,'1999-06-28'); -INSERT INTO t1 VALUES (1,1999,55,0,'1999-06-28'); -INSERT INTO t1 VALUES (1,1999,56,0,'1999-07-01'); -INSERT INTO t1 VALUES (1,1999,57,0,'1999-07-01'); -INSERT INTO t1 VALUES (1,1999,58,0,'1999-07-01'); -INSERT INTO t1 VALUES (1,1999,59,0,'1999-07-01'); -INSERT INTO t1 VALUES (1,1999,60,0,'1999-07-01'); -INSERT INTO t1 VALUES (3,1999,35,0,'1999-07-12'); +INSERT INTO t1 VALUES (3,1999,34,0,'1999-07-12'), (1,1999,111,0,'1999-03-23'), +(1,1999,222,0,'1999-03-23'), (3,1999,33,0,'1999-07-12'), +(3,1999,32,0,'1999-07-12'), (3,1999,31,0,'1999-07-12'), +(3,1999,30,0,'1999-07-12'), (3,1999,29,0,'1999-07-12'), +(3,1999,28,0,'1999-07-12'), (1,1999,40,1,'1999-05-01'), +(1,1999,41,1,'1999-05-01'), (1,1999,42,1,'1999-05-01'), +(1,1999,46,1,'1999-05-01'), (1,1999,47,1,'1999-05-01'), +(1,1999,48,1,'1999-05-01'), (1,1999,49,1,'1999-05-01'), +(1,1999,50,0,'1999-05-01'), (1,1999,51,0,'1999-05-01'), +(1,1999,200,0,'1999-06-28'), (1,1999,52,0,'1999-06-28'), +(1,1999,53,0,'1999-06-28'), (1,1999,54,0,'1999-06-28'), +(1,1999,55,0,'1999-06-28'), (1,1999,56,0,'1999-07-01'), +(1,1999,57,0,'1999-07-01'), (1,1999,58,0,'1999-07-01'), +(1,1999,59,0,'1999-07-01'), (1,1999,60,0,'1999-07-01'), +(3,1999,35,0,'1999-07-12'); select YEAR,ISSUE from t1 where PAPER_ID=3 and (YEAR>1999 or (YEAR=1999 and ISSUE>28)) order by YEAR,ISSUE; YEAR ISSUE 1999 29 @@ -88,7 +91,12 @@ PRIMARY KEY (id), KEY parent_id (parent_id), KEY level (level) ); -INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1),(179,5,2); +INSERT INTO t1 VALUES (1,0,0), (3,1,1), (4,1,1), (8,2,2), (9,2,2), (17,3,2), +(22,4,2), (24,4,2), (28,5,2), (29,5,2), (30,5,2), (31,6,2), (32,6,2), (33,6,2), +(203,7,2), (202,7,2), (20,3,2), (157,0,0), (193,5,2), (40,7,2), (2,1,1), +(15,2,2), (6,1,1), (34,6,2), (35,6,2), (16,3,2), (7,1,1), (36,7,2), (18,3,2), +(26,5,2), (27,5,2), (183,4,2), (38,7,2), (25,5,2), (37,7,2), (21,4,2), +(19,3,2), (5,1,1), (179,5,2); SELECT * FROM t1 WHERE level = 1 AND parent_id = 1; id parent_id level 3 1 1 @@ -207,3 +215,14 @@ select count(*) from t1 where art = 'J'; count(*) 213 drop table t1; +create table t1 ( id1 int not null, id2 int not null, idnull int null, c char(20), primary key (id1,id2)); +insert into t1 values (0,1,NULL,"aaa"), (1,1,NULL,"aaa"), (2,1,NULL,"aaa"), +(3,1,NULL,"aaa"), (4,1,NULL,"aaa"), (5,1,NULL,"aaa"), +(6,1,NULL,"aaa"), (7,1,NULL,"aaa"), (8,1,NULL,"aaa"), +(9,1,NULL,"aaa"), (10,1,NULL,"aaa"), (11,1,NULL,"aaa"), +(12,1,NULL,"aaa"), (13,1,NULL,"aaa"), (14,1,NULL,"aaa"), +(15,1,NULL,"aaa"), (16,1,NULL,"aaa"), (17,1,NULL,"aaa"), +(18,1,NULL,"aaa"), (19,1,NULL,"aaa"), (20,1,NULL,"aaa"); +select a.id1, b.idnull from t1 as a, t1 as b where a.id2=1 and a.id1=1 and b.id1=a.idnull order by b.id2 desc limit 1; +id1 idnull +drop table t1; diff --git a/mysql-test/r/rpl000018.result b/mysql-test/r/rpl000018.result index 7211d440524..b71f6492b97 100644 --- a/mysql-test/r/rpl000018.result +++ b/mysql-test/r/rpl000018.result @@ -1,3 +1,4 @@ +reset master; reset slave; start slave; show binary logs; diff --git a/mysql-test/r/rpl_do_grant.result b/mysql-test/r/rpl_do_grant.result new file mode 100644 index 00000000000..fec935ae7ac --- /dev/null +++ b/mysql-test/r/rpl_do_grant.result @@ -0,0 +1,26 @@ +slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +slave start; +delete from mysql.user where user='rpl_do_grant'; +delete from mysql.db where user='rpl_do_grant'; +flush privileges; +delete from mysql.user where user='rpl_ignore_grant'; +delete from mysql.db where user='rpl_ignore_grant'; +flush privileges; +grant select on *.* to rpl_do_grant@localhost; +grant drop on test.* to rpl_do_grant@localhost; +show grants for rpl_do_grant@localhost; +Grants for rpl_do_grant@localhost +GRANT SELECT ON *.* TO 'rpl_do_grant'@'localhost' +GRANT DROP ON `test`.* TO 'rpl_do_grant'@'localhost' +set password for rpl_do_grant@localhost=password("does it work?"); +select password<>'' from mysql.user where user='rpl_do_grant'; +password<>'' +1 +delete from mysql.user where user='rpl_do_grant'; +delete from mysql.db where user='rpl_do_grant'; +flush privileges; +flush privileges; diff --git a/mysql-test/r/rpl_error_ignored_table.result b/mysql-test/r/rpl_error_ignored_table.result new file mode 100644 index 00000000000..e1486220542 --- /dev/null +++ b/mysql-test/r/rpl_error_ignored_table.result @@ -0,0 +1,15 @@ +slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +slave start; +create table t1 (a int primary key); +insert into t1 values (1),(1); +Duplicate entry '1' for key 1 +show slave status; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +127.0.0.1 root MASTER_PORT 1 master-bin.001 213 slave-relay-bin.002 254 master-bin.001 Yes Yes 0 0 213 254 +show tables like 't1'; +Tables_in_test (t1) +drop table t1; diff --git a/mysql-test/r/rpl_flush_log_loop.result b/mysql-test/r/rpl_flush_log_loop.result index 954ab107123..a1709647f19 100644 --- a/mysql-test/r/rpl_flush_log_loop.result +++ b/mysql-test/r/rpl_flush_log_loop.result @@ -14,4 +14,4 @@ start slave; flush logs; show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Replicate_do_table Replicate_ignore_table Replicate_wild_do_table Replicate_wild_ignore_table Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space -127.0.0.1 root SLAVE_PORT 60 slave-bin.000001 79 relay-log.000001 122 slave-bin.000001 Yes Yes 0 0 79 122 +127.0.0.1 root SLAVE_PORT 60 slave-bin.000001 79 relay-log.000002 4 slave-bin.000001 Yes Yes 0 0 79 4 diff --git a/mysql-test/r/rpl_ignore_grant.result b/mysql-test/r/rpl_ignore_grant.result new file mode 100644 index 00000000000..6cd7d5b4c00 --- /dev/null +++ b/mysql-test/r/rpl_ignore_grant.result @@ -0,0 +1,37 @@ +slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +slave start; +delete from mysql.user where user='rpl_ignore_grant'; +delete from mysql.db where user='rpl_ignore_grant'; +flush privileges; +delete from mysql.user where user='rpl_ignore_grant'; +delete from mysql.db where user='rpl_ignore_grant'; +flush privileges; +grant select on *.* to rpl_ignore_grant@localhost; +grant drop on test.* to rpl_ignore_grant@localhost; +show grants for rpl_ignore_grant@localhost; +Grants for rpl_ignore_grant@localhost +GRANT SELECT ON *.* TO 'rpl_ignore_grant'@'localhost' +GRANT DROP ON `test`.* TO 'rpl_ignore_grant'@'localhost' +show grants for rpl_ignore_grant@localhost; +There is no such grant defined for user 'rpl_ignore_grant' on host 'localhost' +select count(*) from mysql.user where user='rpl_ignore_grant'; +count(*) +0 +select count(*) from mysql.db where user='rpl_ignore_grant'; +count(*) +0 +grant select on *.* to rpl_ignore_grant@localhost; +set password for rpl_ignore_grant@localhost=password("does it work?"); +select password<>'' from mysql.user where user='rpl_ignore_grant'; +password<>'' +0 +delete from mysql.user where user='rpl_ignore_grant'; +delete from mysql.db where user='rpl_ignore_grant'; +flush privileges; +delete from mysql.user where user='rpl_ignore_grant'; +delete from mysql.db where user='rpl_ignore_grant'; +flush privileges; diff --git a/mysql-test/r/rpl_insert_id.result b/mysql-test/r/rpl_insert_id.result index 3d7a2a0dd75..cf70e196921 100644 --- a/mysql-test/r/rpl_insert_id.result +++ b/mysql-test/r/rpl_insert_id.result @@ -20,8 +20,8 @@ b c 1 4 drop table t1; drop table t2; -create table t1(a int auto_increment, key(a)); -create table t2(b int auto_increment, c int, key(b)); +create table t1(a int auto_increment, key(a)) type=innodb; +create table t2(b int auto_increment, c int, key(b), foreign key(b) references t1(a)) type=innodb; SET FOREIGN_KEY_CHECKS=0; insert into t1 values (10); insert into t1 values (null),(null),(null); diff --git a/mysql-test/r/rpl_loaddata.result b/mysql-test/r/rpl_loaddata.result index ec9011d573f..fb3921eb677 100644 --- a/mysql-test/r/rpl_loaddata.result +++ b/mysql-test/r/rpl_loaddata.result @@ -24,3 +24,30 @@ drop table t2; drop table t3; create table t1(a int, b int, unique(b)); insert into t1 values(1,10); +load data infile '../../std_data/rpl_loaddata.dat' into table t1; +set global sql_slave_skip_counter=1; +start slave; +show slave status; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +127.0.0.1 root MASTER_PORT 1 master-bin.001 1311 slave-relay-bin.002 1352 master-bin.001 Yes Yes 0 0 1311 1352 +set sql_log_bin=0; +delete from t1; +set sql_log_bin=1; +load data infile '../../std_data/rpl_loaddata.dat' into table t1; +stop slave; +change master to master_user='test'; +change master to master_user='root'; +show slave status; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +127.0.0.1 root MASTER_PORT 1 master-bin.001 1442 slave-relay-bin.001 4 master-bin.001 No No 0 0 1442 4 +set global sql_slave_skip_counter=1; +start slave; +set sql_log_bin=0; +delete from t1; +set sql_log_bin=1; +load data infile '../../std_data/rpl_loaddata.dat' into table t1; +stop slave; +reset slave; +show slave status; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +127.0.0.1 root MASTER_PORT 1 4 slave-relay-bin.001 4 No No 0 0 0 4 diff --git a/mysql-test/r/rpl_master_pos_wait.result b/mysql-test/r/rpl_master_pos_wait.result index 801ffad6975..bbd125a98d6 100644 --- a/mysql-test/r/rpl_master_pos_wait.result +++ b/mysql-test/r/rpl_master_pos_wait.result @@ -4,6 +4,10 @@ reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; -select master_pos_wait('master-bin.999999',0,10); -master_pos_wait('master-bin.999999',0,10) +select master_pos_wait('master-bin.999999',0,2); +master_pos_wait('master-bin.999999',0,2) -1 + select master_pos_wait('master-bin.999999',0); +stop slave sql_thread; +master_pos_wait('master-bin.999999',0) +NULL diff --git a/mysql-test/r/rpl_max_relay_size.result b/mysql-test/r/rpl_max_relay_size.result new file mode 100644 index 00000000000..1fa3fcd1adb --- /dev/null +++ b/mysql-test/r/rpl_max_relay_size.result @@ -0,0 +1,61 @@ +slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +slave start; +stop slave; +create table t1 (a int); +drop table t1; +reset slave; +set global max_binlog_size=8192; +set global max_relay_log_size=8192-1; +select @@global.max_relay_log_size; +@@global.max_relay_log_size +4096 +start slave; +show slave status; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +127.0.0.1 root MASTER_PORT 1 master-bin.001 50477 slave-relay-bin.014 1221 master-bin.001 Yes Yes 0 0 50477 1221 +stop slave; +reset slave; +set global max_relay_log_size=(5*4096); +select @@global.max_relay_log_size; +@@global.max_relay_log_size +20480 +start slave; +show slave status; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +127.0.0.1 root MASTER_PORT 1 master-bin.001 50477 slave-relay-bin.004 9457 master-bin.001 Yes Yes 0 0 50477 9457 +stop slave; +reset slave; +set global max_relay_log_size=0; +select @@global.max_relay_log_size; +@@global.max_relay_log_size +0 +start slave; +show slave status; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +127.0.0.1 root MASTER_PORT 1 master-bin.001 50477 slave-relay-bin.008 1283 master-bin.001 Yes Yes 0 0 50477 1283 +stop slave; +reset slave; +flush logs; +show slave status; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +127.0.0.1 root MASTER_PORT 1 4 slave-relay-bin.001 4 No No 0 0 0 4 +reset slave; +start slave; +flush logs; +create table t1 (a int); +show slave status; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +127.0.0.1 root MASTER_PORT 1 master-bin.001 50535 slave-relay-bin.009 62 master-bin.001 Yes Yes 0 0 50535 62 +flush logs; +drop table t1; +show slave status; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +127.0.0.1 root MASTER_PORT 1 master-bin.001 50583 slave-relay-bin.010 52 master-bin.001 Yes Yes 0 0 50583 52 +flush logs; +show master status; +File Position Binlog_do_db Binlog_ignore_db +master-bin.002 4 diff --git a/mysql-test/r/rpl_relayspace.result b/mysql-test/r/rpl_relayspace.result index 610419980b5..1f2a739d3e3 100644 --- a/mysql-test/r/rpl_relayspace.result +++ b/mysql-test/r/rpl_relayspace.result @@ -6,8 +6,14 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; stop slave; create table t1 (a int); +drop table t1; +create table t1 (a int); +drop table t1; +reset slave; +start slave io_thread; +stop slave io_thread; reset slave; start slave; -select master_pos_wait('master-bin.001',5000,45)=-1; -master_pos_wait('master-bin.001',5000,45)=-1 +select master_pos_wait('master-bin.001',200,6)=-1; +master_pos_wait('master-bin.001',200,6)=-1 0 diff --git a/mysql-test/r/rpl_reset_slave.result b/mysql-test/r/rpl_reset_slave.result new file mode 100644 index 00000000000..c1bc1e8e483 --- /dev/null +++ b/mysql-test/r/rpl_reset_slave.result @@ -0,0 +1,22 @@ +slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +slave start; +show slave status; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +127.0.0.1 root MASTER_PORT 1 master-bin.001 79 slave-relay-bin.002 120 master-bin.001 Yes Yes 0 0 79 120 +stop slave; +change master to master_user='test'; +show slave status; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +127.0.0.1 test MASTER_PORT 1 master-bin.001 79 slave-relay-bin.001 4 master-bin.001 No No 0 0 79 4 +reset slave; +show slave status; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +127.0.0.1 root MASTER_PORT 1 4 slave-relay-bin.001 4 No No 0 0 0 4 +start slave; +show slave status; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +127.0.0.1 root MASTER_PORT 1 master-bin.001 79 slave-relay-bin.002 120 master-bin.001 Yes Yes 0 0 79 120 diff --git a/mysql-test/r/rpl_rotate_logs.result b/mysql-test/r/rpl_rotate_logs.result index 753edebea60..57b48827e33 100644 --- a/mysql-test/r/rpl_rotate_logs.result +++ b/mysql-test/r/rpl_rotate_logs.result @@ -5,7 +5,7 @@ ERROR HY000: Could not initialize master info structure, check permisions on mas start slave; ERROR HY000: Could not initialize master info structure, check permisions on master.info change master to master_host='127.0.0.1',master_port=MASTER_PORT, master_user='root'; -Could not initialize master info +Could not initialize master info structure, check permisions on master.info reset slave; change master to master_host='127.0.0.1',master_port=MASTER_PORT, master_user='root'; reset master; @@ -73,17 +73,15 @@ show binary logs; Log_name master-bin.000003 master-bin.000004 -master-bin.000005 -master-bin.000006 show master status; File Position Binlog_do_db Binlog_ignore_db -master-bin.000006 838 +master-bin.000004 2886 select * from t4; a testing temporary tables part 2 show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Replicate_do_table Replicate_ignore_table Replicate_wild_do_table Replicate_wild_ignore_table Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space -127.0.0.1 root MASTER_PORT 60 master-bin.000006 838 slave-relay-bin.000001 8067 master-bin.000006 Yes Yes 0 0 838 8067 +127.0.0.1 root MASTER_PORT 60 master-bin.000006 838 slave-relay-bin.000001 8067 master-bin.000004 Yes Yes 0 0 2886 8067 lock tables t3 read; select count(*) from t3 where n >= 4; count(*) diff --git a/mysql-test/r/sel000100.result b/mysql-test/r/sel000100.result index f9234815a2b..3ffa4004b84 100644 --- a/mysql-test/r/sel000100.result +++ b/mysql-test/r/sel000100.result @@ -26,3 +26,13 @@ ORDER BY link; key_link_id link NULL NULL drop table t1,t2; +CREATE TABLE t1 ( +html varchar(5) default NULL, +rin int(11) default '0', +out int(11) default '0' +) TYPE=MyISAM; +INSERT INTO t1 VALUES ('1',1,0); +SELECT DISTINCT html,SUM(out)/(SUM(rin)+1) as 'prod' FROM t1 GROUP BY rin; +html prod +1 0.00 +drop table t1; diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index fbce0e9290a..e83bbc2fc8c 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -2568,21 +2568,51 @@ fld1 fld1 250503 250505 250504 250505 250505 250505 +insert into t2 (fld1, companynr) values (999999,99); select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; companynr companyname +99 NULL +select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null; +count(*) +1199 explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1200 1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using where; Not exists explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Not exists +1 SIMPLE t2 ALL NULL NULL NULL NULL 1200 Using where; Not exists select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; companynr companynr 37 36 41 40 -explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; +delete from t2 where fld1=999999; +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0; +table type possible_keys key key_len ref rows Extra +t2 ALL NULL NULL NULL NULL 1199 Using where +t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0; +table type possible_keys key key_len ref rows Extra +t2 ALL NULL NULL NULL NULL 1199 Using where +t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0; +table type possible_keys key key_len ref rows Extra +t2 ALL NULL NULL NULL NULL 1199 Using where +t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using where +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null; +table type possible_keys key key_len ref rows Extra +t4 ALL NULL NULL NULL NULL 12 +t2 ALL NULL NULL NULL NULL 1199 Using where +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0; +table type possible_keys key key_len ref rows Extra +t4 ALL PRIMARY NULL NULL NULL 12 +t2 ALL NULL NULL NULL NULL 1199 Using where +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0; +table type possible_keys key key_len ref rows Extra +t4 ALL NULL NULL NULL NULL 12 +t2 ALL NULL NULL NULL NULL 1199 Using where +plain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using temporary 1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using where; Using index @@ -3487,3 +3517,23 @@ aa id t2_id id 5 8303 2520 2520 6 8304 2521 2521 drop table t1,t2; +create table t1 (id1 int NOT NULL); +create table t2 (id2 int NOT NULL); +create table t3 (id3 int NOT NULL); +create table t4 (id4 int NOT NULL, id44 int NOT NULL, KEY (id4)); +insert into t1 values (1); +insert into t1 values (2); +insert into t2 values (1); +insert into t4 values (1,1); +explain select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3 +left join t4 on id3 = id4 where id2 = 1 or id4 = 1; +table type possible_keys key key_len ref rows Extra +t3 system NULL NULL NULL NULL 0 const row not found +t1 ALL NULL NULL NULL NULL 2 +t2 ALL NULL NULL NULL NULL 1 +t4 ALL id4 NULL NULL NULL 1 Using where +select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3 +left join t4 on id3 = id4 where id2 = 1 or id4 = 1; +id1 id2 id3 id4 id44 +1 1 NULL NULL NULL +drop table t1,t2,t3,t4; diff --git a/mysql-test/r/select_safe.result b/mysql-test/r/select_safe.result index 0f2a781d073..dfb12eacd9c 100644 --- a/mysql-test/r/select_safe.result +++ b/mysql-test/r/select_safe.result @@ -13,7 +13,7 @@ a b 1 test 2 test2 update t1 set b="a" where a=1; -select 1 from t1,t1 as t2,t1 as t3,t1 as t4; +select 1 from t1,t1 as t2,t1 as t3; 1 1 1 @@ -35,18 +35,19 @@ update t1 set b="a" limit 1; update t1 set b="a" where b="b" limit 2; delete from t1 where b="test" limit 1; delete from t1 where a+0=1 limit 2; +alter table t1 add key b (b); SET MAX_JOIN_SIZE=2; SELECT @@MAX_JOIN_SIZE, @@SQL_BIG_SELECTS; @@MAX_JOIN_SIZE @@SQL_BIG_SELECTS 2 0 insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"); -SELECT * from t1; +SELECT * from t1 order by a; ERROR 42000: The SELECT would examine more rows than MAX_JOIN_SIZE. Check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is ok SET SQL_BIG_SELECTS=1; -SELECT * from t1; +SELECT * from t1 order by a; a b -3 a 2 test2 +3 a 4 a 5 a SET MAX_JOIN_SIZE=2; @@ -55,9 +56,26 @@ ERROR 42000: The SELECT would examine more rows than MAX_JOIN_SIZE. Check your W SET MAX_JOIN_SIZE=DEFAULT; SELECT * from t1; a b -3 a 2 test2 +3 a 4 a 5 a +SELECT @@MAX_SEEKS_FOR_KEY; +@@max_seeks_for_key +4294967295 +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"); +explain select * from t1,t1 as t2 where t1.b=t2.b; +table type possible_keys key key_len ref rows Extra +t1 ALL b NULL NULL NULL 21 +t2 ALL b NULL NULL NULL 16 Using where +set MAX_SEEKS_FOR_KEY=1; +explain select * from t1,t1 as t2 where t1.b=t2.b; +table type possible_keys key key_len ref rows Extra +t1 ALL b NULL NULL NULL 21 +t2 ref b b 21 t1.b 6 Using where +SET MAX_SEEKS_FOR_KEY=DEFAULT; drop table t1; SET SQL_SAFE_UPDATES=0,SQL_SELECT_LIMIT=DEFAULT, SQL_MAX_JOIN_SIZE=DEFAULT; diff --git a/mysql-test/r/symlink.result b/mysql-test/r/symlink.result index f12b98ba041..216fb4d6124 100644 --- a/mysql-test/r/symlink.result +++ b/mysql-test/r/symlink.result @@ -1,4 +1,5 @@ drop table if exists t1,t2,t7,t8,t9; +drop database if exists mysqltest; create table t1 (a int not null auto_increment, b char(16) not null, primary key (a)); create table t2 (a int not null auto_increment, b char(16) not null, primary key (a)); insert into t1 (b) values ("test"),("test1"),("test2"),("test3"); @@ -51,11 +52,11 @@ Got one of the listed errors Got one of the listed errors Got one of the listed errors Got one of the listed errors -alter table t9 rename test_mysqltest.t9; -select count(*) from test_mysqltest.t9; +alter table t9 rename mysqltest.t9; +select count(*) from mysqltest.t9; count(*) 16724 -show create table test_mysqltest.t9; +show create table mysqltest.t9; Table Create Table t9 CREATE TABLE `t9` ( `a` int(11) NOT NULL auto_increment, @@ -64,4 +65,4 @@ t9 CREATE TABLE `t9` ( `d` int(11) NOT NULL default '0', PRIMARY KEY (`a`) ) TYPE=MyISAM CHARSET=latin1 -drop database test_mysqltest; +drop database mysqltest; diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result index cf6533eef67..cebd005c2c8 100644 --- a/mysql-test/r/type_date.result +++ b/mysql-test/r/type_date.result @@ -32,6 +32,8 @@ datum 2000-01-02 2000-01-03 2000-01-04 +SELECT * FROM t1 WHERE datum BETWEEN "2000-1-2" AND datum - INTERVAL 100 DAY; +datum DROP TABLE t1; CREATE TABLE t1 ( user_id char(10), diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index 5a9a8699d06..95e64a2a70e 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -52,10 +52,17 @@ a 0000-00-00 00:00:00 drop table t1; create table t1 (id int, dt datetime); -insert into t1 values (1,"2001-08-14 00:00:00"),(2,"2001-08-15 00:00:00"),(3,"2001-08-16 00:00:00"); +insert into t1 values (1,"2001-08-14 00:00:00"),(2,"2001-08-15 00:00:00"),(3,"2001-08-16 00:00:00"),(4,"2003-09-15 01:20:30"); select * from t1 where dt='2001-08-14 00:00:00' and dt = if(id=1,'2001-08-14 00:00:00','1999-08-15'); id dt 1 2001-08-14 00:00:00 +create index dt on t1 (dt); +select * from t1 where dt > 20021020; +id dt +4 2003-09-15 01:20:30 +select * from t1 ignore index (dt) where dt > 20021020; +id dt +4 2003-09-15 01:20:30 drop table t1; CREATE TABLE `t1` ( `date` datetime NOT NULL default '0000-00-00 00:00:00', diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 6d8bd263546..c29859bd4d4 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -90,6 +90,13 @@ explain (select a,b from t1 limit 2) union all (select a,b from t2 order by a l id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 2 UNION t2 ALL NULL NULL NULL NULL 4 Using filesort +select sql_calc_found_rows a,b from t1 union all select a,b from t2 limit 2; +a b +1 a +2 b +select found_rows(); +found_rows() +6 explain select a,b from t1 union all select a,b from t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 @@ -269,6 +276,35 @@ uid rl g1 cid gg uid rl g1 cid gg 1 NULL V1 NULL 1 drop table t1,t2,t3,t4,t5,t6; +CREATE TABLE t1 (a int not null, b char (10) not null); +insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c'); +CREATE TABLE t2 (a int not null, b char (10) not null); +insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e'); +create table t3 select a,b from t1 union select a,b from t2; +create table t4 (select a,b from t1) union (select a,b from t2) limit 2; +insert into t4 select a,b from t1 union select a,b from t2; +insert into t3 (select a,b from t1) union (select a,b from t2) limit 2; +select * from t3; +a b +1 a +2 b +3 c +4 d +5 f +6 e +1 a +2 b +select * from t4; +a b +1 a +2 b +1 a +2 b +3 c +4 d +5 f +6 e +drop table t1,t2,t3,t4; CREATE TABLE t1 ( id int(3) unsigned default '0') TYPE=MyISAM; INSERT INTO t1 (id) VALUES("1"); CREATE TABLE t2 ( id int(3) unsigned default '0', id_master int(5) default '0', text1 varchar(5) default NULL, text2 varchar(5) default NULL) TYPE=MyISAM; diff --git a/mysql-test/r/user_var.result b/mysql-test/r/user_var.result index 3a53dbdded0..23253f60de9 100644 --- a/mysql-test/r/user_var.result +++ b/mysql-test/r/user_var.result @@ -30,3 +30,15 @@ explain select * from t1 where i=@vv1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref i i 4 const 1 Using where drop table t1,t2; +select @a:=10, @b:=1, @a > @b, @a < @b; +@a:=10 @b:=1 @a > @b @a < @b +10 1 1 0 +select @a:="10", @b:="1", @a > @b, @a < @b; +@a:="10" @b:="1" @a > @b @a < @b +10 1 1 0 +select @a:=10, @b:=2, @a > @b, @a < @b; +@a:=10 @b:=2 @a > @b @a < @b +10 2 1 0 +select @a:="10", @b:="2", @a > @b, @a < @b; +@a:="10" @b:="2" @a > @b @a < @b +10 2 0 1 diff --git a/mysql-test/t/alias.test b/mysql-test/t/alias.test index 64dd481d37d..e0fa10d56d5 100644 --- a/mysql-test/t/alias.test +++ b/mysql-test/t/alias.test @@ -65,3 +65,24 @@ INSERT INTO t1 VALUES (3359362,406,3359362,'Mustermann Musterfrau',7001,'2000-05 SELECT ELT(FIELD(kundentyp,'PP','PPA','PG','PGA','FK','FKA','FP','FPA','K','KA','V','VA',''), 'Privat (Private Nutzung)','Privat (Private Nutzung) Sitz im Ausland','Privat (geschaeftliche Nutzung)','Privat (geschaeftliche Nutzung) Sitz im Ausland','Firma (Kapitalgesellschaft)','Firma (Kapitalgesellschaft) Sitz im Ausland','Firma (Personengesellschaft)','Firma (Personengesellschaft) Sitz im Ausland','oeff. rechtl. Koerperschaft','oeff. rechtl. Koerperschaft Sitz im Ausland','Eingetragener Verein','Eingetragener Verein Sitz im Ausland','Typ unbekannt') AS Kundentyp ,kategorie FROM t1 WHERE hdl_nr < 2000000 AND kategorie IN ('Prepaid','Mobilfunk') AND st_klasse = 'Workflow' GROUP BY kundentyp ORDER BY kategorie; drop table t1; + +# +# test case for #570 +# + +CREATE TABLE t1 ( + AUFNR varchar(12) NOT NULL default '', + PLNFL varchar(6) NOT NULL default '', + VORNR varchar(4) NOT NULL default '', + xstatus_vor smallint(5) unsigned NOT NULL default '0', + +); + +INSERT INTO t1 VALUES ('40004712','000001','0010',9); +INSERT INTO t1 VALUES ('40004712','000001','0020',0); + +UPDATE t1 SET t1.xstatus_vor = Greatest(t1.xstatus_vor,1) WHERE t1.aufnr = +"40004712" AND t1.plnfl = "000001" AND t1.vornr > "0010" ORDER BY t1.vornr +ASC LIMIT 1; + +drop table t1; diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index 346ad8fe80e..06a5db13ea3 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -171,3 +171,65 @@ alter table t1 change a a char(10) character set koi8r; select a,hex(a) from t1; drop table t1; + +# +# Test ALTER TABLE ENABLE/DISABLE keys when things are locked +# + +CREATE TABLE t1 ( + Host varchar(16) binary NOT NULL default '', + User varchar(16) binary NOT NULL default '', + PRIMARY KEY (Host,User) +) TYPE=MyISAM; + +ALTER TABLE t1 DISABLE KEYS; +LOCK TABLES t1 WRITE; +INSERT INTO t1 VALUES ('localhost','root'),('localhost',''),('games','monty'); +SHOW INDEX FROM t1; +ALTER TABLE t1 ENABLE KEYS; +UNLOCK TABLES; +CHECK TABLES t1; +DROP TABLE t1; + +# +# Test with two keys +# + +CREATE TABLE t1 ( + Host varchar(16) binary NOT NULL default '', + User varchar(16) binary NOT NULL default '', + PRIMARY KEY (Host,User), + KEY (Host) +) TYPE=MyISAM; + +ALTER TABLE t1 DISABLE KEYS; +SHOW INDEX FROM t1; +LOCK TABLES t1 WRITE; +INSERT INTO t1 VALUES ('localhost','root'),('localhost',''); +SHOW INDEX FROM t1; +ALTER TABLE t1 ENABLE KEYS; +SHOW INDEX FROM t1; +UNLOCK TABLES; +CHECK TABLES t1; + +# Test RENAME with LOCK TABLES +LOCK TABLES t1 WRITE; +ALTER TABLE t1 RENAME t2; +UNLOCK TABLES; +select * from t2; +DROP TABLE t2; + +# +# Test disable keys with locking +# +CREATE TABLE t1 ( + Host varchar(16) binary NOT NULL default '', + User varchar(16) binary NOT NULL default '', + PRIMARY KEY (Host,User), + KEY (Host) +) TYPE=MyISAM; + +LOCK TABLES t1 WRITE; +ALTER TABLE t1 DISABLE KEYS; +SHOW INDEX FROM t1; +DROP TABLE t1; diff --git a/mysql-test/t/convert.test b/mysql-test/t/convert.test new file mode 100644 index 00000000000..f26ef3a8c72 --- /dev/null +++ b/mysql-test/t/convert.test @@ -0,0 +1,11 @@ +# Test of character set conversions + +# Test that SET DEFAULT works + +select @@convert_character_set; +select @@global.convert_character_set; +show variables like "%convert_character_set%"; +SET CHARACTER SET cp1251_koi8; +select @@convert_character_set; +SET CHARACTER SET DEFAULT; +select @@convert_character_set; diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 9bc37a0864d..ebb3854309b 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -215,3 +215,10 @@ CREATE TABLE t1 (a int not null); show create table t1; SET SESSION table_type=default; drop table t1; + +# +# Bug # 801 +# + +create table t1 select x'4132'; +drop table t1; diff --git a/mysql-test/t/ctype_cp1251-master.opt b/mysql-test/t/ctype_cp1251-master.opt new file mode 100644 index 00000000000..af089d9f176 --- /dev/null +++ b/mysql-test/t/ctype_cp1251-master.opt @@ -0,0 +1,2 @@ +--default-character-set=cp1251 --new + diff --git a/mysql-test/t/ctype_cp1251.test b/mysql-test/t/ctype_cp1251.test new file mode 100644 index 00000000000..fffade35389 --- /dev/null +++ b/mysql-test/t/ctype_cp1251.test @@ -0,0 +1,17 @@ +# Test of charset cp1251 + +--disable_warnings +drop table if exists t1; +--enable_warnings + +# +# Test problem with LEFT() (Bug #514) +# + +create table t1 (a varchar(10) not null); +insert into t1 values ("a"),("ab"),("abc"); +select * from t1; +select a, left(a,1) as b from t1; +select a, left(a,1) as b from t1 group by a; +SELECT DISTINCT RIGHT(a,1) from t1; +drop table t1; diff --git a/mysql-test/t/ctype_latin1_de.test b/mysql-test/t/ctype_latin1_de.test index 00ac128a478..afad89729b4 100644 --- a/mysql-test/t/ctype_latin1_de.test +++ b/mysql-test/t/ctype_latin1_de.test @@ -37,6 +37,10 @@ select strcmp('ßa','ss'),strcmp('ssa','ß'),strcmp('sssb','sßa'),strcmp('ß','s'); select strcmp('u','öa'),strcmp('u','ö'); # +# overlapping combo's +# +select strcmp('sä', 'ßa'), strcmp('aä', 'äx'); +# # Some other simple tests with the current character set # diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test index 859c4042b1d..0563b432873 100644 --- a/mysql-test/t/distinct.test +++ b/mysql-test/t/distinct.test @@ -62,7 +62,7 @@ drop table t1; # CREATE TABLE t1 (a int(10) unsigned not null primary key,b int(10) unsigned); -INSERT INTO t1 VALUES (1,1),(2,1); +INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1); CREATE TABLE t2 (a int(10) unsigned not null, key (A)); INSERT INTO t2 VALUES (1),(2); CREATE TABLE t3 (a int(10) unsigned, key(A), b text); @@ -274,3 +274,14 @@ CREATE TABLE t2 (a int primary key, b int, c int); INSERT t2 VALUES (3,4,5); SELECT DISTINCT t1.a, t2.b FROM t1, t2 WHERE t1.a=1 ORDER BY t2.c; DROP TABLE t1,t2; + +# +# Test of LEFT() with distinct +# + +CREATE table t1 ( `id` int(11) NOT NULL auto_increment, `name` varchar(50) NOT NULL default '', PRIMARY KEY (`id`)) TYPE=MyISAM AUTO_INCREMENT=3 ; +INSERT INTO t1 VALUES (1, 'aaaaa'); +INSERT INTO t1 VALUES (3, 'aaaaa'); +INSERT INTO t1 VALUES (2, 'eeeeeee'); +select distinct left(name,1) as name from t1; +drop table t1; diff --git a/mysql-test/t/drop.test b/mysql-test/t/drop.test index 307dd45654d..e624ded0102 100644 --- a/mysql-test/t/drop.test +++ b/mysql-test/t/drop.test @@ -29,7 +29,6 @@ create database mysqltest; drop database mysqltest; # test drop/create database and FLUSH TABLES WITH READ LOCK -drop database if exists mysqltest; flush tables with read lock; --error 1209,1223; create database mysqltest; diff --git a/mysql-test/t/flush.test b/mysql-test/t/flush.test index 44a821b63c2..9ee6b5d76b8 100644 --- a/mysql-test/t/flush.test +++ b/mysql-test/t/flush.test @@ -69,4 +69,3 @@ connection con2; insert into t1 values (345); select * from t1; drop table t1; - diff --git a/mysql-test/t/fulltext.test b/mysql-test/t/fulltext.test index d06e2dce0a1..8c6bb97edf1 100644 --- a/mysql-test/t/fulltext.test +++ b/mysql-test/t/fulltext.test @@ -14,7 +14,7 @@ INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'), ('Full-text search in MySQL', 'implements vector space model'); # nl search - + select * from t1 where MATCH(a,b) AGAINST ("collections"); select * from t1 where MATCH(a,b) AGAINST ("indexes"); select * from t1 where MATCH(a,b) AGAINST ("indexes collections"); @@ -23,6 +23,20 @@ select * from t1 where MATCH(a,b) AGAINST ("only"); # UNION of fulltext's select * from t1 where MATCH(a,b) AGAINST ("collections") UNION ALL select * from t1 where MATCH(a,b) AGAINST ("indexes"); + +# add_ft_keys() tests + +explain select * from t1 where MATCH(a,b) AGAINST ("collections"); +explain select * from t1 where MATCH(a,b) AGAINST ("collections")>0; +explain select * from t1 where MATCH(a,b) AGAINST ("collections")>1; +explain select * from t1 where MATCH(a,b) AGAINST ("collections")>=0; +explain select * from t1 where MATCH(a,b) AGAINST ("collections")>=1; +explain select * from t1 where 0<MATCH(a,b) AGAINST ("collections"); +explain select * from t1 where 1<MATCH(a,b) AGAINST ("collections"); +explain select * from t1 where 0<=MATCH(a,b) AGAINST ("collections"); +explain select * from t1 where 1<=MATCH(a,b) AGAINST ("collections"); +explain select * from t1 where MATCH(a,b) AGAINST ("collections")>0 and a like '%ll%'; + # boolean search select * from t1 where MATCH(a,b) AGAINST("support -collections" IN BOOLEAN MODE); @@ -50,6 +64,10 @@ select * from t1 where MATCH a,b AGAINST ('"text i"' IN BOOLEAN MODE); select * from t1 where MATCH a AGAINST ("search" IN BOOLEAN MODE); select * from t1 where MATCH b AGAINST ("sear*" IN BOOLEAN MODE); +# UNION of fulltext's + +select * from t1 where MATCH(a,b) AGAINST ("collections") UNION ALL select * from t1 where MATCH(a,b) AGAINST ("indexes"); + #update/delete with fulltext index delete from t1 where a like "MySQL%"; @@ -176,7 +194,7 @@ select * from t1 where match (a) against ('aaaa'); drop table t1; # -# bug 283 by jocelyn fournier <joc@presence-pc.com> +# bug #283 by jocelyn fournier <joc@presence-pc.com> # FULLTEXT index on a TEXT filed converted to a CHAR field doesn't work anymore # @@ -186,3 +204,17 @@ select ref_mag from t1 where match ref_mag against ('+test' in boolean mode); alter table t1 change ref_mag ref_mag char (255) not null; select ref_mag from t1 where match ref_mag against ('+test' in boolean mode); drop table t1; + +# +# bug #942: JOIN +# + +create table t1 (t1_id int(11) primary key, name varchar(32)); +insert into t1 values (1, 'data1'); +insert into t1 values (2, 'data2'); +create table t2 (t2_id int(11) primary key, t1_id int(11), name varchar(32)); +insert into t2 values (1, 1, 'xxfoo'); +insert into t2 values (2, 1, 'xxbar'); +insert into t2 values (3, 1, 'xxbuz'); +select * from t1 join t2 using(`t1_id`) where match (t1.name, t2.name) against('xxfoo' in boolean mode); +drop table t1,t2; diff --git a/mysql-test/t/fulltext_left_join.test b/mysql-test/t/fulltext_left_join.test index 4fce8ee287e..855649923c4 100644 --- a/mysql-test/t/fulltext_left_join.test +++ b/mysql-test/t/fulltext_left_join.test @@ -31,3 +31,15 @@ select match(t1.texte,t1.sujet,t1.motsclefs) against('droit' IN BOOLEAN MODE) drop table t1, t2; +# +# Bug #484, reported by Stephen Brandon <stephen@brandonitconsulting.co.uk> +# + +create table t1 (venue_id int(11) default null, venue_text varchar(255) default null, dt datetime default null) type=myisam; +insert into t1 (venue_id, venue_text, dt) values (1, 'a1', '2003-05-23 19:30:00'),(null, 'a2', '2003-05-23 19:30:00'); +create table t2 (name varchar(255) not null default '', entity_id int(11) not null auto_increment, primary key (entity_id), fulltext key name (name)) type=myisam; +insert into t2 (name, entity_id) values ('aberdeen town hall', 1), ('glasgow royal concert hall', 2), ('queen\'s hall, edinburgh', 3); +select * from t1 left join t2 on venue_id = entity_id where match(name) against('aberdeen' in boolean mode) and dt = '2003-05-23 19:30:00'; +select * from t1 left join t2 on venue_id = entity_id where match(name) against('aberdeen') and dt = '2003-05-23 19:30:00'; +drop table t1,t2; + diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test index be64c170fa1..d15c26279ec 100644 --- a/mysql-test/t/func_misc.test +++ b/mysql-test/t/func_misc.test @@ -12,3 +12,8 @@ select inet_ntoa(1099511627775),inet_ntoa(4294902271),inet_ntoa(511); # Test for core dump with nan # select length(format('nan', 2)) > 0; + +# +# Test for bug #628 +# +select concat("$",format(2500,2)); diff --git a/mysql-test/t/func_set.test b/mysql-test/t/func_set.test index 60d67a77562..e462e0a96c3 100644 --- a/mysql-test/t/func_set.test +++ b/mysql-test/t/func_set.test @@ -33,3 +33,4 @@ insert into t2 values (1,1),(2,1),(3,1),(4,2); select one.id, elt(two.val,'one','two') from t1 one, t2 two where two.id=one.id; select one.id, elt(two.val,'one','two') from t1 one, t2 two where two.id=one.id order by one.id; drop table t1,t2; +select interval(null, 1, 10, 100); diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 4c996121446..14267976104 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -315,3 +315,24 @@ create table t7 (s1 char); select * from t7 where concat(s1 collate latin1_general_ci,s1 collate latin1_swedish_ci) = 'AA'; drop table t7; + +CREATE TABLE t1 ( + wid int(10) unsigned NOT NULL auto_increment, + data_podp date default NULL, + status_wnio enum('nowy','podp','real','arch') NOT NULL default 'nowy', + PRIMARY KEY(wid), +); + +INSERT INTO t1 VALUES (8,NULL,'real'); +INSERT INTO t1 VALUES (9,NULL,'nowy'); +SELECT elt(status_wnio,data_podp) FROM t1 GROUP BY wid; +DROP TABLE t1; + +# +# test for #739 + +CREATE TABLE t1 (title text) TYPE=MyISAM; +INSERT INTO t1 VALUES ('Congress reconvenes in September to debate welfare and adult education'); +INSERT INTO t1 VALUES ('House passes the CAREERS bill'); +SELECT CONCAT("</a>",RPAD("",(55 - LENGTH(title)),".")) from t1; +DROP TABLE t1; diff --git a/mysql-test/t/func_test.test b/mysql-test/t/func_test.test index 2834d5bd9c7..4f44ec8fef1 100644 --- a/mysql-test/t/func_test.test +++ b/mysql-test/t/func_test.test @@ -20,6 +20,11 @@ select 1 XOR 1, 1 XOR 0, 0 XOR 1, 0 XOR 0, NULL XOR 1, 1 XOR NULL, 0 XOR NULL; select 10 % 7, 10 mod 7, 10 div 3; select (1 << 64)-1, ((1 << 64)-1) DIV 1, ((1 << 64)-1) DIV 2; +create table t1 (a int); +insert t1 values (1); +select * from t1 where 1 xor 1; +drop table t1; + # # Coercibility # diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index af222b0b3cc..d09fd12edc2 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -129,6 +129,8 @@ select extract(MONTH FROM "2001-02-00"); create table t1 (ctime varchar(20)); insert into t1 values ('2001-01-12 12:23:40'); select ctime, hour(ctime) from t1; +# test bug 614 (multiple extracts in where) +select ctime from t1 where extract(MONTH FROM ctime) = 1 AND extract(YEAR FROM ctime) = 2001; drop table t1; # diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test index dcddbc99efa..b1338b790fd 100644 --- a/mysql-test/t/grant.test +++ b/mysql-test/t/grant.test @@ -45,8 +45,37 @@ show grants for mysqltest_1@localhost; revoke all privileges on mysqltest.* from mysqltest_1@localhost; delete from mysql.user where user='mysqltest_1'; flush privileges; -grant usage on test.* to user@localhost with grant option; -show grants for user@localhost; +grant usage on test.* to mysqltest_1@localhost with grant option; +show grants for mysqltest_1@localhost; +delete from mysql.user where user='mysqltest_1'; +delete from mysql.db where user='mysqltest_1'; +delete from mysql.tables_priv where user='mysqltest_1'; +delete from mysql.columns_priv where user='mysqltest_1'; +flush privileges; + +# +# Test what happens when you have same table and colum level grants +# + +create table t1 (a int); +GRANT select,update,insert on t1 to mysqltest_1@localhost; +GRANT select (a), update (a),insert(a), references(a) on t1 to mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1"; +REVOKE select (a), update on t1 from mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +REVOKE insert,insert (a) on t1 from mysqltest_1@localhost; +GRANT references on t1 to mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1"; +delete from mysql.user where user='mysqltest_1'; +delete from mysql.db where user='mysqltest_1'; +delete from mysql.tables_priv where user='mysqltest_1'; +delete from mysql.columns_priv where user='mysqltest_1'; +flush privileges; +drop table t1; +--error 1221 +GRANT FILE on mysqltest.* to mysqltest_1@localhost; # # Test for 'drop user', 'revoke privileges, grant' diff --git a/mysql-test/t/grant_cache.test b/mysql-test/t/grant_cache.test index 2d704a770aa..9ba845d6baa 100644 --- a/mysql-test/t/grant_cache.test +++ b/mysql-test/t/grant_cache.test @@ -5,6 +5,7 @@ # --disable_warnings drop table if exists test.t1,mysqltest.t1,mysqltest.t2; +drop database if exists mysqltest; --enable_warnings reset query cache; diff --git a/mysql-test/t/handler.test b/mysql-test/t/handler.test index 15d2e954a95..09dd06c817c 100644 --- a/mysql-test/t/handler.test +++ b/mysql-test/t/handler.test @@ -99,3 +99,16 @@ handler t1 read first limit 2,2; delete from t1 limit 3; handler t1 read first; drop table t1; + +# +#test for #751 +# +create table t1(a int, index(a)); +insert into t1 values (1), (2), (3); +handler t1 open; +--error 1054 +handler t1 read a=(W); +--error 1210 +handler t1 read a=(a); +drop table t1; + diff --git a/mysql-test/t/heap.test b/mysql-test/t/heap.test index 22d9e57a574..c708ea2baf6 100644 --- a/mysql-test/t/heap.test +++ b/mysql-test/t/heap.test @@ -132,6 +132,15 @@ SELECT * FROM t1 WHERE b<=>NULL; INSERT INTO t1 VALUES (1,3); DROP TABLE t1; +CREATE TABLE t1 ( + a int default NULL, + key a (a) +) TYPE=HEAP; +INSERT INTO t1 VALUES (10), (10), (10); +EXPLAIN SELECT * FROM t1 WHERE a=10; +SELECT * FROM t1 WHERE a=10; +DROP TABLE t1; + # # Test when deleting all rows # diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index 635a15baa41..5ca357efe90 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -6,6 +6,7 @@ --disable_warnings drop table if exists t1,t2,t3; +drop database if exists mysqltest; --enable_warnings create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) type=innodb; @@ -45,9 +46,12 @@ update ignore t1 set id=id+1; # This will change all rows select * from t1; update ignore t1 set id=1023 where id=1010; select * from t1 where parent_id=102; -# explain select level from t1 where level=1; -# explain select level,id from t1 where level=1; -# explain select level,id,parent_id from t1 where level=1; +--replace_column 9 # +explain select level from t1 where level=1; +--replace_column 9 # +explain select level,id from t1 where level=1; +--replace_column 9 # +explain select level,id,parent_id from t1 where level=1; select level,id from t1 where level=1; select level,id,parent_id from t1 where level=1; optimize table t1; @@ -90,6 +94,7 @@ select * from t1; create index skr on t1 (a); insert into t1 values (3,""), (4,"testing"); analyze table t1; +--replace_column 7 # show keys from t1; drop table t1; @@ -228,6 +233,7 @@ drop table t1; CREATE TABLE t1 (a int not null, b int not null,c int not null, key(a),primary key(a,b), unique(c),key(a),unique(b)); +--replace_column 7 # show index from t1; drop table t1; @@ -345,7 +351,8 @@ update ignore t1 set id=id+1; # This will change all rows select * from t1; update ignore t1 set id=1023 where id=1010; select * from t1 where parent_id=102; -# explain select level from t1 where level=1; +--replace_column 9 # +explain select level from t1 where level=1; select level,id from t1 where level=1; select level,id,parent_id from t1 where level=1; select level,id from t1 where level=1 order by id; @@ -402,6 +409,7 @@ create table t1 (a varchar(100) not null, primary key(a), b int not null) type=i insert into t1 values("hello",1),("world",2); select * from t1 order by b desc; optimize table t1; +--replace_column 7 # show keys from t1; drop table t1; @@ -447,6 +455,7 @@ DROP TABLE t1; create table t1 (a int primary key,b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int, a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, b1 int, b2 int, b3 int, b4 int, b5 int, b6 int) type = innodb; insert into t1 values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1); +--replace_column 9 # explain select * from t1 where a > 0 and a < 50; drop table t1; @@ -573,13 +582,21 @@ drop table t1; create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) type=innodb; insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4); +--replace_column 9 # explain select * from t1 order by a; +--replace_column 9 # explain select * from t1 order by b; +--replace_column 9 # explain select * from t1 order by c; +--replace_column 9 # explain select a from t1 order by a; +--replace_column 9 # explain select b from t1 order by b; +--replace_column 9 # explain select a,b from t1 order by b; +--replace_column 9 # explain select a,b from t1; +--replace_column 9 # explain select a,b,c from t1; drop table t1; @@ -840,8 +857,10 @@ insert into t1 (a) select b from t2; insert into t2 (a) select b from t1; insert into t1 (a) select b from t2; select count(*) from t1; +--replace_column 9 # explain select * from t1 where c between 1 and 10000; update t1 set c=a; +--replace_column 9 # explain select * from t1 where c between 1 and 10000; drop table t1,t2; diff --git a/mysql-test/t/insert.test b/mysql-test/t/insert.test index bfa8aac7a1f..9cc0bf8c46c 100644 --- a/mysql-test/t/insert.test +++ b/mysql-test/t/insert.test @@ -54,6 +54,17 @@ select * from t1; drop table t1; # +#Test of behaviour with INSERT VALUES (NULL) +# + +create table t1 (id int NOT NULL DEFAULT 8); +-- error 1048 +insert into t1 values(NULL); +insert into t1 values (1), (NULL), (2); +select * from t1; +drop table t1; + +# # Test of mysqld crash with fully qualified column names # diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test index 5bd7b95f560..4ac5a69a508 100644 --- a/mysql-test/t/insert_select.test +++ b/mysql-test/t/insert_select.test @@ -69,9 +69,8 @@ INSERT INTO t1 (numeropost,icone,contenu,pseudo,date,signature,ip) SELECT 1718,icone,contenu,pseudo,date,signature,ip FROM t2 WHERE numeropost=9 ORDER BY numreponse ASC; -DROP TABLE IF EXISTS t1,t2; +DROP TABLE t1,t2; -# Addendum by Guilhem: # Check if a partly-completed INSERT SELECT in a MyISAM table goes # into the binlog @@ -88,3 +87,51 @@ let $VERSION=`select version()`; --replace_result $VERSION VERSION show binlog events; drop table t1, t2; +drop table if exists t1, t2; + +# +# Test of insert ... select from same table +# + +create table t1 (a int not null); +create table t2 (a int not null); +insert into t1 values (1); +insert into t1 values (a+2); +insert into t1 values (a+3); +insert into t1 values (4),(a+5); +insert into t1 select * from t1; +select * from t1; +insert into t1 select * from t1 as t2; +select * from t1; +insert into t2 select * from t1 as t2; +select * from t1; +insert into t1 select t2.a from t1,t2; +select * from t1; +--error 1066 +insert into t1 select * from t1,t1; +drop table t1,t2; + +# +# test replace ... select +# + +create table t1 (a int not null primary key, b char(10)); +create table t2 (a int not null, b char(10)); +insert into t1 values (1,"t1:1"),(3,"t1:3"); +insert into t2 values (2,"t2:2"), (3,"t2:3"); +--error 1062 +insert into t1 select * from t2; +select * from t1; +replace into t1 select * from t2; +select * from t1; +drop table t1,t2; + +# +# Test that caused uninitialized memory access in auto_increment_key update +# + +CREATE TABLE t1 ( USID INTEGER UNSIGNED, ServerID TINYINT UNSIGNED, State ENUM ('unknown', 'Access-Granted', 'Session-Active', 'Session-Closed' ) NOT NULL DEFAULT 'unknown', SessionID CHAR(32), User CHAR(32) NOT NULL DEFAULT '<UNKNOWN>', NASAddr INTEGER UNSIGNED, NASPort INTEGER UNSIGNED, NASPortType INTEGER UNSIGNED, ConnectSpeed INTEGER UNSIGNED, CarrierType CHAR(32), CallingStationID CHAR(32), CalledStationID CHAR(32), AssignedAddr INTEGER UNSIGNED, SessionTime INTEGER UNSIGNED, PacketsIn INTEGER UNSIGNED, OctetsIn INTEGER UNSIGNED, PacketsOut INTEGER UNSIGNED, OctetsOut INTEGER UNSIGNED, TerminateCause INTEGER UNSIGNED, UnauthTime TINYINT UNSIGNED, AccessRequestTime DATETIME, AcctStartTime DATETIME, AcctLastTime DATETIME, LastModification TIMESTAMP NOT NULL); +CREATE TABLE t2 ( USID INTEGER UNSIGNED AUTO_INCREMENT, ServerID TINYINT UNSIGNED, State ENUM ('unknown', 'Access-Granted', 'Session-Active', 'Session-Closed' ) NOT NULL DEFAULT 'unknown', SessionID CHAR(32), User TEXT NOT NULL, NASAddr INTEGER UNSIGNED, NASPort INTEGER UNSIGNED, NASPortType INTEGER UNSIGNED, ConnectSpeed INTEGER UNSIGNED, CarrierType CHAR(32), CallingStationID CHAR(32), CalledStationID CHAR(32), AssignedAddr INTEGER UNSIGNED, SessionTime INTEGER UNSIGNED, PacketsIn INTEGER UNSIGNED, OctetsIn INTEGER UNSIGNED, PacketsOut INTEGER UNSIGNED, OctetsOut INTEGER UNSIGNED, TerminateCause INTEGER UNSIGNED, UnauthTime TINYINT UNSIGNED, AccessRequestTime DATETIME, AcctStartTime DATETIME, AcctLastTime DATETIME, LastModification TIMESTAMP NOT NULL, INDEX(USID,ServerID,NASAddr,SessionID), INDEX(AssignedAddr)); +INSERT INTO t1 VALUES (39,42,'Access-Granted','46','491721000045',2130706433,17690,NULL,NULL,'Localnet','491721000045','49172200000',754974766,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2003-07-18 00:11:21',NULL,NULL,20030718001121); +INSERT INTO t2 SELECT USID, ServerID, State, SessionID, User, NASAddr, NASPort, NASPortType, ConnectSpeed, CarrierType, CallingStationID, CalledStationID, AssignedAddr, SessionTime, PacketsIn, OctetsIn, PacketsOut, OctetsOut, TerminateCause, UnauthTime, AccessRequestTime, AcctStartTime, AcctLastTime, LastModification from t1 LIMIT 1; +drop table t1,t2; diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index 882aec1006a..e8977ae9b62 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -99,10 +99,7 @@ CREATE TABLE t2 ( INSERT INTO t2 VALUES (3,2,11,12,5400,7800); INSERT INTO t2 VALUES (4,2,25,12,6500,11200); INSERT INTO t2 VALUES (5,1,37,6,10000,12000); - -select a.id, b.category as catid, b.state as stateid, b.county as -countyid from t1 a, t2 b where (a.token = -'a71250b7ed780f6ef3185bfffe027983') and (a.count = b.id); +select a.id, b.category as catid, b.state as stateid, b.county as countyid from t1 a, t2 b ignore index (primary) where (a.token ='a71250b7ed780f6ef3185bfffe027983') and (a.count = b.id); select a.id, b.category as catid, b.state as stateid, b.county as countyid from t1 a, t2 b where (a.token = 'a71250b7ed780f6ef3185bfffe027983') and (a.count = b.id) order by a.id; diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index df7e5b1524d..a63defff540 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -426,3 +426,15 @@ insert into t2 values(2),(3); insert into t3 values(2),(4); select * from t1 natural left join t2 natural left join t3; drop table t1,t2,t3; + +# +# Test of USING +# +create table t1 (f1 integer,f2 integer,f3 integer); +create table t2 (f2 integer,f4 integer); +create table t3 (f3 integer,f5 integer); +--error 1054 +select * from t1 + left outer join t2 using (f2) + left outer join t3 using (f3); +drop table t1,t2,t3; diff --git a/mysql-test/t/lock_tables_lost_commit-master.opt b/mysql-test/t/lock_tables_lost_commit-master.opt new file mode 100644 index 00000000000..d357a51cb27 --- /dev/null +++ b/mysql-test/t/lock_tables_lost_commit-master.opt @@ -0,0 +1 @@ +--binlog-ignore-db=test innodb
\ No newline at end of file diff --git a/mysql-test/t/lock_tables_lost_commit.test b/mysql-test/t/lock_tables_lost_commit.test new file mode 100644 index 00000000000..a12ee7369cb --- /dev/null +++ b/mysql-test/t/lock_tables_lost_commit.test @@ -0,0 +1,18 @@ +# This is a test for bug 578 + +connect (con1,localhost,root,,); +connect (con2,localhost,root,,); + +connection con1; +drop table if exists t1; +create table t1(a int) type=innodb; +lock tables t1 write; +insert into t1 values(10); +disconnect con1; + +connection con2; +# The bug was that, because of the LOCK TABLES, the handler "forgot" to commit, +# and the other commit when we write to the binlog was not done because of +# binlog-ignore-db +select * from t1; +drop table t1; diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index 01ba8986474..c3bffa4f2fb 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -250,3 +250,16 @@ EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2 AND file_code = '0000000115' LIMIT 1; DROP TABLE t2, t1; +# +# Test of ORDER BY DESC on key (Bug #515) +# + +create table t1 (x int, y int, index xy(x, y)); +create table t2 (x int, y int, index xy(x, y)); +create table t3 (x int, y int, index xy(x, y)) type=merge union=(t1,t2); +insert into t1 values(1, 2); +insert into t2 values(1, 3); +select * from t3 where x = 1 and y < 5 order by y; +# Bug is that followng query returns empty set while it must be same as above +select * from t3 where x = 1 and y < 5 order by y desc; +drop table t1,t2,t3; diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test index 6261260115c..b784bcf5076 100644 --- a/mysql-test/t/query_cache.test +++ b/mysql-test/t/query_cache.test @@ -11,8 +11,8 @@ flush query cache; # This crashed in some versions reset query cache; flush status; --disable_warnings +drop table if exists t0,t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16,t17,t18,t19,t20,t21,t22,t23,t24,t25,t26,t27,t28,t29,t30,t31,t32,t33,t34,t35,t36,t37,t38,t39,t40,t41,t42,t43,t44,t45,t46,t47,t48,t49,t50,t51,t52,t53,t54,t55,t56,t57,t58,t59,t60,t61,t62,t63,t64,t65,t66,t67,t68,t69,t70,t71,t72,t73,t74,t75,t76,t77,t78,t79,t80,t81,t82,t83,t84,t85,t86,t87,t88,t89,t90,t91,t92,t93,t94,t95,t96,t97,t98,t99,t100,t101,t102,t103,t104,t105,t106,t107,t108,t109,t110,t111,t112,t113,t114,t115,t116,t117,t118,t119,t120,t121,t122,t123,t124,t125,t126,t127,t128,t129,t130,t131,t132,t133,t134,t135,t136,t137,t138,t139,t140,t141,t142,t143,t144,t145,t146,t147,t148,t149,t150,t151,t152,t153,t154,t155,t156,t157,t158,t159,t160,t161,t162,t163,t164,t165,t166,t167,t168,t169,t170,t171,t172,t173,t174,t175,t176,t177,t178,t179,t180,t181,t182,t183,t184,t185,t186,t187,t188,t189,t190,t191,t192,t193,t194,t195,t196,t197,t198,t199,t200,t201,t202,t203,t204,t205,t206,t207,t208,t209,t210,t211,t212,t213,t214,t215,t216,t217,t218,t219,t220,t221,t222,t223,t224,t225,t226,t227,t228,t229,t230,t231,t232,t233,t234,t235,t236,t237,t238,t239,t240,t241,t242,t243,t244,t245,t246,t247,t248,t249,t250,t251,t252,t253,t254,t255,t256,t00,mysqltest.t1; drop database if exists mysqltest; -drop table if exists t1,t2,t3,t11,t21, mysqltest.t1; --enable_warnings # @@ -244,7 +244,7 @@ drop table t1,t2; # # noncachable ODBC work around (and prepare cache for drop database) # -create database if not exists mysqltest; +create database mysqltest; create table mysqltest.t1 (i int not null auto_increment, a int, primary key (i)); insert into mysqltest.t1 (a) values (1); select * from mysqltest.t1 where i is null; @@ -487,3 +487,531 @@ select * from t1; show status like "Qcache_queries_in_cache"; SET OPTION SQL_SELECT_LIMIT=DEFAULT; drop table t1; + +# +# more then 255 (257) merged tables test +# +flush status; +create table t0(a int); +create table t1(a int); +create table t2(a int); +create table t3(a int); +create table t4(a int); +create table t5(a int); +create table t6(a int); +create table t7(a int); +create table t8(a int); +create table t9(a int); +create table t10(a int); +create table t11(a int); +create table t12(a int); +create table t13(a int); +create table t14(a int); +create table t15(a int); +create table t16(a int); +create table t17(a int); +create table t18(a int); +create table t19(a int); +create table t20(a int); +create table t21(a int); +create table t22(a int); +create table t23(a int); +create table t24(a int); +create table t25(a int); +create table t26(a int); +create table t27(a int); +create table t28(a int); +create table t29(a int); +create table t30(a int); +create table t31(a int); +create table t32(a int); +create table t33(a int); +create table t34(a int); +create table t35(a int); +create table t36(a int); +create table t37(a int); +create table t38(a int); +create table t39(a int); +create table t40(a int); +create table t41(a int); +create table t42(a int); +create table t43(a int); +create table t44(a int); +create table t45(a int); +create table t46(a int); +create table t47(a int); +create table t48(a int); +create table t49(a int); +create table t50(a int); +create table t51(a int); +create table t52(a int); +create table t53(a int); +create table t54(a int); +create table t55(a int); +create table t56(a int); +create table t57(a int); +create table t58(a int); +create table t59(a int); +create table t60(a int); +create table t61(a int); +create table t62(a int); +create table t63(a int); +create table t64(a int); +create table t65(a int); +create table t66(a int); +create table t67(a int); +create table t68(a int); +create table t69(a int); +create table t70(a int); +create table t71(a int); +create table t72(a int); +create table t73(a int); +create table t74(a int); +create table t75(a int); +create table t76(a int); +create table t77(a int); +create table t78(a int); +create table t79(a int); +create table t80(a int); +create table t81(a int); +create table t82(a int); +create table t83(a int); +create table t84(a int); +create table t85(a int); +create table t86(a int); +create table t87(a int); +create table t88(a int); +create table t89(a int); +create table t90(a int); +create table t91(a int); +create table t92(a int); +create table t93(a int); +create table t94(a int); +create table t95(a int); +create table t96(a int); +create table t97(a int); +create table t98(a int); +create table t99(a int); +create table t100(a int); +create table t101(a int); +create table t102(a int); +create table t103(a int); +create table t104(a int); +create table t105(a int); +create table t106(a int); +create table t107(a int); +create table t108(a int); +create table t109(a int); +create table t110(a int); +create table t111(a int); +create table t112(a int); +create table t113(a int); +create table t114(a int); +create table t115(a int); +create table t116(a int); +create table t117(a int); +create table t118(a int); +create table t119(a int); +create table t120(a int); +create table t121(a int); +create table t122(a int); +create table t123(a int); +create table t124(a int); +create table t125(a int); +create table t126(a int); +create table t127(a int); +create table t128(a int); +create table t129(a int); +create table t130(a int); +create table t131(a int); +create table t132(a int); +create table t133(a int); +create table t134(a int); +create table t135(a int); +create table t136(a int); +create table t137(a int); +create table t138(a int); +create table t139(a int); +create table t140(a int); +create table t141(a int); +create table t142(a int); +create table t143(a int); +create table t144(a int); +create table t145(a int); +create table t146(a int); +create table t147(a int); +create table t148(a int); +create table t149(a int); +create table t150(a int); +create table t151(a int); +create table t152(a int); +create table t153(a int); +create table t154(a int); +create table t155(a int); +create table t156(a int); +create table t157(a int); +create table t158(a int); +create table t159(a int); +create table t160(a int); +create table t161(a int); +create table t162(a int); +create table t163(a int); +create table t164(a int); +create table t165(a int); +create table t166(a int); +create table t167(a int); +create table t168(a int); +create table t169(a int); +create table t170(a int); +create table t171(a int); +create table t172(a int); +create table t173(a int); +create table t174(a int); +create table t175(a int); +create table t176(a int); +create table t177(a int); +create table t178(a int); +create table t179(a int); +create table t180(a int); +create table t181(a int); +create table t182(a int); +create table t183(a int); +create table t184(a int); +create table t185(a int); +create table t186(a int); +create table t187(a int); +create table t188(a int); +create table t189(a int); +create table t190(a int); +create table t191(a int); +create table t192(a int); +create table t193(a int); +create table t194(a int); +create table t195(a int); +create table t196(a int); +create table t197(a int); +create table t198(a int); +create table t199(a int); +create table t200(a int); +create table t201(a int); +create table t202(a int); +create table t203(a int); +create table t204(a int); +create table t205(a int); +create table t206(a int); +create table t207(a int); +create table t208(a int); +create table t209(a int); +create table t210(a int); +create table t211(a int); +create table t212(a int); +create table t213(a int); +create table t214(a int); +create table t215(a int); +create table t216(a int); +create table t217(a int); +create table t218(a int); +create table t219(a int); +create table t220(a int); +create table t221(a int); +create table t222(a int); +create table t223(a int); +create table t224(a int); +create table t225(a int); +create table t226(a int); +create table t227(a int); +create table t228(a int); +create table t229(a int); +create table t230(a int); +create table t231(a int); +create table t232(a int); +create table t233(a int); +create table t234(a int); +create table t235(a int); +create table t236(a int); +create table t237(a int); +create table t238(a int); +create table t239(a int); +create table t240(a int); +create table t241(a int); +create table t242(a int); +create table t243(a int); +create table t244(a int); +create table t245(a int); +create table t246(a int); +create table t247(a int); +create table t248(a int); +create table t249(a int); +create table t250(a int); +create table t251(a int); +create table t252(a int); +create table t253(a int); +create table t254(a int); +create table t255(a int); +create table t256(a int); +create table t00 (a int) type=MERGE UNION=(t0,t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16,t17,t18,t19,t20,t21,t22,t23,t24,t25,t26,t27,t28,t29,t30,t31,t32,t33,t34,t35,t36,t37,t38,t39,t40,t41,t42,t43,t44,t45,t46,t47,t48,t49,t50,t51,t52,t53,t54,t55,t56,t57,t58,t59,t60,t61,t62,t63,t64,t65,t66,t67,t68,t69,t70,t71,t72,t73,t74,t75,t76,t77,t78,t79,t80,t81,t82,t83,t84,t85,t86,t87,t88,t89,t90,t91,t92,t93,t94,t95,t96,t97,t98,t99,t100,t101,t102,t103,t104,t105,t106,t107,t108,t109,t110,t111,t112,t113,t114,t115,t116,t117,t118,t119,t120,t121,t122,t123,t124,t125,t126,t127,t128,t129,t130,t131,t132,t133,t134,t135,t136,t137,t138,t139,t140,t141,t142,t143,t144,t145,t146,t147,t148,t149,t150,t151,t152,t153,t154,t155,t156,t157,t158,t159,t160,t161,t162,t163,t164,t165,t166,t167,t168,t169,t170,t171,t172,t173,t174,t175,t176,t177,t178,t179,t180,t181,t182,t183,t184,t185,t186,t187,t188,t189,t190,t191,t192,t193,t194,t195,t196,t197,t198,t199,t200,t201,t202,t203,t204,t205,t206,t207,t208,t209,t210,t211,t212,t213,t214,t215,t216,t217,t218,t219,t220,t221,t222,t223,t224,t225,t226,t227,t228,t229,t230,t231,t232,t233,t234,t235,t236,t237,t238,t239,t240,t241,t242,t243,t244,t245,t246,t247,t248,t249,t250,t251,t252,t253,t254,t255,t256) INSERT_METHOD=FIRST; +insert into t0 values (1),(2); +insert into t1 values (1),(2); +insert into t2 values (1),(2); +insert into t3 values (1),(2); +insert into t4 values (1),(2); +insert into t5 values (1),(2); +insert into t6 values (1),(2); +insert into t7 values (1),(2); +insert into t8 values (1),(2); +insert into t9 values (1),(2); +insert into t10 values (1),(2); +insert into t11 values (1),(2); +insert into t12 values (1),(2); +insert into t13 values (1),(2); +insert into t14 values (1),(2); +insert into t15 values (1),(2); +insert into t16 values (1),(2); +insert into t17 values (1),(2); +insert into t18 values (1),(2); +insert into t19 values (1),(2); +insert into t20 values (1),(2); +insert into t21 values (1),(2); +insert into t22 values (1),(2); +insert into t23 values (1),(2); +insert into t24 values (1),(2); +insert into t25 values (1),(2); +insert into t26 values (1),(2); +insert into t27 values (1),(2); +insert into t28 values (1),(2); +insert into t29 values (1),(2); +insert into t30 values (1),(2); +insert into t31 values (1),(2); +insert into t32 values (1),(2); +insert into t33 values (1),(2); +insert into t34 values (1),(2); +insert into t35 values (1),(2); +insert into t36 values (1),(2); +insert into t37 values (1),(2); +insert into t38 values (1),(2); +insert into t39 values (1),(2); +insert into t40 values (1),(2); +insert into t41 values (1),(2); +insert into t42 values (1),(2); +insert into t43 values (1),(2); +insert into t44 values (1),(2); +insert into t45 values (1),(2); +insert into t46 values (1),(2); +insert into t47 values (1),(2); +insert into t48 values (1),(2); +insert into t49 values (1),(2); +insert into t50 values (1),(2); +insert into t51 values (1),(2); +insert into t52 values (1),(2); +insert into t53 values (1),(2); +insert into t54 values (1),(2); +insert into t55 values (1),(2); +insert into t56 values (1),(2); +insert into t57 values (1),(2); +insert into t58 values (1),(2); +insert into t59 values (1),(2); +insert into t60 values (1),(2); +insert into t61 values (1),(2); +insert into t62 values (1),(2); +insert into t63 values (1),(2); +insert into t64 values (1),(2); +insert into t65 values (1),(2); +insert into t66 values (1),(2); +insert into t67 values (1),(2); +insert into t68 values (1),(2); +insert into t69 values (1),(2); +insert into t70 values (1),(2); +insert into t71 values (1),(2); +insert into t72 values (1),(2); +insert into t73 values (1),(2); +insert into t74 values (1),(2); +insert into t75 values (1),(2); +insert into t76 values (1),(2); +insert into t77 values (1),(2); +insert into t78 values (1),(2); +insert into t79 values (1),(2); +insert into t80 values (1),(2); +insert into t81 values (1),(2); +insert into t82 values (1),(2); +insert into t83 values (1),(2); +insert into t84 values (1),(2); +insert into t85 values (1),(2); +insert into t86 values (1),(2); +insert into t87 values (1),(2); +insert into t88 values (1),(2); +insert into t89 values (1),(2); +insert into t90 values (1),(2); +insert into t91 values (1),(2); +insert into t92 values (1),(2); +insert into t93 values (1),(2); +insert into t94 values (1),(2); +insert into t95 values (1),(2); +insert into t96 values (1),(2); +insert into t97 values (1),(2); +insert into t98 values (1),(2); +insert into t99 values (1),(2); +insert into t100 values (1),(2); +insert into t101 values (1),(2); +insert into t102 values (1),(2); +insert into t103 values (1),(2); +insert into t104 values (1),(2); +insert into t105 values (1),(2); +insert into t106 values (1),(2); +insert into t107 values (1),(2); +insert into t108 values (1),(2); +insert into t109 values (1),(2); +insert into t110 values (1),(2); +insert into t111 values (1),(2); +insert into t112 values (1),(2); +insert into t113 values (1),(2); +insert into t114 values (1),(2); +insert into t115 values (1),(2); +insert into t116 values (1),(2); +insert into t117 values (1),(2); +insert into t118 values (1),(2); +insert into t119 values (1),(2); +insert into t120 values (1),(2); +insert into t121 values (1),(2); +insert into t122 values (1),(2); +insert into t123 values (1),(2); +insert into t124 values (1),(2); +insert into t125 values (1),(2); +insert into t126 values (1),(2); +insert into t127 values (1),(2); +insert into t128 values (1),(2); +insert into t129 values (1),(2); +insert into t130 values (1),(2); +insert into t131 values (1),(2); +insert into t132 values (1),(2); +insert into t133 values (1),(2); +insert into t134 values (1),(2); +insert into t135 values (1),(2); +insert into t136 values (1),(2); +insert into t137 values (1),(2); +insert into t138 values (1),(2); +insert into t139 values (1),(2); +insert into t140 values (1),(2); +insert into t141 values (1),(2); +insert into t142 values (1),(2); +insert into t143 values (1),(2); +insert into t144 values (1),(2); +insert into t145 values (1),(2); +insert into t146 values (1),(2); +insert into t147 values (1),(2); +insert into t148 values (1),(2); +insert into t149 values (1),(2); +insert into t150 values (1),(2); +insert into t151 values (1),(2); +insert into t152 values (1),(2); +insert into t153 values (1),(2); +insert into t154 values (1),(2); +insert into t155 values (1),(2); +insert into t156 values (1),(2); +insert into t157 values (1),(2); +insert into t158 values (1),(2); +insert into t159 values (1),(2); +insert into t160 values (1),(2); +insert into t161 values (1),(2); +insert into t162 values (1),(2); +insert into t163 values (1),(2); +insert into t164 values (1),(2); +insert into t165 values (1),(2); +insert into t166 values (1),(2); +insert into t167 values (1),(2); +insert into t168 values (1),(2); +insert into t169 values (1),(2); +insert into t170 values (1),(2); +insert into t171 values (1),(2); +insert into t172 values (1),(2); +insert into t173 values (1),(2); +insert into t174 values (1),(2); +insert into t175 values (1),(2); +insert into t176 values (1),(2); +insert into t177 values (1),(2); +insert into t178 values (1),(2); +insert into t179 values (1),(2); +insert into t180 values (1),(2); +insert into t181 values (1),(2); +insert into t182 values (1),(2); +insert into t183 values (1),(2); +insert into t184 values (1),(2); +insert into t185 values (1),(2); +insert into t186 values (1),(2); +insert into t187 values (1),(2); +insert into t188 values (1),(2); +insert into t189 values (1),(2); +insert into t190 values (1),(2); +insert into t191 values (1),(2); +insert into t192 values (1),(2); +insert into t193 values (1),(2); +insert into t194 values (1),(2); +insert into t195 values (1),(2); +insert into t196 values (1),(2); +insert into t197 values (1),(2); +insert into t198 values (1),(2); +insert into t199 values (1),(2); +insert into t200 values (1),(2); +insert into t201 values (1),(2); +insert into t202 values (1),(2); +insert into t203 values (1),(2); +insert into t204 values (1),(2); +insert into t205 values (1),(2); +insert into t206 values (1),(2); +insert into t207 values (1),(2); +insert into t208 values (1),(2); +insert into t209 values (1),(2); +insert into t210 values (1),(2); +insert into t211 values (1),(2); +insert into t212 values (1),(2); +insert into t213 values (1),(2); +insert into t214 values (1),(2); +insert into t215 values (1),(2); +insert into t216 values (1),(2); +insert into t217 values (1),(2); +insert into t218 values (1),(2); +insert into t219 values (1),(2); +insert into t220 values (1),(2); +insert into t221 values (1),(2); +insert into t222 values (1),(2); +insert into t223 values (1),(2); +insert into t224 values (1),(2); +insert into t225 values (1),(2); +insert into t226 values (1),(2); +insert into t227 values (1),(2); +insert into t228 values (1),(2); +insert into t229 values (1),(2); +insert into t230 values (1),(2); +insert into t231 values (1),(2); +insert into t232 values (1),(2); +insert into t233 values (1),(2); +insert into t234 values (1),(2); +insert into t235 values (1),(2); +insert into t236 values (1),(2); +insert into t237 values (1),(2); +insert into t238 values (1),(2); +insert into t239 values (1),(2); +insert into t240 values (1),(2); +insert into t241 values (1),(2); +insert into t242 values (1),(2); +insert into t243 values (1),(2); +insert into t244 values (1),(2); +insert into t245 values (1),(2); +insert into t246 values (1),(2); +insert into t247 values (1),(2); +insert into t248 values (1),(2); +insert into t249 values (1),(2); +insert into t250 values (1),(2); +insert into t251 values (1),(2); +insert into t252 values (1),(2); +insert into t253 values (1),(2); +insert into t254 values (1),(2); +insert into t255 values (1),(2); +insert into t256 values (1),(2); +enable_result_log; +select count(*) from t00; +select count(*) from t00; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_hits"; +delete from t256; +show status like "Qcache_queries_in_cache"; +drop table t0,t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16,t17,t18,t19,t20,t21,t22,t23,t24,t25,t26,t27,t28,t29,t30,t31,t32,t33,t34,t35,t36,t37,t38,t39,t40,t41,t42,t43,t44,t45,t46,t47,t48,t49,t50,t51,t52,t53,t54,t55,t56,t57,t58,t59,t60,t61,t62,t63,t64,t65,t66,t67,t68,t69,t70,t71,t72,t73,t74,t75,t76,t77,t78,t79,t80,t81,t82,t83,t84,t85,t86,t87,t88,t89,t90,t91,t92,t93,t94,t95,t96,t97,t98,t99,t100,t101,t102,t103,t104,t105,t106,t107,t108,t109,t110,t111,t112,t113,t114,t115,t116,t117,t118,t119,t120,t121,t122,t123,t124,t125,t126,t127,t128,t129,t130,t131,t132,t133,t134,t135,t136,t137,t138,t139,t140,t141,t142,t143,t144,t145,t146,t147,t148,t149,t150,t151,t152,t153,t154,t155,t156,t157,t158,t159,t160,t161,t162,t163,t164,t165,t166,t167,t168,t169,t170,t171,t172,t173,t174,t175,t176,t177,t178,t179,t180,t181,t182,t183,t184,t185,t186,t187,t188,t189,t190,t191,t192,t193,t194,t195,t196,t197,t198,t199,t200,t201,t202,t203,t204,t205,t206,t207,t208,t209,t210,t211,t212,t213,t214,t215,t216,t217,t218,t219,t220,t221,t222,t223,t224,t225,t226,t227,t228,t229,t230,t231,t232,t233,t234,t235,t236,t237,t238,t239,t240,t241,t242,t243,t244,t245,t246,t247,t248,t249,t250,t251,t252,t253,t254,t255,t256,t00; diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index 31b3ced6cc6..8d341837acd 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -1,5 +1,5 @@ # -# Problem med range optimizer +# Problem with range optimizer # --disable_warnings @@ -13,7 +13,24 @@ CREATE TABLE t1 ( PRIMARY KEY (event_date,type,event_id) ); -INSERT INTO t1 VALUES ('1999-07-10',100100,24),('1999-07-11',100100,25),('1999-07-13',100600,0),('1999-07-13',100600,4),('1999-07-13',100600,26),('1999-07-14',100600,10),('1999-07-15',100600,16),('1999-07-15',100800,45),('1999-07-15',101000,47),('1999-07-16',100800,46),('1999-07-20',100600,5),('1999-07-20',100600,27),('1999-07-21',100600,11),('1999-07-22',100600,17),('1999-07-23',100100,39),('1999-07-24',100100,39),('1999-07-24',100500,40),('1999-07-25',100100,39),('1999-07-27',100600,1),('1999-07-27',100600,6),('1999-07-27',100600,28),('1999-07-28',100600,12),('1999-07-29',100500,41),('1999-07-29',100600,18),('1999-07-30',100500,41),('1999-07-31',100500,41),('1999-08-01',100700,34),('1999-08-03',100600,7),('1999-08-03',100600,29),('1999-08-04',100600,13),('1999-08-05',100500,42),('1999-08-05',100600,19),('1999-08-06',100500,42),('1999-08-07',100500,42),('1999-08-08',100500,42),('1999-08-10',100600,2),('1999-08-10',100600,9),('1999-08-10',100600,30),('1999-08-11',100600,14),('1999-08-12',100600,20),('1999-08-17',100500,8),('1999-08-17',100600,31),('1999-08-18',100600,15),('1999-08-19',100600,22),('1999-08-24',100600,3),('1999-08-24',100600,32),('1999-08-27',100500,43),('1999-08-31',100600,33),('1999-09-17',100100,37),('1999-09-18',100100,37),('1999-09-19',100100,37),('2000-12-18',100700,38); +INSERT INTO t1 VALUES ('1999-07-10',100100,24), ('1999-07-11',100100,25), +('1999-07-13',100600,0), ('1999-07-13',100600,4), ('1999-07-13',100600,26), +('1999-07-14',100600,10), ('1999-07-15',100600,16), ('1999-07-15',100800,45), +('1999-07-15',101000,47), ('1999-07-16',100800,46), ('1999-07-20',100600,5), +('1999-07-20',100600,27), ('1999-07-21',100600,11), ('1999-07-22',100600,17), +('1999-07-23',100100,39), ('1999-07-24',100100,39), ('1999-07-24',100500,40), +('1999-07-25',100100,39), ('1999-07-27',100600,1), ('1999-07-27',100600,6), +('1999-07-27',100600,28), ('1999-07-28',100600,12), ('1999-07-29',100500,41), +('1999-07-29',100600,18), ('1999-07-30',100500,41), ('1999-07-31',100500,41), +('1999-08-01',100700,34), ('1999-08-03',100600,7), ('1999-08-03',100600,29), +('1999-08-04',100600,13), ('1999-08-05',100500,42), ('1999-08-05',100600,19), +('1999-08-06',100500,42), ('1999-08-07',100500,42), ('1999-08-08',100500,42), +('1999-08-10',100600,2), ('1999-08-10',100600,9), ('1999-08-10',100600,30), +('1999-08-11',100600,14), ('1999-08-12',100600,20), ('1999-08-17',100500,8), +('1999-08-17',100600,31), ('1999-08-18',100600,15), ('1999-08-19',100600,22), +('1999-08-24',100600,3), ('1999-08-24',100600,32), ('1999-08-27',100500,43), +('1999-08-31',100600,33), ('1999-09-17',100100,37), ('1999-09-18',100100,37), +('1999-09-19',100100,37), ('2000-12-18',100700,38); select event_date,type,event_id from t1 WHERE event_date >= "1999-07-01" AND event_date < "1999-07-15" AND (type=100600 OR type=100100) ORDER BY event_date; explain select event_date,type,event_id from t1 WHERE type = 100601 and event_date >= "1999-07-01" AND event_date < "1999-07-15" AND (type=100600 OR type=100100) ORDER BY event_date; @@ -28,35 +45,21 @@ CREATE TABLE t1 ( ISS_DATE date DEFAULT '0000-00-00' NOT NULL, PRIMARY KEY (PAPER_ID,YEAR,ISSUE) ); -INSERT INTO t1 VALUES (3,1999,34,0,'1999-07-12'); -INSERT INTO t1 VALUES (1,1999,111,0,'1999-03-23'); -INSERT INTO t1 VALUES (1,1999,222,0,'1999-03-23'); -INSERT INTO t1 VALUES (3,1999,33,0,'1999-07-12'); -INSERT INTO t1 VALUES (3,1999,32,0,'1999-07-12'); -INSERT INTO t1 VALUES (3,1999,31,0,'1999-07-12'); -INSERT INTO t1 VALUES (3,1999,30,0,'1999-07-12'); -INSERT INTO t1 VALUES (3,1999,29,0,'1999-07-12'); -INSERT INTO t1 VALUES (3,1999,28,0,'1999-07-12'); -INSERT INTO t1 VALUES (1,1999,40,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,41,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,42,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,46,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,47,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,48,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,49,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,50,0,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,51,0,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,200,0,'1999-06-28'); -INSERT INTO t1 VALUES (1,1999,52,0,'1999-06-28'); -INSERT INTO t1 VALUES (1,1999,53,0,'1999-06-28'); -INSERT INTO t1 VALUES (1,1999,54,0,'1999-06-28'); -INSERT INTO t1 VALUES (1,1999,55,0,'1999-06-28'); -INSERT INTO t1 VALUES (1,1999,56,0,'1999-07-01'); -INSERT INTO t1 VALUES (1,1999,57,0,'1999-07-01'); -INSERT INTO t1 VALUES (1,1999,58,0,'1999-07-01'); -INSERT INTO t1 VALUES (1,1999,59,0,'1999-07-01'); -INSERT INTO t1 VALUES (1,1999,60,0,'1999-07-01'); -INSERT INTO t1 VALUES (3,1999,35,0,'1999-07-12'); +INSERT INTO t1 VALUES (3,1999,34,0,'1999-07-12'), (1,1999,111,0,'1999-03-23'), + (1,1999,222,0,'1999-03-23'), (3,1999,33,0,'1999-07-12'), + (3,1999,32,0,'1999-07-12'), (3,1999,31,0,'1999-07-12'), + (3,1999,30,0,'1999-07-12'), (3,1999,29,0,'1999-07-12'), + (3,1999,28,0,'1999-07-12'), (1,1999,40,1,'1999-05-01'), + (1,1999,41,1,'1999-05-01'), (1,1999,42,1,'1999-05-01'), + (1,1999,46,1,'1999-05-01'), (1,1999,47,1,'1999-05-01'), + (1,1999,48,1,'1999-05-01'), (1,1999,49,1,'1999-05-01'), + (1,1999,50,0,'1999-05-01'), (1,1999,51,0,'1999-05-01'), + (1,1999,200,0,'1999-06-28'), (1,1999,52,0,'1999-06-28'), + (1,1999,53,0,'1999-06-28'), (1,1999,54,0,'1999-06-28'), + (1,1999,55,0,'1999-06-28'), (1,1999,56,0,'1999-07-01'), + (1,1999,57,0,'1999-07-01'), (1,1999,58,0,'1999-07-01'), + (1,1999,59,0,'1999-07-01'), (1,1999,60,0,'1999-07-01'), + (3,1999,35,0,'1999-07-12'); select YEAR,ISSUE from t1 where PAPER_ID=3 and (YEAR>1999 or (YEAR=1999 and ISSUE>28)) order by YEAR,ISSUE; check table t1; repair table t1; @@ -70,7 +73,12 @@ CREATE TABLE t1 ( KEY parent_id (parent_id), KEY level (level) ); -INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1),(179,5,2); +INSERT INTO t1 VALUES (1,0,0), (3,1,1), (4,1,1), (8,2,2), (9,2,2), (17,3,2), +(22,4,2), (24,4,2), (28,5,2), (29,5,2), (30,5,2), (31,6,2), (32,6,2), (33,6,2), +(203,7,2), (202,7,2), (20,3,2), (157,0,0), (193,5,2), (40,7,2), (2,1,1), +(15,2,2), (6,1,1), (34,6,2), (35,6,2), (16,3,2), (7,1,1), (36,7,2), (18,3,2), +(26,5,2), (27,5,2), (183,4,2), (38,7,2), (25,5,2), (37,7,2), (21,4,2), +(19,3,2), (5,1,1), (179,5,2); SELECT * FROM t1 WHERE level = 1 AND parent_id = 1; # The following select returned 0 rows in 3.23.8 SELECT * FROM t1 WHERE level = 1 AND parent_id = 1 order by id; @@ -166,3 +174,15 @@ select count(*) from t1 where art = 'j' or art = 'J'; select count(*) from t1 where art = 'j'; select count(*) from t1 where art = 'J'; drop table t1; + +create table t1 ( id1 int not null, id2 int not null, idnull int null, c char(20), primary key (id1,id2)); +insert into t1 values (0,1,NULL,"aaa"), (1,1,NULL,"aaa"), (2,1,NULL,"aaa"), + (3,1,NULL,"aaa"), (4,1,NULL,"aaa"), (5,1,NULL,"aaa"), + (6,1,NULL,"aaa"), (7,1,NULL,"aaa"), (8,1,NULL,"aaa"), + (9,1,NULL,"aaa"), (10,1,NULL,"aaa"), (11,1,NULL,"aaa"), + (12,1,NULL,"aaa"), (13,1,NULL,"aaa"), (14,1,NULL,"aaa"), + (15,1,NULL,"aaa"), (16,1,NULL,"aaa"), (17,1,NULL,"aaa"), + (18,1,NULL,"aaa"), (19,1,NULL,"aaa"), (20,1,NULL,"aaa"); +select a.id1, b.idnull from t1 as a, t1 as b where a.id2=1 and a.id1=1 and b.id1=a.idnull order by b.id2 desc limit 1; +drop table t1; + diff --git a/mysql-test/t/rpl000001.test b/mysql-test/t/rpl000001.test index d562a47ef90..f464f1e2751 100644 --- a/mysql-test/t/rpl000001.test +++ b/mysql-test/t/rpl000001.test @@ -90,8 +90,8 @@ connection master; --error 1053; reap; connection slave; -sync_with_master; -#give the slave a chance to exit +# The SQL slave thread should now have stopped because the query was killed on +# the master (so it has a non-zero error code in the binlog). wait_for_slave_to_stop; # The following test can't be done because the result of Pos will differ diff --git a/mysql-test/t/rpl000018.test b/mysql-test/t/rpl000018.test index b8c09ed431d..884ec9727d2 100644 --- a/mysql-test/t/rpl000018.test +++ b/mysql-test/t/rpl000018.test @@ -6,6 +6,8 @@ require_manager; connect (master,localhost,root,,test,0,master.sock); connect (slave,localhost,root,,test,0,slave.sock); +connection master; +reset master; server_stop master; server_start master; connection slave; diff --git a/mysql-test/t/rpl_do_grant.test b/mysql-test/t/rpl_do_grant.test new file mode 100644 index 00000000000..89ff1afb5c9 --- /dev/null +++ b/mysql-test/t/rpl_do_grant.test @@ -0,0 +1,46 @@ +# Test that GRANT and SET PASSWORD are replicated to the slave + +source include/master-slave.inc; + +# do not be influenced by other tests. +connection master; +delete from mysql.user where user='rpl_do_grant'; +delete from mysql.db where user='rpl_do_grant'; +flush privileges; +save_master_pos; +connection slave; +sync_with_master; +# if these DELETE did nothing on the master, we need to do them manually on the +# slave. +delete from mysql.user where user='rpl_ignore_grant'; +delete from mysql.db where user='rpl_ignore_grant'; +flush privileges; + +# test replication of GRANT +connection master; +grant select on *.* to rpl_do_grant@localhost; +grant drop on test.* to rpl_do_grant@localhost; +save_master_pos; +connection slave; +sync_with_master; +show grants for rpl_do_grant@localhost; + +# test replication of SET PASSWORD +connection master; +set password for rpl_do_grant@localhost=password("does it work?"); +save_master_pos; +connection slave; +sync_with_master; +select password<>'' from mysql.user where user='rpl_do_grant'; + +# clear what we have done, to not influence other tests. +connection master; +delete from mysql.user where user='rpl_do_grant'; +delete from mysql.db where user='rpl_do_grant'; +flush privileges; +save_master_pos; +connection slave; +sync_with_master; +# no need to delete manually, as the DELETEs must have done some real job on +# master (updated binlog) +flush privileges; diff --git a/mysql-test/t/rpl_error_ignored_table-slave.opt b/mysql-test/t/rpl_error_ignored_table-slave.opt new file mode 100644 index 00000000000..0d3485f9e25 --- /dev/null +++ b/mysql-test/t/rpl_error_ignored_table-slave.opt @@ -0,0 +1 @@ +--replicate-ignore-table=test.t1 diff --git a/mysql-test/t/rpl_error_ignored_table.test b/mysql-test/t/rpl_error_ignored_table.test new file mode 100644 index 00000000000..686472433eb --- /dev/null +++ b/mysql-test/t/rpl_error_ignored_table.test @@ -0,0 +1,25 @@ +# Test for +# Bug #797: If a query is ignored on slave (replicate-ignore-table) the slave +# still checks that it has the same error as on the master. + +source include/master-slave.inc; +connection master; +create table t1 (a int primary key); +# generate an error that goes to the binlog +--error 1062; +insert into t1 values (1),(1); +save_master_pos; +connection slave; +# as the t1 table is ignored on the slave, the slave should be able to sync +sync_with_master; +# The port number is different when doing the release build with +# Do-compile, hence we have to replace the port number here accordingly +--replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT +show slave status; +# check that the table has been ignored, because otherwise the test is nonsense +show tables like 't1'; +connection master; +drop table t1; +save_master_pos; +connection slave; +sync_with_master; diff --git a/mysql-test/t/rpl_flush_log_loop.test b/mysql-test/t/rpl_flush_log_loop.test index 62bcf2c8b33..00fab13bac7 100644 --- a/mysql-test/t/rpl_flush_log_loop.test +++ b/mysql-test/t/rpl_flush_log_loop.test @@ -14,6 +14,7 @@ stop slave; eval change master to master_host='127.0.0.1',master_user='root', master_password='',master_port=$SLAVE_MYPORT; start slave; +sleep 5; flush logs; sleep 5; --replace_result $SLAVE_MYPORT SLAVE_PORT diff --git a/mysql-test/t/rpl_ignore_grant-slave.opt b/mysql-test/t/rpl_ignore_grant-slave.opt new file mode 100644 index 00000000000..e931bfbd37e --- /dev/null +++ b/mysql-test/t/rpl_ignore_grant-slave.opt @@ -0,0 +1 @@ +--replicate-wild-ignore-table=mysql.% diff --git a/mysql-test/t/rpl_ignore_grant.test b/mysql-test/t/rpl_ignore_grant.test new file mode 100644 index 00000000000..2fd7f186b3e --- /dev/null +++ b/mysql-test/t/rpl_ignore_grant.test @@ -0,0 +1,57 @@ +# Test that GRANT is not replicated to the slave +# when --replicate-wild-ignore-table=mysql.% +# In BUG#980, this test would _randomly_ fail. + +source include/master-slave.inc; + +# do not be influenced by other tests. +connection master; +delete from mysql.user where user='rpl_ignore_grant'; +delete from mysql.db where user='rpl_ignore_grant'; +flush privileges; +save_master_pos; +connection slave; +sync_with_master; +# as these DELETE were not replicated, we need to do them manually on the +# slave. +delete from mysql.user where user='rpl_ignore_grant'; +delete from mysql.db where user='rpl_ignore_grant'; +flush privileges; + +# test non-replication of GRANT +connection master; +grant select on *.* to rpl_ignore_grant@localhost; +grant drop on test.* to rpl_ignore_grant@localhost; +show grants for rpl_ignore_grant@localhost; +save_master_pos; +connection slave; +sync_with_master; +--error 1141 #("no such grant for user") +show grants for rpl_ignore_grant@localhost; +# check it another way +select count(*) from mysql.user where user='rpl_ignore_grant'; +select count(*) from mysql.db where user='rpl_ignore_grant'; + +# test non-replication of SET PASSWORD +# first force creation of the user on slave (because as the user does not exist +# on slave, the SET PASSWORD may be replicated but silently do nothing; this is +# not what we want; we want it to be not-replicated). +grant select on *.* to rpl_ignore_grant@localhost; +connection master; +set password for rpl_ignore_grant@localhost=password("does it work?"); +save_master_pos; +connection slave; +sync_with_master; +select password<>'' from mysql.user where user='rpl_ignore_grant'; + +# clear what we have done, to not influence other tests. +connection master; +delete from mysql.user where user='rpl_ignore_grant'; +delete from mysql.db where user='rpl_ignore_grant'; +flush privileges; +save_master_pos; +connection slave; +sync_with_master; +delete from mysql.user where user='rpl_ignore_grant'; +delete from mysql.db where user='rpl_ignore_grant'; +flush privileges; diff --git a/mysql-test/t/rpl_insert_id.test b/mysql-test/t/rpl_insert_id.test index 3e84f86c092..b2b92dec7aa 100644 --- a/mysql-test/t/rpl_insert_id.test +++ b/mysql-test/t/rpl_insert_id.test @@ -4,6 +4,7 @@ # We also check how the foreign_key_check variable is replicated source include/master-slave.inc; +source include/have_innodb.inc connection master; create table t1(a int auto_increment, key(a)); create table t2(b int auto_increment, c int, key(b)); @@ -21,8 +22,10 @@ connection master; #are replicated the same way drop table t1; drop table t2; -create table t1(a int auto_increment, key(a)); -create table t2(b int auto_increment, c int, key(b)); +--disable_warnings +create table t1(a int auto_increment, key(a)) type=innodb; +create table t2(b int auto_increment, c int, key(b), foreign key(b) references t1(a)) type=innodb; +--enable_warnings SET FOREIGN_KEY_CHECKS=0; insert into t1 values (10); insert into t1 values (null),(null),(null); diff --git a/mysql-test/t/rpl_loaddata.test b/mysql-test/t/rpl_loaddata.test index b540c3907e9..061066c7a31 100644 --- a/mysql-test/t/rpl_loaddata.test +++ b/mysql-test/t/rpl_loaddata.test @@ -2,10 +2,14 @@ # Honours autoincrement values # i.e. if the master and slave have the same sequence # -# check replication of load data for temporary tables with additional parameters +# check replication of load data for temporary tables with additional +# parameters # # check if duplicate entries trigger an error (they should unless IGNORE or # REPLACE was used on the master) (bug 571). +# +# check if START SLAVE, RESET SLAVE, CHANGE MASTER reset Last_slave_error and +# Last_slave_errno in SHOW SLAVE STATUS (1st and 3rd commands did not: bug 986) source include/master-slave.inc; @@ -37,21 +41,60 @@ connection slave; sync_with_master; insert into t1 values(1,10); +connection master; +load data infile '../../std_data/rpl_loaddata.dat' into table t1; + +save_master_pos; +connection slave; +# The SQL slave thread should be stopped now. +wait_for_slave_to_stop; + +# Skip the bad event and see if error is cleared in SHOW SLAVE STATUS by START +# SLAVE, even though we are not executing any event (as sql_slave_skip_counter +# takes us directly to the end of the relay log). -# NOTE UNTIL MERGE 4.0 INTO 4.1 -# Below we generate an error, but this error shows up in SHOW SLAVE STATUS -# in the next test. In 4.0 this is fixed (RESET SLAVE resets the error), but it -# has not been merged into 4.1 yet. So for the moment, I comment all lines -# below, to not generate the error, so that the test suite passes. -# When you do the 4.0 -> 4.1 merge, please remove this note and re-enable the -# error generation, by deleting the '#' characters below, and update the result. -# The changeset to merge in 4.1 is -# ChangeSet@1.1455.34.1, 2003-06-10 23:29:49+02:00, guilhem@mysql.com +set global sql_slave_skip_counter=1; +start slave; +sync_with_master; +--replace_result $MASTER_MYPORT MASTER_PORT +show slave status; -#connection master; -#load data infile '../../std_data/rpl_loaddata.dat' into table t1; +# Trigger error again to test CHANGE MASTER -#save_master_pos; -#connection slave; +connection master; +set sql_log_bin=0; +delete from t1; +set sql_log_bin=1; +load data infile '../../std_data/rpl_loaddata.dat' into table t1; +save_master_pos; +connection slave; # The SQL slave thread should be stopped now. -#wait_for_slave_to_stop; +wait_for_slave_to_stop; + +# CHANGE MASTER and see if error is cleared in SHOW SLAVE STATUS. +stop slave; +change master to master_user='test'; +change master to master_user='root'; +--replace_result $MASTER_MYPORT MASTER_PORT +show slave status; + +# Trigger error again to test RESET SLAVE + +set global sql_slave_skip_counter=1; +start slave; +sync_with_master; +connection master; +set sql_log_bin=0; +delete from t1; +set sql_log_bin=1; +load data infile '../../std_data/rpl_loaddata.dat' into table t1; +save_master_pos; +connection slave; +# The SQL slave thread should be stopped now. +wait_for_slave_to_stop; + +# RESET SLAVE and see if error is cleared in SHOW SLAVE STATUS. +stop slave; +reset slave; +--replace_result $MASTER_MYPORT MASTER_PORT +show slave status; diff --git a/mysql-test/t/rpl_master_pos_wait.test b/mysql-test/t/rpl_master_pos_wait.test index a6aae222a89..24479636c91 100644 --- a/mysql-test/t/rpl_master_pos_wait.test +++ b/mysql-test/t/rpl_master_pos_wait.test @@ -5,5 +5,11 @@ save_master_pos; connection slave; sync_with_master; # Ask for a master log that has certainly not been reached yet -# timeout= 10 seconds -select master_pos_wait('master-bin.999999',0,10); +# timeout= 2 seconds +select master_pos_wait('master-bin.999999',0,2); +# Testcase for bug 651 (master_pos_wait() hangs if slave idle and STOP SLAVE). +send select master_pos_wait('master-bin.999999',0); +connection slave1; +stop slave sql_thread; +connection slave; +reap; diff --git a/mysql-test/t/rpl_max_relay_size.test b/mysql-test/t/rpl_max_relay_size.test new file mode 100644 index 00000000000..a2167b1ef36 --- /dev/null +++ b/mysql-test/t/rpl_max_relay_size.test @@ -0,0 +1,87 @@ +# Test of options max_binlog_size and max_relay_log_size and +# how they act (if max_relay_log_size == 0, use max_binlog_size +# for relay logs too). +# Test of manual relay log rotation with FLUSH LOGS. + +source include/master-slave.inc; +connection slave; +stop slave; +connection master; +# Generate a big enough master's binlog to cause relay log rotations +create table t1 (a int); +let $1=800; +disable_query_log; +begin; +while ($1) +{ +# eval means expand $ expressions + eval insert into t1 values( $1 ); + dec $1; +} +enable_query_log; +drop table t1; +save_master_pos; +connection slave; +reset slave; +set global max_binlog_size=8192; +set global max_relay_log_size=8192-1; # mapped to 4096 +select @@global.max_relay_log_size; +start slave; +sync_with_master; +--replace_result $MASTER_MYPORT MASTER_PORT 3306 MASTER_PORT 3334 MASTER_PORT +show slave status; +stop slave; +reset slave; +set global max_relay_log_size=(5*4096); +select @@global.max_relay_log_size; +start slave; +sync_with_master; +--replace_result $MASTER_MYPORT MASTER_PORT 3306 MASTER_PORT 3334 MASTER_PORT +show slave status; +stop slave; +reset slave; +set global max_relay_log_size=0; +select @@global.max_relay_log_size; +start slave; +sync_with_master; +--replace_result $MASTER_MYPORT MASTER_PORT 3306 MASTER_PORT 3334 MASTER_PORT +show slave status; + +# Tests below are mainly to ensure that we have not coded with wrong assumptions + +stop slave; +reset slave; +# test of relay log rotation when the slave is stopped +# (to make sure it does not crash). +flush logs; +--replace_result $MASTER_MYPORT MASTER_PORT 3306 MASTER_PORT 3334 MASTER_PORT +show slave status; + +reset slave; +start slave; +sync_with_master; +# test of relay log rotation when the slave is started +flush logs; +# We have now easy way to be sure that the SQL thread has now deleted the +# log we just closed. But a trick to achieve this is do an update on the master. +connection master; +create table t1 (a int); +save_master_pos; +connection slave; +sync_with_master; +--replace_result $MASTER_MYPORT MASTER_PORT 3306 MASTER_PORT 3334 MASTER_PORT +show slave status; +# one more rotation, to be sure Relay_log_space is correctly updated +flush logs; +connection master; +drop table t1; +save_master_pos; +connection slave; +sync_with_master; +--replace_result $MASTER_MYPORT MASTER_PORT 3306 MASTER_PORT 3334 MASTER_PORT +show slave status; + +connection master; +# test that the absence of relay logs does not make a master crash +flush logs; +show master status; diff --git a/mysql-test/t/rpl_relayspace-slave.opt b/mysql-test/t/rpl_relayspace-slave.opt index 9365a2a0a26..05cb01731d2 100644 --- a/mysql-test/t/rpl_relayspace-slave.opt +++ b/mysql-test/t/rpl_relayspace-slave.opt @@ -1 +1 @@ - -O relay_log_space_limit=1024
\ No newline at end of file + -O relay_log_space_limit=10
\ No newline at end of file diff --git a/mysql-test/t/rpl_relayspace.test b/mysql-test/t/rpl_relayspace.test index 8d4f01339c7..bb82781b511 100644 --- a/mysql-test/t/rpl_relayspace.test +++ b/mysql-test/t/rpl_relayspace.test @@ -1,33 +1,32 @@ -# The slave is started with relay_log_space_limit=1024 bytes, -# to force the deadlock +# The slave is started with relay_log_space_limit=10 bytes, +# to force the deadlock after one event. source include/master-slave.inc; connection slave; stop slave; connection master; +# This will generate a master's binlog > 10 bytes create table t1 (a int); -let $1=200; -disable_query_log; -while ($1) -{ -# eval means expand $ expressions - eval insert into t1 values( $1 ); - dec $1; -} -# This will generate one 10kB master's binlog -enable_query_log; -save_master_pos; +drop table t1; +create table t1 (a int); +drop table t1; connection slave; reset slave; +start slave io_thread; +# Give the I/O thread time to block. +sleep 2; +# A bug caused the I/O thread to refuse stopping. +stop slave io_thread; +reset slave; start slave; # The I/O thread stops filling the relay log when -# it's 1kB. And the SQL thread cannot purge this relay log +# it's >10b. And the SQL thread cannot purge this relay log # as purge is done only when the SQL thread switches to another # relay log, which does not exist here. # So we should have a deadlock. # if it is not resolved automatically we'll detect -# it with master_pos_wait that waits for farther than 1kB; -# it will timeout after 45 seconds; +# it with master_pos_wait that waits for farther than 1Ob; +# it will timeout after 10 seconds; # also the slave will probably not cooperate to shutdown # (as 2 threads are locked) -select master_pos_wait('master-bin.001',5000,45)=-1; +select master_pos_wait('master-bin.001',200,6)=-1; diff --git a/mysql-test/t/rpl_reset_slave.test b/mysql-test/t/rpl_reset_slave.test new file mode 100644 index 00000000000..9c58ac0c787 --- /dev/null +++ b/mysql-test/t/rpl_reset_slave.test @@ -0,0 +1,26 @@ +# See SHOW SLAVE STATUS displays well after RESET SLAVE (it should display the +# --master-* options from mysqld, as this is what is going to be used next time +# slave threads will be started). In bug 985, it displayed old values (of before +# RESET SLAVE). + +source include/master-slave.inc; +connection master; +save_master_pos; +connection slave; +sync_with_master; +--replace_result $MASTER_MYPORT MASTER_PORT +show slave status; + +stop slave; +change master to master_user='test'; +--replace_result $MASTER_MYPORT MASTER_PORT +show slave status; + +reset slave; +--replace_result $MASTER_MYPORT MASTER_PORT +show slave status; + +start slave; +sync_with_master; +--replace_result $MASTER_MYPORT MASTER_PORT +show slave status; diff --git a/mysql-test/t/rpl_rotate_logs-master.opt b/mysql-test/t/rpl_rotate_logs-master.opt index f27601e0d7d..ad2c6a647b5 100644 --- a/mysql-test/t/rpl_rotate_logs-master.opt +++ b/mysql-test/t/rpl_rotate_logs-master.opt @@ -1 +1 @@ --O max_binlog_size=2048 +-O max_binlog_size=4096 diff --git a/mysql-test/t/rpl_rotate_logs.test b/mysql-test/t/rpl_rotate_logs.test index df506c08e71..d36f49eee5c 100644 --- a/mysql-test/t/rpl_rotate_logs.test +++ b/mysql-test/t/rpl_rotate_logs.test @@ -20,14 +20,23 @@ connection slave; --disable_warnings drop table if exists t1, t2, t3, t4; --enable_warnings + +# START SLAVE will fail because it can't read the file (mode 000) +# (system error 13) --error 1201 start slave; system chmod 600 var/slave-data/master.info; +# It will fail again because the file is empty so the slave cannot get valuable +# info about how to connect to the master from it (failure in +# init_strvar_from_file() in init_master_info()). --error 1201 start slave; --replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT -# Will get error 13 on Unix systems becasue file is not readable -!eval change master to master_host='127.0.0.1',master_port=$MASTER_MYPORT, master_user='root'; + +# CHANGE MASTER will fail because it first parses master.info before changing +# it (so when master.info is bad, people have to use RESET SLAVE first). +--error 1201 +eval change master to master_host='127.0.0.1',master_port=$MASTER_MYPORT, master_user='root'; reset slave; --replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT eval change master to master_host='127.0.0.1',master_port=$MASTER_MYPORT, master_user='root'; @@ -72,12 +81,9 @@ insert into t2 values(1234); #same value on the master connection master; -save_master_pos; set insert_id=1234; insert into t2 values(NULL); connection slave; -sync_with_master; - wait_for_slave_to_stop; #restart slave skipping one event @@ -126,8 +132,6 @@ show binary logs; show master status; save_master_pos; connection slave; -#stop slave; -#start slave; sync_with_master; select * from t4; diff --git a/mysql-test/t/sel000100.test b/mysql-test/t/sel000100.test index 2f9c06d5cf4..c9923d178c6 100644 --- a/mysql-test/t/sel000100.test +++ b/mysql-test/t/sel000100.test @@ -31,3 +31,18 @@ GROUP BY t1.id ORDER BY link; drop table t1,t2; + +# +# test case for #674 +# +CREATE TABLE t1 ( + html varchar(5) default NULL, + rin int(11) default '0', + out int(11) default '0' +) TYPE=MyISAM; + +INSERT INTO t1 VALUES ('1',1,0); + +SELECT DISTINCT html,SUM(out)/(SUM(rin)+1) as 'prod' FROM t1 GROUP BY rin; + +drop table t1; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 3c26cf1903b..4593eeb0691 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -1527,10 +1527,24 @@ select t2.fld1,t22.fld1 from t2,t2 t22 where t2.fld1 >= 250501 and t2.fld1 <= 25 # # Test of left join. # +insert into t2 (fld1, companynr) values (999999,99); select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; +select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null; explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null; +delete from t2 where fld1=999999; + +# +# Test left join optimization + +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0; +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0; +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0; +# Following can't be optimized +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null; +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0; +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0; # # Joins with forms. @@ -1821,3 +1835,23 @@ CREATE TABLE t2 ( id int(11) NOT NULL auto_increment, PRIMARY KEY (id)) TYPE=M INSERT INTO t2 VALUES (2517), (2518), (2519), (2520), (2521), (2522); select * from t1, t2 WHERE t1.t2_id = t2.id and t1.t2_id > 0 order by t1.id LIMIT 0, 5; drop table t1,t2; + +# +# outer join, impossible on condition, where, and usable key for range +# +create table t1 (id1 int NOT NULL); +create table t2 (id2 int NOT NULL); +create table t3 (id3 int NOT NULL); +create table t4 (id4 int NOT NULL, id44 int NOT NULL, KEY (id4)); + +insert into t1 values (1); +insert into t1 values (2); +insert into t2 values (1); +insert into t4 values (1,1); + +explain select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3 +left join t4 on id3 = id4 where id2 = 1 or id4 = 1; +select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3 +left join t4 on id3 = id4 where id2 = 1 or id4 = 1; + +drop table t1,t2,t3,t4; diff --git a/mysql-test/t/select_safe.test b/mysql-test/t/select_safe.test index be25c7c05d1..1f46355d39b 100644 --- a/mysql-test/t/select_safe.test +++ b/mysql-test/t/select_safe.test @@ -15,7 +15,7 @@ delete from t1 where a=1; insert into t1 values(1,"test"),(2,"test2"); SELECT SQL_BUFFER_RESULT * from t1; update t1 set b="a" where a=1; -select 1 from t1,t1 as t2,t1 as t3,t1 as t4; +select 1 from t1,t1 as t2,t1 as t3; # The following should give errors: --error 1175 @@ -39,19 +39,31 @@ delete from t1 where a+0=1 limit 2; # Test SQL_BIG_SELECTS +alter table t1 add key b (b); SET MAX_JOIN_SIZE=2; SELECT @@MAX_JOIN_SIZE, @@SQL_BIG_SELECTS; insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"); --error 1104 -SELECT * from t1; +SELECT * from t1 order by a; SET SQL_BIG_SELECTS=1; -SELECT * from t1; +SELECT * from t1 order by a; SET MAX_JOIN_SIZE=2; --error 1104 SELECT * from t1; SET MAX_JOIN_SIZE=DEFAULT; SELECT * from t1; +# +# Test MAX_SEEKS_FOR_KEY +# +SELECT @@MAX_SEEKS_FOR_KEY; +analyze table t1; +insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"); +explain select * from t1,t1 as t2 where t1.b=t2.b; +set MAX_SEEKS_FOR_KEY=1; +explain select * from t1,t1 as t2 where t1.b=t2.b; +SET MAX_SEEKS_FOR_KEY=DEFAULT; + drop table t1; SET SQL_SAFE_UPDATES=0,SQL_SELECT_LIMIT=DEFAULT, SQL_MAX_JOIN_SIZE=DEFAULT; diff --git a/mysql-test/t/symlink.test b/mysql-test/t/symlink.test index 67126930e60..a8daf36ed10 100644 --- a/mysql-test/t/symlink.test +++ b/mysql-test/t/symlink.test @@ -5,6 +5,7 @@ enable_query_log; --disable_warnings drop table if exists t1,t2,t7,t8,t9; +drop database if exists mysqltest; --enable_warnings # @@ -68,26 +69,25 @@ create table t1 (a int not null auto_increment, b char(16) not null, primary key # Check that we cannot link over a table from another database. -drop database if exists test_mysqltest; -create database test_mysqltest; +create database mysqltest; --error 1,1 -create table test_mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam index directory="/this-dir-does-not-exist"; +create table mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam index directory="/this-dir-does-not-exist"; --error 1103,1103 -create table test_mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam index directory="not-hard-path"; +create table mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam index directory="not-hard-path"; --error 1,1 -eval create table test_mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam index directory="$MYSQL_TEST_DIR/var/run"; +eval create table mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam index directory="$MYSQL_TEST_DIR/var/run"; --error 1,1 -eval create table test_mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam data directory="$MYSQL_TEST_DIR/var/tmp"; +eval create table mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam data directory="$MYSQL_TEST_DIR/var/tmp"; enable_query_log; -# Check moving table t9 from default database to test_mysqltest; +# Check moving table t9 from default database to mysqltest; # In this case the symlinks should be removed. -alter table t9 rename test_mysqltest.t9; -select count(*) from test_mysqltest.t9; -show create table test_mysqltest.t9; -drop database test_mysqltest; +alter table t9 rename mysqltest.t9; +select count(*) from mysqltest.t9; +show create table mysqltest.t9; +drop database mysqltest; diff --git a/mysql-test/t/type_date.test b/mysql-test/t/type_date.test index 6319a3990e6..5a53c635e6e 100644 --- a/mysql-test/t/type_date.test +++ b/mysql-test/t/type_date.test @@ -37,6 +37,7 @@ INSERT INTO t1 VALUES ( "2000-1-3" ); INSERT INTO t1 VALUES ( "2000-1-4" ); INSERT INTO t1 VALUES ( "2000-1-5" ); SELECT * FROM t1 WHERE datum BETWEEN "2000-1-2" AND "2000-1-4"; +SELECT * FROM t1 WHERE datum BETWEEN "2000-1-2" AND datum - INTERVAL 100 DAY; DROP TABLE t1; # diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test index e9c45b2908f..d2611973101 100644 --- a/mysql-test/t/type_datetime.test +++ b/mysql-test/t/type_datetime.test @@ -39,8 +39,11 @@ drop table t1; # create table t1 (id int, dt datetime); -insert into t1 values (1,"2001-08-14 00:00:00"),(2,"2001-08-15 00:00:00"),(3,"2001-08-16 00:00:00"); +insert into t1 values (1,"2001-08-14 00:00:00"),(2,"2001-08-15 00:00:00"),(3,"2001-08-16 00:00:00"),(4,"2003-09-15 01:20:30"); select * from t1 where dt='2001-08-14 00:00:00' and dt = if(id=1,'2001-08-14 00:00:00','1999-08-15'); +create index dt on t1 (dt); +select * from t1 where dt > 20021020; +select * from t1 ignore index (dt) where dt > 20021020; drop table t1; # diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index aeea27ade0f..c5aa725a459 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -26,8 +26,10 @@ select 't1',b,count(*) from t1 group by b UNION select 't2',b,count(*) from t2 g --error 1248 (select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by t1.b; explain (select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by b desc; -#(select sql_calc_found_rows a,b from t1 limit 2) union all (select a,b from t2 order by a) limit 2; -#select found_rows(); +(select sql_calc_found_rows a,b from t1 limit 2) union all (select a,b from t2 order by a) limit 2; +select found_rows(); +select sql_calc_found_rows a,b from t1 union all select a,b from t2 limit 2; +select found_rows(); # # Test some error conditions with UNION @@ -176,6 +178,22 @@ SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left drop table t1,t2,t3,t4,t5,t6; # +# Test insert ... SELECT with UNION +# + +CREATE TABLE t1 (a int not null, b char (10) not null); +insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c'); +CREATE TABLE t2 (a int not null, b char (10) not null); +insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e'); +create table t3 select a,b from t1 union select a,b from t2; +create table t4 (select a,b from t1) union (select a,b from t2) limit 2; +insert into t4 select a,b from t1 union select a,b from t2; +insert into t3 (select a,b from t1) union (select a,b from t2) limit 2; +select * from t3; +select * from t4; +drop table t1,t2,t3,t4; + +# # Test for another bug with UNION and LEFT JOIN # CREATE TABLE t1 ( id int(3) unsigned default '0') TYPE=MyISAM; diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test index 6260db8c370..514eace25a3 100644 --- a/mysql-test/t/user_var.test +++ b/mysql-test/t/user_var.test @@ -21,3 +21,9 @@ explain select * from t1 where @vv1:=@vv1+1 and i=@vv1; explain select @vv1:=i from t1 where i=@vv1; explain select * from t1 where i=@vv1; drop table t1,t2; + +# Check types of variables +select @a:=10, @b:=1, @a > @b, @a < @b; +select @a:="10", @b:="1", @a > @b, @a < @b; +select @a:=10, @b:=2, @a > @b, @a < @b; +select @a:="10", @b:="2", @a > @b, @a < @b; |