diff options
Diffstat (limited to 'mysql-test/r')
29 files changed, 1391 insertions, 181 deletions
diff --git a/mysql-test/r/bdb.result b/mysql-test/r/bdb.result index 473ac0116cc..d2ef80d4de9 100644 --- a/mysql-test/r/bdb.result +++ b/mysql-test/r/bdb.result @@ -140,13 +140,13 @@ id parent_id level 1015 102 2 explain select level from t1 where level=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref level level 1 const 1 Using index +1 SIMPLE t1 ref level level 1 const 6 Using index explain select level,id from t1 where level=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref level level 1 const 1 Using index +1 SIMPLE t1 ref level level 1 const 6 Using index explain select level,id,parent_id from t1 where level=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref level level 1 const 1 +1 SIMPLE t1 ref level level 1 const 6 select level,id from t1 where level=1; level id 1 1002 @@ -625,7 +625,7 @@ id parent_id level 1016 102 2 explain select level from t1 where level=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref level level 1 const 1 Using index +1 SIMPLE t1 ref level level 1 const 6 Using index select level,id from t1 where level=1; level id 1 1004 @@ -1284,3 +1284,382 @@ SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterd"); id 4 DROP TABLE t1; +set storage_engine=bdb; +drop table if exists t1,t2,t3; +--- Testing varchar --- +--- Testing varchar --- +create table t1 (v varchar(10), c char(10), t text); +insert into t1 values('+ ', '+ ', '+ '); +set @a=repeat(' ',20); +insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a)); +Warnings: +Warning 1265 Data truncated for column 'v' at row 1 +select concat('*',v,'*',c,'*',t,'*') from t1; +concat('*',v,'*',c,'*',t,'*') +*+ *+*+ * +*+ *+*+ * +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` varchar(10) default NULL, + `c` char(10) default NULL, + `t` text +) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 +create table t2 like t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `v` varchar(10) default NULL, + `c` char(10) default NULL, + `t` text +) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 +create table t3 select * from t1; +show create table t3; +Table Create Table +t3 CREATE TABLE `t3` ( + `v` varchar(10) default NULL, + `c` char(10) default NULL, + `t` text +) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 +alter table t1 modify c varchar(10); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` varchar(10) default NULL, + `c` varchar(10) default NULL, + `t` text +) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 +alter table t1 modify v char(10); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` char(10) default NULL, + `c` varchar(10) default NULL, + `t` text +) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 +alter table t1 modify t varchar(10); +Warnings: +Warning 1265 Data truncated for column 't' at row 2 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` char(10) default NULL, + `c` varchar(10) default NULL, + `t` varchar(10) default NULL +) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 +select concat('*',v,'*',c,'*',t,'*') from t1; +concat('*',v,'*',c,'*',t,'*') +*+*+*+ * +*+*+*+ * +drop table t1,t2,t3; +create table t1 (v varchar(10), c char(10), t text, key(v), key(c), key(t(10))); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` varchar(10) default NULL, + `c` char(10) default NULL, + `t` text, + KEY `v` (`v`), + KEY `c` (`c`), + KEY `t` (`t`(10)) +) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 +select count(*) from t1; +count(*) +270 +insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1))); +select count(*) from t1 where v='a'; +count(*) +10 +select count(*) from t1 where c='a'; +count(*) +10 +select count(*) from t1 where t='a'; +count(*) +10 +select count(*) from t1 where v='a '; +count(*) +10 +select count(*) from t1 where c='a '; +count(*) +10 +select count(*) from t1 where t='a '; +count(*) +10 +select count(*) from t1 where v between 'a' and 'a '; +count(*) +10 +select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; +count(*) +10 +select count(*) from t1 where v like 'a%'; +count(*) +11 +select count(*) from t1 where c like 'a%'; +count(*) +11 +select count(*) from t1 where t like 'a%'; +count(*) +11 +select count(*) from t1 where v like 'a %'; +count(*) +9 +explain select count(*) from t1 where v='a '; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref v v 13 const 10 Using where +explain select count(*) from t1 where c='a '; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref c c 11 const 10 Using where +explain select count(*) from t1 where t='a '; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range t t 13 NULL 10 Using where +explain select count(*) from t1 where v like 'a%'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 13 NULL 11 Using where +explain select count(*) from t1 where v between 'a' and 'a '; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 13 NULL 10 Using where +explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 13 NULL 10 Using where +alter table t1 add unique(v); +ERROR 23000: Duplicate entry '{ ' for key 1 +alter table t1 add key(v); +select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a'; +qq +*a*a*a* +*a *a*a * +*a *a*a * +*a *a*a * +*a *a*a * +*a *a*a * +*a *a*a * +*a *a*a * +*a *a*a * +*a *a*a * +explain select * from t1 where v='a'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref v,v_2 v 13 const 10 Using where +select v,count(*) from t1 group by v limit 10; +v count(*) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select v,count(t) from t1 group by v limit 10; +v count(t) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select v,count(c) from t1 group by v limit 10; +v count(c) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select sql_big_result v,count(t) from t1 group by v limit 10; +v count(t) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select sql_big_result v,count(c) from t1 group by v limit 10; +v count(c) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select c,count(*) from t1 group by c limit 10; +c count(*) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select c,count(t) from t1 group by c limit 10; +c count(t) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select sql_big_result c,count(t) from t1 group by c limit 10; +c count(t) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select t,count(*) from t1 group by t limit 10; +t count(*) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select t,count(t) from t1 group by t limit 10; +t count(t) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select sql_big_result t,count(t) from t1 group by t limit 10; +t count(t) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +drop table t1; +create table t1 (a char(10), unique (a)); +insert into t1 values ('a '); +insert into t1 values ('a '); +ERROR 23000: Duplicate entry 'a' for key 1 +alter table t1 modify a varchar(10); +insert into t1 values ('a '),('a '),('a '),('a '); +ERROR 23000: Duplicate entry 'a ' for key 1 +insert into t1 values ('a '); +ERROR 23000: Duplicate entry 'a ' for key 1 +insert into t1 values ('a '); +ERROR 23000: Duplicate entry 'a ' for key 1 +insert into t1 values ('a '); +ERROR 23000: Duplicate entry 'a ' for key 1 +update t1 set a='a ' where a like 'a%'; +select concat(a,'.') from t1; +concat(a,'.') +a . +update t1 set a='abc ' where a like 'a '; +select concat(a,'.') from t1; +concat(a,'.') +a . +update t1 set a='a ' where a like 'a %'; +select concat(a,'.') from t1; +concat(a,'.') +a . +update t1 set a='a ' where a like 'a '; +select concat(a,'.') from t1; +concat(a,'.') +a . +drop table t1; +create table t1 (v varchar(10), c char(10), t text, key(v(5)), key(c(5)), key(t(5))); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` varchar(10) default NULL, + `c` char(10) default NULL, + `t` text, + KEY `v` (`v`(5)), + KEY `c` (`c`(5)), + KEY `t` (`t`(5)) +) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 +drop table t1; +create table t1 (v char(10) character set utf8); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` char(10) character set utf8 default NULL +) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 +drop table t1; +create table t1 (v varchar(10), c char(10)) row_format=fixed; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` varchar(10) default NULL, + `c` char(10) default NULL +) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED +insert into t1 values('a','a'),('a ','a '); +select concat('*',v,'*',c,'*') from t1; +concat('*',v,'*',c,'*') +*a*a* +*a *a* +drop table t1; +create table t1 (v varchar(65530), key(v(10))); +insert into t1 values(repeat('a',65530)); +select length(v) from t1 where v=repeat('a',65530); +length(v) +65530 +drop table t1; +create table t1 (v varchar(65530), key(v)); +Warnings: +Warning 1071 Specified key was too long; max key length is 255 bytes +drop table if exists t1; +create table t1 (v varchar(65536)); +Warnings: +Note 1246 Converting column 'v' from VARCHAR to TEXT +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` mediumtext +) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 +drop table t1; +create table t1 (v varchar(65530) character set utf8); +Warnings: +Note 1246 Converting column 'v' from VARCHAR to TEXT +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` mediumtext character set utf8 +) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 +drop table t1; +set storage_engine=MyISAM; diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 596e8a4c4da..bac3c9000ad 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -415,7 +415,7 @@ a int(11) YES NULL b bigint(11) 0 c bigint(10) 0 d date YES NULL -e char(1) +e varchar(1) f datetime YES NULL g time YES NULL h longblob diff --git a/mysql-test/r/ctype_tis620.result b/mysql-test/r/ctype_tis620.result index 1fece515f9f..a0fdcccca2c 100644 --- a/mysql-test/r/ctype_tis620.result +++ b/mysql-test/r/ctype_tis620.result @@ -259,8 +259,8 @@ WU ¡Ñ³°ªÒ ¡Ñ³°Ô¡Ò ¡Ñ³·ÔÁÒ -¡Ñ¹µì ¡Ñ¹µÔ¡Ã +¡Ñ¹µì ¡Ñ¹¸ÔªÒ ¡Ñ¹¸ÔÁÒ ¡Ñ¹ÂÒÃѵ¹ì @@ -272,8 +272,8 @@ WU ¡Ò¨¹ÇÃó ¡Ò¨¹Ò ¡Ò¹´Ò -¡Ò¹µì ¡Ò¹µìÃÇÕ +¡Ò¹µì ¡ÒÂÊÔ·¸Ôì ¡ÒÃÇÔÍà ¡ÒÃØ³Õ @@ -350,8 +350,8 @@ WU à¡ÕÂõÔÈÑ¡´Ôì à¡ÕÂõÔÊÇÑÊ´Ôì à¡×éÍ¡ÙÅ -á¡éÇ á¡éÇ㨠+á¡éÇ â¡Ážѹ¸ì â¡àÁÈ â¡ÅºÍÅ ¤Í¹à¹ç¤ªÑè¹Êì @@ -368,8 +368,8 @@ WU ¢¨Ãà¡ÕÂÃµÔ ¢¨ÃÈÃÕ ¢¨ÃÈÑ¡´Ôì -¢¹ÔÉ°ì ¢¹ÔÉ°Ò +¢¹ÔÉ°ì ¢ÇÑ㨠¢ÇѪÑ ¢ÇÑà´×͹ @@ -412,8 +412,8 @@ WU ठÊËÒÂÍÔÁà»ê¡«ì à¤.«Õ.¾Õ. áÁªªÕ¹à¹ÍÃÕè षշÕÅÔÊ«Ôè§ -ह áÁ¡«ì (»ÃÐà·Èä·Â) ह áÁç¡«ì (»ÃÐà·Èä·Â) +ह áÁ¡«ì (»ÃÐà·Èä·Â) à¤Ã×ÍÇÑÅÂì ᤹¹Ù ÍÔ¹àµÍÃìà·Ã´ á¤Ð¨éÍ @@ -494,12 +494,12 @@ WU ¨ÓàÃÔ ¨ÓÅͧ ¨Ô³³ì -¨Ôµµì ¨Ôµµì¹ÔÉÒ ¨ÔµµÁÒÊ ¨ÔµµÒ ¨ÔµµÔ ¨ÔµµÔ¹¹Ñ¹·ì +¨Ôµµì ¨ÔµÃÅ´Ò ¨ÔµÃÒ ¨ÔµÃÒÀóì @@ -627,10 +627,10 @@ WU ªÇ¹¾ÔÈ ªÇÅÔµ ªÇÔÈÒ -ªèÍ ªèͩѵà ªèÍ·Ô¾Âì ªèÍÍÑªÑ +ªèÍ ªÐ¹ÔÅ ªÐÍé͹ ªÑªªÑ @@ -758,12 +758,12 @@ WU ³¸ÔµÒ ³ÀÑ·Ã ³Àѷáóì -³Ã§¤ì ³Ã§¤ìªÑ ³Ã§¤ì¾ÑªÃì ³Ã§¤ìÄ·¸Ôì ³Ã§¤ìÇÔ·Âì ³Ã§¤ìÈÑ¡´Ôì +³Ã§¤ì ³Ã§ÃÑ¡Éì ³Ã§Ä·¸Ôì ³Ã§ÈÑ¡´Ôì @@ -862,11 +862,11 @@ WU µÐÇѹ µÑ觨Ñè§ËÅÍ´ä¿ µÒà¿ç´ -µØê µØê¡µÒ µØéÁ µØë µØÅÒÅѡɳì +µØê àµçÁà´ª àµ×͹㨠àµ×͹µÒ @@ -917,8 +917,8 @@ WU ·Ñº·ÔÁ ·ÑȹÇÃó ·Ñȹվà -·ÑȹÕÂì ·ÑȹÕÂÒ +·ÑȹÕÂì ·ÑÈÇÃó ·èÒ·ÃÒÂá¨é§ÇѲ¹Ò ·Ô¦ÑÁ¾Ã @@ -1088,8 +1088,8 @@ WU ¹Ã¾Å ¹ÃÀÑ·Ãì ¹ÃÒ -¹ÃÔ¹·Ãì ¹ÃÔ¹·Ãìà´ª +¹ÃÔ¹·Ãì ¹ÃÔÈ ¹ÃÔÉ° ¹ÃÕ @@ -1115,8 +1115,8 @@ WU ¹Ñ·¸Á¹ ¹Ñ¹ªÑ ¹Ñ¹·ªÑ -¹Ñ¹·¹ì ¹Ñ¹·¹Ò +¹Ñ¹·¹ì ¹Ñ¹·¾Ã ¹Ñ¹·¾Å ¹Ñ¹·ÁÒÊ @@ -1148,14 +1148,14 @@ WU ¹Ôà«Ð ¹Ô´ ¹Ô´Ò -¹ÔµÂì ¹ÔµÂÒ +¹ÔµÂì ¹ÔµÔ ¹ÔµÔ¾§Éì ¹ÔµÔÁÒ ¹Ô·ÃÒ -¹Ô·ÑÈ¹ì ¹Ô·ÑȹÕÂì +¹Ô·ÑÈ¹ì ¹Ô¸ÔÇ´Õ ¹Ô»»Í¹à¾¹µì(»ÃÐà·Èä·Â) ¹Ô¾¹¸ì @@ -1349,8 +1349,8 @@ WU »ÃÒ³ÕÂì »ÃÒâÁ·Âì »ÃÒö¹Ò -»ÃÔì »ÃÔÒ +»ÃÔì »ÃÔ´Ò »ÃÔ³´Ò »ÃÔ·ÑÈ @@ -1454,10 +1454,10 @@ WU ¾§ÉìÈÑ¡´Ôì ¾§ÉìÊѹµì ¾§Éì͹ѹµì -¾¨¹ì ¾¨¹Ò ¾¨¹Òö ¾¨¹ÕÂì +¾¨¹ì ¾¨ÁÒ¹ ¾¨ÁÒÅÂì ¾¹Á @@ -1504,8 +1504,8 @@ WU ¾ÃÊÇÃÃ¤ì ¾ÃËÁ¾Ñ²¹ì ¾ÃéÍÁªÑ -¾ÃлÃÐá´§ Î͹´éÒ¤ÒÃìÊì ¾ÃлÃÐá´§ Î͹´éÒ¤ÒÃìÊì ¨Ó¡Ñ´ +¾ÃлÃÐá´§ Î͹´éÒ¤ÒÃìÊì ¾ÃÐÃÒÁ 3 ¤ÒÃìà«ç¹àµÍÃì ¾ÃÐÃÒÁ 3 Î͹´éÒ¤ÒÃìÊì ¾ÃÔéÁà¾ÃÒ @@ -1604,10 +1604,10 @@ WU ¾Ùŷͧ¾Ãç;à¾ÍÃìµÕé_ ¾ÙżŠà¾ç§ ¿Ù ËÅÔ¹ -à¾çªÃì ྪÃÃѵ¹ì ྪÃÅ´Ò à¾ªÃÔ¹·Ãì +à¾çªÃì à¾ç¨Ñ¹·Ãì à¾ç·Ô¾Âì à¾ç¹ÀÒ @@ -1831,7 +1831,6 @@ WU ÃÒàÁÈÃì ÃÒÂÕ¹ Ã×è¹ÇÃÒËì -ÃØé§ ÃØ觷ԾÂì ÃØ觷ÔÇÒ ÃØ觹ÀÒ @@ -1840,11 +1839,12 @@ WU ÃØè§Ãѵ¹ì ÃØè§ÃÑÈÁÕ ÃØè§àÃ×ͧ -ÃØè§âè¹ì ÃØè§âè¹ì¢¹Êè§ +ÃØè§âè¹ì ÃØé§ÅÒÇÃó ÃØè§ÇÔ·Âì ÃØè§ÍÃس +ÃØé§ ÃØ¨Ò ÃبÒÀÒ ÃØËÐ¹Ò @@ -1861,8 +1861,8 @@ WU áþᾤ ¤Í¹ÊµÃѤªÑè¹ âç§Ò¹àËÅç¡¡ÃØ§à·¾Ï âè¹ì»ÃÐàÊÃÔ° -Ä·¸Ôì Ä·¸ÔªÑ +Ä·¸Ôì ÅÅÔ´Ò ÅÅÔµÒ ÅÐÁèÍÁ @@ -2050,8 +2050,8 @@ WU ÇÔäÅÇÃó ÇÔÇ ÇÔÇÃø¹ì -ÇÔÇѲ¹ì ÇÔÇѲ¹ìªÑ +ÇÔÇѲ¹ì ÇÔÈ¹Õ ÇÔÈÃص ÇÔÈÒÅ @@ -2146,11 +2146,11 @@ WU ÈÈÔÇÔÁÅ ÈÈÔÉÒ ÈÑ¡´Ò -ÈÑ¡´Ôì ÈÑ¡´ÔìªÑ ÈÑ¡´ÔìàªÇ§ ÈÑ¡´Ôì´Ò ÈÑ¡´ÔìÇÔºÙÅÂì +ÈÑ¡´Ôì ÈÑ¡ÃÔ¹·Ãì ÈѹʹÕÂì ÈÒ¹µÔᏴì @@ -2348,18 +2348,18 @@ WU ÊÓÃÒ ÊÓÄ·¸Ôì ÊÓÅÕ -ÊÔ§Ëì ÊÔ§Ëì¾Å ÊÔ§ËÒ +ÊÔ§Ëì ÊԵҹѹ ÊԵҾà ÊÔ·¸Ò -ÊÔ·¸Ôì ÊÔ·¸ÔªÑ ÊÔ·¸Ôà´ª ÊÔ·¸Ô¾Ã ÊÔ·¸Ô¾Ãó ÊÔ·¸Ô¾Å +ÊÔ·¸Ôì ÊÔ¹·ÇÕ ÊÔÃԪѠÊÔÃÔà´ª @@ -2393,15 +2393,15 @@ WU ÊØ¢ÊÇÑÊ´Ôì¡Å¡Òà ÊØ¢Êѹµì ÊØ¢ØÁ -Êؤ¹¸ì Êؤ¹¸Ò +Êؤ¹¸ì ÊبÒÃÕ ÊبԵ ÊبԵµÒ ÊبԵÃÒ ÊبԹ´Ò -ÊبԹµì ÊبԹµ¹ì +ÊبԹµì ÊتŠÊتÑ ÊØªÒ´Ò @@ -2443,13 +2443,13 @@ WU ÊØ¸Ô´Ò ÊظÔÈÑ¡´Ôì ÊØ¸Õ -ÊظÕÃì ÊظÕÃÒ +ÊظÕÃì Êع·Ã Êع·ÃÕ Êعѷ·Õ -Êعѹ·ì Êعѹ·Ò +Êعѹ·ì ÊØ¹ÔµÒ ÊعÔÈÒ ÊعÔÉÒ @@ -2511,10 +2511,10 @@ WU ÊØÃÈÑ¡´Ôì ÊØÃÊÔ·¸Ôì ÊØÃѪ¹Õ¡Ã -ÊØÃѵ¹ì ÊØÃѵ¹Ç´Õ ÊØÃѵ¹ìÇ´Õ ÊØÃѵ¹Ò +ÊØÃѵ¹ì ÊØÃѵÂÒ ÊØÃÒ§¤¹Ò ÊØÃԪѠ@@ -2529,8 +2529,8 @@ WU ÊØÃոҾà ÊØÃվà ÊØÃÕÁÒÈ -ÊØÃÕÂì ÊØÃÕÂì¾Ã +ÊØÃÕÂì ÊØÃÕÃѵ¹ì ÊØÅÑ´´Ò ÊØÇÀÑ·Ãì @@ -2540,9 +2540,9 @@ WU ÊØÇÃÃ³Õ ÊØÇÃóÕÂì ÊØÇÃѵ¹ì -ÊØÇѲ¹ì ÊØÇѲ¹ìªÑ ÊØÇѲ¹Ò +ÊØÇѲ¹ì ÊØÇѵªÑ ÊØÇÒÃÕ ÊØÇԪѠ@@ -2623,8 +2623,8 @@ WU ͹¹·ì ͹ÇѪ ͹ÑÒ -͹ѹµì ͹ѹµÈÑ¡´Ôì +͹ѹµì Í¹Ø¡ÔµÔ Í¹Ø¡ÙÅ Í¹ØªÒ @@ -2770,8 +2770,8 @@ WU ÍÒÃҾà ÍÒÃÔÂÒ ÍÒÃÕ -ÍÒÃÕÂì ÍÒÃÕÂì àÊÁÒ©ÔÁ (ä·Âູ¡Ñ¹ +ÍÒÃÕÂì ÍÒÃÕÃѵ¹ì ÍÒÃÕÇÃó ÍӹǠdiff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result index a8182561c66..d0c4dfda32c 100644 --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@ -1,15 +1,23 @@ DROP TABLE IF EXISTS t1; SET CHARACTER SET koi8r; -CREATE TABLE t1 (word VARCHAR(64) CHARACTER SET ucs2); -INSERT INTO t1 VALUES (_koi8r'ò'), (X'2004'); +CREATE TABLE t1 (word VARCHAR(64) CHARACTER SET ucs2, word2 CHAR(64) CHARACTER SET ucs2); +INSERT INTO t1 VALUES (_koi8r'ò',_koi8r'ò'), (X'2004',X'2004'); SELECT hex(word) FROM t1 ORDER BY word; hex(word) 0420 2004 +SELECT hex(word2) FROM t1 ORDER BY word2; +hex(word2) +0420 +2004 DELETE FROM t1; -INSERT INTO t1 VALUES (X'042000200020'), (X'200400200020'); +INSERT INTO t1 VALUES (X'042000200020',X'042000200020'), (X'200400200020', X'200400200020'); SELECT hex(word) FROM t1 ORDER BY word; hex(word) +042000200020 +200400200020 +SELECT hex(word2) FROM t1 ORDER BY word2; +hex(word2) 0420 2004 DROP TABLE t1; diff --git a/mysql-test/r/endspace.result b/mysql-test/r/endspace.result index 96210a0e16d..b1942409c91 100644 --- a/mysql-test/r/endspace.result +++ b/mysql-test/r/endspace.result @@ -43,7 +43,7 @@ teststring teststring explain select * from t1 order by text1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL key1 32 NULL 3 Using index +1 SIMPLE t1 index NULL key1 34 NULL 3 Using index alter table t1 modify text1 char(32) binary not null; check table t1; Table Op Msg_type Msg_text @@ -99,15 +99,15 @@ concat('|', text1, '|') explain select concat('|', text1, '|') from t1 where text1='teststring '; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range key1 key1 22 NULL 2 Using where -select * from t1 where text1 like 'teststring_%'; -text1 -teststring -teststring -select * from t1 where text1='teststring' or text1 like 'teststring_%'; -text1 -teststring -teststring -teststring +select concat('|', text1, '|') from t1 where text1 like 'teststring_%'; +concat('|', text1, '|') +|teststring | +|teststring | +select concat('|', text1, '|') from t1 where text1='teststring' or text1 like 'teststring_%'; +concat('|', text1, '|') +|teststring | +|teststring| +|teststring | select concat('|', text1, '|') from t1 where text1='teststring' or text1 > 'teststring\t'; concat('|', text1, '|') |teststring| @@ -121,14 +121,14 @@ concat('|', text1, '|') drop table t1; create table t1 (text1 varchar(32) not NULL, KEY key1 (text1)) pack_keys=0; insert into t1 values ('teststring'), ('nothing'), ('teststring\t'); -select * from t1 where text1='teststring' or text1 like 'teststring_%'; -text1 -teststring -teststring -select * from t1 where text1='teststring' or text1 >= 'teststring\t'; -text1 -teststring -teststring +select concat('|', text1, '|') from t1 where text1='teststring' or text1 like 'teststring_%'; +concat('|', text1, '|') +|teststring | +|teststring| +select concat('|', text1, '|') from t1 where text1='teststring' or text1 >= 'teststring\t'; +concat('|', text1, '|') +|teststring | +|teststring| drop table t1; create table t1 (text1 varchar(32) not NULL, KEY key1 using BTREE (text1)) engine=heap; insert into t1 values ('teststring'), ('nothing'), ('teststring\t'); @@ -151,7 +151,7 @@ teststring teststring explain select * from t1 order by text1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL key1 32 NULL 3 +1 SIMPLE t1 index NULL key1 34 NULL 3 alter table t1 modify text1 char(32) binary not null; select * from t1 order by text1; text1 @@ -200,12 +200,10 @@ teststring teststring select text1, length(text1) from t1 where text1='teststring' or text1 like 'teststring_%'; text1 length(text1) -teststring 10 teststring 11 teststring 11 select text1, length(text1) from t1 where text1='teststring' or text1 >= 'teststring\t'; text1 length(text1) -teststring 10 teststring 11 teststring 11 select concat('|', text1, '|') from t1 order by text1; diff --git a/mysql-test/r/func_like.result b/mysql-test/r/func_like.result index e9434b1749d..ac8e5eda8e8 100644 --- a/mysql-test/r/func_like.result +++ b/mysql-test/r/func_like.result @@ -3,10 +3,10 @@ create table t1 (a varchar(10), key(a)); insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test"); explain select * from t1 where a like 'abc%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index a a 11 NULL 5 Using where; Using index +1 SIMPLE t1 index a a 13 NULL 5 Using where; Using index explain select * from t1 where a like concat('abc','%'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index a a 11 NULL 5 Using where; Using index +1 SIMPLE t1 index a a 13 NULL 5 Using where; Using index select * from t1 where a like "abc%"; a abc diff --git a/mysql-test/r/heap.result b/mysql-test/r/heap.result index 0432faaab9d..a18e6592815 100644 --- a/mysql-test/r/heap.result +++ b/mysql-test/r/heap.result @@ -1,4 +1,4 @@ -drop table if exists t1; +drop table if exists t1,t2,t3; create table t1 (a int not null,b int not null, primary key (a)) engine=heap comment="testing heaps" avg_row_length=100 min_rows=1 max_rows=100; insert into t1 values(1,1),(2,2),(3,3),(4,4); delete from t1 where a=1 or a=0; @@ -233,3 +233,421 @@ SELECT * FROM t1 WHERE B is not null; a B 1 1 DROP TABLE t1; +set storage_engine=HEAP; +create table t1 (v varchar(10), c char(10), t varchar(50)); +insert into t1 values('+ ', '+ ', '+ '); +set @a=repeat(' ',20); +insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a)); +Warnings: +Warning 1265 Data truncated for column 'v' at row 1 +select concat('*',v,'*',c,'*',t,'*') from t1; +concat('*',v,'*',c,'*',t,'*') +*+ *+*+ * +*+ *+*+ * +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` varchar(10) default NULL, + `c` char(10) default NULL, + `t` varchar(50) default NULL +) ENGINE=HEAP DEFAULT CHARSET=latin1 +create table t2 like t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `v` varchar(10) default NULL, + `c` char(10) default NULL, + `t` varchar(50) default NULL +) ENGINE=HEAP DEFAULT CHARSET=latin1 +create table t3 select * from t1; +show create table t3; +Table Create Table +t3 CREATE TABLE `t3` ( + `v` varchar(10) default NULL, + `c` char(10) default NULL, + `t` varchar(50) default NULL +) ENGINE=HEAP DEFAULT CHARSET=latin1 +alter table t1 modify c varchar(10); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` varchar(10) default NULL, + `c` varchar(10) default NULL, + `t` varchar(50) default NULL +) ENGINE=HEAP DEFAULT CHARSET=latin1 +alter table t1 modify v char(10); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` char(10) default NULL, + `c` varchar(10) default NULL, + `t` varchar(50) default NULL +) ENGINE=HEAP DEFAULT CHARSET=latin1 +alter table t1 modify t varchar(10); +Warnings: +Warning 1265 Data truncated for column 't' at row 2 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` char(10) default NULL, + `c` varchar(10) default NULL, + `t` varchar(10) default NULL +) ENGINE=HEAP DEFAULT CHARSET=latin1 +select concat('*',v,'*',c,'*',t,'*') from t1; +concat('*',v,'*',c,'*',t,'*') +*+*+*+ * +*+*+*+ * +drop table t1,t2,t3; +create table t1 (v varchar(10), c char(10), t varchar(50), key(v), key(c), key(t(10))); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` varchar(10) default NULL, + `c` char(10) default NULL, + `t` varchar(50) default NULL, + KEY `v` (`v`), + KEY `c` (`c`), + KEY `t` (`t`(10)) +) ENGINE=HEAP DEFAULT CHARSET=latin1 +select count(*) from t1; +count(*) +270 +insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1))); +select count(*) from t1 where v='a'; +count(*) +10 +select count(*) from t1 where c='a'; +count(*) +10 +select count(*) from t1 where t='a'; +count(*) +10 +select count(*) from t1 where v='a '; +count(*) +10 +select count(*) from t1 where c='a '; +count(*) +10 +select count(*) from t1 where t='a '; +count(*) +10 +select count(*) from t1 where v between 'a' and 'a '; +count(*) +10 +select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; +count(*) +10 +select count(*) from t1 where v like 'a%'; +count(*) +11 +select count(*) from t1 where c like 'a%'; +count(*) +11 +select count(*) from t1 where t like 'a%'; +count(*) +11 +select count(*) from t1 where v like 'a %'; +count(*) +9 +explain select count(*) from t1 where v='a '; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref v v 13 const 10 Using where +explain select count(*) from t1 where c='a '; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref c c 11 const 10 Using where +explain select count(*) from t1 where t='a '; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref t t 13 const 10 Using where +explain select count(*) from t1 where v like 'a%'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL v NULL NULL NULL 271 Using where +explain select count(*) from t1 where v between 'a' and 'a '; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL v NULL NULL NULL 271 Using where +explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL v NULL NULL NULL 271 Using where +alter table t1 add unique(v); +ERROR 23000: Duplicate entry '{ ' for key 1 +alter table t1 add key(v); +select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a'; +qq +*a *a*a * +*a *a*a * +*a *a*a * +*a *a*a * +*a *a*a * +*a *a*a * +*a *a*a * +*a *a*a * +*a *a*a * +*a*a*a* +explain select * from t1 where v='a'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref v,v_2 v 13 const 10 Using where +select v,count(*) from t1 group by v limit 10; +v count(*) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select v,count(t) from t1 group by v limit 10; +v count(t) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select v,count(c) from t1 group by v limit 10; +v count(c) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select sql_big_result v,count(t) from t1 group by v limit 10; +v count(t) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select sql_big_result v,count(c) from t1 group by v limit 10; +v count(c) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select c,count(*) from t1 group by c limit 10; +c count(*) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select c,count(t) from t1 group by c limit 10; +c count(t) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select sql_big_result c,count(t) from t1 group by c limit 10; +c count(t) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select t,count(*) from t1 group by t limit 10; +t count(*) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select t,count(t) from t1 group by t limit 10; +t count(t) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select sql_big_result t,count(t) from t1 group by t limit 10; +t count(t) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +drop table t1; +create table t1 (a char(10), unique (a)); +insert into t1 values ('a'); +insert into t1 values ('a '); +ERROR 23000: Duplicate entry 'a' for key 1 +alter table t1 modify a varchar(10); +insert into t1 values ('a '),('a '),('a '),('a '); +ERROR 23000: Duplicate entry 'a ' for key 1 +insert into t1 values ('a '); +ERROR 23000: Duplicate entry 'a ' for key 1 +insert into t1 values ('a '); +ERROR 23000: Duplicate entry 'a ' for key 1 +insert into t1 values ('a '); +ERROR 23000: Duplicate entry 'a ' for key 1 +update t1 set a='a ' where a like 'a '; +update t1 set a='a ' where a like 'a '; +drop table t1; +create table t1 (v varchar(10), c char(10), t varchar(50), key using btree (v), key using btree (c), key using btree (t(10))); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` varchar(10) default NULL, + `c` char(10) default NULL, + `t` varchar(50) default NULL, + KEY `v` TYPE BTREE (`v`), + KEY `c` TYPE BTREE (`c`), + KEY `t` TYPE BTREE (`t`(10)) +) ENGINE=HEAP DEFAULT CHARSET=latin1 +select count(*) from t1; +count(*) +270 +insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1))); +select count(*) from t1 where v='a'; +count(*) +10 +select count(*) from t1 where c='a'; +count(*) +10 +select count(*) from t1 where t='a'; +count(*) +10 +select count(*) from t1 where v='a '; +count(*) +10 +select count(*) from t1 where c='a '; +count(*) +10 +select count(*) from t1 where t='a '; +count(*) +10 +select count(*) from t1 where v between 'a' and 'a '; +count(*) +10 +select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; +count(*) +10 +explain select count(*) from t1 where v='a '; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref v v 13 const # Using where +explain select count(*) from t1 where c='a '; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref c c 11 const # Using where +explain select count(*) from t1 where t='a '; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref t t 13 const # Using where +explain select count(*) from t1 where v like 'a%'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 13 NULL # Using where +explain select count(*) from t1 where v between 'a' and 'a '; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 13 NULL # Using where +explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 13 NULL # Using where +alter table t1 add unique(v); +ERROR 23000: Duplicate entry '{ ' for key 1 +alter table t1 add key(v); +select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a'; +qq +*a*a*a* +*a *a*a * +*a *a*a * +*a *a*a * +*a *a*a * +*a *a*a * +*a *a*a * +*a *a*a * +*a *a*a * +*a *a*a * +explain select * from t1 where v='a'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref v,v_2 v 13 const 7 Using where +drop table t1; +create table t1 (a char(10), unique using btree (a)) engine=heap; +insert into t1 values ('a'); +insert into t1 values ('a '); +ERROR 23000: Duplicate entry 'a' for key 1 +alter table t1 modify a varchar(10); +insert into t1 values ('a '),('a '),('a '),('a '); +ERROR 23000: Duplicate entry 'a ' for key 1 +insert into t1 values ('a '); +ERROR 23000: Duplicate entry 'a ' for key 1 +insert into t1 values ('a '); +ERROR 23000: Duplicate entry 'a ' for key 1 +insert into t1 values ('a '); +ERROR 23000: Duplicate entry 'a ' for key 1 +update t1 set a='a ' where a like 'a '; +update t1 set a='a ' where a like 'a '; +drop table t1; +create table t1 (v varchar(10), c char(10), t varchar(50), key(v(5)), key(c(5)), key(t(5))); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` varchar(10) default NULL, + `c` char(10) default NULL, + `t` varchar(50) default NULL, + KEY `v` (`v`(5)), + KEY `c` (`c`(5)), + KEY `t` (`t`(5)) +) ENGINE=HEAP DEFAULT CHARSET=latin1 +drop table t1; +create table t1 (v varchar(65530), key(v(10))); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` varchar(65530) default NULL, + KEY `v` (`v`(10)) +) ENGINE=HEAP DEFAULT CHARSET=latin1 +insert into t1 values(repeat('a',65530)); +select length(v) from t1 where v=repeat('a',65530); +length(v) +65530 +drop table t1; +set storage_engine=MyISAM; diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 40767a40b82..52eb603f652 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -1421,19 +1421,19 @@ insert t2 select * from t1; insert t3 select * from t1; checksum table t1, t2, t3, t4 quick; Table Checksum -test.t1 968604391 +test.t1 272226711 test.t2 NULL test.t3 NULL test.t4 NULL checksum table t1, t2, t3, t4; Table Checksum -test.t1 968604391 +test.t1 272226711 test.t2 968604391 test.t3 968604391 test.t4 NULL checksum table t1, t2, t3, t4 extended; Table Checksum -test.t1 968604391 +test.t1 272226711 test.t2 968604391 test.t3 968604391 test.t4 NULL @@ -1664,3 +1664,5 @@ select count(*) from t1 where x = 18446744073709551601; count(*) 1 drop table t1; +create table t1 (v varchar(16384)) engine=innodb; +ERROR 42000: Column length too big for column 'v' (max = 255); use BLOB instead diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index b115b926f88..a9d90813660 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -614,19 +614,19 @@ KEY files (fileset_id,fileset_root_id) EXPLAIN SELECT * FROM t2 IGNORE INDEX (files) WHERE fileset_id = 2 AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range PRIMARY PRIMARY 33 NULL 5 Using where +1 SIMPLE t2 range PRIMARY PRIMARY 35 NULL 5 Using where EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2 AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range PRIMARY,files PRIMARY 33 NULL 5 Using where +1 SIMPLE t2 range PRIMARY,files PRIMARY 35 NULL 5 Using where EXPLAIN SELECT * FROM t1 WHERE fileset_id = 2 AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,files PRIMARY 33 NULL 5 Using where +1 SIMPLE t1 range PRIMARY,files PRIMARY 35 NULL 5 Using where EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2 AND file_code = '0000000115' LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 const PRIMARY,files PRIMARY 33 const,const 1 +1 SIMPLE t2 ref PRIMARY,files PRIMARY 35 const,const 1 Using where DROP TABLE t2, t1; create table t1 (x int, y int, index xy(x, y)); create table t2 (x int, y int, index xy(x, y)); diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index 26dcce43d08..39dfef3a871 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -408,8 +408,8 @@ Table Op Msg_type Msg_text test.t1 repair status OK select concat(a,'.') from t1 where a='aaa'; concat(a,'.') -aaa. aaa . +aaa. select concat(a,'.') from t1 where binary a='aaa'; concat(a,'.') aaa. @@ -461,20 +461,27 @@ concat(a,'.') a . drop table t1; 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 '); +insert into t1 (b) values ('a'),('b'),('c'); select concat(b,'.') from t1; concat(b,'.') a. -a . -a . +b. +c. update t1 set b='b ' where a=2; update t1 set b='b ' where a > 1; ERROR 23000: Duplicate entry 'b ' for key 2 +insert into t1 (b) values ('b'); +ERROR 23000: Duplicate entry 'b' for key 2 +select * from t1; +a b +1 a +2 b +3 c delete from t1 where b='b'; select a,concat(b,'.') from t1; a concat(b,'.') 1 a. -3 a . +3 c. drop table t1; create table t1 (a int not null); create table t2 (a int not null, primary key (a)); @@ -506,18 +513,18 @@ insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, ""); insert t2 select * from t1; checksum table t1, t2, t3 quick; Table Checksum -test.t1 968604391 +test.t1 272226711 test.t2 NULL test.t3 NULL checksum table t1, t2, t3; Table Checksum -test.t1 968604391 -test.t2 968604391 +test.t1 272226711 +test.t2 272226711 test.t3 NULL checksum table t1, t2, t3 extended; Table Checksum -test.t1 968604391 -test.t2 968604391 +test.t1 272226711 +test.t2 272226711 test.t3 NULL drop table t1,t2; create table t1 (a int, key (a)); @@ -554,3 +561,384 @@ select count(*) from t1 where a is null; count(*) 2 drop table t1; +set storage_engine=MyISAM; +drop table if exists t1,t2,t3; +--- Testing varchar --- +--- Testing varchar --- +create table t1 (v varchar(10), c char(10), t text); +insert into t1 values('+ ', '+ ', '+ '); +set @a=repeat(' ',20); +insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a)); +Warnings: +Warning 1265 Data truncated for column 'v' at row 1 +select concat('*',v,'*',c,'*',t,'*') from t1; +concat('*',v,'*',c,'*',t,'*') +*+ *+*+ * +*+ *+*+ * +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` varchar(10) default NULL, + `c` char(10) default NULL, + `t` text +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +create table t2 like t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `v` varchar(10) default NULL, + `c` char(10) default NULL, + `t` text +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +create table t3 select * from t1; +show create table t3; +Table Create Table +t3 CREATE TABLE `t3` ( + `v` varchar(10) default NULL, + `c` char(10) default NULL, + `t` text +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +alter table t1 modify c varchar(10); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` varchar(10) default NULL, + `c` varchar(10) default NULL, + `t` text +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +alter table t1 modify v char(10); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` char(10) default NULL, + `c` varchar(10) default NULL, + `t` text +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +alter table t1 modify t varchar(10); +Warnings: +Warning 1265 Data truncated for column 't' at row 2 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` char(10) default NULL, + `c` varchar(10) default NULL, + `t` varchar(10) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select concat('*',v,'*',c,'*',t,'*') from t1; +concat('*',v,'*',c,'*',t,'*') +*+*+*+ * +*+*+*+ * +drop table t1,t2,t3; +create table t1 (v varchar(10), c char(10), t text, key(v), key(c), key(t(10))); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` varchar(10) default NULL, + `c` char(10) default NULL, + `t` text, + KEY `v` (`v`), + KEY `c` (`c`), + KEY `t` (`t`(10)) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select count(*) from t1; +count(*) +270 +insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1))); +select count(*) from t1 where v='a'; +count(*) +10 +select count(*) from t1 where c='a'; +count(*) +10 +select count(*) from t1 where t='a'; +count(*) +10 +select count(*) from t1 where v='a '; +count(*) +10 +select count(*) from t1 where c='a '; +count(*) +10 +select count(*) from t1 where t='a '; +count(*) +10 +select count(*) from t1 where v between 'a' and 'a '; +count(*) +10 +select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; +count(*) +10 +select count(*) from t1 where v like 'a%'; +count(*) +11 +select count(*) from t1 where c like 'a%'; +count(*) +11 +select count(*) from t1 where t like 'a%'; +count(*) +11 +select count(*) from t1 where v like 'a %'; +count(*) +9 +explain select count(*) from t1 where v='a '; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref v v 13 const 9 Using where; Using index +explain select count(*) from t1 where c='a '; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref c c 11 const 9 Using where; Using index +explain select count(*) from t1 where t='a '; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range t t 13 NULL 9 Using where +explain select count(*) from t1 where v like 'a%'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 13 NULL 10 Using where; Using index +explain select count(*) from t1 where v between 'a' and 'a '; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 13 NULL 9 Using where; Using index +explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 13 NULL 9 Using where; Using index +alter table t1 add unique(v); +ERROR 23000: Duplicate entry '{ ' for key 1 +alter table t1 add key(v); +select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a'; +qq +*a*a*a* +*a *a*a * +*a *a*a * +*a *a*a * +*a *a*a * +*a *a*a * +*a *a*a * +*a *a*a * +*a *a*a * +*a *a*a * +explain select * from t1 where v='a'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref v,v_2 v_2 13 const 7 Using where +select v,count(*) from t1 group by v limit 10; +v count(*) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select v,count(t) from t1 group by v limit 10; +v count(t) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select v,count(c) from t1 group by v limit 10; +v count(c) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select sql_big_result v,count(t) from t1 group by v limit 10; +v count(t) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select sql_big_result v,count(c) from t1 group by v limit 10; +v count(c) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select c,count(*) from t1 group by c limit 10; +c count(*) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select c,count(t) from t1 group by c limit 10; +c count(t) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select sql_big_result c,count(t) from t1 group by c limit 10; +c count(t) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select t,count(*) from t1 group by t limit 10; +t count(*) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select t,count(t) from t1 group by t limit 10; +t count(t) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select sql_big_result t,count(t) from t1 group by t limit 10; +t count(t) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +drop table t1; +create table t1 (a char(10), unique (a)); +insert into t1 values ('a '); +insert into t1 values ('a '); +ERROR 23000: Duplicate entry 'a' for key 1 +alter table t1 modify a varchar(10); +insert into t1 values ('a '),('a '),('a '),('a '); +ERROR 23000: Duplicate entry 'a ' for key 1 +insert into t1 values ('a '); +ERROR 23000: Duplicate entry 'a ' for key 1 +insert into t1 values ('a '); +ERROR 23000: Duplicate entry 'a ' for key 1 +insert into t1 values ('a '); +ERROR 23000: Duplicate entry 'a ' for key 1 +update t1 set a='a ' where a like 'a%'; +select concat(a,'.') from t1; +concat(a,'.') +a . +update t1 set a='abc ' where a like 'a '; +select concat(a,'.') from t1; +concat(a,'.') +a . +update t1 set a='a ' where a like 'a %'; +select concat(a,'.') from t1; +concat(a,'.') +a . +update t1 set a='a ' where a like 'a '; +select concat(a,'.') from t1; +concat(a,'.') +a . +drop table t1; +create table t1 (v varchar(10), c char(10), t text, key(v(5)), key(c(5)), key(t(5))); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` varchar(10) default NULL, + `c` char(10) default NULL, + `t` text, + KEY `v` (`v`(5)), + KEY `c` (`c`(5)), + KEY `t` (`t`(5)) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 (v char(10) character set utf8); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` char(10) character set utf8 default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 (v varchar(10), c char(10)) row_format=fixed; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` varchar(10) default NULL, + `c` char(10) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED +insert into t1 values('a','a'),('a ','a '); +select concat('*',v,'*',c,'*') from t1; +concat('*',v,'*',c,'*') +*a*a* +*a *a* +drop table t1; +create table t1 (v varchar(65530), key(v(10))); +insert into t1 values(repeat('a',65530)); +select length(v) from t1 where v=repeat('a',65530); +length(v) +65530 +drop table t1; +create table t1 (v varchar(65530), key(v)); +Warnings: +Warning 1071 Specified key was too long; max key length is 1000 bytes +drop table if exists t1; +create table t1 (v varchar(65536)); +Warnings: +Note 1246 Converting column 'v' from VARCHAR to TEXT +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` mediumtext +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 (v varchar(65530) character set utf8); +Warnings: +Note 1246 Converting column 'v' from VARCHAR to TEXT +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` mediumtext character set utf8 +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +set storage_engine=MyISAM; +create table t1 (v varchar(65535)); +ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index 2c8b8816da5..034341956c3 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -106,7 +106,7 @@ INSERT INTO t1 VALUES (1, "test", "tes"), (2, "TEST", "TES"); <table_structure name="t1"> <field Field="a" Type="int(11)" Null="YES" Key="" Extra="" /> <field Field="b" Type="text" Null="YES" Key="" Extra="" /> - <field Field="c" Type="char(3)" Null="YES" Key="" Extra="" /> + <field Field="c" Type="varchar(3)" Null="YES" Key="" Extra="" /> </table_structure> <table_data name="t1"> <row> diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 15643f29513..9c3d5509ce8 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -272,7 +272,7 @@ create table t1 (a int not null, b int, c varchar(10), key (a, b, c)); insert into t1 values (1, NULL, NULL), (1, NULL, 'b'), (1, 1, NULL), (1, 1, 'b'), (1, 1, 'b'), (2, 1, 'a'), (2, 1, 'b'), (2, 2, 'a'), (2, 2, 'b'), (2, 3, 'c'),(1,3,'b'); explain select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index a a 20 NULL 11 Using where; Using index +1 SIMPLE t1 index a a 22 NULL 11 Using where; Using index select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc; a b c 1 NULL b @@ -348,7 +348,7 @@ Warning 1265 Data truncated for column 'b' at row 2 Warning 1265 Data truncated for column 'c' at row 3 explain select * from t1 order by a, b, c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL a 18 NULL 11 Using index +1 SIMPLE t1 index NULL a 20 NULL 11 Using index select * from t1 order by a, b, c; a b c 1 0 @@ -364,7 +364,7 @@ a b c 2 3 c explain select * from t1 order by a desc, b desc, c desc; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL a 18 NULL 11 Using index +1 SIMPLE t1 index NULL a 20 NULL 11 Using index select * from t1 order by a desc, b desc, c desc; a b c 2 3 c @@ -380,7 +380,7 @@ a b c 1 0 explain select * from t1 where (a = 1 and b = 1 and c = 'b') or (a > 2) order by a desc; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 18 NULL 3 Using where; Using index +1 SIMPLE t1 range a a 20 NULL 3 Using where; Using index select * from t1 where (a = 1 and b = 1 and c = 'b') or (a > 2) order by a desc; a b c 1 1 b @@ -574,7 +574,7 @@ KEY StringField (FieldKey,StringVal(32)) INSERT INTO t1 VALUES ('0',3,'0'),('0',2,'1'),('0',1,'2'),('1',2,'1'),('1',1,'3'), ('1',0,'2'),('2',3,'0'),('2',2,'1'),('2',1,'2'),('2',3,'0'),('2',2,'1'),('2',1,'2'),('3',2,'1'),('3',1,'2'),('3','3','3'); EXPLAIN SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref FieldKey,LongField,StringField LongField 36 const 3 Using where +1 SIMPLE t1 ref FieldKey,LongField,StringField LongField 38 const 3 Using where SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal; FieldKey LongVal StringVal 1 0 2 @@ -582,7 +582,7 @@ FieldKey LongVal StringVal 1 2 1 EXPLAIN SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range FieldKey,LongField,StringField FieldKey 36 NULL 4 Using where; Using filesort +1 SIMPLE t1 range FieldKey,LongField,StringField FieldKey 38 NULL 4 Using where; Using filesort SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal; FieldKey LongVal StringVal 3 1 2 @@ -590,7 +590,7 @@ FieldKey LongVal StringVal 3 3 3 EXPLAIN SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range FieldKey,LongField,StringField LongField 36 NULL 4 Using where +1 SIMPLE t1 range FieldKey,LongField,StringField LongField 38 NULL 4 Using where SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal; FieldKey LongVal StringVal 3 1 2 diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index e0f230fa579..27981798c09 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -207,10 +207,10 @@ create table t1 ( a int primary key, b varchar(30)) engine = MYISAM ; prepare stmt1 from ' show table status from test like ''t1%'' '; execute stmt1; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment -t1 MyISAM 9 Dynamic 0 0 0 4294967295 1024 0 NULL # # # latin1_swedish_ci NULL +t1 MyISAM 10 Dynamic 0 0 0 4294967295 1024 0 NULL # # # latin1_swedish_ci NULL show table status from test like 't1%' ; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment -t1 MyISAM 9 Dynamic 0 0 0 4294967295 1024 0 NULL # # # latin1_swedish_ci NULL +t1 MyISAM 10 Dynamic 0 0 0 4294967295 1024 0 NULL # # # latin1_swedish_ci NULL deallocate prepare stmt1 ; drop table t1; create table t1(a varchar(2), b varchar(3)); diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result index fe7aa623023..4b09f9e7f4e 100644 --- a/mysql-test/r/ps_1general.result +++ b/mysql-test/r/ps_1general.result @@ -276,7 +276,7 @@ t2 MyISAM 9 Fixed 0 0 0 64424509439 1024 0 NULL # # # latin1_swedish_ci NULL prepare stmt4 from ' show table status from test like ''t9%'' '; execute stmt4; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment -t9 MyISAM 9 Dynamic 2 220 440 4294967295 2048 0 NULL # # # latin1_swedish_ci NULL +t9 MyISAM 10 Dynamic 2 222 444 4294967295 2048 0 NULL # # # latin1_swedish_ci NULL prepare stmt4 from ' show status like ''Threads_running'' '; execute stmt4; Variable_name Value @@ -290,7 +290,7 @@ execute stmt4; prepare stmt4 from ' show full processlist '; execute stmt4; Id User Host db Command Time State Info -number root localhost test Query 0 NULL show full processlist +number root localhost test Query seconds NULL show full processlist prepare stmt4 from ' show grants for user '; prepare stmt4 from ' show create table t2 '; ERROR HY000: This command is not supported in the prepared statement protocol yet diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result index 6773370fbc5..d1828de7c9d 100644 --- a/mysql-test/r/ps_2myisam.result +++ b/mysql-test/r/ps_2myisam.result @@ -69,7 +69,7 @@ def test t9 t9 c17 c17 13 4 4 Y 32864 0 63 def test t9 t9 c18 c18 1 1 1 Y 32768 0 63 def test t9 t9 c19 c19 1 1 1 Y 32768 0 63 def test t9 t9 c20 c20 254 1 1 Y 0 0 8 -def test t9 t9 c21 c21 253 10 10 Y 0 0 8 +def test t9 t9 c21 c21 254 10 10 Y 0 0 8 def test t9 t9 c22 c22 253 30 30 Y 0 0 8 def test t9 t9 c23 c23 252 255 8 Y 144 0 63 def test t9 t9 c24 c24 252 255 8 Y 16 0 8 @@ -1705,8 +1705,8 @@ affected rows: 3 info: Records: 3 Duplicates: 0 Warnings: 0 select a,b from t2 order by a ; a b -3 duplicate -4 duplicate +3 duplicate +4 duplicate 103 three delete from t2 ; prepare stmt1 from ' insert into t2 (b,a) @@ -1777,11 +1777,11 @@ t5 CREATE TABLE `t5` ( `param04` longtext, `const05` binary(3) NOT NULL default '', `param05` longblob, - `const06` varchar(10) NOT NULL default '', + `const06` char(10) NOT NULL default '', `param06` longtext, `const07` date default NULL, `param07` longblob, - `const08` varchar(19) NOT NULL default '', + `const08` char(19) NOT NULL default '', `param08` longtext, `const09` datetime default NULL, `param09` longblob, @@ -1807,11 +1807,11 @@ def test t5 t5 const04 const04 254 3 3 N 1 0 8 def test t5 t5 param04 param04 252 16777215 3 Y 16 0 8 def test t5 t5 const05 const05 254 3 3 N 129 0 63 def test t5 t5 param05 param05 252 16777215 3 Y 144 0 63 -def test t5 t5 const06 const06 253 10 10 N 1 0 8 +def test t5 t5 const06 const06 254 10 10 N 1 0 8 def test t5 t5 param06 param06 252 16777215 10 Y 16 0 8 def test t5 t5 const07 const07 10 10 10 Y 128 0 63 def test t5 t5 param07 param07 252 16777215 10 Y 144 0 63 -def test t5 t5 const08 const08 253 19 19 N 1 0 8 +def test t5 t5 const08 const08 254 19 19 N 1 0 8 def test t5 t5 param08 param08 252 16777215 19 Y 16 0 8 def test t5 t5 const09 const09 12 19 19 Y 128 0 63 def test t5 t5 param09 param09 252 16777215 19 Y 144 0 63 diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result index 09f19c3763c..bdf481ac4eb 100644 --- a/mysql-test/r/ps_3innodb.result +++ b/mysql-test/r/ps_3innodb.result @@ -69,8 +69,8 @@ def test t9 t9 c17 c17 13 4 4 Y 32864 0 63 def test t9 t9 c18 c18 1 1 1 Y 32768 0 63 def test t9 t9 c19 c19 1 1 1 Y 32768 0 63 def test t9 t9 c20 c20 254 1 1 Y 0 0 8 -def test t9 t9 c21 c21 253 10 10 Y 0 0 8 -def test t9 t9 c22 c22 253 30 30 Y 0 0 8 +def test t9 t9 c21 c21 254 10 10 Y 0 0 8 +def test t9 t9 c22 c22 254 30 30 Y 0 0 8 def test t9 t9 c23 c23 252 255 8 Y 144 0 63 def test t9 t9 c24 c24 252 255 8 Y 16 0 8 def test t9 t9 c25 c25 252 65535 4 Y 144 0 63 @@ -1760,11 +1760,11 @@ t5 CREATE TABLE `t5` ( `param04` longtext, `const05` binary(3) NOT NULL default '', `param05` longblob, - `const06` varchar(10) NOT NULL default '', + `const06` char(10) NOT NULL default '', `param06` longtext, `const07` date default NULL, `param07` longblob, - `const08` varchar(19) NOT NULL default '', + `const08` char(19) NOT NULL default '', `param08` longtext, `const09` datetime default NULL, `param09` longblob, @@ -1790,11 +1790,11 @@ def test t5 t5 const04 const04 254 3 3 N 1 0 8 def test t5 t5 param04 param04 252 16777215 3 Y 16 0 8 def test t5 t5 const05 const05 254 3 3 N 129 0 63 def test t5 t5 param05 param05 252 16777215 3 Y 144 0 63 -def test t5 t5 const06 const06 253 10 10 N 1 0 8 +def test t5 t5 const06 const06 254 10 10 N 1 0 8 def test t5 t5 param06 param06 252 16777215 10 Y 16 0 8 def test t5 t5 const07 const07 10 10 10 Y 128 0 63 def test t5 t5 param07 param07 252 16777215 10 Y 144 0 63 -def test t5 t5 const08 const08 253 19 19 N 1 0 8 +def test t5 t5 const08 const08 254 19 19 N 1 0 8 def test t5 t5 param08 param08 252 16777215 19 Y 16 0 8 def test t5 t5 const09 const09 12 19 19 Y 128 0 63 def test t5 t5 param09 param09 252 16777215 19 Y 144 0 63 diff --git a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result index 427fee8e757..fd861df0a0f 100644 --- a/mysql-test/r/ps_4heap.result +++ b/mysql-test/r/ps_4heap.result @@ -13,9 +13,9 @@ c5 integer, c6 bigint, c7 float, c8 double, c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4), c13 date, c14 datetime, c15 timestamp(14), c16 time, c17 year, c18 bit, c19 bool, c20 char, -c21 char(10), c22 varchar(30), c23 char(100), c24 char(100), -c25 char(100), c26 char(100), c27 char(100), c28 char(100), -c29 char(100), c30 char(100), c31 enum('one', 'two', 'three'), +c21 char(10), c22 varchar(30), c23 varchar(100), c24 varchar(100), +c25 varchar(100), c26 varchar(100), c27 varchar(100), c28 varchar(100), +c29 varchar(100), c30 varchar(100), c31 enum('one', 'two', 'three'), c32 set('monday', 'tuesday', 'wednesday'), primary key(c1) ) engine = 'HEAP' ; @@ -70,7 +70,7 @@ def test t9 t9 c17 c17 13 4 4 Y 32864 0 63 def test t9 t9 c18 c18 1 1 1 Y 32768 0 63 def test t9 t9 c19 c19 1 1 1 Y 32768 0 63 def test t9 t9 c20 c20 254 1 1 Y 0 0 8 -def test t9 t9 c21 c21 253 10 10 Y 0 0 8 +def test t9 t9 c21 c21 254 10 10 Y 0 0 8 def test t9 t9 c22 c22 253 30 30 Y 0 0 8 def test t9 t9 c23 c23 253 100 8 Y 0 0 8 def test t9 t9 c24 c24 253 100 8 Y 0 0 8 @@ -1689,8 +1689,8 @@ affected rows: 3 info: Records: 3 Duplicates: 0 Warnings: 0 select a,b from t2 order by a ; a b -3 duplicate -4 duplicate +3 duplicate +4 duplicate 103 three delete from t2 ; prepare stmt1 from ' insert into t2 (b,a) @@ -1761,11 +1761,11 @@ t5 CREATE TABLE `t5` ( `param04` longtext, `const05` binary(3) NOT NULL default '', `param05` longblob, - `const06` varchar(10) NOT NULL default '', + `const06` char(10) NOT NULL default '', `param06` longtext, `const07` date default NULL, `param07` longblob, - `const08` varchar(19) NOT NULL default '', + `const08` char(19) NOT NULL default '', `param08` longtext, `const09` datetime default NULL, `param09` longblob, @@ -1791,11 +1791,11 @@ def test t5 t5 const04 const04 254 3 3 N 1 0 8 def test t5 t5 param04 param04 252 16777215 3 Y 16 0 8 def test t5 t5 const05 const05 254 3 3 N 129 0 63 def test t5 t5 param05 param05 252 16777215 3 Y 144 0 63 -def test t5 t5 const06 const06 253 10 10 N 1 0 8 +def test t5 t5 const06 const06 254 10 10 N 1 0 8 def test t5 t5 param06 param06 252 16777215 10 Y 16 0 8 def test t5 t5 const07 const07 10 10 10 Y 128 0 63 def test t5 t5 param07 param07 252 16777215 10 Y 144 0 63 -def test t5 t5 const08 const08 253 19 19 N 1 0 8 +def test t5 t5 const08 const08 254 19 19 N 1 0 8 def test t5 t5 param08 param08 252 16777215 19 Y 16 0 8 def test t5 t5 const09 const09 12 19 19 Y 128 0 63 def test t5 t5 param09 param09 252 16777215 19 Y 144 0 63 diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result index 51d842ae000..2425f4262f5 100644 --- a/mysql-test/r/ps_5merge.result +++ b/mysql-test/r/ps_5merge.result @@ -112,7 +112,7 @@ def test t9 t9 c17 c17 13 4 4 Y 32864 0 63 def test t9 t9 c18 c18 1 1 1 Y 32768 0 63 def test t9 t9 c19 c19 1 1 1 Y 32768 0 63 def test t9 t9 c20 c20 254 1 1 Y 0 0 8 -def test t9 t9 c21 c21 253 10 10 Y 0 0 8 +def test t9 t9 c21 c21 254 10 10 Y 0 0 8 def test t9 t9 c22 c22 253 30 30 Y 0 0 8 def test t9 t9 c23 c23 252 255 8 Y 144 0 63 def test t9 t9 c24 c24 252 255 8 Y 16 0 8 @@ -1700,11 +1700,11 @@ t5 CREATE TABLE `t5` ( `param04` longtext, `const05` binary(3) NOT NULL default '', `param05` longblob, - `const06` varchar(10) NOT NULL default '', + `const06` char(10) NOT NULL default '', `param06` longtext, `const07` date default NULL, `param07` longblob, - `const08` varchar(19) NOT NULL default '', + `const08` char(19) NOT NULL default '', `param08` longtext, `const09` datetime default NULL, `param09` longblob, @@ -1730,11 +1730,11 @@ def test t5 t5 const04 const04 254 3 3 N 1 0 8 def test t5 t5 param04 param04 252 16777215 3 Y 16 0 8 def test t5 t5 const05 const05 254 3 3 N 129 0 63 def test t5 t5 param05 param05 252 16777215 3 Y 144 0 63 -def test t5 t5 const06 const06 253 10 10 N 1 0 8 +def test t5 t5 const06 const06 254 10 10 N 1 0 8 def test t5 t5 param06 param06 252 16777215 10 Y 16 0 8 def test t5 t5 const07 const07 10 10 10 Y 128 0 63 def test t5 t5 param07 param07 252 16777215 10 Y 144 0 63 -def test t5 t5 const08 const08 253 19 19 N 1 0 8 +def test t5 t5 const08 const08 254 19 19 N 1 0 8 def test t5 t5 param08 param08 252 16777215 19 Y 16 0 8 def test t5 t5 const09 const09 12 19 19 Y 128 0 63 def test t5 t5 param09 param09 252 16777215 19 Y 144 0 63 @@ -3121,7 +3121,7 @@ def test t9 t9 c17 c17 13 4 4 Y 32864 0 63 def test t9 t9 c18 c18 1 1 1 Y 32768 0 63 def test t9 t9 c19 c19 1 1 1 Y 32768 0 63 def test t9 t9 c20 c20 254 1 1 Y 0 0 8 -def test t9 t9 c21 c21 253 10 10 Y 0 0 8 +def test t9 t9 c21 c21 254 10 10 Y 0 0 8 def test t9 t9 c22 c22 253 30 30 Y 0 0 8 def test t9 t9 c23 c23 252 255 8 Y 144 0 63 def test t9 t9 c24 c24 252 255 8 Y 16 0 8 @@ -4709,11 +4709,11 @@ t5 CREATE TABLE `t5` ( `param04` longtext, `const05` binary(3) NOT NULL default '', `param05` longblob, - `const06` varchar(10) NOT NULL default '', + `const06` char(10) NOT NULL default '', `param06` longtext, `const07` date default NULL, `param07` longblob, - `const08` varchar(19) NOT NULL default '', + `const08` char(19) NOT NULL default '', `param08` longtext, `const09` datetime default NULL, `param09` longblob, @@ -4739,11 +4739,11 @@ def test t5 t5 const04 const04 254 3 3 N 1 0 8 def test t5 t5 param04 param04 252 16777215 3 Y 16 0 8 def test t5 t5 const05 const05 254 3 3 N 129 0 63 def test t5 t5 param05 param05 252 16777215 3 Y 144 0 63 -def test t5 t5 const06 const06 253 10 10 N 1 0 8 +def test t5 t5 const06 const06 254 10 10 N 1 0 8 def test t5 t5 param06 param06 252 16777215 10 Y 16 0 8 def test t5 t5 const07 const07 10 10 10 Y 128 0 63 def test t5 t5 param07 param07 252 16777215 10 Y 144 0 63 -def test t5 t5 const08 const08 253 19 19 N 1 0 8 +def test t5 t5 const08 const08 254 19 19 N 1 0 8 def test t5 t5 param08 param08 252 16777215 19 Y 16 0 8 def test t5 t5 const09 const09 12 19 19 Y 128 0 63 def test t5 t5 param09 param09 252 16777215 19 Y 144 0 63 diff --git a/mysql-test/r/ps_6bdb.result b/mysql-test/r/ps_6bdb.result index 85cb8af652e..5337f99894e 100644 --- a/mysql-test/r/ps_6bdb.result +++ b/mysql-test/r/ps_6bdb.result @@ -69,7 +69,7 @@ def test t9 t9 c17 c17 13 4 4 Y 32864 0 63 def test t9 t9 c18 c18 1 1 1 Y 32768 0 63 def test t9 t9 c19 c19 1 1 1 Y 32768 0 63 def test t9 t9 c20 c20 254 1 1 Y 0 0 8 -def test t9 t9 c21 c21 253 10 10 Y 0 0 8 +def test t9 t9 c21 c21 254 10 10 Y 0 0 8 def test t9 t9 c22 c22 253 30 30 Y 0 0 8 def test t9 t9 c23 c23 252 255 8 Y 144 0 63 def test t9 t9 c24 c24 252 255 8 Y 16 0 8 @@ -1688,8 +1688,8 @@ affected rows: 3 info: Records: 3 Duplicates: 0 Warnings: 0 select a,b from t2 order by a ; a b -3 duplicate -4 duplicate +3 duplicate +4 duplicate 103 three delete from t2 ; prepare stmt1 from ' insert into t2 (b,a) @@ -1760,11 +1760,11 @@ t5 CREATE TABLE `t5` ( `param04` longtext, `const05` binary(3) NOT NULL default '', `param05` longblob, - `const06` varchar(10) NOT NULL default '', + `const06` char(10) NOT NULL default '', `param06` longtext, `const07` date default NULL, `param07` longblob, - `const08` varchar(19) NOT NULL default '', + `const08` char(19) NOT NULL default '', `param08` longtext, `const09` datetime default NULL, `param09` longblob, @@ -1790,11 +1790,11 @@ def test t5 t5 const04 const04 254 3 3 N 1 0 8 def test t5 t5 param04 param04 252 16777215 3 Y 16 0 8 def test t5 t5 const05 const05 254 3 3 N 129 0 63 def test t5 t5 param05 param05 252 16777215 3 Y 144 0 63 -def test t5 t5 const06 const06 253 10 10 N 1 0 8 +def test t5 t5 const06 const06 254 10 10 N 1 0 8 def test t5 t5 param06 param06 252 16777215 10 Y 16 0 8 def test t5 t5 const07 const07 10 10 10 Y 128 0 63 def test t5 t5 param07 param07 252 16777215 10 Y 144 0 63 -def test t5 t5 const08 const08 253 19 19 N 1 0 8 +def test t5 t5 const08 const08 254 19 19 N 1 0 8 def test t5 t5 param08 param08 252 16777215 19 Y 16 0 8 def test t5 t5 const09 const09 12 19 19 Y 128 0 63 def test t5 t5 param09 param09 252 16777215 19 Y 144 0 63 diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index e0e839abdce..cda5ebaf123 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -2309,7 +2309,7 @@ select * from t2 where s = 'one'; s select * from t3 where s = 'one'; s -one +one select * from t1,t2 where t1.s = t2.s; s s two two diff --git a/mysql-test/r/select.result.es b/mysql-test/r/select.result.es index 2ff58372d6d..6d2a848db8c 100644 --- a/mysql-test/r/select.result.es +++ b/mysql-test/r/select.result.es @@ -1,4 +1,6 @@ drop table if exists t1,t2,t3,t4; +drop table if exists t1_1,t1_2,t9_1,t9_2; +drop view if exists v1; CREATE TABLE t1 ( Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL, Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL @@ -2074,10 +2076,8 @@ INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12 SELECT a.gvid, (SUM(CASE b.sampletid WHEN 140 THEN b.samplevalue ELSE 0 END)) as the_success,(SUM(CASE b.sampletid WHEN 141 THEN b.samplevalue ELSE 0 END)) as the_fail,(SUM(CASE b.sampletid WHEN 142 THEN b.samplevalue ELSE 0 END)) as the_size,(SUM(CASE b.sampletid WHEN 143 THEN b.samplevalue ELSE 0 END)) as the_time FROM t1 a, t2 b WHERE a.hmid = b.hmid AND a.volid = b.volid AND b.sampletime >= 'wrong-date-value' AND b.sampletime < 'wrong-date-value' AND b.sampletid IN (140, 141, 142, 143) GROUP BY a.gvid; gvid the_success the_fail the_size the_time Warnings: -Warning 1292 Truncated incorrect datetime value: 'wrong-date-value' -Warning 1292 Truncated incorrect datetime value: 'wrong-date-value' -Warning 1292 Truncated incorrect datetime value: 'wrong-date-value' -Warning 1292 Truncated incorrect datetime value: 'wrong-date-value' +Warning 1292 Incorrect datetime value: 'wrong-date-value' for column 'sampletime' at row 1 +Warning 1292 Incorrect datetime value: 'wrong-date-value' for column 'sampletime' at row 1 SELECT a.gvid, (SUM(CASE b.sampletid WHEN 140 THEN b.samplevalue ELSE 0 END)) as the_success,(SUM(CASE b.sampletid WHEN 141 THEN b.samplevalue ELSE 0 END)) as the_fail,(SUM(CASE b.sampletid WHEN 142 THEN b.samplevalue ELSE 0 END)) as the_size,(SUM(CASE b.sampletid WHEN 143 THEN b.samplevalue ELSE 0 END)) as the_time FROM t1 a, t2 b WHERE a.hmid = b.hmid AND a.volid = b.volid AND b.sampletime >= NULL AND b.sampletime < NULL AND b.sampletid IN (140, 141, 142, 143) GROUP BY a.gvid; gvid the_success the_fail the_size the_time DROP TABLE t1,t2; @@ -2340,7 +2340,7 @@ select * from t2 where s = 'one'; s select * from t3 where s = 'one'; s -one +one select * from t1,t2 where t1.s = t2.s; s s two two diff --git a/mysql-test/r/select_found.result b/mysql-test/r/select_found.result index 00dbcb54d93..5a2f26a7105 100644 --- a/mysql-test/r/select_found.result +++ b/mysql-test/r/select_found.result @@ -84,7 +84,7 @@ UNIQUE KEY e_n (email,name) EXPLAIN SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 system PRIMARY,kid NULL NULL NULL 0 const row not found -1 SIMPLE t2 index NULL e_n 100 NULL 200 +1 SIMPLE t2 index NULL e_n 104 NULL 200 SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10; email email1 diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index be4b9622983..931fb8b20b9 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -154,7 +154,7 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, - `b` char(10) default NULL, + `b` varchar(10) default NULL, KEY `b` (`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MIN_ROWS=10 MAX_ROWS=100 AVG_ROW_LENGTH=10 PACK_KEYS=1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED COMMENT='test' alter table t1 MAX_ROWS=200 ROW_FORMAT=dynamic PACK_KEYS=0; diff --git a/mysql-test/r/strict.result b/mysql-test/r/strict.result index 19c4def9b32..4c2dcd19a9c 100644 --- a/mysql-test/r/strict.result +++ b/mysql-test/r/strict.result @@ -810,12 +810,14 @@ col1 col2 NULL NULL 1.79769313486232e+308 0 DROP TABLE t1; -CREATE TABLE t1 (col1 CHAR(5), col2 VARCHAR(5)); -INSERT INTO t1 VALUES ('hello', 'hello'),('he', 'he'),('hello ', 'hello '); +CREATE TABLE t1 (col1 CHAR(5), col2 VARCHAR(6)); +INSERT INTO t1 VALUES ('hello', 'hello'),('he', 'he'),('hello ', 'hello '); INSERT INTO t1 (col1) VALUES ('hellobob'); ERROR 01000: Data truncated for column 'col1' at row 1 INSERT INTO t1 (col2) VALUES ('hellobob'); ERROR 01000: Data truncated for column 'col2' at row 1 +INSERT INTO t1 (col2) VALUES ('hello '); +ERROR 01000: Data truncated for column 'col2' at row 1 UPDATE t1 SET col1 ='hellobob' WHERE col1 ='he'; ERROR 01000: Data truncated for column 'col1' at row 2 UPDATE t1 SET col2 ='hellobob' WHERE col2 ='he'; @@ -830,9 +832,9 @@ Warning 1265 Data truncated for column 'col2' at row 2 SELECT * FROM t1; col1 col2 hello hello -he hello -hello hello -hello hello +he hellot +hello hello +hello hellob DROP TABLE t1; CREATE TABLE t1 (col1 enum('red','blue','green')); INSERT INTO t1 VALUES ('red'),('blue'),('green'); diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index ceb8919b26a..03965abd821 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -361,10 +361,10 @@ INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1'); INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1'); EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t8 const PRIMARY PRIMARY 35 const 1 Using index -4 SUBQUERY t8 const PRIMARY PRIMARY 35 1 Using index -2 SUBQUERY t8 const PRIMARY PRIMARY 35 const 1 -3 SUBQUERY t8 const PRIMARY PRIMARY 35 1 Using index +1 PRIMARY t8 ref PRIMARY PRIMARY 37 const 1 Using where; Using index +4 SUBQUERY t8 ref PRIMARY PRIMARY 37 1 +2 SUBQUERY t8 ref PRIMARY PRIMARY 37 const 1 Using where +3 SUBQUERY t8 ref PRIMARY PRIMARY 37 1 Warnings: Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` AS `email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select `test`.`t8`.`pseudo` AS `pseudo` from `test`.`t8` where (`test`.`t8`.`pseudo` = _latin1'joce')))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select `test`.`t8`.`pseudo` AS `pseudo` from `test`.`t8` where (`test`.`t8`.`pseudo` = _latin1'joce'))) SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM @@ -390,13 +390,13 @@ INSERT INTO t1 (topic,date,pseudo) VALUES ('43506','2002-10-02','joce'),('40143','2002-08-03','joce'); EXPLAIN EXTENDED SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 41 NULL 2 Using where; Using index +1 SIMPLE t1 index NULL PRIMARY 43 NULL 2 Using where; Using index Warnings: Note 1003 select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = 20020803) EXPLAIN EXTENDED SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 SUBQUERY t1 index NULL PRIMARY 41 NULL 2 Using where; Using index +2 SUBQUERY t1 index NULL PRIMARY 43 NULL 2 Using where; Using index Warnings: Note 1003 select (select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = 20020803)) AS `(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')` SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'; diff --git a/mysql-test/r/system_mysql_db.result b/mysql-test/r/system_mysql_db.result index 35d7a78ab56..400b508ff50 100644 --- a/mysql-test/r/system_mysql_db.result +++ b/mysql-test/r/system_mysql_db.result @@ -63,9 +63,9 @@ host CREATE TABLE `host` ( show create table user; Table Create Table user CREATE TABLE `user` ( - `Host` varchar(60) collate utf8_bin NOT NULL default '', - `User` varchar(16) collate utf8_bin NOT NULL default '', - `Password` varchar(41) collate utf8_bin NOT NULL default '', + `Host` char(60) collate utf8_bin NOT NULL default '', + `User` char(16) collate utf8_bin NOT NULL default '', + `Password` char(41) collate utf8_bin NOT NULL default '', `Select_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', `Insert_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', `Update_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result index 2f564112e40..c8f402d1515 100644 --- a/mysql-test/r/type_blob.result +++ b/mysql-test/r/type_blob.result @@ -4,17 +4,19 @@ show columns from t1; Field Type Null Key Default Extra a blob YES NULL b text YES NULL -c blob YES NULL +c tinyblob YES NULL d mediumtext YES NULL e longtext YES NULL -CREATE TABLE t2 (a char(257), b varbinary(70000), c varchar(70000000)); +CREATE TABLE t2 (a char(255), b varbinary(70000), c varchar(70000000)); Warnings: -Note 1246 Converting column 'a' from CHAR to TEXT -Note 1246 Converting column 'b' from CHAR to BLOB -Note 1246 Converting column 'c' from CHAR to TEXT +Note 1246 Converting column 'b' from VARCHAR to BLOB +Note 1246 Converting column 'c' from VARCHAR to TEXT +CREATE TABLE t4 (c varchar(65530) character set utf8 not null); +Warnings: +Note 1246 Converting column 'c' from VARCHAR to TEXT show columns from t2; Field Type Null Key Default Extra -a text YES NULL +a char(255) YES NULL b mediumblob YES NULL c longtext YES NULL create table t3 (a long, b long byte); @@ -24,10 +26,18 @@ t3 CREATE TABLE `t3` ( `a` mediumtext, `b` mediumblob ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -drop table t1,t2,t3 -#; +show create TABLE t4; +Table Create Table +t4 CREATE TABLE `t4` ( + `c` mediumtext character set utf8 NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1,t2,t3,t4; CREATE TABLE t1 (a char(257) default "hello"); ERROR 42000: Column length too big for column 'a' (max = 255); use BLOB instead +CREATE TABLE t2 (a char(256)); +ERROR 42000: Column length too big for column 'a' (max = 255); use BLOB instead +CREATE TABLE t1 (a varchar(70000) default "hello"); +ERROR 42000: Column length too big for column 'a' (max = 65535); use BLOB instead CREATE TABLE t2 (a blob default "hello"); ERROR 42000: BLOB/TEXT column 'a' can't have a default value drop table if exists t1,t2; @@ -71,16 +81,16 @@ lock tables t1 READ; show full fields from t1; Field Type Collation Null Key Default Extra Privileges Comment t text latin1_swedish_ci YES NULL select,insert,update,references -c varchar(10) latin1_swedish_ci YES NULL select,insert,update,references +c char(10) latin1_swedish_ci YES NULL select,insert,update,references b blob NULL YES NULL select,insert,update,references -d varbinary(10) NULL YES NULL select,insert,update,references +d binary(10) NULL YES NULL select,insert,update,references lock tables t1 WRITE; show full fields from t1; Field Type Collation Null Key Default Extra Privileges Comment t text latin1_swedish_ci YES NULL select,insert,update,references -c varchar(10) latin1_swedish_ci YES NULL select,insert,update,references +c char(10) latin1_swedish_ci YES NULL select,insert,update,references b blob NULL YES NULL select,insert,update,references -d varbinary(10) NULL YES NULL select,insert,update,references +d binary(10) NULL YES NULL select,insert,update,references unlock tables; select t from t1 where t like "hello"; t @@ -520,6 +530,11 @@ load_file('../../std_data/words.dat') longblob NULL YES NULL select,insert,upd drop table t1; create table t1 (id integer primary key auto_increment, txt text not null, unique index txt_index (txt (20))); insert into t1 (txt) values ('Chevy'), ('Chevy '); +ERROR 23000: Duplicate entry 'Chevy ' for key 2 +insert into t1 (txt) values ('Chevy'), ('CHEVY'); +ERROR 23000: Duplicate entry 'Chevy' for key 2 +alter table t1 drop index txt_index, add index txt_index (txt(20)); +insert into t1 (txt) values ('Chevy '); select * from t1 where txt='Chevy'; id txt 1 Chevy @@ -589,7 +604,7 @@ id txt 2 Chevy 3 Ford drop table t1; -create table t1 (id integer primary key auto_increment, txt text, unique index txt_index (txt (20))); +create table t1 (id integer primary key auto_increment, txt text, index txt_index (txt (20))); insert into t1 (txt) values ('Chevy'), ('Chevy '), (NULL); select * from t1 where txt='Chevy' or txt is NULL; id txt diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result index f08a160be3b..13727d53553 100644 --- a/mysql-test/r/type_ranges.result +++ b/mysql-test/r/type_ranges.result @@ -41,7 +41,7 @@ KEY (options,flags) show full fields from t1; Field Type Collation Null Key Default Extra Privileges Comment auto int(5) unsigned NULL PRI NULL auto_increment select,insert,update,references -string varchar(10) latin1_swedish_ci YES hello select,insert,update,references +string char(10) latin1_swedish_ci YES hello select,insert,update,references tiny tinyint(4) NULL MUL 0 select,insert,update,references short smallint(6) NULL MUL 1 select,insert,update,references medium mediumint(8) NULL MUL 0 select,insert,update,references @@ -209,7 +209,7 @@ update t2 set string="changed" where auto=16; show full columns from t1; Field Type Collation Null Key Default Extra Privileges Comment auto int(5) unsigned NULL MUL NULL auto_increment select,insert,update,references -string varchar(10) latin1_swedish_ci YES new defaul select,insert,update,references +string char(10) latin1_swedish_ci YES new defaul select,insert,update,references tiny tinyint(4) NULL MUL 0 select,insert,update,references short smallint(6) NULL MUL 0 select,insert,update,references medium mediumint(8) NULL MUL 0 select,insert,update,references @@ -223,7 +223,7 @@ umedium mediumint(8) unsigned NULL MUL 0 select,insert,update,references ulong int(11) unsigned NULL MUL 0 select,insert,update,references ulonglong bigint(13) unsigned NULL MUL 0 select,insert,update,references time_stamp timestamp NULL YES CURRENT_TIMESTAMP select,insert,update,references -date_field varchar(10) latin1_swedish_ci YES NULL select,insert,update,references +date_field char(10) latin1_swedish_ci YES NULL select,insert,update,references time_field time NULL YES NULL select,insert,update,references date_time datetime NULL YES NULL select,insert,update,references new_blob_col varchar(20) latin1_swedish_ci YES NULL select,insert,update,references @@ -231,11 +231,11 @@ tinyblob_col tinyblob NULL YES NULL select,insert,update,references mediumblob_col mediumblob NULL select,insert,update,references options enum('one','two','tree') latin1_swedish_ci MUL one select,insert,update,references flags set('one','two','tree') latin1_swedish_ci select,insert,update,references -new_field varchar(10) latin1_swedish_ci new select,insert,update,references +new_field char(10) latin1_swedish_ci new select,insert,update,references show full columns from t2; Field Type Collation Null Key Default Extra Privileges Comment auto int(5) unsigned NULL 0 select,insert,update,references -string varchar(10) latin1_swedish_ci YES new defaul select,insert,update,references +string char(10) latin1_swedish_ci YES new defaul select,insert,update,references tiny tinyint(4) NULL 0 select,insert,update,references short smallint(6) NULL 0 select,insert,update,references medium mediumint(8) NULL 0 select,insert,update,references @@ -249,7 +249,7 @@ umedium mediumint(8) unsigned NULL 0 select,insert,update,references ulong int(11) unsigned NULL 0 select,insert,update,references ulonglong bigint(13) unsigned NULL 0 select,insert,update,references time_stamp timestamp NULL YES 0000-00-00 00:00:00 select,insert,update,references -date_field varchar(10) latin1_swedish_ci YES NULL select,insert,update,references +date_field char(10) latin1_swedish_ci YES NULL select,insert,update,references time_field time NULL YES NULL select,insert,update,references date_time datetime NULL YES NULL select,insert,update,references new_blob_col varchar(20) latin1_swedish_ci YES NULL select,insert,update,references @@ -257,7 +257,7 @@ tinyblob_col tinyblob NULL YES NULL select,insert,update,references mediumblob_col mediumblob NULL select,insert,update,references options enum('one','two','tree') latin1_swedish_ci one select,insert,update,references flags set('one','two','tree') latin1_swedish_ci select,insert,update,references -new_field varchar(10) latin1_swedish_ci new select,insert,update,references +new_field char(10) latin1_swedish_ci new select,insert,update,references select t1.auto,t2.auto from t1,t2 where t1.auto=t2.auto and ((t1.string<>t2.string and (t1.string is not null or t2.string is not null)) or (t1.tiny<>t2.tiny and (t1.tiny is not null or t2.tiny is not null)) or (t1.short<>t2.short and (t1.short is not null or t2.short is not null)) or (t1.medium<>t2.medium and (t1.medium is not null or t2.medium is not null)) or (t1.long_int<>t2.long_int and (t1.long_int is not null or t2.long_int is not null)) or (t1.longlong<>t2.longlong and (t1.longlong is not null or t2.longlong is not null)) or (t1.real_float<>t2.real_float and (t1.real_float is not null or t2.real_float is not null)) or (t1.real_double<>t2.real_double and (t1.real_double is not null or t2.real_double is not null)) or (t1.utiny<>t2.utiny and (t1.utiny is not null or t2.utiny is not null)) or (t1.ushort<>t2.ushort and (t1.ushort is not null or t2.ushort is not null)) or (t1.umedium<>t2.umedium and (t1.umedium is not null or t2.umedium is not null)) or (t1.ulong<>t2.ulong and (t1.ulong is not null or t2.ulong is not null)) or (t1.ulonglong<>t2.ulonglong and (t1.ulonglong is not null or t2.ulonglong is not null)) or (t1.time_stamp<>t2.time_stamp and (t1.time_stamp is not null or t2.time_stamp is not null)) or (t1.date_field<>t2.date_field and (t1.date_field is not null or t2.date_field is not null)) or (t1.time_field<>t2.time_field and (t1.time_field is not null or t2.time_field is not null)) or (t1.date_time<>t2.date_time and (t1.date_time is not null or t2.date_time is not null)) or (t1.new_blob_col<>t2.new_blob_col and (t1.new_blob_col is not null or t2.new_blob_col is not null)) or (t1.tinyblob_col<>t2.tinyblob_col and (t1.tinyblob_col is not null or t2.tinyblob_col is not null)) or (t1.mediumblob_col<>t2.mediumblob_col and (t1.mediumblob_col is not null or t2.mediumblob_col is not null)) or (t1.options<>t2.options and (t1.options is not null or t2.options is not null)) or (t1.flags<>t2.flags and (t1.flags is not null or t2.flags is not null)) or (t1.new_field<>t2.new_field and (t1.new_field is not null or t2.new_field is not null))); auto auto 16 16 diff --git a/mysql-test/r/type_ranges.result.es b/mysql-test/r/type_ranges.result.es index 548b00750ea..c1f6d2453e9 100644 --- a/mysql-test/r/type_ranges.result.es +++ b/mysql-test/r/type_ranges.result.es @@ -223,7 +223,7 @@ umedium mediumint(8) unsigned NULL MUL 0 ulong int(11) unsigned NULL MUL 0 ulonglong bigint(13) unsigned NULL MUL 0 time_stamp timestamp NULL YES CURRENT_TIMESTAMP -date_field varchar(10) latin1_swedish_ci YES NULL +date_field char(10) latin1_swedish_ci YES NULL time_field time NULL YES NULL date_time datetime NULL YES NULL new_blob_col varchar(20) latin1_swedish_ci YES NULL @@ -231,7 +231,7 @@ tinyblob_col tinyblob NULL YES NULL mediumblob_col mediumblob NULL options enum('one','two','tree') latin1_swedish_ci MUL one flags set('one','two','tree') latin1_swedish_ci -new_field varchar(10) latin1_swedish_ci new +new_field char(10) latin1_swedish_ci new show full columns from t2; Field Type Collation Null Key Default Extra Privileges Comment auto int(5) unsigned NULL 0 @@ -249,7 +249,7 @@ umedium mediumint(8) unsigned NULL 0 ulong int(11) unsigned NULL 0 ulonglong bigint(13) unsigned NULL 0 time_stamp timestamp NULL YES 0000-00-00 00:00:00 -date_field varchar(10) latin1_swedish_ci YES NULL +date_field char(10) latin1_swedish_ci YES NULL time_field time NULL YES NULL date_time datetime NULL YES NULL new_blob_col varchar(20) latin1_swedish_ci YES NULL @@ -257,7 +257,7 @@ tinyblob_col tinyblob NULL YES NULL mediumblob_col mediumblob NULL options enum('one','two','tree') latin1_swedish_ci one flags set('one','two','tree') latin1_swedish_ci -new_field varchar(10) latin1_swedish_ci new +new_field char(10) latin1_swedish_ci new select t1.auto,t2.auto from t1,t2 where t1.auto=t2.auto and ((t1.string<>t2.string and (t1.string is not null or t2.string is not null)) or (t1.tiny<>t2.tiny and (t1.tiny is not null or t2.tiny is not null)) or (t1.short<>t2.short and (t1.short is not null or t2.short is not null)) or (t1.medium<>t2.medium and (t1.medium is not null or t2.medium is not null)) or (t1.long_int<>t2.long_int and (t1.long_int is not null or t2.long_int is not null)) or (t1.longlong<>t2.longlong and (t1.longlong is not null or t2.longlong is not null)) or (t1.real_float<>t2.real_float and (t1.real_float is not null or t2.real_float is not null)) or (t1.real_double<>t2.real_double and (t1.real_double is not null or t2.real_double is not null)) or (t1.utiny<>t2.utiny and (t1.utiny is not null or t2.utiny is not null)) or (t1.ushort<>t2.ushort and (t1.ushort is not null or t2.ushort is not null)) or (t1.umedium<>t2.umedium and (t1.umedium is not null or t2.umedium is not null)) or (t1.ulong<>t2.ulong and (t1.ulong is not null or t2.ulong is not null)) or (t1.ulonglong<>t2.ulonglong and (t1.ulonglong is not null or t2.ulonglong is not null)) or (t1.time_stamp<>t2.time_stamp and (t1.time_stamp is not null or t2.time_stamp is not null)) or (t1.date_field<>t2.date_field and (t1.date_field is not null or t2.date_field is not null)) or (t1.time_field<>t2.time_field and (t1.time_field is not null or t2.time_field is not null)) or (t1.date_time<>t2.date_time and (t1.date_time is not null or t2.date_time is not null)) or (t1.new_blob_col<>t2.new_blob_col and (t1.new_blob_col is not null or t2.new_blob_col is not null)) or (t1.tinyblob_col<>t2.tinyblob_col and (t1.tinyblob_col is not null or t2.tinyblob_col is not null)) or (t1.mediumblob_col<>t2.mediumblob_col and (t1.mediumblob_col is not null or t2.mediumblob_col is not null)) or (t1.options<>t2.options and (t1.options is not null or t2.options is not null)) or (t1.flags<>t2.flags and (t1.flags is not null or t2.flags is not null)) or (t1.new_field<>t2.new_field and (t1.new_field is not null or t2.new_field is not null))); auto auto 16 16 |