1set default_storage_engine='tokudb'; 2drop table if exists t1,t2,t3,t4,t5,t6,t7; 3CREATE TABLE t1 (a blob, b text, c blob(250), d text(70000), e text(70000000)); 4show columns from t1; 5Field Type Null Key Default Extra 6a blob YES NULL 7b text YES NULL 8c tinyblob YES NULL 9d mediumtext YES NULL 10e longtext YES NULL 11CREATE TABLE t2 (a char(255), b varbinary(70000), c varchar(70000000)); 12Warnings: 13Note 1246 Converting column 'b' from VARBINARY to BLOB 14Note 1246 Converting column 'c' from VARCHAR to TEXT 15CREATE TABLE t4 (c varchar(65530) character set utf8 not null); 16Warnings: 17Note 1246 Converting column 'c' from VARCHAR to TEXT 18show columns from t2; 19Field Type Null Key Default Extra 20a char(255) YES NULL 21b mediumblob YES NULL 22c longtext YES NULL 23create table t3 (a long, b long byte); 24show create TABLE t3; 25Table Create Table 26t3 CREATE TABLE `t3` ( 27 `a` mediumtext DEFAULT NULL, 28 `b` mediumblob DEFAULT NULL 29) ENGINE=ENGINE DEFAULT CHARSET=latin1 30show create TABLE t4; 31Table Create Table 32t4 CREATE TABLE `t4` ( 33 `c` mediumtext CHARACTER SET utf8 NOT NULL 34) ENGINE=ENGINE DEFAULT CHARSET=latin1 35drop table t1,t2,t3,t4; 36CREATE TABLE t1 (a char(257) default "hello"); 37ERROR 42000: Column length too big for column 'a' (max = 255); use BLOB or TEXT instead 38CREATE TABLE t2 (a char(256)); 39ERROR 42000: Column length too big for column 'a' (max = 255); use BLOB or TEXT instead 40CREATE TABLE t1 (a varchar(70000) default "hello"); 41Warnings: 42Note 1246 Converting column 'a' from VARCHAR to TEXT 43CREATE TABLE t2 (a blob default "hello"); 44drop table if exists t1,t2; 45create table t1 (nr int(5) not null auto_increment,b blob,str char(10), primary key (nr)); 46insert into t1 values (null,"a","A"); 47insert into t1 values (null,"bbb","BBB"); 48insert into t1 values (null,"ccc","CCC"); 49select last_insert_id(); 50last_insert_id() 513 52select * from t1,t1 as t2; 53nr b str nr b str 541 a A 1 a A 552 bbb BBB 1 a A 563 ccc CCC 1 a A 571 a A 2 bbb BBB 582 bbb BBB 2 bbb BBB 593 ccc CCC 2 bbb BBB 601 a A 3 ccc CCC 612 bbb BBB 3 ccc CCC 623 ccc CCC 3 ccc CCC 63drop table t1; 64create table t1 (a text); 65insert into t1 values ('where'); 66update t1 set a='Where'; 67select * from t1; 68a 69Where 70drop table t1; 71create table t1 (t text,c char(10),b blob, d varbinary(10)); 72insert into t1 values (NULL,NULL,NULL,NULL); 73insert into t1 values ("","","",""); 74insert into t1 values ("hello","hello","hello","hello"); 75insert into t1 values ("HELLO","HELLO","HELLO","HELLO"); 76insert into t1 values ("HELLO MY","HELLO MY","HELLO MY","HELLO MY"); 77insert into t1 values ("a","a","a","a"); 78insert into t1 values (1,1,1,1); 79insert into t1 values (NULL,NULL,NULL,NULL); 80update t1 set c="",b=null where c="1"; 81lock tables t1 READ; 82show full fields from t1; 83Field Type Collation Null Key Default Extra Privileges Comment 84t text latin1_swedish_ci YES NULL # 85c char(10) latin1_swedish_ci YES NULL # 86b blob NULL YES NULL # 87d varbinary(10) NULL YES NULL # 88lock tables t1 WRITE; 89show full fields from t1; 90Field Type Collation Null Key Default Extra Privileges Comment 91t text latin1_swedish_ci YES NULL # 92c char(10) latin1_swedish_ci YES NULL # 93b blob NULL YES NULL # 94d varbinary(10) NULL YES NULL # 95unlock tables; 96select t from t1 where t like "hello"; 97t 98hello 99HELLO 100select c from t1 where c like "hello"; 101c 102hello 103HELLO 104select b from t1 where b like "hello"; 105b 106hello 107select d from t1 where d like "hello"; 108d 109hello 110select c from t1 having c like "hello"; 111c 112hello 113HELLO 114select d from t1 having d like "hello"; 115d 116hello 117select t from t1 where t like "%HELLO%"; 118t 119hello 120HELLO 121HELLO MY 122select c from t1 where c like "%HELLO%"; 123c 124hello 125HELLO 126HELLO MY 127select b from t1 where b like "%HELLO%"; 128b 129HELLO 130HELLO MY 131select d from t1 where d like "%HELLO%"; 132d 133HELLO 134HELLO MY 135select c from t1 having c like "%HELLO%"; 136c 137hello 138HELLO 139HELLO MY 140select d from t1 having d like "%HELLO%"; 141d 142HELLO 143HELLO MY 144select d from t1 having d like "%HE%LLO%"; 145d 146HELLO 147HELLO MY 148select t from t1 order by t; 149t 150NULL 151NULL 152 1531 154a 155hello 156HELLO 157HELLO MY 158select c from t1 order by c; 159c 160NULL 161NULL 162 163 164a 165hello 166HELLO 167HELLO MY 168select b from t1 order by b; 169b 170NULL 171NULL 172NULL 173 174HELLO 175HELLO MY 176a 177hello 178select d from t1 order by d; 179d 180NULL 181NULL 182 1831 184HELLO 185HELLO MY 186a 187hello 188select distinct t from t1; 189t 190NULL 191 192hello 193HELLO MY 194a 1951 196select distinct b from t1; 197b 198NULL 199 200hello 201HELLO 202HELLO MY 203a 204select distinct t from t1 order by t; 205t 206NULL 207 2081 209a 210hello 211HELLO MY 212select distinct b from t1 order by b; 213b 214NULL 215 216HELLO 217HELLO MY 218a 219hello 220select t from t1 group by t; 221t 222NULL 223 2241 225a 226hello 227HELLO MY 228select b from t1 group by b; 229b 230NULL 231 232HELLO 233HELLO MY 234a 235hello 236set big_tables=1; 237select distinct t from t1; 238t 239NULL 240 241hello 242HELLO MY 243a 2441 245select distinct b from t1; 246b 247NULL 248 249hello 250HELLO 251HELLO MY 252a 253select distinct t from t1 order by t; 254t 255NULL 256 2571 258a 259hello 260HELLO MY 261select distinct b from t1 order by b; 262b 263NULL 264 265HELLO 266HELLO MY 267a 268hello 269select distinct c from t1; 270c 271NULL 272 273hello 274HELLO MY 275a 276select distinct d from t1; 277d 278NULL 279 280hello 281HELLO 282HELLO MY 283a 2841 285select distinct c from t1 order by c; 286c 287NULL 288 289a 290hello 291HELLO MY 292select distinct d from t1 order by d; 293d 294NULL 295 2961 297HELLO 298HELLO MY 299a 300hello 301select c from t1 group by c; 302c 303NULL 304 305a 306hello 307HELLO MY 308select d from t1 group by d; 309d 310NULL 311 3121 313HELLO 314HELLO MY 315a 316hello 317set big_tables=0; 318select distinct * from t1; 319t c b d 320NULL NULL NULL NULL 321 322hello hello hello hello 323HELLO HELLO HELLO HELLO 324HELLO MY HELLO MY HELLO MY HELLO MY 325a a a a 3261 NULL 1 327select t,count(*) from t1 group by t; 328t count(*) 329NULL 2 330 1 3311 1 332a 1 333hello 2 334HELLO MY 1 335select b,count(*) from t1 group by b; 336b count(*) 337NULL 3 338 1 339HELLO 1 340HELLO MY 1 341a 1 342hello 1 343select c,count(*) from t1 group by c; 344c count(*) 345NULL 2 346 2 347a 1 348hello 2 349HELLO MY 1 350select d,count(*) from t1 group by d; 351d count(*) 352NULL 2 353 1 3541 1 355HELLO 1 356HELLO MY 1 357a 1 358hello 1 359drop table t1; 360create table t1 (a text, unique (a(21000))); 361ERROR 42000: Specified key was too long; max key length is 3072 bytes 362create table t1 (a text, key (a(2100))); 363show create table t1; 364Table Create Table 365t1 CREATE TABLE `t1` ( 366 `a` text DEFAULT NULL, 367 KEY `a` (`a`(2100)) 368) ENGINE=ENGINE DEFAULT CHARSET=latin1 369drop table t1; 370CREATE TABLE t1 ( 371t1_id bigint(21) NOT NULL auto_increment, 372_field_72 varchar(128) DEFAULT '' NOT NULL, 373_field_95 varchar(32), 374_field_115 tinyint(4) DEFAULT '0' NOT NULL, 375_field_122 tinyint(4) DEFAULT '0' NOT NULL, 376_field_126 tinyint(4), 377_field_134 tinyint(4), 378PRIMARY KEY (t1_id), 379UNIQUE _field_72 (_field_72), 380KEY _field_115 (_field_115), 381KEY _field_122 (_field_122) 382); 383INSERT INTO t1 VALUES (1,'admin','21232f297a57a5a743894a0e4a801fc3',0,1,NULL,NULL); 384INSERT INTO t1 VALUES (2,'hroberts','7415275a8c95952901e42b13a6b78566',0,1,NULL,NULL); 385INSERT INTO t1 VALUES (3,'guest','d41d8cd98f00b204e9800998ecf8427e',1,0,NULL,NULL); 386CREATE TABLE t2 ( 387seq_0_id bigint(21) DEFAULT '0' NOT NULL, 388seq_1_id bigint(21) DEFAULT '0' NOT NULL, 389PRIMARY KEY (seq_0_id,seq_1_id) 390); 391INSERT INTO t2 VALUES (1,1); 392INSERT INTO t2 VALUES (2,1); 393INSERT INTO t2 VALUES (2,2); 394CREATE TABLE t3 ( 395t3_id bigint(21) NOT NULL auto_increment, 396_field_131 varchar(128), 397_field_133 tinyint(4) DEFAULT '0' NOT NULL, 398_field_135 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, 399_field_137 tinyint(4), 400_field_139 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, 401_field_140 blob, 402_field_142 tinyint(4) DEFAULT '0' NOT NULL, 403_field_145 tinyint(4) DEFAULT '0' NOT NULL, 404_field_148 tinyint(4) DEFAULT '0' NOT NULL, 405PRIMARY KEY (t3_id), 406KEY _field_133 (_field_133), 407KEY _field_135 (_field_135), 408KEY _field_139 (_field_139), 409KEY _field_142 (_field_142), 410KEY _field_145 (_field_145), 411KEY _field_148 (_field_148) 412); 413INSERT INTO t3 VALUES (1,'test job 1',0,'0000-00-00 00:00:00',0,'1999-02-25 22:43:32','test\r\njob\r\n1',0,0,0); 414INSERT INTO t3 VALUES (2,'test job 2',0,'0000-00-00 00:00:00',0,'1999-02-26 21:08:04','',0,0,0); 415CREATE TABLE t4 ( 416seq_0_id bigint(21) DEFAULT '0' NOT NULL, 417seq_1_id bigint(21) DEFAULT '0' NOT NULL, 418PRIMARY KEY (seq_0_id,seq_1_id) 419); 420INSERT INTO t4 VALUES (1,1); 421INSERT INTO t4 VALUES (2,1); 422CREATE TABLE t5 ( 423t5_id bigint(21) NOT NULL auto_increment, 424_field_149 tinyint(4), 425_field_156 varchar(128) DEFAULT '' NOT NULL, 426_field_157 varchar(128) DEFAULT '' NOT NULL, 427_field_158 varchar(128) DEFAULT '' NOT NULL, 428_field_159 varchar(128) DEFAULT '' NOT NULL, 429_field_160 varchar(128) DEFAULT '' NOT NULL, 430_field_161 varchar(128) DEFAULT '' NOT NULL, 431PRIMARY KEY (t5_id), 432KEY _field_156 (_field_156), 433KEY _field_157 (_field_157), 434KEY _field_158 (_field_158), 435KEY _field_159 (_field_159), 436KEY _field_160 (_field_160), 437KEY _field_161 (_field_161) 438); 439INSERT INTO t5 VALUES (1,0,'tomato','','','','',''); 440INSERT INTO t5 VALUES (2,0,'cilantro','','','','',''); 441CREATE TABLE t6 ( 442seq_0_id bigint(21) DEFAULT '0' NOT NULL, 443seq_1_id bigint(21) DEFAULT '0' NOT NULL, 444PRIMARY KEY (seq_0_id,seq_1_id) 445); 446INSERT INTO t6 VALUES (1,1); 447INSERT INTO t6 VALUES (1,2); 448INSERT INTO t6 VALUES (2,2); 449CREATE TABLE t7 ( 450t7_id bigint(21) NOT NULL auto_increment, 451_field_143 tinyint(4), 452_field_165 varchar(32), 453_field_166 smallint(6) DEFAULT '0' NOT NULL, 454PRIMARY KEY (t7_id), 455KEY _field_166 (_field_166) 456); 457INSERT INTO t7 VALUES (1,0,'High',1); 458INSERT INTO t7 VALUES (2,0,'Medium',2); 459INSERT INTO t7 VALUES (3,0,'Low',3); 460select replace(t3._field_140, "\r","^M"),t3_id,min(t3._field_131), min(t3._field_135), min(t3._field_139), min(t3._field_137), min(link_alias_142._field_165), min(link_alias_133._field_72), min(t3._field_145), min(link_alias_148._field_156), replace(min(t3._field_140), "\r","^M"),t3.t3_id from t3 left join t4 on t4.seq_0_id = t3.t3_id left join t7 link_alias_142 on t4.seq_1_id = link_alias_142.t7_id left join t6 on t6.seq_0_id = t3.t3_id left join t1 link_alias_133 on t6.seq_1_id = link_alias_133.t1_id left join t2 on t2.seq_0_id = t3.t3_id left join t5 link_alias_148 on t2.seq_1_id = link_alias_148.t5_id where t3.t3_id in (1) group by t3.t3_id order by link_alias_142._field_166, _field_139, link_alias_133._field_72, _field_135, link_alias_148._field_156; 461replace(t3._field_140, "\r","^M") t3_id min(t3._field_131) min(t3._field_135) min(t3._field_139) min(t3._field_137) min(link_alias_142._field_165) min(link_alias_133._field_72) min(t3._field_145) min(link_alias_148._field_156) replace(min(t3._field_140), "\r","^M") t3_id 462test^M 463job^M 4641 1 test job 1 0000-00-00 00:00:00 1999-02-25 22:43:32 0 High admin 0 tomato test^M 465job^M 4661 1 467drop table t1,t2,t3,t4,t5,t6,t7; 468create table t1 (a blob); 469insert into t1 values ("empty"),(""); 470select a,reverse(a) from t1; 471a reverse(a) 472empty ytpme 473 474drop table t1; 475create table t1 (a blob, key (a(10))); 476insert into t1 values ("bye"),("hello"),("hello"),("hello word"); 477select * from t1 where a like "hello%"; 478a 479hello 480hello 481hello word 482drop table t1; 483CREATE TABLE t1 ( 484f1 int(11) DEFAULT '0' NOT NULL, 485f2 varchar(16) DEFAULT '' NOT NULL, 486f5 text, 487KEY index_name (f1,f2,f5(16)) 488); 489INSERT INTO t1 VALUES (0,'traktor','1111111111111'); 490INSERT INTO t1 VALUES (1,'traktor','1111111111111111111111111'); 491select count(*) from t1 where f2='traktor'; 492count(*) 4932 494drop table t1; 495create table t1 (foobar tinyblob not null, boggle smallint not null, key (foobar(32), boggle)); 496insert into t1 values ('fish', 10),('bear', 20); 497select foobar, boggle from t1 where foobar = 'fish'; 498foobar boggle 499fish 10 500select foobar, boggle from t1 where foobar = 'fish' and boggle = 10; 501foobar boggle 502fish 10 503drop table t1; 504create table t1 (id integer auto_increment unique,imagem LONGBLOB not null default ''); 505insert into t1 (id) values (1); 506select 507charset(load_file('../../std_data/words.dat')), 508collation(load_file('../../std_data/words.dat')), 509coercibility(load_file('../../std_data/words.dat')); 510charset(load_file('../../std_data/words.dat')) collation(load_file('../../std_data/words.dat')) coercibility(load_file('../../std_data/words.dat')) 511binary binary 4 512explain extended select 513charset(load_file('MYSQLTEST_VARDIR/std_data/words.dat')), 514collation(load_file('MYSQLTEST_VARDIR/std_data/words.dat')), 515coercibility(load_file('MYSQLTEST_VARDIR/std_data/words.dat')); 516id select_type table type possible_keys key key_len ref rows filtered Extra 5171 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 518Warnings: 519Note 1003 select charset(load_file('MYSQLTEST_VARDIR/std_data/words.dat')) AS `charset(load_file('MYSQLTEST_VARDIR/std_data/words.dat'))`,collation(load_file('MYSQLTEST_VARDIR/std_data/words.dat')) AS `collation(load_file('MYSQLTEST_VARDIR/std_data/words.dat'))`,coercibility(load_file('MYSQLTEST_VARDIR/std_data/words.dat')) AS `coercibility(load_file('MYSQLTEST_VARDIR/std_data/words.dat'))` 520update t1 set imagem=load_file('MYSQLTEST_VARDIR/std_data/words.dat') where id=1; 521select if(imagem is null, "ERROR", "OK"),length(imagem) from t1 where id = 1; 522if(imagem is null, "ERROR", "OK") length(imagem) 523OK 581 524drop table t1; 525create table t1 select load_file('MYSQLTEST_VARDIR/std_data/words.dat') l; 526show full fields from t1; 527Field Type Collation Null Key Default Extra Privileges Comment 528l longblob NULL YES NULL # 529drop table t1; 530create table t1 (id integer primary key auto_increment, txt text not null, unique index txt_index (txt (20))); 531insert into t1 (txt) values ('Chevy'), ('Chevy '); 532ERROR 23000: Duplicate entry 'Chevy' for key 'txt_index' 533insert into t1 (txt) values ('Chevy'), ('CHEVY'); 534ERROR 23000: Duplicate entry 'Chevy' for key 'txt_index' 535alter table t1 drop index txt_index, add index txt_index (txt(20)); 536insert into t1 (txt) values ('Chevy '); 537select * from t1 where txt='Chevy'; 538id txt 5395 Chevy 540select * from t1 where txt='Chevy '; 541id txt 5425 Chevy 543select * from t1 where txt='Chevy ' or txt='Chevy'; 544id txt 5455 Chevy 546select * from t1 where txt='Chevy' or txt='Chevy '; 547id txt 5485 Chevy 549select * from t1 where id='1' or id='2'; 550id txt 551insert into t1 (txt) values('Ford'); 552select * from t1 where txt='Chevy' or txt='Chevy ' or txt='Ford'; 553id txt 5545 Chevy 5556 Ford 556select * from t1 where txt='Chevy' or txt='Chevy '; 557id txt 5585 Chevy 559select * from t1 where txt='Chevy' or txt='Chevy ' or txt=' Chevy'; 560id txt 5615 Chevy 562select * from t1 where txt in ('Chevy ','Chevy'); 563id txt 5645 Chevy 565select * from t1 where txt in ('Chevy'); 566id txt 5675 Chevy 568select * from t1 where txt between 'Chevy' and 'Chevy'; 569id txt 5705 Chevy 571select * from t1 where txt between 'Chevy' and 'Chevy' or txt='Chevy '; 572id txt 5735 Chevy 574select * from t1 where txt between 'Chevy' and 'Chevy '; 575id txt 5765 Chevy 577select * from t1 where txt < 'Chevy '; 578id txt 579select * from t1 where txt <= 'Chevy'; 580id txt 5815 Chevy 582select * from t1 where txt > 'Chevy'; 583id txt 5846 Ford 585select * from t1 where txt >= 'Chevy'; 586id txt 5875 Chevy 5886 Ford 589drop table t1; 590create table t1 (id integer primary key auto_increment, txt text, index txt_index (txt (20))); 591insert into t1 (txt) values ('Chevy'), ('Chevy '), (NULL); 592select * from t1 where txt='Chevy' or txt is NULL; 593id txt 5941 Chevy 5952 Chevy 5963 NULL 597explain select * from t1 where txt='Chevy' or txt is NULL; 598id select_type table type possible_keys key key_len ref rows Extra 5991 SIMPLE t1 ALL txt_index NULL NULL NULL 3 Using where 600select * from t1 where txt='Chevy '; 601id txt 6021 Chevy 6032 Chevy 604select * from t1 where txt='Chevy ' or txt='Chevy'; 605id txt 6061 Chevy 6072 Chevy 608select * from t1 where txt='Chevy' or txt='Chevy '; 609id txt 6101 Chevy 6112 Chevy 612select * from t1 where id='1' or id='2'; 613id txt 6141 Chevy 6152 Chevy 616insert into t1 (txt) values('Ford'); 617select * from t1 where txt='Chevy' or txt='Chevy ' or txt='Ford'; 618id txt 6191 Chevy 6202 Chevy 6214 Ford 622select * from t1 where txt='Chevy' or txt='Chevy '; 623id txt 6241 Chevy 6252 Chevy 626select * from t1 where txt='Chevy' or txt='Chevy ' or txt=' Chevy'; 627id txt 6281 Chevy 6292 Chevy 630select * from t1 where txt in ('Chevy ','Chevy'); 631id txt 6321 Chevy 6332 Chevy 634select * from t1 where txt in ('Chevy'); 635id txt 6361 Chevy 6372 Chevy 638select * from t1 where txt between 'Chevy' and 'Chevy'; 639id txt 6401 Chevy 6412 Chevy 642select * from t1 where txt between 'Chevy' and 'Chevy' or txt='Chevy '; 643id txt 6441 Chevy 6452 Chevy 646select * from t1 where txt between 'Chevy' and 'Chevy '; 647id txt 6481 Chevy 6492 Chevy 650select * from t1 where txt < 'Chevy '; 651id txt 652select * from t1 where txt < 'Chevy ' or txt is NULL; 653id txt 6543 NULL 655select * from t1 where txt <= 'Chevy'; 656id txt 6571 Chevy 6582 Chevy 659select * from t1 where txt > 'Chevy'; 660id txt 6614 Ford 662select * from t1 where txt >= 'Chevy'; 663id txt 6641 Chevy 6652 Chevy 6664 Ford 667alter table t1 modify column txt blob; 668explain select * from t1 where txt='Chevy' or txt is NULL; 669id select_type table type possible_keys key key_len ref rows Extra 6701 SIMPLE t1 ALL txt_index NULL NULL NULL 4 Using where 671select * from t1 where txt='Chevy' or txt is NULL; 672id txt 6731 Chevy 6743 NULL 675explain select * from t1 where txt='Chevy' or txt is NULL order by txt; 676id select_type table type possible_keys key key_len ref rows Extra 6771 SIMPLE t1 ALL txt_index NULL NULL NULL 4 Using where; Using filesort 678select * from t1 where txt='Chevy' or txt is NULL order by txt; 679id txt 6803 NULL 6811 Chevy 682drop table t1; 683CREATE TABLE t1 ( i int(11) NOT NULL default '0', c text NOT NULL, d varchar(1) NOT NULL DEFAULT ' ', PRIMARY KEY (i), KEY (c(1),d)); 684INSERT t1 (i, c) VALUES (1,''),(2,''),(3,'asdfh'),(4,''); 685select max(i) from t1 where c = ''; 686max(i) 6874 688drop table t1; 689create table t1 (a int, b int, c tinyblob, d int, e int); 690alter table t1 add primary key (a,b,c(255),d); 691alter table t1 add key (a,b,d,e); 692show create table t1; 693Table Create Table 694t1 CREATE TABLE `t1` ( 695 `a` int(11) NOT NULL, 696 `b` int(11) NOT NULL, 697 `c` tinyblob NOT NULL, 698 `d` int(11) NOT NULL, 699 `e` int(11) DEFAULT NULL, 700 PRIMARY KEY (`a`,`b`,`c`(255),`d`), 701 KEY `a` (`a`,`b`,`d`,`e`) 702) ENGINE=ENGINE DEFAULT CHARSET=latin1 703drop table t1; 704CREATE table t1 (a blob); 705insert into t1 values ('b'),('a\0'),('a'),('a '),('aa'),(NULL); 706select hex(a) from t1 order by a; 707hex(a) 708NULL 70961 7106100 7116120 7126161 71362 714select hex(concat(a,'\0')) as b from t1 order by concat(a,'\0'); 715b 716NULL 7176100 718610000 719612000 720616100 7216200 722alter table t1 modify a varbinary(5); 723select hex(a) from t1 order by a; 724hex(a) 725NULL 72661 7276100 7286120 7296161 73062 731select hex(concat(a,'\0')) as b from t1 order by concat(a,'\0'); 732b 733NULL 7346100 735610000 736612000 737616100 7386200 739alter table t1 modify a char(5); 740select hex(a) from t1 order by a; 741hex(a) 742NULL 7436100 74461 74561 7466161 74762 748select hex(concat(a,'\0')) as b from t1 order by concat(a,'\0'); 749b 750NULL 751610000 7526100 7536100 754616100 7556200 756alter table t1 modify a binary(5); 757select hex(a) from t1 order by a; 758hex(a) 759NULL 7606100000000 7616100000000 7626100000000 7636161000000 7646200000000 765select hex(concat(a,'\0')) as b from t1 order by concat(a,'\0'); 766b 767NULL 768610000000000 769610000000000 770610000000000 771616100000000 772620000000000 773drop table t1; 774create table t1 (a text default ''); 775show create table t1; 776Table Create Table 777t1 CREATE TABLE `t1` ( 778 `a` text DEFAULT '' 779) ENGINE=ENGINE DEFAULT CHARSET=latin1 780insert into t1 values (default); 781select * from t1; 782a 783 784drop table t1; 785set @@sql_mode='TRADITIONAL'; 786create table t1 (a text default ''); 787drop table t1; 788set @@sql_mode=''; 789CREATE TABLE t (c TEXT CHARSET ASCII); 790INSERT INTO t (c) VALUES (REPEAT('1',65537)); 791Warnings: 792Warning 1265 Data truncated for column 'c' at row 1 793INSERT INTO t (c) VALUES (REPEAT('2',65536)); 794Warnings: 795Warning 1265 Data truncated for column 'c' at row 1 796INSERT INTO t (c) VALUES (REPEAT('3',65535)); 797SELECT LENGTH(c), CHAR_LENGTH(c) FROM t; 798LENGTH(c) CHAR_LENGTH(c) 79965535 65535 80065535 65535 80165535 65535 802DROP TABLE t; 803drop table if exists b15776; 804create table b15776 (data blob(2147483647)); 805drop table b15776; 806create table b15776 (data blob(-1)); 807ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-1))' at line 1 808create table b15776 (data blob(2147483648)); 809drop table b15776; 810create table b15776 (data blob(4294967294)); 811drop table b15776; 812create table b15776 (data blob(4294967295)); 813drop table b15776; 814create table b15776 (data blob(4294967296)); 815ERROR 42000: Display width out of range for 'data' (max = 4294967295) 816CREATE TABLE b15776 (a blob(2147483647), b blob(2147483648), c blob(4294967295), a1 text(2147483647), b1 text(2147483648), c1 text(4294967295) ); 817show columns from b15776; 818Field Type Null Key Default Extra 819a longblob YES NULL 820b longblob YES NULL 821c longblob YES NULL 822a1 longtext YES NULL 823b1 longtext YES NULL 824c1 longtext YES NULL 825drop table b15776; 826CREATE TABLE b15776 (a blob(4294967296)); 827ERROR 42000: Display width out of range for 'a' (max = 4294967295) 828CREATE TABLE b15776 (a text(4294967296)); 829ERROR 42000: Display width out of range for 'a' (max = 4294967295) 830CREATE TABLE b15776 (a blob(999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999)); 831ERROR 42000: Display width out of range for 'a' (max = 4294967295) 832CREATE TABLE b15776 (a text(999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999)); 833ERROR 42000: Display width out of range for 'a' (max = 4294967295) 834CREATE TABLE b15776 (a int(0)); 835INSERT INTO b15776 values (NULL), (1), (42), (654); 836SELECT * from b15776 ORDER BY a; 837a 838NULL 8391 84042 841654 842DROP TABLE b15776; 843CREATE TABLE b15776 (a int(-1)); 844ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-1))' at line 1 845CREATE TABLE b15776 (a int(255)); 846DROP TABLE b15776; 847CREATE TABLE b15776 (a int(256)); 848ERROR 42000: Display width out of range for 'a' (max = 255) 849CREATE TABLE b15776 (data blob(-1)); 850ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-1))' at line 1 851CREATE TABLE b15776 (a char(2147483647)); 852ERROR 42000: Column length too big for column 'a' (max = 255); use BLOB or TEXT instead 853CREATE TABLE b15776 (a char(2147483648)); 854ERROR 42000: Column length too big for column 'a' (max = 255); use BLOB or TEXT instead 855CREATE TABLE b15776 (a char(4294967295)); 856ERROR 42000: Column length too big for column 'a' (max = 255); use BLOB or TEXT instead 857CREATE TABLE b15776 (a char(4294967296)); 858ERROR 42000: Column length too big for column 'a' (max = 255); use BLOB or TEXT instead 859CREATE TABLE b15776 (a year(4294967295)); 860Warnings: 861Note 1287 'YEAR(4294967295)' is deprecated and will be removed in a future release. Please use YEAR(4) instead 862INSERT INTO b15776 VALUES (42); 863SELECT * FROM b15776; 864a 8652042 866DROP TABLE b15776; 867CREATE TABLE b15776 (a year(0)); 868Warnings: 869Note 1287 'YEAR(0)' is deprecated and will be removed in a future release. Please use YEAR(4) instead 870DROP TABLE b15776; 871CREATE TABLE b15776 (a year(-2)); 872ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-2))' at line 1 873CREATE TABLE b15776 (a int(999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999)); 874ERROR 42000: Display width out of range for 'a' (max = 255) 875CREATE TABLE b15776 (a char(999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999)); 876ERROR 42000: Column length too big for column 'a' (max = 255); use BLOB or TEXT instead 877CREATE TABLE b15776 select cast(null as char(4294967295)); 878show columns from b15776; 879Field Type Null Key Default Extra 880cast(null as char(4294967295)) char(0) YES NULL 881drop table b15776; 882CREATE TABLE b15776 select cast(null as nchar(4294967295)); 883show columns from b15776; 884Field Type Null Key Default Extra 885cast(null as nchar(4294967295)) char(0) YES NULL 886drop table b15776; 887CREATE TABLE b15776 select cast(null as binary(4294967295)); 888show columns from b15776; 889Field Type Null Key Default Extra 890cast(null as binary(4294967295)) binary(0) YES NULL 891drop table b15776; 892explain select cast(1 as char(4294967295)); 893id select_type table type possible_keys key key_len ref rows Extra 8941 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used 895explain select cast(1 as nchar(4294967295)); 896id select_type table type possible_keys key key_len ref rows Extra 8971 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used 898explain select cast(1 as binary(4294967295)); 899id select_type table type possible_keys key key_len ref rows Extra 9001 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used 901explain select cast(1 as char(4294967296)); 902ERROR 42000: Display width out of range for '1' (max = 4294967295) 903explain select cast(1 as nchar(4294967296)); 904ERROR 42000: Display width out of range for '1' (max = 4294967295) 905explain select cast(1 as binary(4294967296)); 906ERROR 42000: Display width out of range for '1' (max = 4294967295) 907explain select cast(1 as decimal(-1)); 908ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-1))' at line 1 909explain select cast(1 as decimal(64, 30)); 910id select_type table type possible_keys key key_len ref rows Extra 9111 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used 912explain select cast(1 as decimal(64, 999999999999999999999999999999)); 913Got one of the listed errors 914explain select cast(1 as decimal(4294967296)); 915Got one of the listed errors 916explain select cast(1 as decimal(999999999999999999999999999999999999)); 917Got one of the listed errors 918explain select convert(1, char(4294967295)); 919id select_type table type possible_keys key key_len ref rows Extra 9201 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used 921explain select convert(1, char(4294967296)); 922ERROR 42000: Display width out of range for '1' (max = 4294967295) 923explain select convert(1, char(999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999)); 924ERROR 42000: Display width out of range for '1' (max = 4294967295) 925explain select convert(1, nchar(4294967295)); 926id select_type table type possible_keys key key_len ref rows Extra 9271 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used 928explain select convert(1, nchar(4294967296)); 929ERROR 42000: Display width out of range for '1' (max = 4294967295) 930explain select convert(1, nchar(999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999)); 931ERROR 42000: Display width out of range for '1' (max = 4294967295) 932explain select convert(1, binary(4294967295)); 933id select_type table type possible_keys key key_len ref rows Extra 9341 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used 935explain select convert(1, binary(4294967296)); 936ERROR 42000: Display width out of range for '1' (max = 4294967295) 937explain select convert(1, binary(999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999)); 938ERROR 42000: Display width out of range for '1' (max = 4294967295) 939End of 5.0 tests 940CREATE TABLE t1(id INT NOT NULL); 941CREATE TABLE t2(id INT NOT NULL, c TEXT NOT NULL); 942INSERT INTO t1 VALUES (1); 943INSERT INTO t2 VALUES (1, ''); 944UPDATE t2 SET c = REPEAT('1', 70000); 945Warnings: 946Warning 1265 Data truncated for column 'c' at row 1 947SELECT LENGTH(c) FROM t2; 948LENGTH(c) 94965535 950UPDATE t1 LEFT JOIN t2 USING(id) SET t2.c = REPEAT('1', 70000) WHERE t1.id = 1; 951Warnings: 952Warning 1265 Data truncated for column 'c' at row 1 953SELECT LENGTH(c) FROM t2; 954LENGTH(c) 95565535 956DROP TABLE t1, t2; 957# Bug #52160: crash and inconsistent results when grouping 958# by a function and column 959CREATE FUNCTION f1() RETURNS TINYBLOB RETURN 1; 960CREATE TABLE t1(a CHAR(1)); 961INSERT INTO t1 VALUES ('0'), ('0'); 962SELECT COUNT(*) FROM t1 GROUP BY f1(), a; 963COUNT(*) 9642 965DROP FUNCTION f1; 966DROP TABLE t1; 967End of 5.1 tests 968