diff options
Diffstat (limited to 'mysql-test/t')
104 files changed, 2765 insertions, 784 deletions
diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index dbfbd4267d8..2b329f3ec6e 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -10,10 +10,14 @@ col3 varchar (20) not null, col4 varchar(4) not null, col5 enum('PENDING', 'ACTIVE', 'DISABLED') not null, col6 int not null, to_be_deleted int); +insert into t1 values (2,4,3,5,"PENDING",1,7); alter table t1 add column col4_5 varchar(20) not null after col4, -add column col7 varchar(30) not null after col6, -add column col8 datetime not null, drop column to_be_deleted; +add column col7 varchar(30) not null after col5, +add column col8 datetime not null, drop column to_be_deleted, +change column col2 fourth varchar(30) not null after col3, +modify column col6 int not null first; +select * from t1; drop table t1; create table t1 (bandID MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY, payoutID SMALLINT UNSIGNED NOT NULL); @@ -73,6 +77,26 @@ OPTIMIZE TABLE t1; DROP TABLE t1; # +# ALTER TABLE ... ENABLE/DISABLE KEYS + +create table t1 (n1 int not null, n2 int, n3 int, n4 float, + unique(n1), + key (n1, n2, n3, n4), + key (n2, n3, n4, n1), + key (n3, n4, n1, n2), + key (n4, n1, n2, n3) ); +alter table t1 disable keys; +#let $1=10000; +let $1=10; +while ($1) +{ + eval insert into t1 values($1,RAND()*1000,RAND()*1000,RAND()); + dec $1; +} +alter table t1 enable keys; +drop table t1; + +# # Drop and add an auto_increment column # @@ -82,3 +106,12 @@ alter table t1 drop i,add i int unsigned not null auto_increment, drop primary k select * from t1; drop table t1; +# +# Alter table and rename +# + +create table t1 (i int unsigned not null auto_increment primary key); +alter table t1 rename t2; +alter table t2 rename t1, add c char(10) comment "no comment"; +show columns from t1; +drop table t1; diff --git a/mysql-test/t/analyse.test b/mysql-test/t/analyse.test index 1b5022f6e4c..3f56b3e47ce 100644 --- a/mysql-test/t/analyse.test +++ b/mysql-test/t/analyse.test @@ -2,6 +2,7 @@ # Test of procedure analyse # +drop table if exists t1,t2; create table t1 (i int, j int); insert into t1 values (1,2), (3,4), (5,6), (7,8); select * from t1 procedure analyse(); diff --git a/mysql-test/t/auto_increment.test b/mysql-test/t/auto_increment.test index b9b8c244699..d86466572d8 100644 --- a/mysql-test/t/auto_increment.test +++ b/mysql-test/t/auto_increment.test @@ -2,6 +2,7 @@ # Test of auto_increment; The test for BDB tables is in bdb.test # +drop table if exists t1; create table t1 (a int not null auto_increment,b int, primary key (a)) type=myisam auto_increment=3; insert into t1 values (1,1),(NULL,3),(NULL,4); delete from t1 where a=4; @@ -18,20 +19,6 @@ insert into t1 values (NULL,9,9); select * from t1; drop table t1; -create table t1 (a int not null auto_increment,b int, primary key (a)) type=isam; -insert into t1 values (1,1),(NULL,2),(3,3),(NULL,4); -delete from t1 where a=4 or a=2; -insert into t1 values (NULL,4),(NULL,5),(6,6); -select * from t1; -delete from t1 where a=6; -#show table status like "t1"; -replace t1 values (3,1); -replace t1 values (3,3); -ALTER TABLE t1 add c int; -insert into t1 values (NULL,6,6); -select * from t1; -drop table t1; - create table t1 ( skey tinyint unsigned NOT NULL auto_increment PRIMARY KEY, sval char(20) diff --git a/mysql-test/t/backup.test b/mysql-test/t/backup.test index 71343783d69..ad332cde646 100644 --- a/mysql-test/t/backup.test +++ b/mysql-test/t/backup.test @@ -40,9 +40,3 @@ unlock tables; connection con1; reap; drop table t1; - - - - - - diff --git a/mysql-test/t/bdb-crash.test b/mysql-test/t/bdb-crash.test index 55e00ad2a5e..cdbc2dbe0e4 100644 --- a/mysql-test/t/bdb-crash.test +++ b/mysql-test/t/bdb-crash.test @@ -1,3 +1,4 @@ +-- source include/have_bdb.inc # test for bug reported by Mark Steele drop table if exists t1; diff --git a/mysql-test/t/bdb.test b/mysql-test/t/bdb.test index 50698bb8df6..0df93b5f220 100644 --- a/mysql-test/t/bdb.test +++ b/mysql-test/t/bdb.test @@ -767,3 +767,19 @@ select INFO_NOTE from t1 where STR_DATE = '20010610'; select INFO_NOTE from t1 where STR_DATE < '20010610'; select INFO_NOTE from t1 where STR_DATE > '20010610'; drop table t1; + +# +# Test problem with multi table delete which quickly shows up with bdb tables. +# + +create table t1 (a int not null, b int, primary key (a)) type =bdb; +create table t2 (a int not null, b int, primary key (a)) type =bdb; +insert into t1 values (2, 3),(1, 7),(10, 7); +insert into t2 values (2, 3),(1, 7),(10, 7); +select * from t1; +select * from t2; +delete t1, t2 from t1, t2 where t1.a = t2.a; +select * from t1; +select * from t2; +select * from t2; +drop table t1,t2; diff --git a/mysql-test/t/bench_count_distinct.test b/mysql-test/t/bench_count_distinct.test index 62d456a3cf8..9059428bea4 100644 --- a/mysql-test/t/bench_count_distinct.test +++ b/mysql-test/t/bench_count_distinct.test @@ -1,12 +1,13 @@ drop table if exists t1; create table t1(n int not null, key(n)) delay_key_write = 1; let $1=100; +disable_query_log; while ($1) { eval insert into t1 values($1); eval insert into t1 values($1); dec $1; } - +enable_query_log; select count(distinct n) from t1; drop table t1; diff --git a/mysql-test/t/bigint.test b/mysql-test/t/bigint.test index 6470b6f6a30..cec3e1ca45c 100644 --- a/mysql-test/t/bigint.test +++ b/mysql-test/t/bigint.test @@ -14,3 +14,26 @@ select * from t1 where a='18446744073709551615'; delete from t1 where a=18446744073709551615; select * from t1; drop table t1; + +create table t1 ( a int not null default 1, big bigint ); +insert into t1 (big) values (-1),(12345678901234567),(9223372036854775807),(18446744073709551615); +select min(big),max(big),max(big)-1 from t1; +select min(big),max(big),max(big)-1 from t1 group by a; +alter table t1 modify big bigint unsigned not null; +select min(big),max(big),max(big)-1 from t1; +select min(big),max(big),max(big)-1 from t1 group by a; +alter table t1 add key (big); +select min(big),max(big),max(big)-1 from t1; +select min(big),max(big),max(big)-1 from t1 group by a; +alter table t1 modify big bigint not null; +select min(big),max(big),max(big)-1 from t1; +select min(big),max(big),max(big)-1 from t1 group by a; +drop table t1; + +select CAST(1-2 AS UNSIGNED); +select CAST(CAST(1-2 AS UNSIGNED) AS SIGNED INTEGER); +select CONVERT('-1',UNSIGNED); +select cast(-5 as unsigned) | 1, cast(-5 as unsigned) & -1; +select cast(-5 as unsigned) -1, cast(-5 as unsigned) + 1; +select ~5, cast(~5 as signed); +select cast(5 as unsigned) -6.0; diff --git a/mysql-test/t/bulk_replace.test b/mysql-test/t/bulk_replace.test new file mode 100644 index 00000000000..d366004c16f --- /dev/null +++ b/mysql-test/t/bulk_replace.test @@ -0,0 +1,14 @@ +# +# this is a test of bulk-insert code +# as used by REPLACE +# +# by Monty +# + +drop table if exists t1; +CREATE TABLE t1 (a int, unique (a), b int not null, unique(b), c int not null, index(c)); +replace into t1 values (1,1,1),(2,2,2),(3,1,3); +select * from t1; +check table t1; +drop table t1; + diff --git a/mysql-test/t/check.test b/mysql-test/t/check.test index 62af9f92e65..947566e725b 100644 --- a/mysql-test/t/check.test +++ b/mysql-test/t/check.test @@ -5,14 +5,17 @@ drop table if exists t1; #add a lot of keys to slow down check create table t1(n int not null, key(n), key(n), key(n), key(n)); let $1=10000; +disable_query_log; while ($1) { eval insert into t1 values ($1); dec $1; } -send check table t1 type=extended; +enable_query_log; +send check table t1 extended; connection con2; insert into t1 values (200000); connection con1; reap; drop table t1; + diff --git a/mysql-test/t/count_distinct2-master.opt b/mysql-test/t/count_distinct2-master.opt new file mode 100644 index 00000000000..d81cc55090d --- /dev/null +++ b/mysql-test/t/count_distinct2-master.opt @@ -0,0 +1 @@ +--set-variable=max_heap_table_size=16384 diff --git a/mysql-test/t/count_distinct2.test b/mysql-test/t/count_distinct2.test new file mode 100644 index 00000000000..d1bea7614c8 --- /dev/null +++ b/mysql-test/t/count_distinct2.test @@ -0,0 +1,79 @@ +drop table if exists t1; + +create table t1(n1 int, n2 int, s char(20), vs varchar(20), t text); +insert into t1 values (1,11, 'one','eleven', 'eleven'), + (1,11, 'one','eleven', 'eleven'), + (2,11, 'two','eleven', 'eleven'), + (2,12, 'two','twevle', 'twelve'), + (2,13, 'two','thirteen', 'foo'), + (2,13, 'two','thirteen', 'foo'), + (2,13, 'two','thirteen', 'bar'), + (NULL,13, 'two','thirteen', 'bar'), + (2,NULL, 'two','thirteen', 'bar'), + (2,13, NULL,'thirteen', 'bar'), + (2,13, 'two',NULL, 'bar'), + (2,13, 'two','thirteen', NULL); + +select distinct n1 from t1; +select count(distinct n1) from t1; + +select distinct n2 from t1; +select count(distinct n2) from t1; + +select distinct s from t1; +select count(distinct s) from t1; + +select distinct vs from t1; +select count(distinct vs) from t1; + +select distinct t from t1; +select count(distinct t) from t1; + +select distinct n1,n2 from t1; +select count(distinct n1,n2) from t1; + +select distinct n1,s from t1; +select count(distinct n1,s) from t1; + +select distinct s,n1,vs from t1; +select count(distinct s,n1,vs) from t1; + +select distinct s,t from t1; +select count(distinct s,t) from t1; + +select count(distinct n1), count(distinct n2) from t1; + +select count(distinct n2), n1 from t1 group by n1; +drop table t1; + +# test the conversion from tree to MyISAM +create table t1 (n int default NULL); +let $1=5000; +disable_query_log; +while ($1) +{ + eval insert into t1 values($1); + dec $1; +} +enable_query_log; + +flush status; +select count(distinct n) from t1; +show status like 'Created_tmp_disk_tables'; +drop table t1; + +#test conversion from heap to MyISAM +create table t1 (s text); +let $1=5000; +disable_query_log; +while ($1) +{ + eval insert into t1 values('$1'); + dec $1; +} +enable_query_log; +flush status; +select count(distinct s) from t1; +show status like 'Created_tmp_disk_tables'; +drop table t1; + diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 1a829eec6a3..99d4e5ae229 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -25,14 +25,10 @@ drop table if exists t1,t2; !$1164 create table t1 (a int not null auto_increment,primary key (a)) type=heap; !$1163 create table t1 (a int not null,b text) type=heap; !$1171 create table t1 (a int ,primary key(a)) type=heap; -!$1121 create table t1 (a int,b text, index(a)) type=isam; -!$1073 create table t1 (a int,b text, index(b)) type=isam; drop table if exists t1; -!$1075 create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ord,ordid)) type=isam; !$1164 create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ord,ordid)) type=heap; !$1171 create table t1 (ordid int(8), primary key (ordid)); -!$1121 create table t1 (ordid int(8), unique (ordid)) type=isam; -- error 1044,1 create table not_existing_database.test (a int); @@ -71,6 +67,22 @@ select * from t2 where b="world"; drop table t1,t2; # +# Test types after CREATE ... SELECT +# + +create table t1(x varchar(50) ); +create table t2 select x from t1 where 1=2; +describe t1; +describe t2; +drop table t2; +create table t2 select now() as a , curtime() as b, curdate() as c , 1+1 as d , 1.0 + 1 as e , 33333333333333333 + 3 as f; +describe t2; +drop table t2; +create table t2 select CAST("2001-12-29" AS DATE) as d, CAST("20:45:11" AS TIME) as t, CAST("2001-12-29 20:45:11" AS DATETIME) as dt; +describe t2; +drop table t1,t2; + +# # Test of primary key with 32 index # diff --git a/mysql-test/t/ctype_latin1_de-master.opt b/mysql-test/t/ctype_latin1_de-master.opt new file mode 100644 index 00000000000..98accd58c46 --- /dev/null +++ b/mysql-test/t/ctype_latin1_de-master.opt @@ -0,0 +1 @@ +--default-character-set=latin1_de diff --git a/mysql-test/t/ctype_latin1_de.test b/mysql-test/t/ctype_latin1_de.test new file mode 100644 index 00000000000..d39d7f6e5dd --- /dev/null +++ b/mysql-test/t/ctype_latin1_de.test @@ -0,0 +1,46 @@ +# +# Test latin_de character set +# +drop table if exists t1; +create table t1 (a char (20) not null, b int not null auto_increment, index (a,b),index(b)); +insert into t1 (a) values ('ä'),('ac'),('ae'),('ad'),('Äc'),('aeb'); +insert into t1 (a) values ('üc'),('uc'),('ue'),('ud'),('Ü'),('ueb'),('uf'); +insert into t1 (a) values ('ö'),('oc'),('Öa'),('oe'),('od'),('Öc'),('oeb'); +insert into t1 (a) values ('s'),('ss'),('ß'),('ßb'),('ssa'),('ssc'),('ßa'); +insert into t1 (a) values ('eä'),('uü'),('öo'),('ää'),('ääa'),('aeae'); +insert into t1 (a) values ('q'),('a'),('u'),('o'),('é'),('É'); +select a,b from t1 order by a,b; +select a,b from t1 order by upper(a),b; +select a from t1 order by a desc; +check table t1; +select * from t1 where a like "ö%"; +select * from t1 where a like binary "%É%"; +select * from t1 where a like "%Á%"; +select * from t1 where a like "%U%"; +select * from t1 where a like "%ss%"; +drop table t1; + +# The following should all be true +select strcmp('ä','ae'),strcmp('ae','ä'),strcmp('aeq','äq'),strcmp('äq','aeq'); +select strcmp('ss','ß'),strcmp('ß','ss'),strcmp('ßs','sss'),strcmp('ßq','ssq'); + +# The following should all return -1 +select strcmp('ä','af'),strcmp('a','ä'),strcmp('ää','aeq'),strcmp('ää','aeaeq'); +select strcmp('ss','ßa'),strcmp('ß','ssa'),strcmp('sßa','sssb'),strcmp('s','ß'); +select strcmp('ö','oö'),strcmp('Ü','uü'),strcmp('ö','oeb'); + +# The following should all return 1 +select strcmp('af','ä'),strcmp('ä','a'),strcmp('aeq','ää'),strcmp('aeaeq','ää'); +select strcmp('ßa','ss'),strcmp('ssa','ß'),strcmp('sssb','sßa'),strcmp('ß','s'); +select strcmp('u','öa'),strcmp('u','ö'); + +# +# Some other simple tests with the current character set +# + +create table t1 (a varchar(10), key(a)); +insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test"); +select * from t1 where a like "abc%"; +select * from t1 where a like "test%"; +select * from t1 where a like "te_t"; +drop table t1; diff --git a/mysql-test/t/dirty-close.test b/mysql-test/t/dirty_close.test index 3ed22f26d5b..3ed22f26d5b 100644 --- a/mysql-test/t/dirty-close.test +++ b/mysql-test/t/dirty_close.test diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test index bf8a03ac40d..b850ec5d562 100644 --- a/mysql-test/t/distinct.test +++ b/mysql-test/t/distinct.test @@ -207,3 +207,14 @@ insert into t1 (a) values (1),(2),(3),(4),(1),(2),(3),(4); select distinct a from t1 group by b,a having a > 2 order by a desc; select distinct a,c from t1 group by b,c,a having a > 2 order by a desc; drop table t1; + +# +# Test problem with DISTINCT and ORDER BY DESC +# + +create table t1 (a char(1), key(a)) type=myisam; +insert into t1 values('1'),('1'); +select * from t1 where a >= '1'; +select distinct a from t1 order by a desc; +select distinct a from t1 where a >= '1' order by a desc; +drop table t1; diff --git a/mysql-test/t/drop.test b/mysql-test/t/drop.test index 2a45fe8253b..c92f2b1f3b9 100644 --- a/mysql-test/t/drop.test +++ b/mysql-test/t/drop.test @@ -11,33 +11,33 @@ create table t1(n int); drop table t1; select * from t1; -#now test for a bug in drop database - it is important that the name -#of the table is the same as the name of the database - in the original -#code this triggered a bug -drop database if exists foo; -create database foo; -drop database if exists foo; -create database foo; -create table foo.foo (n int); -insert into foo.foo values (4); -select * from foo.foo; -drop database if exists foo; -create database foo; -drop database foo; +# now test for a bug in drop database - it is important that the name +# of the table is the same as the name of the database - in the original +# code this triggered a bug +drop database if exists mysqltest; +create database mysqltest; +drop database if exists mysqltest; +create database mysqltest; +create table mysqltest.mysqltest (n int); +insert into mysqltest.mysqltest values (4); +select * from mysqltest.mysqltest; +drop database if exists mysqltest; +create database mysqltest; +drop database mysqltest; # test drop/create database and FLUSH TABLES WITH READ LOCK -drop database if exists foo; +drop database if exists mysqltest; flush tables with read lock; ---error 1209 -create database foo; +--error 1209,1223; +create database mysqltest; unlock tables; -create database foo; +create database mysqltest; show databases; flush tables with read lock; ---error 1208 -drop database foo; +--error 1208,1223; +drop database mysqltest; unlock tables; -drop database foo; +drop database mysqltest; show databases; - - +--error 1008 +drop database mysqltest; diff --git a/mysql-test/t/flush.test b/mysql-test/t/flush.test index 4491de1f82b..540aa4235cc 100644 --- a/mysql-test/t/flush.test +++ b/mysql-test/t/flush.test @@ -1,3 +1,11 @@ +# This test doesn't work with the embedded version as this code +# assumes that one query is running while we are doing queries on +# a second connection. +# This would work if mysqltest run would be threaded and handle each +# connection in a separate thread. +# +-- source include/not_embedded.inc + connect (con1,localhost,root,,); connect (con2,localhost,root,,); connection con1; @@ -7,6 +15,7 @@ drop table if exists t2; create table t2(n int); insert into t2 values(3); let $1=100; +disable_query_log; while ($1) { connection con1; @@ -19,7 +28,7 @@ while ($1) reap; dec $1; } - +enable_query_log; connection con1; select * from t1; connection con2; @@ -35,15 +44,15 @@ reap; #test if drop database will wait until we release the global read lock connection con1; -drop database if exists foo; -create database foo; -create table foo.t1(n int); -insert into foo.t1 values (23); +drop database if exists mysqltest; +create database mysqltest; +create table mysqltest.t1(n int); +insert into mysqltest.t1 values (23); flush tables with read lock; connection con2; -send drop database foo; +send drop database mysqltest; connection con1; -select * from foo.t1; +select * from mysqltest.t1; unlock tables; connection con2; reap; @@ -57,3 +66,4 @@ 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 83e328ca616..65d5381d725 100644 --- a/mysql-test/t/fulltext.test +++ b/mysql-test/t/fulltext.test @@ -5,10 +5,49 @@ drop table if exists t1,t2,t3; CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b)); -INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'),('Full-text indexes', 'are called collections'),('Only MyISAM tables','support collections'),('Function MATCH ... AGAINST()','is used to do a search'),('Full-text search in MySQL', 'implements vector space model'); +INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'), + ('Full-text indexes', 'are called collections'), + ('Only MyISAM tables','support collections'), + ('Function MATCH ... AGAINST()','is used to do a 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"); + +# UNION of fulltext's +select * from t1 where MATCH(a,b) AGAINST ("collections") UNION ALL select * from t1 where MATCH(a,b) AGAINST ("indexes"); + +# boolean search + +select * from t1 where MATCH(a,b) AGAINST("support -collections" IN BOOLEAN MODE); +select * from t1 where MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE); +select * from t1 where MATCH(a,b) AGAINST("support +collections" IN BOOLEAN MODE); +select * from t1 where MATCH(a,b) AGAINST("sear*" IN BOOLEAN MODE); +select * from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE); +select * from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE); +select * from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE); +select * from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE); +select *, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1; +select *, MATCH(a,b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1; + +select * from t1 where MATCH a,b AGAINST ("+call* +coll*" IN BOOLEAN MODE); + +select * from t1 where MATCH a,b AGAINST ('"support now"' IN BOOLEAN MODE); +select * from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE); +select * from t1 where MATCH a,b AGAINST ('"text search" "now support"' IN BOOLEAN MODE); +select * from t1 where MATCH a,b AGAINST ('"text search" -"now support"' IN BOOLEAN MODE); +select * from t1 where MATCH a,b AGAINST ('"text search" +"now support"' IN BOOLEAN MODE); + +# boolean w/o index: + +select * from t1 where MATCH a AGAINST ("search" IN BOOLEAN MODE); +select * from t1 where MATCH b AGAINST ("sear*" IN BOOLEAN MODE); + +#update/delete with fulltext index + delete from t1 where a like "MySQL%"; update t1 set a='some test foobar' where MATCH a,b AGAINST ('model'); delete from t1 where MATCH(a,b) AGAINST ("indexes"); @@ -77,14 +116,47 @@ CREATE TABLE t3 ( --error 1210 select * from t2 where MATCH inhalt AGAINST (t2.inhalt); - ---error 1210 -select * from t2 where MATCH inhalt AGAINST (t2.inhalt); - --error 1191 select * from t2 where MATCH ticket AGAINST ('foobar'); - --error 1210 select * from t2,t3 where MATCH (t2.inhalt,t3.inhalt) AGAINST ('foobar'); drop table t1,t2,t3; + +# +# three more bugtests +# + +CREATE TABLE t1 ( + id int(11) auto_increment, + title varchar(100) default '', + PRIMARY KEY (id), + KEY ind5 (title), + FULLTEXT KEY FT1 (title) +) TYPE=MyISAM; + +insert into t1 (title) values ('this is a test'); +select * from t1 where match title against ('test' in boolean mode); +update t1 set title='this is A test' where id=1; +check table t1; +update t1 set title='this test once revealed a bug' where id=1; +select * from t1; +update t1 set title=NULL where id=1; + +drop table t1; + +# one more bug - const_table related + +CREATE TABLE t1 (a int(11), b text, FULLTEXT KEY (b)) TYPE=MyISAM; +insert into t1 values (1,"I wonder why the fulltext index doesnt work?"); +SELECT * from t1 where MATCH (b) AGAINST ('apples'); + +insert into t1 values (2,"fullaaa fullzzz"); +select * from t1 where match b against ('full*' in boolean mode); + +drop table t1; +CREATE TABLE t1 ( id int(11) NOT NULL auto_increment primary key, mytext text NOT NULL, FULLTEXT KEY mytext (mytext)) TYPE=MyISAM; +INSERT INTO t1 VALUES (1,'my small mouse'),(2,'la-la-la'),(3,'It is so funny'),(4,'MySQL Tutorial'); +select 8 from t1; +drop table t1; + diff --git a/mysql-test/t/fulltext_cache.test b/mysql-test/t/fulltext_cache.test index fc5f0e266b3..0b15e57a97b 100644 --- a/mysql-test/t/fulltext_cache.test +++ b/mysql-test/t/fulltext_cache.test @@ -26,10 +26,16 @@ INSERT INTO t2 VALUES (6,2,'um chocolate Snickers'); INSERT INTO t2 VALUES (7,1,'Bife'); INSERT INTO t2 VALUES (8,1,'Pizza de Salmao'); -SELECT t1.q, t2.item, t2.id, MATCH t2.item AGAINST ('sushi') as x FROM t1, t2 -WHERE (t2.id2 = t1.id) ORDER BY x DESC,t2.id; +SELECT t1.q, t2.item, t2.id, MATCH t2.item AGAINST ('sushi') +as x FROM t1, t2 WHERE (t2.id2 = t1.id) ORDER BY x DESC,t2.id; -SELECT t1.q, t2.item, t2.id, MATCH t2.item AGAINST ('sushi') as x FROM t2, t1 -WHERE (t2.id2 = t1.id) ORDER BY x DESC,t2.id; +SELECT t1.q, t2.item, t2.id, MATCH t2.item AGAINST ('sushi' IN BOOLEAN MODE) +as x FROM t1, t2 WHERE (t2.id2 = t1.id) ORDER BY x DESC,t2.id; + +SELECT t1.q, t2.item, t2.id, MATCH t2.item AGAINST ('sushi') +as x FROM t2, t1 WHERE (t2.id2 = t1.id) ORDER BY x DESC,t2.id; + +SELECT t1.q, t2.item, t2.id, MATCH t2.item AGAINST ('sushi' IN BOOLEAN MODE) +as x FROM t2, t1 WHERE (t2.id2 = t1.id) ORDER BY x DESC,t2.id; drop table t1, t2; diff --git a/mysql-test/t/fulltext_distinct.test b/mysql-test/t/fulltext_distinct.test new file mode 100644 index 00000000000..86e2f7ca1b4 --- /dev/null +++ b/mysql-test/t/fulltext_distinct.test @@ -0,0 +1,41 @@ +# +# Test of fulltext index +# bug reported by Tibor Simko <tibor.simko@cern.ch> +# + +DROP TABLE IF EXISTS t1, t2; +CREATE TABLE t1 ( + id mediumint unsigned NOT NULL auto_increment, + tag char(6) NOT NULL default '', + value text NOT NULL default '', + PRIMARY KEY (id), + KEY kt(tag), + KEY kv(value(15)), + FULLTEXT KEY kvf(value) +) TYPE=MyISAM; +CREATE TABLE t2 ( + id_t2 mediumint unsigned NOT NULL default '0', + id_t1 mediumint unsigned NOT NULL default '0', + field_number tinyint unsigned NOT NULL default '0', + PRIMARY KEY (id_t2,id_t1,field_number), + KEY id_t1(id_t1) +) TYPE=MyISAM; + +INSERT INTO t1 (tag,value) VALUES ('foo123','bar111'); +INSERT INTO t1 (tag,value) VALUES ('foo123','bar222'); +INSERT INTO t1 (tag,value) VALUES ('bar345','baz333 ar'); + +INSERT INTO t2 VALUES (2231626,64280,0); +INSERT INTO t2 VALUES (2231626,64281,0); +INSERT INTO t2 VALUES (12346, 3, 1); + +SELECT * FROM t1; SELECT * FROM t2; + +SELECT DISTINCT t2.id_t2 FROM t2, t1 +WHERE MATCH (t1.value) AGAINST ('baz333') AND t1.id = t2.id_t1; + +SELECT DISTINCT t2.id_t2 FROM t2, t1 +WHERE MATCH (t1.value) AGAINST ('baz333' IN BOOLEAN MODE) +AND t1.id = t2.id_t1; + +DROP TABLE t1, t2; diff --git a/mysql-test/t/fulltext_left_join.test b/mysql-test/t/fulltext_left_join.test index d09d577d04b..abb1c8e8473 100644 --- a/mysql-test/t/fulltext_left_join.test +++ b/mysql-test/t/fulltext_left_join.test @@ -22,6 +22,8 @@ INSERT INTO t2 VALUES('456', 'lui'); select match(t1.texte,t1.sujet,t1.motsclefs) against('droit') from t1 left join t2 on t2.id=t1.id; +select match(t1.texte,t1.sujet,t1.motsclefs) against('droit' IN BOOLEAN MODE) + from t1 left join t2 on t2.id=t1.id; drop table t1, t2; diff --git a/mysql-test/t/fulltext_order_by.test b/mysql-test/t/fulltext_order_by.test index 9f35d58e699..d5cb99ef0ee 100644 --- a/mysql-test/t/fulltext_order_by.test +++ b/mysql-test/t/fulltext_order_by.test @@ -9,17 +9,24 @@ INSERT INTO t1 (message) VALUES ("Testing"),("table"),("testbug"), ("steve"),("is"),("cool"),("steve is cool"); # basic MATCH SELECT a, MATCH (message) AGAINST ('steve') FROM t1 WHERE MATCH (message) AGAINST ('steve'); +SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve'); +SELECT a, MATCH (message) AGAINST ('steve') FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE); +SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE); # MATCH + ORDER BY (with ft-ranges) SELECT a, MATCH (message) AGAINST ('steve') FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY a; +SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) ORDER BY a; # MATCH + ORDER BY (with normal ranges) + UNIQUE SELECT a, MATCH (message) AGAINST ('steve') FROM t1 WHERE a in (2,7,4) and MATCH (message) AGAINST ('steve') ORDER BY a DESC; +SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE a in (2,7,4) and MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) ORDER BY a DESC; # MATCH + ORDER BY + UNIQUE (const_table) SELECT a, MATCH (message) AGAINST ('steve') FROM t1 WHERE a=7 and MATCH (message) AGAINST ('steve') ORDER BY 1; +SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE a=7 and MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) ORDER BY 1; # ORDER BY MATCH SELECT a, MATCH (message) AGAINST ('steve') as rel FROM t1 ORDER BY rel; +SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel; drop table t1; diff --git a/mysql-test/t/fulltext_var.test b/mysql-test/t/fulltext_var.test new file mode 100644 index 00000000000..71213d1195a --- /dev/null +++ b/mysql-test/t/fulltext_var.test @@ -0,0 +1,5 @@ +# +# Fulltext configurable parameters +# + +show variables like "ft\_%"; diff --git a/mysql-test/t/func_crypt.test b/mysql-test/t/func_crypt.test index ddc0816e301..f01504d3691 100644 --- a/mysql-test/t/func_crypt.test +++ b/mysql-test/t/func_crypt.test @@ -1,2 +1,3 @@ - select length(encrypt('foo', 'ff')) <> 0; +--replace_result $1$aa$4OSUA5cjdx0RUQ08opV27/ aaqPiZY5xR5l. +select password('test'),length(encrypt('test')),encrypt('test','aa'); diff --git a/mysql-test/t/func_encrypt-master.opt b/mysql-test/t/func_encrypt-master.opt new file mode 100644 index 00000000000..ab687810d5f --- /dev/null +++ b/mysql-test/t/func_encrypt-master.opt @@ -0,0 +1 @@ +--loose-des-key-file=$MYSQL_TEST_DIR/std_data/des_key_file diff --git a/mysql-test/t/func_encrypt.test b/mysql-test/t/func_encrypt.test new file mode 100644 index 00000000000..3b6acc54ec9 --- /dev/null +++ b/mysql-test/t/func_encrypt.test @@ -0,0 +1,67 @@ +-- source include/have_openssl.inc + +drop table if exists t1; +create table t1 (x blob); +insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','The quick red fox jumped over the lazy brown dog')); +insert into t1 values (des_encrypt('a','The quick red fox jumped over the lazy brown dog')); +insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','a')); +insert into t1 values (des_encrypt('ab','The quick red fox jumped over the lazy brown dog')); +insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','ab')); +insert into t1 values (des_encrypt('abc','The quick red fox jumped over the lazy brown dog')); +insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','abc')); +insert into t1 values (des_encrypt('abcd','The quick red fox jumped over the lazy brown dog')); +insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','abcd')); +insert into t1 values (des_encrypt('abcde','The quick red fox jumped over the lazy brown dog')); +insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','abcde')); +insert into t1 values (des_encrypt('abcdef','The quick red fox jumped over the lazy brown dog')); +insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','abcdef')); +insert into t1 values (des_encrypt('abcdefg','The quick red fox jumped over the lazy brown dog')); +insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','abcdefg')); +insert into t1 values (des_encrypt('abcdefgh','The quick red fox jumped over the lazy brown dog')); +insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','abcdefgh')); +insert into t1 values (des_encrypt('abcdefghi','The quick red fox jumped over the lazy brown dog')); +insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','abcdefghi')); +insert into t1 values (des_encrypt('abcdefghij','The quick red fox jumped over the lazy brown dog')); +insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','abcdefghij')); +insert into t1 values (des_encrypt('abcdefghijk','The quick red fox jumped over the lazy brown dog')); +insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','abcdefghijk')); +insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','sabakala')); +insert into t1 values (des_encrypt('quick red fox jumped over the lazy brown dog','sabakala')); +insert into t1 values (des_encrypt('red fox jumped over the lazy brown dog','sabakala')); +insert into t1 values (des_encrypt('fox jumped over the lazy brown dog','sabakala')); +insert into t1 values (des_encrypt('jumped over the lazy brown dog','sabakala')); +insert into t1 values (des_encrypt('over the lazy brown dog','sabakala')); +insert into t1 values (des_encrypt('the lazy brown dog','sabakala')); +insert into t1 values (des_encrypt('lazy brown dog','sabakala')); +insert into t1 values (des_encrypt('brown dog','sabakala')); +insert into t1 values (des_encrypt('dog','sabakala')); +insert into t1 values (des_encrypt('dog!','sabakala')); +insert into t1 values (des_encrypt('dog!!','sabakala')); +insert into t1 values (des_encrypt('dog!!!','sabakala')); +insert into t1 values (des_encrypt('dog!!!!','sabakala')); +insert into t1 values (des_encrypt('dog!!!!!','sabakala')); +insert into t1 values (des_encrypt('jumped over the lazy brown dog','sabakala')); +insert into t1 values (des_encrypt('jumped over the lazy brown dog','sabakala')); +select hex(x), hex(des_decrypt(x,'sabakala')) from t1; +select des_decrypt(x,'sabakala') as s from t1 having s like '%dog%'; +drop table t1; + +# +# Test default keys +# +select hex(des_encrypt("hello")),des_decrypt(des_encrypt("hello")); +select des_decrypt(des_encrypt("hello",4)); +select des_decrypt(des_encrypt("hello",'test'),'test'); +select hex(des_encrypt("hello")),hex(des_encrypt("hello",5)),hex(des_encrypt("hello",'default_password')); +select des_decrypt(des_encrypt("hello"),'default_password'); +select des_decrypt(des_encrypt("hello",4),'password4'); + +# Test flush +SET @a=des_decrypt(des_encrypt("hello")); +flush des_key_file; +select @a = des_decrypt(des_encrypt("hello")); + +# Test usage of wrong password +select hex("hello"); +select hex(des_decrypt(des_encrypt("hello",4),'password2')); +select hex(des_decrypt(des_encrypt("hello","hidden"))); diff --git a/mysql-test/t/func_like.test b/mysql-test/t/func_like.test index d160b04176c..09746fcc817 100644 --- a/mysql-test/t/func_like.test +++ b/mysql-test/t/func_like.test @@ -2,9 +2,19 @@ # Test of like # +drop table if exists t1; create table t1 (a varchar(10), key(a)); insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test"); select * from t1 where a like "abc%"; +select * from t1 where a like "ABC%"; select * from t1 where a like "test%"; select * from t1 where a like "te_t"; + +# +# The following will test the Turbo Boyer-Moore code +# +select * from t1 where a like "%a%"; +select * from t1 where a like "%abcd%"; +select * from t1 where a like "%abc\d%"; + drop table t1; diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index b7200b2d144..68b94d2aeff 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -9,10 +9,11 @@ drop table if exists t1; select 'hello',"'hello'",'""hello""','''h''e''l''l''o''',"hel""lo",'hel\'lo'; select 'hello' 'monty'; select length('\n\t\r\b\0\_\%\\'); +select bit_length('\n\t\r\b\0\_\%\\'); select concat('monty',' was here ','again'),length('hello'),char(ascii('h')); select locate('he','hello'),locate('he','hello',2),locate('lo','hello',2) ; -select instr('hello','he'); -select position('ll' in 'hello'),position('a' in 'hello'); +select instr('hello','HE'), instr('hello',binary 'HE'), instr(binary 'hello','HE'); +select position(binary 'll' in 'hello'),position('a' in binary 'hello'); select left('hello',2),right('hello',2),substring('hello',2,2),mid('hello',1,5) ; select concat('',left(right(concat('what ',concat('is ','happening')),9),4),'',substring('monty',5,1)) ; select substring_index('www.tcx.se','.',-2),substring_index('www.tcx.se','.',1); @@ -35,8 +36,17 @@ select insert('txs',2,1,'hi'),insert('is ',4,0,'a'),insert('txxxxt',2,4,'es'); select replace('aaaa','a','b'),replace('aaaa','aa','b'),replace('aaaa','a','bb'),replace('aaaa','','b'),replace('bbbb','a','c'); select replace(concat(lcase(concat('THIS',' ','IS',' ','A',' ')),ucase('false'),' ','test'),'FALSE','REAL') ; select soundex(''),soundex('he'),soundex('hello all folks'); -select password('test'),length(encrypt('test')),encrypt('test','aa'); select md5('hello'); +select sha('abc'); +select sha1('abc'); +select aes_decrypt(aes_encrypt('abc','1'),'1'); +select aes_decrypt(aes_encrypt('abc','1'),1); +select aes_encrypt(NULL,"a"); +select aes_encrypt("a",NULL); +select aes_decrypt(NULL,"a"); +select aes_decrypt("a",NULL); +select aes_decrypt("a","a"); +select aes_decrypt(aes_encrypt("","a"),"a"); select repeat('monty',5),concat('*',space(5),'*'); select reverse('abc'),reverse('abcd'); select rpad('a',4,'1'),rpad('a',4,'12'),rpad('abcd',3,'12'); diff --git a/mysql-test/t/func_system.test b/mysql-test/t/func_system.test index b0bdbe472dd..052e0530cf6 100644 --- a/mysql-test/t/func_system.test +++ b/mysql-test/t/func_system.test @@ -2,5 +2,5 @@ # system functions # -select database(),user(); +select database(),user() like "%@%"; select version()>="3.23.29"; diff --git a/mysql-test/t/func_test.test b/mysql-test/t/func_test.test index ec44009b1a6..f5ad2e21c73 100644 --- a/mysql-test/t/func_test.test +++ b/mysql-test/t/func_test.test @@ -15,6 +15,8 @@ select 2 between 1 and 3, "monty" between "max" and "my",2=2 and "monty" between select 'b' between 'a' and 'c', 'B' between 'a' and 'c'; select 2 in (3,2,5,9,5,1),"monty" in ("david","monty","allan"), 1.2 in (1.4,1.2,1.0); select -1.49 or -1.49,0.6 or 0.6; +select 3 ^ 11, 1 ^ 1, 1 ^ 0, 1 ^ NULL, NULL ^ 1; +select 1 XOR 1, 1 XOR 0, 0 XOR 1, 0 XOR 0, NULL XOR 1, 1 XOR NULL, 0 XOR NULL; # # Wrong usage of functions diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index ffb0f8bbf1e..a052f5f2d92 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -19,13 +19,23 @@ select month("1997-01-02"),year("98-02-03"),dayofyear("1997-12-31"); select month("2001-02-00"),year("2001-00-00"); select DAYOFYEAR("1997-03-03"), WEEK("1998-03-03"), QUARTER(980303); select HOUR("1997-03-03 23:03:22"), MINUTE("23:03:22"), SECOND(230322); + +# Test of week and yearweek select week(19980101),week(19970101),week(19980101,1),week(19970101,1); select week(19981231),week(19971231),week(19981231,1),week(19971231,1); select week(19950101),week(19950101,1); select yearweek('1981-12-31',1),yearweek('1982-01-01',1),yearweek('1982-12-31',1),yearweek('1983-01-01',1); +select yearweek('1987-01-01',1),yearweek('1987-01-01'); +select week("2000-01-01",0) as '2000', week("2001-01-01",0) as '2001', week("2002-01-01",0) as '2002',week("2003-01-01",0) as '2003', week("2004-01-01",0) as '2004', week("2005-01-01",0) as '2005', week("2006-01-01",0) as '2006'; +select week("2000-01-06",0) as '2000', week("2001-01-06",0) as '2001', week("2002-01-06",0) as '2002',week("2003-01-06",0) as '2003', week("2004-01-06",0) as '2004', week("2005-01-06",0) as '2005', week("2006-01-06",0) as '2006'; +select week("2000-01-01",1) as '2000', week("2001-01-01",1) as '2001', week("2002-01-01",1) as '2002',week("2003-01-01",1) as '2003', week("2004-01-01",1) as '2004', week("2005-01-01",1) as '2005', week("2006-01-01",1) as '2006'; +select week("2000-01-06",1) as '2000', week("2001-01-06",1) as '2001', week("2002-01-06",1) as '2002',week("2003-01-06",1) as '2003', week("2004-01-06",1) as '2004', week("2005-01-06",1) as '2005', week("2006-01-06",1) as '2006'; +select yearweek("2000-01-01",0) as '2000', yearweek("2001-01-01",0) as '2001', yearweek("2002-01-01",0) as '2002',yearweek("2003-01-01",0) as '2003', yearweek("2004-01-01",0) as '2004', yearweek("2005-01-01",0) as '2005', yearweek("2006-01-01",0) as '2006'; +select yearweek("2000-01-06",0) as '2000', yearweek("2001-01-06",0) as '2001', yearweek("2002-01-06",0) as '2002',yearweek("2003-01-06",0) as '2003', yearweek("2004-01-06",0) as '2004', yearweek("2005-01-06",0) as '2005', yearweek("2006-01-06",0) as '2006'; +select yearweek("2000-01-01",1) as '2000', yearweek("2001-01-01",1) as '2001', yearweek("2002-01-01",1) as '2002',yearweek("2003-01-01",1) as '2003', yearweek("2004-01-01",1) as '2004', yearweek("2005-01-01",1) as '2005', yearweek("2006-01-01",1) as '2006'; +select yearweek("2000-01-06",1) as '2000', yearweek("2001-01-06",1) as '2001', yearweek("2002-01-06",1) as '2002',yearweek("2003-01-06",1) as '2003', yearweek("2004-01-06",1) as '2004', yearweek("2005-01-06",1) as '2005', yearweek("2006-01-06",1) as '2006'; select date_format('1998-12-31','%x-%v'),date_format('1999-01-01','%x-%v'); select date_format('1999-12-31','%x-%v'),date_format('2000-01-01','%x-%v'); -select yearweek('1987-01-01',1),yearweek('1987-01-01'); select dayname("1962-03-03"),dayname("1962-03-03")+0; select monthname("1972-03-04"),monthname("1972-03-04")+0; diff --git a/mysql-test/t/gemini.test b/mysql-test/t/gemini.test deleted file mode 100644 index 9d4451c3551..00000000000 --- a/mysql-test/t/gemini.test +++ /dev/null @@ -1,355 +0,0 @@ --- source include/have_gemini.inc - -# -# Small basic test with ignore -# - -drop table if exists t1; -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=gemini; - -insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt'); -select id, code, name from t1 order by id; - -update ignore t1 set id = 8, name = 'Sinisa' where id < 3; -select id, code, name from t1 order by id; -update ignore t1 set id = id + 10, name = 'Ralph' where id < 4; -select id, code, name from t1 order by id; - -drop table t1; - -# -# A bit bigger test -# - -CREATE TABLE t1 ( - id int(11) NOT NULL auto_increment, - parent_id int(11) DEFAULT '0' NOT NULL, - level tinyint(4) DEFAULT '0' NOT NULL, - PRIMARY KEY (id), - KEY parent_id (parent_id), - KEY level (level) -) type=gemini; -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); -update t1 set parent_id=parent_id+100; -select * from t1 where parent_id=102; -update t1 set id=id+1000; -!$1062 update t1 set id=1024 where id=1009; -select * from t1; -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; -select level,id from t1 where level=1; -select level,id,parent_id from t1 where level=1; -drop table t1; - -# -# Test replace -# - -CREATE TABLE t1 ( - gesuchnr int(11) DEFAULT '0' NOT NULL, - benutzer_id int(11) DEFAULT '0' NOT NULL, - PRIMARY KEY (gesuchnr,benutzer_id) -) type=gemini; - -replace into t1 (gesuchnr,benutzer_id) values (2,1); -replace into t1 (gesuchnr,benutzer_id) values (1,1); -replace into t1 (gesuchnr,benutzer_id) values (1,1); -select * from t1; -drop table t1; - -# -# test delete using hidden_primary_key -# - -create table t1 (a int) type=gemini; -insert into t1 values (1), (2); -delete from t1 where a = 1; -select * from t1; -drop table t1; - -# -# Test auto_increment on sub key -# - -#create table t1 (a char(10) not null, b int not null auto_increment, primary key(a,b)) type=gemini; -#insert into t1 values ("a",1),("b",2),("a",2),("c",1); -#insert into t1 values ("a",NULL),("b",NULL),("c",NULL),("e",NULL); -#insert into t1 (a) values ("a"),("b"),("c"),("d"); -#insert into t1 (a) values ('k'),('d'); -#insert into t1 (a) values ("a"); -#insert into t1 values ("d",last_insert_id()); -#select * from t1; -#drop table t1; - -# -# Test when reading on part of unique key -# -CREATE TABLE t1 ( - user_id int(10) DEFAULT '0' NOT NULL, - name varchar(100), - phone varchar(100), - ref_email varchar(100) DEFAULT '' NOT NULL, - detail varchar(200), - PRIMARY KEY (user_id,ref_email) -)type=gemini; - -INSERT INTO t1 VALUES (10292,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10292,'shirish','2333604','shirish@yahoo.com','ddsds'),(10292,'sonali','323232','sonali@bolly.com','filmstar'); -select * from t1 where user_id=10292; -INSERT INTO t1 VALUES (10291,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10293,'shirish','2333604','shirish@yahoo.com','ddsds'); -select * from t1 where user_id=10292; -select * from t1 where user_id>=10292; -select * from t1 where user_id>10292; -select * from t1 where user_id<10292; -drop table t1; - -# -# Test that keys are created in right order -# - Needs ANALYZE TABLE to work - MikeF 2/12/01 -# -#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)) type = gemini; -#show index from t1; -#drop table t1; - -# -# Test of ALTER TABLE and gemini tables -# - -#create table t1 (col1 int not null, col2 char(4) not null, primary key(col1)); -#alter table t1 type=gemini; -#insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4'); -#select * from t1; -#update t1 set col2='7' where col1='4'; -#select * from t1; -#alter table t1 add co3 int not null; -#select * from t1; -#update t1 set col2='9' where col1='2'; -#select * from t1; -#drop table t1; - -# -# INSERT INTO gemini tables -# - -create table t1 (a int not null , b int, primary key (a)) type = gemini; -create table t2 (a int not null , b int, primary key (a)) type = myisam; -insert into t1 VALUES (1,3) , (2,3), (3,3); -select * from t1; -insert into t2 select * from t1; -select * from t2; -delete from t1 where b = 3; -select * from t1; -insert into t1 select * from t2; -select * from t1; -select * from t2; -drop table t1,t2; - -# -# Search on unique key -# - -CREATE TABLE t1 ( - id int(11) NOT NULL auto_increment, - ggid varchar(32) binary DEFAULT '' NOT NULL, - email varchar(64) DEFAULT '' NOT NULL, - passwd varchar(32) binary DEFAULT '' NOT NULL, - PRIMARY KEY (id), - UNIQUE ggid (ggid) -) TYPE=gemini; - -insert into t1 (ggid,passwd) values ('test1','xxx'); -insert into t1 (ggid,passwd) values ('test2','yyy'); - -select * from t1 where ggid='test1'; -select * from t1 where passwd='xxx'; -select * from t1 where id=2; -drop table t1; - -# -# ORDER BY on not primary key -# - -#CREATE TABLE t1 ( -# user_name varchar(12), - #password text, - #subscribed char(1), - #user_id int(11) DEFAULT '0' NOT NULL, - #quota bigint(20), - #weight double, - #access_date date, - #access_time time, - #approved datetime, - #dummy_primary_key int(11) NOT NULL auto_increment, - #PRIMARY KEY (dummy_primary_key) -#) TYPE=gemini; -#INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','23:06:59','2000-09-07 23:06:59',1); -#INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','23:06:59','2000-09-07 23:06:59',2); -#INSERT INTO t1 VALUES ('user_2','somepassword','N',2,2,1.4142135623731,'2000-09-07','23:06:59','2000-09-07 23:06:59',3); -#INSERT INTO t1 VALUES ('user_3','somepassword','Y',3,3,1.7320508075689,'2000-09-07','23:06:59','2000-09-07 23:06:59',4); -#INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','23:06:59','2000-09-07 23:06:59',5); -#select user_name, password , subscribed, user_id, quota, weight, access_date, access_time, approved, dummy_primary_key from t1 order by user_name; -#drop table t1; - -# -# Testing of tables without primary keys -# - -CREATE TABLE t1 ( - id int(11) NOT NULL auto_increment, - parent_id int(11) DEFAULT '0' NOT NULL, - level tinyint(4) DEFAULT '0' NOT NULL, - KEY (id), - KEY parent_id (parent_id), - KEY level (level) -) type=gemini; -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); -INSERT INTO t1 values (179,5,2); -update t1 set parent_id=parent_id+100; -select * from t1 where parent_id=102; -update t1 set id=id+1000; -update t1 set id=1024 where id=1009; -select * from t1; -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; -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; -delete from t1 where level=1; -select * from t1; -drop table t1; - -# -# Test of index only reads -# -CREATE TABLE t1 ( - sca_code char(6) NOT NULL, - cat_code char(6) NOT NULL, - sca_desc varchar(50), - lan_code char(2) NOT NULL, - sca_pic varchar(100), - sca_sdesc varchar(50), - sca_sch_desc varchar(16), - PRIMARY KEY (sca_code, cat_code, lan_code) -) type = gemini ; - -INSERT INTO t1 ( sca_code, cat_code, sca_desc, lan_code, sca_pic, sca_sdesc, sca_sch_desc) VALUES ( 'PD', 'J', 'PENDANT', 'EN', NULL, NULL, 'PENDANT'),( 'RI', 'J', 'RING', 'EN', NULL, NULL, 'RING'); -select count(*) from t1 where sca_code = 'PD'; -drop table t1; - -# -# Test of opening table twice -# -CREATE TABLE t1 (a int not null, primary key (a)) type=gemini; -insert into t1 values(1),(2),(3); -select t1.a from t1 natural join t1 as t2 order by t1.a; -drop table t1; - -# -# Test rollback -# - -select "test for rollback"; -create table t1 (n int not null primary key) type=gemini; -set autocommit=0; -insert into t1 values (4); -commit; -insert into t1 values (5); -rollback; -select n, "after rollback" from t1; -insert into t1 values (5); -commit; -select n, "after commit" from t1; -commit; -insert into t1 values (6); -!$1062 insert into t1 values (4); -commit; -select n, "after commit" from t1; -set autocommit=1; -insert into t1 values (7); -!$1062 insert into t1 values (4); -select n from t1; -# nop -rollback; -drop table t1; - -# -# Testing transactions -# - -create table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) type=gemini; -insert into t1 values(1,'first'); -begin; -insert into t1 values(2,'hamdouni'); -select id as afterbegin_id,nom as afterbegin_nom from t1; -rollback; -select id as afterrollback_id,nom as afterrollback_nom from t1; -set autocommit=0; -insert into t1 values(3,'mysql'); -select id as afterautocommit0_id,nom as afterautocommit0_nom from t1; -rollback; -select id as afterrollback_id,nom as afterrollback_nom from t1; -set autocommit=1; -drop table t1; - -# -# Simple not autocommit test -# - -CREATE TABLE t1 (id char(8) not null primary key, val int not null) type=gemini; -insert into t1 values ('pippo', 12); -!$1062 insert into t1 values ('pippo', 12); # Gives error -delete from t1; -delete from t1 where id = 'pippo'; -select * from t1; - -insert into t1 values ('pippo', 12); -set autocommit=0; -delete from t1; -rollback; -select * from t1; -delete from t1; -commit; -select * from t1; -drop table t1; -set autocommit=1; - -# -# The following simple tests failed at some point -# - -CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(64)) TYPE=gemini; -INSERT INTO t1 VALUES (1, 'Jochen'); -select * from t1; -drop table t1; - -CREATE TABLE t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) TYPE=gemini; -set autocommit=0; -INSERT INTO t1 SET _userid='marc@anyware.co.uk'; -COMMIT; -SELECT * FROM t1; -SELECT _userid FROM t1 WHERE _userid='marc@anyware.co.uk'; -drop table t1; -set autocommit=1; - -# -# Test of load data infile -# - -CREATE TABLE if not exists `t1` ( - `f1` int(11) unsigned NOT NULL default '0', - `f2` tinyint(3) unsigned NOT NULL default '0', - `f3` tinyint(3) unsigned NOT NULL default '0', - PRIMARY KEY (`f1`) -) TYPE=Gemini; -lock table t1 write; -load data infile ''../../std_data/gemini.dat' ignore into table t1 fields terminated by ','; -select f1 from t1; -drop table t1; diff --git a/mysql-test/t/grant_cache-master.opt b/mysql-test/t/grant_cache-master.opt new file mode 100644 index 00000000000..cfdce628e74 --- /dev/null +++ b/mysql-test/t/grant_cache-master.opt @@ -0,0 +1 @@ +--set-variable=query_cache_size=1355776 diff --git a/mysql-test/t/grant_cache.test b/mysql-test/t/grant_cache.test new file mode 100644 index 00000000000..09ce1021fbf --- /dev/null +++ b/mysql-test/t/grant_cache.test @@ -0,0 +1,104 @@ +-- source include/have_query_cache.inc + +# +# Test grants with query cache +# +drop table if exists test.t1,mysqltest.t1,mysqltest.t2; +reset query cache; +flush status; +connect (root,localhost,root,,test,0,master.sock); +connection root; +create database if not exists mysqltest; + +create table mysqltest.t1 (a int,b int,c int); +create table mysqltest.t2 (a int,b int,c int); +insert into mysqltest.t1 values (1,1,1),(2,2,2); +insert into mysqltest.t2 values (3,3,3); +create table test.t1 (a char (10)); +insert into test.t1 values ("test.t1"); +select * from t1; +connect (root2,localhost,root,,mysqltest,0,master.sock); +connection root2; +# put queries in cache +select * from t1; +select a from t1; +select c from t1; +select * from t2; +select * from mysqltest.t1,test.t1; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_hits%"; + +# Create the test users +grant SELECT on mysqltest.* to mysqltest_1@localhost; +grant SELECT on mysqltest.t1 to mysqltest_2@localhost; +grant SELECT on test.t1 to mysqltest_2@localhost; +grant SELECT(a) on mysqltest.t1 to mysqltest_3@localhost; + +# The following queries should be fetched from cache +connect (user1,localhost,mysqltest_1,,mysqltest,0,master.sock); +connection user1; +select "user1"; +select * from t1; +# The pre and end space are intentional + select a from t1 ; +select c from t1; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_hits"; +show status like "Qcache_not_cached"; + +# The following queries should be fetched from cache +connect (user2,localhost,mysqltest_2,,mysqltest,0,master.sock); +connection user2; +select "user2"; +select * from t1; +select a from t1; +select c from t1; +select * from mysqltest.t1,test.t1; +--error 1142 +select * from t2; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_hits"; +show status like "Qcache_not_cached"; + +# The following queries should not be fetched from cache +connect (user3,localhost,mysqltest_3,,mysqltest,0,master.sock); +connection user3; +select "user3"; +--error 1143 +select * from t1; +select a from t1; +--error 1143 +select c from t1; +--error 1142 +select * from t2; +--error 1143 +select mysqltest.t1.c from test.t1,mysqltest.t1; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_hits"; +show status like "Qcache_not_cached"; + +# Connect without a database +connect (user4,localhost,mysqltest_1,,*NO-ONE*,0,master.sock); +connection user4; +select "user4"; +--error 1046 +select a from t1; +# The following query is not cached before (different database) +select * from mysqltest.t1,test.t1; +# Cache a query with 'no database' +select a from mysqltest.t1; +select a from mysqltest.t1; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_hits"; +show status like "Qcache_not_cached"; + +# Cleanup + +connection root; +delete from mysql.user where user in ("mysqltest_1","mysqltest_2","mysqltest_3"); +delete from mysql.db where user in ("mysqltest_1","mysqltest_2","mysqltest_3"); +delete from mysql.tables_priv where user in ("mysqltest_1","mysqltest_2","mysqltest_3"); +delete from mysql.columns_priv where user in ("mysqltest_1","mysqltest_2","mysqltest_3"); +flush privileges; +drop table test.t1,mysqltest.t1,mysqltest.t2; +drop database mysqltest; diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index 11408511e99..0c9197febb5 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -243,19 +243,61 @@ select sql_big_result spid,sum(userid) from t1 group by spid desc; explain select sql_big_result score,count(*) from t1 group by score desc; select sql_big_result score,count(*) from t1 group by score desc; drop table t1; + +# +# Compare with hash keys +# + +CREATE TABLE t1 (a char(1)); +INSERT INTO t1 VALUES ('A'),('B'),('A'),('B'),('A'),('B'),(NULL),('a'),('b'),(NULL),('A'),('B'),(NULL); +SELECT a FROM t1 GROUP BY a; +SELECT a,count(*) FROM t1 GROUP BY a; +SELECT a FROM t1 GROUP BY binary a; +SELECT a,count(*) FROM t1 GROUP BY binary a; +SELECT binary a FROM t1 GROUP BY 1; +SELECT binary a,count(*) FROM t1 GROUP BY 1; +# Do the same tests with MyISAM temporary tables +SET SQL_BIG_TABLES=1; +SELECT a FROM t1 GROUP BY a; +SELECT a,count(*) FROM t1 GROUP BY a; +SELECT a FROM t1 GROUP BY binary a; +SELECT a,count(*) FROM t1 GROUP BY binary a; +SELECT binary a FROM t1 GROUP BY 1; +SELECT binary a,count(*) FROM t1 GROUP BY 1; +SET SQL_BIG_TABLES=0; +drop table t1; + +# +# Test of key >= 256 bytes +# + +CREATE TABLE t1 ( + `a` char(193) default NULL, + `b` char(63) default NULL +); +INSERT INTO t1 VALUES ('abc','def'),('hij','klm'); +SELECT CONCAT(a, b) FROM t1 GROUP BY 1; +SELECT CONCAT(a, b),count(*) FROM t1 GROUP BY 1; +SELECT CONCAT(a, b),count(distinct a) FROM t1 GROUP BY 1; +SELECT 1 FROM t1 GROUP BY CONCAT(a, b); +INSERT INTO t1 values ('hij','klm'); +SELECT CONCAT(a, b),count(*) FROM t1 GROUP BY 1; +DROP TABLE t1; drop table if exists t1; + +# +# Test problem with ORDER BY on a SUM() column +# + create table t1 (One int unsigned, Two int unsigned, Three int unsigned, Four int unsigned); -insert into t1 values (1,2,1,4); -insert into t1 values (1,2,2,4); -insert into t1 values (1,2,3,4); -insert into t1 values (1,2,4,4); -insert into t1 values (1,1,1,4); -insert into t1 values (1,1,2,4); -insert into t1 values (1,1,3,4); -insert into t1 values (1,1,4,4); -insert into t1 values (1,3,1,4); -insert into t1 values (1,3,2,4); -insert into t1 values (1,3,3,4); -insert into t1 values (1,3,4,4); +insert into t1 values (1,2,1,4),(1,2,2,4),(1,2,3,4),(1,2,4,4),(1,1,1,4),(1,1,2,4),(1,1,3,4),(1,1,4,4),(1,3,1,4),(1,3,2,4),(1,3,3,4),(1,3,4,4); select One, Two, sum(Four) from t1 group by One,Two; -drop table if exists t1; +drop table t1; + +create table t1 (id integer primary key not null auto_increment, gender char(1)); +insert into t1 values (NULL, 'M'), (NULL, 'F'),(NULL, 'F'),(NULL, 'F'),(NULL, 'M'); +create table t2 (user_id integer not null, date date); +insert into t2 values (1, '2002-06-09'),(2, '2002-06-09'),(1, '2002-06-09'),(3, '2002-06-09'),(4, '2002-06-09'),(4, '2002-06-09'); +select u.gender as gender, count(distinct u.id) as dist_count, (count(distinct u.id)/5*100) as percentage from t1 u, t2 l where l.user_id = u.id group by u.gender; +select u.gender as gender, count(distinct u.id) as dist_count, (count(distinct u.id)/5*100) as percentage from t1 u, t2 l where l.user_id = u.id group by u.gender order by percentage; +drop table t1,t2; diff --git a/mysql-test/t/handler.test b/mysql-test/t/handler.test new file mode 100644 index 00000000000..4f1b11c80ff --- /dev/null +++ b/mysql-test/t/handler.test @@ -0,0 +1,67 @@ +# +# test of HANDLER ... +# + +drop table if exists t1; +create table t1 (a int, b char(10), key a(a), key b(a,b)); +insert into t1 values +(17,"ddd"),(18,"eee"),(19,"fff"),(19,"yyy"), +(14,"aaa"),(15,"bbb"),(16,"ccc"),(16,"xxx"), +(20,"ggg"),(21,"hhh"),(22,"iii"); +handler t1 open as t2; +handler t2 read a first; +handler t2 read a next; +handler t2 read a next; +handler t2 read a prev; +handler t2 read a last; +handler t2 read a prev; +handler t2 read a prev; + +handler t2 read a first; +handler t2 read a prev; + +handler t2 read a last; +handler t2 read a prev; +handler t2 read a next; +handler t2 read a next; + +handler t2 read a=(15); +handler t2 read a=(16); + +!$1070 handler t2 read a=(19,"fff"); + +handler t2 read b=(19,"fff"); +handler t2 read b=(19,"yyy"); +handler t2 read b=(19); + +!$1109 handler t1 read a last; + +handler t2 read a=(11); +handler t2 read a>=(11); + +handler t2 read a=(18); +handler t2 read a>=(18); +handler t2 read a>(18); +handler t2 read a<=(18); +handler t2 read a<(18); + +handler t2 read a first limit 5; +handler t2 read a next limit 3; +handler t2 read a prev limit 10; + +handler t2 read a>=(16) limit 4; +handler t2 read a>=(16) limit 2,2; +handler t2 read a last limit 3; + +handler t2 read a=(19); +handler t2 read a=(19) where b="yyy"; + +handler t2 read first; +handler t2 read next; +alter table t1 type=MyISAM; +handler t2 read next; +!$1064 handler t2 read last; + +handler t2 close; +drop table if exists t1; + diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test index fff5415976c..8dd7606d82b 100644 --- a/mysql-test/t/having.test +++ b/mysql-test/t/having.test @@ -48,3 +48,15 @@ GROUP BY e.id HAVING chr_strand= -1 and end >= 0 AND start <= 999660; drop table t1,t2; + +# +# Test problem with having and MAX() IS NOT NULL +# + +CREATE TABLE t1 (Fld1 int(11) default NULL,Fld2 int(11) default NULL); +INSERT INTO t1 VALUES (1,10),(1,20),(2,NULL),(2,NULL),(3,50); +select Fld1, max(Fld2) as q from t1 group by Fld1 having q is not null; +select Fld1, max(Fld2) from t1 group by Fld1 having max(Fld2) is not null; +select Fld1, max(Fld2) from t1 group by Fld1 having avg(Fld2) is not null; +select Fld1, max(Fld2) from t1 group by Fld1 having std(Fld2) is not null; +drop table t1; diff --git a/mysql-test/t/heap.test b/mysql-test/t/heap.test index abb9e1fd1bc..ef7e3239a96 100644 --- a/mysql-test/t/heap.test +++ b/mysql-test/t/heap.test @@ -19,6 +19,12 @@ drop table t1; create table t1 (a int not null,b int not null, primary key (a)) type=heap comment="testing heaps"; insert into t1 values(1,1),(2,2),(3,3),(4,4); +delete from t1 where a > 0; +select * from t1; +drop table t1; + +create table t1 (a int not null,b int not null, primary key (a)) type=heap comment="testing heaps"; +insert into t1 values(1,1),(2,2),(3,3),(4,4); alter table t1 modify a int not null auto_increment, type=myisam, comment="new myisam table"; #show table status like "t1"; select * from t1; @@ -100,3 +106,35 @@ update t1 set new_col=btn; explain select * from t1 where btn="a"; explain select * from t1 where btn="a" and new_col="a"; drop table t1; + +# +# Test of NULL keys +# + +CREATE TABLE t1 ( + a int default NULL, + b int default NULL, + KEY a (a), + UNIQUE b (b) +) type=heap; +INSERT INTO t1 VALUES (NULL,99),(99,NULL),(1,1),(2,2),(1,3); +SELECT * FROM t1 WHERE a=NULL; +explain SELECT * FROM t1 WHERE a IS NULL; +SELECT * FROM t1 WHERE a<=>NULL; +SELECT * FROM t1 WHERE b=NULL; +explain SELECT * FROM t1 WHERE b IS NULL; +SELECT * FROM t1 WHERE b<=>NULL; + +--error 1062 +INSERT INTO t1 VALUES (1,3); +DROP TABLE t1; + +# +# Test when deleting all rows +# + +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; +SELECT * from t1; +DROP TABLE t1; diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index a962f120d4e..1b7ee99e15a 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -515,6 +515,53 @@ select * from t1; drop table t1; # +# Test DROP DATABASE +# + +create database mysqltest; +create table mysqltest.t1 (a int not null) type= innodb; +insert into mysqltest.t1 values(1); +create table mysqltest.t2 (a int not null) type= myisam; +insert into mysqltest.t2 values(1); +create table mysqltest.t3 (a int not null) type= heap; +insert into mysqltest.t3 values(1); +commit; +drop database mysqltest; +# Don't check error message +--error 12,12 +show tables from mysqltest; + +# +# Test truncate table with and without auto_commit +# + +set autocommit=0; +create table t1 (a int not null) type= innodb; +insert into t1 values(1),(2); +--error 1192 +truncate table t1; +commit; +truncate table t1; +select * from t1; +insert into t1 values(1),(2); +delete from t1; +select * from t1; +commit; +drop table t1; +set autocommit=1; + +create table t1 (a int not null) type= innodb; +insert into t1 values(1),(2); +truncate table t1; +insert into t1 values(1),(2); +select * from t1; +truncate table t1; +insert into t1 values(1),(2); +delete from t1; +select * from t1; +drop table t1; + +# # Test of how ORDER BY works when doing it on the whole table # @@ -531,9 +578,63 @@ explain select a,b,c from t1; drop table t1; # -# Check describe +# Check describe & handler # -create table t1 (testint int not null default 1) type=innodb; +create table t1 (t int not null default 1, key (t)) type=innodb; desc t1; +--error 1031 +handler t1 open t1; +--error 1109 +handler t1 read t first; +--error 1109 +handler t1 close; drop table t1; + +# +# Test of multi-table-delete +# + +CREATE TABLE t1 ( + number bigint(20) NOT NULL default '0', + cname char(15) NOT NULL default '', + carrier_id smallint(6) NOT NULL default '0', + privacy tinyint(4) NOT NULL default '0', + last_mod_date timestamp(14) NOT NULL, + last_mod_id smallint(6) NOT NULL default '0', + last_app_date timestamp(14) NOT NULL, + last_app_id smallint(6) default '-1', + version smallint(6) NOT NULL default '0', + assigned_scps int(11) default '0', + status tinyint(4) default '0' +) TYPE=InnoDB; +INSERT INTO t1 VALUES (4077711111,'SeanWheeler',90,2,20020111112846,500,00000000000000,-1,2,3,1); +INSERT INTO t1 VALUES (9197722223,'berry',90,3,20020111112809,500,20020102114532,501,4,10,0); +INSERT INTO t1 VALUES (650,'San Francisco',0,0,20011227111336,342,00000000000000,-1,1,24,1); +INSERT INTO t1 VALUES (302467,'Sue\'s Subshop',90,3,20020109113241,500,20020102115111,501,7,24,0); +INSERT INTO t1 VALUES (6014911113,'SudzCarwash',520,1,20020102115234,500,20020102115259,501,33,32768,0); +INSERT INTO t1 VALUES (333,'tubs',99,2,20020109113440,501,20020109113440,500,3,10,0); +CREATE TABLE t2 ( + number bigint(20) NOT NULL default '0', + cname char(15) NOT NULL default '', + carrier_id smallint(6) NOT NULL default '0', + privacy tinyint(4) NOT NULL default '0', + last_mod_date timestamp(14) NOT NULL, + last_mod_id smallint(6) NOT NULL default '0', + last_app_date timestamp(14) NOT NULL, + last_app_id smallint(6) default '-1', + version smallint(6) NOT NULL default '0', + assigned_scps int(11) default '0', + status tinyint(4) default '0' +) TYPE=InnoDB; +INSERT INTO t2 VALUES (4077711111,'SeanWheeler',0,2,20020111112853,500,00000000000000,-1,2,3,1); +INSERT INTO t2 VALUES (9197722223,'berry',90,3,20020111112818,500,20020102114532,501,4,10,0); +INSERT INTO t2 VALUES (650,'San Francisco',90,0,20020109113158,342,00000000000000,-1,1,24,1); +INSERT INTO t2 VALUES (333,'tubs',99,2,20020109113453,501,20020109113453,500,3,10,0); +select * from t1; +select * from t2; +delete t1, t2 from t1 left join t2 on t1.number=t2.number where (t1.carrier_id=90 and t1.number=t2.number) or (t2.carrier_id=90 and t1.number=t2.number) or (t1.carrier_id=90 and t2.number is null); +select * from t1; +select * from t2; +select * from t2; +drop table t1,t2; diff --git a/mysql-test/t/innodb_cache-master.opt b/mysql-test/t/innodb_cache-master.opt new file mode 100644 index 00000000000..5f0ebff98f6 --- /dev/null +++ b/mysql-test/t/innodb_cache-master.opt @@ -0,0 +1 @@ +--set-variable=query_cache_size=1M diff --git a/mysql-test/t/innodb_cache.test b/mysql-test/t/innodb_cache.test new file mode 100644 index 00000000000..21d30420eaf --- /dev/null +++ b/mysql-test/t/innodb_cache.test @@ -0,0 +1,50 @@ +-- source include/have_innodb.inc +-- source include/have_query_cache.inc + +# +# Without auto_commit. +# +drop table if exists t1, t2, t3; +flush status; +set autocommit=0; +create table t1 (a int not null) type=innodb; +insert into t1 values (1),(2),(3); +select * from t1; +show status like "Qcache_queries_in_cache"; +drop table t1; +commit; +set autocommit=1; +begin; +create table t1 (a int not null) type=innodb; +insert into t1 values (1),(2),(3); +select * from t1; +show status like "Qcache_queries_in_cache"; +drop table t1; +commit; +create table t1 (a int not null) type=innodb; +create table t2 (a int not null) type=innodb; +create table t3 (a int not null) type=innodb; +insert into t1 values (1),(2); +insert into t2 values (1),(2); +insert into t3 values (1),(2); +select * from t1; +select * from t2; +select * from t3; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_hits"; +begin; +select * from t1; +select * from t2; +select * from t3; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_hits"; +insert into t1 values (3); +insert into t2 values (3); +insert into t1 values (4); +select * from t1; +select * from t2; +select * from t3; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_hits"; +commit; +show status like "Qcache_queries_in_cache";
\ No newline at end of file diff --git a/mysql-test/t/insert.test b/mysql-test/t/insert.test index cf6f41d454d..85798ec57f6 100644 --- a/mysql-test/t/insert.test +++ b/mysql-test/t/insert.test @@ -10,3 +10,41 @@ insert into t1 values (a+3); insert into t1 values (4),(a+5); select * from t1; drop table t1; + +# +# Test of duplicate key values with packed keys +# + +create table t1 (id int not null auto_increment primary key, username varchar(32) not null, unique (username)); +insert into t1 values (0,"mysql"); +insert into t1 values (0,"mysql ab"); +insert into t1 values (0,"mysql a"); +insert into t1 values (0,"r1manic"); +insert into t1 values (0,"r1man"); +drop table t1; + +# +# Test insert syntax +# + +create table t1 (a int not null auto_increment, primary key (a), t timestamp, c char(10) default "hello"); +insert into t1 values (default,default,default), (default,default,default), (4,0,"a"),(default,default,default); +select a,t>0,c from t1; +truncate table t1; +insert into t1 set a=default,t=default,c=default; +insert into t1 set a=default,t=default,c=default; +insert into t1 set a=4,t=0,c="a"; +insert into t1 set a=default,t=default,c=default; +select a,t>0,c from t1; +drop table t1; + +# +# Test of mysqld crash with fully qualified column names +# + +drop database if exists foo; +create database foo; +use foo; +create table t1 (c int); +insert into foo.t1 set foo.t1.c = '1'; +drop database foo; diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test index 17ecb2d5a1f..42f65858d77 100644 --- a/mysql-test/t/insert_select.test +++ b/mysql-test/t/insert_select.test @@ -7,6 +7,64 @@ create table t1 (bandID MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY, payoutID SMALLI insert into t1 (bandID,payoutID) VALUES (1,6),(2,6),(3,4),(4,9),(5,10),(6,1),(7,12),(8,12); create table t2 (payoutID SMALLINT UNSIGNED NOT NULL PRIMARY KEY); insert into t2 (payoutID) SELECT DISTINCT payoutID FROM t1; +--error 1062 insert into t2 (payoutID) SELECT payoutID+10 FROM t1; +insert ignore into t2 (payoutID) SELECT payoutID+10 FROM t1; select * from t2; drop table t1,t2; +# +# bug in bulk insert optimization +# test case by Fournier Jocelyn <joc@presence-pc.com> +# + +DROP TABLE IF EXISTS crash1,crash2; +CREATE TABLE `crash1` ( + `numeropost` bigint(20) unsigned NOT NULL default '0', + `icone` tinyint(4) unsigned NOT NULL default '0', + `numreponse` bigint(20) unsigned NOT NULL auto_increment, + `contenu` text NOT NULL, + `pseudo` varchar(50) NOT NULL default '', + `date` datetime NOT NULL default '0000-00-00 00:00:00', + `ip` bigint(11) NOT NULL default '0', + `signature` tinyint(1) unsigned NOT NULL default '0', + PRIMARY KEY (`numeropost`,`numreponse`) + ,KEY `ip` (`ip`), + KEY `date` (`date`), + KEY `pseudo` (`pseudo`), + KEY `numreponse` (`numreponse`) +) TYPE=MyISAM; + +CREATE TABLE `crash2` ( + `numeropost` bigint(20) unsigned NOT NULL default '0', + `icone` tinyint(4) unsigned NOT NULL default '0', + `numreponse` bigint(20) unsigned NOT NULL auto_increment, + `contenu` text NOT NULL, + `pseudo` varchar(50) NOT NULL default '', + `date` datetime NOT NULL default '0000-00-00 00:00:00', + `ip` bigint(11) NOT NULL default '0', + `signature` tinyint(1) unsigned NOT NULL default '0', + PRIMARY KEY (`numeropost`,`numreponse`), + KEY `ip` (`ip`), + KEY `date` (`date`), + KEY `pseudo` (`pseudo`), + KEY `numreponse` (`numreponse`) +) TYPE=MyISAM; + +INSERT INTO crash2 +(numeropost,icone,numreponse,contenu,pseudo,date,ip,signature) VALUES +(9,1,56,'test','joce','2001-07-25 13:50:53' +,3649052399,0); + + +INSERT INTO crash1 (numeropost,icone,contenu,pseudo,date,signature,ip) +SELECT 1618,icone,contenu,pseudo,date,signature,ip FROM crash2 +WHERE numeropost=9 ORDER BY numreponse ASC; + +show variables like '%bulk%'; + +INSERT INTO crash1 (numeropost,icone,contenu,pseudo,date,signature,ip) +SELECT 1718,icone,contenu,pseudo,date,signature,ip FROM crash2 +WHERE numeropost=9 ORDER BY numreponse ASC; + +DROP TABLE IF EXISTS crash1,crash2; + diff --git a/mysql-test/t/isam.test b/mysql-test/t/isam.test index 1cf068b42ba..c7308c4b999 100644 --- a/mysql-test/t/isam.test +++ b/mysql-test/t/isam.test @@ -1,19 +1,66 @@ +-- source include/have_isam.inc + +drop table if exists t1,t2; + # # Test possible problem with rows that are about 65535 bytes long # -create table t1 (a tinyint not null auto_increment, b blob not null, primary key (a)); +create table t1 (a tinyint not null auto_increment, b blob not null, primary key (a)) type=isam; let $1=100; +disable_query_log; while ($1) { eval insert into t1 (b) values(repeat(char(65+$1),65540-$1)); dec $1; } -check table t1; -repair table t1; +enable_query_log; delete from t1 where (a & 1); -check table t1; -repair table t1; -check table t1; +select sum(length(b)) from t1; +drop table t1; + +# +# Test of auto_increment; The test for BDB tables is in bdb.test +# + +create table t1 (a int not null auto_increment,b int, primary key (a)) type=isam; +insert into t1 values (1,1),(NULL,2),(3,3),(NULL,4); +delete from t1 where a=4 or a=2; +insert into t1 values (NULL,4),(NULL,5),(6,6); +select * from t1; +delete from t1 where a=6; +#show table status like "t1"; +replace t1 values (3,1); +replace t1 values (3,3); +ALTER TABLE t1 add c int; +insert into t1 values (NULL,6,6); +select * from t1; drop table t1; + +# +# Test of some CREATE TABLE's that should fail +# +!$1121 create table t1 (a int,b text, index(a)) type=isam; +!$1073 create table t1 (a int,b text, index(b)) type=isam; +!$1075 create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ord,ordid)) type=isam; +!$1121 create table t1 (ordid int(8), unique (ordid)) type=isam; +drop table if exists t1; + +# +# Test of some show commands +# + +create table t1 (a int not null primary key, b int not null,c int not null, key(b,c)); +insert into t1 values (1,2,2),(2,2,3),(3,2,4),(4,2,4); +create table t2 type=isam select * from t1; +optimize table t1; +check table t1,t2; +repair table t1,t2; +check table t2,t1; +lock tables t1 write; +check table t2,t1; +show columns from t1; +show full columns from t1; +show index from t1; +drop table t1,t2; diff --git a/mysql-test/t/isolation.test b/mysql-test/t/isolation.test deleted file mode 100644 index d9750ecb351..00000000000 --- a/mysql-test/t/isolation.test +++ /dev/null @@ -1,208 +0,0 @@ -source include/have_gemini.inc -source include/master-slave.inc; -connection master; -drop table if exists t1; -create table t1 (f1 char(20) not null) type = gemini; -set autocommit=0; - -insert into t1 (f1) values ("test1"), ("bar"); -connection master1; -!$1030 select * from t1; -connection master; -commit; - -drop table if exists t1; -create table t1 (f1 char(20) not null) type = gemini; -insert into t1 (f1) values ("test2"), ("bar"); -connection master1; -set transaction isolation level serializable; -!$-1217 select * from t1; -connection master; -commit; - -drop table if exists t1; -create table t1 (f1 char(20) not null) type = gemini; -insert into t1 (f1) values ("test3"), ("bar"); -connection master1; -set transaction isolation level read uncommitted; -select * from t1; -connection master; -commit; - -drop table if exists t1; -create table t1 (f1 char(20) not null) type = gemini; -connection master1; -set transaction isolation level read committed; -select * from t1; -connection master; -commit; - -drop table if exists t1; -create table t1 (f1 char(20) not null) type = gemini; -insert into t1 (f1) values ("test4"), ("bar"); -connection master1; -set transaction isolation level repeatable read; -select * from t1; -connection master; -commit; - -drop table if exists t1; -create table t1 (f1 char(20) not null) type = gemini; -set transaction isolation level serializable; -insert into t1 (f1) values ("test5"), ("bar"); -connection master1; -set transaction isolation level serializable; -select * from t1; -connection master; -commit; - -drop table if exists t1; -create table t1 (f1 char(20) not null) type = gemini; -set transaction isolation level serializable; -insert into t1 (f1) values ("test6"), ("bar"); -connection master1; -set transaction isolation level read uncommitted; -select * from t1; -connection master; -commit; - -drop table if exists t1; -create table t1 (f1 char(20) not null) type = gemini; -set transaction isolation level serializable; -insert into t1 (f1) values ("test7"), ("bar"); -connection master1; -set transaction isolation level read committed; -select * from t1; -connection master; -commit; - -drop table if exists t1; -create table t1 (f1 char(20) not null) type = gemini; -set transaction isolation level serializable; -insert into t1 (f1) values ("test8"), ("bar"); -connection master1; -set transaction isolation level repeatable read; -select * from t1; -connection master; -commit; - -drop table if exists t1; -create table t1 (f1 char(20) not null) type = gemini; -set transaction isolation level read uncommitted; -insert into t1 (f1) values ("test9"), ("bar"); -connection master1; -set transaction isolation level serializable; -select * from t1; -connection master; -commit; - -drop table if exists t1; -create table t1 (f1 char(20) not null) type = gemini; -set transaction isolation level read uncommitted; -insert into t1 (f1) values ("test10"), ("bar"); -connection master1; -set transaction isolation level read uncommitted; -select * from t1; -connection master; -commit; - -drop table if exists t1; -create table t1 (f1 char(20) not null) type = gemini; -set transaction isolation level read uncommitted; -insert into t1 (f1) values ("test11"), ("bar"); -connection master1; -set transaction isolation level read committed; -select * from t1; -connection master; -commit; - -drop table if exists t1; -create table t1 (f1 char(20) not null) type = gemini; -set transaction isolation level read uncommitted; -insert into t1 (f1) values ("test12"), ("bar"); -connection master1; -set transaction isolation level repeatable read; -select * from t1; -connection master; -commit; - -drop table if exists t1; -create table t1 (f1 char(20) not null) type = gemini; -set transaction isolation level read committed; -insert into t1 (f1) values ("test13"), ("bar"); -connection master1; -set transaction isolation level serializable; -select * from t1; -connection master; -commit; - -drop table if exists t1; -create table t1 (f1 char(20) not null) type = gemini; -set transaction isolation level read committed; -insert into t1 (f1) values ("test14"), ("bar"); -connection master1; -set transaction isolation level read uncommitted; -select * from t1; -connection master; -commit; - -drop table if exists t1; -create table t1 (f1 char(20) not null) type = gemini; -set transaction isolation level read committed; -insert into t1 (f1) values ("test15"), ("bar"); -connection master1; -set transaction isolation level read committed; -select * from t1; -connection master; -commit; - -drop table if exists t1; -create table t1 (f1 char(20) not null) type = gemini; -set transaction isolation level read committed; -insert into t1 (f1) values ("test16"), ("bar"); -connection master1; -set transaction isolation level repeatable read; -select * from t1; -connection master; -commit; - -drop table if exists t1; -create table t1 (f1 char(20) not null) type = gemini; -set transaction isolation level repeatable read; -insert into t1 (f1) values ("test17"), ("bar"); -connection master1; -set transaction isolation level serializable; -select * from t1; -connection master; -commit; - -drop table if exists t1; -create table t1 (f1 char(20) not null) type = gemini; -set transaction isolation level repeatable read; -insert into t1 (f1) values ("test18"), ("bar"); -connection master1; -set transaction isolation level read uncommitted; -select * from t1; -connection master; -commit; - -drop table if exists t1; -create table t1 (f1 char(20) not null) type = gemini; -set transaction isolation level repeatable read; -insert into t1 (f1) values ("test19"), ("bar"); -connection master1; -set transaction isolation level read committed; -select * from t1; -connection master; -commit; - -drop table if exists t1; -create table t1 (f1 char(20) not null) type = gemini; -set transaction isolation level repeatable read; -insert into t1 (f1) values ("test20"), ("bar"); -connection master1; -set transaction isolation level repeatable read; -select * from t1; -connection master; -commit; -drop table t1; diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index d31db5c4837..18006e8fd22 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -1,7 +1,6 @@ # # This failed for lia Perminov # - drop table if exists t1,t2,t3; create table t1 (id int primary key); @@ -87,6 +86,7 @@ drop table t1, t2; create table t1 (a int primary key); insert into t1 values(1),(2); select t1.a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a); +--replace_result "31 tables" "XX tables" "63 tables" "XX tables" --error 1116 select t1.a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a) left join t1 as t32 using (a) left join t1 as t33 using (a) left join t1 as t34 using (a) left join t1 as t35 using (a) left join t1 as t36 using (a) left join t1 as t37 using (a) left join t1 as t38 using (a) left join t1 as t39 using (a) left join t1 as t40 using (a) left join t1 as t41 using (a) left join t1 as t42 using (a) left join t1 as t43 using (a) left join t1 as t44 using (a) left join t1 as t45 using (a) left join t1 as t46 using (a) left join t1 as t47 using (a) left join t1 as t48 using (a) left join t1 as t49 using (a) left join t1 as t50 using (a) left join t1 as t51 using (a) left join t1 as t52 using (a) left join t1 as t53 using (a) left join t1 as t54 using (a) left join t1 as t55 using (a) left join t1 as t56 using (a) left join t1 as t57 using (a) left join t1 as t58 using (a) left join t1 as t59 using (a) left join t1 as t60 using (a) left join t1 as t61 using (a) left join t1 as t62 using (a) left join t1 as t63 using (a) left join t1 as t64 using (a) left join t1 as t65 using (a); drop table t1; diff --git a/mysql-test/t/key.test b/mysql-test/t/key.test index a8417fad876..1996c793880 100644 --- a/mysql-test/t/key.test +++ b/mysql-test/t/key.test @@ -160,3 +160,31 @@ SELECT * FROM t1 WHERE a='a' AND b=2; SELECT * FROM t1 WHERE a='a' AND b in (2); SELECT * FROM t1 WHERE a='a' AND b in (1,2); drop table t1; + +# +# Test of create key order +# + +create table t1 (a int not null unique, b int unique, c int, d int not null primary key, key(c), e int not null unique); +show keys from t1; +drop table t1; + +# +# Problem with UNIQUE() with NULL parts and auto increment +# + +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c CHAR(10) NOT NULL,i INT NOT NULL AUTO_INCREMENT, +UNIQUE (c,i)); +INSERT INTO t1 (c) VALUES (NULL),(NULL); +SELECT * FROM t1; +INSERT INTO t1 (c) VALUES ('a'),('a'); +SELECT * FROM t1; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c CHAR(10) NULL, i INT NOT NULL AUTO_INCREMENT, +UNIQUE (c,i)); +INSERT INTO t1 (c) VALUES (NULL),(NULL); +SELECT * FROM t1; +INSERT INTO t1 (c) VALUES ('a'),('a'); +SELECT * FROM t1; +drop table t1; diff --git a/mysql-test/t/keywords.test b/mysql-test/t/keywords.test index b9a1f34c715..3bd757aa069 100644 --- a/mysql-test/t/keywords.test +++ b/mysql-test/t/keywords.test @@ -8,3 +8,7 @@ insert into t1 values ("12:22:22","97:02:03","1997-01-02"); select * from t1; select t1.time+0,t1.date+0,t1.timestamp+0,concat(date," ",time) from t1; drop table t1; +create table events(binlog int); +insert into events values(1); +select events.binlog from events; +drop table events; diff --git a/mysql-test/t/kill.test b/mysql-test/t/kill.test index be6860a3cc3..6bcc43ac916 100644 --- a/mysql-test/t/kill.test +++ b/mysql-test/t/kill.test @@ -1,3 +1,11 @@ +# This test doesn't work with the embedded version as this code +# assumes that one query is running while we are doing queries on +# a second connection. +# This would work if mysqltest run would be threaded and handle each +# connection in a separate thread. +# +-- source include/not_embedded.inc + connect (con1, localhost, root,,); connect (con2, localhost, root,,); diff --git a/mysql-test/t/limit.test b/mysql-test/t/limit.test index 112761abd87..d4f6ce186cf 100644 --- a/mysql-test/t/limit.test +++ b/mysql-test/t/limit.test @@ -28,3 +28,5 @@ delete from t1 limit 0; update t1 set i=3 limit 0; select * from t1; drop table t1; + +select 0 limit 0; diff --git a/mysql-test/t/lock.test b/mysql-test/t/lock.test index 77354e63252..385713174d2 100644 --- a/mysql-test/t/lock.test +++ b/mysql-test/t/lock.test @@ -53,45 +53,3 @@ lock tables t1 write; check table t2; unlock tables; drop table t1,t2; - -#test to see if select will get the lock ahead of low priority update -connect (locker,localhost,root,,); -connect (reader,localhost,root,,); -connect (writer,localhost,root,,); - -connection locker; -create table t1(n int); -insert into t1 values (1); -lock tables t1 write; -connection writer; -send update low_priority t1 set n = 4; -connection reader; ---sleep 2 -send select n from t1; -connection locker; ---sleep 2 -unlock tables; -connection writer; -reap; -connection reader; -reap; -drop table t1; - -connection locker; -create table t1(n int); -insert into t1 values (1); -lock tables t1 read; -connection writer; -send update low_priority t1 set n = 4; -connection reader; ---sleep 2 -send select n from t1; -connection locker; ---sleep 2 -unlock tables; -connection writer; -reap; -connection reader; -reap; -drop table t1; - diff --git a/mysql-test/t/lock_multi.test b/mysql-test/t/lock_multi.test new file mode 100644 index 00000000000..53e9fd3393c --- /dev/null +++ b/mysql-test/t/lock_multi.test @@ -0,0 +1,49 @@ +# This test doesn't work with the embedded version as this code +# assumes that one query is running while we are doing queries on +# a second connection. +# This would work if mysqltest run would be threaded and handle each +# connection in a separate thread. +# +-- source include/not_embedded.inc + +#test to see if select will get the lock ahead of low priority update + +connect (locker,localhost,root,,); +connect (reader,localhost,root,,); +connect (writer,localhost,root,,); + +connection locker; +create table t1(n int); +insert into t1 values (1); +lock tables t1 write; +connection writer; +send update low_priority t1 set n = 4; +connection reader; +--sleep 2 +send select n from t1; +connection locker; +--sleep 2 +unlock tables; +connection writer; +reap; +connection reader; +reap; +drop table t1; + +connection locker; +create table t1(n int); +insert into t1 values (1); +lock tables t1 read; +connection writer; +send update low_priority t1 set n = 4; +connection reader; +--sleep 2 +send select n from t1; +connection locker; +--sleep 2 +unlock tables; +connection writer; +reap; +connection reader; +reap; +drop table t1; diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index 238dd599664..86e787db0a3 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -2,7 +2,7 @@ # test of MERGE TABLES # -drop table if exists t1,t2,t3; +drop table if exists t1,t2,t3,t4,t5,t6; create table t1 (a int not null primary key auto_increment, message char(20)); create table t2 (a int not null primary key auto_increment, message char(20)); INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1"); @@ -22,7 +22,7 @@ insert into t2 select NULL,message from t1; insert into t1 select NULL,message from t2; insert into t2 select NULL,message from t1; insert into t1 select NULL,message from t2; -create table t3 (a int not null, b char(20), key(a)) type=MERGE UNION=(t1,t2); +create table t3 (a int not null, b char(20), key(a)) type=MERGE UNION=(test.t1,test.t2); explain select * from t3 where a < 10; explain select * from t3 where a > 10 and a < 20; select * from t3 where a = 10; @@ -31,6 +31,14 @@ select * from t3 where a > 10 and a < 20; explain select a from t3 order by a desc limit 10; select a from t3 order by a desc limit 10; select a from t3 order by a desc limit 300,10; +delete from t3 where a=3; +select * from t3 where a < 10; +delete from t3 where a >= 6 and a <= 8; +select * from t3 where a < 10; +update t3 set a=3 where a=9; +select * from t3 where a < 10; +update t3 set a=6 where a=7; +select * from t3 where a < 10; show create table t3; # The following should give errors @@ -114,3 +122,66 @@ insert into t2 values (1,1),(2,2),(0,0),(4,4),(5,5),(6,6); flush tables; select * from t3 where a=1 order by b limit 2; drop table t3,t1,t2; + +# +# [phi] testing INSERT_METHOD stuff +# + +drop table if exists t6, t5, t4, t3, t2, t1; +# first testing of common stuff with new parameters +create table t1 (a int not null, b int not null auto_increment, primary key(a,b)); +create table t2 (a int not null, b int not null auto_increment, primary key(a,b)); +create table t3 (a int not null, b int not null, key(a,b)) UNION=(t1,t2) INSERT_METHOD=NO; +create table t4 (a int not null, b int not null, key(a,b)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=NO; +create table t5 (a int not null, b int not null auto_increment, primary key(a,b)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=FIRST; +create table t6 (a int not null, b int not null auto_increment, primary key(a,b)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST; +show create table t3; +show create table t4; +show create table t5; +show create table t6; +insert into t1 values (1,NULL),(1,NULL),(1,NULL),(1,NULL); +insert into t2 values (2,NULL),(2,NULL),(2,NULL),(2,NULL); +select * from t3 order by b,a limit 3; +select * from t4 order by b,a limit 3; +select * from t5 order by b,a limit 3,3; +select * from t6 order by b,a limit 6,3; +# now testing inserts and where the data gets written +insert into t5 values (5,1),(5,2); +insert into t6 values (6,1),(6,2); +select * from t1 order by a,b; +select * from t2 order by a,b; +select * from t4 order by a,b; +# preperation for next test +insert into t3 values (3,1),(3,2),(3,3),(3,4); +select * from t3 order by a,b; +# now testing whether options are kept by alter table +alter table t4 UNION=(t1,t2,t3); +show create table t4; +select * from t4 order by a,b; +# testing switching off insert method and inserts again +alter table t4 INSERT_METHOD=FIRST; +show create table t4; +insert into t4 values (4,1),(4,2); +select * from t1 order by a,b; +select * from t2 order by a,b; +select * from t3 order by a,b; +select * from t4 order by a,b; +select * from t5 order by a,b; +# auto_increment +select 1; +insert into t5 values (1,NULL),(5,NULL); +insert into t6 values (2,NULL),(6,NULL); +select * from t1 order by a,b; +select * from t2 order by a,b; +select * from t5 order by a,b; +select * from t6 order by a,b; +drop table if exists t6, t5, t4, t3, t2, t1; + +CREATE TABLE t1 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', PRIMARY KEY (a,b)) TYPE=MyISAM; +INSERT INTO t1 VALUES (1,1), (2,1); +CREATE TABLE t2 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', PRIMARY KEY (a,b)) TYPE=MyISAM; +INSERT INTO t2 VALUES (1,2), (2,2); +CREATE TABLE t ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', KEY a (a,b)) TYPE=MRG_MyISAM UNION=(t1,t2); +select max(b) from t where a = 2; +select max(b) from t1 where a = 2; +drop table if exists t,t1,t2; diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test new file mode 100644 index 00000000000..dc4d444d463 --- /dev/null +++ b/mysql-test/t/multi_update.test @@ -0,0 +1,82 @@ +# +# Only run the test if we are using --big-test, because this test takes a +# long time +# +#-- require r/big_test.require +#eval select $BIG_TEST as using_big_test; + +drop table if exists t1,t2,t3; +create table t1(id1 int not null auto_increment primary key, t char(12)); +create table t2(id2 int not null, t char(12)); +create table t3(id3 int not null, t char(12), index(id3)); +disable_query_log; +let $1 = 100; +while ($1) + { + let $2 = 5; + eval insert into t1(t) values ('$1'); + while ($2) + { + eval insert into t2(id2,t) values ($1,'$2'); + let $3 = 10; + while ($3) + { + eval insert into t3(id3,t) values ($1,'$2'); + dec $3; + } + dec $2; + } + dec $1; + } +enable_query_log; + +select count(*) from t1 where id1 > 95; +select count(*) from t2 where id2 > 95; +select count(*) from t3 where id3 > 95; + +update t1,t2,t3 set t1.t="aaa", t2.t="bbb", t3.t="cc" where t1.id1 = t2.id2 and t2.id2 = t3.id3 and t1.id1 > 90; +select count(*) from t1 where t = "aaa"; +select count(*) from t1 where id1 > 90; +select count(*) from t2 where t = "bbb"; +select count(*) from t2 where id2 > 90; +select count(*) from t3 where t = "cc"; +select count(*) from t3 where id3 > 90; +delete t1.*, t2.*, t3.* from t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3 and t1.id1 > 95; + +check table t1, t2, t3; + +select count(*) from t1 where id1 > 95; +select count(*) from t2 where id2 > 95; +select count(*) from t3 where id3 > 95; + +delete t1, t2, t3 from t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3 and t1.id1 > 5; +select count(*) from t1 where id1 > 5; +select count(*) from t2 where id2 > 5; +select count(*) from t3 where id3 > 5; + +delete from t1, t2, t3 using t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3 and t1.id1 > 0; + +# These queries will force a scan of the table +select count(*) from t1 where id1; +select count(*) from t2 where id2; +select count(*) from t3 where id3; + +drop table t1,t2,t3; +create table t1(id1 int not null primary key, t varchar(100)) pack_keys = 1; +create table t2(id2 int not null, t varchar(100), index(id2)) pack_keys = 1; +disable_query_log; +let $1 = 1000; +while ($1) + { + let $2 = 5; + eval insert into t1 values ($1,'aaaaaaaaaaaaaaaaaaaa'); + while ($2) + { + eval insert into t2(id2,t) values ($1,'bbbbbbbbbbbbbbbbb'); + dec $2; + } + dec $1; + } +enable_query_log; +delete t1 from t1,t2 where t1.id1 = t2.id2 and t1.id1 > 500; +drop table t1,t2; diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index eb1d9a01020..c607cb4d63e 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -24,11 +24,13 @@ drop table t1; create table t1 (a tinyint not null auto_increment, b blob not null, primary key (a)); let $1=100; +disable_query_log; while ($1) { eval insert into t1 (b) values(repeat(char(65+$1),65550-$1)); dec $1; } +enable_query_log; check table t1; repair table t1; delete from t1 where (a & 1); @@ -64,3 +66,30 @@ explain select a,b from t1 order by b; explain select a,b from t1; explain select a,b,c from t1; drop table t1; + +# +# Test of REPAIR that once failed +# +CREATE TABLE `t1` ( + `post_id` mediumint(8) unsigned NOT NULL auto_increment, + `topic_id` mediumint(8) unsigned NOT NULL default '0', + `post_time` datetime NOT NULL default '0000-00-00 00:00:00', + `post_text` text NOT NULL, + `icon_url` varchar(10) NOT NULL default '', + `sign` tinyint(1) unsigned NOT NULL default '0', + `post_edit` varchar(150) NOT NULL default '', + `poster_login` varchar(35) NOT NULL default '', + `ip` varchar(15) NOT NULL default '', + PRIMARY KEY (`post_id`), + KEY `post_time` (`post_time`), + KEY `ip` (`ip`), + KEY `poster_login` (`poster_login`), + KEY `topic_id` (`topic_id`), + FULLTEXT KEY `post_text` (`post_text`) +) TYPE=MyISAM; + +INSERT INTO t1 (post_text) VALUES ('ceci est un test'),('ceci est un test'),('ceci est un test'),('ceci est un test'),('ceci est un test'); + +REPAIR TABLE t1; +CHECK TABLE t1; +drop table t1; diff --git a/mysql-test/t/null.test b/mysql-test/t/null.test index a010ab38e07..8bd9e806118 100644 --- a/mysql-test/t/null.test +++ b/mysql-test/t/null.test @@ -25,7 +25,6 @@ drop table t1; # Test problem med index on NULL columns and testing with =NULL; # -DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( indexed_field int default NULL, KEY indexed_field (indexed_field) diff --git a/mysql-test/t/olap.test b/mysql-test/t/olap.test new file mode 100644 index 00000000000..8fe547db615 --- /dev/null +++ b/mysql-test/t/olap.test @@ -0,0 +1,27 @@ +drop table if exists sales; +create table sales ( product varchar(32), country varchar(32), year int, profit int); +insert into sales values ( 'Computer', 'India',2000, 1200), +( 'TV', 'United States', 1999, 150), +( 'Calculator', 'United States', 1999,50), +( 'Computer', 'United States', 1999,1500), +( 'Computer', 'United States', 2000,1500), +( 'TV', 'United States', 2000, 150), +( 'TV', 'India', 2000, 100), +( 'TV', 'India', 2000, 100), +( 'Calculator', 'United States', 2000,75), +( 'Calculator', 'India', 2000,75), +( 'TV', 'India', 1999, 100), +( 'Computer', 'India', 1999,1200), +( 'Computer', 'United States', 2000,1500), +( 'Calculator', 'United States', 2000,75); +--error 1235 +select product, country , year, sum(profit) from sales group by product, country, year with cube; +--error 1235 +explain select product, country , year, sum(profit) from sales group by product, country, year with cube; +--error 1235 +select product, country , year, sum(profit) from sales group by product, country, year with rollup; +--error 1235 +explain select product, country , year, sum(profit) from sales group by product, country, year with rollup; +--error 1235 +select product, country , year, sum(profit) from sales group by product, country, year with cube union all select product, country , year, sum(profit) from sales group by product, country, year with rollup; +drop table sales; diff --git a/mysql-test/t/openssl_1.test b/mysql-test/t/openssl_1.test new file mode 100644 index 00000000000..8dfd0d8c2f9 --- /dev/null +++ b/mysql-test/t/openssl_1.test @@ -0,0 +1,6 @@ +# We test openssl. Result set is optimized to be compiled with --with-openssl but +# SSL is swithced off in some reason +-- source include/have_openssl_2.inc + +SHOW STATUS LIKE 'SSL%'; + diff --git a/mysql-test/t/openssl_2.test b/mysql-test/t/openssl_2.test new file mode 100644 index 00000000000..12f1240f3af --- /dev/null +++ b/mysql-test/t/openssl_2.test @@ -0,0 +1,5 @@ +# We want to test everything with SSL turned on. +-- source include/have_openssl_2.inc + +SHOW STATUS LIKE 'SSL%'; + diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index 16094206745..baa3fe67f0b 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -206,8 +206,53 @@ ORDER by lastchange_datum DESC LIMIT 2; drop table t1; # -# Test optimizing bug with EQ_REF tables, where some ORDER BY parts where -# wrongly removed. +# Test optimization of ORDER BY DESC +# + +create table t1 (a int not null, b int, c varchar(10), key (a, b, c)); +insert into t1 values (1, NULL, NULL), (1, NULL, 'b'), (1, 1, NULL), (1, 1, 'b'), (1, 1, 'b'), (2, 1, 'a'), (2, 1, 'b'), (2, 2, 'a'), (2, 2, 'b'), (2, 3, 'c'),(1,3,'b'); + +explain select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc; +select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc; +explain select * from t1 where a >= 1 and a < 3 order by a desc; +select * from t1 where a >= 1 and a < 3 order by a desc; +explain select * from t1 where a = 1 order by a desc, b desc; +select * from t1 where a = 1 order by a desc, b desc; +explain select * from t1 where a = 1 and b is null order by a desc, b desc; +select * from t1 where a = 1 and b is null order by a desc, b desc; +explain select * from t1 where a >= 1 and a < 3 and b >0 order by a desc,b desc; +explain select * from t1 where a = 2 and b >0 order by a desc,b desc; +explain select * from t1 where a = 2 and b is null order by a desc,b desc; +explain select * from t1 where a = 2 and (b is null or b > 0) order by a +desc,b desc; +explain select * from t1 where a = 2 and b > 0 order by a desc,b desc; +explain select * from t1 where a = 2 and b < 2 order by a desc,b desc; + +# +# Test things when we don't have NULL keys +# + +alter table t1 modify b int not null, modify c varchar(10) not null; +explain select * from t1 order by a, b, c; +select * from t1 order by a, b, c; +explain select * from t1 order by a desc, b desc, c desc; +select * from t1 order by a desc, b desc, c desc; +# test multiple ranges, NO_MAX_RANGE and EQ_RANGE +explain select * from t1 where (a = 1 and b = 1 and c = 'b') or (a > 2) order by a desc; +select * from t1 where (a = 1 and b = 1 and c = 'b') or (a > 2) order by a desc; +# test NEAR_MAX, NO_MIN_RANGE +explain select * from t1 where a < 2 and b <= 1 order by a desc, b desc; +select * from t1 where a < 2 and b <= 1 order by a desc, b desc; +select count(*) from t1 where a < 5 and b > 0; +select * from t1 where a < 5 and b > 0 order by a desc,b desc; +# test HA_READ_AFTER_KEY (at the end of the file), NEAR_MIN +explain select * from t1 where a between 1 and 3 and b <= 1 order by a desc, b desc; +select * from t1 where a between 1 and 3 and b <= 1 order by a desc, b desc; +# test HA_READ_AFTER_KEY (in the middle of the file) +explain select * from t1 where a between 0 and 1 order by a desc, b desc; +select * from t1 where a between 0 and 1 order by a desc, b desc; +drop table t1; + CREATE TABLE t1 ( gid int(10) unsigned NOT NULL auto_increment, diff --git a/mysql-test/t/order_fill_sortbuf-master.opt b/mysql-test/t/order_fill_sortbuf-master.opt new file mode 100644 index 00000000000..116494d4588 --- /dev/null +++ b/mysql-test/t/order_fill_sortbuf-master.opt @@ -0,0 +1 @@ +--set-variable=sort_buffer=0 diff --git a/mysql-test/t/order_fill_sortbuf.test b/mysql-test/t/order_fill_sortbuf.test new file mode 100644 index 00000000000..6419f2a93df --- /dev/null +++ b/mysql-test/t/order_fill_sortbuf.test @@ -0,0 +1,21 @@ +# +# This test does a create-select with ORDER BY, where there is so many +# rows MySQL needs to use a merge during the sort phase. +# + +drop table if exists t1,t2; +CREATE TABLE `t1` ( + `id` int(11) NOT NULL default '0', + `id2` int(11) NOT NULL default '0', + `id3` int(11) NOT NULL default '0'); +let $1=4000; +disable_query_log; +while ($1) + { + eval insert into t1 (id,id2,id3) values ($1,$1,$1); + dec $1; + } +enable_query_log; +create table t2 select id2 from t1 order by id3; +select count(*) from t2; +drop table t1,t2; diff --git a/mysql-test/t/query_cache-master.opt b/mysql-test/t/query_cache-master.opt new file mode 100644 index 00000000000..cfdce628e74 --- /dev/null +++ b/mysql-test/t/query_cache-master.opt @@ -0,0 +1 @@ +--set-variable=query_cache_size=1355776 diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test new file mode 100644 index 00000000000..e7633f3bae0 --- /dev/null +++ b/mysql-test/t/query_cache.test @@ -0,0 +1,338 @@ +-- source include/have_query_cache.inc + +# +# Tests with query cache +# + +# Reset query cache variables. + +flush query cache; # This crashed in some versions +flush query cache; # This crashed in some versions +reset query cache; +flush status; +drop table if exists t1,t2,t3,t11,t21, mysqltest.t1; + +# +# First simple test +# + +create table t1 (a int not null); +insert into t1 values (1),(2),(3); +select * from t1; +select * from t1; +select sql_no_cache * from t1; +select length(now()) from t1; + +# Only check the variables that are independent of the machine and startup +# options + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; + +drop table t1; + +show status like "Qcache_queries_in_cache"; + +# +# MERGE TABLES with INSERT/UPDATE and DELETE +# +create table t1 (a int not null); +insert into t1 values (1),(2),(3); +create table t2 (a int not null); +insert into t2 values (4),(5),(6); +create table t3 (a int not null) type=MERGE UNION=(t1,t2) INSERT_METHOD=FIRST; +# insert +select * from t3; +select * from t3; +show status like "Qcache_hits"; +show status like "Qcache_queries_in_cache"; +insert into t2 values (7); +show status like "Qcache_queries_in_cache"; +select * from t1; +select * from t1; +show status like "Qcache_hits"; +show status like "Qcache_queries_in_cache"; +insert into t3 values (8); +show status like "Qcache_queries_in_cache"; +# update +select * from t3; +show status like "Qcache_queries_in_cache"; +update t2 set a=9 where a=7; +show status like "Qcache_queries_in_cache"; +select * from t1; +show status like "Qcache_queries_in_cache"; +update t3 set a=10 where a=1; +show status like "Qcache_queries_in_cache"; +#delete +select * from t3; +show status like "Qcache_queries_in_cache"; +delete from t2 where a=9; +show status like "Qcache_queries_in_cache"; +select * from t1; +show status like "Qcache_queries_in_cache"; +delete from t3 where a=10; +show status like "Qcache_queries_in_cache"; +drop table t1, t2, t3; +# +# FLUSH QUERY CACHE +# +create table t1 (a int not null); +insert into t1 values (1),(2),(3); +create table t2 (a int not null); +insert into t2 values (1),(2),(3); +select * from t1; +select * from t2; +insert into t1 values (4); +show status like "Qcache_free_blocks"; +flush query cache; +show status like "Qcache_free_blocks"; +drop table t1, t2; +# With join results... +create table t1 (a text not null); +create table t11 (a text not null); +create table t2 (a text not null); +create table t21 (a text not null); +create table t3 (a text not null); +insert into t1 values("1111111111111111111111111111111111111111111111111111"); +insert into t2 select * from t1; +insert into t1 select * from t2; +insert into t2 select * from t1; +insert into t1 select * from t2; +insert into t2 select * from t1; +insert into t1 select * from t2; +insert into t2 select * from t1; +insert into t1 select * from t2; +insert into t2 select * from t1; +insert into t1 select * from t2; +insert into t2 select * from t1; +insert into t1 select * from t2; +insert into t2 select * from t1; +insert into t1 select * from t2; +insert into t2 select * from t1; +# t11 and t21 must be over 4Kb (QUERY_CACHE_MIN_RESULT_DATA_SIZE) +insert into t11 select * from t1; +insert into t21 select * from t1; +insert into t1 select * from t2; +insert into t2 select * from t1; +insert into t1 select * from t2; +#results of t3 must be > 0.5Mb +insert into t3 select * from t1; +insert into t3 select * from t2; +insert into t3 select * from t1; +disable_result_log; +select * from t11; +select * from t21; +enable_result_log; +show status like "Qcache_total_blocks"; +show status like "Qcache_free_blocks"; +disable_result_log; +insert into t11 values(""); +select * from t3; +enable_result_log; +show status like "Qcache_total_blocks"; +show status like "Qcache_free_blocks"; +flush query cache; +show status like "Qcache_total_blocks"; +show status like "Qcache_free_blocks"; +drop table t1, t2, t3, t11, t21; +# +# SELECT SQL_CACHE ... +# +set query_cache_type=demand; +create table t1 (a int not null); +insert into t1 values (1),(2),(3); +select * from t1; +show status like "Qcache_queries_in_cache"; +select sql_cache * from t1; +set query_cache_type=2; +select sql_cache * from t1; +show status like "Qcache_hits"; +show status like "Qcache_queries_in_cache"; +set query_cache_type=on; +# +# RESET QUERY CACHE +# +reset query cache; +show status like "Qcache_queries_in_cache"; +# +# SELECT SQL_NO_CACHE +# +select sql_no_cache * from t1; +show status like "Qcache_queries_in_cache"; +drop table t1; +# +# Check that queries that uses NOW(), LAST_INSERT_ID()... are not cached. +# +create table t1 (a text not null); +select CONNECTION_ID() from t1; +#GET_LOCK +#RELEASE_LOCK +#LOAD_FILE +select FOUND_ROWS(); +select NOW() from t1; +select CURDATE() from t1; +select CURTIME() from t1; +select DATABASE() from t1; +select ENCRYPT("test") from t1; +select LAST_INSERT_ID() from t1; +select RAND() from t1; +select UNIX_TIMESTAMP() from t1; +select USER() from t1; +select benchmark(1,1) from t1; +show status like "Qcache_queries_in_cache"; +# +# Tests when the cache is filled +# +create table t2 (a text not null); +insert into t1 values("1111111111111111111111111111111111111111111111111111"); +insert into t2 select * from t1; +insert into t1 select * from t2; # 2 +insert into t2 select * from t1; # 3 +insert into t1 select * from t2; # 5 +insert into t2 select * from t1; # 8 +insert into t1 select * from t2; # 13 +insert into t2 select * from t1; # 21 +insert into t1 select * from t2; # 34 +insert into t2 select * from t1; # 55 +insert into t1 select * from t2; # 89 +insert into t2 select * from t1; # 144 +insert into t1 select * from t2; # 233 +insert into t2 select * from t1; # 357 +insert into t1 select * from t2; # 610 +insert into t2 select * from t1; # 987 +insert into t1 select * from t2; # 1597 +insert into t2 select * from t1; # 2584 +insert into t1 select * from t2; # 4181 + +show status like "Qcache_hits"; +disable_result_log; +select a as a1, a as a2 from t1; +select a as a2, a as a3 from t1; +select a as a3, a as a4 from t1; +# next query must be out of 1Mb cache +select a as a1, a as a2 from t1; +enable_result_log; +show status like "Qcache_hits"; +show status like "Qcache_queries_in_cache"; +reset query cache; +# +# Query bigger then query_cache_limit +# +insert into t2 select * from t1; +insert into t1 select * from t2; +insert into t2 select * from t1; +insert into t1 select * from t2; +insert into t2 select * from t1; +insert into t1 select * from t2; +insert into t2 select * from t1; +insert into t1 select * from t2; +insert into t2 select * from t1; +insert into t1 select * from t2; +disable_result_log; +select * from t1; +enable_result_log; +show status like "Qcache_queries_in_cache"; +drop table t1,t2; + +# +# noncachable ODBC work around (and prepare cache for drop database) +# +create database if not exists 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; + +# +# drop db +# +select * from mysqltest.t1; +show status like "Qcache_queries_in_cache"; +drop database mysqltest; +show status like "Qcache_queries_in_cache"; + +# +# Charset convertion (cp1251_koi8 always present) +# +create table t1 (a char(1) not null); +insert into t1 values("á"); +select * from t1; +set CHARACTER SET cp1251_koi8; +select * from t1; +set CHARACTER SET DEFAULT; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_hits"; +drop table t1; + +# The following tests can't be done as the values differen on 32 and 64 bit +# machines :( +#show variables like "query_cache_size"; +#show status like "Qcache_free_memory"; + +# +# same tables in different db +# +create database if not exists mysqltest; +create table mysqltest.t1 (i int not null); +create table t1 (i int not null); +insert into mysqltest.t1 (i) values (1); +insert into t1 (i) values (2); + +select * from t1; +use mysqltest; +select * from t1; +select * from t1; +use test; +select * from t1; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_hits"; + +drop database mysqltest; +drop table t1; + +# +# FOUND_ROWS() +# + +create table t1 (i int not null); +insert into t1 (i) values (1),(2),(3),(4); + +select SQL_CALC_FOUND_ROWS * from t1 limit 2; +select FOUND_ROWS(); +show status like "Qcache_hits"; +show status like "Qcache_queries_in_cache"; + +select * from t1 where i=1; +select FOUND_ROWS(); +show status like "Qcache_hits"; +show status like "Qcache_queries_in_cache"; + +select SQL_CALC_FOUND_ROWS * from t1 limit 2; +select FOUND_ROWS(); +show status like "Qcache_hits"; +show status like "Qcache_queries_in_cache"; + +select * from t1 where i=1; +select FOUND_ROWS(); +show status like "Qcache_hits"; +show status like "Qcache_queries_in_cache"; +drop table t1; + +# +# Test insert delayed +# + +flush query cache; +reset query cache; + +create table t1 (a int not null); +insert into t1 values (1),(2),(3); +select * from t1; +select * from t1; +show status like "Qcache_queries_in_cache"; +insert delayed into t1 values (4); +--sleep 5 # Wait for insert delayed to be executed. +select a from t1; +show status like "Qcache_queries_in_cache"; +drop table t1; +show status like "Qcache_queries_in_cache"; diff --git a/mysql-test/t/rename.test b/mysql-test/t/rename.test index 2a9cf113a47..ce4651d8de3 100644 --- a/mysql-test/t/rename.test +++ b/mysql-test/t/rename.test @@ -12,13 +12,19 @@ select * from t1; rename table t3 to t4, t2 to t3, t1 to t2, t4 to t1; rename table t3 to t4, t2 to t3, t1 to t2, t4 to t1; select * from t1; + # The following should give errors -!$1050 rename table t1 to t2; -!$1050 rename table t1 to t1; -!$1050 rename table t3 to t4, t2 to t3, t1 to t2, t4 to t2; +--error 1050,1050 +rename table t1 to t2; +--error 1050,1050 +rename table t1 to t1; +--error 1050,1050 +rename table t3 to t4, t2 to t3, t1 to t2, t4 to t2; show tables like "t_"; -!$1050 rename table t3 to t1, t2 to t3, t1 to t2, t4 to t1; -!$1017 rename table t3 to t4, t5 to t3, t1 to t2, t4 to t1; +--error 1050,1050 +rename table t3 to t1, t2 to t3, t1 to t2, t4 to t1; +--error 1017,1017 +rename table t3 to t4, t5 to t3, t1 to t2, t4 to t1; select * from t1; select * from t2; diff --git a/mysql-test/t/rpl000001.test b/mysql-test/t/rpl000001.test index 113a9637dac..6d46124d238 100644 --- a/mysql-test/t/rpl000001.test +++ b/mysql-test/t/rpl000001.test @@ -1,11 +1,15 @@ +eval_result; source include/master-slave.inc; connection master; use test; drop table if exists t1,t3; create table t1 (word char(20) not null); load data infile '../../std_data/words.dat' into table t1; -set password = password('foo'); -set password = password(''); +--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR +eval load data local infile '$MYSQL_TEST_DIR/std_data/words.dat' into table t1; +select * from t1; +set password for root@"localhost" = password('foo'); +set password for root@"localhost" = password(''); create table t3(n int); insert into t3 values(1),(2); save_master_pos; @@ -24,21 +28,38 @@ sync_with_master; connection master; reset master; connection slave; +slave stop; reset slave; connection master; drop table if exists t1,t2; create table t1(n int); -let $1=10; +#we want the log to exceed 16K to test deal with the log that is bigger than +#IO_SIZE +let $1=5000; +disable_query_log; while ($1) { - eval insert into t1 values($1); + eval insert into t1 values($1+get_lock("hold_slave",10)*0); dec $1; } +enable_query_log; + +#try to cause a large relay log lag on the slave +connection slave; +select get_lock("hold_slave",10); +slave start; +#hope this is long enough for I/O thread to fetch over 16K relay log data +sleep 1; +select release_lock("hold_slave"); +unlock tables; + +connection master; create table t2(id int); insert into t2 values(connection_id()); save_master_pos; + connection master1; #avoid generating result create temporary table t1_temp(n int); @@ -57,14 +78,14 @@ reap; connection slave; sync_with_master ; #give the slave a chance to exit -sleep 0.5; +wait_for_slave_to_stop; # The following test can't be done because the result of Pos will differ # on different computers # --replace_result 9306 9999 3334 9999 3335 9999 # show slave status; -set sql_slave_skip_counter=1; +set global sql_slave_skip_counter=1; slave start; select count(*) from t1; connection master1; diff --git a/mysql-test/t/rpl000002.test b/mysql-test/t/rpl000002.test index 0c490e6316d..420ae27a337 100644 --- a/mysql-test/t/rpl000002.test +++ b/mysql-test/t/rpl000002.test @@ -1,4 +1,5 @@ source include/master-slave.inc; +eval_result; connection master; use test; drop table if exists t1; @@ -11,6 +12,7 @@ use test; sync_with_master; select * from t1; connection master; +show slave hosts; drop table t1; save_master_pos; connection slave; diff --git a/mysql-test/t/rpl000009.test b/mysql-test/t/rpl000009.test index 768c6c151b4..d45fe0870ff 100644 --- a/mysql-test/t/rpl000009.test +++ b/mysql-test/t/rpl000009.test @@ -1,5 +1,6 @@ -#this one assumes we are ignoring updates on tables in database foo, but doing -#the ones in database bar +# This one assumes we are ignoring updates on tables in database foo, but doing +# the ones in database bar + source include/master-slave.inc; connection master; drop database if exists foo; @@ -31,3 +32,57 @@ connection slave; sync_with_master; drop database if exists bar; drop database if exists foo; + +# Now let's test load data from master + +# First create some databases and tables on the master + +connection master; +set sql_log_bin = 0; +create database foo; +create database bar; +show databases; +create table foo.t1(n int, s char(20)); +create table foo.t2(n int, s text); +insert into foo.t1 values (1, 'one'), (2, 'two'), (3, 'three'); +insert into foo.t2 values (11, 'eleven'), (12, 'twelve'), (13, 'thirteen'); + +create table bar.t1(n int, s char(20)); +create table bar.t2(n int, s text); +insert into bar.t1 values (1, 'one bar'), (2, 'two bar'), (3, 'three bar'); +insert into bar.t2 values (11, 'eleven bar'), (12, 'twelve bar'), + (13, 'thirteen bar'); +set sql_log_bin = 1; +save_master_pos; +connection slave; +sync_with_master; + +# This should show that the slave is empty at this point +show databases; +load data from master; + +# Now let's check if we have the right tables and the right data in them +show databases; +use foo; +show tables; +use bar; +show tables; +select * from bar.t1; +select * from bar.t2; + +# Now let's see if replication works +connection master; +insert into bar.t1 values (4, 'four bar'); +save_master_pos; +connection slave; +sync_with_master; +select * from bar.t1; + +# Now time for cleanup +connection master; +drop database bar; +drop database foo; +save_master_pos; +connection slave; +sync_with_master; + diff --git a/mysql-test/t/rpl000014.test b/mysql-test/t/rpl000014.test index b501d63b10e..df15fa6e600 100644 --- a/mysql-test/t/rpl000014.test +++ b/mysql-test/t/rpl000014.test @@ -4,18 +4,19 @@ show master status; save_master_pos; connection slave; sync_with_master; ---replace_result 9306 9999 3334 9999 3335 9999 +--replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT show slave status; change master to master_log_pos=73; slave stop; + change master to master_log_pos=73; ---replace_result 9306 9999 3334 9999 3335 9999 +--replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT show slave status; slave start; ---replace_result 9306 9999 3334 9999 3335 9999 +--replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT show slave status; change master to master_log_pos=173; ---replace_result 9306 9999 3334 9999 3335 9999 +--replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT show slave status; connection master; show master status; @@ -25,7 +26,7 @@ create table foo (n int); insert into foo values (1),(2),(3); save_master_pos; connection slave; -change master to master_log_pos=73; +change master to master_log_pos=79; sync_with_master; select * from foo; connection master; diff --git a/mysql-test/t/rpl000015-slave-master-info.opt b/mysql-test/t/rpl000015-slave-master-info.opt index 80190bf6d29..28bc753dd56 100644 --- a/mysql-test/t/rpl000015-slave-master-info.opt +++ b/mysql-test/t/rpl000015-slave-master-info.opt @@ -1 +1 @@ ---server-id=2 +--server-id=22 --master-connect-retry=7 diff --git a/mysql-test/t/rpl000015.test b/mysql-test/t/rpl000015.test index 73a10bed7b3..c42e14699c5 100644 --- a/mysql-test/t/rpl000015.test +++ b/mysql-test/t/rpl000015.test @@ -1,22 +1,26 @@ -connect (master,localhost,root,,test,0,mysql-master.sock); -connect (slave,localhost,root,,test,0, mysql-slave.sock); +connect (master,localhost,root,,test,0,master.sock); +connect (slave,localhost,root,,test,0, slave.sock); connection master; reset master; show master status; save_master_pos; connection slave; reset slave; +--replace_result $MASTER_MYPORT MASTER_PORT show slave status; + change master to master_host='127.0.0.1'; ---replace_result 3306 9998 9306 9999 3334 9999 3335 9999 +# The following needs to be cleaned up when change master is fixed +--replace_result $MASTER_MYPORT MASTER_PORT 3306 MASTER_PORT 3334 MASTER_PORT show slave status; +--replace_result $MASTER_MYPORT MASTER_PORT eval change master to master_host='127.0.0.1',master_user='root', master_password='',master_port=$MASTER_MYPORT; ---replace_result 9306 9999 3334 9999 3335 9999 +--replace_result $MASTER_MYPORT MASTER_PORT show slave status; slave start; sync_with_master; ---replace_result 9306 9999 3334 9999 3335 9999 +--replace_result $MASTER_MYPORT MASTER_PORT show slave status; connection master; drop table if exists t1; @@ -31,4 +35,3 @@ drop table t1; save_master_pos; connection slave; sync_with_master; - diff --git a/mysql-test/t/rpl000016-slave.opt b/mysql-test/t/rpl000016-slave.opt new file mode 100644 index 00000000000..f27601e0d7d --- /dev/null +++ b/mysql-test/t/rpl000016-slave.opt @@ -0,0 +1 @@ +-O max_binlog_size=2048 diff --git a/mysql-test/t/rpl000016-slave.sh b/mysql-test/t/rpl000016-slave.sh index 62748605af1..9259f593e54 100755 --- a/mysql-test/t/rpl000016-slave.sh +++ b/mysql-test/t/rpl000016-slave.sh @@ -1 +1,2 @@ rm -f $MYSQL_TEST_DIR/var/slave-data/master.info +rm -f $MYSQL_TEST_DIR/var/slave-data/*relay* diff --git a/mysql-test/t/rpl000016.test b/mysql-test/t/rpl000016.test index c9b6ccabcd2..b830d0b8d91 100644 --- a/mysql-test/t/rpl000016.test +++ b/mysql-test/t/rpl000016.test @@ -1,14 +1,16 @@ -connect (master,localhost,root,,test,0,mysql-master.sock); -connect (slave,localhost,root,,test,0,mysql-slave.sock); +connect (master,localhost,root,,test,0,master.sock); +connect (slave,localhost,root,,test,0,slave.sock); system cat /dev/null > var/slave-data/master.info; system chmod 000 var/slave-data/master.info; connection slave; !slave start; system chmod 600 var/slave-data/master.info; !slave start; +--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'; 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'; connection master; @@ -22,7 +24,7 @@ insert into t1 values('Could not break slave'),('Tried hard'); save_master_pos; connection slave; sync_with_master; ---replace_result 9306 9999 3334 9999 3335 9999 +--replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT show slave status; select * from t1; connection master; @@ -49,11 +51,10 @@ insert into t2 values(NULL); connection slave; sync_with_master; -#the slave may have already stopped, so we ignore the error -!slave stop; +wait_for_slave_to_stop; #restart slave skipping one event -set sql_slave_skip_counter=1; +set global sql_slave_skip_counter=1; slave start; connection master; @@ -69,19 +70,21 @@ insert into t2 values (65); save_master_pos; connection slave; sync_with_master; ---replace_result 9306 9999 3334 9999 3335 9999 +--replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT show slave status; select * from t2; connection master; let $1=100; drop table if exists t3; create table t3 (n int); +disable_query_log; while ($1) { #eval means expand $ expressions eval insert into t3 values($1 + 4); dec $1; } +enable_query_log; show master logs; show master status; save_master_pos; @@ -89,7 +92,7 @@ connection slave; slave stop; slave start; sync_with_master; ---replace_result 9306 9999 3334 9999 3335 9999 +--replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT show slave status; # because of concurrent insert, the table may not be up to date # if we do not lock diff --git a/mysql-test/t/rpl000017-slave.sh b/mysql-test/t/rpl000017-slave.sh index 0588859c591..066b4880cc1 100755 --- a/mysql-test/t/rpl000017-slave.sh +++ b/mysql-test/t/rpl000017-slave.sh @@ -1,3 +1,5 @@ +rm -f $MYSQL_TEST_DIR/var/log/*relay* +rm -f $MYSQL_TEST_DIR/var/slave-data/relay-log.info cat > $MYSQL_TEST_DIR/var/slave-data/master.info <<EOF master-bin.001 4 @@ -6,4 +8,5 @@ replicate aaaaaaaaaaaaaaabthispartofthepasswordisnotused $MASTER_MYPORT 1 +0 EOF diff --git a/mysql-test/t/rpl000017.test b/mysql-test/t/rpl000017.test index 8e4e61cb9d6..f346ca39ca0 100644 --- a/mysql-test/t/rpl000017.test +++ b/mysql-test/t/rpl000017.test @@ -1,9 +1,9 @@ -connect (master,localhost,root,,test,0,mysql-master.sock); -connect (slave,localhost,root,,test,0,mysql-slave.sock); +connect (master,localhost,root,,test,0,master.sock); +connect (slave,localhost,root,,test,0,slave.sock); connection master; reset master; -grant file on *.* to replicate@localhost identified by 'aaaaaaaaaaaaaaab'; -grant file on *.* to replicate@127.0.0.1 identified by 'aaaaaaaaaaaaaaab'; +grant replication slave on *.* to replicate@localhost identified by 'aaaaaaaaaaaaaaab'; +grant replication slave on *.* to replicate@127.0.0.1 identified by 'aaaaaaaaaaaaaaab'; connection slave; slave start; connection master; diff --git a/mysql-test/t/rpl000018-master.sh b/mysql-test/t/rpl000018-master.sh deleted file mode 100755 index e570f106ec6..00000000000 --- a/mysql-test/t/rpl000018-master.sh +++ /dev/null @@ -1,3 +0,0 @@ -rm -f $MYSQL_TEST_DIR/var/master-data/master-bin.* -cp $MYSQL_TEST_DIR/std_data/master-bin.001 $MYSQL_TEST_DIR/var/master-data/ -echo ./master-bin.001 > $MYSQL_TEST_DIR/var/master-data/master-bin.index diff --git a/mysql-test/t/rpl000018.test b/mysql-test/t/rpl000018.test index 44236323d1d..30d601bb375 100644 --- a/mysql-test/t/rpl000018.test +++ b/mysql-test/t/rpl000018.test @@ -1,5 +1,8 @@ -connect (master,localhost,root,,test,0,mysql-master.sock); -connect (slave,localhost,root,,test,0,mysql-slave.sock); +require_manager; +connect (master,localhost,root,,test,0,master.sock); +connect (slave,localhost,root,,test,0,slave.sock); +server_stop master; +server_start master; connection slave; reset slave; slave start; diff --git a/mysql-test/t/rpl_compat.test b/mysql-test/t/rpl_compat.test new file mode 100644 index 00000000000..9c67cce1680 --- /dev/null +++ b/mysql-test/t/rpl_compat.test @@ -0,0 +1,86 @@ +eval_result; +source include/master-slave.inc; +connection master; +require_version 3.23; +use test; +drop table if exists t1,t3; +create table t1 (word char(20) not null); +load data infile '../../std_data/words.dat' into table t1; +select * from t1; +set password for root@"localhost" = password('foo'); +set password for root@"localhost" = password(''); +create table t3(n int); +insert into t3 values(1),(2); +save_master_pos; +connection slave; +sync_with_master; +use test; +select * from t3; +select sum(length(word)) from t1; +connection master; +drop table t1,t3; +save_master_pos; +connection slave; +sync_with_master; + +#test handling of aborted connection in the middle of update +connection master; +reset master; +connection slave; +slave stop; +reset slave; + +connection master; +create table t1(n int); +insert into t1 values (1),(2),(3); +create table t2(id int); +insert into t2 values(connection_id()); +save_master_pos; + +connection master1; +#avoid generating result +create temporary table t1_temp(n int); +insert into t1_temp select get_lock('crash_lock%20C', 1) from t2; + +connection master; +send update t1 set n = n + get_lock('crash_lock%20C', 2); +connection master1; +sleep 2; +select (@id := id) - id from t2; +kill @id; +drop table t2; +connection master; +--error 1053; +reap; +connection slave; +slave start; +sync_with_master ; +#now slave will hit an error +wait_for_slave_to_stop; + +set global sql_slave_skip_counter=1; +slave start; +select count(*) from t1; +connection master1; +drop table t1; +create table t1 (n int); +insert into t1 values(3456); +use mysql; +insert into user (Host, User, Password) + VALUES ("10.10.10.%", "blafasel2", password("blafasel2")); +select select_priv,user from mysql.user where user = 'blafasel2'; +update user set Select_priv = "Y" where User="blafasel2"; +select select_priv,user from mysql.user where user = 'blafasel2'; +use test; +save_master_pos; +connection slave; +sync_with_master; +select n from t1; +select select_priv,user from mysql.user where user = 'blafasel2'; +connection master1; +drop table t1; +save_master_pos; +connection slave; +sync_with_master; + + diff --git a/mysql-test/t/rpl_empty_master_crash.test b/mysql-test/t/rpl_empty_master_crash.test index 7c4437a3657..158b59169ee 100644 --- a/mysql-test/t/rpl_empty_master_crash.test +++ b/mysql-test/t/rpl_empty_master_crash.test @@ -3,6 +3,5 @@ connection master; use test; drop table if exists t1; show slave status; -#--error 1218 # for mysql-4.0 ---error 1042 +--error 1218 load table t1 from master; diff --git a/mysql-test/t/rpl_failsafe.test b/mysql-test/t/rpl_failsafe.test new file mode 100644 index 00000000000..866efbce5bf --- /dev/null +++ b/mysql-test/t/rpl_failsafe.test @@ -0,0 +1,24 @@ +require_manager; +source include/master-slave.inc; +connect (slave_sec,localhost,root,,test,0,slave.sock-1); +connect (slave_ter,localhost,root,,test,0,slave.sock-2); +connection master; +show variables like 'rpl_recovery_rank'; +show status like 'Rpl_status'; +create table t1(n int); +drop table t1; +save_master_pos; +connection slave; +sync_with_master; +show variables like 'rpl_recovery_rank'; +show status like 'Rpl_status'; +connection slave_sec; +slave start; +sync_with_master; +show variables like 'rpl_recovery_rank'; +show status like 'Rpl_status'; +connection slave_ter; +slave start; +sync_with_master; +show variables like 'rpl_recovery_rank'; +show status like 'Rpl_status'; diff --git a/mysql-test/t/rpl_get_lock.test b/mysql-test/t/rpl_get_lock.test index 3c33103e4c7..1d98eb16bb0 100644 --- a/mysql-test/t/rpl_get_lock.test +++ b/mysql-test/t/rpl_get_lock.test @@ -6,6 +6,8 @@ dirty_close master; connection master1; select get_lock("lock",2); select release_lock("lock"); +#ignore +disable_query_log; let $1=2000; while ($1) { @@ -13,11 +15,16 @@ while ($1) do release_lock("lock"); dec $1; } +enable_query_log; save_master_pos; connection slave; sync_with_master; select get_lock("lock",3); select * from t1; +select is_free_lock("lock"); +# Check lock functions +select is_free_lock("lock2"); +select is_free_lock(NULL); connection master1; drop table t1; save_master_pos; diff --git a/mysql-test/t/rpl_log.test b/mysql-test/t/rpl_log.test new file mode 100644 index 00000000000..3514d435737 --- /dev/null +++ b/mysql-test/t/rpl_log.test @@ -0,0 +1,57 @@ +source include/master-slave.inc; + +#clean up slave binlogs +connection slave; +slave stop; +reset master; +reset slave; +let $VERSION=`select version()`; + +connection master; +reset master; +drop table if exists t1; +create table t1(n int not null auto_increment primary key); +insert into t1 values (NULL); +drop table t1; +create table t1 (word char(20) not null); +load data infile '../../std_data/words.dat' into table t1; +drop table t1; +--replace_result $VERSION VERSION +show binlog events; +show binlog events from 79 limit 1; +show binlog events from 79 limit 2; +show binlog events from 79 limit 2,1; +flush logs; +create table t1 (n int); +insert into t1 values (1); +drop table t1; +--replace_result $VERSION VERSION +show binlog events; +show binlog events in 'master-bin.002'; +show master logs; +save_master_pos; +connection slave; +slave start; +sync_with_master; +show master logs; +--replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT $VERSION VERSION +# We can't compare binlog from slave as the result differ between +# machines based on where the LOAD DATA file is stored. +disable_result_log; + +show binlog events in 'slave-bin.001' from 4; +--replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT $VERSION VERSION +enable_result_log; +show binlog events in 'slave-bin.002' from 4; +--replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT +show slave status; +show new master for slave with master_log_file='master-bin.001' and + master_log_pos=4 and master_server_id=1; +show new master for slave with master_log_file='master-bin.001' and + master_log_pos=79 and master_server_id=1; +show new master for slave with master_log_file='master-bin.001' and + master_log_pos=311 and master_server_id=1; +show new master for slave with master_log_file='master-bin.002' and + master_log_pos=4 and master_server_id=1; +show new master for slave with master_log_file='master-bin.002' and + master_log_pos=122 and master_server_id=1; diff --git a/mysql-test/t/rpl_magic.test b/mysql-test/t/rpl_magic.test new file mode 100644 index 00000000000..376edc301d7 --- /dev/null +++ b/mysql-test/t/rpl_magic.test @@ -0,0 +1,31 @@ +source include/master-slave.inc; + +#first, make sure the slave has had enough time to register +connection master; +save_master_pos; +connection slave; +sync_with_master; + +#discover slaves +connection master; +rpl_probe; + +#turn on master/slave query direction auto-magic +enable_rpl_parse; +drop table if exists t1; +create table t1 ( n int); +insert into t1 values (1),(2),(3),(4); +disable_rpl_parse; +save_master_pos; +enable_rpl_parse; +connection slave; +sync_with_master; +insert into t1 values(5); +connection master; +select * from t1; +select * from t1; +disable_rpl_parse; +select * from t1; +connection slave; +select * from t1; +drop table t1; diff --git a/mysql-test/t/rpl_mystery22.test b/mysql-test/t/rpl_mystery22.test index 3a48ef84dc1..e987074697b 100644 --- a/mysql-test/t/rpl_mystery22.test +++ b/mysql-test/t/rpl_mystery22.test @@ -1,5 +1,6 @@ # test case to make slave thread get ahead by 22 bytes +drop table if exists t1; source include/master-slave.inc; connection master; # first, cause a duplicate key problem on the slave diff --git a/mysql-test/t/rpl_sporadic_master.test b/mysql-test/t/rpl_sporadic_master.test index c1a47781ddf..0487f868436 100644 --- a/mysql-test/t/rpl_sporadic_master.test +++ b/mysql-test/t/rpl_sporadic_master.test @@ -7,7 +7,7 @@ drop table if exists t1,t2; create table t2(n int); create table t1(n int not null auto_increment primary key); insert into t1 values (NULL),(NULL); -delete from t1; +truncate table t1; # We have to use 4 in the following to make this test work with all table types insert into t1 values (4),(NULL); save_master_pos; @@ -18,10 +18,8 @@ slave start; connection master; insert into t1 values (NULL),(NULL); flush logs; -delete from t1; -insert into t1 values (10),(NULL); -insert into t1 values (NULL),(NULL); -insert into t1 values (NULL),(NULL); +truncate table t1; +insert into t1 values (10),(NULL),(NULL),(NULL),(NULL),(NULL); save_master_pos; connection slave; sync_with_master; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index cdb6ee57e0f..159746e4dde 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -1713,7 +1713,20 @@ show full columns from t2 from test like 's%'; show keys from t2; # +# Test of DO + +DO 1; +DO benchmark(100,1+1),1,1; + +# # Drop the test tables # drop table t4, t3,t2, t1; +DROP TABLE IF EXISTS t1,t2; +CREATE TABLE t1 ( gvid int(10) unsigned default NULL, hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, mmid int(10) unsigned default NULL, hdid int(10) unsigned default NULL, fsid int(10) unsigned default NULL, ctid int(10) unsigned default NULL, dtid int(10) unsigned default NULL, cost int(10) unsigned default NULL, performance int(10) unsigned default NULL, serialnumber bigint(20) unsigned default NULL, monitored tinyint(3) unsigned default '1', removed tinyint(3) unsigned default '0', target tinyint(3) unsigned default '0', dt_modified timestamp(14) NOT NULL, name varchar(255) binary default NULL, description varchar(255) default NULL, UNIQUE KEY hmid (hmid,volid)) TYPE=MyISAM; +INSERT INTO t1 VALUES (200001,2,1,1,100,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\E$',''),(200002,2,2,1,101,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\C$',''),(200003,1,3,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,1,20020425060427,'c:',NULL); +CREATE TABLE t2 ( hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, sampletid smallint(5) unsigned default NULL, sampletime datetime default NULL, samplevalue bigint(20) unsigned default NULL, KEY idx1 (hmid,volid,sampletid,sampletime)) TYPE=MyISAM; +INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35); +SELECT a.gvid, (SUM(CASE b.sampletid WHEN 140 THEN b.samplevalue ELSE 0 END)) as the_success,(SUM(CASE b.sampletid WHEN 141 THEN b.samplevalue ELSE 0 END)) as the_fail,(SUM(CASE b.sampletid WHEN 142 THEN b.samplevalue ELSE 0 END)) as the_size,(SUM(CASE b.sampletid WHEN 143 THEN b.samplevalue ELSE 0 END)) as the_time FROM t1 a, t2 b WHERE a.hmid = b.hmid AND a.volid = b.volid AND b.sampletime >= 'NULL' AND b.sampletime < 'NULL' AND b.sampletid IN (140, 141, 142, 143) GROUP BY a.gvid; +DROP TABLE t1,t2; diff --git a/mysql-test/t/select_found.test b/mysql-test/t/select_found.test new file mode 100644 index 00000000000..0a483c860cb --- /dev/null +++ b/mysql-test/t/select_found.test @@ -0,0 +1,87 @@ +# +# Testing of found_rows() +# + +drop table if exists t1,t2; +create table t1 (a int not null auto_increment, b int not null, primary key(a)); +insert into t1 (b) values (2),(3),(5),(5),(5),(6),(7),(9); +select SQL_CALC_FOUND_ROWS * from t1; +select found_rows(); +select SQL_CALC_FOUND_ROWS * from t1 limit 1; +select found_rows(); +select SQL_BUFFER_RESULT SQL_CALC_FOUND_ROWS * from t1 limit 1; +select found_rows(); +select SQL_CALC_FOUND_ROWS * from t1 order by b desc limit 1; +select found_rows(); +select SQL_CALC_FOUND_ROWS distinct b from t1 limit 1; +select found_rows(); +select SQL_CALC_FOUND_ROWS b,count(*) as c from t1 group by b order by c desc limit 1; +select found_rows(); +select SQL_CALC_FOUND_ROWS * from t1 left join t1 as t2 on (t1.b=t2.a) limit 2,1; +select found_rows(); +drop table t1; + +# +# Test SQL_CALC_FOUND_ROWS optimization when used with one table and filesort +# + +create table t1 (a int not null primary key); +insert into t1 values (1),(2),(3),(4),(5); +select sql_calc_found_rows a from t1 where a in (1,2,3) order by a desc limit 0,2; +select FOUND_ROWS(); +select sql_calc_found_rows a from t1 where a in (1,2,3) order by a+2 desc limit 0,2; +select FOUND_ROWS(); +drop table t1; + +# +# Test of SQL_CALC_FOUND_ROWS with DISTINCT +# + +CREATE TABLE t1 ( +`id` smallint(5) unsigned NOT NULL auto_increment, +`kid` smallint(5) unsigned NOT NULL default '0', +PRIMARY KEY (`id`), +KEY `kid` (`kid`) +); + +CREATE TABLE t2 ( + id smallint(5) unsigned NOT NULL auto_increment, + name varchar(50) NOT NULL default '', + email varchar(50) NOT NULL default '', + PRIMARY KEY (id), + UNIQUE KEY e_n (email,name) +); + +INSERT INTO t2 VALUES (1,'name1','email1'),(2,'name2','email2'),(3,'name3','email3'),(4,'name4','email4'),(5,'name5','email5'),(6,'name6','email6'),(7,'name7','email7'),(8,'name8','email8'),(9,'name9','email9'),(10,'name10','email10'),(11,'name11','email11'),(12,'name12','email12'),(13,'name13','email13'),(14,'name14','email14'),(15,'name15','email15'),(16,'name16','email16'),(17,'name17','email17'),(18,'name18','email18'),(19,'name19','email19'),(20,'name20','email20'),(21,'name21','email21'),(22,'name22','email22'),(23,'name23','email23'),(24,'name24','email24'),(25,'name25','email25'),(26,'name26','email26'),(27,'name27','email27'),(28,'name28','email28'),(29,'name29','email29'),(30,'name30','email30'),(31,'name31','email31'),(32,'name32','email32'),(33,'name33','email33'),(34,'name34','email34'),(35,'name35','email35'),(36,'name36','email36'),(37,'name37','email37'),(38,'name38','email38'),(39,'name39','email39'),(40,'name40','email40'),(41,'name41','email41'),(42,'name42','email42'),(43,'name43','email43'),(44,'name44','email44'),(45,'name45','email45'),(46,'name46','email46'),(47,'name47','email47'),(48,'name48','email48'),(49,'name49','email49'),(50,'name50','email50'),(51,'name51','email51'),(52,'name52','email52'),(53,'name53','email53'),(54,'name54','email54'),(55,'name55','email55'),(56,'name56','email56'),(57,'name57','email57'),(58,'name58','email58'),(59,'name59','email59'),(60,'name60','email60'),(61,'name61','email61'),(62,'name62','email62'),(63,'name63','email63'),(64,'name64','email64'),(65,'name65','email65'),(66,'name66','email66'),(67,'name67','email67'),(68,'name68','email68'),(69,'name69','email69'),(70,'name70','email70'),(71,'name71','email71'),(72,'name72','email72'),(73,'name73','email73'),(74,'name74','email74'),(75,'name75','email75'),(76,'name76','email76'),(77,'name77','email77'),(78,'name78','email78'),(79,'name79','email79'),(80,'name80','email80'),(81,'name81','email81'),(82,'name82','email82'),(83,'name83','email83'),(84,'name84','email84'),(85,'name85','email85'),(86,'name86','email86'),(87,'name87','email87'),(88,'name88','email88'),(89,'name89','email89'),(90,'name90','email90'),(91,'name91','email91'),(92,'name92','email92'),(93,'name93','email93'),(94,'name94','email94'),(95,'name95','email95'),(96,'name96','email96'),(97,'name97','email97'),(98,'name98','email98'),(99,'name99','email99'),(100,'name100','email100'),(101,'name101','email101'),(102,'name102','email102'),(103,'name103','email103'),(104,'name104','email104'),(105,'name105','email105'),(106,'name106','email106'),(107,'name107','email107'),(108,'name108','email108'),(109,'name109','email109'),(110,'name110','email110'),(111,'name111','email111'),(112,'name112','email112'),(113,'name113','email113'),(114,'name114','email114'),(115,'name115','email115'),(116,'name116','email116'),(117,'name117','email117'),(118,'name118','email118'),(119,'name119','email119'),(120,'name120','email120'),(121,'name121','email121'),(122,'name122','email122'),(123,'name123','email123'),(124,'name124','email124'),(125,'name125','email125'),(126,'name126','email126'),(127,'name127','email127'),(128,'name128','email128'),(129,'name129','email129'),(130,'name130','email130'),(131,'name131','email131'),(132,'name132','email132'),(133,'name133','email133'),(134,'name134','email134'),(135,'name135','email135'),(136,'name136','email136'),(137,'name137','email137'),(138,'name138','email138'),(139,'name139','email139'),(140,'name140','email140'),(141,'name141','email141'),(142,'name142','email142'),(143,'name143','email143'),(144,'name144','email144'),(145,'name145','email145'),(146,'name146','email146'),(147,'name147','email147'),(148,'name148','email148'),(149,'name149','email149'),(150,'name150','email150'),(151,'name151','email151'),(152,'name152','email152'),(153,'name153','email153'),(154,'name154','email154'),(155,'name155','email155'),(156,'name156','email156'),(157,'name157','email157'),(158,'name158','email158'),(159,'name159','email159'),(160,'name160','email160'),(161,'name161','email161'),(162,'name162','email162'),(163,'name163','email163'),(164,'name164','email164'),(165,'name165','email165'),(166,'name166','email166'),(167,'name167','email167'),(168,'name168','email168'),(169,'name169','email169'),(170,'name170','email170'),(171,'name171','email171'),(172,'name172','email172'),(173,'name173','email173'),(174,'name174','email174'),(175,'name175','email175'),(176,'name176','email176'),(177,'name177','email177'),(178,'name178','email178'),(179,'name179','email179'),(180,'name180','email180'),(181,'name181','email181'),(182,'name182','email182'),(183,'name183','email183'),(184,'name184','email184'),(185,'name185','email185'),(186,'name186','email186'),(187,'name187','email187'),(188,'name188','email188'),(189,'name189','email189'),(190,'name190','email190'),(191,'name191','email191'),(192,'name192','email192'),(193,'name193','email193'),(194,'name194','email194'),(195,'name195','email195'),(196,'name196','email196'),(197,'name197','email197'),(198,'name198','email198'),(199,'name199','email199'),(200,'name200','email200'); + +SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10; +SELECT FOUND_ROWS(); + +SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL order by email LIMIT 10; +SELECT FOUND_ROWS(); + +SELECT DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10; +SELECT DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL ORDER BY email LIMIT 10; +INSERT INTO `t1` (`id`, `kid`) VALUES ('', '150'); + +SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10; +SELECT FOUND_ROWS(); + +drop table t1,t2; + +# +# Test bug when using range optimization +# + +CREATE TABLE `t1` ( + `titre` char(80) NOT NULL default '', + `numeropost` mediumint(8) unsigned NOT NULL auto_increment, + `maxnumrep` int(10) unsigned NOT NULL default '0', + PRIMARY KEY (`numeropost`), + KEY `maxnumrep` (`maxnumrep`) +) TYPE=MyISAM ROW_FORMAT=FIXED; +INSERT INTO t1 (titre,maxnumrep) VALUES +('test1','1'),('test2','2'),('test3','3'); +SELECT SQL_CALC_FOUND_ROWS titre,numeropost,maxnumrep FROM t1 WHERE numeropost IN (1,2) ORDER BY maxnumrep DESC LIMIT 0, 1; +SELECT FOUND_ROWS(); +drop table t1; diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test index e9c7ffa6bb8..5e10ebf23a3 100644 --- a/mysql-test/t/show_check.test +++ b/mysql-test/t/show_check.test @@ -1,30 +1,17 @@ # # Test of some show commands # -drop table if exists t1,t2; -create table t1 (a int not null primary key, b int not null,c int not null, key(b,c)); -insert into t1 values (1,2,2),(2,2,3),(3,2,4),(4,2,4); -create table t2 type=isam select * from t1; -optimize table t1; -check table t1,t2; -repair table t1,t2; -check table t2,t1; -lock tables t1 write; -check table t2,t1; -show columns from t1; -show full columns from t1; -show index from t1; -drop table t1,t2; +drop table if exists t1,t2; create table t1 (a int not null primary key, b int not null,c int not null, key(b,c)); insert into t1 values (1,2,2),(2,2,3),(3,2,4),(4,2,4); -check table t1 type=fast; -check table t1 type=fast; -check table t1 type=changed; +check table t1 fast; +check table t1 fast; +check table t1 changed; insert into t1 values (5,5,5); -check table t1 type=changed; -check table t1 type=medium; -check table t1 type=extended; +check table t1 changed; +check table t1 medium; +check table t1 extended; show index from t1; !$1062 insert into t1 values (5,5,5); optimize table t1; @@ -74,6 +61,21 @@ create table t1 (a int not null, primary key (a)); show create table t1; drop table t1; +flush tables; +show open tables; +create table t1(n int); +insert into t1 values (1); +show open tables; +drop table t1; + +create table t1 (a int not null, b VARCHAR(10), INDEX (b) ) AVG_ROW_LENGTH=10 CHECKSUM=1 COMMENT="test" TYPE=MYISAM MIN_ROWS=10 MAX_ROWS=100 PACK_KEYS=1 DELAY_KEY_WRITE=1 ROW_FORMAT=fixed; +show create table t1; +alter table t1 MAX_ROWS=200 ROW_FORMAT=dynamic PACK_KEYS=0; +show create table t1; +ALTER TABLE t1 AVG_ROW_LENGTH=0 CHECKSUM=0 COMMENT="" MIN_ROWS=0 MAX_ROWS=0 PACK_KEYS=DEFAULT DELAY_KEY_WRITE=0 ROW_FORMAT=default; +show create table t1; +drop table t1; + create table t1 (a decimal(9,2), b decimal (9,0), e double(9,2), f double(5,0), h float(3,2), i float(3,0)); show columns from t1; drop table t1; diff --git a/mysql-test/t/status.test b/mysql-test/t/status.test index 70a7a3ab584..cffca437cf1 100644 --- a/mysql-test/t/status.test +++ b/mysql-test/t/status.test @@ -1,3 +1,11 @@ +# This test doesn't work with the embedded version as this code +# assumes that one query is running while we are doing queries on +# a second connection. +# This would work if mysqltest run would be threaded and handle each +# connection in a separate thread. +# +-- source include/not_embedded.inc + connect (con1,localhost,root,,); connect (con2,localhost,root,,); @@ -6,7 +14,7 @@ show status like 'Table_lock%'; connection con1; SET SQL_LOG_BIN=0; drop table if exists t1; -create table t1(n int); +create table t1(n int) type=myisam; insert into t1 values(1); connection con2; lock tables t1 read; diff --git a/mysql-test/t/symlink.test b/mysql-test/t/symlink.test new file mode 100644 index 00000000000..586761921b8 --- /dev/null +++ b/mysql-test/t/symlink.test @@ -0,0 +1,89 @@ +-- require r/have_symlink.require +disable_query_log; +show variables like "have_symlink"; +enable_query_log; + +# +# First create little data to play with +# +drop table if exists t1,t2,t7,t8,t9; +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"); +insert into t2 (b) select b from t1; +insert into t1 (b) select b from t2; +insert into t2 (b) select b from t1; +insert into t1 (b) select b from t2; +insert into t2 (b) select b from t1; +insert into t1 (b) select b from t2; +insert into t2 (b) select b from t1; +insert into t1 (b) select b from t2; +insert into t2 (b) select b from t1; +insert into t1 (b) select b from t2; +insert into t2 (b) select b from t1; +insert into t1 (b) select b from t2; +insert into t2 (b) select b from t1; +insert into t1 (b) select b from t2; +insert into t2 (b) select b from t1; +insert into t1 (b) select b from t2; +insert into t2 (b) select b from t1; +insert into t1 (b) select b from t2; +drop table t2; + +# +# Start the test +# We use t9 here to not crash with tables generated by the backup test +# + +disable_query_log; +eval create table t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam data directory="$MYSQL_TEST_DIR/var/tmp" index directory="$MYSQL_TEST_DIR/var/run"; +enable_query_log; + +insert into t9 select * from t1; +check table t9; +optimize table t9; +repair table t9; +alter table t9 add column c int not null; +show create table t9; + +# Test renames +alter table t9 rename t8, add column d int not null; +alter table t8 rename t7; +rename table t7 to t9; +# Drop old t1 table, keep t9 +drop table t1; + +# +# Test error handling +# Note that we are using the above table t9 here! +# + +disable_query_log; +--error 1103,1103 +create table t1 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam data directory="tmp"; + +# Check that we cannot link over a table from another database. + +drop database if exists test_mysqltest; +create database test_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"; + +--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"; + +--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"; + +--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"; +enable_query_log; + +# Check moving table t9 from default database to test_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; diff --git a/mysql-test/t/tablelock.test b/mysql-test/t/tablelock.test index c32a3f7cd35..fa8c4f03675 100644 --- a/mysql-test/t/tablelock.test +++ b/mysql-test/t/tablelock.test @@ -2,6 +2,7 @@ # Test of lock tables # +drop table if exists t1,t2; create table t1 ( n int auto_increment primary key); lock tables t1 write; insert into t1 values(NULL); @@ -36,12 +37,10 @@ drop table t1; CREATE TABLE t1 (a int); CREATE TABLE t2 (a int); lock tables t1 write,t1 as b write, t2 write, t2 as c read; -drop table t1; -drop table t2; +drop table t1,t2; CREATE TABLE t1 (a int); CREATE TABLE t2 (a int); lock tables t1 write,t1 as b write, t2 write, t2 as c read; -drop table t2; -drop table t1; +drop table t2,t1; unlock tables; diff --git a/mysql-test/t/truncate.test b/mysql-test/t/truncate.test index e995517cf1e..1729ddc586a 100644 --- a/mysql-test/t/truncate.test +++ b/mysql-test/t/truncate.test @@ -2,10 +2,19 @@ # Test of truncate # create table t1 (a integer, b integer,c1 CHAR(10)); +insert into t1 (a) values (1),(2); truncate table t1; select count(*) from t1; insert into t1 values(1,2,"test"); select count(*) from t1; +delete from t1; +select * from t1; drop table t1; # The following should fail -!$1146 select count(*) from t1; +--error 1146 +select count(*) from t1; +create temporary table t1 (n int); +insert into t1 values (1),(2),(3); +truncate table t1; +select * from t1; +drop table t1; diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test index 626dedad547..a516bc89f99 100644 --- a/mysql-test/t/type_datetime.test +++ b/mysql-test/t/type_datetime.test @@ -32,6 +32,15 @@ select * from t1 where a is null; drop table t1; # +# Test with bug when propagating DATETIME to integer and WHERE optimization +# + +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"); +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'); +drop table t1; + +# # Test of datetime optimization # diff --git a/mysql-test/t/type_decimal.test b/mysql-test/t/type_decimal.test index 7d4dcf3403e..683ed7239e6 100644 --- a/mysql-test/t/type_decimal.test +++ b/mysql-test/t/type_decimal.test @@ -160,6 +160,8 @@ insert into t1 values ("-.1"),("+.1"),(".1"); insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001"); insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11"); insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11"); +insert into t1 values ("1e+1000"),("1e-1000"),("-1e+1000"); +insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0"); select * from t1; drop table t1; @@ -169,6 +171,8 @@ insert into t1 values ("-.1"),("+.1"),(".1"); insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001"); insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11"); insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11"); +insert into t1 values ("1e+1000"),("1e-1000"),("-1e+1000"); +insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0"); select * from t1; drop table t1; @@ -178,16 +182,21 @@ insert into t1 values ("-.1"),("+.1"),(".1"); insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001"); insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11"); insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11"); +insert into t1 values ("1e+1000"),("1e-1000"),("-1e+1000"); +insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0"); select * from t1; drop table t1; create table t1 (a decimal(10,2)); +# The -0.0 needs to be quoted as not all platforms supports this insert into t1 values (0.0),("-0.0"),(+0.0),(01.0),(+01.0),(-01.0); insert into t1 values (-.1),(+.1),(.1); insert into t1 values (00000000000001),(+0000000000001),(-0000000000001); insert into t1 values (+111111111.11),(111111111.11),(-11111111.11); insert into t1 values (-111111111.11),(+1111111111.11),(1111111111.11); +insert into t1 values (1e+1000),(1e-1000),(-1e+1000); +insert into t1 values (123.4e0),(123.4e+2),(123.4e-2),(123e1),(123e+0); select * from t1; drop table t1; diff --git a/mysql-test/t/type_enum.test b/mysql-test/t/type_enum.test index 6e90ae437f8..8f399e4364e 100644 --- a/mysql-test/t/type_enum.test +++ b/mysql-test/t/type_enum.test @@ -2,6 +2,7 @@ # Problem with many enums # +DROP TABLE if exists t1; CREATE TABLE t1 ( field enum('001001','001004','001010','001018','001019','001020','001021','001027','001028','001029','001030','001031','001100','002003','002004','002005','002007','002008','002009','002012','002013','002014','003002','003003','003004','003005','003006','003007','003008','003009','003010','003011','003012','003013','003014','003015','003016','003017','003018','003019','004002','004003','004005','004006','004007','004008','004010','004012','004014','004016','004017','004020','004021','004022','004023','004024','004025','004026','006002','006004','006006','006010','006011','006012','006013','006014','007001','007002','007003','007004','007005','007006','007007','007008','007009','007010','007011','007012','007013','007014','007015','007016','007017','007018','007019','007020','007021','007022','007023','007024','007025','007026','007027','007028','007029','007030','007031','007032','007033','007034','007035','007036','007037','007038','007039','007040','007043','007044','009001','009002','009004','009005','009006','009007','009008','009009','009010','009011','009012','009013','010002','010003','010004','010005','010006','010007','010008','010009','010010','010011','010012','010013','010015','010016','010017','010018','010019','010020','010021','010022','010023','010024','010025','010026','010027','010028','011001','011002','011003','011004','011006','011012','011013','011014','011015','011016','012017','012018','012019','012023','012027','012028','012029','012030','012031','012032','012033','012034','012035','012036','012037','012038','012039','014001','016002','016003','016004','016007','016010','016011','016016','016019','016020','016021','016022','016023','016024','016026','016027','016028','016029','016030','016031','016032','016033','016034','017002','018001','019002','019004','020001','020003','020004','020005','020006','020007','020008','020009','022001','022002','022003','023001','023002','023003','023004','023005','023006','023007','023008','023010','023011','023012','023017','023019','023020','023021','023025','023026','023027','023028','023029','023030','023031','023032','023033','023034','023035','025001','025003','025004','025005','025006','025007','025008','025009','025010','025011','025012','025013','025014','025015','025016','025017','025018','025019','025020','025021','025022','025023','025024','025025','025026','025027','025028','025029','025030','025031','025032','025033','025034','025035','025036','025037','025038','025039','025040','025041','025042','025043','025044','025045','025046','025047','025048','025049','025050','025051','025052','025053','025054','025055','025056','025057','025058','025059','025060','025061','025062','025063','027001','027002','027011','035008','035012','036001','037001','037003','037004','037005','037006','037007','037008','037009','038004','038005','038006','038007','038009','039001','039002','039003','039004','039005','039006','046001','046002','046003','046004','046005','046007','046008','046009','046010','046011','046012','046013','046014','047001','047002','048001','051001','051002','051003','051004','052001','052002','052005','053015','053016','053019','053020','053023','053024','053026','053028','053029','053033','053034','053036','053037','053038','053039','053041','053042','053043','053045','053046','053047','053048','053051','053052','053054','053055','053056','053057','053068','053069','053070','053073','053074','053075','053086','053094','053095','053096','053097','053098','053099','053100','053101','053102','053103','053104','053105','053107','053122','053123','053124','053125','053127','053128','054001','054002','054003','054004','054005','054006','054007','054009','054010','056001','056002','056003','056004','056005','056006','056009','056010','056011','056016','056017','056018','056019','056020','056021','056022','057001','057002','057003','057004','058002','058003','058004','058005','060001','060003','060004','060005','060006','060007','061002','061003','061004','061005','061006','069006','069007','069010','069011','069012','069013','069014','069015','069016','069017','069018','069020','069021','069022','069023','069024','071002','071003','071004','071005','071006','071008','071011','071013','071020','071021','071022','072001','073001','073002','073003','073004','074001','074002','074003','074004','074005','074006','074007','074008','074009','074010','074011','074012','075001','075007','076101','076102','076103','077001','077002','077003','077004','077006','077007','077008','077009','078005','079002','079003','079004','079005','079006','079007','081001','082006','082007','082011','082013','082014','082015','082016','082017','082021','082022','082023','082024','082025','082026','082027','082028','082029','082030','082031','082032','082033','082034','082035','082036','082037','082038','082039','082040','082041','082042','082043','082044','084001','084002','084003','084004','084005','084007','084008','084009','084011','084013','084014','084016','084017','084027','084031','084032','084033','084035','084036','084037','084038','084039','084040','084041','084042','084043','084044','084045','084046','084047','084048','084049','084050','084051','085001','085002','085003','085004','085005','085006','085007','085009','085011','085012','085013','085014','085015','085016','085017','085018','085019','085020','085021','085022','085023','085028','085029','085030','085031','085033','085034','085035','085036','085037','085038','085040','085041','085042','085043','085044','085045','085046','085047','085048','085063','085064','085065','085068','085070','085071','085073','085082','085083','085086','085088','085089','085090','085091','085092','085093','085094','085095','085096','085097','085098','085099','085100','085101','085102','085103','085104','085105','085106','085107','085108','085109','085110','085111','085112','085113','085115','085119','085120','085121','085122','085123','085124','085125','085126','085127','085128','085129','085130','085132','085133','085134','085135','085136','085137','086001','086002','086003','086004','086005','088001','088003','088005','088006','088007','088008','088009','089001','090001','090002','090003','090004','090005','090006','090007','090008','090009','090010','090013','090015','090016','090017','090018','090019','090022','090027','090028','091001','091002','091005','091008','091009','091010','091011','091012','091013','091014','091015','091016','091017','091018','093001','093003','093098','093100','093102','093104','093141','093142','093146','093151','093153','093167','093168','093176','094001','094002','094004','094005','095004','099001','099002','100001','101001','102002','102003','105001','105002','106001','113001','113002','113003','113004','113005','113006','113007','113008','113009','113010','113011','113012','113013','113014','113015','113016','113017','113018','113019','113020','113021','113022','113023','113024','113025','113026','113027','113028','114001','115001','115002','115003','115004','115005','115006','115007','115008','115009','115010','115011','115012','115013','115014','115015','115016','115017','115018','115020','115021','115022','115023','115025','115026','115027','115028','115029','115030','115031','115032','115033','115034','115035','115036','115039','115040','115041','115042','115043','115044','115045','115046','115047','115048','115049','115050','115051','115052','115053','115054','115055','115056','115057','115059','115060','115061','115062','115063','115064','115065','115066','115067','115068','115069','115070','115071','115072','115073','115075','115076','115081','115082','115085','115086','115087','115088','115095','115096','115097','115098','115099','115101','115102','115103','115104','115105','115106','115108','115109','115110','115111','115112','115113','115114','115115','115116','115117','115118','115119','115120','115121','115122','116001','116002','116003','116004','116005','116006','116007','116008','116009','116010','116011','116012','117001','117002','117003','123001','124010','124014','124015','124019','124024','124025','124026','124027','124028','124029','124030','124031','124032','124033','124035','124036','124037','124038','124039','124040','124041','124042','124043','124044','124045','124046','124047','124048','124049','124050','124051','124052','124053','124054','124055','124056','124057','124058','124059','124060','124061','124062','124063','124064','124065','126001','126002','126003','126004','126005','126006','126007','126008','126009','126010','126011','126012','130001','132001','132002','132003','133001','133008','133009','133010','133011','133012','133013','133014','133015','133016','133017','133018','133019','133020','133021','133022','133023','133024','133025','133027','133028','133029','133030','133031','134001','135001','135002','135003','135004','135005','135006','135007','135008','135009','135010','136001','137009','137010','137011','137012','137013','137014','137015','137016','137017','137018','137019','138001','138002','138003','138004','139001','139003','140001','141001','141002','141003','141006','141007','141008','141009','141011','141012','141014','141015','141016','141017','141018','141019','141020','141021','141022','141023','141024','141025','141026','141027','141028','142001','142002','142003','142004','142005','142006','142007','142008','142010','142011','142012','144001','145001','145002','145003','145004','145005','145006','145007','145008','145009','145010','145011','145012','145013','145014','145015','145016','147001','150003','150005','150009','150013','150014','150015','150016','150017','150020','150021','152001','152002','152003','152004','152005','152006','152007','154001','154002','154003','155001','155002','155003','155004','155005','155006','159001','159002','159003','159004','160001','160002','160003','161001','162001','162002','162003','162004','162007','162010','162011','162012','163001','163002','163003','163005','163010','163011','163014','163015','163016','165001','165002','165003','165004','165005','165006','165007','165008','165009','165010','165011','165012','165013','165014','165015','165016','165017','165018','165019','165020','165021','165022','165023','165024','165025','165026','165027','165028','165029','165030','165031','165032','165033','165034','165035','165036','167001','168001','168002','168003','168004','168005','168007','168008','168009','168010','168011','168012','168013','168014','169001','169002','169003','169007','169008','169009','169010','170001','171001','171002','171003','171004','171005','171006','171007','171008','171009','172001','174001','174002','174003','176001','176002','176003','177001','177002','179001','179002','179003','179004','179005','179006','179007','179008','179009','179010','179011','179012','179013','179014','179015','179016','179017','179018','179019','179020','179021','179022','179023','179024','179025','179026','179027','179028','179029','179030','179031','179032','179033','179034','179035','179036','179037','179038','179039','179040','179041','179042','179043','179044','179045','179046','179047','180001','180010','180012','180013','180014','180015','180016','180017','180018','180019','180020','180021','180022','180023','180024','180025','180026','180027','180028','180030','180031','180032','180033','180034','180035','180036','180037','180038','180039','180041','180042','180043','180044','180045','180046','180047','180048','180049','180050','180051','180052','180053','180054','180055','180056','180057','180058','180059','180060','180061','180062','180063','180064','180065','180066','180067','180068','180069','180070','180071','182001','184001','184002','184005','184006','184007','184008','184009','184010','184011','185001','185003','187001','188001','188002','188003','188004','188005','188006','188007','188008','188009','188010','188011','191001','191002','192002','194001','194002','194003','194004','194005','194006','194007','195001','195002','195003','195004','195005','195006','195007','196001','196002','197001','197002','197003','197004','197005','197006','198001','198003','198004','198005','198006','198007','198008','198009','198010','198011','198012','198013','198014','198015','198016','198017','201001','201002','201005','202001','203001','203002','203003','203017','203018','203019','204001','204002','204003','205001','208001','208002','208003','208004','208005','209001','209002','209003','210001','210002','210003','210004','210005','210006','210007','210008','210009','210010','210011','210012','210013','211017','212001','212002','212003','212004','212005','212006','212007','212008','212009','212010','212011','212012','212013','218001','218003','218004','218006','218007','218008','218009','218011','218015','218016','218017','218018','218019','218020','218021','218022','218023','218024','218025','218026','218027','218028','218029','218030','218031','218032','218033','218034','218035','218036','221001','221002','221003','221004','221005','221006','221007','221008','221009','221010','221011','221012','221013','223001','223002','223003','224001','224002','224003','224006','224007','224008','225001','225002','225003','225004','225005','225006','225007','225008','225009','225010','225011','225012','225013','226001','226002','226003','226004','226005','226006','226007','226008','226009','227001','227002','227003','227004','227005','227006','227007','227008','227009','227010','227011','227012','227013','227014','227015','227016','227017','227018','227019','227020','227021','227022','227023','227024','227025','227026','227027','227028','227029','227030','227031','227032','227033','227034','227035','227036','227037','227038','227039','227040','227041','227042','227043','227044','227045','227046','227047','227048','227049','227050','227051','227052','227053','227054','227055','227056','227057','227058','227059','227060','227061','227062','227063','227064','227065','227066','227067','227068','227069','227070','227071','227072','227073','227074','227075','227076','227077','227078','227079','227080','227081','227082','227083','227084','227085','227086','227087','227088','227089','227090','227091','227092','227093','227094','227095','227096','227097','227098','227099','227100','227101','227102','227103','227104','227105','227106','227107','227108','227109','227110','227111','227112','227113','227114','227115','227116','227117','227118','227119','227120','227122','227123','227124','227125','227126','227127','227128','227129','227130','227131','227132','227133','227134','227135','227136','227137','227138','227139','227140','227141','227142','227143','227144','227145','227146','227147','227148','227149','227150','227151','227152','228001','229001','229002','229003','229004','229005','230001','230002','232001','233001','233002','233003','233004','233005','233006','233007','233008','234001','234002','234003','234004','234005','234006','234007','234008','234009','234010','234011','234012','234013','234014','234015','234016','234017','234018','234019','234020','234021','234022','234023','234024','234025','234026','234027','234028','234029','234030','235001','235002','235003','235004','235005','236001','236002','236003','237001','238002','238003','238004','238005','238006','238007','238008','333013','333014','333015','333016','333017','333018','333019','333020','333021','333022','333023','333024','333025','333030','333031','333032','333033','333034','333035','334001','334002','334003','334004','334005','334006','334007','336004','337001','337002','337003','337004','339001','339002','343001','344001','344002','344003','344004','344005','345001','345002','345003','347001','347002','348001','348002','348003','348004','348005','349001','349002','349003','350001','353001','353002','353003','353004','355001','355002','355003','355004','355005','355006','356001','358001','359001','359002','360001','360002','360003','360004','360005','366001','366002','366003','366004','369001','373001','373002','373003','373004','373005','373006','373007','373008','373009','373010','373011','373012','373013','373014','373015','373016','373017','373018','373019','373020','373021','374001','374002','374003','374004','374005','374006','374007','374008','374009','374010','374011','374012','374013','374014','374015','374016','376001','376002','376003','376004','376005','376006','376007','376008','376009','376010','376011','376012','376013','376016','376017','376018','376019','376020','376021','379003','382001','382002','383001','384001','384002','385001','385002','386001','386002','386003','386004','386005','386006','386007','386008','386009','386010','386011','386012','386013','386014','387001','389001','389002','389003','389004','392001','393001','393002','393003','393004','395001','396001','397001','397002','399001','399002','399003','400001','400002','401001','401002','401003','402001','402002','402003','402004','402005','403001','403002','403003','504001','504002','504004','504005','504006','504007','504008','504009','504010','504011','504012','504013','504014','504017','504018','504019','504021','504022','504023','504024','504025','506001','506002','508001','508002','511001','511002','511003','511004','511005','511006','511007','511008','511009','511010','511011','511012','511013','511014','511017','511018','511020','511021','511022','511024','511028','511029','513001','513002','513003','513004','514001','515001','515002','515003','515007','515008','515009','515010','515011','515012','515013','515014','515015','518001','518002','518003','520001','520002','521001','521002','521003','521004','521005','521006','521007','521008','521009','521010','521011','521012','521013','521014','521015','521016','523001','523002','523003','523004','523005','523006','523007','524001','700001','701001','701002','701003','702001','702002','702003','702004','702005','702006','702007','702008','703001','703002','703003','704001','704002','704003','704004','705001','706001','706002','707001','707002','707003','708001','709001','709002','710001','710002','711001','711002','712001','713001','713002','714001','714002','715001','716001','718001','718002','719001','719002','991001','991002','991003','991004','991005','991006','991007','991008','992001','995001','996001','996002','996003','998001','998002','998003','998004','998005','998006','998007','999001','999002','011017','011018','034001','034002','071010','208006','239001','519001','519003','126013','184012','053071','374017','374018','374019','374020','374021','404001','405002','405001','405003','405007','405006','405005','405004','240011','240010','240009','240008','240007','240006','240005','240004','240003','240002','240001','240012','240013','240014','240015','240016','240017','357001','235006','235007','712002','355008','355007','056023','999999','046015','019005','126014','241003','241002','241001','240018','240020','240019','242001','242002','242003','242004','242005','242006','089002','406001','406002','406003','406004','406005','406006','243001','243002','243003','243004','243005','243006','243007','243008','010030','010029','407001','407006','407005','407004','407003','407002','408001','366005','133032','016035','077010','996004','025064','011019','407007','407008','407009','409001','115123','504026','039007','039009','039008','039010','039011','039012','180072','240021','240023','408002','405008','235008','525001','525002','525003','525004','410001','410002','410003','410004','410005','410006','410007','410008','410009','410010','410011','410012','410013','410014','410015','410016','344006','240031','240030','240029','240028','240027','240026','240025','240024','240034','240033','240032','410017','410018','411001','411002','411003','411004','411005','411006','411007','411008','203020','203021','203022','412001','412002','412003','412004','069025','244001','244002','244009','244008','244007','244006','244005','244004','244003','244015','244014','244013','244012','244011','244010','244016','244017','240042','240041','240040','240039','240038','240037','240036','240035','405009','405010','240043','504034','504033','504032','504031','504030','504029','504028','504027','504042','504041','504040','504039','504038','504037','504036','504035','800001','410019','410020','410021','244018','244019','244020','399004','413001','504043','198018','198019','344007','082045','010031','010032','010033','010034','010035','504044','515016','801002','801003','801004','801005','802001','801001','414001','414002','414003','141029','141030','803001','803002','803003','803004','803005','803006','803007','803008','803009','803010','803011','803012','803013','803014','803015','803016','803017','410022','410023','803018','803019','803020','415002','415001','244021','011020','011023','011022','011021','025065','165037','165038','165039','416001','416002','416003','417001','418001','504045','803022','803021','240022','419001','420001','804010','804009','804008','804007','804006','804005','804004','804003','804002','804001','804020','804019','804018','804017','804016','804015','804014','804013','804012','804011','804024','804021','804023','804022','511019','511016','511015','511032','511031','511030','511027','511026','511025','511033','511023','133034','133033','169011','344008','344009','244022','244026','244025','244030','244023','244024','244027','244028','244029','244031','082046','082047','082048','126015','126016','416004','416005','421001','421002','016037','016036','115124','115125','115126','240049','240048','240047','240046','240045','240044','244032','244033','422001','422002','422003','422004','422005','184013','239002','805001','805002','805003','805004','805005','056024','423001','344010','235009','212014','056025','056026','802002','244034','244035','244036','244037','244038','244039','515017','504046','203015','245002','245001','071023','056027','056028','056029','056030','056031','056032','424001','056034','056033','805006','805007','805008','805009','805010','422008','422007','422006','422010','422009','422011','209004','150022','150023','100002','056035','023036','185004','185005','246001','247001','247002','425001','416006','165042','165041','165040','165043','010040','010039','010038','010037','010036','422012','422013','422014','422015','426000','248001','248002','248003','248004','248005','249001','249002','249003','249004','249005','249006','250007','250001','250002','250003','250004','250005','250006','250008','250009','250010','250011','250012','250013','251001','251002','422016','422017','422018','806001','806002','116013','235010','235011','091026','091027','091028','091029','091019','091020','091021','091022','091023','091024','091025','252001','243009','249007','249008','249009','011024','011025','427001','428002','428001','169012','429001','429002','429003') DEFAULT '001001' NOT NULL, KEY field (field) diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test new file mode 100644 index 00000000000..6e6b43ebe3b --- /dev/null +++ b/mysql-test/t/union.test @@ -0,0 +1,100 @@ +# +# Test of unions +# + +drop table if exists t1,t2,t3; +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'); + +select a,b from t1 union select a,b from t2; +select a,b from t1 union all select a,b from t2; +select a,b from t1 union all select a,b from t2 order by b; +select a,b from t1 union all select a,b from t2 union select 7,'g'; +select 0,'#' union select a,b from t1 union all select a,b from t2 union select 7,'gg'; +select a,b from t1 union select a,b from t1; +select 't1',b,count(*) from t1 group by b UNION select 't2',b,count(*) from t2 group by b; + +#test alternate syntax for unions +(select a,b from t1 limit 2) union all (select a,b from t2 order by a) limit 4; +(select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1); +(select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by b desc; +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(); + +# +# Test some error conditions with UNION +# + +explain select a,b from t1 union all select a,b from t2; + +--error 1054 +explain select xx from t1 union select 1; +explain select a,b from t1 union select 1; +explain select 1 union select a,b from t1 union select 1; +explain select a,b from t1 union select 1 limit 0; + +--error 1221 +select a,b from t1 into outfile 'skr' union select a,b from t2; + +--error 1221 +select a,b from t1 order by a union select a,b from t2; + +--error 1221 +insert into t3 select a from t1 order by a union select a from t2; + +--error 1222 +create table t3 select a,b from t1 union select a from t2; + +--error 1222 +select a,b from t1 union select a from t2; + +--error 1222 +select * from t1 union select a from t2; + +--error 1222 +select a from t1 union select * from t2; + +--error 1234 +select * from t1 union select SQL_BUFFER_RESULT * from t2; + +# Test CREATE, INSERT and REPLACE +create table t3 select a,b from t1 union all select a,b from t2; +insert into t3 select a,b from t1 union all select a,b from t2; +replace into t3 select a,b as c from t1 union all select a,b from t2; + +drop table t1,t2,t3; + +# +# Test bug reported by joc@presence-pc.com +# + +CREATE TABLE t1 ( + `pseudo` char(35) NOT NULL default '', + `pseudo1` char(35) NOT NULL default '', + `same` tinyint(1) unsigned NOT NULL default '1', + PRIMARY KEY (`pseudo1`), + KEY `pseudo` (`pseudo`) +) TYPE=MyISAM; +INSERT INTO t1 (pseudo,pseudo1,same) VALUES ('joce', 'testtt', 1),('joce', 'tsestset', 1),('dekad', 'joce', 1); +SELECT pseudo FROM t1 WHERE pseudo1='joce' UNION SELECT pseudo FROM t1 WHERE pseudo='joce'; +SELECT pseudo1 FROM t1 WHERE pseudo1='joce' UNION SELECT pseudo1 FROM t1 WHERE pseudo='joce'; +SELECT * FROM t1 WHERE pseudo1='joce' UNION SELECT * FROM t1 WHERE pseudo='joce' order by pseudo desc,pseudo1 desc; +SELECT pseudo1 FROM t1 WHERE pseudo='joce' UNION SELECT pseudo FROM t1 WHERE pseudo1='joce'; +SELECT pseudo1 FROM t1 WHERE pseudo='joce' UNION ALL SELECT pseudo FROM t1 WHERE pseudo1='joce'; +SELECT pseudo1 FROM t1 WHERE pseudo='joce' UNION SELECT 1; +drop table t1; +drop table if exists t1,t2; +create table t1 (a int); +create table t2 (a int); +insert into t1 values (1),(2),(3),(4),(5); +insert into t2 values (11),(12),(13),(14),(15); +(select * from t1 limit 2) union (select * from t2 limit 3) limit 4; +(select * from t1 limit 2) union (select * from t2 limit 3); +(select * from t1 limit 2) union (select * from t2 limit 20,3); +set SQL_SELECT_LIMIT=2; +(select * from t1 limit 1) union (select * from t2 limit 3); +set SQL_SELECT_LIMIT=DEFAULT; +drop table t1,t2; diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test index 6f446a11521..5cbbd2a350e 100644 --- a/mysql-test/t/update.test +++ b/mysql-test/t/update.test @@ -76,3 +76,14 @@ alter table t1 change lfdnr lfdnr int(10) unsigned default 0 not null auto_incre update t1 set status=1 where type='Open'; select status from t1; drop table t1; + +# +# Test of ORDER BY +# + +create table t1 (a int not null, b int not null); +insert into t1 values (1,1),(1,2),(1,3); +update t1 set b=4 where a=1 order by b asc limit 1; +update t1 set b=4 where a=1 order by b desc limit 1; +select * from t1; +drop table t1; diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test index 70283446fb3..bb1a9b59611 100644 --- a/mysql-test/t/user_var.test +++ b/mysql-test/t/user_var.test @@ -1,4 +1,4 @@ -error 1204; +error 1054; set @a := foo; set @a := connection_id() + 3; select @a - connection_id(); diff --git a/mysql-test/t/varbinary.test b/mysql-test/t/varbinary.test index 0ab26f51e65..6d2660271ea 100644 --- a/mysql-test/t/varbinary.test +++ b/mysql-test/t/varbinary.test @@ -4,6 +4,7 @@ select 0x41,0x41+0,0x41 | 0x7fffffffffffffff | 0,0xffffffffffffffff | 0 ; select 0x31+1,concat(0x31)+1,-0xf; +select x'31',X'ffff'+0; # # Test of hex constants in WHERE: @@ -14,3 +15,18 @@ insert into t1 set UNIQ=0x38afba1d73e6a18a; insert into t1 set UNIQ=123; explain select * from t1 where UNIQ=0x38afba1d73e6a18a; drop table t1; + +# +# Test error conditions +# +--error 1064 +select x'hello'; +--error 1054 +select 0xfg; + +# +# Test likely error conditions +# +create table t1 select 1 as x, 2 as xx; +select x,xx from t1; +drop table t1; diff --git a/mysql-test/t/variables-master.opt b/mysql-test/t/variables-master.opt new file mode 100644 index 00000000000..5851d32ec31 --- /dev/null +++ b/mysql-test/t/variables-master.opt @@ -0,0 +1 @@ +max_join_size=10 diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index b3127402238..967c21f7eab 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -32,9 +32,200 @@ drop table t1; # Test system variables # +set max_join_size=100; +show variables like 'max_join_size'; +show global variables like 'max_join_size'; +set GLOBAL max_join_size=2000; +show global variables like 'max_join_size'; +set max_join_size=DEFAULT; +show variables like 'max_join_size'; +set GLOBAL max_join_size=DEFAULT; +show global variables like 'max_join_size'; +set @@max_join_size=1000, @@global.max_join_size=2000; +select @@local.max_join_size, @@global.max_join_size; +select @@identity, length(@@version)>0; select @@VERSION=version(); select last_insert_id(345); -select @@IDENTITY,last_insert_id(); -select @@identity; +select @@IDENTITY,last_insert_id(), @@identity; + +set big_tables=OFF, big_tables=ON, big_tables=0, big_tables=1, big_tables="OFF", big_tables="ON"; + +set global concurrent_insert=ON; +show variables like 'concurrent_insert'; +set global concurrent_insert=1; +show variables like 'concurrent_insert'; +set global concurrent_insert=0; +show variables like 'concurrent_insert'; +set global concurrent_insert=OFF; +show variables like 'concurrent_insert'; +set global concurrent_insert=DEFAULT; +show variables like 'concurrent_insert'; + +set table_type=MYISAM, table_type="HEAP", global table_type="INNODB"; +show local variables like 'table_type'; +show global variables like 'table_type'; +set GLOBAL query_cache_size=100000; +set GLOBAL safe_show_database=0; + +set myisam_max_sort_file_size=10000, GLOBAL myisam_max_sort_file_size=20000; +show variables like 'myisam_max_sort_file_size'; +show global variables like 'myisam_max_sort_file_size'; +set myisam_max_sort_file_size=default; +show variables like 'myisam_max_sort_file_size'; + +set global net_buffer_length=1024, net_write_timeout=200, net_read_timeout=300; +set session net_buffer_length=2048, net_write_timeout=500, net_read_timeout=600; +show global variables like 'net_%'; +show session variables like 'net_%'; +set session net_buffer_length=8000, global net_read_timeout=900, net_write_timeout=1000; +show global variables like 'net_%'; +show session variables like 'net_%'; +set net_buffer_length=1; +show variables like 'net_buffer_length'; +set net_buffer_length=2000000000; +show variables like 'net_buffer_length'; + +set GLOBAL character set cp1251_koi8; +show global variables like "convert_character_set"; +set character set cp1251_koi8; +show variables like "convert_character_set"; +set global character set default, session character set default; +show variables like "convert_character_set"; +select @@timestamp>0; + +# The following should give errors + +--error 1231 +set big_tables=OFFF; +--error 1231 +set big_tables="OFFF"; --error 1193 -select @@unknown_variable; +set unknown_variable=1; +--error 1232 +set max_join_size="hello"; +--error 1231 +set table_type=UNKNOWN_TABLE_TYPE; +--error 1231 +set table_type=INNODB, big_tables=2; +show local variables like 'table_type'; +--error 1229 +set SESSION query_cache_size=10000; +--error 1230 +set GLOBAL table_type=DEFAULT; +--error 1115 +set convert_character_set=UNKNOWN_CHARACTER_SET; +--error 1115 +set character set unknown; +--error 1232 +set character set 0; +--error 1228 +set global autocommit=1; +--error 1228 +select @@global.timestamp; +--error 1193 +set @@version=''; +--error 1229 +set @@concurrent_insert=1; +--error 1228 +set @@global.sql_auto_is_null=1; +--error 1228 +select @@global.sql_auto_is_null; + +# Test setting all variables + +set autocommit=1; +set big_tables=1; +select @@autocommit, @@big_tables; +set global binlog_cache_size=100; +set bulk_insert_buffer_size=100; +set convert_character_set=cp1251_koi8; +set convert_character_set=default; +set @@global.concurrent_insert=1; +set global connect_timeout=100; +set global delay_key_write=1; +set global delayed_insert_limit=100; +set global delayed_insert_timeout=100; +set global delayed_queue_size=100; +set global flush=1; +set global flush_time=100; +set insert_id=1; +set interactive_timeout=100; +set join_buffer_size=100; +set last_insert_id=1; +set global local_infile=1; +set long_query_time=100; +set low_priority_updates=1; +set max_allowed_packet=100; +set global max_binlog_cache_size=100; +set global max_binlog_size=100; +set global max_connect_errors=100; +set global max_connections=100; +set global max_delayed_threads=100; +set max_heap_table_size=100; +set max_join_size=100; +set max_sort_length=100; +set max_tmp_tables=100; +set global max_user_connections=100; +select @@max_user_connections; +set global max_write_lock_count=100; +set myisam_max_extra_sort_file_size=100; +select @@myisam_max_extra_sort_file_size; +set myisam_max_sort_file_size=100; +set myisam_sort_buffer_size=100; +set net_buffer_length=100; +set net_read_timeout=100; +set net_write_timeout=100; +set global query_cache_limit=100; +set global query_cache_size=100; +set global query_cache_type=demand; +set read_buffer_size=100; +set read_rnd_buffer_size=100; +set global rpl_recovery_rank=100; +set global safe_show_database=1; +set global server_id=100; +set global slave_net_timeout=100; +set global slow_launch_time=100; +set sort_buffer_size=100; +set sql_auto_is_null=1; +select @@sql_auto_is_null; +set @@sql_auto_is_null=0; +select @@sql_auto_is_null; +set sql_big_selects=1; +set sql_big_tables=1; +set sql_buffer_result=1; +set sql_log_bin=1; +set sql_log_off=1; +set sql_log_update=1; +set sql_low_priority_updates=1; +set sql_max_join_size=200; +select @@sql_max_join_size,@@max_join_size; +set sql_quote_show_create=1; +set sql_safe_updates=1; +set sql_select_limit=1; +set global sql_slave_skip_counter=100; +set sql_warnings=1; +set global table_cache=100; +set table_type=myisam; +set global thread_cache_size=100; +set timestamp=1, timestamp=default; +set tmp_table_size=100; +set tx_isolation="READ-COMMITTED"; +set wait_timeout=100; +set log_warnings=1; + +# +# key buffer +# + +DROP TABLE IF EXISTS t1,t2; +create table t1 (a int not null auto_increment, primary key(a)); +create table t2 (a int not null auto_increment, primary key(a)); +insert into t1 values(null),(null),(null); +insert into t2 values(null),(null),(null); +set global key_buffer_size=100000; +select @@key_buffer_size; +select * from t1 where a=2; +select * from t2 where a=3; +check table t1,t2; +drop table t1,t2; + |