diff options
Diffstat (limited to 'mysql-test/t')
34 files changed, 548 insertions, 93 deletions
diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index c013b2251a9..8da017e8acb 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -244,6 +244,23 @@ LOCK TABLES t1 WRITE; ALTER TABLE t1 DISABLE KEYS; SHOW INDEX FROM t1; DROP TABLE t1; +# +# Bug #2628: 'alter table t1 rename mysqltest.t1' silently drops mysqltest.t1 +# if it exists +# +create table t1 (name char(15)); +insert into t1 (name) values ("current"); +create database mysqltest; +create table mysqltest.t1 (name char(15)); +insert into mysqltest.t1 (name) values ("mysqltest"); +select * from t1; +select * from mysqltest.t1; +--error 1050 +alter table t1 rename mysqltest.t1; +select * from t1; +select * from mysqltest.t1; +drop table t1; +drop database mysqltest; # # Bug 2361 diff --git a/mysql-test/t/bdb.test b/mysql-test/t/bdb.test index 12b81f27147..6823dd23b73 100644 --- a/mysql-test/t/bdb.test +++ b/mysql-test/t/bdb.test @@ -808,3 +808,17 @@ insert into t2 select * from t1; delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b; drop table t1,t2; + +# +# Test index only read (Bug #2509) +# +create table t1 (a char(10), key(a), b int not null, key(b)) engine=bdb; +insert into t1 values ('a',1),('A',2); +explain select a from t1; +select a from t1; +explain select b from t1; +select b from t1; +alter table t1 modify a char(10) binary; +explain select a from t1; +select a from t1; +drop table t1; diff --git a/mysql-test/t/bigint.test b/mysql-test/t/bigint.test index 5c977983087..9bbe10eae8c 100644 --- a/mysql-test/t/bigint.test +++ b/mysql-test/t/bigint.test @@ -68,3 +68,6 @@ insert into t1 values ('10000000000000000000'); select * from t1; drop table t1; +# atof() behaviour is different of different systems. to be fixed in 4.1 +#SELECT '0x8000000000000001'+0; + diff --git a/mysql-test/t/ctype_tis620-master.opt b/mysql-test/t/ctype_tis620-master.opt new file mode 100644 index 00000000000..69d47c06e42 --- /dev/null +++ b/mysql-test/t/ctype_tis620-master.opt @@ -0,0 +1 @@ +--default-character-set=tis620 diff --git a/mysql-test/t/ctype_tis620.test b/mysql-test/t/ctype_tis620.test index 82c660dfea0..7314b52e301 100644 --- a/mysql-test/t/ctype_tis620.test +++ b/mysql-test/t/ctype_tis620.test @@ -1,64 +1,23 @@ --- source include/have_tis620.inc - -# -# Tests with the big5 character set -# --disable_warnings drop table if exists t1; --enable_warnings -# -# Bug 1552: tis620 <-> unicode conversion crashed -# Check tis620 -> utf8 -> tis620 round trip conversion -# +CREATE TABLE t1 ( + recid int(11) NOT NULL auto_increment, + dyninfo text, + PRIMARY KEY (recid) +) ENGINE=MyISAM; -SET @pl0= _tis620 0x000102030405060708090A0B0C0D0E0F; -SET @pl1= _tis620 0x101112131415161718191A1B1C1D1E1F; -SET @pl2= _tis620 0x202122232425262728292A2B2C2D2E2F; -SET @pl3= _tis620 0x303132333435363738393A3B3C3D3E3F; -SET @pl4= _tis620 0x404142434445464748494A4B4C4D4E4F; -SET @pl5= _tis620 0x505152535455565758595A5B5C5D5E5F; -SET @pl6= _tis620 0x606162636465666768696A6B6C6D6E6F; -SET @pl7= _tis620 0x707172737475767778797A7B7C7D7E7F; -SET @pl8= _tis620 0x808182838485868788898A8B8C8D8E8F; -SET @pl9= _tis620 0x909192939495969798999A9B9C9D9E9F; -SET @plA= _tis620 0xA0A1A2A3A4A5A6A7A8A9AAABACADAEAF; -SET @plB= _tis620 0xB0B1B2B3B4B5B6B7B8B9BABBBCBDBEBF; -SET @plC= _tis620 0xC0C1C2C3C4C5C6C7C8C9CACBCCCDCECF; -SET @plD= _tis620 0xD0D1D2D3D4D5D6D7D8D9DADBDCDDDEDF; -SET @plE= _tis620 0xE0E1E2E3E4E5E6E7E8E9EAEBECEDEEEF; -SET @plF= _tis620 0xF0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF; +INSERT INTO t1 VALUES (1,'color=\"STB,NPG\"\r\nengine=\"J30A13\"\r\nframe=\"MRHCG1640YP4\"\r\ngrade=\"V6\"\r\nmodel=\"ACCORD\"\r\nmodelcode=\"CG164YEN\"\r\ntype=\"VT6\"\r\n'); +INSERT INTO t1 VALUES (2,'color=\"HTM,NPG,DEG,RGS\"\r\nengine=\"F23A5YP1\"\r\nframe=\"MRHCF8640YP3\"\r\ngrade=\"EXi AT\"\r\nmodel=\"ACCORD\"\r\nmodelcode=\"CF864YE\"\r\ntype=\"EXA\"\r\n'); -SELECT hex(@u0:=convert(@pl0 using utf8)); -SELECT hex(@u1:=convert(@pl1 using utf8)); -SELECT hex(@u2:=convert(@pl2 using utf8)); -SELECT hex(@u3:=convert(@pl3 using utf8)); -SELECT hex(@u4:=convert(@pl4 using utf8)); -SELECT hex(@u5:=convert(@pl5 using utf8)); -SELECT hex(@u6:=convert(@pl6 using utf8)); -SELECT hex(@u7:=convert(@pl7 using utf8)); -SELECT hex(@u8:=convert(@pl8 using utf8)); -SELECT hex(@u9:=convert(@pl9 using utf8)); -SELECT hex(@uA:=convert(@plA using utf8)); -SELECT hex(@uB:=convert(@plB using utf8)); -SELECT hex(@uC:=convert(@plC using utf8)); -SELECT hex(@uD:=convert(@plD using utf8)); -SELECT hex(@uE:=convert(@plE using utf8)); -SELECT hex(@uF:=convert(@plF using utf8)); +SELECT DISTINCT + (IF( LOCATE( 'year=\"', dyninfo ) = 1, + SUBSTRING( dyninfo, 6+1, LOCATE('\"\r',dyninfo) - 6 -1), + IF( LOCATE( '\nyear=\"', dyninfo ), + SUBSTRING( dyninfo, LOCATE( '\nyear=\"', dyninfo ) + 7, + LOCATE( '\"\r', SUBSTRING( dyninfo, LOCATE( '\nyear=\"', dyninfo ) +7 )) - 1), '' ))) AS year +FROM t1 +HAVING year != '' ORDER BY year; -SELECT hex(convert(@u0 USING tis620)); -SELECT hex(convert(@u1 USING tis620)); -SELECT hex(convert(@u2 USING tis620)); -SELECT hex(convert(@u3 USING tis620)); -SELECT hex(convert(@u4 USING tis620)); -SELECT hex(convert(@u5 USING tis620)); -SELECT hex(convert(@u6 USING tis620)); -SELECT hex(convert(@u7 USING tis620)); -SELECT hex(convert(@u8 USING tis620)); -SELECT hex(convert(@u9 USING tis620)); -SELECT hex(convert(@uA USING tis620)); -SELECT hex(convert(@uB USING tis620)); -SELECT hex(convert(@uC USING tis620)); -SELECT hex(convert(@uD USING tis620)); -SELECT hex(convert(@uE USING tis620)); -SELECT hex(convert(@uF USING tis620)); +DROP TABLE t1; diff --git a/mysql-test/t/ctype_tis620.test-old b/mysql-test/t/ctype_tis620.test-old new file mode 100644 index 00000000000..82c660dfea0 --- /dev/null +++ b/mysql-test/t/ctype_tis620.test-old @@ -0,0 +1,64 @@ +-- source include/have_tis620.inc + +# +# Tests with the big5 character set +# +--disable_warnings +drop table if exists t1; +--enable_warnings + +# +# Bug 1552: tis620 <-> unicode conversion crashed +# Check tis620 -> utf8 -> tis620 round trip conversion +# + +SET @pl0= _tis620 0x000102030405060708090A0B0C0D0E0F; +SET @pl1= _tis620 0x101112131415161718191A1B1C1D1E1F; +SET @pl2= _tis620 0x202122232425262728292A2B2C2D2E2F; +SET @pl3= _tis620 0x303132333435363738393A3B3C3D3E3F; +SET @pl4= _tis620 0x404142434445464748494A4B4C4D4E4F; +SET @pl5= _tis620 0x505152535455565758595A5B5C5D5E5F; +SET @pl6= _tis620 0x606162636465666768696A6B6C6D6E6F; +SET @pl7= _tis620 0x707172737475767778797A7B7C7D7E7F; +SET @pl8= _tis620 0x808182838485868788898A8B8C8D8E8F; +SET @pl9= _tis620 0x909192939495969798999A9B9C9D9E9F; +SET @plA= _tis620 0xA0A1A2A3A4A5A6A7A8A9AAABACADAEAF; +SET @plB= _tis620 0xB0B1B2B3B4B5B6B7B8B9BABBBCBDBEBF; +SET @plC= _tis620 0xC0C1C2C3C4C5C6C7C8C9CACBCCCDCECF; +SET @plD= _tis620 0xD0D1D2D3D4D5D6D7D8D9DADBDCDDDEDF; +SET @plE= _tis620 0xE0E1E2E3E4E5E6E7E8E9EAEBECEDEEEF; +SET @plF= _tis620 0xF0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF; + +SELECT hex(@u0:=convert(@pl0 using utf8)); +SELECT hex(@u1:=convert(@pl1 using utf8)); +SELECT hex(@u2:=convert(@pl2 using utf8)); +SELECT hex(@u3:=convert(@pl3 using utf8)); +SELECT hex(@u4:=convert(@pl4 using utf8)); +SELECT hex(@u5:=convert(@pl5 using utf8)); +SELECT hex(@u6:=convert(@pl6 using utf8)); +SELECT hex(@u7:=convert(@pl7 using utf8)); +SELECT hex(@u8:=convert(@pl8 using utf8)); +SELECT hex(@u9:=convert(@pl9 using utf8)); +SELECT hex(@uA:=convert(@plA using utf8)); +SELECT hex(@uB:=convert(@plB using utf8)); +SELECT hex(@uC:=convert(@plC using utf8)); +SELECT hex(@uD:=convert(@plD using utf8)); +SELECT hex(@uE:=convert(@plE using utf8)); +SELECT hex(@uF:=convert(@plF using utf8)); + +SELECT hex(convert(@u0 USING tis620)); +SELECT hex(convert(@u1 USING tis620)); +SELECT hex(convert(@u2 USING tis620)); +SELECT hex(convert(@u3 USING tis620)); +SELECT hex(convert(@u4 USING tis620)); +SELECT hex(convert(@u5 USING tis620)); +SELECT hex(convert(@u6 USING tis620)); +SELECT hex(convert(@u7 USING tis620)); +SELECT hex(convert(@u8 USING tis620)); +SELECT hex(convert(@u9 USING tis620)); +SELECT hex(convert(@uA USING tis620)); +SELECT hex(convert(@uB USING tis620)); +SELECT hex(convert(@uC USING tis620)); +SELECT hex(convert(@uD USING tis620)); +SELECT hex(convert(@uE USING tis620)); +SELECT hex(convert(@uF USING tis620)); diff --git a/mysql-test/t/fulltext.test b/mysql-test/t/fulltext.test index 02ada3dc8cb..be75d5ee7cc 100644 --- a/mysql-test/t/fulltext.test +++ b/mysql-test/t/fulltext.test @@ -12,6 +12,7 @@ INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'), ('Only MyISAM tables','support collections'), ('Function MATCH ... AGAINST()','is used to do a search'), ('Full-text search in MySQL', 'implements vector space model'); +SHOW INDEX FROM t1; # nl search diff --git a/mysql-test/t/func_like.test b/mysql-test/t/func_like.test index a05a2a3bdaa..8b719b077f2 100644 --- a/mysql-test/t/func_like.test +++ b/mysql-test/t/func_like.test @@ -25,6 +25,16 @@ select * from t1 where a like "%abc\d%"; drop table t1; +# +# Bug #2231 +# + +create table t1 (a varchar(10), key(a)); +insert into t1 values ('a'), ('a\\b'); +select * from t1 where a like 'a\\%' escape '#'; +select * from t1 where a like 'a\\%' escape '#' and a like 'a\\\\b'; +drop table t1; + # # Test like with non-default character set diff --git a/mysql-test/t/func_math.test b/mysql-test/t/func_math.test index b6e2d3bd725..0f473abb96d 100644 --- a/mysql-test/t/func_math.test +++ b/mysql-test/t/func_math.test @@ -29,4 +29,15 @@ explain extended select rand(999999),rand(); select pi(),sin(pi()/2),cos(pi()/2),abs(tan(pi())),cot(1),asin(1),acos(0),atan(1); explain extended select pi(),sin(pi()/2),cos(pi()/2),abs(tan(pi())),cot(1),asin(1),acos(0),atan(1); select degrees(pi()),radians(360); + +# +# Bug #2338 Trignometric arithmatic problems +# + +SELECT ACOS(1.0); +SELECT ASIN(1.0); +SELECT ACOS(0.2*5.0); +SELECT ACOS(0.5*2.0); +SELECT ASIN(0.8+0.2); +SELECT ASIN(1.2-0.2); explain extended select degrees(pi()),radians(360); diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 155ed459d1f..233b58ea9b6 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -1,5 +1,3 @@ -# Version: 3.23.29 -# # Description # ----------- # Testing string functions @@ -141,6 +139,36 @@ INSERT INTO t1 VALUES (1, 'a545f661efdd1fb66fdee3aab79945bf'); SELECT 1 FROM t1 WHERE tmp=AES_DECRYPT(tmp,"password"); DROP TABLE t1; +CREATE TABLE t1 ( + wid int(10) unsigned NOT NULL auto_increment, + data_podp date default NULL, + status_wnio enum('nowy','podp','real','arch') NOT NULL default 'nowy', + PRIMARY KEY(wid), +); + +INSERT INTO t1 VALUES (8,NULL,'real'); +INSERT INTO t1 VALUES (9,NULL,'nowy'); +SELECT elt(status_wnio,data_podp) FROM t1 GROUP BY wid; +DROP TABLE t1; + +# +# test for #739 + +CREATE TABLE t1 (title text) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('Congress reconvenes in September to debate welfare and adult education'); +INSERT INTO t1 VALUES ('House passes the CAREERS bill'); +SELECT CONCAT("</a>",RPAD("",(55 - LENGTH(title)),".")) from t1; +DROP TABLE t1; + +# +# test for Bug #2290 "output truncated with ELT when using DISTINCT" +# + +CREATE TABLE t1 (i int, j int); +INSERT INTO t1 VALUES (1,1),(2,2); +SELECT DISTINCT i, ELT(j, '345', '34') FROM t1; +DROP TABLE t1; + # # Test collation and coercibility # @@ -322,27 +350,6 @@ select * from t7 where concat(s1 collate latin1_general_ci,s1 collate latin1_swedish_ci) = 'AA'; drop table t7; -CREATE TABLE t1 ( - wid int(10) unsigned NOT NULL auto_increment, - data_podp date default NULL, - status_wnio enum('nowy','podp','real','arch') NOT NULL default 'nowy', - PRIMARY KEY(wid), -); - -INSERT INTO t1 VALUES (8,NULL,'real'); -INSERT INTO t1 VALUES (9,NULL,'nowy'); -SELECT elt(status_wnio,data_podp) FROM t1 GROUP BY wid; -DROP TABLE t1; - -# -# test for #739 - -CREATE TABLE t1 (title text) ENGINE=MyISAM; -INSERT INTO t1 VALUES ('Congress reconvenes in September to debate welfare and adult education'); -INSERT INTO t1 VALUES ('House passes the CAREERS bill'); -SELECT CONCAT("</a>",RPAD("",(55 - LENGTH(title)),".")) from t1; -DROP TABLE t1; - select substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2),substring_index("1abcd;2abcd;3abcd;4abcd", ';', -2); explain extended select md5('hello'), sha('abc'), sha1('abc'), soundex(''), 'mood' sounds like 'mud', aes_decrypt(aes_encrypt('abc','1'),'1'),concat('*',space(5),'*'), reverse('abc'), rpad('a',4,'1'), lpad('a',4,'1'), concat_ws(',','',NULL,'a'),make_set(255,_latin2'a',_latin2'b',_latin2'c'),elt(2,1),locate("a","b",2),format(130,10),char(0),conv(130,16,10),hex(130),binary 'HE', export_set(255,_latin2'y',_latin2'n',_latin2' '),FIELD('b' COLLATE latin1_bin,'A','B'),FIND_IN_SET(_latin1'B',_latin1'a,b,c,d'),collation(conv(130,16,10)), coercibility(conv(130,16,10)),length('\n\t\r\b\0\_\%\\'),bit_length('\n\t\r\b\0\_\%\\'),bit_length('\n\t\r\b\0\_\%\\'),concat('monty',' was here ','again'),length('hello'),char(ascii('h')),ord('h'),quote(1/0),crc32("123"),replace('aaaa','a','b'),insert('txs',2,1,'hi'),left(_latin2'a',1),right(_latin2'a',1),lcase(_latin2'a'),ucase(_latin2'a'),SUBSTR('abcdefg',3,2),substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2),trim(_latin2' a '),ltrim(_latin2' a '),rtrim(_latin2' a '), decode(encode(repeat("a",100000),"monty"),"monty"); diff --git a/mysql-test/t/handler.test b/mysql-test/t/handler.test index d734882591a..72b6624509e 100644 --- a/mysql-test/t/handler.test +++ b/mysql-test/t/handler.test @@ -110,3 +110,19 @@ handler t1 read a=(W); --error 1210 handler t1 read a=(a); drop table t1; +# +# BUG#2304 +# +create table t1 (a char(5)); +insert into t1 values ("Ok"); +handler t1 open as t; +handler t read first; +use mysql; +handler t read first; +handler t close; +handler test.t1 open as t; +handler t read first; +handler t close; +use test; +drop table t1; + diff --git a/mysql-test/t/init_file-master.opt b/mysql-test/t/init_file-master.opt new file mode 100644 index 00000000000..825311dabf2 --- /dev/null +++ b/mysql-test/t/init_file-master.opt @@ -0,0 +1 @@ +--init-file=$MYSQL_TEST_DIR/std_data/init_file.dat diff --git a/mysql-test/t/init_file.test b/mysql-test/t/init_file.test new file mode 100644 index 00000000000..604d0a01794 --- /dev/null +++ b/mysql-test/t/init_file.test @@ -0,0 +1,7 @@ +# +# This is a regression test for bug #2526 "--init-file crashes MySQL if it +# contains a large select" +# +# See mysql-test/std_data/init_file.dat and +# mysql-test/t/init_file-master.opt for the actual test +# diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index b227614e7ae..92ab6008840 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -753,8 +753,7 @@ insert into t2 set id=1, t1_id=1; delete t1,t2 from t1,t2 where t1.id=t2.t1_id; select * from t1; select * from t2; -drop table t1,t2; -DROP TABLE IF EXISTS t1,t2; +drop table t2,t1; CREATE TABLE t1(id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB; CREATE TABLE t2(id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id) ) ENGINE=INNODB; INSERT INTO t1 VALUES(1); @@ -934,6 +933,15 @@ SELECT * from t1; drop table t1; # +# Test dictionary handling with spaceand quoting +# + +CREATE TABLE t1 (`id 1` INT NOT NULL, PRIMARY KEY (`id 1`)) TYPE=INNODB; +CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), FOREIGN KEY (`t1_id`) REFERENCES `t1`(`id 1`) ON DELETE CASCADE ) TYPE=INNODB; +#show create table t2; +drop table t2,t1; + +# # Test timestamps # diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test index e39e470cf31..234a5ec0eef 100644 --- a/mysql-test/t/insert_select.test +++ b/mysql-test/t/insert_select.test @@ -135,3 +135,34 @@ CREATE TABLE t2 ( USID INTEGER UNSIGNED AUTO_INCREMENT, ServerID TINYINT UNSIGNE INSERT INTO t1 VALUES (39,42,'Access-Granted','46','491721000045',2130706433,17690,NULL,NULL,'Localnet','491721000045','49172200000',754974766,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2003-07-18 00:11:21',NULL,NULL,20030718001121); INSERT INTO t2 SELECT USID, ServerID, State, SessionID, User, NASAddr, NASPort, NASPortType, ConnectSpeed, CarrierType, CallingStationID, CalledStationID, AssignedAddr, SessionTime, PacketsIn, OctetsIn, PacketsOut, OctetsOut, TerminateCause, UnauthTime, AccessRequestTime, AcctStartTime, AcctLastTime, LastModification from t1 LIMIT 1; drop table t1,t2; + +# +# Another problem from Bug #2012 +# + +CREATE TABLE t1( + Month date NOT NULL, + Type tinyint(3) unsigned NOT NULL auto_increment, + Field int(10) unsigned NOT NULL, + Count int(10) unsigned NOT NULL, + UNIQUE KEY Month (Month,Type,Field) +); + +insert into t1 Values +(20030901, 1, 1, 100), +(20030901, 1, 2, 100), +(20030901, 2, 1, 100), +(20030901, 2, 2, 100), +(20030901, 3, 1, 100); + +select * from t1; + +Select null, Field, Count From t1 Where Month=20030901 and Type=2; + +create table t2(No int not null, Field int not null, Count int not null); + +insert into t2 Select null, Field, Count From t1 Where Month=20030901 and Type=2; + +select * from t2; + +drop table t1, t2; diff --git a/mysql-test/t/lock.test b/mysql-test/t/lock.test index 69c453bf60a..26fc4e32bda 100644 --- a/mysql-test/t/lock.test +++ b/mysql-test/t/lock.test @@ -53,5 +53,9 @@ check table t1; # Check error message lock tables t1 write; check table t2; +--error 1100 +insert into t1 select nr from t1; unlock tables; +lock tables t1 write, t1 as t1_alias read; +insert into t1 select index1,nr from t1 as t1_alias; drop table t1,t2; diff --git a/mysql-test/t/lowercase_table2-master.opt b/mysql-test/t/lowercase_table2-master.opt new file mode 100644 index 00000000000..9b27aef9bf8 --- /dev/null +++ b/mysql-test/t/lowercase_table2-master.opt @@ -0,0 +1 @@ +--lower_case_table_names=0 diff --git a/mysql-test/t/lowercase_table2.test b/mysql-test/t/lowercase_table2.test new file mode 100644 index 00000000000..86bb26f0cf9 --- /dev/null +++ b/mysql-test/t/lowercase_table2.test @@ -0,0 +1,80 @@ +# +# Test of --lower-case-table-names=2 +# (User has case insensitive file system and want's to preserve case of +# table names) +# +--source include/have_innodb.inc +--require r/lowercase2.require +disable_query_log; +show variables like "lower_case_table_names"; +enable_query_log; + +--disable_warnings +DROP TABLE IF EXISTS t1,t2,T1,T2,t3,T3; +DROP DATABASE IF EXISTS `TEST_$1`; +DROP DATABASE IF EXISTS `test_$1`; +--enable_warnings + +CREATE TABLE T1 (a int); +INSERT INTO T1 VALUES (1); +SHOW TABLES LIKE "T1"; +SHOW TABLES LIKE "t1"; +SHOW CREATE TABLE T1; +RENAME TABLE T1 TO T2; +SHOW TABLES LIKE "T2"; +SELECT * FROM t2; +RENAME TABLE T2 TO t3; +SHOW TABLES LIKE "T3"; +RENAME TABLE T3 TO T1; +SHOW TABLES LIKE "T1"; +ALTER TABLE T1 add b int; +SHOW TABLES LIKE "T1"; +ALTER TABLE T1 RENAME T2; +SHOW TABLES LIKE "T2"; + +LOCK TABLE T2 WRITE; +ALTER TABLE T2 drop b; +SHOW TABLES LIKE "T2"; +UNLOCK TABLES; +RENAME TABLE T2 TO T1; +SHOW TABLES LIKE "T1"; +SELECT * from T1; +DROP TABLE T1; + +# +# Test database level +# + +CREATE DATABASE `TEST_$1`; +SHOW DATABASES LIKE "TEST%"; +DROP DATABASE `test_$1`; + +# +# Test of innodb tables with lower_case_table_names=2 +# + +CREATE TABLE T1 (a int) engine=innodb; +INSERT INTO T1 VALUES (1); +SHOW TABLES LIKE "T1"; +SHOW TABLES LIKE "t1"; +SHOW CREATE TABLE T1; +RENAME TABLE T1 TO T2; +SHOW TABLES LIKE "T2"; +SELECT * FROM t2; +RENAME TABLE T2 TO t3; +SHOW TABLES LIKE "T3"; +RENAME TABLE T3 TO T1; +SHOW TABLES LIKE "T1"; +ALTER TABLE T1 add b int; +SHOW TABLES LIKE "T1"; +ALTER TABLE T1 RENAME T2; +SHOW TABLES LIKE "T2"; + +LOCK TABLE T2 WRITE; +ALTER TABLE T2 drop b; +SHOW TABLES LIKE "T2"; +UNLOCK TABLES; +RENAME TABLE T2 TO T1; +SHOW TABLES LIKE "T1"; +SELECT * from T1; +DROP TABLE T1; diff --git a/mysql-test/t/myisam-blob-master.opt b/mysql-test/t/myisam-blob-master.opt new file mode 100644 index 00000000000..1a1076c7bad --- /dev/null +++ b/mysql-test/t/myisam-blob-master.opt @@ -0,0 +1 @@ +--max-allowed-packet=24M --skip-innodb --key-buffer-size=1M diff --git a/mysql-test/t/myisam-blob.test b/mysql-test/t/myisam-blob.test new file mode 100644 index 00000000000..d58222ec8bf --- /dev/null +++ b/mysql-test/t/myisam-blob.test @@ -0,0 +1,30 @@ +# +# Test bugs in the MyISAM code with blobs +# + +--disable_warnings +drop table if exists t1; +--enable_warnings + +# Bug #2159 (Problem with update of blob to > 16M) + +CREATE TABLE t1 (data LONGBLOB) ENGINE=myisam; +INSERT INTO t1 (data) VALUES (NULL); +UPDATE t1 set data=repeat('a',18*1024*1024); +select length(data) from t1; +delete from t1 where left(data,1)='a'; +check table t1; +truncate table t1; +INSERT INTO t1 (data) VALUES (repeat('a',1*1024*1024)); +INSERT INTO t1 (data) VALUES (repeat('b',16*1024*1024-1024)); +delete from t1 where left(data,1)='b'; +check table t1; + +# now we have two blocks in the table, first is a 1M record and second is +# a 16M delete block. + +UPDATE t1 set data=repeat('c',17*1024*1024); +check table t1; +delete from t1 where left(data,1)='c'; +check table t1; +drop table t1; diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index 85e5c7a7751..6ced608344b 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -399,6 +399,43 @@ check table t1; drop table t1; # +# two bugs in myisam-space-stripping feature +# +create table t1 ( a text not null, key a (a(20))); +insert into t1 values ('aaa '),('aaa'),('aa'); +check table t1; +repair table t1; +select concat(a,'.') from t1 where a='aaa'; +select concat(a,'.') from t1 where binary a='aaa'; +update t1 set a='bbb' where a='aaa'; +select concat(a,'.') from t1; +drop table t1; + +# +# Third bug in the same code (BUG#2295) +# + +create table t1(a text not null, b text not null, c text not null, index (a(10),b(10),c(10))); +insert into t1 values('807780', '477', '165'); +insert into t1 values('807780', '477', '162'); +insert into t1 values('807780', '472', '162'); +select * from t1 where a='807780' and b='477' and c='165'; +drop table t1; + +# +# Test text and unique +# +create table t1 (a int not null auto_increment primary key, b text not null, unique b (b(20))); +insert into t1 (b) values ('a'),('a '),('a '); +select concat(b,'.') from t1; +update t1 set b='b ' where a=2; +--error 1062 +update t1 set b='b ' where a > 1; +delete from t1 where b='b'; +select a,concat(b,'.') from t1; +drop table t1; + +# # Test RTREE index # --error 1235 diff --git a/mysql-test/t/mysqlbinlog.test b/mysql-test/t/mysqlbinlog.test index 2d1277a1cbc..07adf2732dc 100644 --- a/mysql-test/t/mysqlbinlog.test +++ b/mysql-test/t/mysqlbinlog.test @@ -64,10 +64,6 @@ select "--- --position --" as ""; # These are tests for remote binlog. # They should return the same as previous test. -# But now they are not. V. Vagin should fix this. -# We test all the same options second time since code for remote case is -# essentially different. If code for both cases will be unified we'll be -# able to throw out most of this. --disable_query_log select "--- Remote --" as ""; diff --git a/mysql-test/t/raid.test b/mysql-test/t/raid.test index 0d6e851a153..457262a8d75 100644 --- a/mysql-test/t/raid.test +++ b/mysql-test/t/raid.test @@ -18,6 +18,7 @@ DROP DATABASE IF EXISTS test_$1; create database test_$1; create table test_$1.r1 (i int) raid_type=1; +create table test_$1.r2 (i int) raid_type=1 raid_chunks=32; drop database test_$1; CREATE TABLE t1 ( diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index f01d8d4eacf..642dac471da 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -229,7 +229,7 @@ SELECT * FROM t1 WHERE a IN(1,2) AND b=5; DROP TABLE t1; # -# Test error with +# Test problem with range optimzer and sub ranges # CREATE TABLE t1 (a int, b int, c int, INDEX (c,a,b)); @@ -240,6 +240,22 @@ SELECT COUNT(*) FROM t1 WHERE (c=0 and a=1) or (c=0 and b=1); SELECT COUNT(*) FROM t1 WHERE (c=0 and b=1) or (c=0 and a=1); DROP TABLE t1; +# +# Test problem with range optimization over overlapping ranges (#2448) +# + +CREATE TABLE t1 ( a int not null, b int not null, INDEX ab(a,b) ); +INSERT INTO t1 VALUES (47,1), (70,1), (15,1), (15, 4); +SELECT * FROM t1 +WHERE +( + ( b =1 AND a BETWEEN 14 AND 21 ) OR + ( b =2 AND a BETWEEN 16 AND 18 ) OR + ( b =3 AND a BETWEEN 15 AND 19 ) OR + (a BETWEEN 19 AND 47) +); +DROP TABLE t1; + # test for a bug with in() and unique key create table t1 (id int(10) primary key); diff --git a/mysql-test/t/rpl_heap.test b/mysql-test/t/rpl_heap.test new file mode 100644 index 00000000000..15f61918034 --- /dev/null +++ b/mysql-test/t/rpl_heap.test @@ -0,0 +1,47 @@ +# You must run this test with --manager. + +require_manager; + +# Don't know why, but using TCP/IP connections makes this test fail +# with "Lost connection to MySQL server during query" when we +# issue a query after the server restart. +# Maybe this is something awkward in mysqltest or in the manager? +# So we use sockets. +connect (master,localhost,root,,test,0,master.sock); +connect (slave,localhost,root,,test,0,slave.sock); + +connection master; +reset master; +drop table if exists t1; +create table t1 (a int) type=HEAP; +insert into t1 values(10); +save_master_pos; +show binlog events from 79; +connection slave; +reset slave; +start slave; +sync_with_master; +show create table t1; +select * from t1; # should be one row + +server_stop master; +server_start master; + +connection master; +select * from t1; +# to check that DELETE is not written twice +# (the LIMIT is to not use the query cache) +select * from t1 limit 10; +save_master_pos; +show binlog events in 'master-bin.002' from 79; + +connection slave; +sync_with_master; +select * from t1; # should be empty + +# clean up +connection master; +drop table t1; +save_master_pos; +connection slave; +sync_with_master; diff --git a/mysql-test/t/rpl_insert_id.test b/mysql-test/t/rpl_insert_id.test index 8244c323a0d..4c27c03af60 100644 --- a/mysql-test/t/rpl_insert_id.test +++ b/mysql-test/t/rpl_insert_id.test @@ -41,8 +41,8 @@ connection master; # check if INSERT SELECT in auto_increment is well replicated (bug #490) -drop table t1; drop table t2; +drop table t1; create table t1(a int auto_increment, key(a)); create table t2(b int auto_increment, c int, key(b)); insert into t1 values (10); diff --git a/mysql-test/t/rpl_multi_delete-slave.opt b/mysql-test/t/rpl_multi_delete-slave.opt new file mode 100644 index 00000000000..c98fe0b0a46 --- /dev/null +++ b/mysql-test/t/rpl_multi_delete-slave.opt @@ -0,0 +1 @@ +--replicate-wild-do-table=test.% diff --git a/mysql-test/t/rpl_multi_delete.test b/mysql-test/t/rpl_multi_delete.test new file mode 100644 index 00000000000..299cb720b62 --- /dev/null +++ b/mysql-test/t/rpl_multi_delete.test @@ -0,0 +1,23 @@ +source include/master-slave.inc; +create table t1 (a int); +create table t2 (a int); + +insert into t1 values (1); +insert into t2 values (1); + +delete t1.* from t1, t2 where t1.a = t2.a; + +save_master_pos; +select * from t1; +select * from t2; + +connection slave; +sync_with_master; +select * from t1; +select * from t2; + +connection master; +drop table t1,t2; +save_master_pos; +connection slave; +sync_with_master; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 446c8768146..0a3de178456 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -1862,3 +1862,19 @@ select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3 left join t4 on id3 = id4 where id2 = 1 or id4 = 1; drop table t1,t2,t3,t4; +# +# Bug #2298 +# + +create table t1(s varchar(10) not null); +create table t2(s varchar(10) not null primary key); +create table t3(s varchar(10) not null primary key); +insert into t1 values ('one\t'), ('two\t'); +insert into t2 values ('one\r'), ('two\t'); +insert into t3 values ('one '), ('two\t'); +select * from t1 where s = 'one'; +select * from t2 where s = 'one'; +select * from t3 where s = 'one'; +select * from t1,t2 where t1.s = t2.s; +select * from t2,t3 where t2.s = t3.s; +drop table t1, t2, t3; diff --git a/mysql-test/t/timezone.test b/mysql-test/t/timezone.test index ab732c11a34..ba65eb72fe6 100644 --- a/mysql-test/t/timezone.test +++ b/mysql-test/t/timezone.test @@ -38,3 +38,10 @@ INSERT INTO t1 (ts) VALUES (Unix_timestamp('2003-03-30 04:00:01')); SELECT ts,from_unixtime(ts) FROM t1; DROP TABLE t1; +# +# Test for fix for Bug#2523 +# +select unix_timestamp('1970-01-01 01:00:00'), + unix_timestamp('1970-01-01 01:00:01'), + unix_timestamp('2038-01-01 00:59:59'), + unix_timestamp('2038-01-01 01:00:00'); diff --git a/mysql-test/t/type_blob.test b/mysql-test/t/type_blob.test index 70fa2f9b172..c826e59b29d 100644 --- a/mysql-test/t/type_blob.test +++ b/mysql-test/t/type_blob.test @@ -357,3 +357,9 @@ select * from t1 where txt <= 'Chevy'; select * from t1 where txt > 'Chevy'; select * from t1 where txt >= 'Chevy'; drop table t1; +CREATE TABLE t1 ( i int(11) NOT NULL default '0', c text NOT NULL, PRIMARY KEY (i), KEY (c(1),c(1))); +INSERT t1 VALUES (1,''),(2,''),(3,'asdfh'),(4,''); +select max(i) from t1 where c = ''; +drop table t1; + + diff --git a/mysql-test/t/type_timestamp.test b/mysql-test/t/type_timestamp.test index fc7cd019171..425e038956e 100644 --- a/mysql-test/t/type_timestamp.test +++ b/mysql-test/t/type_timestamp.test @@ -76,8 +76,35 @@ select * from t1; drop table t1; # -# Bug #1885 +# Bug #1885, bug #2539. +# Not perfect but still sensible attitude towards defaults for TIMESTAMP +# We will ignore default value for first TIMESTAMP column. # +create table t1 (t1 timestamp default '2003-01-01 00:00:00', + t2 timestamp default '2003-01-01 00:00:00'); +set TIMESTAMP=1000000000; +insert into t1 values(); +select * from t1; +show create table t1; +show columns from t1; +show columns from t1 like 't2'; +create table t2 (select * from t1); +show create table t2; + +# Ugly, but we can't do anything about this in 4.0 +alter table t1 add column t0 timestamp first; +show create table t1; ---error 1067 -create table t1 (a timestamp default 1); +drop table t1,t2; + +# +# Test for bug 2464, DEFAULT keyword in INSERT statement should return +# default value for column. +# + +create table t1 (ts1 timestamp, ts2 timestamp); +set TIMESTAMP=1000000000; +insert into t1 values (); +insert into t1 values (DEFAULT, DEFAULT); +select * from t1; +drop table t1; diff --git a/mysql-test/t/type_year.test b/mysql-test/t/type_year.test index 9922df447fc..c67b8447494 100644 --- a/mysql-test/t/type_year.test +++ b/mysql-test/t/type_year.test @@ -11,3 +11,12 @@ select * from t1; select * from t1 order by y; select * from t1 order by y2; drop table t1; + +# +# Bug 2335 +# + +create table t1 (y year); +insert into t1 values (now()); +select if(y = now(), 1, 0) from t1; +drop table t1; diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index e2537532eb4..2fec3535739 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -139,7 +139,7 @@ set character_set_client=UNKNOWN_CHARACTER_SET; set collation_connection=UNKNOWN_COLLATION; --error 1228 set global autocommit=1; ---error 1228 +--error 1238 select @@global.timestamp; --error 1193 set @@version=''; @@ -147,7 +147,7 @@ set @@version=''; set @@concurrent_insert=1; --error 1228 set @@global.sql_auto_is_null=1; ---error 1228 +--error 1238 select @@global.sql_auto_is_null; --error 1229 set myisam_max_sort_file_size=100; @@ -267,6 +267,9 @@ drop table t1,t2; select @@xxxxxxxxxx; select 1; +--error 1238 +select @@session.key_buffer_size; + # # swap # |