1set optimizer_switch='index_condition_pushdown=on'; 2drop table if exists t1, t2, t3; 3CREATE TABLE t1 ( 4event_date date DEFAULT '0000-00-00' NOT NULL, 5type int(11) DEFAULT '0' NOT NULL, 6event_id int(11) DEFAULT '0' NOT NULL, 7PRIMARY KEY (event_date,type,event_id) 8); 9INSERT INTO t1 VALUES ('1999-07-10',100100,24), ('1999-07-11',100100,25), 10('1999-07-13',100600,0), ('1999-07-13',100600,4), ('1999-07-13',100600,26), 11('1999-07-14',100600,10), ('1999-07-15',100600,16), ('1999-07-15',100800,45), 12('1999-07-15',101000,47), ('1999-07-16',100800,46), ('1999-07-20',100600,5), 13('1999-07-20',100600,27), ('1999-07-21',100600,11), ('1999-07-22',100600,17), 14('1999-07-23',100100,39), ('1999-07-24',100100,39), ('1999-07-24',100500,40), 15('1999-07-25',100100,39), ('1999-07-27',100600,1), ('1999-07-27',100600,6), 16('1999-07-27',100600,28), ('1999-07-28',100600,12), ('1999-07-29',100500,41), 17('1999-07-29',100600,18), ('1999-07-30',100500,41), ('1999-07-31',100500,41), 18('1999-08-01',100700,34), ('1999-08-03',100600,7), ('1999-08-03',100600,29), 19('1999-08-04',100600,13), ('1999-08-05',100500,42), ('1999-08-05',100600,19), 20('1999-08-06',100500,42), ('1999-08-07',100500,42), ('1999-08-08',100500,42), 21('1999-08-10',100600,2), ('1999-08-10',100600,9), ('1999-08-10',100600,30), 22('1999-08-11',100600,14), ('1999-08-12',100600,20), ('1999-08-17',100500,8), 23('1999-08-17',100600,31), ('1999-08-18',100600,15), ('1999-08-19',100600,22), 24('1999-08-24',100600,3), ('1999-08-24',100600,32), ('1999-08-27',100500,43), 25('1999-08-31',100600,33), ('1999-09-17',100100,37), ('1999-09-18',100100,37), 26('1999-09-19',100100,37), ('2000-12-18',100700,38); 27select event_date,type,event_id from t1 WHERE event_date >= "1999-07-01" AND event_date < "1999-07-15" AND (type=100600 OR type=100100) ORDER BY event_date; 28event_date type event_id 291999-07-10 100100 24 301999-07-11 100100 25 311999-07-13 100600 0 321999-07-13 100600 4 331999-07-13 100600 26 341999-07-14 100600 10 35explain select event_date,type,event_id from t1 WHERE type = 100601 and event_date >= "1999-07-01" AND event_date < "1999-07-15" AND (type=100600 OR type=100100) ORDER BY event_date; 36id select_type table type possible_keys key key_len ref rows Extra 371 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 38select event_date,type,event_id from t1 WHERE event_date >= "1999-07-01" AND event_date <= "1999-07-15" AND (type=100600 OR type=100100) or event_date >= "1999-07-01" AND event_date <= "1999-07-15" AND type=100099; 39event_date type event_id 401999-07-10 100100 24 411999-07-11 100100 25 421999-07-13 100600 0 431999-07-13 100600 4 441999-07-13 100600 26 451999-07-14 100600 10 461999-07-15 100600 16 47drop table t1; 48CREATE TABLE t1 ( 49PAPER_ID smallint(6) DEFAULT '0' NOT NULL, 50YEAR smallint(6) DEFAULT '0' NOT NULL, 51ISSUE smallint(6) DEFAULT '0' NOT NULL, 52CLOSED tinyint(4) DEFAULT '0' NOT NULL, 53ISS_DATE date DEFAULT '0000-00-00' NOT NULL, 54PRIMARY KEY (PAPER_ID,YEAR,ISSUE) 55); 56INSERT INTO t1 VALUES (3,1999,34,0,'1999-07-12'), (1,1999,111,0,'1999-03-23'), 57(1,1999,222,0,'1999-03-23'), (3,1999,33,0,'1999-07-12'), 58(3,1999,32,0,'1999-07-12'), (3,1999,31,0,'1999-07-12'), 59(3,1999,30,0,'1999-07-12'), (3,1999,29,0,'1999-07-12'), 60(3,1999,28,0,'1999-07-12'), (1,1999,40,1,'1999-05-01'), 61(1,1999,41,1,'1999-05-01'), (1,1999,42,1,'1999-05-01'), 62(1,1999,46,1,'1999-05-01'), (1,1999,47,1,'1999-05-01'), 63(1,1999,48,1,'1999-05-01'), (1,1999,49,1,'1999-05-01'), 64(1,1999,50,0,'1999-05-01'), (1,1999,51,0,'1999-05-01'), 65(1,1999,200,0,'1999-06-28'), (1,1999,52,0,'1999-06-28'), 66(1,1999,53,0,'1999-06-28'), (1,1999,54,0,'1999-06-28'), 67(1,1999,55,0,'1999-06-28'), (1,1999,56,0,'1999-07-01'), 68(1,1999,57,0,'1999-07-01'), (1,1999,58,0,'1999-07-01'), 69(1,1999,59,0,'1999-07-01'), (1,1999,60,0,'1999-07-01'), 70(3,1999,35,0,'1999-07-12'); 71select YEAR,ISSUE from t1 where PAPER_ID=3 and (YEAR>1999 or (YEAR=1999 and ISSUE>28)) order by YEAR,ISSUE; 72YEAR ISSUE 731999 29 741999 30 751999 31 761999 32 771999 33 781999 34 791999 35 80check table t1; 81Table Op Msg_type Msg_text 82test.t1 check status OK 83repair table t1; 84Table Op Msg_type Msg_text 85test.t1 repair status OK 86drop table t1; 87CREATE TABLE t1 ( 88id int(11) NOT NULL auto_increment, 89parent_id int(11) DEFAULT '0' NOT NULL, 90level tinyint(4) DEFAULT '0' NOT NULL, 91PRIMARY KEY (id), 92KEY parent_id (parent_id), 93KEY level (level) 94); 95INSERT INTO t1 VALUES (1,0,0), (3,1,1), (4,1,1), (8,2,2), (9,2,2), (17,3,2), 96(22,4,2), (24,4,2), (28,5,2), (29,5,2), (30,5,2), (31,6,2), (32,6,2), (33,6,2), 97(203,7,2), (202,7,2), (20,3,2), (157,0,0), (193,5,2), (40,7,2), (2,1,1), 98(15,2,2), (6,1,1), (34,6,2), (35,6,2), (16,3,2), (7,1,1), (36,7,2), (18,3,2), 99(26,5,2), (27,5,2), (183,4,2), (38,7,2), (25,5,2), (37,7,2), (21,4,2), 100(19,3,2), (5,1,1), (179,5,2); 101SELECT * FROM t1 WHERE level = 1 AND parent_id = 1; 102id parent_id level 1033 1 1 1044 1 1 1052 1 1 1066 1 1 1077 1 1 1085 1 1 109SELECT * FROM t1 WHERE level = 1 AND parent_id = 1 order by id; 110id parent_id level 1112 1 1 1123 1 1 1134 1 1 1145 1 1 1156 1 1 1167 1 1 117drop table t1; 118create table t1( 119Satellite varchar(25) not null, 120SensorMode varchar(25) not null, 121FullImageCornersUpperLeftLongitude double not null, 122FullImageCornersUpperRightLongitude double not null, 123FullImageCornersUpperRightLatitude double not null, 124FullImageCornersLowerRightLatitude double not null, 125index two (Satellite, SensorMode, FullImageCornersUpperLeftLongitude, FullImageCornersUpperRightLongitude, FullImageCornersUpperRightLatitude, FullImageCornersLowerRightLatitude)); 126insert into t1 values("OV-3","PAN1",91,-92,40,50); 127insert into t1 values("OV-4","PAN1",91,-92,40,50); 128select * from t1 where t1.Satellite = "OV-3" and t1.SensorMode = "PAN1" and t1.FullImageCornersUpperLeftLongitude > -90.000000 and t1.FullImageCornersUpperRightLongitude < -82.000000; 129Satellite SensorMode FullImageCornersUpperLeftLongitude FullImageCornersUpperRightLongitude FullImageCornersUpperRightLatitude FullImageCornersLowerRightLatitude 130OV-3 PAN1 91 -92 40 50 131drop table t1; 132create table t1 ( aString char(100) not null default "", key aString (aString(10)) ); 133insert t1 (aString) values ( "believe in myself" ), ( "believe" ), ("baaa" ), ( "believe in love"); 134select * from t1 where aString < "believe in myself" order by aString; 135aString 136baaa 137believe 138believe in love 139select * from t1 where aString > "believe in love" order by aString; 140aString 141believe in myself 142alter table t1 drop key aString; 143select * from t1 where aString < "believe in myself" order by aString; 144aString 145baaa 146believe 147believe in love 148select * from t1 where aString > "believe in love" order by aString; 149aString 150believe in myself 151drop table t1; 152CREATE TABLE t1 ( 153t1ID int(10) unsigned NOT NULL auto_increment, 154art binary(1) NOT NULL default '', 155KNR char(5) NOT NULL default '', 156RECHNR char(6) NOT NULL default '', 157POSNR char(2) NOT NULL default '', 158ARTNR char(10) NOT NULL default '', 159TEX char(70) NOT NULL default '', 160PRIMARY KEY (t1ID), 161KEY IdxArt (art), 162KEY IdxKnr (KNR), 163KEY IdxArtnr (ARTNR) 164) ENGINE=MyISAM; 165INSERT INTO t1 (art) VALUES ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), 166('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), 167('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), 168('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), 169('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), 170('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), 171('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), 172('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), 173('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), 174('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), 175('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), 176('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), 177('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), 178('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), 179('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), 180('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), 181('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), 182('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), 183('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), 184('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), 185('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), 186('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), 187('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), 188('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), 189('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), 190('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), 191('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), 192('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'), 193('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'), 194('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'), 195('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'), 196('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'), 197('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'), 198('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'), 199('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'), 200('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'), 201('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'), 202('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'); 203select count(*) from t1 where upper(art) = 'J'; 204count(*) 205213 206select count(*) from t1 where art = 'J' or art = 'j'; 207count(*) 208602 209select count(*) from t1 where art = 'j' or art = 'J'; 210count(*) 211602 212select count(*) from t1 where art = 'j'; 213count(*) 214389 215select count(*) from t1 where art = 'J'; 216count(*) 217213 218drop table t1; 219create table t1 (x int, y int, index(x), index(y)); 220insert into t1 (x) values (1),(2),(3),(4),(5),(6),(7),(8),(9); 221update t1 set y=x; 222explain select * from t1, t1 t2 where t1.y = 8 and t2.x between 7 and t1.y+0; 223id select_type table type possible_keys key key_len ref rows Extra 2241 SIMPLE t1 ref y y 5 const 1 NULL 2251 SIMPLE t2 range x x 5 NULL 2 Using index condition; Using join buffer (Block Nested Loop) 226explain select * from t1, t1 t2 where t1.y = 8 and t2.x >= 7 and t2.x <= t1.y+0; 227id select_type table type possible_keys key key_len ref rows Extra 2281 SIMPLE t1 ref y y 5 const 1 NULL 2291 SIMPLE t2 range x x 5 NULL 2 Using index condition; Using join buffer (Block Nested Loop) 230explain select * from t1, t1 t2 where t1.y = 2 and t2.x between t1.y-1 and t1.y+1; 231id select_type table type possible_keys key key_len ref rows Extra 2321 SIMPLE t1 ref y y 5 const 1 NULL 2331 SIMPLE t2 range x x 5 NULL 3 Using index condition; Using join buffer (Block Nested Loop) 234explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= t1.y-1 and t2.x <= t1.y+1; 235id select_type table type possible_keys key key_len ref rows Extra 2361 SIMPLE t1 ref y y 5 const 1 NULL 2371 SIMPLE t2 range x x 5 NULL 3 Using index condition; Using join buffer (Block Nested Loop) 238explain select * from t1, t1 t2 where t1.y = 2 and t2.x between 0 and t1.y; 239id select_type table type possible_keys key key_len ref rows Extra 2401 SIMPLE t1 ref y y 5 const 1 NULL 2411 SIMPLE t2 range x x 5 NULL 2 Using index condition; Using join buffer (Block Nested Loop) 242explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= 0 and t2.x <= t1.y; 243id select_type table type possible_keys key key_len ref rows Extra 2441 SIMPLE t1 ref y y 5 const 1 NULL 2451 SIMPLE t2 range x x 5 NULL 2 Using index condition; Using join buffer (Block Nested Loop) 246explain select count(*) from t1 where x in (1); 247id select_type table type possible_keys key key_len ref rows Extra 2481 SIMPLE t1 ref x x 5 const 1 Using index 249explain select count(*) from t1 where x in (1,2); 250id select_type table type possible_keys key key_len ref rows Extra 2511 SIMPLE t1 index x x 5 NULL 9 Using where; Using index 252drop table t1; 253CREATE TABLE t1 (key1 int(11) NOT NULL default '0', KEY i1 (key1)); 254INSERT INTO t1 VALUES (0),(0),(0),(0),(0),(1),(1); 255CREATE TABLE t2 (keya int(11) NOT NULL default '0', KEY j1 (keya)); 256INSERT INTO t2 VALUES (0),(0),(1),(1),(2),(2); 257explain select * from t1, t2 where (t1.key1 <t2.keya + 1) and t2.keya=3; 258id select_type table type possible_keys key key_len ref rows Extra 2591 SIMPLE t2 ref j1 j1 4 const 1 Using index 2601 SIMPLE t1 index i1 i1 4 NULL 7 Using where; Using index; Using join buffer (Block Nested Loop) 261explain select * from t1 force index(i1), t2 force index(j1) where 262(t1.key1 <t2.keya + 1) and t2.keya=3; 263id select_type table type possible_keys key key_len ref rows Extra 2641 SIMPLE t2 ref j1 j1 4 const 1 Using index 2651 SIMPLE t1 index i1 i1 4 NULL 7 Using where; Using index; Using join buffer (Block Nested Loop) 266DROP TABLE t1,t2; 267CREATE TABLE t1 ( 268a int(11) default NULL, 269b int(11) default NULL, 270KEY a (a), 271KEY b (b) 272) ENGINE=MyISAM; 273INSERT INTO t1 VALUES 274(1,1),(2,1),(3,1),(4,1),(5,1),(6,1),(7,1),(8,1),(9,1),(10,2),(10,2), 275(13,2),(14,2),(15,2),(16,2),(17,3),(17,3),(16,3),(17,3),(19,3),(20,3), 276(21,4),(22,5),(23,5),(24,5),(25,5),(26,5),(30,5),(31,5),(32,5),(33,5), 277(33,5),(33,5),(33,5),(33,5),(34,5),(35,5); 278EXPLAIN SELECT * FROM t1 WHERE a IN(1,2) AND b=5; 279id select_type table type possible_keys key key_len ref rows Extra 2801 SIMPLE t1 range a,b a 5 NULL 2 Using index condition; Using where 281SELECT * FROM t1 WHERE a IN(1,2) AND b=5; 282a b 283DROP TABLE t1; 284CREATE TABLE t1 (a int, b int, c int, INDEX (c,a,b)); 285INSERT INTO t1 VALUES (1,0,0),(1,0,0),(1,0,0); 286INSERT INTO t1 VALUES (0,1,0),(0,1,0),(0,1,0); 287SELECT COUNT(*) FROM t1 WHERE (c=0 and a=1) or (c=0 and b=1); 288COUNT(*) 2896 290SELECT COUNT(*) FROM t1 WHERE (c=0 and b=1) or (c=0 and a=1); 291COUNT(*) 2926 293DROP TABLE t1; 294CREATE TABLE t1 ( a int not null, b int not null, INDEX ab(a,b) ); 295INSERT INTO t1 VALUES (47,1), (70,1), (15,1), (15, 4); 296SELECT * FROM t1 297WHERE 298( 299( b =1 AND a BETWEEN 14 AND 21 ) OR 300( b =2 AND a BETWEEN 16 AND 18 ) OR 301( b =3 AND a BETWEEN 15 AND 19 ) OR 302(a BETWEEN 19 AND 47) 303); 304a b 30515 1 30647 1 307DROP TABLE t1; 308CREATE TABLE t1 ( 309id int( 11 ) unsigned NOT NULL AUTO_INCREMENT , 310line int( 5 ) unsigned NOT NULL default '0', 311columnid int( 3 ) unsigned NOT NULL default '0', 312owner int( 3 ) unsigned NOT NULL default '0', 313ordinal int( 3 ) unsigned NOT NULL default '0', 314showid smallint( 6 ) unsigned NOT NULL default '1', 315tableid int( 1 ) unsigned NOT NULL default '1', 316content int( 5 ) unsigned NOT NULL default '188', 317PRIMARY KEY ( owner, id ) , 318KEY menu( owner, showid, columnid ) , 319KEY `COLUMN` ( owner, columnid, line ) , 320KEY `LINES` ( owner, tableid, content, id ) , 321KEY recount( owner, line ) 322) ENGINE = MYISAM; 323INSERT into t1 (owner,id,columnid,line) values (11,15,15,1),(11,13,13,5); 324SELECT id, columnid, tableid, content, showid, line, ordinal FROM t1 WHERE owner=11 AND ((columnid IN ( 15, 13, 14 ) AND line IN ( 1, 2, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 31 )) OR (columnid IN ( 13, 14 ) AND line IN ( 15 ))) LIMIT 0 , 30; 325id columnid tableid content showid line ordinal 32613 13 1 188 1 5 0 32715 15 1 188 1 1 0 328drop table t1; 329create table t1 (id int(10) primary key); 330insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9); 331select id from t1 where id in (2,5,9) ; 332id 3332 3345 3359 336select id from t1 where id=2 or id=5 or id=9 ; 337id 3382 3395 3409 341drop table t1; 342create table t1 ( id1 int not null, id2 int not null, idnull int null, c char(20), primary key (id1,id2)); 343insert into t1 values (0,1,NULL,"aaa"), (1,1,NULL,"aaa"), (2,1,NULL,"aaa"), 344(3,1,NULL,"aaa"), (4,1,NULL,"aaa"), (5,1,NULL,"aaa"), 345(6,1,NULL,"aaa"), (7,1,NULL,"aaa"), (8,1,NULL,"aaa"), 346(9,1,NULL,"aaa"), (10,1,NULL,"aaa"), (11,1,NULL,"aaa"), 347(12,1,NULL,"aaa"), (13,1,NULL,"aaa"), (14,1,NULL,"aaa"), 348(15,1,NULL,"aaa"), (16,1,NULL,"aaa"), (17,1,NULL,"aaa"), 349(18,1,NULL,"aaa"), (19,1,NULL,"aaa"), (20,1,NULL,"aaa"); 350select a.id1, b.idnull from t1 as a, t1 as b where a.id2=1 and a.id1=1 and b.id1=a.idnull order by b.id2 desc limit 1; 351id1 idnull 352drop table t1; 353create table t1 ( 354id int not null auto_increment, 355name char(1) not null, 356uid int not null, 357primary key (id), 358index uid_index (uid)); 359create table t2 ( 360id int not null auto_increment, 361name char(1) not null, 362uid int not null, 363primary key (id), 364index uid_index (uid)); 365insert into t1(id, uid, name) values(1, 0, ' '); 366insert into t1(uid, name) values(0, ' '); 367insert into t2(uid, name) select uid, name from t1; 368insert into t1(uid, name) select uid, name from t2; 369insert into t2(uid, name) select uid, name from t1; 370insert into t1(uid, name) select uid, name from t2; 371insert into t2(uid, name) select uid, name from t1; 372insert into t1(uid, name) select uid, name from t2; 373insert into t2(uid, name) select uid, name from t1; 374insert into t1(uid, name) select uid, name from t2; 375insert into t2(uid, name) select uid, name from t1; 376insert into t1(uid, name) select uid, name from t2; 377insert into t2(uid, name) select uid, name from t1; 378insert into t2(uid, name) select uid, name from t1; 379insert into t2(uid, name) select uid, name from t1; 380insert into t2(uid, name) select uid, name from t1; 381insert into t1(uid, name) select uid, name from t2; 382delete from t2; 383insert into t2(uid, name) values 384(1, CHAR(64+1)), 385(2, CHAR(64+2)), 386(3, CHAR(64+3)), 387(4, CHAR(64+4)), 388(5, CHAR(64+5)), 389(6, CHAR(64+6)), 390(7, CHAR(64+7)), 391(8, CHAR(64+8)), 392(9, CHAR(64+9)), 393(10, CHAR(64+10)), 394(11, CHAR(64+11)), 395(12, CHAR(64+12)), 396(13, CHAR(64+13)), 397(14, CHAR(64+14)), 398(15, CHAR(64+15)), 399(16, CHAR(64+16)), 400(17, CHAR(64+17)), 401(18, CHAR(64+18)), 402(19, CHAR(64+19)), 403(20, CHAR(64+20)), 404(21, CHAR(64+21)), 405(22, CHAR(64+22)), 406(23, CHAR(64+23)), 407(24, CHAR(64+24)), 408(25, CHAR(64+25)), 409(26, CHAR(64+26)); 410insert into t1(uid, name) select uid, name from t2 order by uid; 411delete from t2; 412insert into t2(id, uid, name) select id, uid, name from t1; 413select count(*) from t1; 414count(*) 4151026 416select count(*) from t2; 417count(*) 4181026 419analyze table t1,t2; 420Table Op Msg_type Msg_text 421test.t1 analyze status OK 422test.t2 analyze status Table is already up to date 423explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0; 424id select_type table type possible_keys key key_len ref rows Extra 4251 SIMPLE t1 range uid_index uid_index 4 NULL 112 Using index condition 4261 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 38 NULL 427explain select * from t1, t2 where t1.uid=t2.uid AND t2.uid > 0; 428id select_type table type possible_keys key key_len ref rows Extra 4291 SIMPLE t1 range uid_index uid_index 4 NULL 112 Using index condition 4301 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 38 NULL 431explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid != 0; 432id select_type table type possible_keys key key_len ref rows Extra 4331 SIMPLE t1 range uid_index uid_index 4 NULL 113 Using index condition 4341 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 38 NULL 435explain select * from t1, t2 where t1.uid=t2.uid AND t2.uid != 0; 436id select_type table type possible_keys key key_len ref rows Extra 4371 SIMPLE t1 range uid_index uid_index 4 NULL 113 Using index condition 4381 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 38 NULL 439select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0; 440id name uid id name uid 4411001 A 1 1001 A 1 4421002 B 2 1002 B 2 4431003 C 3 1003 C 3 4441004 D 4 1004 D 4 4451005 E 5 1005 E 5 4461006 F 6 1006 F 6 4471007 G 7 1007 G 7 4481008 H 8 1008 H 8 4491009 I 9 1009 I 9 4501010 J 10 1010 J 10 4511011 K 11 1011 K 11 4521012 L 12 1012 L 12 4531013 M 13 1013 M 13 4541014 N 14 1014 N 14 4551015 O 15 1015 O 15 4561016 P 16 1016 P 16 4571017 Q 17 1017 Q 17 4581018 R 18 1018 R 18 4591019 S 19 1019 S 19 4601020 T 20 1020 T 20 4611021 U 21 1021 U 21 4621022 V 22 1022 V 22 4631023 W 23 1023 W 23 4641024 X 24 1024 X 24 4651025 Y 25 1025 Y 25 4661026 Z 26 1026 Z 26 467select * from t1, t2 where t1.uid=t2.uid AND t1.uid != 0; 468id name uid id name uid 4691001 A 1 1001 A 1 4701002 B 2 1002 B 2 4711003 C 3 1003 C 3 4721004 D 4 1004 D 4 4731005 E 5 1005 E 5 4741006 F 6 1006 F 6 4751007 G 7 1007 G 7 4761008 H 8 1008 H 8 4771009 I 9 1009 I 9 4781010 J 10 1010 J 10 4791011 K 11 1011 K 11 4801012 L 12 1012 L 12 4811013 M 13 1013 M 13 4821014 N 14 1014 N 14 4831015 O 15 1015 O 15 4841016 P 16 1016 P 16 4851017 Q 17 1017 Q 17 4861018 R 18 1018 R 18 4871019 S 19 1019 S 19 4881020 T 20 1020 T 20 4891021 U 21 1021 U 21 4901022 V 22 1022 V 22 4911023 W 23 1023 W 23 4921024 X 24 1024 X 24 4931025 Y 25 1025 Y 25 4941026 Z 26 1026 Z 26 495drop table t1,t2; 496create table t1 (x bigint unsigned not null); 497insert into t1(x) values (0xfffffffffffffff0); 498insert into t1(x) values (0xfffffffffffffff1); 499select * from t1; 500x 50118446744073709551600 50218446744073709551601 503select count(*) from t1 where x>0; 504count(*) 5052 506select count(*) from t1 where x=0; 507count(*) 5080 509select count(*) from t1 where x<0; 510count(*) 5110 512select count(*) from t1 where x < -16; 513count(*) 5140 515select count(*) from t1 where x = -16; 516count(*) 5170 518select count(*) from t1 where x > -16; 519count(*) 5202 521select count(*) from t1 where x = 18446744073709551601; 522count(*) 5231 524create table t2 (x bigint not null); 525insert into t2(x) values (-16); 526insert into t2(x) values (-15); 527select * from t2; 528x 529-16 530-15 531select count(*) from t2 where x>0; 532count(*) 5330 534select count(*) from t2 where x=0; 535count(*) 5360 537select count(*) from t2 where x<0; 538count(*) 5392 540select count(*) from t2 where x < -16; 541count(*) 5420 543select count(*) from t2 where x = -16; 544count(*) 5451 546select count(*) from t2 where x > -16; 547count(*) 5481 549select count(*) from t2 where x = 18446744073709551601; 550count(*) 5510 552drop table t1,t2; 553create table t1 (x bigint unsigned not null primary key) engine=innodb; 554insert into t1(x) values (0xfffffffffffffff0); 555insert into t1(x) values (0xfffffffffffffff1); 556select * from t1; 557x 55818446744073709551600 55918446744073709551601 560select count(*) from t1 where x>0; 561count(*) 5622 563select count(*) from t1 where x=0; 564count(*) 5650 566select count(*) from t1 where x<0; 567count(*) 5680 569select count(*) from t1 where x < -16; 570count(*) 5710 572select count(*) from t1 where x = -16; 573count(*) 5740 575select count(*) from t1 where x > -16; 576count(*) 5772 578select count(*) from t1 where x = 18446744073709551601; 579count(*) 5801 581drop table t1; 582create table t1 (a bigint unsigned); 583create index t1i on t1(a); 584insert into t1 select 18446744073709551615; 585insert into t1 select 18446744073709551614; 586explain select * from t1 where a <> -1; 587id select_type table type possible_keys key key_len ref rows Extra 5881 SIMPLE t1 index t1i t1i 9 NULL 2 Using where; Using index 589select * from t1 where a <> -1; 590a 59118446744073709551614 59218446744073709551615 593explain select * from t1 where a > -1 or a < -1; 594id select_type table type possible_keys key key_len ref rows Extra 5951 SIMPLE t1 index t1i t1i 9 NULL 2 Using where; Using index 596select * from t1 where a > -1 or a < -1; 597a 59818446744073709551614 59918446744073709551615 600explain select * from t1 where a > -1; 601id select_type table type possible_keys key key_len ref rows Extra 6021 SIMPLE t1 index t1i t1i 9 NULL 2 Using where; Using index 603select * from t1 where a > -1; 604a 60518446744073709551614 60618446744073709551615 607explain select * from t1 where a < -1; 608id select_type table type possible_keys key key_len ref rows Extra 6091 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 610select * from t1 where a < -1; 611a 612drop table t1; 613set names latin1; 614create table t1 (a char(10), b text, key (a)) character set latin1; 615INSERT INTO t1 (a) VALUES 616('111'),('222'),('222'),('222'),('222'),('444'),('aaa'),('AAA'),('bbb'); 617explain select * from t1 where a='aaa'; 618id select_type table type possible_keys key key_len ref rows Extra 6191 SIMPLE t1 ref a a 11 const 2 Using index condition 620explain select * from t1 where a=binary 'aaa'; 621id select_type table type possible_keys key key_len ref rows Extra 6221 SIMPLE t1 range a a 11 NULL 2 Using index condition 623explain select * from t1 where a='aaa' collate latin1_bin; 624id select_type table type possible_keys key key_len ref rows Extra 6251 SIMPLE t1 range a a 11 NULL 2 Using index condition 626explain select * from t1 where a='aaa' collate latin1_german1_ci; 627id select_type table type possible_keys key key_len ref rows Extra 6281 SIMPLE t1 ALL a NULL NULL NULL 9 Using where 629drop table t1; 630CREATE TABLE t1 ( 631`CLIENT` char(3) character set latin1 collate latin1_bin NOT NULL default '000', 632`ARG1` char(3) character set latin1 collate latin1_bin NOT NULL default '', 633`ARG2` char(3) character set latin1 collate latin1_bin NOT NULL default '', 634`FUNCTION` varchar(10) character set latin1 collate latin1_bin NOT NULL default '', 635`FUNCTINT` int(11) NOT NULL default '0', 636KEY `VERI_CLNT~2` (`ARG1`) 637) ENGINE=InnoDB DEFAULT CHARSET=latin1; 638INSERT INTO t1 VALUES ('000',' 0',' 0','Text 001',0), ('000',' 0',' 1','Text 002',0), 639('000',' 1',' 2','Text 003',0), ('000',' 2',' 3','Text 004',0), 640('001',' 3',' 0','Text 017',0); 641SELECT count(*) FROM t1 WHERE CLIENT='000' AND (ARG1 != ' 1' OR ARG1 != ' 2'); 642count(*) 6434 644SELECT count(*) FROM t1 WHERE CLIENT='000' AND (ARG1 != ' 2' OR ARG1 != ' 1'); 645count(*) 6464 647drop table t1; 648create table t1 (a int); 649insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 650CREATE TABLE t2 ( 651pk1 int(11) NOT NULL, 652pk2 int(11) NOT NULL, 653pk3 int(11) NOT NULL, 654pk4 int(11) NOT NULL, 655filler char(82), 656PRIMARY KEY (pk1,pk2,pk3,pk4) 657) DEFAULT CHARSET=latin1; 658insert into t2 select 1, A.a+10*B.a, 432, 44, 'fillerZ' from t1 A, t1 B; 659INSERT INTO t2 VALUES (2621, 2635, 0, 0,'filler'), (2621, 2635, 1, 0,'filler'), 660(2621, 2635, 10, 0,'filler'), (2621, 2635, 11, 0,'filler'), 661(2621, 2635, 14, 0,'filler'), (2621, 2635, 1000015, 0,'filler'); 662SELECT * FROM t2 663WHERE ((((pk4 =0) AND (pk1 =2621) AND (pk2 =2635))) 664OR ((pk4 =1) AND (((pk1 IN ( 7, 2, 1 ))) OR (pk1 =522)) AND ((pk2 IN ( 0, 2635)))) 665) AND (pk3 >=1000000); 666pk1 pk2 pk3 pk4 filler 6672621 2635 1000015 0 filler 668drop table t1, t2; 669create table t1(a char(2), key(a(1))); 670insert into t1 values ('x'), ('xx'); 671explain select a from t1 where a > 'x'; 672id select_type table type possible_keys key key_len ref rows Extra 6731 SIMPLE t1 range a a 2 NULL 2 Using where 674select a from t1 where a > 'x'; 675a 676xx 677drop table t1; 678CREATE TABLE t1 ( 679OXID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 680OXPARENTID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT 'oxrootid', 681OXLEFT int NOT NULL DEFAULT '0', 682OXRIGHT int NOT NULL DEFAULT '0', 683OXROOTID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 684PRIMARY KEY (OXID), 685KEY OXNID (OXID), 686KEY OXLEFT (OXLEFT), 687KEY OXRIGHT (OXRIGHT), 688KEY OXROOTID (OXROOTID) 689) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci; 690INSERT INTO t1 VALUES 691('d8c4177d09f8b11f5.52725521','oxrootid',1,40,'d8c4177d09f8b11f5.52725521'), 692('d8c4177d151affab2.81582770','d8c4177d09f8b11f5.52725521',2,3, 693'd8c4177d09f8b11f5.52725521'), 694('d8c4177d206a333d2.74422679','d8c4177d09f8b11f5.52725521',4,5, 695'd8c4177d09f8b11f5.52725521'), 696('d8c4177d225791924.30714720','d8c4177d09f8b11f5.52725521',6,7, 697'd8c4177d09f8b11f5.52725521'), 698('d8c4177d2380fc201.39666693','d8c4177d09f8b11f5.52725521',8,9, 699'd8c4177d09f8b11f5.52725521'), 700('d8c4177d24ccef970.14957924','d8c4177d09f8b11f5.52725521',10,11, 701'd8c4177d09f8b11f5.52725521'); 702EXPLAIN 703SELECT s.oxid FROM t1 v, t1 s 704WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND 705v.oxrootid ='d8c4177d09f8b11f5.52725521' AND 706s.oxleft > v.oxleft AND s.oxleft < v.oxright; 707id select_type table type possible_keys key key_len ref rows Extra 7081 SIMPLE v ref OXLEFT,OXRIGHT,OXROOTID OXROOTID 34 const 5 Using index condition 7091 SIMPLE s ALL OXLEFT NULL NULL NULL 6 Range checked for each record (index map: 0x4) 710SELECT s.oxid FROM t1 v, t1 s 711WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND 712v.oxrootid ='d8c4177d09f8b11f5.52725521' AND 713s.oxleft > v.oxleft AND s.oxleft < v.oxright; 714oxid 715d8c4177d151affab2.81582770 716d8c4177d206a333d2.74422679 717d8c4177d225791924.30714720 718d8c4177d2380fc201.39666693 719d8c4177d24ccef970.14957924 720DROP TABLE t1; 721create table t1 ( 722c1 char(10), c2 char(10), c3 char(10), c4 char(10), 723c5 char(10), c6 char(10), c7 char(10), c8 char(10), 724c9 char(10), c10 char(10), c11 char(10), c12 char(10), 725c13 char(10), c14 char(10), c15 char(10), c16 char(10), 726index(c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12,c13,c14,c15,c16) 727); 728insert into t1 (c1) values ('1'),('1'),('1'),('1'); 729select * from t1 where 730c1 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 731"abcdefg1", "123456781", "qwertyui1", "asddfg1", 732"abcdefg2", "123456782", "qwertyui2", "asddfg2", 733"abcdefg3", "123456783", "qwertyui3", "asddfg3", 734"abcdefg4", "123456784", "qwertyui4", "asddfg4", 735"abcdefg5", "123456785", "qwertyui5", "asddfg5", 736"abcdefg6", "123456786", "qwertyui6", "asddfg6", 737"abcdefg7", "123456787", "qwertyui7", "asddfg7", 738"abcdefg8", "123456788", "qwertyui8", "asddfg8", 739"abcdefg9", "123456789", "qwertyui9", "asddfg9", 740"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", 741"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", 742"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") 743and c2 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 744"abcdefg1", "123456781", "qwertyui1", "asddfg1", 745"abcdefg2", "123456782", "qwertyui2", "asddfg2", 746"abcdefg3", "123456783", "qwertyui3", "asddfg3", 747"abcdefg4", "123456784", "qwertyui4", "asddfg4", 748"abcdefg5", "123456785", "qwertyui5", "asddfg5", 749"abcdefg6", "123456786", "qwertyui6", "asddfg6", 750"abcdefg7", "123456787", "qwertyui7", "asddfg7", 751"abcdefg8", "123456788", "qwertyui8", "asddfg8", 752"abcdefg9", "123456789", "qwertyui9", "asddfg9", 753"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", 754"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", 755"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") 756and c3 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 757"abcdefg1", "123456781", "qwertyui1", "asddfg1", 758"abcdefg2", "123456782", "qwertyui2", "asddfg2", 759"abcdefg3", "123456783", "qwertyui3", "asddfg3", 760"abcdefg4", "123456784", "qwertyui4", "asddfg4", 761"abcdefg5", "123456785", "qwertyui5", "asddfg5", 762"abcdefg6", "123456786", "qwertyui6", "asddfg6", 763"abcdefg7", "123456787", "qwertyui7", "asddfg7", 764"abcdefg8", "123456788", "qwertyui8", "asddfg8", 765"abcdefg9", "123456789", "qwertyui9", "asddfg9", 766"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", 767"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", 768"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") 769and c4 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 770"abcdefg1", "123456781", "qwertyui1", "asddfg1", 771"abcdefg2", "123456782", "qwertyui2", "asddfg2", 772"abcdefg3", "123456783", "qwertyui3", "asddfg3", 773"abcdefg4", "123456784", "qwertyui4", "asddfg4", 774"abcdefg5", "123456785", "qwertyui5", "asddfg5", 775"abcdefg6", "123456786", "qwertyui6", "asddfg6", 776"abcdefg7", "123456787", "qwertyui7", "asddfg7", 777"abcdefg8", "123456788", "qwertyui8", "asddfg8", 778"abcdefg9", "123456789", "qwertyui9", "asddfg9", 779"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", 780"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", 781"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") 782and c5 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 783"abcdefg1", "123456781", "qwertyui1", "asddfg1", 784"abcdefg2", "123456782", "qwertyui2", "asddfg2", 785"abcdefg3", "123456783", "qwertyui3", "asddfg3", 786"abcdefg4", "123456784", "qwertyui4", "asddfg4", 787"abcdefg5", "123456785", "qwertyui5", "asddfg5", 788"abcdefg6", "123456786", "qwertyui6", "asddfg6", 789"abcdefg7", "123456787", "qwertyui7", "asddfg7", 790"abcdefg8", "123456788", "qwertyui8", "asddfg8", 791"abcdefg9", "123456789", "qwertyui9", "asddfg9", 792"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", 793"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", 794"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") 795and c6 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 796"abcdefg1", "123456781", "qwertyui1", "asddfg1", 797"abcdefg2", "123456782", "qwertyui2", "asddfg2", 798"abcdefg3", "123456783", "qwertyui3", "asddfg3", 799"abcdefg4", "123456784", "qwertyui4", "asddfg4", 800"abcdefg5", "123456785", "qwertyui5", "asddfg5", 801"abcdefg6", "123456786", "qwertyui6", "asddfg6", 802"abcdefg7", "123456787", "qwertyui7", "asddfg7", 803"abcdefg8", "123456788", "qwertyui8", "asddfg8", 804"abcdefg9", "123456789", "qwertyui9", "asddfg9", 805"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", 806"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", 807"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") 808and c7 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 809"abcdefg1", "123456781", "qwertyui1", "asddfg1", 810"abcdefg2", "123456782", "qwertyui2", "asddfg2", 811"abcdefg3", "123456783", "qwertyui3", "asddfg3", 812"abcdefg4", "123456784", "qwertyui4", "asddfg4", 813"abcdefg5", "123456785", "qwertyui5", "asddfg5", 814"abcdefg6", "123456786", "qwertyui6", "asddfg6", 815"abcdefg7", "123456787", "qwertyui7", "asddfg7", 816"abcdefg8", "123456788", "qwertyui8", "asddfg8", 817"abcdefg9", "123456789", "qwertyui9", "asddfg9", 818"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", 819"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", 820"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") 821and c8 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 822"abcdefg1", "123456781", "qwertyui1", "asddfg1", 823"abcdefg2", "123456782", "qwertyui2", "asddfg2", 824"abcdefg3", "123456783", "qwertyui3", "asddfg3", 825"abcdefg4", "123456784", "qwertyui4", "asddfg4", 826"abcdefg5", "123456785", "qwertyui5", "asddfg5", 827"abcdefg6", "123456786", "qwertyui6", "asddfg6", 828"abcdefg7", "123456787", "qwertyui7", "asddfg7", 829"abcdefg8", "123456788", "qwertyui8", "asddfg8", 830"abcdefg9", "123456789", "qwertyui9", "asddfg9", 831"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", 832"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", 833"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") 834and c9 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 835"abcdefg1", "123456781", "qwertyui1", "asddfg1", 836"abcdefg2", "123456782", "qwertyui2", "asddfg2", 837"abcdefg3", "123456783", "qwertyui3", "asddfg3", 838"abcdefg4", "123456784", "qwertyui4", "asddfg4", 839"abcdefg5", "123456785", "qwertyui5", "asddfg5", 840"abcdefg6", "123456786", "qwertyui6", "asddfg6", 841"abcdefg7", "123456787", "qwertyui7", "asddfg7", 842"abcdefg8", "123456788", "qwertyui8", "asddfg8", 843"abcdefg9", "123456789", "qwertyui9", "asddfg9", 844"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", 845"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", 846"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") 847and c10 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 848"abcdefg1", "123456781", "qwertyui1", "asddfg1", 849"abcdefg2", "123456782", "qwertyui2", "asddfg2", 850"abcdefg3", "123456783", "qwertyui3", "asddfg3", 851"abcdefg4", "123456784", "qwertyui4", "asddfg4", 852"abcdefg5", "123456785", "qwertyui5", "asddfg5", 853"abcdefg6", "123456786", "qwertyui6", "asddfg6", 854"abcdefg7", "123456787", "qwertyui7", "asddfg7", 855"abcdefg8", "123456788", "qwertyui8", "asddfg8", 856"abcdefg9", "123456789", "qwertyui9", "asddfg9", 857"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", 858"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", 859"abcdefgC", "12345678C", "qwertyuiC", "asddfgC"); 860c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 c13 c14 c15 c16 861drop table t1; 862End of 4.1 tests 863CREATE TABLE t1 ( 864id int(11) NOT NULL auto_increment, 865status varchar(20), 866PRIMARY KEY (id), 867KEY (status) 868); 869INSERT INTO t1 VALUES 870(1,'B'), (2,'B'), (3,'B'), (4,'B'), (5,'B'), (6,'B'), 871(7,'B'), (8,'B'), (9,'B'), (10,'B'), (11,'B'), (12,'B'), 872(13,'B'), (14,'B'), (15,'B'), (16,'B'), (17,'B'), (18,'B'), 873(19,'B'), (20,'B'), (21,'B'), (22,'B'), (23,'B'), (24,'B'), 874(25,'A'), (26,'A'), (27,'A'), (28,'A'), (29,'A'), (30,'A'), 875(31,'A'), (32,'A'), (33,'A'), (34,'A'), (35,'A'), (36,'A'), 876(37,'A'), (38,'A'), (39,'A'), (40,'A'), (41,'A'), (42,'A'), 877(43,'A'), (44,'A'), (45,'A'), (46,'A'), (47,'A'), (48,'A'), 878(49,'A'), (50,'A'), (51,'A'), (52,'A'), (53,'C'), (54,'C'), 879(55,'C'), (56,'C'), (57,'C'), (58,'C'), (59,'C'), (60,'C'); 880EXPLAIN SELECT * FROM t1 WHERE status <> 'A' AND status <> 'B'; 881id select_type table type possible_keys key key_len ref rows Extra 8821 SIMPLE t1 range status status 23 NULL 11 Using index condition 883EXPLAIN SELECT * FROM t1 WHERE status NOT IN ('A','B'); 884id select_type table type possible_keys key key_len ref rows Extra 8851 SIMPLE t1 range status status 23 NULL 11 Using index condition 886SELECT * FROM t1 WHERE status <> 'A' AND status <> 'B'; 887id status 88853 C 88954 C 89055 C 89156 C 89257 C 89358 C 89459 C 89560 C 896SELECT * FROM t1 WHERE status NOT IN ('A','B'); 897id status 89853 C 89954 C 90055 C 90156 C 90257 C 90358 C 90459 C 90560 C 906EXPLAIN SELECT status FROM t1 WHERE status <> 'A' AND status <> 'B'; 907id select_type table type possible_keys key key_len ref rows Extra 9081 SIMPLE t1 range status status 23 NULL 11 Using where; Using index 909EXPLAIN SELECT status FROM t1 WHERE status NOT IN ('A','B'); 910id select_type table type possible_keys key key_len ref rows Extra 9111 SIMPLE t1 range status status 23 NULL 11 Using where; Using index 912EXPLAIN SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B'; 913id select_type table type possible_keys key key_len ref rows Extra 9141 SIMPLE t1 range status status 23 NULL 10 Using index condition 915EXPLAIN SELECT * FROM t1 WHERE status < 'A' OR status > 'B'; 916id select_type table type possible_keys key key_len ref rows Extra 9171 SIMPLE t1 range status status 23 NULL 10 Using index condition 918SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B'; 919id status 92053 C 92154 C 92255 C 92356 C 92457 C 92558 C 92659 C 92760 C 928SELECT * FROM t1 WHERE status < 'A' OR status > 'B'; 929id status 93053 C 93154 C 93255 C 93356 C 93457 C 93558 C 93659 C 93760 C 938DROP TABLE t1; 939CREATE TABLE t1 (a int, b int, primary key(a,b)); 940INSERT INTO t1 VALUES 941(1,1),(1,2),(1,3),(2,1),(2,2),(2,3),(3,1),(3,2),(3,3),(4,1),(4,2),(4,3); 942CREATE VIEW v1 as SELECT a,b FROM t1 WHERE b=3; 943EXPLAIN SELECT a,b FROM t1 WHERE a < 2 and b=3; 944id select_type table type possible_keys key key_len ref rows Extra 9451 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where; Using index 946EXPLAIN SELECT a,b FROM v1 WHERE a < 2 and b=3; 947id select_type table type possible_keys key key_len ref rows Extra 9481 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where; Using index 949EXPLAIN SELECT a,b FROM t1 WHERE a < 2; 950id select_type table type possible_keys key key_len ref rows Extra 9511 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where; Using index 952EXPLAIN SELECT a,b FROM v1 WHERE a < 2; 953id select_type table type possible_keys key key_len ref rows Extra 9541 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where; Using index 955SELECT a,b FROM t1 WHERE a < 2 and b=3; 956a b 9571 3 958SELECT a,b FROM v1 WHERE a < 2 and b=3; 959a b 9601 3 961DROP VIEW v1; 962DROP TABLE t1; 963CREATE TABLE t1 (name varchar(15) NOT NULL, KEY idx(name)); 964INSERT INTO t1 VALUES ('Betty'), ('Anna'); 965SELECT * FROM t1; 966name 967Anna 968Betty 969DELETE FROM t1 WHERE name NOT LIKE 'A%a'; 970SELECT * FROM t1; 971name 972Anna 973DROP TABLE t1; 974CREATE TABLE t1 (a int, KEY idx(a)); 975INSERT INTO t1 VALUES (NULL), (1), (2), (3); 976SELECT * FROM t1; 977a 978NULL 9791 9802 9813 982DELETE FROM t1 WHERE NOT(a <=> 2); 983SELECT * FROM t1; 984a 9852 986DROP TABLE t1; 987create table t1 (a int, b int, primary key(a,b)); 988create view v1 as select a, b from t1; 989INSERT INTO `t1` VALUES 990(0,0),(1,0),(2,0),(3,0),(4,0),(5,1),(6,1),(7,1),(8,1),(9,1),(10,2),(11,2),(12,2) 991,(13,2),(14,2),(15,3),(16,3),(17,3),(18,3),(19,3); 992explain select * from t1 where a in (3,4) and b in (1,2,3); 993id select_type table type possible_keys key key_len ref rows Extra 9941 SIMPLE t1 range PRIMARY PRIMARY 8 NULL # Using where; Using index 995explain select * from v1 where a in (3,4) and b in (1,2,3); 996id select_type table type possible_keys key key_len ref rows Extra 9971 SIMPLE t1 range PRIMARY PRIMARY 8 NULL # Using where; Using index 998explain select * from t1 where a between 3 and 4 and b between 1 and 2; 999id select_type table type possible_keys key key_len ref rows Extra 10001 SIMPLE t1 range PRIMARY PRIMARY 8 NULL # Using where; Using index 1001explain select * from v1 where a between 3 and 4 and b between 1 and 2; 1002id select_type table type possible_keys key key_len ref rows Extra 10031 SIMPLE t1 range PRIMARY PRIMARY 8 NULL # Using where; Using index 1004drop view v1; 1005drop table t1; 1006create table t3 (a int); 1007insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1008create table t1 (a varchar(10), filler char(200), key(a)) charset=binary; 1009insert into t1 values ('a',''); 1010insert into t1 values ('a ',''); 1011insert into t1 values ('a ', ''); 1012insert into t1 select concat('a', 1000 + A.a + 10 * (B.a + 10 * C.a)), '' 1013 from t3 A, t3 B, t3 C; 1014create table t2 (a varchar(10), filler char(200), key(a)); 1015insert into t2 select * from t1; 1016explain select * from t1 where a between 'a' and 'a '; 1017id select_type table type possible_keys key key_len ref rows Extra 10181 SIMPLE t1 range a a 13 NULL # Using index condition 1019explain select * from t1 where a = 'a' or a='a '; 1020id select_type table type possible_keys key key_len ref rows Extra 10211 SIMPLE t1 range a a 13 NULL # Using index condition 1022explain select * from t2 where a between 'a' and 'a '; 1023id select_type table type possible_keys key key_len ref rows Extra 10241 SIMPLE t2 ref a a 13 const # Using index condition 1025explain select * from t2 where a = 'a' or a='a '; 1026id select_type table type possible_keys key key_len ref rows Extra 10271 SIMPLE t2 ref a a 13 const # Using index condition 1028update t1 set a='b' where a<>'a'; 1029explain select * from t1 where a not between 'b' and 'b'; 1030id select_type table type possible_keys key key_len ref rows Extra 10311 SIMPLE t1 range a a 13 NULL # Using index condition 1032select a, hex(filler) from t1 where a not between 'b' and 'b'; 1033a hex(filler) 1034a 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 1035drop table t1,t2,t3; 1036create table t1 (a int); 1037insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1038create table t2 (a int, key(a)); 1039insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C; 1040set @a="select * from t2 force index (a) where a NOT IN(0"; 1041select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z; 1042count(*) 10431000 1044set @a=concat(@a, ')'); 1045insert into t2 values (11),(13),(15); 1046set @b= concat("explain ", @a); 1047prepare stmt1 from @b; 1048execute stmt1; 1049id select_type table type possible_keys key key_len ref rows Extra 10501 SIMPLE t2 index a a 5 NULL 1003 Using where; Using index 1051prepare stmt1 from @a; 1052execute stmt1; 1053a 105411 105513 105615 1057drop table t1, t2; 1058CREATE TABLE t1 ( 1059id int NOT NULL DEFAULT '0', 1060b int NOT NULL DEFAULT '0', 1061c int NOT NULL DEFAULT '0', 1062INDEX idx1(b,c), INDEX idx2(c)); 1063INSERT INTO t1(id) VALUES (1), (2), (3), (4), (5), (6), (7), (8); 1064INSERT INTO t1(b,c) VALUES (3,4), (3,4); 1065SELECT * FROM t1 WHERE b<=3 AND 3<=c; 1066id b c 10670 3 4 10680 3 4 1069SELECT * FROM t1 WHERE 3 BETWEEN b AND c; 1070id b c 10710 3 4 10720 3 4 1073EXPLAIN SELECT * FROM t1 WHERE b<=3 AND 3<=c; 1074id select_type table type possible_keys key key_len ref rows Extra 10751 SIMPLE t1 range idx1,idx2 idx2 4 NULL 3 Using index condition; Using where 1076EXPLAIN SELECT * FROM t1 WHERE 3 BETWEEN b AND c; 1077id select_type table type possible_keys key key_len ref rows Extra 10781 SIMPLE t1 range idx1,idx2 idx2 4 NULL 3 Using where 1079SELECT * FROM t1 WHERE 0 < b OR 0 > c; 1080id b c 10810 3 4 10820 3 4 1083SELECT * FROM t1 WHERE 0 NOT BETWEEN b AND c; 1084id b c 10850 3 4 10860 3 4 1087EXPLAIN SELECT * FROM t1 WHERE 0 < b OR 0 > c; 1088id select_type table type possible_keys key key_len ref rows Extra 10891 SIMPLE t1 ALL idx1,idx2 NULL NULL NULL 10 Using where 1090EXPLAIN SELECT * FROM t1 WHERE 0 NOT BETWEEN b AND c; 1091id select_type table type possible_keys key key_len ref rows Extra 10921 SIMPLE t1 ALL idx1,idx2 NULL NULL NULL 10 Using where 1093DROP TABLE t1; 1094CREATE TABLE t1 ( 1095item char(20) NOT NULL default '', 1096started datetime NOT NULL default '0000-00-00 00:00:00', 1097price decimal(16,3) NOT NULL default '0.000', 1098PRIMARY KEY (item,started) 1099) ENGINE=MyISAM; 1100INSERT INTO t1 VALUES 1101('A1','2005-11-01 08:00:00',1000), 1102('A1','2005-11-15 00:00:00',2000), 1103('A1','2005-12-12 08:00:00',3000), 1104('A2','2005-12-01 08:00:00',1000); 1105EXPLAIN SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; 1106id select_type table type possible_keys key key_len ref rows Extra 11071 SIMPLE t1 ref PRIMARY PRIMARY 20 const 2 Using index condition 1108Warnings: 1109Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 1110Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 1111SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; 1112item started price 1113A1 2005-11-01 08:00:00 1000.000 1114A1 2005-11-15 00:00:00 2000.000 1115Warnings: 1116Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 1117Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 1118SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-02 00:00:00'; 1119item started price 1120A1 2005-11-01 08:00:00 1000.000 1121A1 2005-11-15 00:00:00 2000.000 1122DROP INDEX `PRIMARY` ON t1; 1123EXPLAIN SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; 1124id select_type table type possible_keys key key_len ref rows Extra 11251 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where 1126Warnings: 1127Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 1128SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; 1129item started price 1130A1 2005-11-01 08:00:00 1000.000 1131A1 2005-11-15 00:00:00 2000.000 1132Warnings: 1133Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 1134SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-02 00:00:00'; 1135item started price 1136A1 2005-11-01 08:00:00 1000.000 1137A1 2005-11-15 00:00:00 2000.000 1138DROP TABLE t1; 1139 1140BUG#32198 "Comparison of DATE with DATETIME still not using indexes correctly" 1141 1142CREATE TABLE t1 ( 1143id int(11) NOT NULL auto_increment, 1144dateval date default NULL, 1145PRIMARY KEY (id), 1146KEY dateval (dateval) 1147) AUTO_INCREMENT=173; 1148INSERT INTO t1 VALUES 1149(1,'2007-01-01'),(2,'2007-01-02'),(3,'2007-01-03'),(4,'2007-01-04'), 1150(5,'2007-01-05'),(6,'2007-01-06'),(7,'2007-01-07'),(8,'2007-01-08'), 1151(9,'2007-01-09'),(10,'2007-01-10'),(11,'2007-01-11'); 1152This must use range access: 1153explain select * from t1 where dateval >= '2007-01-01 00:00:00' and dateval <= '2007-01-02 23:59:59'; 1154id select_type table type possible_keys key key_len ref rows Extra 11551 SIMPLE t1 range dateval dateval 4 NULL 2 Using index condition 1156drop table t1; 1157CREATE TABLE t1 ( 1158a varchar(32), index (a) 1159) DEFAULT CHARSET=latin1 COLLATE=latin1_bin; 1160INSERT INTO t1 VALUES 1161('B'), ('A'), ('A'), ('C'), ('B'), ('A'), ('A'); 1162SELECT a FROM t1 WHERE a='b' OR a='B'; 1163a 1164B 1165B 1166EXPLAIN SELECT a FROM t1 WHERE a='b' OR a='B'; 1167id select_type table type possible_keys key key_len ref rows Extra 11681 SIMPLE t1 range a a 35 NULL 3 Using where; Using index 1169DROP TABLE t1; 1170CREATE TABLE t1 (f1 TINYINT(11) UNSIGNED NOT NULL, PRIMARY KEY (f1)); 1171INSERT INTO t1 VALUES (127),(254),(0),(1),(255); 1172SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 256; 1173COUNT(*) 11745 1175SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 256.0; 1176COUNT(*) 11775 1178SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 255; 1179COUNT(*) 11804 1181SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < -1; 1182COUNT(*) 11830 1184SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -1; 1185COUNT(*) 11865 1187DROP TABLE t1; 1188CREATE TABLE t1 ( f1 TINYINT(11) NOT NULL, PRIMARY KEY (f1)); 1189INSERT INTO t1 VALUES (127),(126),(0),(-128),(-127); 1190SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 128; 1191COUNT(*) 11925 1193SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 128.0; 1194COUNT(*) 11955 1196SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 127; 1197COUNT(*) 11984 1199SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -129; 1200COUNT(*) 12015 1202SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -129.0; 1203COUNT(*) 12045 1205SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -128; 1206COUNT(*) 12074 1208DROP TABLE t1; 1209create table t1 (a int); 1210insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1211create table t2 (a int, b int, filler char(100)); 1212insert into t2 select A.a + 10 * (B.a + 10 * C.a), 10, 'filler' from t1 A, 1213t1 B, t1 C where A.a < 5; 1214insert into t2 select 1000, b, 'filler' from t2; 1215alter table t2 add index (a,b); 1216select 'In following EXPLAIN the access method should be ref or range, #rows~=500 (and not 2)' Z; 1217Z 1218In following EXPLAIN the access method should be ref or range, #rows~=500 (and not 2) 1219explain select * from t2 where a=1000 and b<11; 1220id select_type table type possible_keys key key_len ref rows Extra 12211 SIMPLE t2 ref a a 5 const 502 Using index condition 1222drop table t1, t2; 1223CREATE TABLE t1( a INT, b INT, KEY( a, b ) ); 1224CREATE TABLE t2( a INT, b INT, KEY( a, b ) ); 1225CREATE TABLE t3( a INT, b INT, KEY( a, b ) ); 1226INSERT INTO t1( a, b ) 1227VALUES (0, 1), (1, 2), (1, 4), (2, 3), (5, 0), (9, 7); 1228INSERT INTO t2( a, b ) 1229VALUES ( 1, 1), ( 2, 1), ( 3, 1), ( 4, 1), ( 5, 1), 1230( 6, 1), ( 7, 1), ( 8, 1), ( 9, 1), (10, 1), 1231(11, 1), (12, 1), (13, 1), (14, 1), (15, 1), 1232(16, 1), (17, 1), (18, 1), (19, 1), (20, 1); 1233INSERT INTO t2 SELECT a, 2 FROM t2 WHERE b = 1; 1234INSERT INTO t2 SELECT a, 3 FROM t2 WHERE b = 1; 1235INSERT INTO t2 SELECT -1, -1 FROM t2; 1236INSERT INTO t2 SELECT -1, -1 FROM t2; 1237INSERT INTO t2 SELECT -1, -1 FROM t2; 1238INSERT INTO t3 1239VALUES (1, 0), (2, 0), (3, 0), (4, 0), (5, 0), 1240(6, 0), (7, 0), (8, 0), (9, 0), (10, 0); 1241INSERT INTO t3 SELECT * FROM t3 WHERE a = 10; 1242INSERT INTO t3 SELECT * FROM t3 WHERE a = 10; 1243SELECT * FROM t1 WHERE 12443 <= a AND a < 5 OR 12455 < a AND b = 3 OR 12463 <= a; 1247a b 12485 0 12499 7 1250EXPLAIN 1251SELECT * FROM t1 WHERE 12523 <= a AND a < 5 OR 12535 < a AND b = 3 OR 12543 <= a; 1255id select_type table type possible_keys key key_len ref rows Extra 12561 SIMPLE t1 range a a 5 NULL 3 Using where; Using index 1257SELECT * FROM t1 WHERE 12583 <= a AND a < 5 OR 12595 <= a AND b = 3 OR 12603 <= a; 1261a b 12625 0 12639 7 1264EXPLAIN 1265SELECT * FROM t1 WHERE 12663 <= a AND a < 5 OR 12675 <= a AND b = 3 OR 12683 <= a; 1269id select_type table type possible_keys key key_len ref rows Extra 12701 SIMPLE t1 range a a 5 NULL 4 Using where; Using index 1271SELECT * FROM t1 WHERE 12723 <= a AND a <= 5 OR 12735 <= a AND b = 3 OR 12743 <= a; 1275a b 12765 0 12779 7 1278EXPLAIN 1279SELECT * FROM t1 WHERE 12803 <= a AND a <= 5 OR 12815 <= a AND b = 3 OR 12823 <= a; 1283id select_type table type possible_keys key key_len ref rows Extra 12841 SIMPLE t1 range a a 5 NULL 3 Using where; Using index 1285SELECT * FROM t1 WHERE 12863 <= a AND a <= 5 OR 12873 <= a; 1288a b 12895 0 12909 7 1291EXPLAIN 1292SELECT * FROM t1 WHERE 12933 <= a AND a <= 5 OR 12943 <= a; 1295id select_type table type possible_keys key key_len ref rows Extra 12961 SIMPLE t1 range a a 5 NULL 3 Using where; Using index 1297SELECT * FROM t2 WHERE 12985 <= a AND a < 10 AND b = 1 OR 129915 <= a AND a < 20 AND b = 3 1300OR 13011 <= a AND b = 1; 1302a b 13031 1 13042 1 13053 1 13064 1 13075 1 13086 1 13097 1 13108 1 13119 1 131210 1 131311 1 131412 1 131513 1 131614 1 131715 1 131815 3 131916 1 132016 3 132117 1 132217 3 132318 1 132418 3 132519 1 132619 3 132720 1 1328EXPLAIN 1329SELECT * FROM t2 WHERE 13305 <= a AND a < 10 AND b = 1 OR 133115 <= a AND a < 20 AND b = 3 1332OR 13331 <= a AND b = 1; 1334id select_type table type possible_keys key key_len ref rows Extra 13351 SIMPLE t2 range a a 10 NULL 50 Using where; Using index 1336SELECT * FROM t2 WHERE 13375 <= a AND a < 10 AND b = 2 OR 133815 <= a AND a < 20 AND b = 3 1339OR 13401 <= a AND b = 1; 1341a b 13421 1 13432 1 13443 1 13454 1 13465 1 13475 2 13486 1 13496 2 13507 1 13517 2 13528 1 13538 2 13549 1 13559 2 135610 1 135711 1 135812 1 135913 1 136014 1 136115 1 136215 3 136316 1 136416 3 136517 1 136617 3 136718 1 136818 3 136919 1 137019 3 137120 1 1372EXPLAIN 1373SELECT * FROM t2 WHERE 13745 <= a AND a < 10 AND b = 2 OR 137515 <= a AND a < 20 AND b = 3 1376OR 13771 <= a AND b = 1; 1378id select_type table type possible_keys key key_len ref rows Extra 13791 SIMPLE t2 range a a 10 NULL 50 Using where; Using index 1380SELECT * FROM t3 WHERE 13815 <= a AND a < 10 AND b = 3 OR 1382a < 5 OR 1383a < 10; 1384a b 13851 0 13862 0 13873 0 13884 0 13895 0 13906 0 13917 0 13928 0 13939 0 1394EXPLAIN 1395SELECT * FROM t3 WHERE 13965 <= a AND a < 10 AND b = 3 OR 1397a < 5 OR 1398a < 10; 1399id select_type table type possible_keys key key_len ref rows Extra 14001 SIMPLE t3 range a a 5 NULL 8 Using where; Using index 1401DROP TABLE t1, t2, t3; 1402# 1403# Bug #47123: Endless 100% CPU loop with STRAIGHT_JOIN 1404# 1405CREATE TABLE t1(a INT, KEY(a)); 1406INSERT INTO t1 VALUES (1), (NULL); 1407SELECT * FROM t1 WHERE a <> NULL and (a <> NULL or a <= NULL); 1408a 1409DROP TABLE t1; 1410# 1411# Bug#47925: regression of range optimizer and date comparison in 5.1.39! 1412# 1413CREATE TABLE t1 ( a DATE, KEY ( a ) ); 1414CREATE TABLE t2 ( a DATETIME, KEY ( a ) ); 1415# Make optimizer choose range scan 1416INSERT INTO t1 VALUES ('2009-09-22'), ('2009-09-22'), ('2009-09-22'); 1417INSERT INTO t1 VALUES ('2009-09-23'), ('2009-09-23'), ('2009-09-23'); 1418INSERT INTO t2 VALUES ('2009-09-22 12:00:00'), ('2009-09-22 12:00:00'), 1419('2009-09-22 12:00:00'); 1420INSERT INTO t2 VALUES ('2009-09-23 12:00:00'), ('2009-09-23 12:00:00'), 1421('2009-09-23 12:00:00'); 1422# DATE vs DATE 1423EXPLAIN 1424SELECT * FROM t1 WHERE a >= '2009/09/23'; 1425id select_type table type possible_keys key key_len ref rows Extra 1426X X X range a a X X X X 1427SELECT * FROM t1 WHERE a >= '2009/09/23'; 1428a 14292009-09-23 14302009-09-23 14312009-09-23 1432SELECT * FROM t1 WHERE a >= '20090923'; 1433a 14342009-09-23 14352009-09-23 14362009-09-23 1437SELECT * FROM t1 WHERE a >= 20090923; 1438a 14392009-09-23 14402009-09-23 14412009-09-23 1442SELECT * FROM t1 WHERE a >= '2009-9-23'; 1443a 14442009-09-23 14452009-09-23 14462009-09-23 1447SELECT * FROM t1 WHERE a >= '2009.09.23'; 1448a 14492009-09-23 14502009-09-23 14512009-09-23 1452SELECT * FROM t1 WHERE a >= '2009:09:23'; 1453a 14542009-09-23 14552009-09-23 14562009-09-23 1457# DATE vs DATETIME 1458EXPLAIN 1459SELECT * FROM t2 WHERE a >= '2009/09/23'; 1460id select_type table type possible_keys key key_len ref rows Extra 1461X X X range a a X X X X 1462SELECT * FROM t2 WHERE a >= '2009/09/23'; 1463a 14642009-09-23 12:00:00 14652009-09-23 12:00:00 14662009-09-23 12:00:00 1467SELECT * FROM t2 WHERE a >= '2009/09/23'; 1468a 14692009-09-23 12:00:00 14702009-09-23 12:00:00 14712009-09-23 12:00:00 1472SELECT * FROM t2 WHERE a >= '20090923'; 1473a 14742009-09-23 12:00:00 14752009-09-23 12:00:00 14762009-09-23 12:00:00 1477SELECT * FROM t2 WHERE a >= 20090923; 1478a 14792009-09-23 12:00:00 14802009-09-23 12:00:00 14812009-09-23 12:00:00 1482SELECT * FROM t2 WHERE a >= '2009-9-23'; 1483a 14842009-09-23 12:00:00 14852009-09-23 12:00:00 14862009-09-23 12:00:00 1487SELECT * FROM t2 WHERE a >= '2009.09.23'; 1488a 14892009-09-23 12:00:00 14902009-09-23 12:00:00 14912009-09-23 12:00:00 1492SELECT * FROM t2 WHERE a >= '2009:09:23'; 1493a 14942009-09-23 12:00:00 14952009-09-23 12:00:00 14962009-09-23 12:00:00 1497# DATETIME vs DATETIME 1498EXPLAIN 1499SELECT * FROM t2 WHERE a >= '2009/09/23 12:00:00'; 1500id select_type table type possible_keys key key_len ref rows Extra 1501X X X range a a X X X X 1502SELECT * FROM t2 WHERE a >= '2009/09/23 12:00:00'; 1503a 15042009-09-23 12:00:00 15052009-09-23 12:00:00 15062009-09-23 12:00:00 1507SELECT * FROM t2 WHERE a >= '20090923120000'; 1508a 15092009-09-23 12:00:00 15102009-09-23 12:00:00 15112009-09-23 12:00:00 1512SELECT * FROM t2 WHERE a >= 20090923120000; 1513a 15142009-09-23 12:00:00 15152009-09-23 12:00:00 15162009-09-23 12:00:00 1517SELECT * FROM t2 WHERE a >= '2009-9-23 12:00:00'; 1518a 15192009-09-23 12:00:00 15202009-09-23 12:00:00 15212009-09-23 12:00:00 1522SELECT * FROM t2 WHERE a >= '2009.09.23 12:00:00'; 1523a 15242009-09-23 12:00:00 15252009-09-23 12:00:00 15262009-09-23 12:00:00 1527SELECT * FROM t2 WHERE a >= '2009:09:23 12:00:00'; 1528a 15292009-09-23 12:00:00 15302009-09-23 12:00:00 15312009-09-23 12:00:00 1532# DATETIME vs DATE 1533EXPLAIN 1534SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00'; 1535id select_type table type possible_keys key key_len ref rows Extra 1536X X X range a a X X X X 1537SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00'; 1538a 15392009-09-23 15402009-09-23 15412009-09-23 1542SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00'; 1543a 15442009-09-23 15452009-09-23 15462009-09-23 1547SELECT * FROM t1 WHERE a >= '20090923000000'; 1548a 15492009-09-23 15502009-09-23 15512009-09-23 1552SELECT * FROM t1 WHERE a >= 20090923000000; 1553a 15542009-09-23 15552009-09-23 15562009-09-23 1557SELECT * FROM t1 WHERE a >= '2009-9-23 00:00:00'; 1558a 15592009-09-23 15602009-09-23 15612009-09-23 1562SELECT * FROM t1 WHERE a >= '2009.09.23 00:00:00'; 1563a 15642009-09-23 15652009-09-23 15662009-09-23 1567SELECT * FROM t1 WHERE a >= '2009:09:23 00:00:00'; 1568a 15692009-09-23 15702009-09-23 15712009-09-23 1572# Test of the new get_date_from_str implementation 1573# Behavior differs slightly between the trunk and mysql-pe. 1574# The former may give errors for the truncated values, while the latter 1575# gives warnings. The purpose of this test is not to interfere, and only 1576# preserve existing behavior. 1577SELECT str_to_date('2007-10-00', '%Y-%m-%d') >= '' AND 1578str_to_date('2007-10-00', '%Y-%m-%d') <= '2007/10/20'; 1579str_to_date('2007-10-00', '%Y-%m-%d') >= '' AND 1580str_to_date('2007-10-00', '%Y-%m-%d') <= '2007/10/20' 15811 1582Warnings: 1583Warning 1292 Truncated incorrect date value: '' 1584SELECT str_to_date('2007-20-00', '%Y-%m-%d') >= '2007/10/20' AND 1585str_to_date('2007-20-00', '%Y-%m-%d') <= ''; 1586str_to_date('2007-20-00', '%Y-%m-%d') >= '2007/10/20' AND 1587str_to_date('2007-20-00', '%Y-%m-%d') <= '' 1588NULL 1589Warnings: 1590Warning 1292 Truncated incorrect date value: '' 1591Warning 1411 Incorrect datetime value: '2007-20-00' for function str_to_date 1592Warning 1411 Incorrect datetime value: '2007-20-00' for function str_to_date 1593SELECT str_to_date('2007-10-00', '%Y-%m-%d') BETWEEN '' AND '2007/10/20'; 1594str_to_date('2007-10-00', '%Y-%m-%d') BETWEEN '' AND '2007/10/20' 15951 1596Warnings: 1597Warning 1292 Truncated incorrect datetime value: '' 1598SELECT str_to_date('2007-20-00', '%Y-%m-%d') BETWEEN '2007/10/20' AND ''; 1599str_to_date('2007-20-00', '%Y-%m-%d') BETWEEN '2007/10/20' AND '' 1600NULL 1601Warnings: 1602Warning 1411 Incorrect datetime value: '2007-20-00' for function str_to_date 1603SELECT str_to_date('', '%Y-%m-%d'); 1604str_to_date('', '%Y-%m-%d') 16050000-00-00 1606DROP TABLE t1, t2; 1607# 1608# Bug#48459: valgrind errors with query using 'Range checked for each 1609# record' 1610# 1611CREATE TABLE t1 ( 1612a INT, 1613b CHAR(2), 1614c INT, 1615d INT, 1616KEY ( c ), 1617KEY ( d, a, b ( 2 ) ), 1618KEY ( b ( 1 ) ) 1619); 1620INSERT INTO t1 VALUES ( NULL, 'a', 1, 2 ), ( NULL, 'a', 1, 2 ), 1621( 1, 'a', 1, 2 ), ( 1, 'a', 1, 2 ); 1622CREATE TABLE t2 ( 1623a INT, 1624c INT, 1625e INT, 1626KEY ( e ) 1627); 1628INSERT INTO t2 VALUES ( 1, 1, NULL ), ( 1, 1, NULL ); 1629# Should not give Valgrind warnings 1630SELECT 1 1631FROM t1, t2 1632WHERE t1.d <> '1' AND t1.b > '1' 1633AND t1.a = t2.a AND t1.c = t2.c; 16341 16351 16361 16371 16381 1639DROP TABLE t1, t2; 1640# 1641# Bug #48665: sql-bench's insert test fails due to wrong result 1642# 1643CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a)); 1644INSERT INTO t1 VALUES (0,0), (1,1); 1645EXPLAIN 1646SELECT * FROM t1 FORCE INDEX (PRIMARY) 1647WHERE (a>=1 AND a<=2) OR (a>=4 AND a<=5) OR (a>=0 AND a <=10); 1648id select_type table type possible_keys key key_len ref rows Extra 1649@ @ @ range @ @ @ @ @ @ 1650# Should return 2 rows 1651SELECT * FROM t1 FORCE INDEX (PRIMARY) 1652WHERE (a>=1 AND a<=2) OR (a>=4 AND a<=5) OR (a>=0 AND a <=10); 1653a b 16540 0 16551 1 1656DROP TABLE t1; 1657# 1658# Bug #54802: 'NOT BETWEEN' evaluation is incorrect 1659# 1660CREATE TABLE t1 (c_key INT, c_notkey INT, KEY(c_key)); 1661INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3); 1662EXPLAIN SELECT * FROM t1 WHERE 2 NOT BETWEEN c_notkey AND c_key; 1663id select_type table type possible_keys key key_len ref rows Extra 16641 SIMPLE t1 ALL c_key NULL NULL NULL 3 Using where 1665SELECT * FROM t1 WHERE 2 NOT BETWEEN c_notkey AND c_key; 1666c_key c_notkey 16671 1 16683 3 1669DROP TABLE t1; 1670# 1671# Bug #57030: 'BETWEEN' evaluation is incorrect 1672# 1673CREATE TABLE t1(pk INT PRIMARY KEY, i4 INT); 1674CREATE UNIQUE INDEX i4_uq ON t1(i4); 1675INSERT INTO t1 VALUES (1,10), (2,20), (3,30); 1676EXPLAIN 1677SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 10; 1678id select_type table type possible_keys key key_len ref rows Extra 16791 SIMPLE t1 const i4_uq i4_uq 5 const 1 NULL 1680SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 10; 1681pk i4 16821 10 1683EXPLAIN 1684SELECT * FROM t1 WHERE 10 BETWEEN i4 AND i4; 1685id select_type table type possible_keys key key_len ref rows Extra 16861 SIMPLE t1 const i4_uq i4_uq 5 const 1 NULL 1687SELECT * FROM t1 WHERE 10 BETWEEN i4 AND i4; 1688pk i4 16891 10 1690EXPLAIN 1691SELECT * FROM t1 WHERE 10 BETWEEN 10 AND i4; 1692id select_type table type possible_keys key key_len ref rows Extra 16931 SIMPLE t1 range i4_uq i4_uq 5 NULL 3 Using index condition 1694SELECT * FROM t1 WHERE 10 BETWEEN 10 AND i4; 1695pk i4 16961 10 16972 20 16983 30 1699EXPLAIN 1700SELECT * FROM t1 WHERE 10 BETWEEN i4 AND 10; 1701id select_type table type possible_keys key key_len ref rows Extra 17021 SIMPLE t1 range i4_uq i4_uq 5 NULL 1 Using index condition 1703SELECT * FROM t1 WHERE 10 BETWEEN i4 AND 10; 1704pk i4 17051 10 1706EXPLAIN 1707SELECT * FROM t1 WHERE 10 BETWEEN 10 AND 10; 1708id select_type table type possible_keys key key_len ref rows Extra 17091 SIMPLE t1 ALL NULL NULL NULL NULL 3 NULL 1710SELECT * FROM t1 WHERE 10 BETWEEN 10 AND 10; 1711pk i4 17121 10 17132 20 17143 30 1715EXPLAIN 1716SELECT * FROM t1 WHERE 10 BETWEEN 11 AND 11; 1717id select_type table type possible_keys key key_len ref rows Extra 17181 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 1719SELECT * FROM t1 WHERE 10 BETWEEN 11 AND 11; 1720pk i4 1721EXPLAIN 1722SELECT * FROM t1 WHERE 10 BETWEEN 100 AND 0; 1723id select_type table type possible_keys key key_len ref rows Extra 17241 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 1725SELECT * FROM t1 WHERE 10 BETWEEN 100 AND 0; 1726pk i4 1727EXPLAIN 1728SELECT * FROM t1 WHERE i4 BETWEEN 100 AND 0; 1729id select_type table type possible_keys key key_len ref rows Extra 17301 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 1731SELECT * FROM t1 WHERE i4 BETWEEN 100 AND 0; 1732pk i4 1733EXPLAIN 1734SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 99999999999999999; 1735id select_type table type possible_keys key key_len ref rows Extra 17361 SIMPLE t1 range i4_uq i4_uq 5 NULL 3 Using index condition 1737SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 99999999999999999; 1738pk i4 17391 10 17402 20 17413 30 1742EXPLAIN 1743SELECT * FROM t1 WHERE i4 BETWEEN 999999999999999 AND 30; 1744id select_type table type possible_keys key key_len ref rows Extra 17451 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 1746SELECT * FROM t1 WHERE i4 BETWEEN 999999999999999 AND 30; 1747pk i4 1748EXPLAIN 1749SELECT * FROM t1 WHERE i4 BETWEEN 10 AND '20'; 1750id select_type table type possible_keys key key_len ref rows Extra 17511 SIMPLE t1 range i4_uq i4_uq 5 NULL 1 Using index condition 1752SELECT * FROM t1 WHERE i4 BETWEEN 10 AND '20'; 1753pk i4 17541 10 17552 20 1756EXPLAIN 1757SELECT * FROM t1, t1 as t2 WHERE t2.pk BETWEEN t1.i4 AND t1.i4; 1758id select_type table type possible_keys key key_len ref rows Extra 17591 SIMPLE t1 ALL i4_uq NULL NULL NULL 3 NULL 17601 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.i4 1 Using index condition 1761SELECT * FROM t1, t1 as t2 WHERE t2.pk BETWEEN t1.i4 AND t1.i4; 1762pk i4 pk i4 1763EXPLAIN 1764SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk; 1765id select_type table type possible_keys key key_len ref rows Extra 17661 SIMPLE t1 ALL i4_uq NULL NULL NULL 3 NULL 17671 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.i4 1 Using index condition 1768SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk; 1769pk i4 pk i4 1770DROP TABLE t1; 1771# 1772# BUG#13519696 - 62940: SELECT RESULTS VARY WITH VERSION AND 1773# WITH/WITHOUT INDEX RANGE SCAN 1774# 1775create table t1 (id int unsigned not null auto_increment primary key); 1776insert into t1 values (null); 1777insert into t1 select null from t1; 1778insert into t1 select null from t1; 1779insert into t1 select null from t1; 1780insert into t1 select null from t1; 1781insert into t1 select null from t1; 1782insert into t1 select null from t1; 1783insert into t1 select null from t1; 1784insert into t1 select null from t1; 1785create table t2 ( 1786id int unsigned not null auto_increment, 1787val decimal(5,3) not null, 1788primary key (id,val), 1789unique key (val,id), 1790unique key (id)); 1791insert into t2 select null,id*0.0009 from t1; 1792select count(val) from t2 ignore index (val) where val > 0.1155; 1793count(val) 1794128 1795select count(val) from t2 force index (val) where val > 0.1155; 1796count(val) 1797128 1798drop table t2, t1; 1799# 1800# BUG#13453382 - REGRESSION SINCE 5.1.39, RANGE OPTIMIZER WRONG 1801# RESULTS WITH DECIMAL CONVERSION 1802# 1803create table t1 (a int,b int,c int,primary key (a,c)); 1804insert into t1 values (1,1,2),(1,1,3),(1,1,4); 1805select convert(3, signed integer) > 2.9; 1806convert(3, signed integer) > 2.9 18071 1808select * from t1 force index (primary) where a=1 and c>= 2.9; 1809a b c 18101 1 3 18111 1 4 1812select * from t1 ignore index (primary) where a=1 and c>= 2.9; 1813a b c 18141 1 3 18151 1 4 1816select * from t1 force index (primary) where a=1 and c> 2.9; 1817a b c 18181 1 3 18191 1 4 1820select * from t1 ignore index (primary) where a=1 and c> 2.9; 1821a b c 18221 1 3 18231 1 4 1824drop table t1; 1825# 1826# BUG#13463488 - 63437: CHAR & BETWEEN WITH INDEX RETURNS WRONG 1827# RESULT AFTER MYSQL 5.1. 1828# 1829CREATE TABLE t1( 1830F1 CHAR(5) NOT NULL, 1831F2 CHAR(5) NOT NULL, 1832F3 CHAR(5) NOT NULL, 1833PRIMARY KEY(F1), 1834INDEX IDX_F2(F2) 1835); 1836INSERT INTO t1 VALUES 1837('A','A','A'),('AA','AA','AA'),('AAA','AAA','AAA'), 1838('AAAA','AAAA','AAAA'),('AAAAA','AAAAA','AAAAA'); 1839SELECT * FROM t1 WHERE F1 = 'A '; 1840F1 F2 F3 1841A A A 1842SELECT * FROM t1 IGNORE INDEX(PRIMARY) WHERE F1 = 'A '; 1843F1 F2 F3 1844A A A 1845SELECT * FROM t1 WHERE F1 >= 'A '; 1846F1 F2 F3 1847A A A 1848AA AA AA 1849AAA AAA AAA 1850AAAA AAAA AAAA 1851AAAAA AAAAA AAAAA 1852SELECT * FROM t1 WHERE F1 > 'A '; 1853F1 F2 F3 1854AA AA AA 1855AAA AAA AAA 1856AAAA AAAA AAAA 1857AAAAA AAAAA AAAAA 1858SELECT * FROM t1 WHERE F1 BETWEEN 'A ' AND 'AAAAA'; 1859F1 F2 F3 1860A A A 1861AA AA AA 1862AAA AAA AAA 1863AAAA AAAA AAAA 1864AAAAA AAAAA AAAAA 1865SELECT * FROM t1 WHERE F2 BETWEEN 'A ' AND 'AAAAA'; 1866F1 F2 F3 1867A A A 1868AA AA AA 1869AAA AAA AAA 1870AAAA AAAA AAAA 1871AAAAA AAAAA AAAAA 1872SELECT * FROM t1 WHERE F3 BETWEEN 'A ' AND 'AAAAA'; 1873F1 F2 F3 1874A A A 1875AA AA AA 1876AAA AAA AAA 1877AAAA AAAA AAAA 1878AAAAA AAAAA AAAAA 1879SELECT * FROM t1 IGNORE INDEX(PRIMARY) WHERE F1 BETWEEN 'A ' AND 1880'AAAAA'; 1881F1 F2 F3 1882A A A 1883AA AA AA 1884AAA AAA AAA 1885AAAA AAAA AAAA 1886AAAAA AAAAA AAAAA 1887DROP TABLE t1; 1888End of 5.1 tests 1889CREATE TABLE t1 (c1 DECIMAL(10,0),INDEX(c1)); 1890INSERT INTO t1 VALUES (1),(2),(3); 1891SELECT c1 FROM t1 WHERE c1 >= 'A' GROUP BY 1; 1892c1 18931 18942 18953 1896Warnings: 1897Warning 1366 Incorrect decimal value: 'A' for column 'c1' at row 1 1898Warning 1292 Truncated incorrect DOUBLE value: 'A' 1899DROP TABLE t1; 1900create table t1 (a int,b int,key (b),key (a),key (b,a)); 1901insert into t1(a,b) values (1,2),(3,4),(5,6),(7,8); 1902create table t2 (c int); 1903insert into t2(c) values (1),(5),(6),(7),(8); 1904select 1 from (select c from t1,t2 where b >= 1 and a <=> c group by 1 limit 1) as d1; 19051 19061 1907drop table t1, t2; 1908# 1909# Bug #26106: Wrong plan may be chosen when there are several possible 1910# range and ref accesses 1911# 1912# Note: The fix for this bug has been reverted. The code will no longer 1913# select the optimal plan for the two following test queries. This is 1914# not due to a bug but due to minor differences in range estimates 1915# produced by the storage engine. 1916CREATE TABLE t1( 1917a INT, 1918b INT, 1919KEY k ( a ), 1920KEY l ( a, b ) 1921); 1922INSERT INTO t1(a) VALUES (1); 1923INSERT INTO t1 1924VALUES (2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3); 1925INSERT INTO t1 SELECT 3, 4 FROM t1 WHERE a = 2 AND b = 3; 1926INSERT INTO t1 SELECT 4, 1 FROM t1 WHERE a = 2 AND b = 3; 1927ANALYZE TABLE t1; 1928Table Op Msg_type Msg_text 1929test.t1 analyze status OK 1930INSERT INTO t1 VALUES (1, 2); 1931INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; 1932INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; 1933INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; 1934INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; 1935INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; 1936INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; 1937INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; 1938# This must use range over index l, not k. 1939# Update: Due to patch being reverted and minor differences in 1940# range estimates k is selected. 1941EXPLAIN SELECT * FROM t1 WHERE a = 1 AND b >= 2; 1942id select_type table type possible_keys key key_len ref rows Extra 19431 SIMPLE t1 ref k,l k 5 const 134 Using where 1944CREATE TABLE t2( 1945a INT, 1946b INT, 1947c INT, 1948KEY k ( a ), 1949KEY l ( a, b ), 1950KEY m ( b ), 1951KEY n ( a, c ) 1952); 1953INSERT INTO t2(a) VALUES (1); 1954INSERT INTO t2 1955VALUES (2,3,3),(2,3,3),(2,3,3),(2,3,3),(2,3,3), 1956(2,3,3),(2,3,3),(2,3,3),(2,3,3),(2,3,3); 1957INSERT INTO t2 SELECT 3, 4, 4 FROM t2 WHERE a = 2 AND b = 3; 1958INSERT INTO t2 SELECT 4, 1, 1 FROM t2 WHERE a = 2 AND b = 3; 1959ANALYZE TABLE t2; 1960Table Op Msg_type Msg_text 1961test.t2 analyze status OK 1962INSERT INTO t2 VALUES (1, 2, 2); 1963INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2; 1964INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2; 1965INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2; 1966INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2; 1967INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2; 1968INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2; 1969INSERT INTO t2 VALUES (1, 1, 2); 1970# This must use range over index l, not n. 1971# Update: Due to patch being reverted and minor differences in 1972# range estimates k is selected. 1973EXPLAIN SELECT * FROM t2 WHERE a = 1 AND b >= 2 AND c >= 2; 1974id select_type table type possible_keys key key_len ref rows Extra 19751 SIMPLE t2 ref k,l,m,n k 5 const 66 Using where 1976DROP TABLE t1, t2; 1977# 1978# BUG#11765831: 'RANGE ACCESS' MAY INCORRECTLY FILTER 1979# AWAY QUALIFYING ROWS 1980# 1981CREATE TABLE t10( 1982K INT NOT NULL AUTO_INCREMENT, 1983I INT, J INT, 1984PRIMARY KEY(K), 1985KEY(I,J) 1986); 1987INSERT INTO t10(I,J) VALUES (6,1),(6,2),(6,3),(6,4),(6,5), 1988(6,6),(6,7),(6,8),(6,9),(6,0); 1989CREATE TABLE t100 LIKE t10; 1990INSERT INTO t100(I,J) SELECT X.I, X.K+(10*Y.K) FROM t10 AS X,t10 AS Y; 1991INSERT INTO t100(I,J) VALUES(8,26); 1992 1993EXPLAIN SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5); 1994id select_type table type possible_keys key key_len ref rows Extra 19951 SIMPLE t100 range I I 10 NULL 4 Using index condition 1996 1997SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5); 1998K I J 1999101 8 26 2000DROP TABLE t10,t100; 2001# 2002# BUG#12698916 - JOIN QUERY GIVES WRONG RESULT AT 2ND EXEC. OR 2003# AFTER FLUSH TABLES [-INT VS NULL] 2004# 2005CREATE TABLE t1 (col_int INT, pk INT) ENGINE=InnoDB STATS_PERSISTENT=0; 2006INSERT INTO t1 VALUES (-100,1),(1,6); 2007CREATE TABLE t2 ( 2008col_int_key INT, 2009col_varchar VARCHAR(100) NOT NULL DEFAULT "DEFAULT", 2010pk INT NOT NULL, 2011PRIMARY KEY (pk), 2012KEY (col_int_key) 2013) ENGINE=InnoDB STATS_PERSISTENT=0; 2014INSERT INTO t2 VALUES 2015(1,"GOOD",1),(100,"",2),(200,"",3),(300,"",4),(400,"",5),(500,"",8); 2016EXPLAIN SELECT t1.*,t2.* FROM t1 straight_join t2 2017ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk; 2018id select_type table type possible_keys key key_len ref rows Extra 20191 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where 20201 SIMPLE t2 ref PRIMARY,col_int_key col_int_key 5 test.t1.col_int 1 Using index condition 2021SELECT t1.*,t2.* FROM t1 straight_join t2 2022ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk; 2023col_int pk col_int_key col_varchar pk 20241 6 1 GOOD 1 2025# need FLUSH so that InnoDB statistics change and thus plan changes 2026FLUSH TABLES; 2027EXPLAIN SELECT t1.*,t2.* FROM t1 straight_join t2 2028ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk; 2029id select_type table type possible_keys key key_len ref rows Extra 20301 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL 20311 SIMPLE t2 ALL PRIMARY,col_int_key NULL NULL NULL 6 Range checked for each record (index map: 0x3) 2032SELECT t1.*,t2.* FROM t1 straight_join t2 2033ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk; 2034col_int pk col_int_key col_varchar pk 20351 6 1 GOOD 1 2036DROP TABLE t1,t2; 2037# 2038# Bug#12694872 - 2039# VALGRIND: 18,816 BYTES IN 196 BLOCKS ARE DEFINITELY LOST IN UNIQUE::GET 2040# 2041CREATE TABLE t1 ( 2042pk INTEGER AUTO_INCREMENT, 2043col_int_nokey INTEGER NOT NULL, 2044col_int_key INTEGER NOT NULL, 2045col_date_key DATE NOT NULL, 2046col_varchar_key VARCHAR(1) NOT NULL, 2047col_varchar_nokey VARCHAR(1) NOT NULL, 2048PRIMARY KEY (pk), 2049KEY (col_int_key), 2050KEY (col_date_key), 2051KEY (col_varchar_key, col_int_key) 2052); 2053INSERT INTO t1 ( 2054col_int_key, 2055col_int_nokey, 2056col_date_key, 2057col_varchar_key, 2058col_varchar_nokey 2059) VALUES 2060(0, 4, '2011-08-25', 'j', 'j'), 2061(8, 6, '2004-09-18', 'v', 'v'), 2062(1, 3, '2009-12-01', 'c', 'c'), 2063(8, 5, '2004-12-17', 'm', 'm'), 2064(9, 3, '2000-03-14', 'd', 'd'), 2065(6, 2, '2006-05-25', 'y', 'y'), 2066(1, 9, '2008-01-23', 't', 't'), 2067(6, 3, '2007-06-18', 'd', 'd'), 2068(2, 8, '2002-10-13', 's', 's'), 2069(4, 1, '1900-01-01', 'r', 'r'), 2070(8, 8, '1959-04-25', 'm', 'm'), 2071(4, 8, '2006-03-09', 'b', 'b'), 2072(4, 5, '2001-06-05', 'x', 'x'), 2073(7, 7, '2006-05-28', 'g', 'g'), 2074(4, 5, '2001-04-19', 'p', 'p'), 2075(1, 1, '1900-01-01', 'q', 'q'), 2076(9, 6, '2004-08-20', 'w', 'w'), 2077(4, 2, '2004-10-10', 'd', 'd'), 2078(8, 9, '2000-04-02', 'e', 'e') 2079; 2080ALTER TABLE t1 DISABLE KEYS; 2081SELECT table2.col_date_key AS field1, 2082CONCAT ( table2.col_varchar_nokey, table1.col_varchar_nokey ) AS field2 2083FROM ( t1 AS table1 INNER JOIN t1 AS table2 2084ON (( table2.pk <> table1.pk ) AND 2085( table2.pk >= table1.col_int_nokey ) ) ) 2086WHERE ( table1.pk > 226 AND 2087table1.pk < ( 226 + 102 ) OR 2088( table1.col_int_key > 226 AND 2089table1.col_int_key < ( 226 + 36 ) OR 2090( table1.col_varchar_key <= 'h' OR 2091table1.col_int_key > 226 AND 2092table1.col_int_key < ( 226 + 227 ) ) 2093) 2094) 2095; 2096field1 field2 20971900-01-01 qb 20981900-01-01 qc 20991900-01-01 qd 21001900-01-01 qd 21011900-01-01 qd 21021900-01-01 qe 21031900-01-01 qg 21041900-01-01 rb 21051900-01-01 rc 21061900-01-01 rd 21071900-01-01 rd 21081900-01-01 rd 21091900-01-01 re 21101900-01-01 rg 21111959-04-25 mb 21121959-04-25 mc 21131959-04-25 md 21141959-04-25 md 21151959-04-25 md 21161959-04-25 me 21171959-04-25 mg 21182000-03-14 dc 21192000-03-14 dd 21202000-03-14 dd 21212000-04-02 eb 21222000-04-02 ec 21232000-04-02 ed 21242000-04-02 ed 21252000-04-02 ed 21262000-04-02 eg 21272001-04-19 pb 21282001-04-19 pc 21292001-04-19 pd 21302001-04-19 pd 21312001-04-19 pd 21322001-04-19 pe 21332001-04-19 pg 21342001-06-05 xb 21352001-06-05 xc 21362001-06-05 xd 21372001-06-05 xd 21382001-06-05 xd 21392001-06-05 xe 21402001-06-05 xg 21412002-10-13 sb 21422002-10-13 sc 21432002-10-13 sd 21442002-10-13 sd 21452002-10-13 sd 21462002-10-13 se 21472002-10-13 sg 21482004-08-20 wb 21492004-08-20 wc 21502004-08-20 wd 21512004-08-20 wd 21522004-08-20 wd 21532004-08-20 we 21542004-08-20 wg 21552004-09-18 vd 21562004-10-10 db 21572004-10-10 dc 21582004-10-10 dd 21592004-10-10 dd 21602004-10-10 de 21612004-10-10 dg 21622004-12-17 mc 21632004-12-17 md 21642004-12-17 md 21652004-12-17 md 21662006-03-09 bc 21672006-03-09 bd 21682006-03-09 bd 21692006-03-09 bd 21702006-03-09 be 21712006-03-09 bg 21722006-05-25 yc 21732006-05-25 yd 21742006-05-25 yd 21752006-05-25 yd 21762006-05-28 gb 21772006-05-28 gc 21782006-05-28 gd 21792006-05-28 gd 21802006-05-28 gd 21812006-05-28 ge 21822007-06-18 db 21832007-06-18 dc 21842007-06-18 dd 21852007-06-18 dd 21862007-06-18 dg 21872008-01-23 tc 21882008-01-23 td 21892008-01-23 td 21902008-01-23 td 21912008-01-23 tg 21922009-12-01 cd 21932009-12-01 cd 21942009-12-01 cd 2195ALTER TABLE t1 ENABLE KEYS; 2196CREATE TABLE t2 SELECT table2.col_date_key AS field1, 2197CONCAT ( table2.col_varchar_nokey, table1.col_varchar_nokey ) AS field2 2198FROM ( t1 AS table1 INNER JOIN t1 AS table2 2199ON (( table2.pk <> table1.pk ) AND 2200( table2.pk >= table1.col_int_nokey ) ) ) 2201WHERE ( table1.pk > 226 AND 2202table1.pk < ( 226 + 102 ) OR 2203( table1.col_int_key > 226 AND 2204table1.col_int_key < ( 226 + 36 ) OR 2205( table1.col_varchar_key <= 'h' OR 2206table1.col_int_key > 226 AND 2207table1.col_int_key < ( 226 + 227 ) ) 2208) 2209) 2210; 2211SELECT * FROM t2 2212WHERE (field1, field2) IN (SELECT table2.col_date_key AS field1, 2213CONCAT ( table2.col_varchar_nokey, table1.col_varchar_nokey ) AS field2 2214FROM ( t1 AS table1 INNER JOIN t1 AS table2 2215ON (( table2.pk <> table1.pk ) AND 2216( table2.pk >= table1.col_int_nokey ) ) ) 2217WHERE ( table1.pk > 226 AND 2218table1.pk < ( 226 + 102 ) OR 2219( table1.col_int_key > 226 AND 2220table1.col_int_key < ( 226 + 36 ) OR 2221( table1.col_varchar_key <= 'h' OR 2222table1.col_int_key > 226 AND 2223table1.col_int_key < ( 226 + 227 ) ) 2224) 2225) 2226); 2227field1 field2 22281900-01-01 qb 22291900-01-01 qc 22301900-01-01 qd 22311900-01-01 qd 22321900-01-01 qd 22331900-01-01 qe 22341900-01-01 qg 22351900-01-01 rb 22361900-01-01 rc 22371900-01-01 rd 22381900-01-01 rd 22391900-01-01 rd 22401900-01-01 re 22411900-01-01 rg 22421959-04-25 mb 22431959-04-25 mc 22441959-04-25 md 22451959-04-25 md 22461959-04-25 md 22471959-04-25 me 22481959-04-25 mg 22492000-03-14 dc 22502000-03-14 dd 22512000-03-14 dd 22522000-04-02 eb 22532000-04-02 ec 22542000-04-02 ed 22552000-04-02 ed 22562000-04-02 ed 22572000-04-02 eg 22582001-04-19 pb 22592001-04-19 pc 22602001-04-19 pd 22612001-04-19 pd 22622001-04-19 pd 22632001-04-19 pe 22642001-04-19 pg 22652001-06-05 xb 22662001-06-05 xc 22672001-06-05 xd 22682001-06-05 xd 22692001-06-05 xd 22702001-06-05 xe 22712001-06-05 xg 22722002-10-13 sb 22732002-10-13 sc 22742002-10-13 sd 22752002-10-13 sd 22762002-10-13 sd 22772002-10-13 se 22782002-10-13 sg 22792004-08-20 wb 22802004-08-20 wc 22812004-08-20 wd 22822004-08-20 wd 22832004-08-20 wd 22842004-08-20 we 22852004-08-20 wg 22862004-09-18 vd 22872004-10-10 db 22882004-10-10 dc 22892004-10-10 dd 22902004-10-10 dd 22912004-10-10 de 22922004-10-10 dg 22932004-12-17 mc 22942004-12-17 md 22952004-12-17 md 22962004-12-17 md 22972006-03-09 bc 22982006-03-09 bd 22992006-03-09 bd 23002006-03-09 bd 23012006-03-09 be 23022006-03-09 bg 23032006-05-25 yc 23042006-05-25 yd 23052006-05-25 yd 23062006-05-25 yd 23072006-05-28 gb 23082006-05-28 gc 23092006-05-28 gd 23102006-05-28 gd 23112006-05-28 gd 23122006-05-28 ge 23132007-06-18 db 23142007-06-18 dc 23152007-06-18 dd 23162007-06-18 dd 23172007-06-18 dg 23182008-01-23 tc 23192008-01-23 td 23202008-01-23 td 23212008-01-23 td 23222008-01-23 tg 23232009-12-01 cd 23242009-12-01 cd 23252009-12-01 cd 2326DROP TABLE t1, t2; 2327# 2328# BUG#12912171 - ASSERTION FAILED: QUICK->HEAD->READ_SET == 2329# SAVE_READ_SET 2330# 2331CREATE TABLE t1 ( 2332a INT, 2333b INT, 2334c INT, 2335PRIMARY KEY (c,a), KEY (a),KEY (a) 2336) ENGINE=INNODB PARTITION BY KEY () PARTITIONS 2; 2337Warnings: 2338Note 1831 Duplicate index 'a_2' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release. 2339INSERT INTO t1 VALUES (1,5,1),(2,4,1),(3,3,1),(4,2,1),(5,1,1); 2340UPDATE t1 SET b = 0, c=1 WHERE a <=>0; 2341SELECT * FROM t1; 2342a b c 23431 5 1 23442 4 1 23453 3 1 23464 2 1 23475 1 1 2348DROP TABLE t1; 2349# 2350# BUG#13256446 - ASSERTION QUICK->HEAD->READ_SET == 2351# SAVE_READ_SET' FAILED IN OPT_RANGE.CC:1606 2352# 2353CREATE TABLE t1 ( 2354f1 INT AUTO_INCREMENT, 2355f2 INT, 2356f3 INT, 2357f4 INT, 2358PRIMARY KEY (f1),KEY(f2) 2359) ENGINE=INNODB; 2360CREATE ALGORITHM=MERGE VIEW v2 AS SELECT f1,f2,f3,f4 FROM t1 WHERE f2=103; 2361INSERT INTO t1 VALUES (154,0,NULL,0),(0,NULL,9,0), 2362(NULL,102,NULL,3),(0,3,NULL,0), (9,0,NULL,0),(0,9,NULL,157); 2363SELECT * FROM v2; 2364f1 f2 f3 f4 2365UPDATE v2 SET f4=0, f2=NULL, f1=NULL WHERE f1 > 16 ORDER BY f1; 2366SELECT * FROM v2; 2367f1 f2 f3 f4 2368DROP TABLE t1; 2369DROP VIEW v2; 2370CREATE TABLE t1 ( 2371f1 INT AUTO_INCREMENT, 2372f2 INT, 2373f3 INT, 2374f4 INT, 2375PRIMARY KEY (f1),KEY(f2) 2376) ENGINE=INNODB; 2377INSERT INTO t1 VALUES(1,NULL,NULL,0), (2,2,0,3), (9,0,107,18), 2378(10,0,0,0), (231,0,0,0), (232,0,8,0), (234,0,0,NULL), (235,8,0,3); 2379CREATE ALGORITHM=MERGE VIEW v3 AS SELECT f1,f2,f3,f4 FROM t1 WHERE f1<=85 ; 2380SELECT * FROM v3; 2381f1 f2 f3 f4 23821 NULL NULL 0 23832 2 0 3 23849 0 107 18 238510 0 0 0 2386UPDATE v3 SET f3=0, f4=4 WHERE f2=68 ORDER BY f1; 2387SELECT * FROM v3; 2388f1 f2 f3 f4 23891 NULL NULL 0 23902 2 0 3 23919 0 107 18 239210 0 0 0 2393DROP TABLE t1; 2394DROP VIEW v3; 2395# 2396# BUG#13731380: RANGE OPTIMIZER CALLS RECORDS_IN_RANGE() FOR OPEN RANGE 2397# 2398CREATE TABLE t1 (pk INT PRIMARY KEY); 2399INSERT INTO t1 VALUES (1),(3),(5); 2400SELECT * FROM t1 WHERE pk <> 3 OR pk < 4; 2401pk 24021 24033 24045 2405DROP TABLE t1; 2406# 2407# BUG#13803810: TOO FEW ROWS RETURNED FOR RANGE ACCESS IN 2408# VARCHAR INDEX USING DATETIME VALUE 2409 2410CREATE TABLE t1 (a DATETIME); 2411INSERT INTO t1 VALUES ('2001-01-01 00:00:00'); 2412INSERT INTO t1 VALUES ('2001-01-01 11:22:33'); 2413CREATE TABLE t2 (b VARCHAR(64), KEY (b)); 2414INSERT INTO t2 VALUES ('2001-01-01'); 2415INSERT INTO t2 VALUES ('2001.01.01'); 2416INSERT INTO t2 VALUES ('2001#01#01'); 2417INSERT INTO t2 VALUES ('2001-01-01 00:00:00'); 2418INSERT INTO t2 VALUES ('2001-01-01 11:22:33'); 2419 2420# range/ref access cannot be used for this query 2421 2422EXPLAIN SELECT * FROM t2 WHERE b=CAST('2001-01-01' AS DATE); 2423id select_type table type possible_keys key key_len ref rows Extra 24241 SIMPLE t2 index b b 67 NULL 5 Using where; Using index 2425SELECT * FROM t2 WHERE b=CAST('2001-01-01' AS DATE); 2426b 24272001#01#01 24282001-01-01 24292001-01-01 00:00:00 24302001.01.01 2431 2432# range/ref access cannot be used for any of the queries below. 2433# See BUG#13814468 about 'Range checked for each record' 2434 2435EXPLAIN SELECT * FROM t1, t2 WHERE a=b ORDER BY BINARY a, BINARY b; 2436id select_type table type possible_keys key key_len ref rows Extra 24371 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort 24381 SIMPLE t2 ALL b NULL NULL NULL 5 Range checked for each record (index map: 0x1) 2439SELECT * FROM t1, t2 WHERE a=b ORDER BY BINARY a, BINARY b; 2440a b 24412001-01-01 00:00:00 2001#01#01 24422001-01-01 00:00:00 2001-01-01 24432001-01-01 00:00:00 2001-01-01 00:00:00 24442001-01-01 00:00:00 2001.01.01 24452001-01-01 11:22:33 2001-01-01 11:22:33 2446 2447EXPLAIN SELECT * FROM t1, t2 WHERE b=a ORDER BY BINARY a, BINARY b; 2448id select_type table type possible_keys key key_len ref rows Extra 24491 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort 24501 SIMPLE t2 ALL b NULL NULL NULL 5 Range checked for each record (index map: 0x1) 2451SELECT * FROM t1, t2 WHERE b=a ORDER BY BINARY a, BINARY b; 2452a b 24532001-01-01 00:00:00 2001#01#01 24542001-01-01 00:00:00 2001-01-01 24552001-01-01 00:00:00 2001-01-01 00:00:00 24562001-01-01 00:00:00 2001.01.01 24572001-01-01 11:22:33 2001-01-01 11:22:33 2458 2459DROP TABLE t1,t2; 2460# 2461# Bug #20229614: OR CONDITIONS ON MULTI-COLUMN INDEX MAY NOT USE ALL 2462# INDEX COLUMNS TO FILTER ROWS 2463# 2464CREATE TABLE t1 ( 2465c1 INT, 2466c2 INT, 2467c3 INT, 2468PRIMARY KEY(c1, c2, c3) 2469) ENGINE=INNODB; 2470INSERT INTO t1 VALUES (1, 1, 1), (1, 1, 2), (1, 1, 3), 2471(1, 1, 4), (1, 1, 5); 2472INSERT INTO t1 SELECT c1, 2, c3 FROM t1; 2473INSERT INTO t1 SELECT c1, 3, c3 FROM t1 WHERE c2 = 1; 2474SELECT COUNT(*) FROM t1; 2475COUNT(*) 247615 2477EXPLAIN SELECT c1, c2, c3 2478FROM t1 2479WHERE (c1 = 1 AND c2 = 1 AND c3 = 1) OR 2480(c1 = 1 AND c2 = 2 AND c3 = 2) OR 2481(c1 = 1 AND c2 = 2 AND c3 = 3); 2482id select_type table type possible_keys key key_len ref rows Extra 24831 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using where; Using index 2484DROP TABLE t1; 2485# 2486# Bug#21139683: ASSERTION FAILED: TYPE_ARG == MAYBE_KEY || 2487# TYPE_ARG == IMPOSSIBLE 2488# 2489CREATE TABLE t1 ( 2490a BLOB, 2491PRIMARY KEY(a(1)), 2492KEY(a(1)) 2493) ENGINE=INNODB; 2494SELECT 1 FROM t1 WHERE a <> 'a' OR a <> ""; 24951 2496DROP TABLE t1; 2497# 2498# Bug #23259872: OPTIMIZER CHOOSES TO USE NON PRIMARY 2499# INDEX, EVEN THOUGH COST IS HIGHER 2500# 2501CREATE TABLE `giant_table` ( 2502`id` int(11) NOT NULL AUTO_INCREMENT, 2503`one_id` int(11) NOT NULL, 2504`other_id` bigint(20) NOT NULL DEFAULT '0', 2505`some_other_id` int(11) DEFAULT 0 NOT NULL, 2506`something` double NOT NULL DEFAULT '0', 2507`comment` text COLLATE utf8_unicode_ci, 2508`flags` int(11) NOT NULL DEFAULT '0', 2509`time_created` int(11) NOT NULL DEFAULT '0', 2510PRIMARY KEY (`id`), 2511KEY `time_created` (`time_created`), 2512KEY `some_other_id` (`some_other_id`), 2513KEY `one_other_idx` (`one_id`,`other_id`), 2514KEY `other_id` (`other_id`,`time_created`) 2515) ENGINE=InnoDB AUTO_INCREMENT=101651329 2516DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ; 2517CREATE TABLE t1 (c1 INT); 2518INSERT INTO t1 VALUES (66136540), (68983250), (89627210), (77869520), 2519(82543190), (67538270), (77282760), (77908170), 2520(70923370), (68066360); 2521CREATE PROCEDURE p() 2522BEGIN 2523SET @x = 1; 2524REPEAT 2525INSERT INTO giant_table(id,one_id) 2526SELECT c1 + @x, 0 2527FROM t1 2528WHERE c1 IN (66136540, 68985250, 89627210, 77869520 , 82543190, 67538270, 252977282760, 77908170, 70923370, 68066360); 2530SET @x = @x + 1; 2531UNTIL @x > 30 END REPEAT; 2532END $ 2533CALL p(); 2534SELECT count(*) FROM giant_table; 2535count(*) 2536270 2537INSERT INTO giant_table (id,one_id) VALUES (66136539, 0), (68983258,1), 2538(89628210,1), (77869520,2); 2539INSERT INTO giant_table (id,one_id, some_other_id) VALUES(84673401, 0, 1), 2540(61069031, 1, 1); 2541EXPLAIN SELECT id, something, comment, time_created, one_id, other_id, 2542some_other_id, flags 2543FROM giant_table 2544WHERE id IN (66136539, 68983258, 89628210, 77869520, 82543198, 67538272, 254584673401, 61069031, 68214385, 77282865, 76991297, 64569216, 254689481638, 74534074, 70396537, 80076375, 63308530, 77908270, 254770923271, 68066180) 2548AND (giant_table.flags & 0x01) = 0 AND giant_table.some_other_id = 0; 2549id select_type table type possible_keys key key_len ref rows Extra 25501 SIMPLE giant_table range PRIMARY,some_other_id some_other_id 8 NULL 20 Using index condition; Using where 2551DROP PROCEDURE p; 2552DROP TABLE giant_table, t1; 2553set optimizer_switch=default; 2554