1drop table if exists t1,t2,t3; 2create 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; 3insert into t1 values(1,1),(2,2),(3,3),(4,4); 4delete from t1 where a=1 or a=0; 5show keys from t1; 6Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 7t1 0 PRIMARY 1 a NULL 3 NULL NULL HASH 8select * from t1; 9a b 102 2 113 3 124 4 13select * from t1 where a=4; 14a b 154 4 16update t1 set b=5 where a=4; 17update t1 set b=b+1 where a>=3; 18replace t1 values (3,3); 19select * from t1; 20a b 212 2 223 3 234 6 24alter table t1 add c int not null, add key (c,a); 25drop table t1; 26create table t1 (a int not null,b int not null, primary key (a)) engine=memory comment="testing heaps"; 27insert into t1 values(1,1),(2,2),(3,3),(4,4); 28delete from t1 where a > 0; 29select * from t1; 30a b 31drop table t1; 32create table t1 (a int not null,b int not null, primary key (a)) engine=heap comment="testing heaps"; 33insert into t1 values(1,1),(2,2),(3,3),(4,4); 34alter table t1 modify a int not null auto_increment, engine=myisam, comment="new myisam table"; 35select * from t1; 36a b 371 1 382 2 393 3 404 4 41drop table t1; 42create table t1 (a int not null) engine=heap; 43insert into t1 values (869751),(736494),(226312),(802616),(728912); 44select * from t1 where a > 736494; 45a 46869751 47802616 48alter table t1 add unique uniq_id(a); 49select * from t1 where a > 736494; 50a 51869751 52802616 53select * from t1 where a = 736494; 54a 55736494 56select * from t1 where a=869751 or a=736494; 57a 58736494 59869751 60select * from t1 where a in (869751,736494,226312,802616); 61a 62226312 63736494 64802616 65869751 66alter table t1 engine=myisam; 67explain select * from t1 where a in (869751,736494,226312,802616); 68id select_type table type possible_keys key key_len ref rows Extra 691 SIMPLE t1 range uniq_id uniq_id 4 NULL 4 Using where; Using index 70drop table t1; 71create table t1 (x int not null, y int not null, key x (x), unique y (y)) 72engine=heap; 73insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6); 74select * from t1 where x=1; 75x y 761 3 771 1 78select * from t1,t1 as t2 where t1.x=t2.y; 79x y x y 801 1 1 1 812 2 2 2 821 3 1 1 832 4 2 2 842 5 2 2 852 6 2 2 86explain select * from t1,t1 as t2 where t1.x=t2.y; 87id select_type table type possible_keys key key_len ref rows Extra 881 SIMPLE t1 ALL x NULL NULL NULL 6 NULL 891 SIMPLE t2 eq_ref y y 4 test.t1.x 1 NULL 90drop table t1; 91create table t1 (a int) engine=heap; 92insert into t1 values(1); 93select max(a) from t1; 94max(a) 951 96drop table t1; 97CREATE TABLE t1 ( a int not null default 0, b int not null default 0, key(a), key(b) ) ENGINE=HEAP; 98insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6); 99select * from t1 where a=1; 100a b 1011 6 1021 5 1031 4 1041 3 1051 2 1061 1 107insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6); 108select * from t1 where a=1; 109a b 1101 6 1111 5 1121 4 1131 3 1141 2 1151 1 1161 6 1171 5 1181 4 1191 3 1201 2 1211 1 122drop table t1; 123create table t1 (id int unsigned not null, primary key (id)) engine=HEAP; 124insert into t1 values(1); 125select max(id) from t1; 126max(id) 1271 128insert into t1 values(2); 129select max(id) from t1; 130max(id) 1312 132replace into t1 values(1); 133drop table t1; 134create table t1 (n int) engine=heap; 135drop table t1; 136create table t1 (n int) engine=heap; 137drop table if exists t1; 138CREATE table t1(f1 int not null,f2 char(20) not 139null,index(f2)) engine=heap; 140INSERT into t1 set f1=12,f2="bill"; 141INSERT into t1 set f1=13,f2="bill"; 142INSERT into t1 set f1=14,f2="bill"; 143INSERT into t1 set f1=15,f2="bill"; 144INSERT into t1 set f1=16,f2="ted"; 145INSERT into t1 set f1=12,f2="ted"; 146INSERT into t1 set f1=12,f2="ted"; 147INSERT into t1 set f1=12,f2="ted"; 148INSERT into t1 set f1=12,f2="ted"; 149delete from t1 where f2="bill"; 150select * from t1; 151f1 f2 15216 ted 15312 ted 15412 ted 15512 ted 15612 ted 157drop table t1; 158create table t1 (btn char(10) not null, key(btn)) engine=heap; 159insert into t1 values ("hello"),("hello"),("hello"),("hello"),("hello"),("a"),("b"),("c"),("d"),("e"),("f"),("g"),("h"),("i"); 160explain select * from t1 where btn like "q%"; 161id select_type table type possible_keys key key_len ref rows Extra 1621 SIMPLE t1 ALL btn NULL NULL NULL 14 Using where 163select * from t1 where btn like "q%"; 164btn 165alter table t1 add column new_col char(1) not null, add key (btn,new_col), drop key btn; 166update t1 set new_col=left(btn,1); 167explain select * from t1 where btn="a"; 168id select_type table type possible_keys key key_len ref rows Extra 1691 SIMPLE t1 ALL btn NULL NULL NULL 14 Using where 170explain select * from t1 where btn="a" and new_col="a"; 171id select_type table type possible_keys key key_len ref rows Extra 1721 SIMPLE t1 ref btn btn 11 const,const 2 Using where 173drop table t1; 174CREATE TABLE t1 ( 175a int default NULL, 176b int default NULL, 177KEY a (a), 178UNIQUE b (b) 179) engine=heap; 180INSERT INTO t1 VALUES (NULL,99),(99,NULL),(1,1),(2,2),(1,3); 181SELECT * FROM t1 WHERE a=NULL; 182a b 183explain SELECT * FROM t1 WHERE a IS NULL; 184id select_type table type possible_keys key key_len ref rows Extra 1851 SIMPLE t1 ref a a 5 const 2 Using where 186SELECT * FROM t1 WHERE a<=>NULL; 187a b 188NULL 99 189SELECT * FROM t1 WHERE b=NULL; 190a b 191explain SELECT * FROM t1 WHERE b IS NULL; 192id select_type table type possible_keys key key_len ref rows Extra 1931 SIMPLE t1 ref b b 5 const 1 Using where 194SELECT * FROM t1 WHERE b<=>NULL; 195a b 19699 NULL 197INSERT INTO t1 VALUES (1,3); 198ERROR 23000: Duplicate entry '3' for key 'b' 199DROP TABLE t1; 200CREATE TABLE t1 ( 201a int default NULL, 202key a (a) 203) ENGINE=HEAP; 204INSERT INTO t1 VALUES (10), (10), (10); 205EXPLAIN SELECT * FROM t1 WHERE a=10; 206id select_type table type possible_keys key key_len ref rows Extra 2071 SIMPLE t1 ref a a 5 const 3 NULL 208SELECT * FROM t1 WHERE a=10; 209a 21010 21110 21210 213DROP TABLE t1; 214CREATE TABLE t1 (a int not null, primary key(a)) engine=heap; 215INSERT into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11); 216DELETE from t1 where a < 100; 217SELECT * from t1; 218a 219DROP TABLE t1; 220CREATE TABLE `job_titles` ( 221`job_title_id` int(6) unsigned NOT NULL default '0', 222`job_title` char(18) NOT NULL default '', 223PRIMARY KEY (`job_title_id`), 224UNIQUE KEY `job_title_id` (`job_title_id`,`job_title`) 225) ENGINE=HEAP; 226SELECT MAX(job_title_id) FROM job_titles; 227MAX(job_title_id) 228NULL 229DROP TABLE job_titles; 230CREATE TABLE t1 (a INT NOT NULL, B INT, KEY(B)) ENGINE=HEAP; 231INSERT INTO t1 VALUES(1,1), (1,NULL); 232SELECT * FROM t1 WHERE B is not null; 233a B 2341 1 235DROP TABLE t1; 236CREATE TABLE t1 (pseudo char(35) PRIMARY KEY, date int(10) unsigned NOT NULL) ENGINE=HEAP; 237INSERT INTO t1 VALUES ('massecot',1101106491),('altec',1101106492),('stitch+',1101106304),('Seb Corgan',1101106305),('beerfilou',1101106263),('flaker',1101106529),('joce8',5),('M4vrick',1101106418),('gabay008',1101106525),('Vamp irX',1101106291),('ZoomZip',1101106546),('rip666',1101106502),('CBP ',1101106397),('guezpard',1101106496); 238DELETE FROM t1 WHERE date<1101106546; 239SELECT * FROM t1; 240pseudo date 241ZoomZip 1101106546 242DROP TABLE t1; 243create table t1(a char(2)) engine=memory; 244insert into t1 values (NULL), (NULL); 245delete from t1 where a is null; 246insert into t1 values ('2'), ('3'); 247select * from t1; 248a 2493 2502 251drop table t1; 252set default_storage_engine=HEAP; 253create table t1 (v varchar(10), c char(10), t varchar(50)); 254insert into t1 values('+ ', '+ ', '+ '); 255set @a=repeat(' ',20); 256insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a)); 257Warnings: 258Note 1265 Data truncated for column 'v' at row 1 259select concat('*',v,'*',c,'*',t,'*') from t1; 260concat('*',v,'*',c,'*',t,'*') 261*+ *+*+ * 262*+ *+*+ * 263show create table t1; 264Table Create Table 265t1 CREATE TABLE `t1` ( 266 `v` varchar(10) DEFAULT NULL, 267 `c` char(10) DEFAULT NULL, 268 `t` varchar(50) DEFAULT NULL 269) ENGINE=MEMORY DEFAULT CHARSET=latin1 270create table t2 like t1; 271show create table t2; 272Table Create Table 273t2 CREATE TABLE `t2` ( 274 `v` varchar(10) DEFAULT NULL, 275 `c` char(10) DEFAULT NULL, 276 `t` varchar(50) DEFAULT NULL 277) ENGINE=MEMORY DEFAULT CHARSET=latin1 278create table t3 select * from t1; 279show create table t3; 280Table Create Table 281t3 CREATE TABLE `t3` ( 282 `v` varchar(10) DEFAULT NULL, 283 `c` char(10) DEFAULT NULL, 284 `t` varchar(50) DEFAULT NULL 285) ENGINE=MEMORY DEFAULT CHARSET=latin1 286alter table t1 modify c varchar(10); 287show create table t1; 288Table Create Table 289t1 CREATE TABLE `t1` ( 290 `v` varchar(10) DEFAULT NULL, 291 `c` varchar(10) DEFAULT NULL, 292 `t` varchar(50) DEFAULT NULL 293) ENGINE=MEMORY DEFAULT CHARSET=latin1 294alter table t1 modify v char(10); 295show create table t1; 296Table Create Table 297t1 CREATE TABLE `t1` ( 298 `v` char(10) DEFAULT NULL, 299 `c` varchar(10) DEFAULT NULL, 300 `t` varchar(50) DEFAULT NULL 301) ENGINE=MEMORY DEFAULT CHARSET=latin1 302alter table t1 modify t varchar(10); 303Warnings: 304Warning 1265 Data truncated for column 't' at row 2 305show create table t1; 306Table Create Table 307t1 CREATE TABLE `t1` ( 308 `v` char(10) DEFAULT NULL, 309 `c` varchar(10) DEFAULT NULL, 310 `t` varchar(10) DEFAULT NULL 311) ENGINE=MEMORY DEFAULT CHARSET=latin1 312select concat('*',v,'*',c,'*',t,'*') from t1; 313concat('*',v,'*',c,'*',t,'*') 314*+*+*+ * 315*+*+*+ * 316drop table t1,t2,t3; 317create table t1 (v varchar(10), c char(10), t varchar(50), key(v), key(c), key(t(10))); 318show create table t1; 319Table Create Table 320t1 CREATE TABLE `t1` ( 321 `v` varchar(10) DEFAULT NULL, 322 `c` char(10) DEFAULT NULL, 323 `t` varchar(50) DEFAULT NULL, 324 KEY `v` (`v`), 325 KEY `c` (`c`), 326 KEY `t` (`t`(10)) 327) ENGINE=MEMORY DEFAULT CHARSET=latin1 328select count(*) from t1; 329count(*) 330270 331insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1))); 332select count(*) from t1 where v='a'; 333count(*) 33410 335select count(*) from t1 where c='a'; 336count(*) 33710 338select count(*) from t1 where t='a'; 339count(*) 34010 341select count(*) from t1 where v='a '; 342count(*) 34310 344select count(*) from t1 where c='a '; 345count(*) 34610 347select count(*) from t1 where t='a '; 348count(*) 34910 350select count(*) from t1 where v between 'a' and 'a '; 351count(*) 35210 353select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 354count(*) 35510 356select count(*) from t1 where v like 'a%'; 357count(*) 35811 359select count(*) from t1 where c like 'a%'; 360count(*) 36111 362select count(*) from t1 where t like 'a%'; 363count(*) 36411 365select count(*) from t1 where v like 'a %'; 366count(*) 3679 368explain select count(*) from t1 where v='a '; 369id select_type table type possible_keys key key_len ref rows Extra 3701 SIMPLE t1 ref v v 13 const 10 Using where 371explain select count(*) from t1 where c='a '; 372id select_type table type possible_keys key key_len ref rows Extra 3731 SIMPLE t1 ref c c 11 const 10 Using where 374explain select count(*) from t1 where t='a '; 375id select_type table type possible_keys key key_len ref rows Extra 3761 SIMPLE t1 ref t t 13 const 10 Using where 377explain select count(*) from t1 where v like 'a%'; 378id select_type table type possible_keys key key_len ref rows Extra 3791 SIMPLE t1 ALL v NULL NULL NULL 271 Using where 380explain select count(*) from t1 where v between 'a' and 'a '; 381id select_type table type possible_keys key key_len ref rows Extra 3821 SIMPLE t1 ref v v 13 const 10 Using where 383explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 384id select_type table type possible_keys key key_len ref rows Extra 3851 SIMPLE t1 ref v v 13 const 10 Using where 386alter table t1 add unique(v); 387ERROR 23000: Duplicate entry '{ ' for key 'v_2' 388select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a' order by length(concat('*',v,'*',c,'*',t,'*')); 389qq 390*a*a*a* 391*a *a*a * 392*a *a*a * 393*a *a*a * 394*a *a*a * 395*a *a*a * 396*a *a*a * 397*a *a*a * 398*a *a*a * 399*a *a*a * 400explain select * from t1 where v='a'; 401id select_type table type possible_keys key key_len ref rows Extra 4021 SIMPLE t1 ref v v 13 const 10 Using where 403select v,count(*) from t1 group by v limit 10; 404v count(*) 405a 1 406a 10 407b 10 408c 10 409d 10 410e 10 411f 10 412g 10 413h 10 414i 10 415select v,count(t) from t1 group by v limit 10; 416v count(t) 417a 1 418a 10 419b 10 420c 10 421d 10 422e 10 423f 10 424g 10 425h 10 426i 10 427select v,count(c) from t1 group by v limit 10; 428v count(c) 429a 1 430a 10 431b 10 432c 10 433d 10 434e 10 435f 10 436g 10 437h 10 438i 10 439select sql_big_result trim(v),count(t) from t1 group by v limit 10; 440trim(v) count(t) 441a 1 442a 10 443b 10 444c 10 445d 10 446e 10 447f 10 448g 10 449h 10 450i 10 451select sql_big_result trim(v),count(c) from t1 group by v limit 10; 452trim(v) count(c) 453a 1 454a 10 455b 10 456c 10 457d 10 458e 10 459f 10 460g 10 461h 10 462i 10 463select c,count(*) from t1 group by c limit 10; 464c count(*) 465a 1 466a 10 467b 10 468c 10 469d 10 470e 10 471f 10 472g 10 473h 10 474i 10 475select c,count(t) from t1 group by c limit 10; 476c count(t) 477a 1 478a 10 479b 10 480c 10 481d 10 482e 10 483f 10 484g 10 485h 10 486i 10 487select sql_big_result c,count(t) from t1 group by c limit 10; 488c count(t) 489a 1 490a 10 491b 10 492c 10 493d 10 494e 10 495f 10 496g 10 497h 10 498i 10 499select t,count(*) from t1 group by t limit 10; 500t count(*) 501a 1 502a 10 503b 10 504c 10 505d 10 506e 10 507f 10 508g 10 509h 10 510i 10 511select t,count(t) from t1 group by t limit 10; 512t count(t) 513a 1 514a 10 515b 10 516c 10 517d 10 518e 10 519f 10 520g 10 521h 10 522i 10 523select sql_big_result trim(t),count(t) from t1 group by t limit 10; 524trim(t) count(t) 525a 1 526a 10 527b 10 528c 10 529d 10 530e 10 531f 10 532g 10 533h 10 534i 10 535drop table t1; 536create table t1 (a char(10), unique (a)); 537insert into t1 values ('a'); 538insert into t1 values ('a '); 539ERROR 23000: Duplicate entry 'a' for key 'a' 540alter table t1 modify a varchar(10); 541insert into t1 values ('a '),('a '),('a '),('a '); 542ERROR 23000: Duplicate entry 'a ' for key 'a' 543insert into t1 values ('a '); 544ERROR 23000: Duplicate entry 'a ' for key 'a' 545insert into t1 values ('a '); 546ERROR 23000: Duplicate entry 'a ' for key 'a' 547insert into t1 values ('a '); 548ERROR 23000: Duplicate entry 'a ' for key 'a' 549update t1 set a='a ' where a like 'a '; 550update t1 set a='a ' where a like 'a '; 551drop table t1; 552create table t1 (v varchar(10), c char(10), t varchar(50), key using btree (v), key using btree (c), key using btree (t(10))); 553show create table t1; 554Table Create Table 555t1 CREATE TABLE `t1` ( 556 `v` varchar(10) DEFAULT NULL, 557 `c` char(10) DEFAULT NULL, 558 `t` varchar(50) DEFAULT NULL, 559 KEY `v` (`v`) USING BTREE, 560 KEY `c` (`c`) USING BTREE, 561 KEY `t` (`t`(10)) USING BTREE 562) ENGINE=MEMORY DEFAULT CHARSET=latin1 563select count(*) from t1; 564count(*) 565270 566insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1))); 567select count(*) from t1 where v='a'; 568count(*) 56910 570select count(*) from t1 where c='a'; 571count(*) 57210 573select count(*) from t1 where t='a'; 574count(*) 57510 576select count(*) from t1 where v='a '; 577count(*) 57810 579select count(*) from t1 where c='a '; 580count(*) 58110 582select count(*) from t1 where t='a '; 583count(*) 58410 585select count(*) from t1 where v between 'a' and 'a '; 586count(*) 58710 588select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 589count(*) 59010 591explain select count(*) from t1 where v='a '; 592id select_type table type possible_keys key key_len ref rows Extra 5931 SIMPLE t1 ref v v 13 const # Using where 594explain select count(*) from t1 where c='a '; 595id select_type table type possible_keys key key_len ref rows Extra 5961 SIMPLE t1 ref c c 11 const # Using where 597explain select count(*) from t1 where t='a '; 598id select_type table type possible_keys key key_len ref rows Extra 5991 SIMPLE t1 ref t t 13 const # Using where 600explain select count(*) from t1 where v like 'a%'; 601id select_type table type possible_keys key key_len ref rows Extra 6021 SIMPLE t1 range v v 13 NULL # Using where 603explain select count(*) from t1 where v between 'a' and 'a '; 604id select_type table type possible_keys key key_len ref rows Extra 6051 SIMPLE t1 ref v v 13 const # Using where 606explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 607id select_type table type possible_keys key key_len ref rows Extra 6081 SIMPLE t1 ref v v 13 const # Using where 609alter table t1 add unique(v); 610ERROR 23000: Duplicate entry '{ ' for key 'v_2' 611select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a' order by length(concat('*',v,'*',c,'*',t,'*')); 612qq 613*a*a*a* 614*a *a*a * 615*a *a*a * 616*a *a*a * 617*a *a*a * 618*a *a*a * 619*a *a*a * 620*a *a*a * 621*a *a*a * 622*a *a*a * 623explain select * from t1 where v='a'; 624id select_type table type possible_keys key key_len ref rows Extra 6251 SIMPLE t1 ref v v 13 const # Using where 626drop table t1; 627create table t1 (a char(10), unique using btree (a)) engine=heap; 628insert into t1 values ('a'); 629insert into t1 values ('a '); 630ERROR 23000: Duplicate entry 'a' for key 'a' 631alter table t1 modify a varchar(10); 632insert into t1 values ('a '),('a '),('a '),('a '); 633ERROR 23000: Duplicate entry 'a ' for key 'a' 634insert into t1 values ('a '); 635ERROR 23000: Duplicate entry 'a ' for key 'a' 636insert into t1 values ('a '); 637ERROR 23000: Duplicate entry 'a ' for key 'a' 638insert into t1 values ('a '); 639ERROR 23000: Duplicate entry 'a ' for key 'a' 640update t1 set a='a ' where a like 'a '; 641update t1 set a='a ' where a like 'a '; 642drop table t1; 643create table t1 (v varchar(10), c char(10), t varchar(50), key(v(5)), key(c(5)), key(t(5))); 644show create table t1; 645Table Create Table 646t1 CREATE TABLE `t1` ( 647 `v` varchar(10) DEFAULT NULL, 648 `c` char(10) DEFAULT NULL, 649 `t` varchar(50) DEFAULT NULL, 650 KEY `v` (`v`(5)), 651 KEY `c` (`c`(5)), 652 KEY `t` (`t`(5)) 653) ENGINE=MEMORY DEFAULT CHARSET=latin1 654drop table t1; 655create table t1 (v varchar(65530), key(v(10))); 656show create table t1; 657Table Create Table 658t1 CREATE TABLE `t1` ( 659 `v` varchar(65530) DEFAULT NULL, 660 KEY `v` (`v`(10)) 661) ENGINE=MEMORY DEFAULT CHARSET=latin1 662insert into t1 values(repeat('a',65530)); 663select length(v) from t1 where v=repeat('a',65530); 664length(v) 66565530 666drop table t1; 667set default_storage_engine=MyISAM; 668create table t1 (a bigint unsigned auto_increment primary key, b int, 669key (b, a)) engine=heap; 670insert t1 (b) values (1),(1),(1),(1),(1),(1),(1),(1); 671select * from t1; 672a b 6731 1 6742 1 6753 1 6764 1 6775 1 6786 1 6797 1 6808 1 681drop table t1; 682create table t1 (a int not null, b int not null auto_increment, 683primary key(a, b), key(b)) engine=heap; 684insert t1 (a) values (1),(1),(1),(1),(1),(1),(1),(1); 685select * from t1; 686a b 6871 1 6881 2 6891 3 6901 4 6911 5 6921 6 6931 7 6941 8 695drop table t1; 696create table t1 (a int not null, b int not null auto_increment, 697primary key(a, b)) engine=heap; 698ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key 699create table t1 (c char(255), primary key(c(90))); 700insert into t1 values ("abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"); 701insert into t1 values ("abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"); 702ERROR 23000: Duplicate entry 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijkl' for key 'PRIMARY' 703drop table t1; 704# 705# Bug 12796: Record doesn't show when selecting through index 706# 707CREATE TABLE t1 (a int, key(a)) engine=heap; 708insert into t1 values (0); 709delete from t1; 710select * from t1; 711a 712insert into t1 values (0), (1); 713select * from t1 where a = 0; 714a 7150 716drop table t1; 717create table t1 (c char(10)) engine=memory; 718create table t2 (c varchar(10)) engine=memory; 719show table status like 't_'; 720Name 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 721t1 MEMORY 10 Fixed 0 11 0 # 0 0 NULL # NULL NULL latin1_swedish_ci NULL 722t2 MEMORY 10 Fixed 0 12 0 # 0 0 NULL # NULL NULL latin1_swedish_ci NULL 723drop table t1, t2; 724CREATE TABLE t1(a VARCHAR(1), b VARCHAR(2), c VARCHAR(256), 725KEY(a), KEY(b), KEY(c)) ENGINE=MEMORY; 726INSERT INTO t1 VALUES('a','aa',REPEAT('a', 256)),('a','aa',REPEAT('a',256)); 727SELECT COUNT(*) FROM t1 WHERE a='a'; 728COUNT(*) 7292 730SELECT COUNT(*) FROM t1 WHERE b='aa'; 731COUNT(*) 7322 733SELECT COUNT(*) FROM t1 WHERE c=REPEAT('a',256); 734COUNT(*) 7352 736DROP TABLE t1; 737CREATE TABLE t1(c1 VARCHAR(100), c2 INT) ENGINE=MEMORY; 738INSERT INTO t1 VALUES('', 0); 739ALTER TABLE t1 MODIFY c1 VARCHAR(101); 740SELECT c2 FROM t1; 741c2 7420 743DROP TABLE t1; 744# 745# BUG#11825482: Broken key length calculation for btree index 746# 747CREATE TABLE h1 (f1 VARCHAR(1), f2 INT NOT NULL, 748UNIQUE KEY h1i (f1,f2) USING BTREE ) ENGINE=HEAP; 749INSERT INTO h1 VALUES(NULL,0),(NULL,1); 750SELECT 'wrong' as 'result' FROM dual WHERE ('h', 0) NOT IN (SELECT * FROM h1); 751result 752CREATE TABLE t1 ( 753pk int NOT NULL, 754col_int_nokey INT, 755col_varchar_nokey VARCHAR(1), 756PRIMARY KEY (pk) 757); 758INSERT INTO t1 VALUES (19,5,'h'),(20,5,'h'); 759CREATE TABLE t2 (col_int_nokey INT); 760INSERT INTO t2 VALUES (1),(2); 761CREATE VIEW v1 AS 762SELECT col_varchar_nokey, COUNT( col_varchar_nokey ) 763FROM t1 764WHERE col_int_nokey <= 141 AND pk <= 4 765; 766SELECT col_int_nokey FROM t2 767WHERE ('h', 0) NOT IN ( SELECT * FROM v1); 768col_int_nokey 769# shouldn't crash 770EXPLAIN SELECT col_int_nokey FROM t2 771WHERE ('h', 0) NOT IN ( SELECT * FROM v1); 772id select_type table type possible_keys key key_len ref rows Extra 7731 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 7742 DEPENDENT SUBQUERY <derived3> system NULL NULL NULL NULL 1 NULL 7753 DERIVED t1 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using where 776DROP TABLE t1,t2,h1; 777DROP VIEW v1; 778CREATE TABLE t1 ( 779c1 VARCHAR(10) NOT NULL, 780KEY i1 (c1(3)) 781) ENGINE=MEMORY DEFAULT CHARSET=latin1; 782INSERT INTO t1 VALUES ('foo1'), ('bar2'), ('baz3'); 783SELECT * FROM t1 WHERE c1='bar2'; 784c1 785bar2 786#should show one tuple! 787SELECT * FROM t1 IGNORE INDEX (i1) WHERE c1='bar2'; 788c1 789bar2 790#should show one tuple! 791DROP TABLE t1; 792