diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/auto_increment.result | 7 | ||||
| -rw-r--r-- | mysql-test/r/distinct.result | 6 | ||||
| -rw-r--r-- | mysql-test/r/func_gconcat.result | 41 | ||||
| -rw-r--r-- | mysql-test/r/func_str.result | 54 | ||||
| -rw-r--r-- | mysql-test/r/grant_cache.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/handler.result | 19 | ||||
| -rw-r--r-- | mysql-test/r/merge.result | 48 | ||||
| -rw-r--r-- | mysql-test/r/null_key.result | 110 | ||||
| -rw-r--r-- | mysql-test/r/subselect.result | 27 | ||||
| -rw-r--r-- | mysql-test/t/auto_increment.test | 4 | ||||
| -rw-r--r-- | mysql-test/t/func_gconcat.test | 36 | ||||
| -rw-r--r-- | mysql-test/t/func_str.test | 38 | ||||
| -rw-r--r-- | mysql-test/t/handler.test | 14 | ||||
| -rw-r--r-- | mysql-test/t/innodb.test | 4 | ||||
| -rw-r--r-- | mysql-test/t/merge.test | 29 | ||||
| -rw-r--r-- | mysql-test/t/null_key.test | 35 | ||||
| -rw-r--r-- | mysql-test/t/subselect.test | 26 |
17 files changed, 429 insertions, 71 deletions
diff --git a/mysql-test/r/auto_increment.result b/mysql-test/r/auto_increment.result index 5553f718799..19f8ffa84d4 100644 --- a/mysql-test/r/auto_increment.result +++ b/mysql-test/r/auto_increment.result @@ -111,11 +111,16 @@ insert into t1 set i = null; select last_insert_id(); last_insert_id() 255 +insert into t1 set i = 254; +ERROR 23000: Duplicate entry '254' for key 1 +select last_insert_id(); +last_insert_id() +255 insert into t1 set i = null; ERROR 23000: Duplicate entry '255' for key 1 select last_insert_id(); last_insert_id() -255 +0 drop table t1; create table t1 (i tinyint unsigned not null auto_increment, key (i)); insert into t1 set i = 254; diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index 9ebcd1fb915..c5841f28830 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -173,9 +173,9 @@ INSERT INTO t2 values (1),(2),(3); INSERT INTO t3 VALUES (1,'1'),(2,'2'),(1,'1'),(2,'2'); explain SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 index a a 5 NULL 6 Using index; Using temporary -1 SIMPLE t2 index a a 4 NULL 5 Using index; Distinct -1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 Using where; Distinct +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 2 Using temporary +1 SIMPLE t2 ref a a 4 test.t1.a 2 Using index +1 SIMPLE t3 ref a a 5 test.t1.b 2 Using where; Using index SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a; a 1 diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result index bf96727a6a7..15b406bcdda 100644 --- a/mysql-test/r/func_gconcat.result +++ b/mysql-test/r/func_gconcat.result @@ -1,6 +1,4 @@ -drop table if exists t1; -Warnings: -Note 1051 Unknown table 't1' +drop table if exists t1, t2; create table t1 (grp int, a bigint unsigned, c char(10) not null, d char(10) not null); insert into t1 values (1,1,"a","a"); insert into t1 values (2,2,"b","a"); @@ -155,25 +153,28 @@ show warnings; Level Code Message Warning 1258 1 line(s) was(were) cut by group_concat() set group_concat_max_len = 1024; -drop table if exists T_URL; -Warnings: -Note 1051 Unknown table 'T_URL' -create table T_URL ( URL_ID int(11), URL varchar(80)); -drop table if exists T_REQUEST; -Warnings: -Note 1051 Unknown table 'T_REQUEST' -create table T_REQUEST ( REQ_ID int(11), URL_ID int(11)); -insert into T_URL values (4,'www.host.com'), (5,'www.google.com'),(5,'www.help.com'); -insert into T_REQUEST values (1,4), (5,4), (5,5); -select REQ_ID, Group_Concat(URL) as URL from T_URL, T_REQUEST where -T_REQUEST.URL_ID = T_URL.URL_ID group by REQ_ID; -REQ_ID URL -1 www.host.com -5 www.host.com,www.google.com,www.help.com -drop table T_URL; -drop table T_REQUEST; select group_concat(sum(a)) from t1 group by grp; ERROR HY000: Invalid use of group function select grp,group_concat(c order by 2) from t1 group by grp; ERROR 42S22: Unknown column '2' in 'group statement' drop table t1; +create table t1 ( URL_ID int(11), URL varchar(80)); +create table t2 ( REQ_ID int(11), URL_ID int(11)); +insert into t1 values (4,'www.host.com'), (5,'www.google.com'),(5,'www.help.com'); +insert into t2 values (1,4), (5,4), (5,5); +select REQ_ID, Group_Concat(URL) as URL from t1, t2 where +t2.URL_ID = t1.URL_ID group by REQ_ID; +REQ_ID URL +1 X +5 X,X,X +drop table t1; +drop table t2; +create table t1 (id int, name varchar(16)); +insert into t1 values (1,'longername'),(1,'evenlongername'); +select ifnull(group_concat(concat(t1.id, ':', t1.name)), 'shortname') as 'without distinct: how it should be' from t1; +without distinct: how it should be +1:longername,1:evenlongername +select distinct ifnull(group_concat(concat(t1.id, ':', t1.name)), 'shortname') as 'with distinct: cutoff at length of shortname' from t1; +with distinct: cutoff at length of shortname +1:longername,1:evenlongername +drop table t1; diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index 34c7752798d..c2a921e1a54 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -1,4 +1,5 @@ drop table if exists t1; +set names latin1; select 'hello',"'hello'",'""hello""','''h''e''l''l''o''',"hel""lo",'hel\'lo'; hello 'hello' ""hello"" 'h'e'l'l'o' hel"lo hel'lo hello 'hello' ""hello"" 'h'e'l'l'o' hel"lo hel'lo @@ -249,6 +250,59 @@ INSERT INTO t1 VALUES (1, 'a545f661efdd1fb66fdee3aab79945bf'); SELECT 1 FROM t1 WHERE tmp=AES_DECRYPT(tmp,"password"); 1 DROP TABLE t1; +select 1=_latin1'1'; +1=_latin1'1' +1 +select _latin1'1'=1; +_latin1'1'=1 +1 +select _latin2'1'=1; +_latin2'1'=1 +1 +select 1=_latin2'1'; +1=_latin2'1' +1 +select _latin1'1'=_latin2'1'; +ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation '=' +select row('a','b','c') = row('a','b','c'); +row('a','b','c') = row('a','b','c') +1 +select row('A','b','c') = row('a','b','c'); +row('A','b','c') = row('a','b','c') +1 +select row('A' COLLATE latin1_bin,'b','c') = row('a','b','c'); +row('A' COLLATE latin1_bin,'b','c') = row('a','b','c') +0 +select row('A','b','c') = row('a' COLLATE latin1_bin,'b','c'); +row('A','b','c') = row('a' COLLATE latin1_bin,'b','c') +0 +select row('A' COLLATE latin1_general_ci,'b','c') = row('a' COLLATE latin1_bin,'b','c'); +ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation '=' +select concat(_latin1'a',_latin2'a'); +ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'concat' +select concat(_latin1'a',_latin2'a',_latin5'a'); +ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin5_turkish_ci,COERCIBLE) for operation 'concat' +select concat(_latin1'a',_latin2'a',_latin5'a',_latin7'a'); +ERROR HY000: Illegal mix of collations for operation 'concat' +select FIELD('b','A','B'); +FIELD('b','A','B') +2 +select FIELD('B','A','B'); +FIELD('B','A','B') +2 +select FIELD('b' COLLATE latin1_bin,'A','B'); +FIELD('b' COLLATE latin1_bin,'A','B') +0 +select FIELD('b','A' COLLATE latin1_bin,'B'); +FIELD('b','A' COLLATE latin1_bin,'B') +0 +select FIELD(_latin2'b','A','B'); +ERROR HY000: Illegal mix of collations for operation 'field' +select FIELD('b',_latin2'A','B'); +ERROR HY000: Illegal mix of collations for operation 'field' +select FIELD('b',_latin2'A','B',1); +FIELD('b',_latin2'A','B',1) +1 select POSITION(_latin1'B' IN _latin1'abcd'); POSITION(_latin1'B' IN _latin1'abcd') 2 diff --git a/mysql-test/r/grant_cache.result b/mysql-test/r/grant_cache.result index 0ffc48ad879..c8ae0b4d9b3 100644 --- a/mysql-test/r/grant_cache.result +++ b/mysql-test/r/grant_cache.result @@ -122,7 +122,7 @@ select "user4"; user4 user4 select a from t1; -ERROR 42000: No Database Selected +ERROR 3D000: No Database Selected select * from mysqltest.t1,test.t1; a b c a 1 1 1 test.t1 diff --git a/mysql-test/r/handler.result b/mysql-test/r/handler.result index 26b3c700d59..4e0153006f8 100644 --- a/mysql-test/r/handler.result +++ b/mysql-test/r/handler.result @@ -148,3 +148,22 @@ alter table t1 type=MyISAM; handler t2 read first; ERROR 42S02: Unknown table 't2' in HANDLER drop table t1; +create table t1 (a int); +insert into t1 values (1),(2),(3),(4),(5),(6); +delete from t1 limit 2; +handler t1 open; +handler t1 read first; +a +3 +handler t1 read first limit 1,1; +a +4 +handler t1 read first limit 2,2; +a +5 +6 +delete from t1 limit 3; +handler t1 read first; +a +6 +drop table t1; diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index 323931ae3eb..413277fee43 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -1,4 +1,5 @@ drop table if exists t1,t2,t3,t4,t5,t6; +drop database if exists mysqltest; 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"); @@ -174,15 +175,26 @@ t3 CREATE TABLE `t3` ( `a` int(11) NOT NULL default '0', `b` char(20) default NULL, KEY `a` (`a`) -) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2) +) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(`t1`,`t2`) create table t4 (a int not null, b char(10), key(a)) type=MERGE UNION=(t1,t2); select * from t4; ERROR HY000: Can't open file: 't4.MRG'. (errno: 143) -create table t5 (a int not null, b char(10), key(a)) type=MERGE UNION=(test.t1,test_2.t2); -ERROR HY000: Incorrect table definition; All MERGE tables must be in the same database -drop table if exists t5,t4,t3,t1,t2; -Warnings: -Note 1051 Unknown table 't5' +alter table t4 add column c int; +ERROR HY000: Can't open file: 't4.MRG'. (errno: 143) +create database mysqltest; +create table mysqltest.t6 (a int not null primary key auto_increment, message char(20)); +create table t5 (a int not null, b char(20), key(a)) type=MERGE UNION=(test.t1,mysqltest.t6); +show create table t5; +Table Create Table +t5 CREATE TABLE `t5` ( + `a` int(11) NOT NULL default '0', + `b` char(20) default NULL, + KEY `a` (`a`) +) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(`t1`,`mysqltest`.`t6`) +alter table t5 type=myisam; +drop table t5, mysqltest.t6; +drop database mysqltest; +drop table t4,t3,t1,t2; create table t1 (c char(10)) type=myisam; create table t2 (c char(10)) type=myisam; create table t3 (c char(10)) union=(t1,t2) type=merge; @@ -251,14 +263,14 @@ t3 CREATE TABLE `t3` ( `incr` int(11) NOT NULL default '0', `othr` int(11) NOT NULL default '0', PRIMARY KEY (`incr`) -) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2) +) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(`t1`,`t2`) alter table t3 drop primary key; show create table t3; Table Create Table t3 CREATE TABLE `t3` ( `incr` int(11) NOT NULL default '0', `othr` int(11) NOT NULL default '0' -) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2) +) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(`t1`,`t2`) drop table t3,t2,t1; create table t1 (a int not null, key(a)) type=merge; select * from t1; @@ -294,21 +306,21 @@ t4 CREATE TABLE `t4` ( `a` int(11) NOT NULL default '0', `b` int(11) NOT NULL default '0', KEY `a` (`a`,`b`) -) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2) +) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(`t1`,`t2`) show create table t5; Table Create Table t5 CREATE TABLE `t5` ( `a` int(11) NOT NULL default '0', `b` int(11) NOT NULL auto_increment, PRIMARY KEY (`a`,`b`) -) TYPE=MRG_MyISAM CHARSET=latin1 INSERT_METHOD=FIRST UNION=(t1,t2) +) TYPE=MRG_MyISAM CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`t1`,`t2`) show create table t6; Table Create Table t6 CREATE TABLE `t6` ( `a` int(11) NOT NULL default '0', `b` int(11) NOT NULL auto_increment, PRIMARY KEY (`a`,`b`) -) TYPE=MRG_MyISAM CHARSET=latin1 INSERT_METHOD=LAST UNION=(t1,t2) +) TYPE=MRG_MyISAM CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`) 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; @@ -373,7 +385,7 @@ t4 CREATE TABLE `t4` ( `a` int(11) NOT NULL default '0', `b` int(11) NOT NULL default '0', KEY `a` (`a`,`b`) -) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2,t3) +) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(`t1`,`t2`,`t3`) select * from t4 order by a,b; a b 1 1 @@ -399,7 +411,7 @@ t4 CREATE TABLE `t4` ( `a` int(11) NOT NULL default '0', `b` int(11) NOT NULL default '0', KEY `a` (`a`,`b`) -) TYPE=MRG_MyISAM CHARSET=latin1 INSERT_METHOD=FIRST UNION=(t1,t2,t3) +) TYPE=MRG_MyISAM CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`t1`,`t2`,`t3`) insert into t4 values (4,1),(4,2); select * from t1 order by a,b; a b @@ -528,7 +540,11 @@ a b 6 1 6 2 6 3 -drop table if exists t6, t5, t4, t3, t2, t1; +insert into t1 values (99,NULL); +select * from t4 where a+0 > 90; +a b +99 1 +drop table 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; @@ -540,7 +556,7 @@ max(b) select max(b) from t1 where a = 2; max(b) 1 -drop table if exists t3,t1,t2; +drop table t3,t1,t2; create table t1 (a int not null); create table t2 (a int not null); insert into t1 values (1); @@ -559,7 +575,7 @@ select * from t6; a 1 2 -drop table if exists t6, t3, t1, t2, t4, t5; +drop table t6, t3, t1, t2, t4, t5; CREATE TABLE t1 ( fileset_id tinyint(3) unsigned NOT NULL default '0', file_code varchar(32) NOT NULL default '', diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result index 289290ba08c..7dc0b4bfdd3 100644 --- a/mysql-test/r/null_key.result +++ b/mysql-test/r/null_key.result @@ -21,7 +21,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a,b a 9 NULL 3 Using where; Using index explain select * from t1 where (a is null or a = 7) and b=7; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a,b b 4 const 2 Using where +1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using where; Using index +explain select * from t1 where (a is null or a = 7) and b=7 order by a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using where; Using index; Using filesort explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a,b a 5 const 3 Using where; Using index @@ -56,13 +59,15 @@ NULL 9 NULL 9 select * from t1 where (a is null or a = 7) and b=7; a b -NULL 7 7 7 +NULL 7 select * from t1 where a is null and b=9 or a is null and b=7 limit 3; a b NULL 7 NULL 9 NULL 9 +create table t2 like t1; +insert into t2 select * from t1; alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10)); explain select * from t1 where a is null and b = 2; id select_type table type possible_keys key key_len ref rows Extra @@ -84,7 +89,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a,b a 5 NULL 5 Using where explain select * from t1 where (a is null or a = 7) and b=7 and c=0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL a,b NULL NULL NULL 12 Using where +1 SIMPLE t1 ref_or_null a,b a 5 const 4 Using where explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a,b a 5 const 3 Using where @@ -125,8 +130,8 @@ NULL 9 0 NULL 9 0 select * from t1 where (a is null or a = 7) and b=7 and c=0; a b c -NULL 7 0 7 7 0 +NULL 7 0 select * from t1 where a is null and b=9 or a is null and b=7 limit 3; a b c NULL 7 0 @@ -136,6 +141,103 @@ select * from t1 where b like "6%"; a b c 6 6 0 drop table t1; +rename table t2 to t1; +alter table t1 modify b int null; +insert into t1 values (7,null), (8,null), (8,7); +explain select * from t1 where a = 7 and (b=7 or b is null); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref_or_null a,b a 10 const,const 2 Using where; Using index +select * from t1 where a = 7 and (b=7 or b is null); +a b +7 7 +7 NULL +explain select * from t1 where (a = 7 or a is null) and (b=7 or b is null); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a,b a 10 NULL 4 Using where; Using index +select * from t1 where (a = 7 or a is null) and (b=7 or b is null); +a b +NULL 7 +7 NULL +7 7 +explain select * from t1 where (a = 7 or a is null) and (a = 7 or a is null); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref_or_null a a 5 const 5 Using where; Using index +select * from t1 where (a = 7 or a is null) and (a = 7 or a is null); +a b +7 NULL +7 7 +NULL 7 +NULL 9 +NULL 9 +create table t2 (a int); +insert into t2 values (7),(8); +explain select * from t2 straight_join t1 where t1.a=t2.a and b is null; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +1 SIMPLE t1 ref a,b a 10 test.t2.a,const 2 Using where; Using index +drop index b on t1; +explain select * from t2,t1 where t1.a=t2.a and b is null; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +1 SIMPLE t1 ref a a 10 test.t2.a,const 2 Using where; Using index +select * from t2,t1 where t1.a=t2.a and b is null; +a a b +7 7 NULL +8 8 NULL +explain select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using where; Using index +select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null); +a a b +7 7 7 +7 7 NULL +8 8 7 +8 8 NULL +explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using where; Using index +select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7; +a a b +7 7 7 +7 NULL 7 +8 8 7 +8 NULL 7 +explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and (b= 7 or b is null); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +1 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using where; Using index +select * from t2,t1 where (t1.a=t2.a or t1.a is null) and (b= 7 or b is null); +a a b +7 7 NULL +7 7 7 +7 NULL 7 +8 8 NULL +8 8 7 +8 NULL 7 +insert into t2 values (null),(6); +delete from t1 where a=8; +explain select * from t2,t1 where t1.a=t2.a or t1.a is null; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 +1 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using where; Using index +explain select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 +1 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using where; Using index +select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9); +a a b +7 7 NULL +7 7 7 +7 NULL 7 +8 NULL 7 +NULL NULL 7 +NULL NULL 9 +NULL NULL 9 +6 6 6 +6 NULL 7 +drop table t1,t2; CREATE TABLE t1 ( id int(10) unsigned NOT NULL auto_increment, uniq_id int(10) unsigned default NULL, diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index ee24ef445fb..bba86ff8891 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -829,7 +829,7 @@ a t1.a in (select t2.a from t2) explain SELECT t1.a, t1.a in (select t2.a from t2) FROM t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 Using index -2 DEPENDENT SUBQUERY t2 index a a 5 NULL 3 Using where; Using index +2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 const 2 Using where; Using index drop table t1,t2; create table t1 (a float); select 10.5 IN (SELECT * from t1 LIMIT 1); @@ -1153,6 +1153,19 @@ INSERT INTO t1 VALUES (1,0,NULL,NULL),(2,0,NULL,NULL); SELECT DISTINCT REF_ID FROM t1 WHERE ID= (SELECT DISTINCT REF_ID FROM t1 WHERE ID=2); REF_ID DROP TABLE t1; +create table t1 (a int, b int); +create table t2 (a int, b int); +insert into t1 values (1,0), (2,0), (3,0); +insert into t2 values (1,1), (2,1), (3,1), (2,2); +update ignore t1 set b=(select b from t2 where t1.a=t2.a); +Warnings: +Error 1240 Subselect returns more than 1 record +select * from t1; +a b +1 1 +2 NULL +3 1 +drop table t1, t2; create table t1(City VARCHAR(30),Location geometry); insert into t1 values("Paris",GeomFromText('POINT(2.33 48.87)')); select City from t1 where (select intersects(GeomFromText(AsText(Location)),GeomFromText('Polygon((2 50, 2.5 50, 2.5 47, 2 47, 2 50))'))=0); @@ -1200,3 +1213,15 @@ SELECT 'c373e9f5ad0791a0dab5444553544200' IN(SELECT t1.FOLDERID FROM t1 WHERE t1 'c373e9f5ad0791a0dab5444553544200' IN(SELECT t1.FOLDERID FROM t1 WHERE t1.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t1.FOLDERNAME = 'Level1') 0 drop table t1; +create table t1 (a int, b int); +create table t2 (a int, b int); +create table t3 (a int, b int); +insert into t1 values (0,100),(1,2), (1,3), (2,2), (2,7), (2,-1), (3,10); +insert into t2 values (0,0), (1,1), (2,1), (3,1), (4,1); +insert into t3 values (3,3), (2,2), (1,1); +select a,(select count(distinct t1.b) as sum from t1,t2 where t1.a=t2.a and t2.b > 0 and t1.a <= t3.b group by t1.a order by sum limit 1) from t3; +a (select count(distinct t1.b) as sum from t1,t2 where t1.a=t2.a and t2.b > 0 and t1.a <= t3.b group by t1.a order by sum limit 1) +3 1 +2 2 +1 2 +drop table t1,t2,t3; diff --git a/mysql-test/t/auto_increment.test b/mysql-test/t/auto_increment.test index 63fdfded6d0..189320a8dcb 100644 --- a/mysql-test/t/auto_increment.test +++ b/mysql-test/t/auto_increment.test @@ -80,6 +80,9 @@ insert into t1 set i = 254; insert into t1 set i = null; select last_insert_id(); --error 1062 +insert into t1 set i = 254; +select last_insert_id(); +--error 1062 insert into t1 set i = null; select last_insert_id(); drop table t1; @@ -100,5 +103,6 @@ select last_insert_id(); --error 1062 insert into t1 values (NULL, 10); select last_insert_id(); + drop table t1; diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test index a5a7abe3b01..f426f9ca4ee 100644 --- a/mysql-test/t/func_gconcat.test +++ b/mysql-test/t/func_gconcat.test @@ -1,8 +1,10 @@ # # simple test of group_concat function # +--disable_warnings +drop table if exists t1, t2; +--enable_warnings -drop table if exists t1; create table t1 (grp int, a bigint unsigned, c char(10) not null, d char(10) not null); insert into t1 values (1,1,"a","a"); insert into t1 values (2,2,"b","a"); @@ -70,19 +72,6 @@ select grp,group_concat(c) from t1 group by grp; show warnings; set group_concat_max_len = 1024; -# Test variable length - -drop table if exists T_URL; -create table T_URL ( URL_ID int(11), URL varchar(80)); -drop table if exists T_REQUEST; -create table T_REQUEST ( REQ_ID int(11), URL_ID int(11)); -insert into T_URL values (4,'www.host.com'), (5,'www.google.com'),(5,'www.help.com'); -insert into T_REQUEST values (1,4), (5,4), (5,5); -select REQ_ID, Group_Concat(URL) as URL from T_URL, T_REQUEST where -T_REQUEST.URL_ID = T_URL.URL_ID group by REQ_ID; -drop table T_URL; -drop table T_REQUEST; - # Test errors --error 1111 @@ -91,3 +80,22 @@ select group_concat(sum(a)) from t1 group by grp; select grp,group_concat(c order by 2) from t1 group by grp; drop table t1; + +# Test variable length + +create table t1 ( URL_ID int(11), URL varchar(80)); +create table t2 ( REQ_ID int(11), URL_ID int(11)); +insert into t1 values (4,'www.host.com'), (5,'www.google.com'),(5,'www.help.com'); +insert into t2 values (1,4), (5,4), (5,5); +# Make this order independent +--replace_result www.help.com X www.host.com X www.google.com X +select REQ_ID, Group_Concat(URL) as URL from t1, t2 where +t2.URL_ID = t1.URL_ID group by REQ_ID; +drop table t1; +drop table t2; + +create table t1 (id int, name varchar(16)); +insert into t1 values (1,'longername'),(1,'evenlongername'); +select ifnull(group_concat(concat(t1.id, ':', t1.name)), 'shortname') as 'without distinct: how it should be' from t1; +select distinct ifnull(group_concat(concat(t1.id, ':', t1.name)), 'shortname') as 'with distinct: cutoff at length of shortname' from t1; +drop table t1; diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index a898d3551d7..c9e7b1a529d 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -8,6 +8,8 @@ drop table if exists t1; --enable_warnings +set names latin1; + select 'hello',"'hello'",'""hello""','''h''e''l''l''o''',"hel""lo",'hel\'lo'; select 'hello' 'monty'; select length('\n\t\r\b\0\_\%\\'); @@ -136,6 +138,42 @@ DROP TABLE t1; # # Test collation and coercibility # + +select 1=_latin1'1'; +select _latin1'1'=1; +select _latin2'1'=1; +select 1=_latin2'1'; +--error 1265 +select _latin1'1'=_latin2'1'; +select row('a','b','c') = row('a','b','c'); +select row('A','b','c') = row('a','b','c'); +select row('A' COLLATE latin1_bin,'b','c') = row('a','b','c'); +select row('A','b','c') = row('a' COLLATE latin1_bin,'b','c'); +--error 1265 +select row('A' COLLATE latin1_general_ci,'b','c') = row('a' COLLATE latin1_bin,'b','c'); + +--error 1265 +select concat(_latin1'a',_latin2'a'); +--error 1268 +select concat(_latin1'a',_latin2'a',_latin5'a'); +--error 1269 +select concat(_latin1'a',_latin2'a',_latin5'a',_latin7'a'); + + +# +# Test FIELD() and collations +# +select FIELD('b','A','B'); +select FIELD('B','A','B'); +select FIELD('b' COLLATE latin1_bin,'A','B'); +select FIELD('b','A' COLLATE latin1_bin,'B'); +--error 1269 +select FIELD(_latin2'b','A','B'); +--error 1269 +select FIELD('b',_latin2'A','B'); +select FIELD('b',_latin2'A','B',1); + + select POSITION(_latin1'B' IN _latin1'abcd'); select POSITION(_latin1'B' IN _latin1'abcd' COLLATE latin1_bin); select POSITION(_latin1'B' COLLATE latin1_bin IN _latin1'abcd'); diff --git a/mysql-test/t/handler.test b/mysql-test/t/handler.test index 30746f10c62..15d2e954a95 100644 --- a/mysql-test/t/handler.test +++ b/mysql-test/t/handler.test @@ -85,3 +85,17 @@ alter table t1 type=MyISAM; handler t2 read first; drop table t1; +# +# test case for the bug #787 +# + +create table t1 (a int); +insert into t1 values (1),(2),(3),(4),(5),(6); +delete from t1 limit 2; +handler t1 open; +handler t1 read first; +handler t1 read first limit 1,1; +handler t1 read first limit 2,2; +delete from t1 limit 3; +handler t1 read first; +drop table t1; diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index 3736f4a2ddc..17df79a69fa 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -22,7 +22,7 @@ drop table t1; # # A bit bigger test -# The 'replace_result' statements are needed because the cardinality calculated +# The 'replace_column' statements are needed because the cardinality calculated # by innodb is not always the same between runs # @@ -51,7 +51,7 @@ select * from t1 where parent_id=102; select level,id from t1 where level=1; select level,id,parent_id from t1 where level=1; optimize table t1; ---replace_result 87 # 50 # 48 # 43 # 25 # 24 # 6 # 3 # +--replace_column 7 # show keys from t1; drop table t1; diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index 7a7678afca1..01ba8986474 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -4,6 +4,7 @@ --disable_warnings drop table if exists t1,t2,t3,t4,t5,t6; +drop database if exists mysqltest; --enable_warnings create table t1 (a int not null primary key auto_increment, message char(20)); @@ -48,13 +49,23 @@ show create table t3; create table t4 (a int not null, b char(10), key(a)) type=MERGE UNION=(t1,t2); --error 1016 select * from t4; ---error 1212 -create table t5 (a int not null, b char(10), key(a)) type=MERGE UNION=(test.t1,test_2.t2); +--error 1016 +alter table t4 add column c int; -# Because of windows, it's important that we drop the merge tables first! -# This should give a warning on table t5 -drop table if exists t5,t4,t3,t1,t2; +# +# Test tables in different databases +# +create database mysqltest; +create table mysqltest.t6 (a int not null primary key auto_increment, message char(20)); +create table t5 (a int not null, b char(20), key(a)) type=MERGE UNION=(test.t1,mysqltest.t6); +show create table t5; +alter table t5 type=myisam; +drop table t5, mysqltest.t6; +drop database mysqltest; +# Because of windows, it's important that we drop the merge tables first! +drop table t4,t3,t1,t2; + create table t1 (c char(10)) type=myisam; create table t2 (c char(10)) type=myisam; create table t3 (c char(10)) union=(t1,t2) type=merge; @@ -177,7 +188,9 @@ 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; +insert into t1 values (99,NULL); +select * from t4 where a+0 > 90; +drop table 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); @@ -186,7 +199,7 @@ INSERT INTO t2 VALUES (1,2), (2,2); CREATE TABLE t3 ( 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 t3 where a = 2; select max(b) from t1 where a = 2; -drop table if exists t3,t1,t2; +drop table t3,t1,t2; # # temporary merge tables @@ -203,7 +216,7 @@ insert into t4 values (1); insert into t5 values (2); create temporary table t6 (a int not null) TYPE=MERGE UNION=(t4,t5); select * from t6; -drop table if exists t6, t3, t1, t2, t4, t5; +drop table t6, t3, t1, t2, t4, t5; # # testing merge::records_in_range and optimizer diff --git a/mysql-test/t/null_key.test b/mysql-test/t/null_key.test index 18d0d368891..7d9500e90dd 100644 --- a/mysql-test/t/null_key.test +++ b/mysql-test/t/null_key.test @@ -14,6 +14,7 @@ explain select * from t1 where a=2 and b = 2; explain select * from t1 where a<=>b limit 2; explain select * from t1 where (a is null or a > 0 and a < 3) and b < 5 limit 3; explain select * from t1 where (a is null or a = 7) and b=7; +explain select * from t1 where (a is null or a = 7) and b=7 order by a; explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2; explain select * from t1 where a is null and b=9 or a is null and b=7 limit 3; explain select * from t1 where a > 1 and a < 3 limit 1; @@ -25,6 +26,8 @@ select * from t1 where (a is null or a > 0 and a < 3) and b < 5 limit 3; select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3; select * from t1 where (a is null or a = 7) and b=7; select * from t1 where a is null and b=9 or a is null and b=7 limit 3; +create table t2 like t1; +insert into t2 select * from t1; alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10)); explain select * from t1 where a is null and b = 2; explain select * from t1 where a is null and b = 2 and c=0; @@ -47,8 +50,38 @@ select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3; select * from t1 where (a is null or a = 7) and b=7 and c=0; select * from t1 where a is null and b=9 or a is null and b=7 limit 3; select * from t1 where b like "6%"; -drop table t1; +# +# Test ref_or_null optimization +# +drop table t1; +rename table t2 to t1; +alter table t1 modify b int null; +insert into t1 values (7,null), (8,null), (8,7); +explain select * from t1 where a = 7 and (b=7 or b is null); +select * from t1 where a = 7 and (b=7 or b is null); +explain select * from t1 where (a = 7 or a is null) and (b=7 or b is null); +select * from t1 where (a = 7 or a is null) and (b=7 or b is null); +explain select * from t1 where (a = 7 or a is null) and (a = 7 or a is null); +select * from t1 where (a = 7 or a is null) and (a = 7 or a is null); +create table t2 (a int); +insert into t2 values (7),(8); +explain select * from t2 straight_join t1 where t1.a=t2.a and b is null; +drop index b on t1; +explain select * from t2,t1 where t1.a=t2.a and b is null; +select * from t2,t1 where t1.a=t2.a and b is null; +explain select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null); +select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null); +explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7; +select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7; +explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and (b= 7 or b is null); +select * from t2,t1 where (t1.a=t2.a or t1.a is null) and (b= 7 or b is null); +insert into t2 values (null),(6); +delete from t1 where a=8; +explain select * from t2,t1 where t1.a=t2.a or t1.a is null; +explain select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9); +select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9); +drop table t1,t2; # # The following failed for Matt Loschert diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 7f957638fff..66d8dd2bc32 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -739,6 +739,19 @@ INSERT INTO t1 VALUES (1,0,NULL,NULL),(2,0,NULL,NULL); SELECT DISTINCT REF_ID FROM t1 WHERE ID= (SELECT DISTINCT REF_ID FROM t1 WHERE ID=2); DROP TABLE t1; +# +# uninterruptable update +# +create table t1 (a int, b int); +create table t2 (a int, b int); + +insert into t1 values (1,0), (2,0), (3,0); +insert into t2 values (1,1), (2,1), (3,1), (2,2); + +update ignore t1 set b=(select b from t2 where t1.a=t2.a); +select * from t1; + +drop table t1, t2; # # correct behavoiur for function from reduced subselect @@ -768,6 +781,7 @@ drop table if exists t1; # # key field overflow test # +--disable_warnings CREATE TABLE t1 ( FOLDERID VARCHAR(32)BINARY NOT NULL @@ -785,6 +799,7 @@ FOLDERID VARCHAR(32)BINARY NOT NULL , PRIMARY KEY ( FOLDERID ) ) TYPE=InnoDB; +--enable_warnings CREATE INDEX FFOLDERID_IDX ON t1 (FOLDERID); CREATE INDEX CMFLDRPARNT_IDX ON t1 (PARENTID); INSERT INTO t1 VALUES("0c9aab05b15048c59bc35c8461507deb", "System", "System", "2003-06-05 16:30:00", "The system content repository folder.", "3", "2003-06-05 16:30:00", "System", "0", NULL, "9c9aab05b15048c59bc35c8461507deb", "1"); @@ -792,3 +807,14 @@ INSERT INTO t1 VALUES("2f6161e879db43c1a5b82c21ddc49089", "Default", "System", " INSERT INTO t1 VALUES("c373e9f5ad0791724315444553544200", "AddDocumentTest", "admin", "2003-06-09 10:51:25", "Movie Reviews", "0", "2003-06-09 10:51:25", "admin", "0", "2f6161e879db43c1a5b82c21ddc49089", "03eea05112b845949f3fd03278b5fe43", NULL); SELECT 'c373e9f5ad0791a0dab5444553544200' IN(SELECT t1.FOLDERID FROM t1 WHERE t1.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t1.FOLDERNAME = 'Level1'); drop table t1; +# +# alloc_group_fields() working +# +create table t1 (a int, b int); +create table t2 (a int, b int); +create table t3 (a int, b int); +insert into t1 values (0,100),(1,2), (1,3), (2,2), (2,7), (2,-1), (3,10); +insert into t2 values (0,0), (1,1), (2,1), (3,1), (4,1); +insert into t3 values (3,3), (2,2), (1,1); +select a,(select count(distinct t1.b) as sum from t1,t2 where t1.a=t2.a and t2.b > 0 and t1.a <= t3.b group by t1.a order by sum limit 1) from t3; +drop table t1,t2,t3;s |