1drop table if exists t1, t2, t3; 2CREATE TABLE t1 ( 3event_date date DEFAULT '0000-00-00' NOT NULL, 4type int(11) DEFAULT '0' NOT NULL, 5event_id int(11) DEFAULT '0' NOT NULL, 6PRIMARY KEY (event_date,type,event_id) 7); 8INSERT INTO t1 VALUES ('1999-07-10',100100,24), ('1999-07-11',100100,25), 9('1999-07-13',100600,0), ('1999-07-13',100600,4), ('1999-07-13',100600,26), 10('1999-07-14',100600,10), ('1999-07-15',100600,16), ('1999-07-15',100800,45), 11('1999-07-15',101000,47), ('1999-07-16',100800,46), ('1999-07-20',100600,5), 12('1999-07-20',100600,27), ('1999-07-21',100600,11), ('1999-07-22',100600,17), 13('1999-07-23',100100,39), ('1999-07-24',100100,39), ('1999-07-24',100500,40), 14('1999-07-25',100100,39), ('1999-07-27',100600,1), ('1999-07-27',100600,6), 15('1999-07-27',100600,28), ('1999-07-28',100600,12), ('1999-07-29',100500,41), 16('1999-07-29',100600,18), ('1999-07-30',100500,41), ('1999-07-31',100500,41), 17('1999-08-01',100700,34), ('1999-08-03',100600,7), ('1999-08-03',100600,29), 18('1999-08-04',100600,13), ('1999-08-05',100500,42), ('1999-08-05',100600,19), 19('1999-08-06',100500,42), ('1999-08-07',100500,42), ('1999-08-08',100500,42), 20('1999-08-10',100600,2), ('1999-08-10',100600,9), ('1999-08-10',100600,30), 21('1999-08-11',100600,14), ('1999-08-12',100600,20), ('1999-08-17',100500,8), 22('1999-08-17',100600,31), ('1999-08-18',100600,15), ('1999-08-19',100600,22), 23('1999-08-24',100600,3), ('1999-08-24',100600,32), ('1999-08-27',100500,43), 24('1999-08-31',100600,33), ('1999-09-17',100100,37), ('1999-09-18',100100,37), 25('1999-09-19',100100,37), ('2000-12-18',100700,38); 26select 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; 27event_date type event_id 281999-07-10 100100 24 291999-07-11 100100 25 301999-07-13 100600 0 311999-07-13 100600 4 321999-07-13 100600 26 331999-07-14 100600 10 34explain 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; 35id select_type table type possible_keys key key_len ref rows Extra 361 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 37select 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; 38event_date type event_id 391999-07-10 100100 24 401999-07-11 100100 25 411999-07-13 100600 0 421999-07-13 100600 4 431999-07-13 100600 26 441999-07-14 100600 10 451999-07-15 100600 16 46drop table t1; 47CREATE TABLE t1 ( 48PAPER_ID smallint(6) DEFAULT '0' NOT NULL, 49YEAR smallint(6) DEFAULT '0' NOT NULL, 50ISSUE smallint(6) DEFAULT '0' NOT NULL, 51CLOSED tinyint(4) DEFAULT '0' NOT NULL, 52ISS_DATE date DEFAULT '0000-00-00' NOT NULL, 53PRIMARY KEY (PAPER_ID,YEAR,ISSUE) 54); 55INSERT INTO t1 VALUES (3,1999,34,0,'1999-07-12'), (1,1999,111,0,'1999-03-23'), 56(1,1999,222,0,'1999-03-23'), (3,1999,33,0,'1999-07-12'), 57(3,1999,32,0,'1999-07-12'), (3,1999,31,0,'1999-07-12'), 58(3,1999,30,0,'1999-07-12'), (3,1999,29,0,'1999-07-12'), 59(3,1999,28,0,'1999-07-12'), (1,1999,40,1,'1999-05-01'), 60(1,1999,41,1,'1999-05-01'), (1,1999,42,1,'1999-05-01'), 61(1,1999,46,1,'1999-05-01'), (1,1999,47,1,'1999-05-01'), 62(1,1999,48,1,'1999-05-01'), (1,1999,49,1,'1999-05-01'), 63(1,1999,50,0,'1999-05-01'), (1,1999,51,0,'1999-05-01'), 64(1,1999,200,0,'1999-06-28'), (1,1999,52,0,'1999-06-28'), 65(1,1999,53,0,'1999-06-28'), (1,1999,54,0,'1999-06-28'), 66(1,1999,55,0,'1999-06-28'), (1,1999,56,0,'1999-07-01'), 67(1,1999,57,0,'1999-07-01'), (1,1999,58,0,'1999-07-01'), 68(1,1999,59,0,'1999-07-01'), (1,1999,60,0,'1999-07-01'), 69(3,1999,35,0,'1999-07-12'); 70select YEAR,ISSUE from t1 where PAPER_ID=3 and (YEAR>1999 or (YEAR=1999 and ISSUE>28)) order by YEAR,ISSUE; 71YEAR ISSUE 721999 29 731999 30 741999 31 751999 32 761999 33 771999 34 781999 35 79check table t1; 80Table Op Msg_type Msg_text 81test.t1 check status OK 82repair table t1; 83Table Op Msg_type Msg_text 84test.t1 repair status OK 85drop table t1; 86CREATE TABLE t1 ( 87id int(11) NOT NULL auto_increment, 88parent_id int(11) DEFAULT '0' NOT NULL, 89level tinyint(4) DEFAULT '0' NOT NULL, 90PRIMARY KEY (id), 91KEY parent_id (parent_id), 92KEY level (level) 93); 94INSERT INTO t1 VALUES (1,0,0), (3,1,1), (4,1,1), (8,2,2), (9,2,2), (17,3,2), 95(22,4,2), (24,4,2), (28,5,2), (29,5,2), (30,5,2), (31,6,2), (32,6,2), (33,6,2), 96(203,7,2), (202,7,2), (20,3,2), (157,0,0), (193,5,2), (40,7,2), (2,1,1), 97(15,2,2), (6,1,1), (34,6,2), (35,6,2), (16,3,2), (7,1,1), (36,7,2), (18,3,2), 98(26,5,2), (27,5,2), (183,4,2), (38,7,2), (25,5,2), (37,7,2), (21,4,2), 99(19,3,2), (5,1,1), (179,5,2); 100SELECT * FROM t1 WHERE level = 1 AND parent_id = 1; 101id parent_id level 1023 1 1 1034 1 1 1042 1 1 1056 1 1 1067 1 1 1075 1 1 108SELECT * FROM t1 WHERE level = 1 AND parent_id = 1 order by id; 109id parent_id level 1102 1 1 1113 1 1 1124 1 1 1135 1 1 1146 1 1 1157 1 1 116drop table t1; 117create table t1( 118Satellite varchar(25) not null, 119SensorMode varchar(25) not null, 120FullImageCornersUpperLeftLongitude double not null, 121FullImageCornersUpperRightLongitude double not null, 122FullImageCornersUpperRightLatitude double not null, 123FullImageCornersLowerRightLatitude double not null, 124index two (Satellite, SensorMode, FullImageCornersUpperLeftLongitude, FullImageCornersUpperRightLongitude, FullImageCornersUpperRightLatitude, FullImageCornersLowerRightLatitude)); 125insert into t1 values("OV-3","PAN1",91,-92,40,50); 126insert into t1 values("OV-4","PAN1",91,-92,40,50); 127select * from t1 where t1.Satellite = "OV-3" and t1.SensorMode = "PAN1" and t1.FullImageCornersUpperLeftLongitude > -90.000000 and t1.FullImageCornersUpperRightLongitude < -82.000000; 128Satellite SensorMode FullImageCornersUpperLeftLongitude FullImageCornersUpperRightLongitude FullImageCornersUpperRightLatitude FullImageCornersLowerRightLatitude 129OV-3 PAN1 91 -92 40 50 130drop table t1; 131create table t1 ( aString char(100) not null default "", key aString (aString(10)) ); 132insert t1 (aString) values ( "believe in myself" ), ( "believe" ), ("baaa" ), ( "believe in love"); 133select * from t1 where aString < "believe in myself" order by aString; 134aString 135baaa 136believe 137believe in love 138select * from t1 where aString > "believe in love" order by aString; 139aString 140believe in myself 141alter table t1 drop key aString; 142select * from t1 where aString < "believe in myself" order by aString; 143aString 144baaa 145believe 146believe in love 147select * from t1 where aString > "believe in love" order by aString; 148aString 149believe in myself 150drop table t1; 151CREATE TABLE t1 ( 152t1ID int(10) unsigned NOT NULL auto_increment, 153art binary(1) NOT NULL default '', 154KNR char(5) NOT NULL default '', 155RECHNR char(6) NOT NULL default '', 156POSNR char(2) NOT NULL default '', 157ARTNR char(10) NOT NULL default '', 158TEX char(70) NOT NULL default '', 159PRIMARY KEY (t1ID), 160KEY IdxArt (art), 161KEY IdxKnr (KNR), 162KEY IdxArtnr (ARTNR) 163) ENGINE=MyISAM; 164INSERT INTO t1 (art) VALUES ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), 165('j'),('J'),('j'),('J'),('j'),('J'),('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'); 202select count(*) from t1 where upper(art) = 'J'; 203count(*) 204213 205select count(*) from t1 where art = 'J' or art = 'j'; 206count(*) 207602 208select count(*) from t1 where art = 'j' or art = 'J'; 209count(*) 210602 211select count(*) from t1 where art = 'j'; 212count(*) 213389 214select count(*) from t1 where art = 'J'; 215count(*) 216213 217drop table t1; 218create table t1 (x int, y int, index(x), index(y)); 219insert into t1 (x) values (1),(2),(3),(4),(5),(6),(7),(8),(9); 220update t1 set y=x; 221explain select * from t1, t1 t2 where t1.y = 8 and t2.x between 7 and t1.y+0; 222id select_type table type possible_keys key key_len ref rows Extra 2231 SIMPLE t1 ref y y 5 const 1 NULL 2241 SIMPLE t2 range x x 5 NULL 2 Using where; Using join buffer (Block Nested Loop) 225explain select * from t1, t1 t2 where t1.y = 8 and t2.x >= 7 and t2.x <= t1.y+0; 226id select_type table type possible_keys key key_len ref rows Extra 2271 SIMPLE t1 ref y y 5 const 1 NULL 2281 SIMPLE t2 range x x 5 NULL 2 Using where; Using join buffer (Block Nested Loop) 229explain select * from t1, t1 t2 where t1.y = 2 and t2.x between t1.y-1 and t1.y+1; 230id select_type table type possible_keys key key_len ref rows Extra 2311 SIMPLE t1 ref y y 5 const 1 NULL 2321 SIMPLE t2 range x x 5 NULL 3 Using where; Using join buffer (Block Nested Loop) 233explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= t1.y-1 and t2.x <= t1.y+1; 234id select_type table type possible_keys key key_len ref rows Extra 2351 SIMPLE t1 ref y y 5 const 1 NULL 2361 SIMPLE t2 range x x 5 NULL 3 Using where; Using join buffer (Block Nested Loop) 237explain select * from t1, t1 t2 where t1.y = 2 and t2.x between 0 and t1.y; 238id select_type table type possible_keys key key_len ref rows Extra 2391 SIMPLE t1 ref y y 5 const 1 NULL 2401 SIMPLE t2 range x x 5 NULL 2 Using where; Using join buffer (Block Nested Loop) 241explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= 0 and t2.x <= t1.y; 242id select_type table type possible_keys key key_len ref rows Extra 2431 SIMPLE t1 ref y y 5 const 1 NULL 2441 SIMPLE t2 range x x 5 NULL 2 Using where; Using join buffer (Block Nested Loop) 245explain select count(*) from t1 where x in (1); 246id select_type table type possible_keys key key_len ref rows Extra 2471 SIMPLE t1 ref x x 5 const 1 Using index 248explain select count(*) from t1 where x in (1,2); 249id select_type table type possible_keys key key_len ref rows Extra 2501 SIMPLE t1 index x x 5 NULL 9 Using where; Using index 251drop table t1; 252CREATE TABLE t1 (key1 int(11) NOT NULL default '0', KEY i1 (key1)); 253INSERT INTO t1 VALUES (0),(0),(0),(0),(0),(1),(1); 254CREATE TABLE t2 (keya int(11) NOT NULL default '0', KEY j1 (keya)); 255INSERT INTO t2 VALUES (0),(0),(1),(1),(2),(2); 256explain select * from t1, t2 where (t1.key1 <t2.keya + 1) and t2.keya=3; 257id select_type table type possible_keys key key_len ref rows Extra 2581 SIMPLE t2 ref j1 j1 4 const 1 Using index 2591 SIMPLE t1 index i1 i1 4 NULL 7 Using where; Using index; Using join buffer (Block Nested Loop) 260explain select * from t1 force index(i1), t2 force index(j1) where 261(t1.key1 <t2.keya + 1) and t2.keya=3; 262id select_type table type possible_keys key key_len ref rows Extra 2631 SIMPLE t2 ref j1 j1 4 const 1 Using index 2641 SIMPLE t1 index i1 i1 4 NULL 7 Using where; Using index; Using join buffer (Block Nested Loop) 265DROP TABLE t1,t2; 266CREATE TABLE t1 ( 267a int(11) default NULL, 268b int(11) default NULL, 269KEY a (a), 270KEY b (b) 271) ENGINE=MyISAM; 272INSERT INTO t1 VALUES 273(1,1),(2,1),(3,1),(4,1),(5,1),(6,1),(7,1),(8,1),(9,1),(10,2),(10,2), 274(13,2),(14,2),(15,2),(16,2),(17,3),(17,3),(16,3),(17,3),(19,3),(20,3), 275(21,4),(22,5),(23,5),(24,5),(25,5),(26,5),(30,5),(31,5),(32,5),(33,5), 276(33,5),(33,5),(33,5),(33,5),(34,5),(35,5); 277EXPLAIN SELECT * FROM t1 WHERE a IN(1,2) AND b=5; 278id select_type table type possible_keys key key_len ref rows Extra 2791 SIMPLE t1 range a,b a 5 NULL 2 Using where 280SELECT * FROM t1 WHERE a IN(1,2) AND b=5; 281a b 282DROP TABLE t1; 283CREATE TABLE t1 (a int, b int, c int, INDEX (c,a,b)); 284INSERT INTO t1 VALUES (1,0,0),(1,0,0),(1,0,0); 285INSERT INTO t1 VALUES (0,1,0),(0,1,0),(0,1,0); 286SELECT COUNT(*) FROM t1 WHERE (c=0 and a=1) or (c=0 and b=1); 287COUNT(*) 2886 289SELECT COUNT(*) FROM t1 WHERE (c=0 and b=1) or (c=0 and a=1); 290COUNT(*) 2916 292DROP TABLE t1; 293CREATE TABLE t1 ( a int not null, b int not null, INDEX ab(a,b) ); 294INSERT INTO t1 VALUES (47,1), (70,1), (15,1), (15, 4); 295SELECT * FROM t1 296WHERE 297( 298( b =1 AND a BETWEEN 14 AND 21 ) OR 299( b =2 AND a BETWEEN 16 AND 18 ) OR 300( b =3 AND a BETWEEN 15 AND 19 ) OR 301(a BETWEEN 19 AND 47) 302); 303a b 30415 1 30547 1 306DROP TABLE t1; 307CREATE TABLE t1 ( 308id int( 11 ) unsigned NOT NULL AUTO_INCREMENT , 309line int( 5 ) unsigned NOT NULL default '0', 310columnid int( 3 ) unsigned NOT NULL default '0', 311owner int( 3 ) unsigned NOT NULL default '0', 312ordinal int( 3 ) unsigned NOT NULL default '0', 313showid smallint( 6 ) unsigned NOT NULL default '1', 314tableid int( 1 ) unsigned NOT NULL default '1', 315content int( 5 ) unsigned NOT NULL default '188', 316PRIMARY KEY ( owner, id ) , 317KEY menu( owner, showid, columnid ) , 318KEY `COLUMN` ( owner, columnid, line ) , 319KEY `LINES` ( owner, tableid, content, id ) , 320KEY recount( owner, line ) 321) ENGINE = MYISAM; 322INSERT into t1 (owner,id,columnid,line) values (11,15,15,1),(11,13,13,5); 323SELECT 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; 324id columnid tableid content showid line ordinal 32513 13 1 188 1 5 0 32615 15 1 188 1 1 0 327drop table t1; 328create table t1 (id int(10) primary key); 329insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9); 330select id from t1 where id in (2,5,9) ; 331id 3322 3335 3349 335select id from t1 where id=2 or id=5 or id=9 ; 336id 3372 3385 3399 340drop table t1; 341create table t1 ( id1 int not null, id2 int not null, idnull int null, c char(20), primary key (id1,id2)); 342insert into t1 values (0,1,NULL,"aaa"), (1,1,NULL,"aaa"), (2,1,NULL,"aaa"), 343(3,1,NULL,"aaa"), (4,1,NULL,"aaa"), (5,1,NULL,"aaa"), 344(6,1,NULL,"aaa"), (7,1,NULL,"aaa"), (8,1,NULL,"aaa"), 345(9,1,NULL,"aaa"), (10,1,NULL,"aaa"), (11,1,NULL,"aaa"), 346(12,1,NULL,"aaa"), (13,1,NULL,"aaa"), (14,1,NULL,"aaa"), 347(15,1,NULL,"aaa"), (16,1,NULL,"aaa"), (17,1,NULL,"aaa"), 348(18,1,NULL,"aaa"), (19,1,NULL,"aaa"), (20,1,NULL,"aaa"); 349select 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; 350id1 idnull 351drop table t1; 352create table t1 ( 353id int not null auto_increment, 354name char(1) not null, 355uid int not null, 356primary key (id), 357index uid_index (uid)); 358create table t2 ( 359id int not null auto_increment, 360name char(1) not null, 361uid int not null, 362primary key (id), 363index uid_index (uid)); 364insert into t1(id, uid, name) values(1, 0, ' '); 365insert into t1(uid, name) values(0, ' '); 366insert into t2(uid, name) select uid, name from t1; 367insert into t1(uid, name) select uid, name from t2; 368insert into t2(uid, name) select uid, name from t1; 369insert into t1(uid, name) select uid, name from t2; 370insert into t2(uid, name) select uid, name from t1; 371insert into t1(uid, name) select uid, name from t2; 372insert into t2(uid, name) select uid, name from t1; 373insert into t1(uid, name) select uid, name from t2; 374insert into t2(uid, name) select uid, name from t1; 375insert into t1(uid, name) select uid, name from t2; 376insert into t2(uid, name) select uid, name from t1; 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 t1(uid, name) select uid, name from t2; 381delete from t2; 382insert into t2(uid, name) values 383(1, CHAR(64+1)), 384(2, CHAR(64+2)), 385(3, CHAR(64+3)), 386(4, CHAR(64+4)), 387(5, CHAR(64+5)), 388(6, CHAR(64+6)), 389(7, CHAR(64+7)), 390(8, CHAR(64+8)), 391(9, CHAR(64+9)), 392(10, CHAR(64+10)), 393(11, CHAR(64+11)), 394(12, CHAR(64+12)), 395(13, CHAR(64+13)), 396(14, CHAR(64+14)), 397(15, CHAR(64+15)), 398(16, CHAR(64+16)), 399(17, CHAR(64+17)), 400(18, CHAR(64+18)), 401(19, CHAR(64+19)), 402(20, CHAR(64+20)), 403(21, CHAR(64+21)), 404(22, CHAR(64+22)), 405(23, CHAR(64+23)), 406(24, CHAR(64+24)), 407(25, CHAR(64+25)), 408(26, CHAR(64+26)); 409insert into t1(uid, name) select uid, name from t2 order by uid; 410delete from t2; 411insert into t2(id, uid, name) select id, uid, name from t1; 412select count(*) from t1; 413count(*) 4141026 415select count(*) from t2; 416count(*) 4171026 418analyze table t1,t2; 419Table Op Msg_type Msg_text 420test.t1 analyze status OK 421test.t2 analyze status Table is already up to date 422explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0; 423id select_type table type possible_keys key key_len ref rows Extra 4241 SIMPLE t1 range uid_index uid_index 4 NULL 112 Using where 4251 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 38 NULL 426explain select * from t1, t2 where t1.uid=t2.uid AND t2.uid > 0; 427id select_type table type possible_keys key key_len ref rows Extra 4281 SIMPLE t1 range uid_index uid_index 4 NULL 112 Using where 4291 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 38 NULL 430explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid != 0; 431id select_type table type possible_keys key key_len ref rows Extra 4321 SIMPLE t1 range uid_index uid_index 4 NULL 113 Using where 4331 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 38 NULL 434explain select * from t1, t2 where t1.uid=t2.uid AND t2.uid != 0; 435id select_type table type possible_keys key key_len ref rows Extra 4361 SIMPLE t1 range uid_index uid_index 4 NULL 113 Using where 4371 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 38 NULL 438select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0; 439id name uid id name uid 4401001 A 1 1001 A 1 4411002 B 2 1002 B 2 4421003 C 3 1003 C 3 4431004 D 4 1004 D 4 4441005 E 5 1005 E 5 4451006 F 6 1006 F 6 4461007 G 7 1007 G 7 4471008 H 8 1008 H 8 4481009 I 9 1009 I 9 4491010 J 10 1010 J 10 4501011 K 11 1011 K 11 4511012 L 12 1012 L 12 4521013 M 13 1013 M 13 4531014 N 14 1014 N 14 4541015 O 15 1015 O 15 4551016 P 16 1016 P 16 4561017 Q 17 1017 Q 17 4571018 R 18 1018 R 18 4581019 S 19 1019 S 19 4591020 T 20 1020 T 20 4601021 U 21 1021 U 21 4611022 V 22 1022 V 22 4621023 W 23 1023 W 23 4631024 X 24 1024 X 24 4641025 Y 25 1025 Y 25 4651026 Z 26 1026 Z 26 466select * from t1, t2 where t1.uid=t2.uid AND t1.uid != 0; 467id name uid id name uid 4681001 A 1 1001 A 1 4691002 B 2 1002 B 2 4701003 C 3 1003 C 3 4711004 D 4 1004 D 4 4721005 E 5 1005 E 5 4731006 F 6 1006 F 6 4741007 G 7 1007 G 7 4751008 H 8 1008 H 8 4761009 I 9 1009 I 9 4771010 J 10 1010 J 10 4781011 K 11 1011 K 11 4791012 L 12 1012 L 12 4801013 M 13 1013 M 13 4811014 N 14 1014 N 14 4821015 O 15 1015 O 15 4831016 P 16 1016 P 16 4841017 Q 17 1017 Q 17 4851018 R 18 1018 R 18 4861019 S 19 1019 S 19 4871020 T 20 1020 T 20 4881021 U 21 1021 U 21 4891022 V 22 1022 V 22 4901023 W 23 1023 W 23 4911024 X 24 1024 X 24 4921025 Y 25 1025 Y 25 4931026 Z 26 1026 Z 26 494drop table t1,t2; 495create table t1 (x bigint unsigned not null); 496insert into t1(x) values (0xfffffffffffffff0); 497insert into t1(x) values (0xfffffffffffffff1); 498select * from t1; 499x 50018446744073709551600 50118446744073709551601 502select count(*) from t1 where x>0; 503count(*) 5042 505select count(*) from t1 where x=0; 506count(*) 5070 508select count(*) from t1 where x<0; 509count(*) 5100 511select count(*) from t1 where x < -16; 512count(*) 5130 514select count(*) from t1 where x = -16; 515count(*) 5160 517select count(*) from t1 where x > -16; 518count(*) 5192 520select count(*) from t1 where x = 18446744073709551601; 521count(*) 5221 523create table t2 (x bigint not null); 524insert into t2(x) values (-16); 525insert into t2(x) values (-15); 526select * from t2; 527x 528-16 529-15 530select count(*) from t2 where x>0; 531count(*) 5320 533select count(*) from t2 where x=0; 534count(*) 5350 536select count(*) from t2 where x<0; 537count(*) 5382 539select count(*) from t2 where x < -16; 540count(*) 5410 542select count(*) from t2 where x = -16; 543count(*) 5441 545select count(*) from t2 where x > -16; 546count(*) 5471 548select count(*) from t2 where x = 18446744073709551601; 549count(*) 5500 551drop table t1,t2; 552create table t1 (x bigint unsigned not null primary key) engine=innodb; 553insert into t1(x) values (0xfffffffffffffff0); 554insert into t1(x) values (0xfffffffffffffff1); 555select * from t1; 556x 55718446744073709551600 55818446744073709551601 559select count(*) from t1 where x>0; 560count(*) 5612 562select count(*) from t1 where x=0; 563count(*) 5640 565select count(*) from t1 where x<0; 566count(*) 5670 568select count(*) from t1 where x < -16; 569count(*) 5700 571select count(*) from t1 where x = -16; 572count(*) 5730 574select count(*) from t1 where x > -16; 575count(*) 5762 577select count(*) from t1 where x = 18446744073709551601; 578count(*) 5791 580drop table t1; 581create table t1 (a bigint unsigned); 582create index t1i on t1(a); 583insert into t1 select 18446744073709551615; 584insert into t1 select 18446744073709551614; 585explain select * from t1 where a <> -1; 586id select_type table type possible_keys key key_len ref rows Extra 5871 SIMPLE t1 index t1i t1i 9 NULL 2 Using where; Using index 588select * from t1 where a <> -1; 589a 59018446744073709551614 59118446744073709551615 592explain select * from t1 where a > -1 or a < -1; 593id select_type table type possible_keys key key_len ref rows Extra 5941 SIMPLE t1 index t1i t1i 9 NULL 2 Using where; Using index 595select * from t1 where a > -1 or a < -1; 596a 59718446744073709551614 59818446744073709551615 599explain select * from t1 where a > -1; 600id select_type table type possible_keys key key_len ref rows Extra 6011 SIMPLE t1 index t1i t1i 9 NULL 2 Using where; Using index 602select * from t1 where a > -1; 603a 60418446744073709551614 60518446744073709551615 606explain select * from t1 where a < -1; 607id select_type table type possible_keys key key_len ref rows Extra 6081 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 609select * from t1 where a < -1; 610a 611drop table t1; 612set names latin1; 613create table t1 (a char(10), b text, key (a)) character set latin1; 614INSERT INTO t1 (a) VALUES 615('111'),('222'),('222'),('222'),('222'),('444'),('aaa'),('AAA'),('bbb'); 616explain select * from t1 where a='aaa'; 617id select_type table type possible_keys key key_len ref rows Extra 6181 SIMPLE t1 ref a a 11 const 2 Using where 619explain select * from t1 where a=binary 'aaa'; 620id select_type table type possible_keys key key_len ref rows Extra 6211 SIMPLE t1 range a a 11 NULL 2 Using where 622explain select * from t1 where a='aaa' collate latin1_bin; 623id select_type table type possible_keys key key_len ref rows Extra 6241 SIMPLE t1 range a a 11 NULL 2 Using where 625explain select * from t1 where a='aaa' collate latin1_german1_ci; 626id select_type table type possible_keys key key_len ref rows Extra 6271 SIMPLE t1 ALL a NULL NULL NULL 9 Using where 628drop table t1; 629CREATE TABLE t1 ( 630`CLIENT` char(3) character set latin1 collate latin1_bin NOT NULL default '000', 631`ARG1` char(3) character set latin1 collate latin1_bin NOT NULL default '', 632`ARG2` char(3) character set latin1 collate latin1_bin NOT NULL default '', 633`FUNCTION` varchar(10) character set latin1 collate latin1_bin NOT NULL default '', 634`FUNCTINT` int(11) NOT NULL default '0', 635KEY `VERI_CLNT~2` (`ARG1`) 636) ENGINE=InnoDB DEFAULT CHARSET=latin1; 637INSERT INTO t1 VALUES ('000',' 0',' 0','Text 001',0), ('000',' 0',' 1','Text 002',0), 638('000',' 1',' 2','Text 003',0), ('000',' 2',' 3','Text 004',0), 639('001',' 3',' 0','Text 017',0); 640SELECT count(*) FROM t1 WHERE CLIENT='000' AND (ARG1 != ' 1' OR ARG1 != ' 2'); 641count(*) 6424 643SELECT count(*) FROM t1 WHERE CLIENT='000' AND (ARG1 != ' 2' OR ARG1 != ' 1'); 644count(*) 6454 646drop table t1; 647create table t1 (a int); 648insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 649CREATE TABLE t2 ( 650pk1 int(11) NOT NULL, 651pk2 int(11) NOT NULL, 652pk3 int(11) NOT NULL, 653pk4 int(11) NOT NULL, 654filler char(82), 655PRIMARY KEY (pk1,pk2,pk3,pk4) 656) DEFAULT CHARSET=latin1; 657insert into t2 select 1, A.a+10*B.a, 432, 44, 'fillerZ' from t1 A, t1 B; 658INSERT INTO t2 VALUES (2621, 2635, 0, 0,'filler'), (2621, 2635, 1, 0,'filler'), 659(2621, 2635, 10, 0,'filler'), (2621, 2635, 11, 0,'filler'), 660(2621, 2635, 14, 0,'filler'), (2621, 2635, 1000015, 0,'filler'); 661SELECT * FROM t2 662WHERE ((((pk4 =0) AND (pk1 =2621) AND (pk2 =2635))) 663OR ((pk4 =1) AND (((pk1 IN ( 7, 2, 1 ))) OR (pk1 =522)) AND ((pk2 IN ( 0, 2635)))) 664) AND (pk3 >=1000000); 665pk1 pk2 pk3 pk4 filler 6662621 2635 1000015 0 filler 667drop table t1, t2; 668create table t1(a char(2), key(a(1))); 669insert into t1 values ('x'), ('xx'); 670explain select a from t1 where a > 'x'; 671id select_type table type possible_keys key key_len ref rows Extra 6721 SIMPLE t1 range a a 2 NULL 2 Using where 673select a from t1 where a > 'x'; 674a 675xx 676drop table t1; 677CREATE TABLE t1 ( 678OXID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 679OXPARENTID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT 'oxrootid', 680OXLEFT int NOT NULL DEFAULT '0', 681OXRIGHT int NOT NULL DEFAULT '0', 682OXROOTID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 683PRIMARY KEY (OXID), 684KEY OXNID (OXID), 685KEY OXLEFT (OXLEFT), 686KEY OXRIGHT (OXRIGHT), 687KEY OXROOTID (OXROOTID) 688) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci; 689INSERT INTO t1 VALUES 690('d8c4177d09f8b11f5.52725521','oxrootid',1,40,'d8c4177d09f8b11f5.52725521'), 691('d8c4177d151affab2.81582770','d8c4177d09f8b11f5.52725521',2,3, 692'd8c4177d09f8b11f5.52725521'), 693('d8c4177d206a333d2.74422679','d8c4177d09f8b11f5.52725521',4,5, 694'd8c4177d09f8b11f5.52725521'), 695('d8c4177d225791924.30714720','d8c4177d09f8b11f5.52725521',6,7, 696'd8c4177d09f8b11f5.52725521'), 697('d8c4177d2380fc201.39666693','d8c4177d09f8b11f5.52725521',8,9, 698'd8c4177d09f8b11f5.52725521'), 699('d8c4177d24ccef970.14957924','d8c4177d09f8b11f5.52725521',10,11, 700'd8c4177d09f8b11f5.52725521'); 701EXPLAIN 702SELECT s.oxid FROM t1 v, t1 s 703WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND 704v.oxrootid ='d8c4177d09f8b11f5.52725521' AND 705s.oxleft > v.oxleft AND s.oxleft < v.oxright; 706id select_type table type possible_keys key key_len ref rows Extra 7071 SIMPLE v ref OXLEFT,OXRIGHT,OXROOTID OXROOTID 34 const 5 Using where 7081 SIMPLE s ALL OXLEFT NULL NULL NULL 6 Range checked for each record (index map: 0x4) 709SELECT s.oxid FROM t1 v, t1 s 710WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND 711v.oxrootid ='d8c4177d09f8b11f5.52725521' AND 712s.oxleft > v.oxleft AND s.oxleft < v.oxright; 713oxid 714d8c4177d151affab2.81582770 715d8c4177d206a333d2.74422679 716d8c4177d225791924.30714720 717d8c4177d2380fc201.39666693 718d8c4177d24ccef970.14957924 719DROP TABLE t1; 720create table t1 ( 721c1 char(10), c2 char(10), c3 char(10), c4 char(10), 722c5 char(10), c6 char(10), c7 char(10), c8 char(10), 723c9 char(10), c10 char(10), c11 char(10), c12 char(10), 724c13 char(10), c14 char(10), c15 char(10), c16 char(10), 725index(c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12,c13,c14,c15,c16) 726); 727insert into t1 (c1) values ('1'),('1'),('1'),('1'); 728select * from t1 where 729c1 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 730"abcdefg1", "123456781", "qwertyui1", "asddfg1", 731"abcdefg2", "123456782", "qwertyui2", "asddfg2", 732"abcdefg3", "123456783", "qwertyui3", "asddfg3", 733"abcdefg4", "123456784", "qwertyui4", "asddfg4", 734"abcdefg5", "123456785", "qwertyui5", "asddfg5", 735"abcdefg6", "123456786", "qwertyui6", "asddfg6", 736"abcdefg7", "123456787", "qwertyui7", "asddfg7", 737"abcdefg8", "123456788", "qwertyui8", "asddfg8", 738"abcdefg9", "123456789", "qwertyui9", "asddfg9", 739"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", 740"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", 741"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") 742and c2 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 743"abcdefg1", "123456781", "qwertyui1", "asddfg1", 744"abcdefg2", "123456782", "qwertyui2", "asddfg2", 745"abcdefg3", "123456783", "qwertyui3", "asddfg3", 746"abcdefg4", "123456784", "qwertyui4", "asddfg4", 747"abcdefg5", "123456785", "qwertyui5", "asddfg5", 748"abcdefg6", "123456786", "qwertyui6", "asddfg6", 749"abcdefg7", "123456787", "qwertyui7", "asddfg7", 750"abcdefg8", "123456788", "qwertyui8", "asddfg8", 751"abcdefg9", "123456789", "qwertyui9", "asddfg9", 752"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", 753"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", 754"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") 755and c3 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 756"abcdefg1", "123456781", "qwertyui1", "asddfg1", 757"abcdefg2", "123456782", "qwertyui2", "asddfg2", 758"abcdefg3", "123456783", "qwertyui3", "asddfg3", 759"abcdefg4", "123456784", "qwertyui4", "asddfg4", 760"abcdefg5", "123456785", "qwertyui5", "asddfg5", 761"abcdefg6", "123456786", "qwertyui6", "asddfg6", 762"abcdefg7", "123456787", "qwertyui7", "asddfg7", 763"abcdefg8", "123456788", "qwertyui8", "asddfg8", 764"abcdefg9", "123456789", "qwertyui9", "asddfg9", 765"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", 766"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", 767"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") 768and c4 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 769"abcdefg1", "123456781", "qwertyui1", "asddfg1", 770"abcdefg2", "123456782", "qwertyui2", "asddfg2", 771"abcdefg3", "123456783", "qwertyui3", "asddfg3", 772"abcdefg4", "123456784", "qwertyui4", "asddfg4", 773"abcdefg5", "123456785", "qwertyui5", "asddfg5", 774"abcdefg6", "123456786", "qwertyui6", "asddfg6", 775"abcdefg7", "123456787", "qwertyui7", "asddfg7", 776"abcdefg8", "123456788", "qwertyui8", "asddfg8", 777"abcdefg9", "123456789", "qwertyui9", "asddfg9", 778"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", 779"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", 780"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") 781and c5 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 782"abcdefg1", "123456781", "qwertyui1", "asddfg1", 783"abcdefg2", "123456782", "qwertyui2", "asddfg2", 784"abcdefg3", "123456783", "qwertyui3", "asddfg3", 785"abcdefg4", "123456784", "qwertyui4", "asddfg4", 786"abcdefg5", "123456785", "qwertyui5", "asddfg5", 787"abcdefg6", "123456786", "qwertyui6", "asddfg6", 788"abcdefg7", "123456787", "qwertyui7", "asddfg7", 789"abcdefg8", "123456788", "qwertyui8", "asddfg8", 790"abcdefg9", "123456789", "qwertyui9", "asddfg9", 791"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", 792"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", 793"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") 794and c6 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 795"abcdefg1", "123456781", "qwertyui1", "asddfg1", 796"abcdefg2", "123456782", "qwertyui2", "asddfg2", 797"abcdefg3", "123456783", "qwertyui3", "asddfg3", 798"abcdefg4", "123456784", "qwertyui4", "asddfg4", 799"abcdefg5", "123456785", "qwertyui5", "asddfg5", 800"abcdefg6", "123456786", "qwertyui6", "asddfg6", 801"abcdefg7", "123456787", "qwertyui7", "asddfg7", 802"abcdefg8", "123456788", "qwertyui8", "asddfg8", 803"abcdefg9", "123456789", "qwertyui9", "asddfg9", 804"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", 805"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", 806"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") 807and c7 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 808"abcdefg1", "123456781", "qwertyui1", "asddfg1", 809"abcdefg2", "123456782", "qwertyui2", "asddfg2", 810"abcdefg3", "123456783", "qwertyui3", "asddfg3", 811"abcdefg4", "123456784", "qwertyui4", "asddfg4", 812"abcdefg5", "123456785", "qwertyui5", "asddfg5", 813"abcdefg6", "123456786", "qwertyui6", "asddfg6", 814"abcdefg7", "123456787", "qwertyui7", "asddfg7", 815"abcdefg8", "123456788", "qwertyui8", "asddfg8", 816"abcdefg9", "123456789", "qwertyui9", "asddfg9", 817"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", 818"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", 819"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") 820and c8 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 821"abcdefg1", "123456781", "qwertyui1", "asddfg1", 822"abcdefg2", "123456782", "qwertyui2", "asddfg2", 823"abcdefg3", "123456783", "qwertyui3", "asddfg3", 824"abcdefg4", "123456784", "qwertyui4", "asddfg4", 825"abcdefg5", "123456785", "qwertyui5", "asddfg5", 826"abcdefg6", "123456786", "qwertyui6", "asddfg6", 827"abcdefg7", "123456787", "qwertyui7", "asddfg7", 828"abcdefg8", "123456788", "qwertyui8", "asddfg8", 829"abcdefg9", "123456789", "qwertyui9", "asddfg9", 830"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", 831"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", 832"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") 833and c9 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 834"abcdefg1", "123456781", "qwertyui1", "asddfg1", 835"abcdefg2", "123456782", "qwertyui2", "asddfg2", 836"abcdefg3", "123456783", "qwertyui3", "asddfg3", 837"abcdefg4", "123456784", "qwertyui4", "asddfg4", 838"abcdefg5", "123456785", "qwertyui5", "asddfg5", 839"abcdefg6", "123456786", "qwertyui6", "asddfg6", 840"abcdefg7", "123456787", "qwertyui7", "asddfg7", 841"abcdefg8", "123456788", "qwertyui8", "asddfg8", 842"abcdefg9", "123456789", "qwertyui9", "asddfg9", 843"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", 844"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", 845"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") 846and c10 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 847"abcdefg1", "123456781", "qwertyui1", "asddfg1", 848"abcdefg2", "123456782", "qwertyui2", "asddfg2", 849"abcdefg3", "123456783", "qwertyui3", "asddfg3", 850"abcdefg4", "123456784", "qwertyui4", "asddfg4", 851"abcdefg5", "123456785", "qwertyui5", "asddfg5", 852"abcdefg6", "123456786", "qwertyui6", "asddfg6", 853"abcdefg7", "123456787", "qwertyui7", "asddfg7", 854"abcdefg8", "123456788", "qwertyui8", "asddfg8", 855"abcdefg9", "123456789", "qwertyui9", "asddfg9", 856"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", 857"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", 858"abcdefgC", "12345678C", "qwertyuiC", "asddfgC"); 859c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 c13 c14 c15 c16 860drop table t1; 861End of 4.1 tests 862CREATE TABLE t1 ( 863id int(11) NOT NULL auto_increment, 864status varchar(20), 865PRIMARY KEY (id), 866KEY (status) 867); 868INSERT INTO t1 VALUES 869(1,'B'), (2,'B'), (3,'B'), (4,'B'), (5,'B'), (6,'B'), 870(7,'B'), (8,'B'), (9,'B'), (10,'B'), (11,'B'), (12,'B'), 871(13,'B'), (14,'B'), (15,'B'), (16,'B'), (17,'B'), (18,'B'), 872(19,'B'), (20,'B'), (21,'B'), (22,'B'), (23,'B'), (24,'B'), 873(25,'A'), (26,'A'), (27,'A'), (28,'A'), (29,'A'), (30,'A'), 874(31,'A'), (32,'A'), (33,'A'), (34,'A'), (35,'A'), (36,'A'), 875(37,'A'), (38,'A'), (39,'A'), (40,'A'), (41,'A'), (42,'A'), 876(43,'A'), (44,'A'), (45,'A'), (46,'A'), (47,'A'), (48,'A'), 877(49,'A'), (50,'A'), (51,'A'), (52,'A'), (53,'C'), (54,'C'), 878(55,'C'), (56,'C'), (57,'C'), (58,'C'), (59,'C'), (60,'C'); 879EXPLAIN SELECT * FROM t1 WHERE status <> 'A' AND status <> 'B'; 880id select_type table type possible_keys key key_len ref rows Extra 8811 SIMPLE t1 range status status 23 NULL 11 Using where 882EXPLAIN SELECT * FROM t1 WHERE status NOT IN ('A','B'); 883id select_type table type possible_keys key key_len ref rows Extra 8841 SIMPLE t1 range status status 23 NULL 11 Using where 885SELECT * FROM t1 WHERE status <> 'A' AND status <> 'B'; 886id status 88753 C 88854 C 88955 C 89056 C 89157 C 89258 C 89359 C 89460 C 895SELECT * FROM t1 WHERE status NOT IN ('A','B'); 896id status 89753 C 89854 C 89955 C 90056 C 90157 C 90258 C 90359 C 90460 C 905EXPLAIN SELECT status FROM t1 WHERE status <> 'A' AND status <> 'B'; 906id select_type table type possible_keys key key_len ref rows Extra 9071 SIMPLE t1 range status status 23 NULL 11 Using where; Using index 908EXPLAIN SELECT status FROM t1 WHERE status NOT IN ('A','B'); 909id select_type table type possible_keys key key_len ref rows Extra 9101 SIMPLE t1 range status status 23 NULL 11 Using where; Using index 911EXPLAIN SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B'; 912id select_type table type possible_keys key key_len ref rows Extra 9131 SIMPLE t1 range status status 23 NULL 10 Using where 914EXPLAIN SELECT * FROM t1 WHERE status < 'A' OR status > 'B'; 915id select_type table type possible_keys key key_len ref rows Extra 9161 SIMPLE t1 range status status 23 NULL 10 Using where 917SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B'; 918id status 91953 C 92054 C 92155 C 92256 C 92357 C 92458 C 92559 C 92660 C 927SELECT * FROM t1 WHERE status < 'A' OR status > 'B'; 928id status 92953 C 93054 C 93155 C 93256 C 93357 C 93458 C 93559 C 93660 C 937DROP TABLE t1; 938CREATE TABLE t1 (a int, b int, primary key(a,b)); 939INSERT INTO t1 VALUES 940(1,1),(1,2),(1,3),(2,1),(2,2),(2,3),(3,1),(3,2),(3,3),(4,1),(4,2),(4,3); 941CREATE VIEW v1 as SELECT a,b FROM t1 WHERE b=3; 942EXPLAIN SELECT a,b FROM t1 WHERE a < 2 and b=3; 943id select_type table type possible_keys key key_len ref rows Extra 9441 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where; Using index 945EXPLAIN SELECT a,b FROM v1 WHERE a < 2 and b=3; 946id select_type table type possible_keys key key_len ref rows Extra 9471 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where; Using index 948EXPLAIN SELECT a,b FROM t1 WHERE a < 2; 949id select_type table type possible_keys key key_len ref rows Extra 9501 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where; Using index 951EXPLAIN SELECT a,b FROM v1 WHERE a < 2; 952id select_type table type possible_keys key key_len ref rows Extra 9531 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where; Using index 954SELECT a,b FROM t1 WHERE a < 2 and b=3; 955a b 9561 3 957SELECT a,b FROM v1 WHERE a < 2 and b=3; 958a b 9591 3 960DROP VIEW v1; 961DROP TABLE t1; 962CREATE TABLE t1 (name varchar(15) NOT NULL, KEY idx(name)); 963INSERT INTO t1 VALUES ('Betty'), ('Anna'); 964SELECT * FROM t1; 965name 966Anna 967Betty 968DELETE FROM t1 WHERE name NOT LIKE 'A%a'; 969SELECT * FROM t1; 970name 971Anna 972DROP TABLE t1; 973CREATE TABLE t1 (a int, KEY idx(a)); 974INSERT INTO t1 VALUES (NULL), (1), (2), (3); 975SELECT * FROM t1; 976a 977NULL 9781 9792 9803 981DELETE FROM t1 WHERE NOT(a <=> 2); 982SELECT * FROM t1; 983a 9842 985DROP TABLE t1; 986create table t1 (a int, b int, primary key(a,b)); 987create view v1 as select a, b from t1; 988INSERT INTO `t1` VALUES 989(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) 990,(13,2),(14,2),(15,3),(16,3),(17,3),(18,3),(19,3); 991explain select * from t1 where a in (3,4) and b in (1,2,3); 992id select_type table type possible_keys key key_len ref rows Extra 9931 SIMPLE t1 range PRIMARY PRIMARY 8 NULL # Using where; Using index 994explain select * from v1 where a in (3,4) and b in (1,2,3); 995id select_type table type possible_keys key key_len ref rows Extra 9961 SIMPLE t1 range PRIMARY PRIMARY 8 NULL # Using where; Using index 997explain select * from t1 where a between 3 and 4 and b between 1 and 2; 998id select_type table type possible_keys key key_len ref rows Extra 9991 SIMPLE t1 range PRIMARY PRIMARY 8 NULL # Using where; Using index 1000explain select * from v1 where a between 3 and 4 and b between 1 and 2; 1001id select_type table type possible_keys key key_len ref rows Extra 10021 SIMPLE t1 range PRIMARY PRIMARY 8 NULL # Using where; Using index 1003drop view v1; 1004drop table t1; 1005create table t3 (a int); 1006insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1007create table t1 (a varchar(10), filler char(200), key(a)) charset=binary; 1008insert into t1 values ('a',''); 1009insert into t1 values ('a ',''); 1010insert into t1 values ('a ', ''); 1011insert into t1 select concat('a', 1000 + A.a + 10 * (B.a + 10 * C.a)), '' 1012 from t3 A, t3 B, t3 C; 1013create table t2 (a varchar(10), filler char(200), key(a)); 1014insert into t2 select * from t1; 1015explain select * from t1 where a between 'a' and 'a '; 1016id select_type table type possible_keys key key_len ref rows Extra 10171 SIMPLE t1 range a a 13 NULL # Using where 1018explain select * from t1 where a = 'a' or a='a '; 1019id select_type table type possible_keys key key_len ref rows Extra 10201 SIMPLE t1 range a a 13 NULL # Using where 1021explain select * from t2 where a between 'a' and 'a '; 1022id select_type table type possible_keys key key_len ref rows Extra 10231 SIMPLE t2 ref a a 13 const # Using where 1024explain select * from t2 where a = 'a' or a='a '; 1025id select_type table type possible_keys key key_len ref rows Extra 10261 SIMPLE t2 ref a a 13 const # Using where 1027update t1 set a='b' where a<>'a'; 1028explain select * from t1 where a not between 'b' and 'b'; 1029id select_type table type possible_keys key key_len ref rows Extra 10301 SIMPLE t1 range a a 13 NULL # Using where 1031select a, hex(filler) from t1 where a not between 'b' and 'b'; 1032a hex(filler) 1033a 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 1034drop table t1,t2,t3; 1035create table t1 (a int); 1036insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1037create table t2 (a int, key(a)); 1038insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C; 1039set @a="select * from t2 force index (a) where a NOT IN(0"; 1040select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z; 1041count(*) 10421000 1043set @a=concat(@a, ')'); 1044insert into t2 values (11),(13),(15); 1045set @b= concat("explain ", @a); 1046prepare stmt1 from @b; 1047execute stmt1; 1048id select_type table type possible_keys key key_len ref rows Extra 10491 SIMPLE t2 index a a 5 NULL 1003 Using where; Using index 1050prepare stmt1 from @a; 1051execute stmt1; 1052a 105311 105413 105515 1056drop table t1, t2; 1057CREATE TABLE t1 ( 1058id int NOT NULL DEFAULT '0', 1059b int NOT NULL DEFAULT '0', 1060c int NOT NULL DEFAULT '0', 1061INDEX idx1(b,c), INDEX idx2(c)); 1062INSERT INTO t1(id) VALUES (1), (2), (3), (4), (5), (6), (7), (8); 1063INSERT INTO t1(b,c) VALUES (3,4), (3,4); 1064SELECT * FROM t1 WHERE b<=3 AND 3<=c; 1065id b c 10660 3 4 10670 3 4 1068SELECT * FROM t1 WHERE 3 BETWEEN b AND c; 1069id b c 10700 3 4 10710 3 4 1072EXPLAIN SELECT * FROM t1 WHERE b<=3 AND 3<=c; 1073id select_type table type possible_keys key key_len ref rows Extra 10741 SIMPLE t1 range idx1,idx2 idx2 4 NULL 3 Using where 1075EXPLAIN SELECT * FROM t1 WHERE 3 BETWEEN b AND c; 1076id select_type table type possible_keys key key_len ref rows Extra 10771 SIMPLE t1 range idx1,idx2 idx2 4 NULL 3 Using where 1078SELECT * FROM t1 WHERE 0 < b OR 0 > c; 1079id b c 10800 3 4 10810 3 4 1082SELECT * FROM t1 WHERE 0 NOT BETWEEN b AND c; 1083id b c 10840 3 4 10850 3 4 1086EXPLAIN SELECT * FROM t1 WHERE 0 < b OR 0 > c; 1087id select_type table type possible_keys key key_len ref rows Extra 10881 SIMPLE t1 ALL idx1,idx2 NULL NULL NULL 10 Using where 1089EXPLAIN SELECT * FROM t1 WHERE 0 NOT BETWEEN b AND c; 1090id select_type table type possible_keys key key_len ref rows Extra 10911 SIMPLE t1 ALL idx1,idx2 NULL NULL NULL 10 Using where 1092DROP TABLE t1; 1093CREATE TABLE t1 ( 1094item char(20) NOT NULL default '', 1095started datetime NOT NULL default '0000-00-00 00:00:00', 1096price decimal(16,3) NOT NULL default '0.000', 1097PRIMARY KEY (item,started) 1098) ENGINE=MyISAM; 1099INSERT INTO t1 VALUES 1100('A1','2005-11-01 08:00:00',1000), 1101('A1','2005-11-15 00:00:00',2000), 1102('A1','2005-12-12 08:00:00',3000), 1103('A2','2005-12-01 08:00:00',1000); 1104EXPLAIN SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; 1105id select_type table type possible_keys key key_len ref rows Extra 11061 SIMPLE t1 ref PRIMARY PRIMARY 20 const 2 Using where 1107Warnings: 1108Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 1109Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 1110SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; 1111item started price 1112A1 2005-11-01 08:00:00 1000.000 1113A1 2005-11-15 00:00:00 2000.000 1114Warnings: 1115Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 1116Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 1117SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-02 00:00:00'; 1118item started price 1119A1 2005-11-01 08:00:00 1000.000 1120A1 2005-11-15 00:00:00 2000.000 1121DROP INDEX `PRIMARY` ON t1; 1122EXPLAIN SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; 1123id select_type table type possible_keys key key_len ref rows Extra 11241 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where 1125Warnings: 1126Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 1127SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; 1128item started price 1129A1 2005-11-01 08:00:00 1000.000 1130A1 2005-11-15 00:00:00 2000.000 1131Warnings: 1132Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 1133SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-02 00:00:00'; 1134item started price 1135A1 2005-11-01 08:00:00 1000.000 1136A1 2005-11-15 00:00:00 2000.000 1137DROP TABLE t1; 1138 1139BUG#32198 "Comparison of DATE with DATETIME still not using indexes correctly" 1140 1141CREATE TABLE t1 ( 1142id int(11) NOT NULL auto_increment, 1143dateval date default NULL, 1144PRIMARY KEY (id), 1145KEY dateval (dateval) 1146) AUTO_INCREMENT=173; 1147INSERT INTO t1 VALUES 1148(1,'2007-01-01'),(2,'2007-01-02'),(3,'2007-01-03'),(4,'2007-01-04'), 1149(5,'2007-01-05'),(6,'2007-01-06'),(7,'2007-01-07'),(8,'2007-01-08'), 1150(9,'2007-01-09'),(10,'2007-01-10'),(11,'2007-01-11'); 1151This must use range access: 1152explain select * from t1 where dateval >= '2007-01-01 00:00:00' and dateval <= '2007-01-02 23:59:59'; 1153id select_type table type possible_keys key key_len ref rows Extra 11541 SIMPLE t1 range dateval dateval 4 NULL 2 Using where 1155drop table t1; 1156CREATE TABLE t1 ( 1157a varchar(32), index (a) 1158) DEFAULT CHARSET=latin1 COLLATE=latin1_bin; 1159INSERT INTO t1 VALUES 1160('B'), ('A'), ('A'), ('C'), ('B'), ('A'), ('A'); 1161SELECT a FROM t1 WHERE a='b' OR a='B'; 1162a 1163B 1164B 1165EXPLAIN SELECT a FROM t1 WHERE a='b' OR a='B'; 1166id select_type table type possible_keys key key_len ref rows Extra 11671 SIMPLE t1 range a a 35 NULL 3 Using where; Using index 1168DROP TABLE t1; 1169CREATE TABLE t1 (f1 TINYINT(11) UNSIGNED NOT NULL, PRIMARY KEY (f1)); 1170INSERT INTO t1 VALUES (127),(254),(0),(1),(255); 1171SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 256; 1172COUNT(*) 11735 1174SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 256.0; 1175COUNT(*) 11765 1177SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 255; 1178COUNT(*) 11794 1180SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < -1; 1181COUNT(*) 11820 1183SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -1; 1184COUNT(*) 11855 1186DROP TABLE t1; 1187CREATE TABLE t1 ( f1 TINYINT(11) NOT NULL, PRIMARY KEY (f1)); 1188INSERT INTO t1 VALUES (127),(126),(0),(-128),(-127); 1189SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 128; 1190COUNT(*) 11915 1192SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 128.0; 1193COUNT(*) 11945 1195SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 127; 1196COUNT(*) 11974 1198SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -129; 1199COUNT(*) 12005 1201SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -129.0; 1202COUNT(*) 12035 1204SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -128; 1205COUNT(*) 12064 1207DROP TABLE t1; 1208create table t1 (a int); 1209insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1210create table t2 (a int, b int, filler char(100)); 1211insert into t2 select A.a + 10 * (B.a + 10 * C.a), 10, 'filler' from t1 A, 1212t1 B, t1 C where A.a < 5; 1213insert into t2 select 1000, b, 'filler' from t2; 1214alter table t2 add index (a,b); 1215select 'In following EXPLAIN the access method should be ref or range, #rows~=500 (and not 2)' Z; 1216Z 1217In following EXPLAIN the access method should be ref or range, #rows~=500 (and not 2) 1218explain select * from t2 where a=1000 and b<11; 1219id select_type table type possible_keys key key_len ref rows Extra 12201 SIMPLE t2 ref a a 5 const 502 Using where 1221drop table t1, t2; 1222CREATE TABLE t1( a INT, b INT, KEY( a, b ) ); 1223CREATE TABLE t2( a INT, b INT, KEY( a, b ) ); 1224CREATE TABLE t3( a INT, b INT, KEY( a, b ) ); 1225INSERT INTO t1( a, b ) 1226VALUES (0, 1), (1, 2), (1, 4), (2, 3), (5, 0), (9, 7); 1227INSERT INTO t2( a, b ) 1228VALUES ( 1, 1), ( 2, 1), ( 3, 1), ( 4, 1), ( 5, 1), 1229( 6, 1), ( 7, 1), ( 8, 1), ( 9, 1), (10, 1), 1230(11, 1), (12, 1), (13, 1), (14, 1), (15, 1), 1231(16, 1), (17, 1), (18, 1), (19, 1), (20, 1); 1232INSERT INTO t2 SELECT a, 2 FROM t2 WHERE b = 1; 1233INSERT INTO t2 SELECT a, 3 FROM t2 WHERE b = 1; 1234INSERT INTO t2 SELECT -1, -1 FROM t2; 1235INSERT INTO t2 SELECT -1, -1 FROM t2; 1236INSERT INTO t2 SELECT -1, -1 FROM t2; 1237INSERT INTO t3 1238VALUES (1, 0), (2, 0), (3, 0), (4, 0), (5, 0), 1239(6, 0), (7, 0), (8, 0), (9, 0), (10, 0); 1240INSERT INTO t3 SELECT * FROM t3 WHERE a = 10; 1241INSERT INTO t3 SELECT * FROM t3 WHERE a = 10; 1242SELECT * FROM t1 WHERE 12433 <= a AND a < 5 OR 12445 < a AND b = 3 OR 12453 <= a; 1246a b 12475 0 12489 7 1249EXPLAIN 1250SELECT * FROM t1 WHERE 12513 <= a AND a < 5 OR 12525 < a AND b = 3 OR 12533 <= a; 1254id select_type table type possible_keys key key_len ref rows Extra 12551 SIMPLE t1 range a a 5 NULL 3 Using where; Using index 1256SELECT * FROM t1 WHERE 12573 <= a AND a < 5 OR 12585 <= a AND b = 3 OR 12593 <= a; 1260a b 12615 0 12629 7 1263EXPLAIN 1264SELECT * FROM t1 WHERE 12653 <= a AND a < 5 OR 12665 <= a AND b = 3 OR 12673 <= a; 1268id select_type table type possible_keys key key_len ref rows Extra 12691 SIMPLE t1 range a a 5 NULL 4 Using where; Using index 1270SELECT * FROM t1 WHERE 12713 <= a AND a <= 5 OR 12725 <= a AND b = 3 OR 12733 <= a; 1274a b 12755 0 12769 7 1277EXPLAIN 1278SELECT * FROM t1 WHERE 12793 <= a AND a <= 5 OR 12805 <= a AND b = 3 OR 12813 <= a; 1282id select_type table type possible_keys key key_len ref rows Extra 12831 SIMPLE t1 range a a 5 NULL 3 Using where; Using index 1284SELECT * FROM t1 WHERE 12853 <= a AND a <= 5 OR 12863 <= a; 1287a b 12885 0 12899 7 1290EXPLAIN 1291SELECT * FROM t1 WHERE 12923 <= a AND a <= 5 OR 12933 <= a; 1294id select_type table type possible_keys key key_len ref rows Extra 12951 SIMPLE t1 range a a 5 NULL 3 Using where; Using index 1296SELECT * FROM t2 WHERE 12975 <= a AND a < 10 AND b = 1 OR 129815 <= a AND a < 20 AND b = 3 1299OR 13001 <= a AND b = 1; 1301a b 13021 1 13032 1 13043 1 13054 1 13065 1 13076 1 13087 1 13098 1 13109 1 131110 1 131211 1 131312 1 131413 1 131514 1 131615 1 131715 3 131816 1 131916 3 132017 1 132117 3 132218 1 132318 3 132419 1 132519 3 132620 1 1327EXPLAIN 1328SELECT * FROM t2 WHERE 13295 <= a AND a < 10 AND b = 1 OR 133015 <= a AND a < 20 AND b = 3 1331OR 13321 <= a AND b = 1; 1333id select_type table type possible_keys key key_len ref rows Extra 13341 SIMPLE t2 range a a 10 NULL 50 Using where; Using index 1335SELECT * FROM t2 WHERE 13365 <= a AND a < 10 AND b = 2 OR 133715 <= a AND a < 20 AND b = 3 1338OR 13391 <= a AND b = 1; 1340a b 13411 1 13422 1 13433 1 13444 1 13455 1 13465 2 13476 1 13486 2 13497 1 13507 2 13518 1 13528 2 13539 1 13549 2 135510 1 135611 1 135712 1 135813 1 135914 1 136015 1 136115 3 136216 1 136316 3 136417 1 136517 3 136618 1 136718 3 136819 1 136919 3 137020 1 1371EXPLAIN 1372SELECT * FROM t2 WHERE 13735 <= a AND a < 10 AND b = 2 OR 137415 <= a AND a < 20 AND b = 3 1375OR 13761 <= a AND b = 1; 1377id select_type table type possible_keys key key_len ref rows Extra 13781 SIMPLE t2 range a a 10 NULL 50 Using where; Using index 1379SELECT * FROM t3 WHERE 13805 <= a AND a < 10 AND b = 3 OR 1381a < 5 OR 1382a < 10; 1383a b 13841 0 13852 0 13863 0 13874 0 13885 0 13896 0 13907 0 13918 0 13929 0 1393EXPLAIN 1394SELECT * FROM t3 WHERE 13955 <= a AND a < 10 AND b = 3 OR 1396a < 5 OR 1397a < 10; 1398id select_type table type possible_keys key key_len ref rows Extra 13991 SIMPLE t3 range a a 5 NULL 8 Using where; Using index 1400DROP TABLE t1, t2, t3; 1401# 1402# Bug #47123: Endless 100% CPU loop with STRAIGHT_JOIN 1403# 1404CREATE TABLE t1(a INT, KEY(a)); 1405INSERT INTO t1 VALUES (1), (NULL); 1406SELECT * FROM t1 WHERE a <> NULL and (a <> NULL or a <= NULL); 1407a 1408DROP TABLE t1; 1409# 1410# Bug#47925: regression of range optimizer and date comparison in 5.1.39! 1411# 1412CREATE TABLE t1 ( a DATE, KEY ( a ) ); 1413CREATE TABLE t2 ( a DATETIME, KEY ( a ) ); 1414# Make optimizer choose range scan 1415INSERT INTO t1 VALUES ('2009-09-22'), ('2009-09-22'), ('2009-09-22'); 1416INSERT INTO t1 VALUES ('2009-09-23'), ('2009-09-23'), ('2009-09-23'); 1417INSERT INTO t2 VALUES ('2009-09-22 12:00:00'), ('2009-09-22 12:00:00'), 1418('2009-09-22 12:00:00'); 1419INSERT INTO t2 VALUES ('2009-09-23 12:00:00'), ('2009-09-23 12:00:00'), 1420('2009-09-23 12:00:00'); 1421# DATE vs DATE 1422EXPLAIN 1423SELECT * FROM t1 WHERE a >= '2009/09/23'; 1424id select_type table type possible_keys key key_len ref rows Extra 1425X X X range a a X X X X 1426SELECT * FROM t1 WHERE a >= '2009/09/23'; 1427a 14282009-09-23 14292009-09-23 14302009-09-23 1431SELECT * FROM t1 WHERE a >= '20090923'; 1432a 14332009-09-23 14342009-09-23 14352009-09-23 1436SELECT * FROM t1 WHERE a >= 20090923; 1437a 14382009-09-23 14392009-09-23 14402009-09-23 1441SELECT * FROM t1 WHERE a >= '2009-9-23'; 1442a 14432009-09-23 14442009-09-23 14452009-09-23 1446SELECT * FROM t1 WHERE a >= '2009.09.23'; 1447a 14482009-09-23 14492009-09-23 14502009-09-23 1451SELECT * FROM t1 WHERE a >= '2009:09:23'; 1452a 14532009-09-23 14542009-09-23 14552009-09-23 1456# DATE vs DATETIME 1457EXPLAIN 1458SELECT * FROM t2 WHERE a >= '2009/09/23'; 1459id select_type table type possible_keys key key_len ref rows Extra 1460X X X range a a X X X X 1461SELECT * FROM t2 WHERE a >= '2009/09/23'; 1462a 14632009-09-23 12:00:00 14642009-09-23 12:00:00 14652009-09-23 12:00:00 1466SELECT * FROM t2 WHERE a >= '2009/09/23'; 1467a 14682009-09-23 12:00:00 14692009-09-23 12:00:00 14702009-09-23 12:00:00 1471SELECT * FROM t2 WHERE a >= '20090923'; 1472a 14732009-09-23 12:00:00 14742009-09-23 12:00:00 14752009-09-23 12:00:00 1476SELECT * FROM t2 WHERE a >= 20090923; 1477a 14782009-09-23 12:00:00 14792009-09-23 12:00:00 14802009-09-23 12:00:00 1481SELECT * FROM t2 WHERE a >= '2009-9-23'; 1482a 14832009-09-23 12:00:00 14842009-09-23 12:00:00 14852009-09-23 12:00:00 1486SELECT * FROM t2 WHERE a >= '2009.09.23'; 1487a 14882009-09-23 12:00:00 14892009-09-23 12:00:00 14902009-09-23 12:00:00 1491SELECT * FROM t2 WHERE a >= '2009:09:23'; 1492a 14932009-09-23 12:00:00 14942009-09-23 12:00:00 14952009-09-23 12:00:00 1496# DATETIME vs DATETIME 1497EXPLAIN 1498SELECT * FROM t2 WHERE a >= '2009/09/23 12:00:00'; 1499id select_type table type possible_keys key key_len ref rows Extra 1500X X X range a a X X X X 1501SELECT * FROM t2 WHERE a >= '2009/09/23 12:00:00'; 1502a 15032009-09-23 12:00:00 15042009-09-23 12:00:00 15052009-09-23 12:00:00 1506SELECT * FROM t2 WHERE a >= '20090923120000'; 1507a 15082009-09-23 12:00:00 15092009-09-23 12:00:00 15102009-09-23 12:00:00 1511SELECT * FROM t2 WHERE a >= 20090923120000; 1512a 15132009-09-23 12:00:00 15142009-09-23 12:00:00 15152009-09-23 12:00:00 1516SELECT * FROM t2 WHERE a >= '2009-9-23 12:00:00'; 1517a 15182009-09-23 12:00:00 15192009-09-23 12:00:00 15202009-09-23 12:00:00 1521SELECT * FROM t2 WHERE a >= '2009.09.23 12:00:00'; 1522a 15232009-09-23 12:00:00 15242009-09-23 12:00:00 15252009-09-23 12:00:00 1526SELECT * FROM t2 WHERE a >= '2009:09:23 12:00:00'; 1527a 15282009-09-23 12:00:00 15292009-09-23 12:00:00 15302009-09-23 12:00:00 1531# DATETIME vs DATE 1532EXPLAIN 1533SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00'; 1534id select_type table type possible_keys key key_len ref rows Extra 1535X X X range a a X X X X 1536SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00'; 1537a 15382009-09-23 15392009-09-23 15402009-09-23 1541SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00'; 1542a 15432009-09-23 15442009-09-23 15452009-09-23 1546SELECT * FROM t1 WHERE a >= '20090923000000'; 1547a 15482009-09-23 15492009-09-23 15502009-09-23 1551SELECT * FROM t1 WHERE a >= 20090923000000; 1552a 15532009-09-23 15542009-09-23 15552009-09-23 1556SELECT * FROM t1 WHERE a >= '2009-9-23 00:00:00'; 1557a 15582009-09-23 15592009-09-23 15602009-09-23 1561SELECT * FROM t1 WHERE a >= '2009.09.23 00:00:00'; 1562a 15632009-09-23 15642009-09-23 15652009-09-23 1566SELECT * FROM t1 WHERE a >= '2009:09:23 00:00:00'; 1567a 15682009-09-23 15692009-09-23 15702009-09-23 1571# Test of the new get_date_from_str implementation 1572# Behavior differs slightly between the trunk and mysql-pe. 1573# The former may give errors for the truncated values, while the latter 1574# gives warnings. The purpose of this test is not to interfere, and only 1575# preserve existing behavior. 1576SELECT str_to_date('2007-10-00', '%Y-%m-%d') >= '' AND 1577str_to_date('2007-10-00', '%Y-%m-%d') <= '2007/10/20'; 1578str_to_date('2007-10-00', '%Y-%m-%d') >= '' AND 1579str_to_date('2007-10-00', '%Y-%m-%d') <= '2007/10/20' 15801 1581Warnings: 1582Warning 1292 Truncated incorrect date value: '' 1583SELECT str_to_date('2007-20-00', '%Y-%m-%d') >= '2007/10/20' AND 1584str_to_date('2007-20-00', '%Y-%m-%d') <= ''; 1585str_to_date('2007-20-00', '%Y-%m-%d') >= '2007/10/20' AND 1586str_to_date('2007-20-00', '%Y-%m-%d') <= '' 1587NULL 1588Warnings: 1589Warning 1292 Truncated incorrect date value: '' 1590Warning 1411 Incorrect datetime value: '2007-20-00' for function str_to_date 1591Warning 1411 Incorrect datetime value: '2007-20-00' for function str_to_date 1592SELECT str_to_date('2007-10-00', '%Y-%m-%d') BETWEEN '' AND '2007/10/20'; 1593str_to_date('2007-10-00', '%Y-%m-%d') BETWEEN '' AND '2007/10/20' 15941 1595Warnings: 1596Warning 1292 Truncated incorrect datetime value: '' 1597SELECT str_to_date('2007-20-00', '%Y-%m-%d') BETWEEN '2007/10/20' AND ''; 1598str_to_date('2007-20-00', '%Y-%m-%d') BETWEEN '2007/10/20' AND '' 1599NULL 1600Warnings: 1601Warning 1411 Incorrect datetime value: '2007-20-00' for function str_to_date 1602SELECT str_to_date('', '%Y-%m-%d'); 1603str_to_date('', '%Y-%m-%d') 16040000-00-00 1605DROP TABLE t1, t2; 1606# 1607# Bug#48459: valgrind errors with query using 'Range checked for each 1608# record' 1609# 1610CREATE TABLE t1 ( 1611a INT, 1612b CHAR(2), 1613c INT, 1614d INT, 1615KEY ( c ), 1616KEY ( d, a, b ( 2 ) ), 1617KEY ( b ( 1 ) ) 1618); 1619INSERT INTO t1 VALUES ( NULL, 'a', 1, 2 ), ( NULL, 'a', 1, 2 ), 1620( 1, 'a', 1, 2 ), ( 1, 'a', 1, 2 ); 1621CREATE TABLE t2 ( 1622a INT, 1623c INT, 1624e INT, 1625KEY ( e ) 1626); 1627INSERT INTO t2 VALUES ( 1, 1, NULL ), ( 1, 1, NULL ); 1628# Should not give Valgrind warnings 1629SELECT 1 1630FROM t1, t2 1631WHERE t1.d <> '1' AND t1.b > '1' 1632AND t1.a = t2.a AND t1.c = t2.c; 16331 16341 16351 16361 16371 1638DROP TABLE t1, t2; 1639# 1640# Bug #48665: sql-bench's insert test fails due to wrong result 1641# 1642CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a)); 1643INSERT INTO t1 VALUES (0,0), (1,1); 1644EXPLAIN 1645SELECT * FROM t1 FORCE INDEX (PRIMARY) 1646WHERE (a>=1 AND a<=2) OR (a>=4 AND a<=5) OR (a>=0 AND a <=10); 1647id select_type table type possible_keys key key_len ref rows Extra 1648@ @ @ range @ @ @ @ @ @ 1649# Should return 2 rows 1650SELECT * FROM t1 FORCE INDEX (PRIMARY) 1651WHERE (a>=1 AND a<=2) OR (a>=4 AND a<=5) OR (a>=0 AND a <=10); 1652a b 16530 0 16541 1 1655DROP TABLE t1; 1656# 1657# Bug #54802: 'NOT BETWEEN' evaluation is incorrect 1658# 1659CREATE TABLE t1 (c_key INT, c_notkey INT, KEY(c_key)); 1660INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3); 1661EXPLAIN SELECT * FROM t1 WHERE 2 NOT BETWEEN c_notkey AND c_key; 1662id select_type table type possible_keys key key_len ref rows Extra 16631 SIMPLE t1 ALL c_key NULL NULL NULL 3 Using where 1664SELECT * FROM t1 WHERE 2 NOT BETWEEN c_notkey AND c_key; 1665c_key c_notkey 16661 1 16673 3 1668DROP TABLE t1; 1669# 1670# Bug #57030: 'BETWEEN' evaluation is incorrect 1671# 1672CREATE TABLE t1(pk INT PRIMARY KEY, i4 INT); 1673CREATE UNIQUE INDEX i4_uq ON t1(i4); 1674INSERT INTO t1 VALUES (1,10), (2,20), (3,30); 1675EXPLAIN 1676SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 10; 1677id select_type table type possible_keys key key_len ref rows Extra 16781 SIMPLE t1 const i4_uq i4_uq 5 const 1 NULL 1679SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 10; 1680pk i4 16811 10 1682EXPLAIN 1683SELECT * FROM t1 WHERE 10 BETWEEN i4 AND i4; 1684id select_type table type possible_keys key key_len ref rows Extra 16851 SIMPLE t1 const i4_uq i4_uq 5 const 1 NULL 1686SELECT * FROM t1 WHERE 10 BETWEEN i4 AND i4; 1687pk i4 16881 10 1689EXPLAIN 1690SELECT * FROM t1 WHERE 10 BETWEEN 10 AND i4; 1691id select_type table type possible_keys key key_len ref rows Extra 16921 SIMPLE t1 range i4_uq i4_uq 5 NULL 3 Using where 1693SELECT * FROM t1 WHERE 10 BETWEEN 10 AND i4; 1694pk i4 16951 10 16962 20 16973 30 1698EXPLAIN 1699SELECT * FROM t1 WHERE 10 BETWEEN i4 AND 10; 1700id select_type table type possible_keys key key_len ref rows Extra 17011 SIMPLE t1 range i4_uq i4_uq 5 NULL 1 Using where 1702SELECT * FROM t1 WHERE 10 BETWEEN i4 AND 10; 1703pk i4 17041 10 1705EXPLAIN 1706SELECT * FROM t1 WHERE 10 BETWEEN 10 AND 10; 1707id select_type table type possible_keys key key_len ref rows Extra 17081 SIMPLE t1 ALL NULL NULL NULL NULL 3 NULL 1709SELECT * FROM t1 WHERE 10 BETWEEN 10 AND 10; 1710pk i4 17111 10 17122 20 17133 30 1714EXPLAIN 1715SELECT * FROM t1 WHERE 10 BETWEEN 11 AND 11; 1716id select_type table type possible_keys key key_len ref rows Extra 17171 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 1718SELECT * FROM t1 WHERE 10 BETWEEN 11 AND 11; 1719pk i4 1720EXPLAIN 1721SELECT * FROM t1 WHERE 10 BETWEEN 100 AND 0; 1722id select_type table type possible_keys key key_len ref rows Extra 17231 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 1724SELECT * FROM t1 WHERE 10 BETWEEN 100 AND 0; 1725pk i4 1726EXPLAIN 1727SELECT * FROM t1 WHERE i4 BETWEEN 100 AND 0; 1728id select_type table type possible_keys key key_len ref rows Extra 17291 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 1730SELECT * FROM t1 WHERE i4 BETWEEN 100 AND 0; 1731pk i4 1732EXPLAIN 1733SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 99999999999999999; 1734id select_type table type possible_keys key key_len ref rows Extra 17351 SIMPLE t1 range i4_uq i4_uq 5 NULL 3 Using where 1736SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 99999999999999999; 1737pk i4 17381 10 17392 20 17403 30 1741EXPLAIN 1742SELECT * FROM t1 WHERE i4 BETWEEN 999999999999999 AND 30; 1743id select_type table type possible_keys key key_len ref rows Extra 17441 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 1745SELECT * FROM t1 WHERE i4 BETWEEN 999999999999999 AND 30; 1746pk i4 1747EXPLAIN 1748SELECT * FROM t1 WHERE i4 BETWEEN 10 AND '20'; 1749id select_type table type possible_keys key key_len ref rows Extra 17501 SIMPLE t1 range i4_uq i4_uq 5 NULL 1 Using where 1751SELECT * FROM t1 WHERE i4 BETWEEN 10 AND '20'; 1752pk i4 17531 10 17542 20 1755EXPLAIN 1756SELECT * FROM t1, t1 as t2 WHERE t2.pk BETWEEN t1.i4 AND t1.i4; 1757id select_type table type possible_keys key key_len ref rows Extra 17581 SIMPLE t1 ALL i4_uq NULL NULL NULL 3 NULL 17591 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.i4 1 Using where 1760SELECT * FROM t1, t1 as t2 WHERE t2.pk BETWEEN t1.i4 AND t1.i4; 1761pk i4 pk i4 1762EXPLAIN 1763SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk; 1764id select_type table type possible_keys key key_len ref rows Extra 17651 SIMPLE t1 ALL i4_uq NULL NULL NULL 3 NULL 17661 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.i4 1 Using where 1767SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk; 1768pk i4 pk i4 1769DROP TABLE t1; 1770# 1771# BUG#13519696 - 62940: SELECT RESULTS VARY WITH VERSION AND 1772# WITH/WITHOUT INDEX RANGE SCAN 1773# 1774create table t1 (id int unsigned not null auto_increment primary key); 1775insert into t1 values (null); 1776insert into t1 select null from t1; 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; 1784create table t2 ( 1785id int unsigned not null auto_increment, 1786val decimal(5,3) not null, 1787primary key (id,val), 1788unique key (val,id), 1789unique key (id)); 1790insert into t2 select null,id*0.0009 from t1; 1791select count(val) from t2 ignore index (val) where val > 0.1155; 1792count(val) 1793128 1794select count(val) from t2 force index (val) where val > 0.1155; 1795count(val) 1796128 1797drop table t2, t1; 1798# 1799# BUG#13453382 - REGRESSION SINCE 5.1.39, RANGE OPTIMIZER WRONG 1800# RESULTS WITH DECIMAL CONVERSION 1801# 1802create table t1 (a int,b int,c int,primary key (a,c)); 1803insert into t1 values (1,1,2),(1,1,3),(1,1,4); 1804select convert(3, signed integer) > 2.9; 1805convert(3, signed integer) > 2.9 18061 1807select * from t1 force index (primary) where a=1 and c>= 2.9; 1808a b c 18091 1 3 18101 1 4 1811select * from t1 ignore index (primary) where a=1 and c>= 2.9; 1812a b c 18131 1 3 18141 1 4 1815select * from t1 force index (primary) where a=1 and c> 2.9; 1816a b c 18171 1 3 18181 1 4 1819select * from t1 ignore index (primary) where a=1 and c> 2.9; 1820a b c 18211 1 3 18221 1 4 1823drop table t1; 1824# 1825# BUG#13463488 - 63437: CHAR & BETWEEN WITH INDEX RETURNS WRONG 1826# RESULT AFTER MYSQL 5.1. 1827# 1828CREATE TABLE t1( 1829F1 CHAR(5) NOT NULL, 1830F2 CHAR(5) NOT NULL, 1831F3 CHAR(5) NOT NULL, 1832PRIMARY KEY(F1), 1833INDEX IDX_F2(F2) 1834); 1835INSERT INTO t1 VALUES 1836('A','A','A'),('AA','AA','AA'),('AAA','AAA','AAA'), 1837('AAAA','AAAA','AAAA'),('AAAAA','AAAAA','AAAAA'); 1838SELECT * FROM t1 WHERE F1 = 'A '; 1839F1 F2 F3 1840A A A 1841SELECT * FROM t1 IGNORE INDEX(PRIMARY) WHERE F1 = 'A '; 1842F1 F2 F3 1843A A A 1844SELECT * FROM t1 WHERE F1 >= 'A '; 1845F1 F2 F3 1846A A A 1847AA AA AA 1848AAA AAA AAA 1849AAAA AAAA AAAA 1850AAAAA AAAAA AAAAA 1851SELECT * FROM t1 WHERE F1 > 'A '; 1852F1 F2 F3 1853AA AA AA 1854AAA AAA AAA 1855AAAA AAAA AAAA 1856AAAAA AAAAA AAAAA 1857SELECT * FROM t1 WHERE F1 BETWEEN 'A ' AND 'AAAAA'; 1858F1 F2 F3 1859A A A 1860AA AA AA 1861AAA AAA AAA 1862AAAA AAAA AAAA 1863AAAAA AAAAA AAAAA 1864SELECT * FROM t1 WHERE F2 BETWEEN 'A ' AND 'AAAAA'; 1865F1 F2 F3 1866A A A 1867AA AA AA 1868AAA AAA AAA 1869AAAA AAAA AAAA 1870AAAAA AAAAA AAAAA 1871SELECT * FROM t1 WHERE F3 BETWEEN 'A ' AND 'AAAAA'; 1872F1 F2 F3 1873A A A 1874AA AA AA 1875AAA AAA AAA 1876AAAA AAAA AAAA 1877AAAAA AAAAA AAAAA 1878SELECT * FROM t1 IGNORE INDEX(PRIMARY) WHERE F1 BETWEEN 'A ' AND 1879'AAAAA'; 1880F1 F2 F3 1881A A A 1882AA AA AA 1883AAA AAA AAA 1884AAAA AAAA AAAA 1885AAAAA AAAAA AAAAA 1886DROP TABLE t1; 1887End of 5.1 tests 1888CREATE TABLE t1 (c1 DECIMAL(10,0),INDEX(c1)); 1889INSERT INTO t1 VALUES (1),(2),(3); 1890SELECT c1 FROM t1 WHERE c1 >= 'A' GROUP BY 1; 1891c1 18921 18932 18943 1895Warnings: 1896Warning 1366 Incorrect decimal value: 'A' for column 'c1' at row 1 1897Warning 1292 Truncated incorrect DOUBLE value: 'A' 1898DROP TABLE t1; 1899create table t1 (a int,b int,key (b),key (a),key (b,a)); 1900insert into t1(a,b) values (1,2),(3,4),(5,6),(7,8); 1901create table t2 (c int); 1902insert into t2(c) values (1),(5),(6),(7),(8); 1903select 1 from (select c from t1,t2 where b >= 1 and a <=> c group by 1 limit 1) as d1; 19041 19051 1906drop table t1, t2; 1907# 1908# Bug #26106: Wrong plan may be chosen when there are several possible 1909# range and ref accesses 1910# 1911# Note: The fix for this bug has been reverted. The code will no longer 1912# select the optimal plan for the two following test queries. This is 1913# not due to a bug but due to minor differences in range estimates 1914# produced by the storage engine. 1915CREATE TABLE t1( 1916a INT, 1917b INT, 1918KEY k ( a ), 1919KEY l ( a, b ) 1920); 1921INSERT INTO t1(a) VALUES (1); 1922INSERT INTO t1 1923VALUES (2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3); 1924INSERT INTO t1 SELECT 3, 4 FROM t1 WHERE a = 2 AND b = 3; 1925INSERT INTO t1 SELECT 4, 1 FROM t1 WHERE a = 2 AND b = 3; 1926ANALYZE TABLE t1; 1927Table Op Msg_type Msg_text 1928test.t1 analyze status OK 1929INSERT INTO t1 VALUES (1, 2); 1930INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=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; 1937# This must use range over index l, not k. 1938# Update: Due to patch being reverted and minor differences in 1939# range estimates k is selected. 1940EXPLAIN SELECT * FROM t1 WHERE a = 1 AND b >= 2; 1941id select_type table type possible_keys key key_len ref rows Extra 19421 SIMPLE t1 ref k,l k 5 const 134 Using where 1943CREATE TABLE t2( 1944a INT, 1945b INT, 1946c INT, 1947KEY k ( a ), 1948KEY l ( a, b ), 1949KEY m ( b ), 1950KEY n ( a, c ) 1951); 1952INSERT INTO t2(a) VALUES (1); 1953INSERT INTO t2 1954VALUES (2,3,3),(2,3,3),(2,3,3),(2,3,3),(2,3,3), 1955(2,3,3),(2,3,3),(2,3,3),(2,3,3),(2,3,3); 1956INSERT INTO t2 SELECT 3, 4, 4 FROM t2 WHERE a = 2 AND b = 3; 1957INSERT INTO t2 SELECT 4, 1, 1 FROM t2 WHERE a = 2 AND b = 3; 1958ANALYZE TABLE t2; 1959Table Op Msg_type Msg_text 1960test.t2 analyze status OK 1961INSERT INTO t2 VALUES (1, 2, 2); 1962INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=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 VALUES (1, 1, 2); 1969# This must use range over index l, not n. 1970# Update: Due to patch being reverted and minor differences in 1971# range estimates k is selected. 1972EXPLAIN SELECT * FROM t2 WHERE a = 1 AND b >= 2 AND c >= 2; 1973id select_type table type possible_keys key key_len ref rows Extra 19741 SIMPLE t2 ref k,l,m,n k 5 const 66 Using where 1975DROP TABLE t1, t2; 1976# 1977# BUG#11765831: 'RANGE ACCESS' MAY INCORRECTLY FILTER 1978# AWAY QUALIFYING ROWS 1979# 1980CREATE TABLE t10( 1981K INT NOT NULL AUTO_INCREMENT, 1982I INT, J INT, 1983PRIMARY KEY(K), 1984KEY(I,J) 1985); 1986INSERT INTO t10(I,J) VALUES (6,1),(6,2),(6,3),(6,4),(6,5), 1987(6,6),(6,7),(6,8),(6,9),(6,0); 1988CREATE TABLE t100 LIKE t10; 1989INSERT INTO t100(I,J) SELECT X.I, X.K+(10*Y.K) FROM t10 AS X,t10 AS Y; 1990INSERT INTO t100(I,J) VALUES(8,26); 1991 1992EXPLAIN SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5); 1993id select_type table type possible_keys key key_len ref rows Extra 19941 SIMPLE t100 range I I 10 NULL 4 Using where 1995 1996SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5); 1997K I J 1998101 8 26 1999DROP TABLE t10,t100; 2000# 2001# BUG#12698916 - JOIN QUERY GIVES WRONG RESULT AT 2ND EXEC. OR 2002# AFTER FLUSH TABLES [-INT VS NULL] 2003# 2004CREATE TABLE t1 (col_int INT, pk INT) ENGINE=InnoDB STATS_PERSISTENT=0; 2005INSERT INTO t1 VALUES (-100,1),(1,6); 2006CREATE TABLE t2 ( 2007col_int_key INT, 2008col_varchar VARCHAR(100) NOT NULL DEFAULT "DEFAULT", 2009pk INT NOT NULL, 2010PRIMARY KEY (pk), 2011KEY (col_int_key) 2012) ENGINE=InnoDB STATS_PERSISTENT=0; 2013INSERT INTO t2 VALUES 2014(1,"GOOD",1),(100,"",2),(200,"",3),(300,"",4),(400,"",5),(500,"",8); 2015EXPLAIN SELECT t1.*,t2.* FROM t1 straight_join t2 2016ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk; 2017id select_type table type possible_keys key key_len ref rows Extra 20181 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where 20191 SIMPLE t2 ref PRIMARY,col_int_key col_int_key 5 test.t1.col_int 1 Using where 2020SELECT t1.*,t2.* FROM t1 straight_join t2 2021ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk; 2022col_int pk col_int_key col_varchar pk 20231 6 1 GOOD 1 2024# need FLUSH so that InnoDB statistics change and thus plan changes 2025FLUSH TABLES; 2026EXPLAIN SELECT t1.*,t2.* FROM t1 straight_join t2 2027ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk; 2028id select_type table type possible_keys key key_len ref rows Extra 20291 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL 20301 SIMPLE t2 ALL PRIMARY,col_int_key NULL NULL NULL 6 Range checked for each record (index map: 0x3) 2031SELECT t1.*,t2.* FROM t1 straight_join t2 2032ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk; 2033col_int pk col_int_key col_varchar pk 20341 6 1 GOOD 1 2035DROP TABLE t1,t2; 2036# 2037# Bug#12694872 - 2038# VALGRIND: 18,816 BYTES IN 196 BLOCKS ARE DEFINITELY LOST IN UNIQUE::GET 2039# 2040CREATE TABLE t1 ( 2041pk INTEGER AUTO_INCREMENT, 2042col_int_nokey INTEGER NOT NULL, 2043col_int_key INTEGER NOT NULL, 2044col_date_key DATE NOT NULL, 2045col_varchar_key VARCHAR(1) NOT NULL, 2046col_varchar_nokey VARCHAR(1) NOT NULL, 2047PRIMARY KEY (pk), 2048KEY (col_int_key), 2049KEY (col_date_key), 2050KEY (col_varchar_key, col_int_key) 2051); 2052INSERT INTO t1 ( 2053col_int_key, 2054col_int_nokey, 2055col_date_key, 2056col_varchar_key, 2057col_varchar_nokey 2058) VALUES 2059(0, 4, '2011-08-25', 'j', 'j'), 2060(8, 6, '2004-09-18', 'v', 'v'), 2061(1, 3, '2009-12-01', 'c', 'c'), 2062(8, 5, '2004-12-17', 'm', 'm'), 2063(9, 3, '2000-03-14', 'd', 'd'), 2064(6, 2, '2006-05-25', 'y', 'y'), 2065(1, 9, '2008-01-23', 't', 't'), 2066(6, 3, '2007-06-18', 'd', 'd'), 2067(2, 8, '2002-10-13', 's', 's'), 2068(4, 1, '1900-01-01', 'r', 'r'), 2069(8, 8, '1959-04-25', 'm', 'm'), 2070(4, 8, '2006-03-09', 'b', 'b'), 2071(4, 5, '2001-06-05', 'x', 'x'), 2072(7, 7, '2006-05-28', 'g', 'g'), 2073(4, 5, '2001-04-19', 'p', 'p'), 2074(1, 1, '1900-01-01', 'q', 'q'), 2075(9, 6, '2004-08-20', 'w', 'w'), 2076(4, 2, '2004-10-10', 'd', 'd'), 2077(8, 9, '2000-04-02', 'e', 'e') 2078; 2079ALTER TABLE t1 DISABLE KEYS; 2080SELECT table2.col_date_key AS field1, 2081CONCAT ( table2.col_varchar_nokey, table1.col_varchar_nokey ) AS field2 2082FROM ( t1 AS table1 INNER JOIN t1 AS table2 2083ON (( table2.pk <> table1.pk ) AND 2084( table2.pk >= table1.col_int_nokey ) ) ) 2085WHERE ( table1.pk > 226 AND 2086table1.pk < ( 226 + 102 ) OR 2087( table1.col_int_key > 226 AND 2088table1.col_int_key < ( 226 + 36 ) OR 2089( table1.col_varchar_key <= 'h' OR 2090table1.col_int_key > 226 AND 2091table1.col_int_key < ( 226 + 227 ) ) 2092) 2093) 2094; 2095field1 field2 20961900-01-01 qb 20971900-01-01 qc 20981900-01-01 qd 20991900-01-01 qd 21001900-01-01 qd 21011900-01-01 qe 21021900-01-01 qg 21031900-01-01 rb 21041900-01-01 rc 21051900-01-01 rd 21061900-01-01 rd 21071900-01-01 rd 21081900-01-01 re 21091900-01-01 rg 21101959-04-25 mb 21111959-04-25 mc 21121959-04-25 md 21131959-04-25 md 21141959-04-25 md 21151959-04-25 me 21161959-04-25 mg 21172000-03-14 dc 21182000-03-14 dd 21192000-03-14 dd 21202000-04-02 eb 21212000-04-02 ec 21222000-04-02 ed 21232000-04-02 ed 21242000-04-02 ed 21252000-04-02 eg 21262001-04-19 pb 21272001-04-19 pc 21282001-04-19 pd 21292001-04-19 pd 21302001-04-19 pd 21312001-04-19 pe 21322001-04-19 pg 21332001-06-05 xb 21342001-06-05 xc 21352001-06-05 xd 21362001-06-05 xd 21372001-06-05 xd 21382001-06-05 xe 21392001-06-05 xg 21402002-10-13 sb 21412002-10-13 sc 21422002-10-13 sd 21432002-10-13 sd 21442002-10-13 sd 21452002-10-13 se 21462002-10-13 sg 21472004-08-20 wb 21482004-08-20 wc 21492004-08-20 wd 21502004-08-20 wd 21512004-08-20 wd 21522004-08-20 we 21532004-08-20 wg 21542004-09-18 vd 21552004-10-10 db 21562004-10-10 dc 21572004-10-10 dd 21582004-10-10 dd 21592004-10-10 de 21602004-10-10 dg 21612004-12-17 mc 21622004-12-17 md 21632004-12-17 md 21642004-12-17 md 21652006-03-09 bc 21662006-03-09 bd 21672006-03-09 bd 21682006-03-09 bd 21692006-03-09 be 21702006-03-09 bg 21712006-05-25 yc 21722006-05-25 yd 21732006-05-25 yd 21742006-05-25 yd 21752006-05-28 gb 21762006-05-28 gc 21772006-05-28 gd 21782006-05-28 gd 21792006-05-28 gd 21802006-05-28 ge 21812007-06-18 db 21822007-06-18 dc 21832007-06-18 dd 21842007-06-18 dd 21852007-06-18 dg 21862008-01-23 tc 21872008-01-23 td 21882008-01-23 td 21892008-01-23 td 21902008-01-23 tg 21912009-12-01 cd 21922009-12-01 cd 21932009-12-01 cd 2194ALTER TABLE t1 ENABLE KEYS; 2195CREATE TABLE t2 SELECT table2.col_date_key AS field1, 2196CONCAT ( table2.col_varchar_nokey, table1.col_varchar_nokey ) AS field2 2197FROM ( t1 AS table1 INNER JOIN t1 AS table2 2198ON (( table2.pk <> table1.pk ) AND 2199( table2.pk >= table1.col_int_nokey ) ) ) 2200WHERE ( table1.pk > 226 AND 2201table1.pk < ( 226 + 102 ) OR 2202( table1.col_int_key > 226 AND 2203table1.col_int_key < ( 226 + 36 ) OR 2204( table1.col_varchar_key <= 'h' OR 2205table1.col_int_key > 226 AND 2206table1.col_int_key < ( 226 + 227 ) ) 2207) 2208) 2209; 2210SELECT * FROM t2 2211WHERE (field1, field2) IN (SELECT table2.col_date_key AS field1, 2212CONCAT ( table2.col_varchar_nokey, table1.col_varchar_nokey ) AS field2 2213FROM ( t1 AS table1 INNER JOIN t1 AS table2 2214ON (( table2.pk <> table1.pk ) AND 2215( table2.pk >= table1.col_int_nokey ) ) ) 2216WHERE ( table1.pk > 226 AND 2217table1.pk < ( 226 + 102 ) OR 2218( table1.col_int_key > 226 AND 2219table1.col_int_key < ( 226 + 36 ) OR 2220( table1.col_varchar_key <= 'h' OR 2221table1.col_int_key > 226 AND 2222table1.col_int_key < ( 226 + 227 ) ) 2223) 2224) 2225); 2226field1 field2 22271900-01-01 qb 22281900-01-01 qc 22291900-01-01 qd 22301900-01-01 qd 22311900-01-01 qd 22321900-01-01 qe 22331900-01-01 qg 22341900-01-01 rb 22351900-01-01 rc 22361900-01-01 rd 22371900-01-01 rd 22381900-01-01 rd 22391900-01-01 re 22401900-01-01 rg 22411959-04-25 mb 22421959-04-25 mc 22431959-04-25 md 22441959-04-25 md 22451959-04-25 md 22461959-04-25 me 22471959-04-25 mg 22482000-03-14 dc 22492000-03-14 dd 22502000-03-14 dd 22512000-04-02 eb 22522000-04-02 ec 22532000-04-02 ed 22542000-04-02 ed 22552000-04-02 ed 22562000-04-02 eg 22572001-04-19 pb 22582001-04-19 pc 22592001-04-19 pd 22602001-04-19 pd 22612001-04-19 pd 22622001-04-19 pe 22632001-04-19 pg 22642001-06-05 xb 22652001-06-05 xc 22662001-06-05 xd 22672001-06-05 xd 22682001-06-05 xd 22692001-06-05 xe 22702001-06-05 xg 22712002-10-13 sb 22722002-10-13 sc 22732002-10-13 sd 22742002-10-13 sd 22752002-10-13 sd 22762002-10-13 se 22772002-10-13 sg 22782004-08-20 wb 22792004-08-20 wc 22802004-08-20 wd 22812004-08-20 wd 22822004-08-20 wd 22832004-08-20 we 22842004-08-20 wg 22852004-09-18 vd 22862004-10-10 db 22872004-10-10 dc 22882004-10-10 dd 22892004-10-10 dd 22902004-10-10 de 22912004-10-10 dg 22922004-12-17 mc 22932004-12-17 md 22942004-12-17 md 22952004-12-17 md 22962006-03-09 bc 22972006-03-09 bd 22982006-03-09 bd 22992006-03-09 bd 23002006-03-09 be 23012006-03-09 bg 23022006-05-25 yc 23032006-05-25 yd 23042006-05-25 yd 23052006-05-25 yd 23062006-05-28 gb 23072006-05-28 gc 23082006-05-28 gd 23092006-05-28 gd 23102006-05-28 gd 23112006-05-28 ge 23122007-06-18 db 23132007-06-18 dc 23142007-06-18 dd 23152007-06-18 dd 23162007-06-18 dg 23172008-01-23 tc 23182008-01-23 td 23192008-01-23 td 23202008-01-23 td 23212008-01-23 tg 23222009-12-01 cd 23232009-12-01 cd 23242009-12-01 cd 2325DROP TABLE t1, t2; 2326# 2327# BUG#12912171 - ASSERTION FAILED: QUICK->HEAD->READ_SET == 2328# SAVE_READ_SET 2329# 2330CREATE TABLE t1 ( 2331a INT, 2332b INT, 2333c INT, 2334PRIMARY KEY (c,a), KEY (a),KEY (a) 2335) ENGINE=INNODB PARTITION BY KEY () PARTITIONS 2; 2336Warnings: 2337Note 1831 Duplicate index 'a_2' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release. 2338INSERT INTO t1 VALUES (1,5,1),(2,4,1),(3,3,1),(4,2,1),(5,1,1); 2339UPDATE t1 SET b = 0, c=1 WHERE a <=>0; 2340SELECT * FROM t1; 2341a b c 23421 5 1 23432 4 1 23443 3 1 23454 2 1 23465 1 1 2347DROP TABLE t1; 2348# 2349# BUG#13256446 - ASSERTION QUICK->HEAD->READ_SET == 2350# SAVE_READ_SET' FAILED IN OPT_RANGE.CC:1606 2351# 2352CREATE TABLE t1 ( 2353f1 INT AUTO_INCREMENT, 2354f2 INT, 2355f3 INT, 2356f4 INT, 2357PRIMARY KEY (f1),KEY(f2) 2358) ENGINE=INNODB; 2359CREATE ALGORITHM=MERGE VIEW v2 AS SELECT f1,f2,f3,f4 FROM t1 WHERE f2=103; 2360INSERT INTO t1 VALUES (154,0,NULL,0),(0,NULL,9,0), 2361(NULL,102,NULL,3),(0,3,NULL,0), (9,0,NULL,0),(0,9,NULL,157); 2362SELECT * FROM v2; 2363f1 f2 f3 f4 2364UPDATE v2 SET f4=0, f2=NULL, f1=NULL WHERE f1 > 16 ORDER BY f1; 2365SELECT * FROM v2; 2366f1 f2 f3 f4 2367DROP TABLE t1; 2368DROP VIEW v2; 2369CREATE TABLE t1 ( 2370f1 INT AUTO_INCREMENT, 2371f2 INT, 2372f3 INT, 2373f4 INT, 2374PRIMARY KEY (f1),KEY(f2) 2375) ENGINE=INNODB; 2376INSERT INTO t1 VALUES(1,NULL,NULL,0), (2,2,0,3), (9,0,107,18), 2377(10,0,0,0), (231,0,0,0), (232,0,8,0), (234,0,0,NULL), (235,8,0,3); 2378CREATE ALGORITHM=MERGE VIEW v3 AS SELECT f1,f2,f3,f4 FROM t1 WHERE f1<=85 ; 2379SELECT * FROM v3; 2380f1 f2 f3 f4 23811 NULL NULL 0 23822 2 0 3 23839 0 107 18 238410 0 0 0 2385UPDATE v3 SET f3=0, f4=4 WHERE f2=68 ORDER BY f1; 2386SELECT * FROM v3; 2387f1 f2 f3 f4 23881 NULL NULL 0 23892 2 0 3 23909 0 107 18 239110 0 0 0 2392DROP TABLE t1; 2393DROP VIEW v3; 2394# 2395# BUG#13731380: RANGE OPTIMIZER CALLS RECORDS_IN_RANGE() FOR OPEN RANGE 2396# 2397CREATE TABLE t1 (pk INT PRIMARY KEY); 2398INSERT INTO t1 VALUES (1),(3),(5); 2399SELECT * FROM t1 WHERE pk <> 3 OR pk < 4; 2400pk 24011 24023 24035 2404DROP TABLE t1; 2405# 2406# BUG#13803810: TOO FEW ROWS RETURNED FOR RANGE ACCESS IN 2407# VARCHAR INDEX USING DATETIME VALUE 2408 2409CREATE TABLE t1 (a DATETIME); 2410INSERT INTO t1 VALUES ('2001-01-01 00:00:00'); 2411INSERT INTO t1 VALUES ('2001-01-01 11:22:33'); 2412CREATE TABLE t2 (b VARCHAR(64), KEY (b)); 2413INSERT INTO t2 VALUES ('2001-01-01'); 2414INSERT INTO t2 VALUES ('2001.01.01'); 2415INSERT INTO t2 VALUES ('2001#01#01'); 2416INSERT INTO t2 VALUES ('2001-01-01 00:00:00'); 2417INSERT INTO t2 VALUES ('2001-01-01 11:22:33'); 2418 2419# range/ref access cannot be used for this query 2420 2421EXPLAIN SELECT * FROM t2 WHERE b=CAST('2001-01-01' AS DATE); 2422id select_type table type possible_keys key key_len ref rows Extra 24231 SIMPLE t2 index b b 67 NULL 5 Using where; Using index 2424SELECT * FROM t2 WHERE b=CAST('2001-01-01' AS DATE); 2425b 24262001#01#01 24272001-01-01 24282001-01-01 00:00:00 24292001.01.01 2430 2431# range/ref access cannot be used for any of the queries below. 2432# See BUG#13814468 about 'Range checked for each record' 2433 2434EXPLAIN SELECT * FROM t1, t2 WHERE a=b ORDER BY BINARY a, BINARY b; 2435id select_type table type possible_keys key key_len ref rows Extra 24361 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort 24371 SIMPLE t2 ALL b NULL NULL NULL 5 Range checked for each record (index map: 0x1) 2438SELECT * FROM t1, t2 WHERE a=b ORDER BY BINARY a, BINARY b; 2439a b 24402001-01-01 00:00:00 2001#01#01 24412001-01-01 00:00:00 2001-01-01 24422001-01-01 00:00:00 2001-01-01 00:00:00 24432001-01-01 00:00:00 2001.01.01 24442001-01-01 11:22:33 2001-01-01 11:22:33 2445 2446EXPLAIN SELECT * FROM t1, t2 WHERE b=a ORDER BY BINARY a, BINARY b; 2447id select_type table type possible_keys key key_len ref rows Extra 24481 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort 24491 SIMPLE t2 ALL b NULL NULL NULL 5 Range checked for each record (index map: 0x1) 2450SELECT * FROM t1, t2 WHERE b=a ORDER BY BINARY a, BINARY b; 2451a b 24522001-01-01 00:00:00 2001#01#01 24532001-01-01 00:00:00 2001-01-01 24542001-01-01 00:00:00 2001-01-01 00:00:00 24552001-01-01 00:00:00 2001.01.01 24562001-01-01 11:22:33 2001-01-01 11:22:33 2457 2458DROP TABLE t1,t2; 2459# 2460# Bug #20229614: OR CONDITIONS ON MULTI-COLUMN INDEX MAY NOT USE ALL 2461# INDEX COLUMNS TO FILTER ROWS 2462# 2463CREATE TABLE t1 ( 2464c1 INT, 2465c2 INT, 2466c3 INT, 2467PRIMARY KEY(c1, c2, c3) 2468) ENGINE=INNODB; 2469INSERT INTO t1 VALUES (1, 1, 1), (1, 1, 2), (1, 1, 3), 2470(1, 1, 4), (1, 1, 5); 2471INSERT INTO t1 SELECT c1, 2, c3 FROM t1; 2472INSERT INTO t1 SELECT c1, 3, c3 FROM t1 WHERE c2 = 1; 2473ANALYZE TABLE t1; 2474Table Op Msg_type Msg_text 2475test.t1 analyze status OK 2476SELECT COUNT(*) FROM t1; 2477COUNT(*) 247815 2479EXPLAIN SELECT c1, c2, c3 2480FROM t1 2481WHERE (c1 = 1 AND c2 = 1 AND c3 = 1) OR 2482(c1 = 1 AND c2 = 2 AND c3 = 2) OR 2483(c1 = 1 AND c2 = 2 AND c3 = 3); 2484id select_type table type possible_keys key key_len ref rows Extra 24851 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using where; Using index 2486DROP TABLE t1; 2487# 2488# Bug#21139683: ASSERTION FAILED: TYPE_ARG == MAYBE_KEY || 2489# TYPE_ARG == IMPOSSIBLE 2490# 2491CREATE TABLE t1 ( 2492a BLOB, 2493PRIMARY KEY(a(1)), 2494KEY(a(1)) 2495) ENGINE=INNODB; 2496SELECT 1 FROM t1 WHERE a <> 'a' OR a <> ""; 24971 2498DROP TABLE t1; 2499# 2500# Bug #23259872: OPTIMIZER CHOOSES TO USE NON PRIMARY 2501# INDEX, EVEN THOUGH COST IS HIGHER 2502# 2503CREATE TABLE `giant_table` ( 2504`id` int(11) NOT NULL AUTO_INCREMENT, 2505`one_id` int(11) NOT NULL, 2506`other_id` bigint(20) NOT NULL DEFAULT '0', 2507`some_other_id` int(11) DEFAULT 0 NOT NULL, 2508`something` double NOT NULL DEFAULT '0', 2509`comment` text COLLATE utf8_unicode_ci, 2510`flags` int(11) NOT NULL DEFAULT '0', 2511`time_created` int(11) NOT NULL DEFAULT '0', 2512PRIMARY KEY (`id`), 2513KEY `time_created` (`time_created`), 2514KEY `some_other_id` (`some_other_id`), 2515KEY `one_other_idx` (`one_id`,`other_id`), 2516KEY `other_id` (`other_id`,`time_created`) 2517) ENGINE=InnoDB AUTO_INCREMENT=101651329 2518DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ; 2519CREATE TABLE t1 (c1 INT); 2520INSERT INTO t1 VALUES (66136540), (68983250), (89627210), (77869520), 2521(82543190), (67538270), (77282760), (77908170), 2522(70923370), (68066360); 2523CREATE PROCEDURE p() 2524BEGIN 2525SET @x = 1; 2526REPEAT 2527INSERT INTO giant_table(id,one_id) 2528SELECT c1 + @x, 0 2529FROM t1 2530WHERE c1 IN (66136540, 68985250, 89627210, 77869520 , 82543190, 67538270, 253177282760, 77908170, 70923370, 68066360); 2532SET @x = @x + 1; 2533UNTIL @x > 30 END REPEAT; 2534END $ 2535CALL p(); 2536SELECT count(*) FROM giant_table; 2537count(*) 2538270 2539INSERT INTO giant_table (id,one_id) VALUES (66136539, 0), (68983258,1), 2540(89628210,1), (77869520,2); 2541INSERT INTO giant_table (id,one_id, some_other_id) VALUES(84673401, 0, 1), 2542(61069031, 1, 1); 2543EXPLAIN SELECT id, something, comment, time_created, one_id, other_id, 2544some_other_id, flags 2545FROM giant_table 2546WHERE id IN (66136539, 68983258, 89628210, 77869520, 82543198, 67538272, 254784673401, 61069031, 68214385, 77282865, 76991297, 64569216, 254889481638, 74534074, 70396537, 80076375, 63308530, 77908270, 254970923271, 68066180) 2550AND (giant_table.flags & 0x01) = 0 AND giant_table.some_other_id = 0; 2551id select_type table type possible_keys key key_len ref rows Extra 25521 SIMPLE giant_table range PRIMARY,some_other_id some_other_id 8 NULL 20 Using where 2553DROP PROCEDURE p; 2554DROP TABLE giant_table, t1; 2555set optimizer_switch=default; 2556