1--disable_warnings 2drop table if exists t1,t2,t3; 3--enable_warnings 4--source include/have_sequence.inc 5SET SQL_WARNINGS=1; 6 7# 8# This failed for Elizabeth Mattijsen 9# 10 11CREATE TABLE t1 ( 12 ID CHAR(32) NOT NULL, 13 name CHAR(32) NOT NULL, 14 value CHAR(255), 15 INDEX indexIDname (ID(8),name(8)) 16) ; 17 18INSERT INTO t1 VALUES 19('keyword','indexdir','/export/home/local/www/database/indexes/keyword'); 20INSERT INTO t1 VALUES ('keyword','urlprefix','text/ /text'); 21INSERT INTO t1 VALUES ('keyword','urlmap','/text/ /'); 22INSERT INTO t1 VALUES ('keyword','attr','personal employee company'); 23INSERT INTO t1 VALUES 24('emailgids','indexdir','/export/home/local/www/database/indexes/emailgids'); 25INSERT INTO t1 VALUES ('emailgids','urlprefix','text/ /text'); 26INSERT INTO t1 VALUES ('emailgids','urlmap','/text/ /'); 27INSERT INTO t1 VALUES ('emailgids','attr','personal employee company'); 28 29SELECT value FROM t1 WHERE ID='emailgids' AND name='attr'; 30 31drop table t1; 32 33# 34# Problem with many key parts and many or 35# 36 37CREATE TABLE t1 ( 38 price int(5) DEFAULT '0' NOT NULL, 39 area varchar(40) DEFAULT '' NOT NULL, 40 type varchar(40) DEFAULT '' NOT NULL, 41 transityes enum('Y','N') DEFAULT 'Y' NOT NULL, 42 shopsyes enum('Y','N') DEFAULT 'Y' NOT NULL, 43 schoolsyes enum('Y','N') DEFAULT 'Y' NOT NULL, 44 petsyes enum('Y','N') DEFAULT 'Y' NOT NULL, 45 KEY price (price,area,type,transityes,shopsyes,schoolsyes,petsyes) 46); 47 48INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','N','N','N','N'); 49INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','N','N','N','N'); 50INSERT IGNORE INTO t1 VALUES (900,'Vancouver','Shared/Roomate','','','',''); 51INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y'); 52INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y'); 53INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y'); 54INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y'); 55INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y'); 56 57 SELECT * FROM t1 WHERE area='Vancouver' and transityes='y' and schoolsyes='y' and ( ((type='1 Bedroom' or type='Studio/Bach') and (price<=500)) or ((type='2 Bedroom') and (price<=550)) or ((type='Shared/Roomate') and (price<=300)) or ((type='Room and Board') and (price<=500)) ) and price <= 400; 58 59drop table t1; 60 61# 62# No longer a problem with primary key 63# 64 65CREATE TABLE t1 (program enum('signup','unique','sliding') not null, type enum('basic','sliding','signup'), sites set('mt'), PRIMARY KEY (program)); 66# This no longer give an error for wrong primary key 67ALTER TABLE t1 modify program enum('signup','unique','sliding'); 68drop table t1; 69 70# 71# Test of compressed decimal index. 72# 73 74CREATE TABLE t1 ( 75 name varchar(50) DEFAULT '' NOT NULL, 76 author varchar(50) DEFAULT '' NOT NULL, 77 category decimal(10,0) DEFAULT '0' NOT NULL, 78 email varchar(50), 79 password varchar(50), 80 proxy varchar(50), 81 bitmap varchar(20), 82 msg varchar(255), 83 urlscol varchar(127), 84 urlhttp varchar(127), 85 timeout decimal(10,0), 86 nbcnx decimal(10,0), 87 creation decimal(10,0), 88 livinguntil decimal(10,0), 89 lang decimal(10,0), 90 type decimal(10,0), 91 subcat decimal(10,0), 92 subtype decimal(10,0), 93 reg char(1), 94 scs varchar(255), 95 capacity decimal(10,0), 96 userISP varchar(50), 97 CCident varchar(50) DEFAULT '' NOT NULL, 98 PRIMARY KEY (name,author,category) 99); 100INSERT INTO t1 VALUES 101('patnom','patauteur',0,'p.favre@cryo-networks.fr',NULL,NULL,'#p2sndnq6ae5g1u6t','essai salut','scol://195.242.78.119:patauteur.patnom',NULL,NULL,NULL,950036174,-882087474,NULL,3,0,3,'1','Pub/patnom/futur_divers.scs',NULL,'pat','CC1'); 102INSERT INTO t1 VALUES 103('LeNomDeMonSite','Marc',0,'m.barilley@cryo-networks.fr',NULL,NULL,NULL,NULL,'scol://195.242.78.119:Marc.LeNomDeMonSite',NULL,NULL,NULL,950560434,-881563214,NULL,3,0,3,'1','Pub/LeNomDeMonSite/domus_hibere.scs',NULL,'Marq','CC1'); 104select * from t1 where name='patnom' and author='patauteur' and category=0; 105drop table t1; 106 107# 108# Problem with search on partial index 109# 110 111create table t1 112( 113 name_id int not null auto_increment, 114 name blob, 115 INDEX name_idx (name(5)), 116 primary key (name_id) 117); 118 119INSERT t1 VALUES(NULL,'/'); 120INSERT t1 VALUES(NULL,'[T,U]_axpby'); 121SELECT * FROM t1 WHERE name='[T,U]_axpy'; 122SELECT * FROM t1 WHERE name='[T,U]_axpby'; 123create table t2 124( 125 name_id int not null auto_increment, 126 name char(255) binary, 127 INDEX name_idx (name(5)), 128 primary key (name_id) 129); 130INSERT t2 select * from t1; 131SELECT * FROM t2 WHERE name='[T,U]_axpy'; 132SELECT * FROM t2 WHERE name='[T,U]_axpby'; 133# Test possible problems with warnings in CREATE ... SELECT 134CREATE TABLE t3 SELECT * FROM t2 WHERE name='[T,U]_axpby'; 135SELECT * FROM t2 WHERE name='[T,U]_axpby'; 136 137drop table t1,t2,t3; 138 139# 140# Test bug with long primary key 141# 142 143create table t1 144( 145 SEQNO numeric(12 ) not null, 146 MOTYPEID numeric(12 ) not null, 147 MOINSTANCEID numeric(12 ) not null, 148 ATTRID numeric(12 ) not null, 149 VALUE varchar(120) not null, 150 primary key (SEQNO, MOTYPEID, MOINSTANCEID, ATTRID, VALUE ) 151); 152INSERT INTO t1 VALUES (1, 1, 1, 1, 'a'); 153INSERT INTO t1 VALUES (1, 1, 1, 1, 'b'); 154--error ER_DUP_ENTRY 155INSERT INTO t1 VALUES (1, 1, 1, 1, 'a'); 156drop table t1; 157 158# 159# Test with blob + tinyint key 160# (Failed for Greg Valure) 161# 162 163CREATE TABLE t1 ( 164 a tinytext NOT NULL, 165 b tinyint(3) unsigned NOT NULL default '0', 166 PRIMARY KEY (a(32),b) 167) ENGINE=MyISAM; 168INSERT INTO t1 VALUES ('a',1),('a',2); 169SELECT * FROM t1 WHERE a='a' AND b=2; 170SELECT * FROM t1 WHERE a='a' AND b in (2); 171SELECT * FROM t1 WHERE a='a' AND b in (1,2); 172drop table t1; 173 174# 175# Test of create key order 176# 177 178create table t1 (a int not null unique, b int unique, c int, d int not null primary key, key(c), e int not null unique); 179show keys from t1; 180drop table t1; 181 182# 183# Problem with UNIQUE() with NULL parts and auto increment 184# 185 186CREATE TABLE t1 (c CHAR(10) NOT NULL,i INT NOT NULL AUTO_INCREMENT, 187UNIQUE (c,i)); 188INSERT IGNORE INTO t1 (c) VALUES (NULL),(NULL); 189SELECT * FROM t1; 190INSERT INTO t1 (c) VALUES ('a'),('a'); 191SELECT * FROM t1; 192DROP TABLE IF EXISTS t1; 193CREATE TABLE t1 (c CHAR(10) NULL, i INT NOT NULL AUTO_INCREMENT, 194UNIQUE (c,i)); 195INSERT INTO t1 (c) VALUES (NULL),(NULL); 196SELECT * FROM t1; 197INSERT INTO t1 (c) VALUES ('a'),('a'); 198SELECT * FROM t1; 199drop table t1; 200 201# 202# longer keys 203# 204create table t1 (i int, a char(200), b text, unique (a), unique (b(300))) charset utf8; 205insert ignore t1 values (1, repeat('a',210), repeat('b', 310)); 206insert ignore t1 values (2, repeat(0xD0B1,215), repeat(0xD0B1, 310)); 207select i, length(a), length(b), char_length(a), char_length(b) from t1; 208select i from t1 where a=repeat(_utf8 'a',200); 209select i from t1 where a=repeat(_utf8 0xD0B1,200); 210select i from t1 where b=repeat(_utf8 'b',310); 211drop table t1; 212 213# 214# Test of key read with primary key (Bug #3497) 215# 216 217CREATE TABLE t1 (id int unsigned auto_increment, name char(50), primary key (id)) engine=myisam; 218insert into t1 (name) values ('a'), ('b'),('c'),('d'),('e'),('f'),('g'); 219explain select 1 from t1 where id =2; 220explain select 1 from t1 where id =2 or id=3; 221explain select name from t1 where id =2; 222ALTER TABLE t1 DROP PRIMARY KEY, ADD INDEX (id); 223explain select 1 from t1 where id =2; 224drop table t1; 225 226# 227# Test of problem with key read (Bug #3666) 228# 229 230CREATE TABLE t1 (numeropost mediumint(8) unsigned NOT NULL default '0', numreponse int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (numeropost,numreponse), UNIQUE KEY numreponse (numreponse)); 231INSERT INTO t1 (numeropost,numreponse) VALUES ('1','1'),('1','2'),('2','3'),('2','4'); 232SELECT numeropost FROM t1 WHERE numreponse='1'; 233EXPLAIN SELECT numeropost FROM t1 WHERE numreponse='1'; 234FLUSH TABLES; 235SELECT numeropost FROM t1 WHERE numreponse='1'; 236drop table t1; 237 238# 239# UNIQUE prefix keys and multi-byte charsets 240# 241 242create table t1 (c varchar(30) character set utf8, t text character set utf8, unique (c(2)), unique (t(3))) engine=myisam; 243show create table t1; 244insert t1 values ('cccc', 'tttt'), 245 (0xD0B1212223D0B1D0B1D0B1D0B1D0B1, 0xD0B1D0B1212223D0B1D0B1D0B1D0B1), 246 (0xD0B1222123D0B1D0B1D0B1D0B1D0B1, 0xD0B1D0B1222123D0B1D0B1D0B1D0B1); 247--error ER_DUP_ENTRY 248insert t1 (c) values ('cc22'); 249--error ER_DUP_ENTRY 250insert t1 (t) values ('ttt22'); 251--error ER_DUP_ENTRY 252insert t1 (c) values (0xD0B1212322D0B1D0B1D0B1D0B1D0B1); 253--error ER_DUP_ENTRY 254insert t1 (t) values (0xD0B1D0B1212322D0B1D0B1D0B1D0B1); 255select c from t1 where c='cccc'; 256select t from t1 where t='tttt'; 257select c from t1 where c=0xD0B1212223D0B1D0B1D0B1D0B1D0B1; 258select t from t1 where t=0xD0B1D0B1212223D0B1D0B1D0B1D0B1; 259drop table t1; 260 261# 262# BUG#6151 - myisam index corruption 263# 264DROP TABLE IF EXISTS t1; 265CREATE TABLE t1 ( 266 c1 int, 267 c2 varbinary(240), 268 UNIQUE KEY (c1), 269 KEY (c2) 270) ENGINE=MyISAM; 271INSERT INTO t1 VALUES (1,'\Z\Z\Z\Z'); 272INSERT INTO t1 VALUES (2,'\Z\Z\Z\Z\Z\Z'); 273INSERT INTO t1 VALUES (3,'\Z\Z\Z\Z'); 274select c1 from t1 where c2='\Z\Z\Z\Z'; 275DELETE FROM t1 WHERE (c1 = 1); 276check table t1; 277select c1 from t1 where c2='\Z\Z\Z\Z'; 278DELETE FROM t1 WHERE (c1 = 3); 279check table t1; 280select c1 from t1 where c2='\Z\Z\Z\Z'; 281 282# 283# test delete of keys in a different order 284# 285truncate table t1; 286insert into t1 values(1,"aaaa"),(2,"aaab"),(3,"aaac"),(4,"aaccc"); 287delete from t1 where c1=3; 288delete from t1 where c1=1; 289delete from t1 where c1=4; 290check table t1; 291 292drop table t1; 293 294# 295# Bug 6166: index prefix length of 0 not rejected 296# 297# this test should fail in 5.0 298# to fix it, remove #ifdef in 299# file sql_yacc.yy(key_part) 300# create dedicated error code for this and 301# and change my_printf_error() to my_error 302 303--error 1391 304create table t1 (c char(10), index (c(0))); 305 306# 307# Bug #6126: Duplicate columns in keys should fail 308# Bug #6252: (dup) 309# 310--error 1060 311create table t1 (c char(10), index (c,c)); 312--error 1060 313create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)); 314--error 1060 315create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)); 316--error 1060 317create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)); 318create table t1 (c1 char(10), c2 char(10)); 319--error 1060 320alter table t1 add key (c1,c1); 321--error 1060 322alter table t1 add key (c2,c1,c1); 323--error 1060 324alter table t1 add key (c1,c2,c1); 325--error 1060 326alter table t1 add key (c1,c1,c2); 327drop table t1; 328 329# 330# Bug#11228: DESC shows arbitrary column as "PRI" 331# 332create table t1 ( 333 i1 INT NOT NULL, 334 i2 INT NOT NULL, 335 UNIQUE i1idx (i1), 336 UNIQUE i2idx (i2)); 337desc t1; 338show create table t1; 339drop table t1; 340 341# 342# Bug#12565 - ERROR 1034 when running simple UPDATE or DELETE 343# on large MyISAM table 344# 345create table t1 ( 346 c1 int, 347 c2 varchar(20) not null, 348 primary key (c1), 349 key (c2(10)) 350) engine=myisam; 351insert into t1 values (1,''); 352insert into t1 values (2,' \t\tTest String'); 353insert into t1 values (3,' \n\tTest String'); 354update t1 set c2 = 'New Test String' where c1 = 1; 355select * from t1; 356drop table t1; 357 358# 359# If we use a partial field for a key that is actually the length of the 360# field, and we extend the field, we end up with a key that includes the 361# whole new length of the field. 362# 363create table t1 (a varchar(10), b varchar(10), key(a(10),b(10))); 364show create table t1; 365alter table t1 modify b varchar(20); 366show create table t1; 367alter table t1 modify a varchar(20); 368show create table t1; 369drop table t1; 370 371# 372# Bug #11227: Incorrectly reporting 'MUL' vs. 'UNI' on varchar 373# 374create table t1 (a int not null primary key, b varchar(20) not null unique); 375desc t1; 376drop table t1; 377create table t1 (a int not null primary key, b int not null unique); 378desc t1; 379drop table t1; 380create table t1 (a int not null primary key, b varchar(20) not null, unique (b(10))); 381desc t1; 382drop table t1; 383create table t1 (a int not null primary key, b varchar(20) not null, c varchar(20) not null, unique(b(10),c(10))); 384desc t1; 385drop table t1; 386 387# End of 4.1 tests 388 389# 390# WL#1563 - Modify MySQL to support on-line CREATE/DROP INDEX 391# To test if this really works, you need to run with --debug 392# and check the trace file. 393# 394# Create a table with named and unnamed indexes. 395create table t1 ( 396 c1 int, 397 c2 char(12), 398 c3 varchar(123), 399 c4 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 400 index (c1), 401 index i1 (c1), 402 index i2 (c2), 403 index i3 (c3), 404 unique i4 (c4), 405 index i5 (c1, c2, c3, c4), 406 primary key (c2, c3), 407 index (c2, c4)); 408show create table t1; 409# Some simple tests. 410alter table t1 drop index c1; 411alter table t1 add index (c1); 412# This creates index 'c1_2'. 413alter table t1 add index (c1); 414alter table t1 drop index i3; 415alter table t1 add index i3 (c3); 416# Two indexes at the same time. 417alter table t1 drop index i2, drop index i4; 418alter table t1 add index i2 (c2), add index i4 (c4); 419# Three indexes, one of them reversely. 420alter table t1 drop index i2, drop index i4, add index i6 (c2, c4); 421alter table t1 add index i2 (c2), add index i4 (c4), drop index i6; 422# include an unique index. 423alter table t1 drop index i2, drop index i4, add unique i4 (c4); 424alter table t1 add index i2 (c2), drop index i4, add index i4 (c4); 425# Modify an index by changing its definition. 426alter table t1 drop index c2, add index (c2(4),c3(7)); 427# Change nothing. The new key definition is the same as the old one. 428alter table t1 drop index c2, add index (c2(4),c3(7)); 429# Test primary key handling. 430alter table t1 add primary key (c1, c2), drop primary key; 431alter table t1 drop primary key; 432# Drop is checked first. Primary key must exist. 433--error 1091 434alter table t1 add primary key (c1, c2), drop primary key; 435show create table t1; 436# Insert non-unique values. 437insert into t1 values(1, 'a', 'a', NULL); 438insert into t1 values(1, 'b', 'b', NULL); 439# Drop some indexes for new adds. 440alter table t1 drop index i3, drop index i2, drop index i1; 441# Add indexes, one is unique on non-unique values. 442--error ER_DUP_ENTRY 443alter table t1 add index i3 (c3), add index i2 (c2), add unique index i1 (c1); 444drop table t1; 445 446 447# 448# Bug #20604: Test for disabled keys with aggregate functions and FORCE INDEX. 449# 450 451CREATE TABLE t1( a TINYINT, KEY(a) ) ENGINE=MyISAM; 452INSERT INTO t1 VALUES( 1 ); 453ALTER TABLE t1 DISABLE KEYS; 454EXPLAIN SELECT MAX(a) FROM t1 FORCE INDEX(a); 455 456drop table t1; 457 458# 459# Bug #24778: Innodb: No result when using ORDER BY 460# 461CREATE TABLE t1 ( 462 a INTEGER auto_increment PRIMARY KEY, 463 b INTEGER NOT NULL, 464 c INTEGER NOT NULL, 465 d CHAR(64) 466); 467 468CREATE TABLE t2 ( 469 a INTEGER auto_increment PRIMARY KEY, 470 b INTEGER NOT NULL, 471 c SMALLINT NOT NULL, 472 d DATETIME NOT NULL, 473 e SMALLINT NOT NULL, 474 f INTEGER NOT NULL, 475 g INTEGER NOT NULL, 476 h SMALLINT NOT NULL, 477 i INTEGER NOT NULL, 478 j INTEGER NOT NULL, 479 UNIQUE INDEX (b), 480 INDEX (b, d, e, f, g, h, i, j, c), 481 INDEX (c) 482); 483 484INSERT INTO t2 VALUES 485 (NULL, 1, 254, '1000-01-01 00:00:00', 257, 0, 0, 0, 0, 0), 486 (NULL, 2, 1, '2004-11-30 12:00:00', 1, 0, 0, 0, 0, 0), 487 (NULL, 3, 1, '2004-11-30 12:00:00', 1, 0, 0, 2, -21600, 0), 488 (NULL, 4, 1, '2004-11-30 12:00:00', 1, 0, 0, 2, -10800, 0), 489 (NULL, 5, 1, '2004-11-30 12:00:00', 1, 0, 0, 5, -10800, 0), 490 (NULL, 6, 1, '2004-11-30 12:00:00', 102, 0, 0, 0, 0, 0), 491 (NULL, 7, 1, '2004-11-30 12:00:00', 105, 2, 0, 0, 0, 0), 492 (NULL, 8, 1, '2004-11-30 12:00:00', 105, 10, 0, 0, 0, 0); 493 494INSERT INTO t1 (b, c, d) VALUES 495 (3388000, -553000, NULL), 496 (3388000, -553000, NULL); 497 498SELECT * 499FROM t2 c JOIN t1 pa ON c.b = pa.a 500WHERE c.c = 1 501ORDER BY c.b, c.d 502; 503 504DROP TABLE t1, t2; 505 506# 507# Bug #31137: Assertion failed: primary_key_no == -1 || primary_key_no == 0 508# 509create table t1(a int not null, key aa(a), 510 b char(10) not null, unique key bb(b(1)), 511 c char(4) not null, unique key cc(c)); 512desc t1; 513show create table t1; 514drop table t1; 515create table t1(a int not null, key aa(a), 516 b char(10) not null, unique key bb(b(1)), 517 c char(4) not null); 518desc t1; 519alter table t1 add unique key cc(c); 520desc t1; 521show create table t1; 522drop table t1; 523 524--echo End of 5.0 tests 525 526# 527# Bug #31148: bool close_thread_table(THD*, TABLE**): Assertion 528# `table->key_read == 0' failed. 529# 530 531--disable_warnings 532DROP TABLE IF EXISTS t1; 533--enable_warnings 534 535CREATE TABLE t1 (a INT PRIMARY KEY AUTO_INCREMENT); 536 537INSERT INTO t1 VALUES (), (), (); 538 539SELECT 1 AS c1 540FROM t1 541ORDER BY ( 542 SELECT 1 AS c2 543 FROM t1 544 GROUP BY GREATEST(LAST_INSERT_ID(), t1.a) ASC 545 LIMIT 1); 546 547DROP TABLE t1; 548 549 550# 551# Bug #31974: Wrong EXPLAIN output 552# 553 554CREATE TABLE t1 (a INT, b INT, INDEX (a,b)); 555INSERT INTO t1 (a, b) 556 VALUES 557 (1,1), (1,2), (1,3), (1,4), (1,5), 558 (2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6); 559ANALYZE table t1; 560EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE 561 (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; 562SELECT 1 as RES FROM t1 AS t1_outer WHERE 563 (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; 564 565DROP TABLE t1; 566 567--echo # 568--echo # Bug#18144: Cost with FORCE/USE index seems incorrect in some cases. 569--echo # 570--echo # We are interested in showing that the cost for the last plan is higher 571--echo # than for the preceding two plans. 572--echo # 573CREATE TABLE t1( a INT, b INT, KEY( a ) ); 574INSERT INTO t1 values (1, 2), (1, 3), (2, 3), (2, 4), (3, 4), (3, 5); 575 576EXPLAIN SELECT a, SUM( b ) FROM t1 GROUP BY a; 577SHOW STATUS LIKE 'Last_query_cost'; 578 579EXPLAIN SELECT a, SUM( b ) FROM t1 USE INDEX( a ) GROUP BY a; 580SHOW STATUS LIKE 'Last_query_cost'; 581 582EXPLAIN SELECT a, SUM( b ) FROM t1 FORCE INDEX( a ) GROUP BY a; 583SHOW STATUS LIKE 'Last_query_cost'; 584 585DROP TABLE t1; 586 587--echo # 588--echo # MDEV-21480: Unique key using ref access though eq_ref access can be used 589--echo # 590 591create table t1(a int, b int,c int, primary key(a), unique key(b,c)); 592insert into t1 select seq, seq, seq from seq_1_to_10; 593 594create table t2(a int, b int,c int); 595insert into t2 select seq, seq, seq+1 from seq_1_to_100; 596 597EXPLAIN SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b; 598SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b; 599 600alter table t1 drop PRIMARY KEY; 601alter table t1 add PRIMARY KEY(b,c); 602EXPLAIN SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b; 603SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b; 604 605drop table t1,t2; 606