diff options
Diffstat (limited to 'mysql-test/t')
36 files changed, 582 insertions, 74 deletions
diff --git a/mysql-test/t/alias.test b/mysql-test/t/alias.test index 852b78dd04e..986af339456 100644 --- a/mysql-test/t/alias.test +++ b/mysql-test/t/alias.test @@ -74,8 +74,7 @@ CREATE TABLE t1 ( AUFNR varchar(12) NOT NULL default '', PLNFL varchar(6) NOT NULL default '', VORNR varchar(4) NOT NULL default '', - xstatus_vor smallint(5) unsigned NOT NULL default '0', - + xstatus_vor smallint(5) unsigned NOT NULL default '0' ); INSERT INTO t1 VALUES ('40004712','000001','0010',9); diff --git a/mysql-test/t/analyse.test b/mysql-test/t/analyse.test index 6aca345b282..47f3473584b 100644 --- a/mysql-test/t/analyse.test +++ b/mysql-test/t/analyse.test @@ -15,3 +15,26 @@ select * from t2; drop table t1,t2; EXPLAIN SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(); + +# +# Test with impossible where +# +create table t1 (a int not null); +create table t2 select * from t1 where 0=1 procedure analyse(); +show create table t2; +select * from t1 where 0=1 procedure analyse(); +insert into t1 values(1); +drop table t2; +create table t2 select * from t1 where 0=1 procedure analyse(); +show create table t2; +select * from t2; +insert into t2 select * from t1 procedure analyse(); +select * from t2; +insert into t1 values(2); +drop table t2; +create table t2 select * from t1 where 0=1 procedure analyse(); +show create table t2; +select * from t2; +insert into t2 select * from t1 procedure analyse(); +select * from t2; +drop table t1,t2; diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 4bd92994530..30441fb9aae 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -242,6 +242,18 @@ create database `db1 `; create table t1(`a ` int); # +# Test for Bug #3481 +# "Parser permits multiple commas without syntax error" +# + +--error 1064 +create table t1 (a int,); +--error 1064 +create table t1 (a int,,b int); +--error 1064 +create table t1 (,b int); + +# # Test create with foreign keys # diff --git a/mysql-test/t/ctype_ujis.test b/mysql-test/t/ctype_ujis.test index abfded5a6c2..6281f2a4249 100644 --- a/mysql-test/t/ctype_ujis.test +++ b/mysql-test/t/ctype_ujis.test @@ -61,3 +61,15 @@ select @ujis2 = CONVERT(@utf82 USING ujis); select @ujis3 = CONVERT(@utf83 USING ujis); select @ujis4 = CONVERT(@utf84 USING ujis); +# +# Testing with '%' and index (Bug #3438) +# + +drop table if exists t1; +create table t1 (c1 varchar(8)) default character set 'ujis'; +insert into t1 values (0xA4A2),(0xA2A2),(0xA4A2); +select c1 as 'no index' from t1 where c1 like cast(concat(0xA4A2, '%') as char character set ujis); +create index idx_c1 on t1(c1); +select c1 as 'using index' from t1 where c1 like cast(concat(0xA4A2, '%') as char character set ujis); +select c1 as 'no index' from t1 where c1 like cast(concat('%',0xA4A2, '%') as char character set ujis); +drop table t1; diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index f0d5a253bf4..fbfd3ccdef1 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -146,7 +146,7 @@ drop table t1; # CREATE TABLE `t1` ( `N` int(11) unsigned NOT NULL default '0', - `M` tinyint(1) default '0', + `M` tinyint(1) default '0' ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `t1` (N, M) VALUES (1, 0),(1, 0),(1, 0),(2, 0),(2, 0),(3, 0); UPDATE `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2; diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 2bd4838f934..7966a2262f4 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -135,6 +135,45 @@ select max(t1.a2),max(t2.a1) from t1 left outer join t2 on t1.a1=10; drop table t1,t2; # +# Test of group function and NULL values +# + +CREATE TABLE t1 (a int, b int); +select count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1; +select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +insert into t1 values (1,null); +select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +insert into t1 values (1,null); +insert into t1 values (2,null); +select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +insert into t1 values (2,1); +select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +insert into t1 values (3,1); +select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b), bit_xor(b) from t1 group by a; +explain extended select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b), bit_xor(b) from t1 group by a; +drop table t1; + +# +# Bug #1972: test for bit_and(), bit_or() and negative values +# +create table t1 (col int); +insert into t1 values (-1), (-2), (-3); +select bit_and(col), bit_or(col) from t1; +select SQL_BIG_RESULT bit_and(col), bit_or(col) from t1 group by col; +drop table t1; + +# +# Bug #3376: avg() and an empty table +# + +create table t1 (a int); +select avg(2) from t1; +drop table t1; + +# # Tests to check MIN/MAX query optimization # @@ -327,27 +366,6 @@ explain select concat(min(t1.a1),min(t2.a4)) from t1, t2 where t2.a4 <> 'AME'; drop table t1, t2; -# -# Test of group function and NULL values -# - -CREATE TABLE t1 (a int, b int); -select count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1; -select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; -insert into t1 values (1,null); -select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; -insert into t1 values (1,null); -insert into t1 values (2,null); -select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; -select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; -insert into t1 values (2,1); -select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; -select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; -insert into t1 values (3,1); -select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; -select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b), bit_xor(b) from t1 group by a; -explain extended select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b), bit_xor(b) from t1 group by a; -drop table t1; --disable_warnings create table t1 (USR_ID integer not null, MAX_REQ integer not null, constraint PK_SEA_USER primary key (USR_ID)) engine=InnoDB; --enable_warnings @@ -369,11 +387,12 @@ insert into t1 values (1); select max(a) as b from t1 having b=1; select a from t1 having a=1; drop table t1; + # -# Bug #1972: test for bit_and(), bit_or() and negative values -# -create table t1 (col int); -insert into t1 values (-1), (-2), (-3); -select bit_and(col), bit_or(col) from t1; -select SQL_BIG_RESULT bit_and(col), bit_or(col) from t1 group by col; +# Bug #3435: variance(const), stddev(const) and an empty table +# + +create table t1 (a int); +select variance(2) from t1; +select stddev(2) from t1; drop table t1; diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 1c85f83baac..c4413c3ef3f 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -39,7 +39,7 @@ SELECT CONCAT('"',CONCAT_WS('";"',repeat('a',60),repeat('b',60),repeat('c',60),r 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 soundex(''),soundex('he'),soundex('hello all folks'),soundex('#3556 in bugdb'); select 'mood' sounds like 'mud'; select 'Glazgo' sounds like 'Liverpool'; select null sounds like 'null'; @@ -146,7 +146,7 @@ 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), + PRIMARY KEY(wid) ); INSERT INTO t1 VALUES (8,NULL,'real'); @@ -358,21 +358,19 @@ select substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2),substring_index("1abcd 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"); # -# Bug #2182 +# lpad returns incorrect result (Bug #2182) # SELECT lpad(12345, 5, "#"); # -# Bug #2972 +# Problem the the CONV() function (Bug #2972) # SELECT conv(71, 10, 36), conv('1Z', 36, 10); - - # -# Bug #3089 +# Bug in SUBSTRING when mixed with CONCAT and ORDER BY (Bug #3089) # create table t1 (id int(1), str varchar(10)) DEFAULT CHARSET=utf8; @@ -382,3 +380,14 @@ insert into t2 values (1,'cccccccccc'), (2,'dddddddddd'); select substring(concat(t1.str, t2.str), 1, 15) "name" from t1, t2 where t2.id=t1.id order by name; drop table t1, t2; + +# +# Test case for conversion of long string value to integer (Bug #3472) +# + +create table t1 (c1 INT, c2 INT UNSIGNED); +insert into t1 values ('21474836461','21474836461'); +insert into t1 values ('-21474836461','-21474836461'); +show warnings; +select * from t1; +drop table t1; diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 5934e291dca..69306f69f40 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -139,10 +139,27 @@ select extract(MINUTE_SECOND FROM "10:11:12"); select extract(SECOND FROM "1999-01-02 10:11:12"); select extract(MONTH FROM "2001-02-00"); +# +# Test big intervals (Bug #3498) +# +SELECT "1900-01-01 00:00:00" + INTERVAL 2147483648 SECOND; +SELECT "1900-01-01 00:00:00" + INTERVAL "1:2147483647" MINUTE_SECOND; +SELECT "1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND;SELECT "1900-01-01 00:00:00" + INTERVAL 1<<37 SECOND; +SELECT "1900-01-01 00:00:00" + INTERVAL 1<<31 MINUTE; +SELECT "1900-01-01 00:00:00" + INTERVAL 1<<20 HOUR; + +SELECT "1900-01-01 00:00:00" + INTERVAL 1<<38 SECOND; +SELECT "1900-01-01 00:00:00" + INTERVAL 1<<33 MINUTE; +SELECT "1900-01-01 00:00:00" + INTERVAL 1<<30 HOUR; +SELECT "1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND; + +# +# Bug #614 (multiple extracts in where) +# + create table t1 (ctime varchar(20)); insert into t1 values ('2001-01-12 12:23:40'); select ctime, hour(ctime) from t1; -# test bug 614 (multiple extracts in where) select ctime from t1 where extract(MONTH FROM ctime) = 1 AND extract(YEAR FROM ctime) = 2001; drop table t1; diff --git a/mysql-test/t/gis-rtree.test b/mysql-test/t/gis-rtree.test index 629a07a4913..8e91e5891b8 100644 --- a/mysql-test/t/gis-rtree.test +++ b/mysql-test/t/gis-rtree.test @@ -103,3 +103,16 @@ check table t1; analyze table t1; drop table t1; +# +# The following crashed gis +# + +CREATE TABLE t1 ( + fid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + g GEOMETRY NOT NULL, + SPATIAL KEY(g) +) ENGINE=MyISAM; + +INSERT INTO t1 (g) VALUES (GeomFromText('LineString(1 2, 2 3)')),(GeomFromText('LineString(1 2, 2 4)')); +#select * from t1 where g<GeomFromText('LineString(1 2, 2 3)'); +drop table t1; diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test index 326759cb9ca..832541b0f86 100644 --- a/mysql-test/t/grant.test +++ b/mysql-test/t/grant.test @@ -53,6 +53,8 @@ delete from mysql.db where user='mysqltest_1'; delete from mysql.tables_priv where user='mysqltest_1'; delete from mysql.columns_priv where user='mysqltest_1'; flush privileges; +--error 1141 +show grants for mysqltest_1@localhost; # # Test what happens when you have same table and colum level grants diff --git a/mysql-test/t/help.test b/mysql-test/t/help.test index 54c551ccd7a..3f3e99e1556 100644 --- a/mysql-test/t/help.test +++ b/mysql-test/t/help.test @@ -1,5 +1,3 @@ --- source include/have_innodb.inc - # category: topic: keyword: # # impossible_category_1 @@ -65,10 +63,12 @@ help 'impossible_function_1'; help 'impossible_category_1'; ############## +--disable_warnings alter table mysql.help_relation engine=innodb; alter table mysql.help_keyword engine=innodb; alter table mysql.help_topic engine=innodb; alter table mysql.help_category engine=innodb; +--enable_warnings ############## help 'function_of_my_dream'; diff --git a/mysql-test/t/innodb-lock.test b/mysql-test/t/innodb-lock.test new file mode 100644 index 00000000000..33baec32549 --- /dev/null +++ b/mysql-test/t/innodb-lock.test @@ -0,0 +1,43 @@ +-- source include/have_innodb.inc + +connect (con1,localhost,root,,); +connect (con2,localhost,root,,); + +--disable_warnings +drop table if exists t1; +--enable_warnings + +# +# Testing of explicit table locks +# + +connection con1; +create table t1 (id integer, x integer) engine=INNODB; +insert into t1 values(0, 0); +set autocommit=0; +SELECT * from t1 where id = 0 FOR UPDATE; + +connection con2; +set autocommit=0; + +# The following statement should hang because con1 is locking the page +--send +lock table t1 write; +--sleep 2; + +connection con1; +update t1 set x=1 where id = 0; +select * from t1; +commit; + +connection con2; +reap; +update t1 set x=2 where id = 0; +commit; +unlock tables; + +connection con1; +select * from t1; +commit; + +drop table t1; diff --git a/mysql-test/t/key.test b/mysql-test/t/key.test index cdaf6062771..8d399abfec9 100644 --- a/mysql-test/t/key.test +++ b/mysql-test/t/key.test @@ -204,3 +204,27 @@ select i from t1 where a=repeat(_utf8 0xD0B1,200); select i from t1 where b=repeat(_utf8 'b',310); drop table t1; +# +# Test of key read with primary key (Bug #3497) +# + +CREATE TABLE t1 (id int unsigned auto_increment, name char(50), primary key (id)) engine=myisam; +insert into t1 (name) values ('a'), ('b'),('c'),('d'),('e'),('f'),('g'); +explain select 1 from t1 where id =2; +explain select 1 from t1 where id =2 or id=3; +explain select name from t1 where id =2; +ALTER TABLE t1 DROP PRIMARY KEY, ADD INDEX (id); +explain select 1 from t1 where id =2; +drop table t1; + +# +# Test of problem with key read (Bug #3666) +# + +CREATE TABLE t1 (numeropost mediumint(8) unsigned NOT NULL default '0', numreponse int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (numeropost,numreponse), UNIQUE KEY numreponse (numreponse)); +INSERT INTO t1 (numeropost,numreponse) VALUES ('1','1'),('1','2'),('2','3'),('2','4'); +SELECT numeropost FROM t1 WHERE numreponse='1'; +EXPLAIN SELECT numeropost FROM t1 WHERE numreponse='1'; +FLUSH TABLES; +SELECT numeropost FROM t1 WHERE numreponse='1'; +drop table t1; diff --git a/mysql-test/t/key_cache.test b/mysql-test/t/key_cache.test index 2bf5cdbcf6f..b45df8eb58e 100644 --- a/mysql-test/t/key_cache.test +++ b/mysql-test/t/key_cache.test @@ -66,6 +66,14 @@ set global keycache1.key_buffer_size=1024*1024; create table t1 (p int primary key, a char(10)) delay_key_write=1; create table t2 (p int primary key, i int, a char(10), key k1(i), key k2(a)); +show status like 'key_blocks_used'; + +# Following results differs on 64 and 32 bit systems because of different +# pointer sizes, which takes up different amount of space in key cache + +--replace_result 1812 KEY_BLOCKS_UNUSED 1793 KEY_BLOCKS_UNUSED +show status like 'key_blocks_unused'; + insert into t1 values (1, 'qqqq'), (11, 'yyyy'); insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'), (3, 1, 'yyyy'), (4, 3, 'zzzz'); @@ -75,6 +83,10 @@ select * from t2; update t1 set p=2 where p=1; update t2 set i=2 where i=1; +show status like 'key_blocks_used'; +--replace_result 1808 KEY_BLOCKS_UNUSED 1789 KEY_BLOCKS_UNUSED +show status like 'key_blocks_unused'; + cache index t1 key (`primary`) in keycache1; explain select p from t1; @@ -133,6 +145,10 @@ cache index t3 in keycache2; cache index t1,t2 in default; drop table t1,t2,t3; +show status like 'key_blocks_used'; +--replace_result 1812 KEY_BLOCKS_UNUSED 1793 KEY_BLOCKS_UNUSED +show status like 'key_blocks_unused'; + # Cleanup # We don't reset keycache2 as we want to ensure that mysqld will reset it set global keycache2.key_buffer_size=0; diff --git a/mysql-test/t/lowercase_table2.test b/mysql-test/t/lowercase_table2.test index 8f542a7af78..5c479391916 100644 --- a/mysql-test/t/lowercase_table2.test +++ b/mysql-test/t/lowercase_table2.test @@ -1,6 +1,6 @@ # # Test of --lower-case-table-names=2 -# (User has case insensitive file system and want's to preserve case of +# (User has case insensitive file system and wants to preserve case of # table names) # --source include/have_innodb.inc diff --git a/mysql-test/t/lowercase_table3.test b/mysql-test/t/lowercase_table3.test index 735a0b390f9..1753772ecc3 100644 --- a/mysql-test/t/lowercase_table3.test +++ b/mysql-test/t/lowercase_table3.test @@ -1,6 +1,6 @@ # # Test of force of lower-case-table-names=0 -# (User has case insensitive file system and want's to preserve case of +# (User has case insensitive file system and wants to preserve case of # table names) # diff --git a/mysql-test/t/lowercase_table_qcache.test b/mysql-test/t/lowercase_table_qcache.test index 7416de25e1d..5077a41402a 100644 --- a/mysql-test/t/lowercase_table_qcache.test +++ b/mysql-test/t/lowercase_table_qcache.test @@ -26,4 +26,4 @@ select * from MySQL.db; enable_result_log; show status like "Qcache_queries_in_cache"; -set GLOBAL query_cache_size=0;
\ No newline at end of file +set GLOBAL query_cache_size=0; diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index d0edebe2da3..68d8af87e45 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -4,6 +4,7 @@ --disable_warnings drop table if exists t1,t2,t3; +drop database if exists mysqltest; --enable_warnings create table t1(id1 int not null auto_increment primary key, t char(12)); @@ -259,6 +260,10 @@ update t1,t2 set t1.b=t1.b+2,t2.b=t1.b+10 where t1.b between 3 and 5 and t2.a=t1 select * from t1; select * from t2; +# test for non-updating table which is also used in sub-select + +update t1,t2 set t1.b=t2.b, t1.a=t2.a where t1.a=t2.a and not exists (select * from t2 where t2.a > 10); + drop table t1,t2; CREATE TABLE t3 ( KEY1 varchar(50) NOT NULL default '', PARAM_CORR_DISTANCE_RUSH double default NULL, PARAM_CORR_DISTANCE_GEM double default NULL, PARAM_AVG_TARE double default NULL, PARAM_AVG_NB_DAYS double default NULL, PARAM_DEFAULT_PROP_GEM_SRVC varchar(50) default NULL, PARAM_DEFAULT_PROP_GEM_NO_ETIK varchar(50) default NULL, PARAM_SCENARIO_COSTS varchar(50) default NULL, PARAM_DEFAULT_WAGON_COST double default NULL, tmp int(11) default NULL, PRIMARY KEY (KEY1)) ENGINE=MyISAM; INSERT INTO t3 VALUES ('A',1,1,22,3.2,'R','R','BASE2',0.24,NULL); @@ -354,7 +359,7 @@ CREATE TABLE t2 ( a int ); DELETE t1 FROM t1, t2 AS t3; DELETE t4 FROM t1, t1 AS t4; DELETE t3 FROM t1 AS t3, t1 AS t4; ---error 1066 +--error 1109 DELETE t1 FROM t1 AS t3, t2 AS t4; INSERT INTO t1 values (1),(2); INSERT INTO t2 values (1),(2); @@ -369,7 +374,6 @@ DROP TABLE t1,t2; # # Test update with const tables # - create table `t1` (`p_id` int(10) unsigned NOT NULL auto_increment, `p_code` varchar(20) NOT NULL default '', `p_active` tinyint(1) unsigned NOT NULL default '1', PRIMARY KEY (`p_id`) ); create table `t2` (`c2_id` int(10) unsigned NULL auto_increment, `c2_p_id` int(10) unsigned NOT NULL default '0', `c2_note` text NOT NULL, `c2_active` tinyint(1) unsigned NOT NULL default '1', PRIMARY KEY (`c2_id`), KEY `c2_p_id` (`c2_p_id`) ); insert into t1 values (0,'A01-Comp',1); @@ -381,6 +385,39 @@ select * from t2; drop table t1, t2; # +# prevelege chexk for multiupdate with other tables +# + +connect (root,localhost,root,,test,$MASTER_MYPORT,master.sock); +connection root; +--disable_warnings +create database mysqltest; +--enable_warnings +create table mysqltest.t1 (a int, b int, primary key (a)); +create table mysqltest.t2 (a int, b int, primary key (a)); +create table mysqltest.t3 (a int, b int, primary key (a)); +grant select on mysqltest.* to mysqltest_1@localhost; +grant update on mysqltest.t1 to mysqltest_1@localhost; +connect (user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,master.sock); +connection user1; +update t1, t2 set t1.b=1 where t1.a=t2.a; +update t1, t2 set t1.b=(select t3.b from t3 where t1.a=t3.a) where t1.a=t2.a; +connection root; +revoke all privileges on mysqltest.t1 from mysqltest_1@localhost; +delete from mysql.user where user='mysqltest_1'; +drop database mysqltest; + +# +# multi delete wrong table check +# +create table t1 (a int, primary key (a)); +create table t2 (a int, primary key (a)); +create table t3 (a int, primary key (a)); +-- error 1109 +delete t1,t3 from t1,t2 where t1.a=t2.a and t2.a=(select t3.a from t3 where t1.a=t3.a); +drop table t1, t2, t3; + +# # Test for bug #1980. # set @ttype_save=@@storage_engine; diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index 95847ba1af7..298a8b1b61b 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -479,3 +479,26 @@ checksum table t1, t2, t3; checksum table t1, t2, t3 extended; #show table status; drop table t1,t2; + +create table t1 (a int, key (a)); +show keys from t1; +alter table t1 disable keys; +show keys from t1; +create table t2 (a int); +let $i=1000; +--disable_query_log +while ($i) +{ + dec $i; + eval insert t2 values (rand()*100000); +} +--enable_query_log +insert t1 select * from t2; +show keys from t1; +alter table t1 enable keys; +show keys from t1; +alter table t1 engine=heap; +alter table t1 disable keys; +show keys from t1; +drop table t1,t2; + diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 28992655bd2..bcfe81dc95f 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -6,7 +6,7 @@ DROP TABLE IF EXISTS t1, `"t"1`; CREATE TABLE t1(a int); INSERT INTO t1 VALUES (1), (2); ---exec $MYSQL_DUMP --skip-all --skip-comments -X test t1 +--exec $MYSQL_DUMP --skip-create --skip-comments -X test t1 DROP TABLE t1; # @@ -16,7 +16,7 @@ DROP TABLE t1; CREATE TABLE t1 (a decimal(240, 20)); INSERT INTO t1 VALUES ("1234567890123456789012345678901234567890"), ("0987654321098765432109876543210987654321"); ---exec $MYSQL_DUMP --skip-comments test t1 +--exec $MYSQL_DUMP --compact test t1 DROP TABLE t1; # @@ -28,12 +28,38 @@ INSERT INTO t1 VALUES (-9e999999); # The following replaces is here because some systems replaces the above # double with '-inf' and others with MAX_DOUBLE --replace_result (-1.79769313486232e+308) (RES) (NULL) (RES) ---exec $MYSQL_DUMP --skip-comments test t1 +--exec $MYSQL_DUMP --compact test t1 +DROP TABLE t1; + +# +# Bug #3361 mysqldump quotes DECIMAL values inconsistently +# + +CREATE TABLE t1 (a DECIMAL(10,5), b FLOAT); + +# check at first how mysql work with quoted decimal + +INSERT INTO t1 VALUES (1.2345, 2.3456); +INSERT INTO t1 VALUES ('1.2345', 2.3456); +INSERT INTO t1 VALUES ("1.2345", 2.3456); + +SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ANSI_QUOTES'; +INSERT INTO t1 VALUES (1.2345, 2.3456); +INSERT INTO t1 VALUES ('1.2345', 2.3456); +--error 1054 +INSERT INTO t1 VALUES ("1.2345", 2.3456); +SET SQL_MODE=@OLD_SQL_MODE; + +# check how mysqldump make quoting +--exec $MYSQL_DUMP --compact test t1 +--exec $MYSQL_DUMP --compact --skip-create test t1 +--exec $MYSQL_DUMP --skip-create --skip-comments test t1 +--exec $MYSQL_DUMP --skip-opt --extended-insert --skip-comments test t1 DROP TABLE t1; CREATE TABLE t1(a int, b text, c varchar(3)); INSERT INTO t1 VALUES (1, "test", "tes"), (2, "TEST", "TES"); ---exec $MYSQL_DUMP --skip-all --skip-comments -X test t1 +--exec $MYSQL_DUMP --skip-create --compact -X test t1 DROP TABLE t1; # @@ -42,7 +68,7 @@ DROP TABLE t1; CREATE TABLE t1 (`a"b"` char(2)); INSERT INTO t1 VALUES ("1\""), ("\"2"); ---exec $MYSQL_DUMP --skip-all --skip-comments -X test t1 +--exec $MYSQL_DUMP --compact --skip-create -X test t1 DROP TABLE t1; # @@ -51,7 +77,7 @@ DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(255)) DEFAULT CHARSET koi8r; INSERT INTO t1 VALUES (_koi8r x'C1C2C3C4C5'); ---exec $MYSQL_DUMP --skip-comments test t1 +--exec $MYSQL_DUMP --skip-comments test t1 DROP TABLE t1; # @@ -65,11 +91,11 @@ INSERT INTO t1 VALUES (1), (2); DROP TABLE t1; # -# Bug #2592 'mysqldum doesn't quote "tricky" names correctly' +# Bug #2592 'mysqldump doesn't quote "tricky" names correctly' # create table ```a` (i int); ---exec $MYSQL_DUMP --skip-comments test +--exec $MYSQL_DUMP --compact test drop table ```a`; # diff --git a/mysql-test/t/ndb_basic.test b/mysql-test/t/ndb_basic.test new file mode 100644 index 00000000000..d03abc34633 --- /dev/null +++ b/mysql-test/t/ndb_basic.test @@ -0,0 +1,98 @@ +-- source include/have_ndb.inc + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +# +# Basic test to show that the NDB +# table handler is working +# + +# +# Create a normal table with primary key +# +CREATE TABLE t1 ( + pk1 INT NOT NULL PRIMARY KEY, + attr1 INT NOT NULL +) ENGINE=ndbcluster; + +INSERT INTO t1 VALUES (9410,9412); + +SELECT pk1 FROM t1; +SELECT * FROM t1; +SELECT t1.* FROM t1; + +UPDATE t1 SET attr1=1 WHERE pk1=9410; +SELECT * FROM t1; + +# Can't UPDATE PK! Test that correct error is returned +-- error 1112 +UPDATE t1 SET pk1=2 WHERE attr1=1; +SELECT * FROM t1; + +# Delete the record +DELETE FROM t1; +SELECT * FROM t1; + +# Delete the record by specifying pk +INSERT INTO t1 VALUES (9410,9412); +DELETE FROM t1 WHERE pk1 = 9410; +SELECT * FROM t1; + +# Insert three records and delete the +INSERT INTO t1 VALUES (9410,9412), (9411, 9413), (9408, 8765); +DELETE FROM t1; +SELECT * FROM t1; + +# Insert three records with attr1=4 and two with attr1=5 +# Delete all with attr1=4 +INSERT INTO t1 values (1, 4), (2, 4), (3, 5), (4, 4), (5, 5); +DELETE FROM t1 WHERE attr1=4; +SELECT * FROM t1 order by pk1; +DELETE FROM t1; + +# Insert two records and delete one +INSERT INTO t1 VALUES (9410,9412), (9411, 9413); +DELETE FROM t1 WHERE pk1 = 9410; +SELECT * FROM t1; +DROP TABLE t1; + +# +# Create table without primary key +# a hidden primary key column is created by handler +# +CREATE TABLE t1 (id INT, id2 int) engine=ndbcluster; +INSERT INTO t1 values(3456, 7890); +SELECT * FROM t1; +UPDATE t1 SET id=2 WHERE id2=12; +SELECT * FROM t1; +UPDATE t1 SET id=1234 WHERE id2=7890; +SELECT * FROM t1; +DELETE FROM t1; + +INSERT INTO t1 values(3456, 7890), (3456, 7890), (3456, 7890); +SELECT * FROM t1; +DELETE FROM t1 WHERE id = 3456; + +DROP TABLE t1; + +# test create with the keyword "engine=NDBCLUSTER" +CREATE TABLE t1 ( + pk1 INT NOT NULL PRIMARY KEY, + attr1 INT NOT NULL +) ENGINE=NDBCLUSTER; + +INSERT INTO t1 values(1, 9999); + +DROP TABLE t1; + +# test create with the keyword "engine=NDB" +CREATE TABLE t1 ( + pk1 INT NOT NULL PRIMARY KEY, + attr1 INT NOT NULL +) ENGINE=NDB; + +INSERT INTO t1 values(1, 9999); + +DROP TABLE t1; diff --git a/mysql-test/t/ndb_cache.test b/mysql-test/t/ndb_cache.test new file mode 100644 index 00000000000..abd09424f64 --- /dev/null +++ b/mysql-test/t/ndb_cache.test @@ -0,0 +1,31 @@ +-- source include/have_query_cache.inc +-- source include/have_ndb.inc + +set GLOBAL query_cache_size=1355776; +reset query cache; +flush status; + +--disable_warnings +drop table if exists t1,t2; +--enable_warnings + +CREATE TABLE t1 (a int) ENGINE=ndbcluster; +CREATE TABLE t2 (a int); + +select * from t1; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +select * from t2; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +select * from t1; +select * from t2; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; + +drop table t1, t2; + +SET GLOBAL query_cache_size=0; diff --git a/mysql-test/t/raid.test b/mysql-test/t/raid.test index 457262a8d75..14a55db0c34 100644 --- a/mysql-test/t/raid.test +++ b/mysql-test/t/raid.test @@ -21,6 +21,18 @@ 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; +# +# Bug #3182: Test using more than 257 raid chunks +# +create database test_$1; +create table test_$1.r2 (i int) raid_type=1 raid_chunks=257; +show create table test_$1.r2; +drop database test_$1; + +# +# Test that data is spread over different raid directories +# + CREATE TABLE t1 ( id int unsigned not null auto_increment primary key, c char(255) not null diff --git a/mysql-test/t/repair_part1.test b/mysql-test/t/repair.test index a2e186fd385..ef7043febbc 100644 --- a/mysql-test/t/repair_part1.test +++ b/mysql-test/t/repair.test @@ -27,8 +27,9 @@ drop table t1; # non-existent table repair table t1 use_frm; -# -# Create test table for repair2 -# The following must be last in this file - create table t1 engine=myisam SELECT 1,"table 1"; +flush tables; +system echo 1 > $MYSQL_TEST_DIR/var/master-data/test/t1.MYI ; +repair table t1; +repair table t1 use_frm; +drop table t1; diff --git a/mysql-test/t/repair_part2-master.sh b/mysql-test/t/repair_part2-master.sh deleted file mode 100644 index 964bde06c18..00000000000 --- a/mysql-test/t/repair_part2-master.sh +++ /dev/null @@ -1 +0,0 @@ -echo "1" > $MYSQL_TEST_DIR/var/master-data/test/t1.MYI diff --git a/mysql-test/t/repair_part2.test b/mysql-test/t/repair_part2.test deleted file mode 100644 index 8c27e382dff..00000000000 --- a/mysql-test/t/repair_part2.test +++ /dev/null @@ -1,7 +0,0 @@ -# -# This test starts with a crashed t1.MYI file left over from repair.test -# - -repair table t1; -repair table t1 use_frm; -drop table t1; diff --git a/mysql-test/t/rpl_error_ignored_table.test b/mysql-test/t/rpl_error_ignored_table.test index 5953fae6030..39fb78a2f36 100644 --- a/mysql-test/t/rpl_error_ignored_table.test +++ b/mysql-test/t/rpl_error_ignored_table.test @@ -40,7 +40,7 @@ insert into t3 values(connection_id()); send update t2 set a = a + 1 + get_lock('crash_lock%20C', 10); connection master1; -sleep 2; +real_sleep 2; select (@id := id) - id from t3; kill @id; drop table t2,t3; diff --git a/mysql-test/t/rpl_multi_delete2-slave.opt b/mysql-test/t/rpl_multi_delete2-slave.opt new file mode 100644 index 00000000000..b828d03fafb --- /dev/null +++ b/mysql-test/t/rpl_multi_delete2-slave.opt @@ -0,0 +1 @@ +--replicate-wild-ignore-table=test.% diff --git a/mysql-test/t/rpl_multi_delete2.test b/mysql-test/t/rpl_multi_delete2.test new file mode 100644 index 00000000000..c5128833843 --- /dev/null +++ b/mysql-test/t/rpl_multi_delete2.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; +# BUG#3461 would cause sync to fail +sync_with_master; +error 1146; +select * from t1; +error 1146; +select * from t2; + +connection master; +drop table t1,t2; diff --git a/mysql-test/t/rpl_multi_update.test b/mysql-test/t/rpl_multi_update.test index 98a199ae412..88994aa66bd 100644 --- a/mysql-test/t/rpl_multi_update.test +++ b/mysql-test/t/rpl_multi_update.test @@ -2,7 +2,7 @@ source include/master-slave.inc; CREATE TABLE t1 ( a int unsigned not null auto_increment primary key, - b int unsigned, + b int unsigned ) ENGINE=MyISAM; CREATE TABLE t2 ( diff --git a/mysql-test/t/rpl_server_id1.test b/mysql-test/t/rpl_server_id1.test new file mode 100644 index 00000000000..aefcb81c930 --- /dev/null +++ b/mysql-test/t/rpl_server_id1.test @@ -0,0 +1,22 @@ +# This test checks that a slave does not execute queries originating +# from itself, by default. + +source include/master-slave.inc; +connection slave; +create table t1 (n int); +reset master; +# replicate ourselves +stop slave; +--replace_result $SLAVE_MYPORT SLAVE_PORT +eval change master to master_port=$SLAVE_MYPORT; +--replace_result $SLAVE_MYPORT SLAVE_PORT +--replace_column 18 # +show slave status; +start slave; +insert into t1 values (1); +# can't MASTER_POS_WAIT(), it does not work in this weird setup +# (when slave is its own master without --replicate-same-server-id) +sleep 2; # enough time for the event to be replicated (it should not) +show status like "slave_running"; +select * from t1; +drop table t1; diff --git a/mysql-test/t/rpl_server_id2-slave.opt b/mysql-test/t/rpl_server_id2-slave.opt new file mode 100644 index 00000000000..302889525dd --- /dev/null +++ b/mysql-test/t/rpl_server_id2-slave.opt @@ -0,0 +1 @@ +--disable-log-slave-updates --replicate-same-server-id diff --git a/mysql-test/t/rpl_server_id2.test b/mysql-test/t/rpl_server_id2.test new file mode 100644 index 00000000000..dc8f733b7ed --- /dev/null +++ b/mysql-test/t/rpl_server_id2.test @@ -0,0 +1,25 @@ +# This test checks that a slave DOES execute queries originating +# from itself, if running with --replicate-same-server-id. + +source include/master-slave.inc; +connection slave; +drop table if exists t1; +create table t1 (n int); +reset master; +# replicate ourselves +stop slave; +--replace_result $SLAVE_MYPORT SLAVE_PORT +eval change master to master_port=$SLAVE_MYPORT; +--replace_result $SLAVE_MYPORT SLAVE_PORT +--replace_column 18 # +show slave status; +start slave; +insert into t1 values (1); +save_master_pos; +sync_with_master; +select * from t1; # check that indeed 2 were inserted +# We stop the slave before cleaning up otherwise we'll get +# 'drop table t1' executed twice, so an error in the slave.err +# (not critical). +stop slave; +drop table t1; diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test index b0307af19bb..deb95af9168 100644 --- a/mysql-test/t/show_check.test +++ b/mysql-test/t/show_check.test @@ -55,7 +55,7 @@ create table t1 ( name char(20) default 'O''Brien' comment 'O''Brien as default', c int not null comment 'int column', `c-b` int comment 'name with a minus', - `space 2` int comment 'name with a space', + `space 2` int comment 'name with a space' ) comment = 'it\'s a table' ; show create table t1; set sql_quote_show_create=0; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 40f182f50a1..c3fb3dc8388 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -678,7 +678,7 @@ CREATE TABLE `t1` ( `id` mediumint(8) unsigned NOT NULL auto_increment, `pseudo` varchar(35) character set latin1 NOT NULL default '', PRIMARY KEY (`id`), - UNIQUE KEY `pseudo` (`pseudo`), + UNIQUE KEY `pseudo` (`pseudo`) ) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC; INSERT INTO t1 (pseudo) VALUES ('test'); SELECT 0 IN (SELECT 1 FROM t1 a); @@ -781,7 +781,7 @@ CREATE TABLE `t1` ( `email` varchar(60) NOT NULL default '', PRIMARY KEY (`id`), UNIQUE KEY `email` (`email`), - UNIQUE KEY `pseudo` (`pseudo`), + UNIQUE KEY `pseudo` (`pseudo`) ) ENGINE=MyISAM CHARSET=latin1 PACK_KEYS=1 ROW_FORMAT=DYNAMIC; INSERT INTO t1 (id,pseudo,email) VALUES (1,'test','test'),(2,'test1','test1'); SELECT pseudo as a, pseudo as b FROM t1 GROUP BY (SELECT a) ORDER BY (SELECT id*1); @@ -1120,3 +1120,14 @@ create table t3(flag int); -- error 1064 select (select * from t3 where id not null) from t1, t2; drop table t1,t2,t3; + +# +# aggregate functions (Bug #3505) +# +CREATE TABLE t1 (id INT); +CREATE TABLE t2 (id INT); +INSERT INTO t1 VALUES (1), (2); +INSERT INTO t2 VALUES (1); +SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id); +SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id) ORDER BY t1.id; +DROP TABLE t1,t2 diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index 9866b867427..d9e19571f81 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -469,12 +469,13 @@ show status like 'Slow_queries'; drop table t1; # -# bug #2508 +# Column 'name' cannot be null (error with union and left join) (bug #2508) # create table t1 ( RID int(11) not null default '0', IID int(11) not null default '0', nada varchar(50) not null,NAME varchar(50) not null,PHONE varchar(50) not null) engine=MyISAM; insert into t1 ( RID,IID,nada,NAME,PHONE) values (1, 1, 'main', 'a', '111'), (2, 1, 'main', 'b', '222'), (3, 1, 'main', 'c', '333'), (4, 1, 'main', 'd', '444'), (5, 1, 'main', 'e', '555'), (6, 2, 'main', 'c', '333'), (7, 2, 'main', 'd', '454'), (8, 2, 'main', 'e', '555'), (9, 2, 'main', 'f', '666'), (10, 2, 'main', 'g', '777'); select A.NAME, A.PHONE, B.NAME, B.PHONE from t1 A left join t1 B on A.NAME = B.NAME and B.IID = 2 where A.IID = 1 and (A.PHONE <> B.PHONE or B.NAME is null) union select A.NAME, A.PHONE, B.NAME, B.PHONE from t1 B left join t1 A on B.NAME = A.NAME and A.IID = 1 where B.IID = 2 and (A.PHONE <> B.PHONE or A.NAME is null); drop table t1; + # # Bug #2809 (UNION fails on MyIsam tables when index on second column from # same table) @@ -487,10 +488,8 @@ select col1 n from t1 union select col2 n from t1 order by n; drop table t1; # -# Bug #1428, incorrect handling of UNION ALL -# NOTE: The current result is wrong, needs to be fixed! +# Incorrect handling of UNION ALL (Bug #1428) # - create table t1 (i int); insert into t1 values (1); select * from t1 UNION select * from t1; @@ -498,4 +497,21 @@ select * from t1 UNION ALL select * from t1; select * from t1 UNION select * from t1 UNION ALL select * from t1; drop table t1; select 1 as a union all select 1 union all select 2 union select 1 union all select 2; +set sql_select_limit=1; +select 1 union select 2; +(select 1) union (select 2); +(select 1) union (select 2) union (select 3) limit 2; +set sql_select_limit=default; +# +# ORDER with LIMIT +# +create table t1 (a int); +insert into t1 values (100), (1); +create table t2 (a int); +insert into t2 values (100); +select a from t1 union select a from t2 order by a; +SET SQL_SELECT_LIMIT=1; +select a from t1 union select a from t2 order by a; +drop table t1, t2; +set sql_select_limit=default; |