1set optimizer_switch='semijoin=on,materialization=on,firstmatch=on,loosescan=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=off'; 2drop table if exists t1, t2, t3; 3SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 4Warnings: 5Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 6CREATE TABLE t1 ( 7event_date date DEFAULT '0000-00-00' NOT NULL, 8type int(11) DEFAULT '0' NOT NULL, 9event_id int(11) DEFAULT '0' NOT NULL, 10PRIMARY KEY (event_date,type,event_id) 11); 12INSERT INTO t1 VALUES ('1999-07-10',100100,24), ('1999-07-11',100100,25), 13('1999-07-13',100600,0), ('1999-07-13',100600,4), ('1999-07-13',100600,26), 14('1999-07-14',100600,10), ('1999-07-15',100600,16), ('1999-07-15',100800,45), 15('1999-07-15',101000,47), ('1999-07-16',100800,46), ('1999-07-20',100600,5), 16('1999-07-20',100600,27), ('1999-07-21',100600,11), ('1999-07-22',100600,17), 17('1999-07-23',100100,39), ('1999-07-24',100100,39), ('1999-07-24',100500,40), 18('1999-07-25',100100,39), ('1999-07-27',100600,1), ('1999-07-27',100600,6), 19('1999-07-27',100600,28), ('1999-07-28',100600,12), ('1999-07-29',100500,41), 20('1999-07-29',100600,18), ('1999-07-30',100500,41), ('1999-07-31',100500,41), 21('1999-08-01',100700,34), ('1999-08-03',100600,7), ('1999-08-03',100600,29), 22('1999-08-04',100600,13), ('1999-08-05',100500,42), ('1999-08-05',100600,19), 23('1999-08-06',100500,42), ('1999-08-07',100500,42), ('1999-08-08',100500,42), 24('1999-08-10',100600,2), ('1999-08-10',100600,9), ('1999-08-10',100600,30), 25('1999-08-11',100600,14), ('1999-08-12',100600,20), ('1999-08-17',100500,8), 26('1999-08-17',100600,31), ('1999-08-18',100600,15), ('1999-08-19',100600,22), 27('1999-08-24',100600,3), ('1999-08-24',100600,32), ('1999-08-27',100500,43), 28('1999-08-31',100600,33), ('1999-09-17',100100,37), ('1999-09-18',100100,37), 29('1999-09-19',100100,37), ('2000-12-18',100700,38); 30select 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; 31event_date type event_id 321999-07-10 100100 24 331999-07-11 100100 25 341999-07-13 100600 0 351999-07-13 100600 4 361999-07-13 100600 26 371999-07-14 100600 10 38explain 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; 39id select_type table partitions type possible_keys key key_len ref rows filtered Extra 401 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 41Warnings: 42Note 1003 /* select#1 */ select `test`.`t1`.`event_date` AS `event_date`,`test`.`t1`.`type` AS `type`,`test`.`t1`.`event_id` AS `event_id` from `test`.`t1` where 0 order by `test`.`t1`.`event_date` 43select 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; 44event_date type event_id 451999-07-10 100100 24 461999-07-11 100100 25 471999-07-13 100600 0 481999-07-13 100600 4 491999-07-13 100600 26 501999-07-14 100600 10 511999-07-15 100600 16 52drop table t1; 53CREATE TABLE t1 ( 54PAPER_ID smallint(6) DEFAULT '0' NOT NULL, 55YEAR smallint(6) DEFAULT '0' NOT NULL, 56ISSUE smallint(6) DEFAULT '0' NOT NULL, 57CLOSED tinyint(4) DEFAULT '0' NOT NULL, 58ISS_DATE date DEFAULT '0000-00-00' NOT NULL, 59PRIMARY KEY (PAPER_ID,YEAR,ISSUE) 60); 61INSERT INTO t1 VALUES (3,1999,34,0,'1999-07-12'), (1,1999,111,0,'1999-03-23'), 62(1,1999,222,0,'1999-03-23'), (3,1999,33,0,'1999-07-12'), 63(3,1999,32,0,'1999-07-12'), (3,1999,31,0,'1999-07-12'), 64(3,1999,30,0,'1999-07-12'), (3,1999,29,0,'1999-07-12'), 65(3,1999,28,0,'1999-07-12'), (1,1999,40,1,'1999-05-01'), 66(1,1999,41,1,'1999-05-01'), (1,1999,42,1,'1999-05-01'), 67(1,1999,46,1,'1999-05-01'), (1,1999,47,1,'1999-05-01'), 68(1,1999,48,1,'1999-05-01'), (1,1999,49,1,'1999-05-01'), 69(1,1999,50,0,'1999-05-01'), (1,1999,51,0,'1999-05-01'), 70(1,1999,200,0,'1999-06-28'), (1,1999,52,0,'1999-06-28'), 71(1,1999,53,0,'1999-06-28'), (1,1999,54,0,'1999-06-28'), 72(1,1999,55,0,'1999-06-28'), (1,1999,56,0,'1999-07-01'), 73(1,1999,57,0,'1999-07-01'), (1,1999,58,0,'1999-07-01'), 74(1,1999,59,0,'1999-07-01'), (1,1999,60,0,'1999-07-01'), 75(3,1999,35,0,'1999-07-12'); 76select YEAR,ISSUE from t1 where PAPER_ID=3 and (YEAR>1999 or (YEAR=1999 and ISSUE>28)) order by YEAR,ISSUE; 77YEAR ISSUE 781999 29 791999 30 801999 31 811999 32 821999 33 831999 34 841999 35 85check table t1; 86Table Op Msg_type Msg_text 87test.t1 check status OK 88repair table t1; 89Table Op Msg_type Msg_text 90test.t1 repair status OK 91drop table t1; 92CREATE TABLE t1 ( 93id int(11) NOT NULL auto_increment, 94parent_id int(11) DEFAULT '0' NOT NULL, 95level tinyint(4) DEFAULT '0' NOT NULL, 96PRIMARY KEY (id), 97KEY parent_id (parent_id), 98KEY level (level) 99); 100INSERT INTO t1 VALUES (1,0,0), (3,1,1), (4,1,1), (8,2,2), (9,2,2), (17,3,2), 101(22,4,2), (24,4,2), (28,5,2), (29,5,2), (30,5,2), (31,6,2), (32,6,2), (33,6,2), 102(203,7,2), (202,7,2), (20,3,2), (157,0,0), (193,5,2), (40,7,2), (2,1,1), 103(15,2,2), (6,1,1), (34,6,2), (35,6,2), (16,3,2), (7,1,1), (36,7,2), (18,3,2), 104(26,5,2), (27,5,2), (183,4,2), (38,7,2), (25,5,2), (37,7,2), (21,4,2), 105(19,3,2), (5,1,1), (179,5,2); 106SELECT * FROM t1 WHERE level = 1 AND parent_id = 1; 107id parent_id level 1083 1 1 1094 1 1 1102 1 1 1116 1 1 1127 1 1 1135 1 1 114SELECT * FROM t1 WHERE level = 1 AND parent_id = 1 order by id; 115id parent_id level 1162 1 1 1173 1 1 1184 1 1 1195 1 1 1206 1 1 1217 1 1 122drop table t1; 123create table t1( 124Satellite varchar(25) not null, 125SensorMode varchar(25) not null, 126FullImageCornersUpperLeftLongitude double not null, 127FullImageCornersUpperRightLongitude double not null, 128FullImageCornersUpperRightLatitude double not null, 129FullImageCornersLowerRightLatitude double not null, 130index two (Satellite, SensorMode, FullImageCornersUpperLeftLongitude, FullImageCornersUpperRightLongitude, FullImageCornersUpperRightLatitude, FullImageCornersLowerRightLatitude)); 131insert into t1 values("OV-3","PAN1",91,-92,40,50); 132insert into t1 values("OV-4","PAN1",91,-92,40,50); 133select * from t1 where t1.Satellite = "OV-3" and t1.SensorMode = "PAN1" and t1.FullImageCornersUpperLeftLongitude > -90.000000 and t1.FullImageCornersUpperRightLongitude < -82.000000; 134Satellite SensorMode FullImageCornersUpperLeftLongitude FullImageCornersUpperRightLongitude FullImageCornersUpperRightLatitude FullImageCornersLowerRightLatitude 135OV-3 PAN1 91 -92 40 50 136drop table t1; 137create table t1 ( aString char(100) not null default "", key aString (aString(10)) ); 138insert t1 (aString) values ( "believe in myself" ), ( "believe" ), ("baaa" ), ( "believe in love"); 139select * from t1 where aString < "believe in myself" order by aString; 140aString 141baaa 142believe 143believe in love 144select * from t1 where aString > "believe in love" order by aString; 145aString 146believe in myself 147alter table t1 drop key aString; 148select * from t1 where aString < "believe in myself" order by aString; 149aString 150baaa 151believe 152believe in love 153select * from t1 where aString > "believe in love" order by aString; 154aString 155believe in myself 156drop table t1; 157CREATE TABLE t1 ( 158t1ID int(10) unsigned NOT NULL auto_increment, 159art binary(1) NOT NULL default '', 160KNR char(5) NOT NULL default '', 161RECHNR char(6) NOT NULL default '', 162POSNR char(2) NOT NULL default '', 163ARTNR char(10) NOT NULL default '', 164TEX char(70) NOT NULL default '', 165PRIMARY KEY (t1ID), 166KEY IdxArt (art), 167KEY IdxKnr (KNR), 168KEY IdxArtnr (ARTNR) 169) ENGINE=MyISAM; 170INSERT INTO t1 (art) VALUES ('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'), 203('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'), 204('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'), 205('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'), 206('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'), 207('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'); 208select count(*) from t1 where upper(art) = 'J'; 209count(*) 210213 211select count(*) from t1 where art = 'J' or art = 'j'; 212count(*) 213602 214select count(*) from t1 where art = 'j' or art = 'J'; 215count(*) 216602 217select count(*) from t1 where art = 'j'; 218count(*) 219389 220select count(*) from t1 where art = 'J'; 221count(*) 222213 223drop table t1; 224create table t1 (x int, y int, index(x), index(y)); 225insert into t1 (x) values (1),(2),(3),(4),(5),(6),(7),(8),(9); 226update t1 set y=x; 227analyze table t1; 228Table Op Msg_type Msg_text 229test.t1 analyze status OK 230explain select * from t1, t1 t2 where t1.y = 8 and t2.x between 7 and t1.y+0; 231id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2321 SIMPLE t1 NULL ref y y 5 const 1 100.00 NULL 2331 SIMPLE t2 NULL range x x 5 NULL 2 100.00 Using index condition; Using MRR; Using join buffer (Block Nested Loop) 234Warnings: 235Note 1003 /* select#1 */ select `test`.`t1`.`x` AS `x`,`test`.`t1`.`y` AS `y`,`test`.`t2`.`x` AS `x`,`test`.`t2`.`y` AS `y` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t1`.`y` = 8) and (`test`.`t2`.`x` between 7 and <cache>((8 + 0)))) 236explain select * from t1, t1 t2 where t1.y = 8 and t2.x >= 7 and t2.x <= t1.y+0; 237id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2381 SIMPLE t1 NULL ref y y 5 const 1 100.00 NULL 2391 SIMPLE t2 NULL range x x 5 NULL 2 100.00 Using index condition; Using MRR; Using join buffer (Block Nested Loop) 240Warnings: 241Note 1003 /* select#1 */ select `test`.`t1`.`x` AS `x`,`test`.`t1`.`y` AS `y`,`test`.`t2`.`x` AS `x`,`test`.`t2`.`y` AS `y` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t1`.`y` = 8) and (`test`.`t2`.`x` >= 7) and (`test`.`t2`.`x` <= <cache>((8 + 0)))) 242explain select * from t1, t1 t2 where t1.y = 2 and t2.x between t1.y-1 and t1.y+1; 243id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2441 SIMPLE t1 NULL ref y y 5 const 1 100.00 NULL 2451 SIMPLE t2 NULL range x x 5 NULL 3 100.00 Using index condition; Using MRR; Using join buffer (Block Nested Loop) 246Warnings: 247Note 1003 /* select#1 */ select `test`.`t1`.`x` AS `x`,`test`.`t1`.`y` AS `y`,`test`.`t2`.`x` AS `x`,`test`.`t2`.`y` AS `y` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t1`.`y` = 2) and (`test`.`t2`.`x` between <cache>((2 - 1)) and <cache>((2 + 1)))) 248explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= t1.y-1 and t2.x <= t1.y+1; 249id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2501 SIMPLE t1 NULL ref y y 5 const 1 100.00 NULL 2511 SIMPLE t2 NULL range x x 5 NULL 3 100.00 Using index condition; Using MRR; Using join buffer (Block Nested Loop) 252Warnings: 253Note 1003 /* select#1 */ select `test`.`t1`.`x` AS `x`,`test`.`t1`.`y` AS `y`,`test`.`t2`.`x` AS `x`,`test`.`t2`.`y` AS `y` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t1`.`y` = 2) and (`test`.`t2`.`x` >= <cache>((2 - 1))) and (`test`.`t2`.`x` <= <cache>((2 + 1)))) 254explain select * from t1, t1 t2 where t1.y = 2 and t2.x between 0 and t1.y; 255id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2561 SIMPLE t1 NULL ref y y 5 const 1 100.00 NULL 2571 SIMPLE t2 NULL range x x 5 NULL 2 100.00 Using index condition; Using MRR; Using join buffer (Block Nested Loop) 258Warnings: 259Note 1003 /* select#1 */ select `test`.`t1`.`x` AS `x`,`test`.`t1`.`y` AS `y`,`test`.`t2`.`x` AS `x`,`test`.`t2`.`y` AS `y` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t1`.`y` = 2) and (`test`.`t2`.`x` between 0 and 2)) 260explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= 0 and t2.x <= t1.y; 261id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2621 SIMPLE t1 NULL ref y y 5 const 1 100.00 NULL 2631 SIMPLE t2 NULL range x x 5 NULL 2 100.00 Using index condition; Using MRR; Using join buffer (Block Nested Loop) 264Warnings: 265Note 1003 /* select#1 */ select `test`.`t1`.`x` AS `x`,`test`.`t1`.`y` AS `y`,`test`.`t2`.`x` AS `x`,`test`.`t2`.`y` AS `y` from `test`.`t1` join `test`.`t1` `t2` where ((`test`.`t1`.`y` = 2) and (`test`.`t2`.`x` >= 0) and (`test`.`t2`.`x` <= 2)) 266explain select count(*) from t1 where x in (1); 267id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2681 SIMPLE t1 NULL ref x x 5 const 1 100.00 Using index 269Warnings: 270Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`x` = 1) 271explain select count(*) from t1 where x in (1,2); 272id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2731 SIMPLE t1 NULL range x x 5 NULL 2 100.00 Using where; Using index 274Warnings: 275Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (`test`.`t1`.`x` in (1,2)) 276drop table t1; 277CREATE TABLE t1 (key1 int(11) NOT NULL default '0', KEY i1 (key1)); 278INSERT INTO t1 VALUES (0),(0),(0),(0),(0),(1),(1); 279CREATE TABLE t2 (keya int(11) NOT NULL default '0', KEY j1 (keya)); 280INSERT INTO t2 VALUES (0),(0),(1),(1),(2),(2); 281explain select * from t1, t2 where (t1.key1 <t2.keya + 1) and t2.keya=3; 282id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2831 SIMPLE t2 NULL ref j1 j1 4 const 1 100.00 Using index 2841 SIMPLE t1 NULL index i1 i1 4 NULL 7 100.00 Using where; Using index; Using join buffer (Block Nested Loop) 285Warnings: 286Note 1003 /* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t2`.`keya` AS `keya` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`keya` = 3) and (`test`.`t1`.`key1` < <cache>((3 + 1)))) 287explain select * from t1 force index(i1), t2 force index(j1) where 288(t1.key1 <t2.keya + 1) and t2.keya=3; 289id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2901 SIMPLE t2 NULL ref j1 j1 4 const 1 100.00 Using index 2911 SIMPLE t1 NULL index i1 i1 4 NULL 7 100.00 Using where; Using index; Using join buffer (Block Nested Loop) 292Warnings: 293Note 1003 /* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t2`.`keya` AS `keya` from `test`.`t1` FORCE INDEX (`i1`) join `test`.`t2` FORCE INDEX (`j1`) where ((`test`.`t2`.`keya` = 3) and (`test`.`t1`.`key1` < <cache>((3 + 1)))) 294DROP TABLE t1,t2; 295CREATE TABLE t1 ( 296a int(11) default NULL, 297b int(11) default NULL, 298KEY a (a), 299KEY b (b) 300) ENGINE=MyISAM; 301INSERT INTO t1 VALUES 302(1,1),(2,1),(3,1),(4,1),(5,1),(6,1),(7,1),(8,1),(9,1),(10,2),(10,2), 303(13,2),(14,2),(15,2),(16,2),(17,3),(17,3),(16,3),(17,3),(19,3),(20,3), 304(21,4),(22,5),(23,5),(24,5),(25,5),(26,5),(30,5),(31,5),(32,5),(33,5), 305(33,5),(33,5),(33,5),(33,5),(34,5),(35,5); 306EXPLAIN SELECT * FROM t1 WHERE a IN(1,2) AND b=5; 307id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3081 SIMPLE t1 NULL range a,b a 5 NULL 2 40.54 Using index condition; Using where; Using MRR 309Warnings: 310Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`b` = 5) and (`test`.`t1`.`a` in (1,2))) 311SELECT * FROM t1 WHERE a IN(1,2) AND b=5; 312a b 313DROP TABLE t1; 314CREATE TABLE t1 (a int, b int, c int, INDEX (c,a,b)); 315INSERT INTO t1 VALUES (1,0,0),(1,0,0),(1,0,0); 316INSERT INTO t1 VALUES (0,1,0),(0,1,0),(0,1,0); 317SELECT COUNT(*) FROM t1 WHERE (c=0 and a=1) or (c=0 and b=1); 318COUNT(*) 3196 320SELECT COUNT(*) FROM t1 WHERE (c=0 and b=1) or (c=0 and a=1); 321COUNT(*) 3226 323DROP TABLE t1; 324CREATE TABLE t1 ( a int not null, b int not null, INDEX ab(a,b) ); 325INSERT INTO t1 VALUES (47,1), (70,1), (15,1), (15, 4); 326SELECT * FROM t1 327WHERE 328( 329( b =1 AND a BETWEEN 14 AND 21 ) OR 330( b =2 AND a BETWEEN 16 AND 18 ) OR 331( b =3 AND a BETWEEN 15 AND 19 ) OR 332(a BETWEEN 19 AND 47) 333); 334a b 33515 1 33647 1 337DROP TABLE t1; 338CREATE TABLE t1 ( 339id int( 11 ) unsigned NOT NULL AUTO_INCREMENT , 340line int( 5 ) unsigned NOT NULL default '0', 341columnid int( 3 ) unsigned NOT NULL default '0', 342owner int( 3 ) unsigned NOT NULL default '0', 343ordinal int( 3 ) unsigned NOT NULL default '0', 344showid smallint( 6 ) unsigned NOT NULL default '1', 345tableid int( 1 ) unsigned NOT NULL default '1', 346content int( 5 ) unsigned NOT NULL default '188', 347PRIMARY KEY ( owner, id ) , 348KEY menu( owner, showid, columnid ) , 349KEY `COLUMN` ( owner, columnid, line ) , 350KEY `LINES` ( owner, tableid, content, id ) , 351KEY recount( owner, line ) 352) ENGINE = MYISAM; 353INSERT into t1 (owner,id,columnid,line) values (11,15,15,1),(11,13,13,5); 354SELECT 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; 355id columnid tableid content showid line ordinal 35613 13 1 188 1 5 0 35715 15 1 188 1 1 0 358drop table t1; 359create table t1 (id int(10) primary key); 360insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9); 361select id from t1 where id in (2,5,9) ; 362id 3632 3645 3659 366select id from t1 where id=2 or id=5 or id=9 ; 367id 3682 3695 3709 371drop table t1; 372create table t1 ( id1 int not null, id2 int not null, idnull int null, c char(20), primary key (id1,id2)); 373insert into t1 values (0,1,NULL,"aaa"), (1,1,NULL,"aaa"), (2,1,NULL,"aaa"), 374(3,1,NULL,"aaa"), (4,1,NULL,"aaa"), (5,1,NULL,"aaa"), 375(6,1,NULL,"aaa"), (7,1,NULL,"aaa"), (8,1,NULL,"aaa"), 376(9,1,NULL,"aaa"), (10,1,NULL,"aaa"), (11,1,NULL,"aaa"), 377(12,1,NULL,"aaa"), (13,1,NULL,"aaa"), (14,1,NULL,"aaa"), 378(15,1,NULL,"aaa"), (16,1,NULL,"aaa"), (17,1,NULL,"aaa"), 379(18,1,NULL,"aaa"), (19,1,NULL,"aaa"), (20,1,NULL,"aaa"); 380select 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; 381id1 idnull 382drop table t1; 383create table t1 ( 384id int not null auto_increment, 385name char(1) not null, 386uid int not null, 387primary key (id), 388index uid_index (uid)); 389create table t2 ( 390id int not null auto_increment, 391name char(1) not null, 392uid int not null, 393primary key (id), 394index uid_index (uid)); 395insert into t1(id, uid, name) values(1, 0, ' '); 396insert into t1(uid, name) values(0, ' '); 397insert into t2(uid, name) select uid, name from t1; 398insert into t1(uid, name) select uid, name from t2; 399insert into t2(uid, name) select uid, name from t1; 400insert into t1(uid, name) select uid, name from t2; 401insert into t2(uid, name) select uid, name from t1; 402insert into t1(uid, name) select uid, name from t2; 403insert into t2(uid, name) select uid, name from t1; 404insert into t1(uid, name) select uid, name from t2; 405insert into t2(uid, name) select uid, name from t1; 406insert into t1(uid, name) select uid, name from t2; 407insert into t2(uid, name) select uid, name from t1; 408insert into t2(uid, name) select uid, name from t1; 409insert into t2(uid, name) select uid, name from t1; 410insert into t2(uid, name) select uid, name from t1; 411insert into t1(uid, name) select uid, name from t2; 412delete from t2; 413insert into t2(uid, name) values 414(1, CHAR(64+1)), 415(2, CHAR(64+2)), 416(3, CHAR(64+3)), 417(4, CHAR(64+4)), 418(5, CHAR(64+5)), 419(6, CHAR(64+6)), 420(7, CHAR(64+7)), 421(8, CHAR(64+8)), 422(9, CHAR(64+9)), 423(10, CHAR(64+10)), 424(11, CHAR(64+11)), 425(12, CHAR(64+12)), 426(13, CHAR(64+13)), 427(14, CHAR(64+14)), 428(15, CHAR(64+15)), 429(16, CHAR(64+16)), 430(17, CHAR(64+17)), 431(18, CHAR(64+18)), 432(19, CHAR(64+19)), 433(20, CHAR(64+20)), 434(21, CHAR(64+21)), 435(22, CHAR(64+22)), 436(23, CHAR(64+23)), 437(24, CHAR(64+24)), 438(25, CHAR(64+25)), 439(26, CHAR(64+26)); 440insert into t1(uid, name) select uid, name from t2 order by uid; 441delete from t2; 442insert into t2(id, uid, name) select id, uid, name from t1; 443select count(*) from t1; 444count(*) 4451026 446select count(*) from t2; 447count(*) 4481026 449analyze table t1,t2; 450Table Op Msg_type Msg_text 451test.t1 analyze status OK 452test.t2 analyze status Table is already up to date 453explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0; 454id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4551 SIMPLE t1 NULL range uid_index uid_index 4 NULL 112 100.00 Using index condition; Using MRR 4561 SIMPLE t2 NULL ref uid_index uid_index 4 test.t1.uid 38 100.00 NULL 457Warnings: 458Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`name` AS `name`,`test`.`t1`.`uid` AS `uid`,`test`.`t2`.`id` AS `id`,`test`.`t2`.`name` AS `name`,`test`.`t2`.`uid` AS `uid` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`uid` = `test`.`t1`.`uid`) and (`test`.`t1`.`uid` > 0)) 459explain select * from t1, t2 where t1.uid=t2.uid AND t2.uid > 0; 460id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4611 SIMPLE t1 NULL range uid_index uid_index 4 NULL 112 100.00 Using index condition; Using MRR 4621 SIMPLE t2 NULL ref uid_index uid_index 4 test.t1.uid 38 100.00 NULL 463Warnings: 464Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`name` AS `name`,`test`.`t1`.`uid` AS `uid`,`test`.`t2`.`id` AS `id`,`test`.`t2`.`name` AS `name`,`test`.`t2`.`uid` AS `uid` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`uid` = `test`.`t1`.`uid`) and (`test`.`t1`.`uid` > 0)) 465explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid != 0; 466id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4671 SIMPLE t1 NULL range uid_index uid_index 4 NULL 113 100.00 Using index condition; Using MRR 4681 SIMPLE t2 NULL ref uid_index uid_index 4 test.t1.uid 38 100.00 NULL 469Warnings: 470Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`name` AS `name`,`test`.`t1`.`uid` AS `uid`,`test`.`t2`.`id` AS `id`,`test`.`t2`.`name` AS `name`,`test`.`t2`.`uid` AS `uid` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`uid` = `test`.`t1`.`uid`) and (`test`.`t1`.`uid` <> 0)) 471explain select * from t1, t2 where t1.uid=t2.uid AND t2.uid != 0; 472id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4731 SIMPLE t1 NULL range uid_index uid_index 4 NULL 113 100.00 Using index condition; Using MRR 4741 SIMPLE t2 NULL ref uid_index uid_index 4 test.t1.uid 38 100.00 NULL 475Warnings: 476Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`name` AS `name`,`test`.`t1`.`uid` AS `uid`,`test`.`t2`.`id` AS `id`,`test`.`t2`.`name` AS `name`,`test`.`t2`.`uid` AS `uid` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`uid` = `test`.`t1`.`uid`) and (`test`.`t1`.`uid` <> 0)) 477select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0; 478id name uid id name uid 4791001 A 1 1001 A 1 4801002 B 2 1002 B 2 4811003 C 3 1003 C 3 4821004 D 4 1004 D 4 4831005 E 5 1005 E 5 4841006 F 6 1006 F 6 4851007 G 7 1007 G 7 4861008 H 8 1008 H 8 4871009 I 9 1009 I 9 4881010 J 10 1010 J 10 4891011 K 11 1011 K 11 4901012 L 12 1012 L 12 4911013 M 13 1013 M 13 4921014 N 14 1014 N 14 4931015 O 15 1015 O 15 4941016 P 16 1016 P 16 4951017 Q 17 1017 Q 17 4961018 R 18 1018 R 18 4971019 S 19 1019 S 19 4981020 T 20 1020 T 20 4991021 U 21 1021 U 21 5001022 V 22 1022 V 22 5011023 W 23 1023 W 23 5021024 X 24 1024 X 24 5031025 Y 25 1025 Y 25 5041026 Z 26 1026 Z 26 505select * from t1, t2 where t1.uid=t2.uid AND t1.uid != 0; 506id name uid id name uid 5071001 A 1 1001 A 1 5081002 B 2 1002 B 2 5091003 C 3 1003 C 3 5101004 D 4 1004 D 4 5111005 E 5 1005 E 5 5121006 F 6 1006 F 6 5131007 G 7 1007 G 7 5141008 H 8 1008 H 8 5151009 I 9 1009 I 9 5161010 J 10 1010 J 10 5171011 K 11 1011 K 11 5181012 L 12 1012 L 12 5191013 M 13 1013 M 13 5201014 N 14 1014 N 14 5211015 O 15 1015 O 15 5221016 P 16 1016 P 16 5231017 Q 17 1017 Q 17 5241018 R 18 1018 R 18 5251019 S 19 1019 S 19 5261020 T 20 1020 T 20 5271021 U 21 1021 U 21 5281022 V 22 1022 V 22 5291023 W 23 1023 W 23 5301024 X 24 1024 X 24 5311025 Y 25 1025 Y 25 5321026 Z 26 1026 Z 26 533drop table t1,t2; 534create table t1 (x bigint unsigned not null); 535insert into t1(x) values (0xfffffffffffffff0); 536insert into t1(x) values (0xfffffffffffffff1); 537select * from t1; 538x 53918446744073709551600 54018446744073709551601 541select count(*) from t1 where x>0; 542count(*) 5432 544select count(*) from t1 where x=0; 545count(*) 5460 547select count(*) from t1 where x<0; 548count(*) 5490 550select count(*) from t1 where x < -16; 551count(*) 5520 553select count(*) from t1 where x = -16; 554count(*) 5550 556select count(*) from t1 where x > -16; 557count(*) 5582 559select count(*) from t1 where x = 18446744073709551601; 560count(*) 5611 562create table t2 (x bigint not null); 563insert into t2(x) values (-16); 564insert into t2(x) values (-15); 565select * from t2; 566x 567-16 568-15 569select count(*) from t2 where x>0; 570count(*) 5710 572select count(*) from t2 where x=0; 573count(*) 5740 575select count(*) from t2 where x<0; 576count(*) 5772 578select count(*) from t2 where x < -16; 579count(*) 5800 581select count(*) from t2 where x = -16; 582count(*) 5831 584select count(*) from t2 where x > -16; 585count(*) 5861 587select count(*) from t2 where x = 18446744073709551601; 588count(*) 5890 590drop table t1,t2; 591create table t1 (x bigint unsigned not null primary key) engine=innodb; 592insert into t1(x) values (0xfffffffffffffff0); 593insert into t1(x) values (0xfffffffffffffff1); 594select * from t1; 595x 59618446744073709551600 59718446744073709551601 598select count(*) from t1 where x>0; 599count(*) 6002 601select count(*) from t1 where x=0; 602count(*) 6030 604select count(*) from t1 where x<0; 605count(*) 6060 607select count(*) from t1 where x < -16; 608count(*) 6090 610select count(*) from t1 where x = -16; 611count(*) 6120 613select count(*) from t1 where x > -16; 614count(*) 6152 616select count(*) from t1 where x = 18446744073709551601; 617count(*) 6181 619drop table t1; 620create table t1 (a bigint unsigned); 621create index t1i on t1(a); 622insert into t1 select 18446744073709551615; 623insert into t1 select 18446744073709551614; 624explain select * from t1 where a <> -1; 625id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6261 SIMPLE t1 NULL index t1i t1i 9 NULL 2 50.00 Using where; Using index 627Warnings: 628Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` <> <cache>(-(1))) 629select * from t1 where a <> -1; 630a 63118446744073709551614 63218446744073709551615 633explain select * from t1 where a > -1 or a < -1; 634id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6351 SIMPLE t1 NULL index t1i t1i 9 NULL 2 75.00 Using where; Using index 636Warnings: 637Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` > <cache>(-(1))) or (`test`.`t1`.`a` < <cache>(-(1)))) 638select * from t1 where a > -1 or a < -1; 639a 64018446744073709551614 64118446744073709551615 642explain select * from t1 where a > -1; 643id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6441 SIMPLE t1 NULL index t1i t1i 9 NULL 2 50.00 Using where; Using index 645Warnings: 646Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > <cache>(-(1))) 647select * from t1 where a > -1; 648a 64918446744073709551614 65018446744073709551615 651explain select * from t1 where a < -1; 652id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6531 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table 654Warnings: 655Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` < -(1)) 656select * from t1 where a < -1; 657a 658drop table t1; 659set names latin1; 660create table t1 (a char(10), b text, key (a)) character set latin1; 661INSERT INTO t1 (a) VALUES 662('111'),('222'),('222'),('222'),('222'),('444'),('aaa'),('AAA'),('bbb'); 663explain select * from t1 where a='aaa'; 664id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6651 SIMPLE t1 NULL ref a a 11 const 2 100.00 NULL 666Warnings: 667Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` = 'aaa') 668explain select * from t1 where a=binary 'aaa'; 669id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6701 SIMPLE t1 NULL range a a 11 NULL 2 100.00 Using index condition; Using MRR 671Warnings: 672Warning 1739 Cannot use ref access on index 'a' due to type or collation conversion on field 'a' 673Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` = <cache>(cast('aaa' as char charset binary))) 674explain select * from t1 where a='aaa' collate latin1_bin; 675id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6761 SIMPLE t1 NULL range a a 11 NULL 2 100.00 Using index condition; Using MRR 677Warnings: 678Warning 1739 Cannot use ref access on index 'a' due to type or collation conversion on field 'a' 679Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` = <cache>(('aaa' collate latin1_bin))) 680explain select * from t1 where a='aaa' collate latin1_german1_ci; 681id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6821 SIMPLE t1 NULL ALL a NULL NULL NULL 9 11.11 Using where 683Warnings: 684Warning 1739 Cannot use ref access on index 'a' due to type or collation conversion on field 'a' 685Warning 1739 Cannot use range access on index 'a' due to type or collation conversion on field 'a' 686Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` = <cache>(('aaa' collate latin1_german1_ci))) 687drop table t1; 688CREATE TABLE t1 ( 689`CLIENT` char(3) character set latin1 collate latin1_bin NOT NULL default '000', 690`ARG1` char(3) character set latin1 collate latin1_bin NOT NULL default '', 691`ARG2` char(3) character set latin1 collate latin1_bin NOT NULL default '', 692`FUNCTION` varchar(10) character set latin1 collate latin1_bin NOT NULL default '', 693`FUNCTINT` int(11) NOT NULL default '0', 694KEY `VERI_CLNT~2` (`ARG1`) 695) ENGINE=InnoDB DEFAULT CHARSET=latin1; 696INSERT INTO t1 VALUES ('000',' 0',' 0','Text 001',0), ('000',' 0',' 1','Text 002',0), 697('000',' 1',' 2','Text 003',0), ('000',' 2',' 3','Text 004',0), 698('001',' 3',' 0','Text 017',0); 699SELECT count(*) FROM t1 WHERE CLIENT='000' AND (ARG1 != ' 1' OR ARG1 != ' 2'); 700count(*) 7014 702SELECT count(*) FROM t1 WHERE CLIENT='000' AND (ARG1 != ' 2' OR ARG1 != ' 1'); 703count(*) 7044 705drop table t1; 706create table t1 (a int); 707insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 708CREATE TABLE t2 ( 709pk1 int(11) NOT NULL, 710pk2 int(11) NOT NULL, 711pk3 int(11) NOT NULL, 712pk4 int(11) NOT NULL, 713filler char(82), 714PRIMARY KEY (pk1,pk2,pk3,pk4) 715) DEFAULT CHARSET=latin1; 716insert into t2 select 1, A.a+10*B.a, 432, 44, 'fillerZ' from t1 A, t1 B; 717INSERT INTO t2 VALUES (2621, 2635, 0, 0,'filler'), (2621, 2635, 1, 0,'filler'), 718(2621, 2635, 10, 0,'filler'), (2621, 2635, 11, 0,'filler'), 719(2621, 2635, 14, 0,'filler'), (2621, 2635, 1000015, 0,'filler'); 720SELECT * FROM t2 721WHERE ((((pk4 =0) AND (pk1 =2621) AND (pk2 =2635))) 722OR ((pk4 =1) AND (((pk1 IN ( 7, 2, 1 ))) OR (pk1 =522)) AND ((pk2 IN ( 0, 2635)))) 723) AND (pk3 >=1000000); 724pk1 pk2 pk3 pk4 filler 7252621 2635 1000015 0 filler 726drop table t1, t2; 727create table t1(a char(2), key(a(1))); 728insert into t1 values ('x'), ('xx'); 729explain select a from t1 where a > 'x'; 730id select_type table partitions type possible_keys key key_len ref rows filtered Extra 7311 SIMPLE t1 NULL range a a 2 NULL 2 100.00 Using where 732Warnings: 733Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > 'x') 734select a from t1 where a > 'x'; 735a 736xx 737drop table t1; 738CREATE TABLE t1 ( 739OXID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 740OXPARENTID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT 'oxrootid', 741OXLEFT int NOT NULL DEFAULT '0', 742OXRIGHT int NOT NULL DEFAULT '0', 743OXROOTID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 744PRIMARY KEY (OXID), 745KEY OXNID (OXID), 746KEY OXLEFT (OXLEFT), 747KEY OXRIGHT (OXRIGHT), 748KEY OXROOTID (OXROOTID) 749) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci; 750INSERT INTO t1 VALUES 751('d8c4177d09f8b11f5.52725521','oxrootid',1,40,'d8c4177d09f8b11f5.52725521'), 752('d8c4177d151affab2.81582770','d8c4177d09f8b11f5.52725521',2,3, 753'd8c4177d09f8b11f5.52725521'), 754('d8c4177d206a333d2.74422679','d8c4177d09f8b11f5.52725521',4,5, 755'd8c4177d09f8b11f5.52725521'), 756('d8c4177d225791924.30714720','d8c4177d09f8b11f5.52725521',6,7, 757'd8c4177d09f8b11f5.52725521'), 758('d8c4177d2380fc201.39666693','d8c4177d09f8b11f5.52725521',8,9, 759'd8c4177d09f8b11f5.52725521'), 760('d8c4177d24ccef970.14957924','d8c4177d09f8b11f5.52725521',10,11, 761'd8c4177d09f8b11f5.52725521'); 762EXPLAIN 763SELECT s.oxid FROM t1 v, t1 s 764WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND 765v.oxrootid ='d8c4177d09f8b11f5.52725521' AND 766s.oxleft > v.oxleft AND s.oxleft < v.oxright; 767id select_type table partitions type possible_keys key key_len ref rows filtered Extra 7681 SIMPLE v NULL ref OXLEFT,OXRIGHT,OXROOTID OXROOTID 34 const 5 100.00 NULL 7691 SIMPLE s NULL ref OXLEFT,OXROOTID OXROOTID 34 const 5 16.67 Using where 770Warnings: 771Note 1003 /* select#1 */ select `test`.`s`.`OXID` AS `oxid` from `test`.`t1` `v` join `test`.`t1` `s` where ((`test`.`s`.`OXROOTID` = 'd8c4177d09f8b11f5.52725521') and (`test`.`v`.`OXROOTID` = 'd8c4177d09f8b11f5.52725521') and (`test`.`s`.`OXLEFT` > `test`.`v`.`OXLEFT`) and (`test`.`s`.`OXLEFT` < `test`.`v`.`OXRIGHT`)) 772SELECT s.oxid FROM t1 v, t1 s 773WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND 774v.oxrootid ='d8c4177d09f8b11f5.52725521' AND 775s.oxleft > v.oxleft AND s.oxleft < v.oxright; 776oxid 777d8c4177d151affab2.81582770 778d8c4177d206a333d2.74422679 779d8c4177d225791924.30714720 780d8c4177d2380fc201.39666693 781d8c4177d24ccef970.14957924 782DROP TABLE t1; 783create table t1 ( 784c1 char(10), c2 char(10), c3 char(10), c4 char(10), 785c5 char(10), c6 char(10), c7 char(10), c8 char(10), 786c9 char(10), c10 char(10), c11 char(10), c12 char(10), 787c13 char(10), c14 char(10), c15 char(10), c16 char(10), 788index(c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12,c13,c14,c15,c16) 789); 790insert into t1 (c1) values ('1'),('1'),('1'),('1'); 791select * from t1 where 792c1 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 793"abcdefg1", "123456781", "qwertyui1", "asddfg1", 794"abcdefg2", "123456782", "qwertyui2", "asddfg2", 795"abcdefg3", "123456783", "qwertyui3", "asddfg3", 796"abcdefg4", "123456784", "qwertyui4", "asddfg4", 797"abcdefg5", "123456785", "qwertyui5", "asddfg5", 798"abcdefg6", "123456786", "qwertyui6", "asddfg6", 799"abcdefg7", "123456787", "qwertyui7", "asddfg7", 800"abcdefg8", "123456788", "qwertyui8", "asddfg8", 801"abcdefg9", "123456789", "qwertyui9", "asddfg9", 802"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", 803"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", 804"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") 805and c2 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 806"abcdefg1", "123456781", "qwertyui1", "asddfg1", 807"abcdefg2", "123456782", "qwertyui2", "asddfg2", 808"abcdefg3", "123456783", "qwertyui3", "asddfg3", 809"abcdefg4", "123456784", "qwertyui4", "asddfg4", 810"abcdefg5", "123456785", "qwertyui5", "asddfg5", 811"abcdefg6", "123456786", "qwertyui6", "asddfg6", 812"abcdefg7", "123456787", "qwertyui7", "asddfg7", 813"abcdefg8", "123456788", "qwertyui8", "asddfg8", 814"abcdefg9", "123456789", "qwertyui9", "asddfg9", 815"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", 816"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", 817"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") 818and c3 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 819"abcdefg1", "123456781", "qwertyui1", "asddfg1", 820"abcdefg2", "123456782", "qwertyui2", "asddfg2", 821"abcdefg3", "123456783", "qwertyui3", "asddfg3", 822"abcdefg4", "123456784", "qwertyui4", "asddfg4", 823"abcdefg5", "123456785", "qwertyui5", "asddfg5", 824"abcdefg6", "123456786", "qwertyui6", "asddfg6", 825"abcdefg7", "123456787", "qwertyui7", "asddfg7", 826"abcdefg8", "123456788", "qwertyui8", "asddfg8", 827"abcdefg9", "123456789", "qwertyui9", "asddfg9", 828"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", 829"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", 830"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") 831and c4 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 832"abcdefg1", "123456781", "qwertyui1", "asddfg1", 833"abcdefg2", "123456782", "qwertyui2", "asddfg2", 834"abcdefg3", "123456783", "qwertyui3", "asddfg3", 835"abcdefg4", "123456784", "qwertyui4", "asddfg4", 836"abcdefg5", "123456785", "qwertyui5", "asddfg5", 837"abcdefg6", "123456786", "qwertyui6", "asddfg6", 838"abcdefg7", "123456787", "qwertyui7", "asddfg7", 839"abcdefg8", "123456788", "qwertyui8", "asddfg8", 840"abcdefg9", "123456789", "qwertyui9", "asddfg9", 841"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", 842"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", 843"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") 844and c5 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 845"abcdefg1", "123456781", "qwertyui1", "asddfg1", 846"abcdefg2", "123456782", "qwertyui2", "asddfg2", 847"abcdefg3", "123456783", "qwertyui3", "asddfg3", 848"abcdefg4", "123456784", "qwertyui4", "asddfg4", 849"abcdefg5", "123456785", "qwertyui5", "asddfg5", 850"abcdefg6", "123456786", "qwertyui6", "asddfg6", 851"abcdefg7", "123456787", "qwertyui7", "asddfg7", 852"abcdefg8", "123456788", "qwertyui8", "asddfg8", 853"abcdefg9", "123456789", "qwertyui9", "asddfg9", 854"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", 855"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", 856"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") 857and c6 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 858"abcdefg1", "123456781", "qwertyui1", "asddfg1", 859"abcdefg2", "123456782", "qwertyui2", "asddfg2", 860"abcdefg3", "123456783", "qwertyui3", "asddfg3", 861"abcdefg4", "123456784", "qwertyui4", "asddfg4", 862"abcdefg5", "123456785", "qwertyui5", "asddfg5", 863"abcdefg6", "123456786", "qwertyui6", "asddfg6", 864"abcdefg7", "123456787", "qwertyui7", "asddfg7", 865"abcdefg8", "123456788", "qwertyui8", "asddfg8", 866"abcdefg9", "123456789", "qwertyui9", "asddfg9", 867"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", 868"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", 869"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") 870and c7 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 871"abcdefg1", "123456781", "qwertyui1", "asddfg1", 872"abcdefg2", "123456782", "qwertyui2", "asddfg2", 873"abcdefg3", "123456783", "qwertyui3", "asddfg3", 874"abcdefg4", "123456784", "qwertyui4", "asddfg4", 875"abcdefg5", "123456785", "qwertyui5", "asddfg5", 876"abcdefg6", "123456786", "qwertyui6", "asddfg6", 877"abcdefg7", "123456787", "qwertyui7", "asddfg7", 878"abcdefg8", "123456788", "qwertyui8", "asddfg8", 879"abcdefg9", "123456789", "qwertyui9", "asddfg9", 880"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", 881"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", 882"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") 883and c8 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 884"abcdefg1", "123456781", "qwertyui1", "asddfg1", 885"abcdefg2", "123456782", "qwertyui2", "asddfg2", 886"abcdefg3", "123456783", "qwertyui3", "asddfg3", 887"abcdefg4", "123456784", "qwertyui4", "asddfg4", 888"abcdefg5", "123456785", "qwertyui5", "asddfg5", 889"abcdefg6", "123456786", "qwertyui6", "asddfg6", 890"abcdefg7", "123456787", "qwertyui7", "asddfg7", 891"abcdefg8", "123456788", "qwertyui8", "asddfg8", 892"abcdefg9", "123456789", "qwertyui9", "asddfg9", 893"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", 894"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", 895"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") 896and c9 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 897"abcdefg1", "123456781", "qwertyui1", "asddfg1", 898"abcdefg2", "123456782", "qwertyui2", "asddfg2", 899"abcdefg3", "123456783", "qwertyui3", "asddfg3", 900"abcdefg4", "123456784", "qwertyui4", "asddfg4", 901"abcdefg5", "123456785", "qwertyui5", "asddfg5", 902"abcdefg6", "123456786", "qwertyui6", "asddfg6", 903"abcdefg7", "123456787", "qwertyui7", "asddfg7", 904"abcdefg8", "123456788", "qwertyui8", "asddfg8", 905"abcdefg9", "123456789", "qwertyui9", "asddfg9", 906"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", 907"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", 908"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") 909and c10 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 910"abcdefg1", "123456781", "qwertyui1", "asddfg1", 911"abcdefg2", "123456782", "qwertyui2", "asddfg2", 912"abcdefg3", "123456783", "qwertyui3", "asddfg3", 913"abcdefg4", "123456784", "qwertyui4", "asddfg4", 914"abcdefg5", "123456785", "qwertyui5", "asddfg5", 915"abcdefg6", "123456786", "qwertyui6", "asddfg6", 916"abcdefg7", "123456787", "qwertyui7", "asddfg7", 917"abcdefg8", "123456788", "qwertyui8", "asddfg8", 918"abcdefg9", "123456789", "qwertyui9", "asddfg9", 919"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", 920"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", 921"abcdefgC", "12345678C", "qwertyuiC", "asddfgC"); 922c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 c13 c14 c15 c16 923Warnings: 924Warning 3170 Memory capacity of 8388608 bytes for 'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query. 925drop table t1; 926End of 4.1 tests 927CREATE TABLE t1 ( 928id int(11) NOT NULL auto_increment, 929status varchar(20), 930PRIMARY KEY (id), 931KEY (status) 932); 933INSERT INTO t1 VALUES 934(1,'B'), (2,'B'), (3,'B'), (4,'B'), (5,'B'), (6,'B'), 935(7,'B'), (8,'B'), (9,'B'), (10,'B'), (11,'B'), (12,'B'), 936(13,'B'), (14,'B'), (15,'B'), (16,'B'), (17,'B'), (18,'B'), 937(19,'B'), (20,'B'), (21,'B'), (22,'B'), (23,'B'), (24,'B'), 938(25,'A'), (26,'A'), (27,'A'), (28,'A'), (29,'A'), (30,'A'), 939(31,'A'), (32,'A'), (33,'A'), (34,'A'), (35,'A'), (36,'A'), 940(37,'A'), (38,'A'), (39,'A'), (40,'A'), (41,'A'), (42,'A'), 941(43,'A'), (44,'A'), (45,'A'), (46,'A'), (47,'A'), (48,'A'), 942(49,'A'), (50,'A'), (51,'A'), (52,'A'), (53,'C'), (54,'C'), 943(55,'C'), (56,'C'), (57,'C'), (58,'C'), (59,'C'), (60,'C'); 944EXPLAIN SELECT * FROM t1 WHERE status <> 'A' AND status <> 'B'; 945id select_type table partitions type possible_keys key key_len ref rows filtered Extra 9461 SIMPLE t1 NULL range status status 23 NULL 11 100.00 Using index condition; Using MRR 947Warnings: 948Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`status` AS `status` from `test`.`t1` where ((`test`.`t1`.`status` <> 'A') and (`test`.`t1`.`status` <> 'B')) 949EXPLAIN SELECT * FROM t1 WHERE status NOT IN ('A','B'); 950id select_type table partitions type possible_keys key key_len ref rows filtered Extra 9511 SIMPLE t1 NULL range status status 23 NULL 11 100.00 Using index condition; Using MRR 952Warnings: 953Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`status` AS `status` from `test`.`t1` where (`test`.`t1`.`status` not in ('A','B')) 954SELECT * FROM t1 WHERE status <> 'A' AND status <> 'B'; 955id status 95653 C 95754 C 95855 C 95956 C 96057 C 96158 C 96259 C 96360 C 964SELECT * FROM t1 WHERE status NOT IN ('A','B'); 965id status 96653 C 96754 C 96855 C 96956 C 97057 C 97158 C 97259 C 97360 C 974EXPLAIN SELECT status FROM t1 WHERE status <> 'A' AND status <> 'B'; 975id select_type table partitions type possible_keys key key_len ref rows filtered Extra 9761 SIMPLE t1 NULL range status status 23 NULL 11 100.00 Using where; Using index 977Warnings: 978Note 1003 /* select#1 */ select `test`.`t1`.`status` AS `status` from `test`.`t1` where ((`test`.`t1`.`status` <> 'A') and (`test`.`t1`.`status` <> 'B')) 979EXPLAIN SELECT status FROM t1 WHERE status NOT IN ('A','B'); 980id select_type table partitions type possible_keys key key_len ref rows filtered Extra 9811 SIMPLE t1 NULL range status status 23 NULL 11 100.00 Using where; Using index 982Warnings: 983Note 1003 /* select#1 */ select `test`.`t1`.`status` AS `status` from `test`.`t1` where (`test`.`t1`.`status` not in ('A','B')) 984EXPLAIN SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B'; 985id select_type table partitions type possible_keys key key_len ref rows filtered Extra 9861 SIMPLE t1 NULL range status status 23 NULL 10 100.00 Using index condition; Using MRR 987Warnings: 988Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`status` AS `status` from `test`.`t1` where (`test`.`t1`.`status` not between 'A' and 'B') 989EXPLAIN SELECT * FROM t1 WHERE status < 'A' OR status > 'B'; 990id select_type table partitions type possible_keys key key_len ref rows filtered Extra 9911 SIMPLE t1 NULL range status status 23 NULL 10 100.00 Using index condition; Using MRR 992Warnings: 993Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`status` AS `status` from `test`.`t1` where ((`test`.`t1`.`status` < 'A') or (`test`.`t1`.`status` > 'B')) 994SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B'; 995id status 99653 C 99754 C 99855 C 99956 C 100057 C 100158 C 100259 C 100360 C 1004SELECT * FROM t1 WHERE status < 'A' OR status > 'B'; 1005id status 100653 C 100754 C 100855 C 100956 C 101057 C 101158 C 101259 C 101360 C 1014DROP TABLE t1; 1015CREATE TABLE t1 (a int, b int, primary key(a,b)); 1016INSERT INTO t1 VALUES 1017(1,1),(1,2),(1,3),(2,1),(2,2),(2,3),(3,1),(3,2),(3,3),(4,1),(4,2),(4,3); 1018CREATE VIEW v1 as SELECT a,b FROM t1 WHERE b=3; 1019EXPLAIN SELECT a,b FROM t1 WHERE a < 2 and b=3; 1020id select_type table partitions type possible_keys key key_len ref rows filtered Extra 10211 SIMPLE t1 NULL range PRIMARY PRIMARY 4 NULL 4 10.00 Using where; Using index 1022Warnings: 1023Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`b` = 3) and (`test`.`t1`.`a` < 2)) 1024EXPLAIN SELECT a,b FROM v1 WHERE a < 2 and b=3; 1025id select_type table partitions type possible_keys key key_len ref rows filtered Extra 10261 SIMPLE t1 NULL range PRIMARY PRIMARY 4 NULL 4 10.00 Using where; Using index 1027Warnings: 1028Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`b` = 3) and (`test`.`t1`.`a` < 2)) 1029EXPLAIN SELECT a,b FROM t1 WHERE a < 2; 1030id select_type table partitions type possible_keys key key_len ref rows filtered Extra 10311 SIMPLE t1 NULL range PRIMARY PRIMARY 4 NULL 4 100.00 Using where; Using index 1032Warnings: 1033Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` < 2) 1034EXPLAIN SELECT a,b FROM v1 WHERE a < 2; 1035id select_type table partitions type possible_keys key key_len ref rows filtered Extra 10361 SIMPLE t1 NULL range PRIMARY PRIMARY 4 NULL 4 10.00 Using where; Using index 1037Warnings: 1038Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`b` = 3) and (`test`.`t1`.`a` < 2)) 1039SELECT a,b FROM t1 WHERE a < 2 and b=3; 1040a b 10411 3 1042SELECT a,b FROM v1 WHERE a < 2 and b=3; 1043a b 10441 3 1045DROP VIEW v1; 1046DROP TABLE t1; 1047CREATE TABLE t1 (name varchar(15) NOT NULL, KEY idx(name)); 1048INSERT INTO t1 VALUES ('Betty'), ('Anna'); 1049SELECT * FROM t1; 1050name 1051Anna 1052Betty 1053DELETE FROM t1 WHERE name NOT LIKE 'A%a'; 1054SELECT * FROM t1; 1055name 1056Anna 1057DROP TABLE t1; 1058CREATE TABLE t1 (a int, KEY idx(a)); 1059INSERT INTO t1 VALUES (NULL), (1), (2), (3); 1060SELECT * FROM t1; 1061a 1062NULL 10631 10642 10653 1066DELETE FROM t1 WHERE NOT(a <=> 2); 1067SELECT * FROM t1; 1068a 10692 1070DROP TABLE t1; 1071create table t1 (a int, b int, primary key(a,b)); 1072create view v1 as select a, b from t1; 1073INSERT INTO `t1` VALUES 1074(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) 1075,(13,2),(14,2),(15,3),(16,3),(17,3),(18,3),(19,3); 1076explain select * from t1 where a in (3,4) and b in (1,2,3); 1077id select_type table partitions type possible_keys key key_len ref rows filtered Extra 10781 SIMPLE t1 NULL range PRIMARY PRIMARY 8 NULL # 100.00 Using where; Using index 1079Warnings: 1080Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` in (3,4)) and (`test`.`t1`.`b` in (1,2,3))) 1081explain select * from v1 where a in (3,4) and b in (1,2,3); 1082id select_type table partitions type possible_keys key key_len ref rows filtered Extra 10831 SIMPLE t1 NULL range PRIMARY PRIMARY 8 NULL # 100.00 Using where; Using index 1084Warnings: 1085Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` in (3,4)) and (`test`.`t1`.`b` in (1,2,3))) 1086explain select * from t1 where a between 3 and 4 and b between 1 and 2; 1087id select_type table partitions type possible_keys key key_len ref rows filtered Extra 10881 SIMPLE t1 NULL range PRIMARY PRIMARY 8 NULL # 11.11 Using where; Using index 1089Warnings: 1090Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` between 3 and 4) and (`test`.`t1`.`b` between 1 and 2)) 1091explain select * from v1 where a between 3 and 4 and b between 1 and 2; 1092id select_type table partitions type possible_keys key key_len ref rows filtered Extra 10931 SIMPLE t1 NULL range PRIMARY PRIMARY 8 NULL # 11.11 Using where; Using index 1094Warnings: 1095Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` between 3 and 4) and (`test`.`t1`.`b` between 1 and 2)) 1096drop view v1; 1097drop table t1; 1098create table t3 (a int); 1099insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1100create table t1 (a varchar(10), filler char(200), key(a)) charset=binary; 1101insert into t1 values ('a',''); 1102insert into t1 values ('a ',''); 1103insert into t1 values ('a ', ''); 1104insert into t1 select concat('a', 1000 + A.a + 10 * (B.a + 10 * C.a)), '' 1105 from t3 A, t3 B, t3 C; 1106create table t2 (a varchar(10), filler char(200), key(a)); 1107insert into t2 select * from t1; 1108explain select * from t1 where a between 'a' and 'a '; 1109id select_type table partitions type possible_keys key key_len ref rows filtered Extra 11101 SIMPLE t1 NULL range a a 13 NULL # 100.00 Using index condition; Using MRR 1111Warnings: 1112Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`filler` AS `filler` from `test`.`t1` where (`test`.`t1`.`a` between 'a' and 'a ') 1113explain select * from t1 where a = 'a' or a='a '; 1114id select_type table partitions type possible_keys key key_len ref rows filtered Extra 11151 SIMPLE t1 NULL range a a 13 NULL # 100.00 Using index condition; Using MRR 1116Warnings: 1117Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`filler` AS `filler` from `test`.`t1` where ((`test`.`t1`.`a` = 'a') or (`test`.`t1`.`a` = 'a ')) 1118explain select * from t2 where a between 'a' and 'a '; 1119id select_type table partitions type possible_keys key key_len ref rows filtered Extra 11201 SIMPLE t2 NULL ref a a 13 const # 100.00 Using index condition 1121Warnings: 1122Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`filler` AS `filler` from `test`.`t2` where (`test`.`t2`.`a` between 'a' and 'a ') 1123explain select * from t2 where a = 'a' or a='a '; 1124id select_type table partitions type possible_keys key key_len ref rows filtered Extra 11251 SIMPLE t2 NULL ref a a 13 const # 100.00 NULL 1126Warnings: 1127Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`filler` AS `filler` from `test`.`t2` where ((`test`.`t2`.`a` = 'a') or (`test`.`t2`.`a` = 'a ')) 1128update t1 set a='b' where a<>'a'; 1129explain select * from t1 where a not between 'b' and 'b'; 1130id select_type table partitions type possible_keys key key_len ref rows filtered Extra 11311 SIMPLE t1 NULL range a a 13 NULL # 100.00 Using index condition; Using MRR 1132Warnings: 1133Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`filler` AS `filler` from `test`.`t1` where (`test`.`t1`.`a` not between 'b' and 'b') 1134select a, hex(filler) from t1 where a not between 'b' and 'b'; 1135a hex(filler) 1136a 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 1137drop table t1,t2,t3; 1138create table t1 (a int); 1139insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1140create table t2 (a int, key(a)); 1141insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C; 1142set @a="select * from t2 force index (a) where a NOT IN(0"; 1143select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z; 1144count(*) 11451000 1146set @a=concat(@a, ')'); 1147insert into t2 values (11),(13),(15); 1148set @b= concat("explain ", @a); 1149prepare stmt1 from @b; 1150execute stmt1; 1151id select_type table partitions type possible_keys key key_len ref rows filtered Extra 11521 SIMPLE t2 NULL index a a 5 NULL 1003 50.00 Using where; Using index 1153Warnings: 1154Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` FORCE INDEX (`a`) where (`test`.`t2`.`a` not in (0,0,2,4,6,8,10,12,14,16,18,20,22,24,26,28,30,32,34,36,38,40,42,44,46,48,50,52,54,56,58,60,62,64,66,68,70,72,74,76,78,80,82,84,86,88,90,92,94,96,98,100,102,104,106,108,110,112,114,116,118,120,122,124,126,128,130,132,134,136,138,140,142,144,146,148,150,152,154,156,158,160,162,164,166,168,170,172,174,176,178,180,182,184,186,188,190,192,194,196,198,200,202,204,206,208,210,212,214,216,218,220,222,224,226,228,230,232,234,236,238,240,242,244,246,248,250,252,254,256,258,260,262,264,266,268,270,272,274,276,278,280,282,284,286,288,290,292,294,296,298,300,302,304,306,308,310,312,314,316,318,320,322,324,326,328,330,332,334,336,338,340,342,344,346,348,350,352,354,356,358,360,362,364,366,368,370,372,374,376,378,380,382,384,386,388,390,392,394,396,398,400,402,404,406,408,410,412,414,416,418,420,422,424,426,428,430,432,434,436,438,440,442,444,446,448,450,452,454,456,458,460,462,464,466,468,470,472,474,476,478,480,482,484,486,488,490,492,494,496,498,500,502,504,506,508,510,512,514,516,518,520,522,524,526,528,530,532,534,536,538,540,542,544,546,548,550,552,554,556,558,560,562,564,566,568,570,572,574,576,578,580,582,584,586,588,590,592,594,596,598,600,602,604,606,608,610,612,614,616,618,620,622,624,626,628,630,632,634,636,638,640,642,644,646,648,650,652,654,656,658,660,662,664,666,668,670,672,674,676,678,680,682,684,686,688,690,692,694,696,698,700,702,704,706,708,710,712,714,716,718,720,722,724,726,728,730,732,734,736,738,740,742,744,746,748,750,752,754,756,758,760,762,764,766,768,770,772,774,776,778,780,782,784,786,788,790,792,794,796,798,800,802,804,806,808,810,812,814,816,818,820,822,824,826,828,830,832,834,836,838,840,842,844,846,848,850,852,854,856,858,860,862,864,866,868,870,872,874,876,878,880,882,884,886,888,890,892,894,896,898,900,902,904,906,908,910,912,914,916,918,920,922,924,926,928,930,932,934,936,938,940,942,944,946,948,950,952,954,956,958,960,962,964,966,968,970,972,974,976,978,980,982,984,986,988,990,992,994,996,998,1000,1002,1004,1006,1008,1010,1012,1014,1016,1018,1020,1022,1024,1026,1028,1030,1032,1034,1036,1038,1040,1042,1044,1046,1048,1050,1052,1054,1056,1058,1060,1062,1064,1066,1068,1070,1072,1074,1076,1078,1080,1082,1084,1086,1088,1090,1092,1094,1096,1098,1100,1102,1104,1106,1108,1110,1112,1114,1116,1118,1120,1122,1124,1126,1128,1130,1132,1134,1136,1138,1140,1142,1144,1146,1148,1150,1152,1154,1156,1158,1160,1162,1164,1166,1168,1170,1172,1174,1176,1178,1180,1182,1184,1186,1188,1190,1192,1194,1196,1198,1200,1202,1204,1206,1208,1210,1212,1214,1216,1218,1220,1222,1224,1226,1228,1230,1232,1234,1236,1238,1240,1242,1244,1246,1248,1250,1252,1254,1256,1258,1260,1262,1264,1266,1268,1270,1272,1274,1276,1278,1280,1282,1284,1286,1288,1290,1292,1294,1296,1298,1300,1302,1304,1306,1308,1310,1312,1314,1316,1318,1320,1322,1324,1326,1328,1330,1332,1334,1336,1338,1340,1342,1344,1346,1348,1350,1352,1354,1356,1358,1360,1362,1364,1366,1368,1370,1372,1374,1376,1378,1380,1382,1384,1386,1388,1390,1392,1394,1396,1398,1400,1402,1404,1406,1408,1410,1412,1414,1416,1418,1420,1422,1424,1426,1428,1430,1432,1434,1436,1438,1440,1442,1444,1446,1448,1450,1452,1454,1456,1458,1460,1462,1464,1466,1468,1470,1472,1474,1476,1478,1480,1482,1484,1486,1488,1490,1492,1494,1496,1498,1500,1502,1504,1506,1508,1510,1512,1514,1516,1518,1520,1522,1524,1526,1528,1530,1532,1534,1536,1538,1540,1542,1544,1546,1548,1550,1552,1554,1556,1558,1560,1562,1564,1566,1568,1570,1572,1574,1576,1578,1580,1582,1584,1586,1588,1590,1592,1594,1596,1598,1600,1602,1604,1606,1608,1610,1612,1614,1616,1618,1620,1622,1624,1626,1628,1630,1632,1634,1636,1638,1640,1642,1644,1646,1648,1650,1652,1654,1656,1658,1660,1662,1664,1666,1668,1670,1672,1674,1676,1678,1680,1682,1684,1686,1688,1690,1692,1694,1696,1698,1700,1702,1704,1706,1708,1710,1712,1714,1716,1718,1720,1722,1724,1726,1728,1730,1732,1734,1736,1738,1740,1742,1744,1746,1748,1750,1752,1754,1756,1758,1760,1762,1764,1766,1768,1770,1772,1774,1776,1778,1780,1782,1784,1786,1788,1790,1792,1794,1796,1798,1800,1802,1804,1806,1808,1810,1812,1814,1816,1818,1820,1822,1824,1826,1828,1830,1832,1834,1836,1838,1840,1842,1844,1846,1848,1850,1852,1854,1856,1858,1860,1862,1864,1866,1868,1870,1872,1874,1876,1878,1880,1882,1884,1886,1888,1890,1892,1894,1896,1898,1900,1902,1904,1906,1908,1910,1912,1914,1916,1918,1920,1922,1924,1926,1928,1930,1932,1934,1936,1938,1940,1942,1944,1946,1948,1950,1952,1954,1956,1958,1960,1962,1964,1966,1968,1970,1972,1974,1976,1978,1980,1982,1984,1986,1988,1990,1992,1994,1996,1998)) 1155prepare stmt1 from @a; 1156execute stmt1; 1157a 115811 115913 116015 1161drop table t1, t2; 1162CREATE TABLE t1 ( 1163id int NOT NULL DEFAULT '0', 1164b int NOT NULL DEFAULT '0', 1165c int NOT NULL DEFAULT '0', 1166INDEX idx1(b,c), INDEX idx2(c)); 1167INSERT INTO t1(id) VALUES (1), (2), (3), (4), (5), (6), (7), (8); 1168INSERT INTO t1(b,c) VALUES (3,4), (3,4); 1169ANALYZE TABLE t1; 1170Table Op Msg_type Msg_text 1171test.t1 analyze status OK 1172SELECT * FROM t1 WHERE b<=3 AND 3<=c; 1173id b c 11740 3 4 11750 3 4 1176SELECT * FROM t1 WHERE 3 BETWEEN b AND c; 1177id b c 11780 3 4 11790 3 4 1180EXPLAIN SELECT * FROM t1 WHERE b<=3 AND 3<=c; 1181id select_type table partitions type possible_keys key key_len ref rows filtered Extra 11821 SIMPLE t1 NULL range idx1,idx2 idx2 4 NULL 3 100.00 Using index condition; Using where; Using MRR 1183Warnings: 1184Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`b` <= 3) and (3 <= `test`.`t1`.`c`)) 1185EXPLAIN SELECT * FROM t1 WHERE 3 BETWEEN b AND c; 1186id select_type table partitions type possible_keys key key_len ref rows filtered Extra 11871 SIMPLE t1 NULL range idx1,idx2 idx2 4 NULL 3 100.00 Using where; Using MRR 1188Warnings: 1189Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (3 between `test`.`t1`.`b` and `test`.`t1`.`c`) 1190SELECT * FROM t1 WHERE 0 < b OR 0 > c; 1191id b c 11920 3 4 11930 3 4 1194SELECT * FROM t1 WHERE 0 NOT BETWEEN b AND c; 1195id b c 11960 3 4 11970 3 4 1198EXPLAIN SELECT * FROM t1 WHERE 0 < b OR 0 > c; 1199id select_type table partitions type possible_keys key key_len ref rows filtered Extra 12001 SIMPLE t1 NULL index_merge idx1,idx2 idx1,idx2 4,4 NULL 4 100.00 Using sort_union(idx1,idx2); Using where 1201Warnings: 1202Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((0 < `test`.`t1`.`b`) or (0 > `test`.`t1`.`c`)) 1203EXPLAIN SELECT * FROM t1 WHERE 0 NOT BETWEEN b AND c; 1204id select_type table partitions type possible_keys key key_len ref rows filtered Extra 12051 SIMPLE t1 NULL index_merge idx1,idx2 idx1,idx2 4,4 NULL 4 100.00 Using sort_union(idx1,idx2); Using where 1206Warnings: 1207Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (0 not between `test`.`t1`.`b` and `test`.`t1`.`c`) 1208DROP TABLE t1; 1209CREATE TABLE t1 ( 1210item char(20) NOT NULL default '', 1211started datetime NOT NULL default '0000-00-00 00:00:00', 1212price decimal(16,3) NOT NULL default '0.000', 1213PRIMARY KEY (item,started) 1214) ENGINE=MyISAM; 1215INSERT INTO t1 VALUES 1216('A1','2005-11-01 08:00:00',1000), 1217('A1','2005-11-15 00:00:00',2000), 1218('A1','2005-12-12 08:00:00',3000), 1219('A2','2005-12-01 08:00:00',1000); 1220EXPLAIN SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; 1221id select_type table partitions type possible_keys key key_len ref rows filtered Extra 12221 SIMPLE t1 NULL ref PRIMARY PRIMARY 20 const 2 33.33 Using index condition 1223Warnings: 1224Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 1225Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 1226Note 1003 /* select#1 */ select `test`.`t1`.`item` AS `item`,`test`.`t1`.`started` AS `started`,`test`.`t1`.`price` AS `price` from `test`.`t1` where ((`test`.`t1`.`item` = 'A1') and (`test`.`t1`.`started` <= '2005-12-01 24:00:00')) 1227SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; 1228item started price 1229A1 2005-11-01 08:00:00 1000.000 1230A1 2005-11-15 00:00:00 2000.000 1231Warnings: 1232Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 1233Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 1234SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-02 00:00:00'; 1235item started price 1236A1 2005-11-01 08:00:00 1000.000 1237A1 2005-11-15 00:00:00 2000.000 1238DROP INDEX `PRIMARY` ON t1; 1239EXPLAIN SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; 1240id select_type table partitions type possible_keys key key_len ref rows filtered Extra 12411 SIMPLE t1 NULL ALL NULL NULL NULL NULL 4 25.00 Using where 1242Warnings: 1243Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 1244Note 1003 /* select#1 */ select `test`.`t1`.`item` AS `item`,`test`.`t1`.`started` AS `started`,`test`.`t1`.`price` AS `price` from `test`.`t1` where ((`test`.`t1`.`item` = 'A1') and (`test`.`t1`.`started` <= '2005-12-01 24:00:00')) 1245SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; 1246item started price 1247A1 2005-11-01 08:00:00 1000.000 1248A1 2005-11-15 00:00:00 2000.000 1249Warnings: 1250Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 1251SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-02 00:00:00'; 1252item started price 1253A1 2005-11-01 08:00:00 1000.000 1254A1 2005-11-15 00:00:00 2000.000 1255DROP TABLE t1; 1256 1257BUG#32198 "Comparison of DATE with DATETIME still not using indexes correctly" 1258 1259CREATE TABLE t1 ( 1260id int(11) NOT NULL auto_increment, 1261dateval date default NULL, 1262PRIMARY KEY (id), 1263KEY dateval (dateval) 1264) AUTO_INCREMENT=173; 1265INSERT INTO t1 VALUES 1266(1,'2007-01-01'),(2,'2007-01-02'),(3,'2007-01-03'),(4,'2007-01-04'), 1267(5,'2007-01-05'),(6,'2007-01-06'),(7,'2007-01-07'),(8,'2007-01-08'), 1268(9,'2007-01-09'),(10,'2007-01-10'),(11,'2007-01-11'); 1269This must use range access: 1270explain select * from t1 where dateval >= '2007-01-01 00:00:00' and dateval <= '2007-01-02 23:59:59'; 1271id select_type table partitions type possible_keys key key_len ref rows filtered Extra 12721 SIMPLE t1 NULL range dateval dateval 4 NULL 2 100.00 Using index condition; Using MRR 1273Warnings: 1274Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`dateval` AS `dateval` from `test`.`t1` where ((`test`.`t1`.`dateval` >= '2007-01-01 00:00:00') and (`test`.`t1`.`dateval` <= '2007-01-02 23:59:59')) 1275drop table t1; 1276CREATE TABLE t1 ( 1277a varchar(32), index (a) 1278) DEFAULT CHARSET=latin1 COLLATE=latin1_bin; 1279INSERT INTO t1 VALUES 1280('B'), ('A'), ('A'), ('C'), ('B'), ('A'), ('A'); 1281SELECT a FROM t1 WHERE a='b' OR a='B'; 1282a 1283B 1284B 1285EXPLAIN SELECT a FROM t1 WHERE a='b' OR a='B'; 1286id select_type table partitions type possible_keys key key_len ref rows filtered Extra 12871 SIMPLE t1 NULL range a a 35 NULL 3 100.00 Using where; Using index 1288Warnings: 1289Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 'b') or (`test`.`t1`.`a` = 'B')) 1290DROP TABLE t1; 1291CREATE TABLE t1 (f1 TINYINT(11) UNSIGNED NOT NULL, PRIMARY KEY (f1)); 1292INSERT INTO t1 VALUES (127),(254),(0),(1),(255); 1293SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 256; 1294COUNT(*) 12955 1296Warnings: 1297Warning 1681 'SQL_NO_CACHE' is deprecated and will be removed in a future release. 1298SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 256.0; 1299COUNT(*) 13005 1301Warnings: 1302Warning 1681 'SQL_NO_CACHE' is deprecated and will be removed in a future release. 1303SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 255; 1304COUNT(*) 13054 1306Warnings: 1307Warning 1681 'SQL_NO_CACHE' is deprecated and will be removed in a future release. 1308SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < -1; 1309COUNT(*) 13100 1311Warnings: 1312Warning 1681 'SQL_NO_CACHE' is deprecated and will be removed in a future release. 1313SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -1; 1314COUNT(*) 13155 1316Warnings: 1317Warning 1681 'SQL_NO_CACHE' is deprecated and will be removed in a future release. 1318DROP TABLE t1; 1319CREATE TABLE t1 ( f1 TINYINT(11) NOT NULL, PRIMARY KEY (f1)); 1320INSERT INTO t1 VALUES (127),(126),(0),(-128),(-127); 1321SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 128; 1322COUNT(*) 13235 1324Warnings: 1325Warning 1681 'SQL_NO_CACHE' is deprecated and will be removed in a future release. 1326SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 128.0; 1327COUNT(*) 13285 1329Warnings: 1330Warning 1681 'SQL_NO_CACHE' is deprecated and will be removed in a future release. 1331SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 127; 1332COUNT(*) 13334 1334Warnings: 1335Warning 1681 'SQL_NO_CACHE' is deprecated and will be removed in a future release. 1336SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -129; 1337COUNT(*) 13385 1339Warnings: 1340Warning 1681 'SQL_NO_CACHE' is deprecated and will be removed in a future release. 1341SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -129.0; 1342COUNT(*) 13435 1344Warnings: 1345Warning 1681 'SQL_NO_CACHE' is deprecated and will be removed in a future release. 1346SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -128; 1347COUNT(*) 13484 1349Warnings: 1350Warning 1681 'SQL_NO_CACHE' is deprecated and will be removed in a future release. 1351DROP TABLE t1; 1352create table t1 (a int); 1353insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1354create table t2 (a int, b int, filler char(100)); 1355insert into t2 select A.a + 10 * (B.a + 10 * C.a), 10, 'filler' from t1 A, 1356t1 B, t1 C where A.a < 5; 1357insert into t2 select 1000, b, 'filler' from t2; 1358alter table t2 add index (a,b); 1359Access method can be range/ALL with #rows >= 500. 1360Or it can be ref with #rows = 2, only when there is memory limit. 1361explain select * from t2 where a=1000 and b<11; 1362id select_type table partitions type possible_keys key key_len ref rows filtered Extra 13631 SIMPLE t2 NULL range a a 10 NULL 502 100.00 Using index condition; Using MRR 1364Warnings: 1365Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`filler` AS `filler` from `test`.`t2` where ((`test`.`t2`.`a` = 1000) and (`test`.`t2`.`b` < 11)) 1366drop table t1, t2; 1367CREATE TABLE t1( a INT, b INT, KEY( a, b ) ); 1368CREATE TABLE t2( a INT, b INT, KEY( a, b ) ); 1369CREATE TABLE t3( a INT, b INT, KEY( a, b ) ); 1370INSERT INTO t1( a, b ) 1371VALUES (0, 1), (1, 2), (1, 4), (2, 3), (5, 0), (9, 7); 1372INSERT INTO t2( a, b ) 1373VALUES ( 1, 1), ( 2, 1), ( 3, 1), ( 4, 1), ( 5, 1), 1374( 6, 1), ( 7, 1), ( 8, 1), ( 9, 1), (10, 1), 1375(11, 1), (12, 1), (13, 1), (14, 1), (15, 1), 1376(16, 1), (17, 1), (18, 1), (19, 1), (20, 1); 1377INSERT INTO t2 SELECT a, 2 FROM t2 WHERE b = 1; 1378INSERT INTO t2 SELECT a, 3 FROM t2 WHERE b = 1; 1379INSERT INTO t2 SELECT -1, -1 FROM t2; 1380INSERT INTO t2 SELECT -1, -1 FROM t2; 1381INSERT INTO t2 SELECT -1, -1 FROM t2; 1382INSERT INTO t3 1383VALUES (1, 0), (2, 0), (3, 0), (4, 0), (5, 0), 1384(6, 0), (7, 0), (8, 0), (9, 0), (10, 0); 1385INSERT INTO t3 SELECT * FROM t3 WHERE a = 10; 1386INSERT INTO t3 SELECT * FROM t3 WHERE a = 10; 1387SELECT * FROM t1 WHERE 13883 <= a AND a < 5 OR 13895 < a AND b = 3 OR 13903 <= a; 1391a b 13925 0 13939 7 1394EXPLAIN 1395SELECT * FROM t1 WHERE 13963 <= a AND a < 5 OR 13975 < a AND b = 3 OR 13983 <= a; 1399id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14001 SIMPLE t1 NULL range a a 5 NULL 3 100.00 Using where; Using index 1401Warnings: 1402Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (((3 <= `test`.`t1`.`a`) and (`test`.`t1`.`a` < 5)) or ((`test`.`t1`.`b` = 3) and (5 < `test`.`t1`.`a`)) or (3 <= `test`.`t1`.`a`)) 1403SELECT * FROM t1 WHERE 14043 <= a AND a < 5 OR 14055 <= a AND b = 3 OR 14063 <= a; 1407a b 14085 0 14099 7 1410EXPLAIN 1411SELECT * FROM t1 WHERE 14123 <= a AND a < 5 OR 14135 <= a AND b = 3 OR 14143 <= a; 1415id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14161 SIMPLE t1 NULL range a a 5 NULL 4 100.00 Using where; Using index 1417Warnings: 1418Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (((3 <= `test`.`t1`.`a`) and (`test`.`t1`.`a` < 5)) or ((`test`.`t1`.`b` = 3) and (5 <= `test`.`t1`.`a`)) or (3 <= `test`.`t1`.`a`)) 1419SELECT * FROM t1 WHERE 14203 <= a AND a <= 5 OR 14215 <= a AND b = 3 OR 14223 <= a; 1423a b 14245 0 14259 7 1426EXPLAIN 1427SELECT * FROM t1 WHERE 14283 <= a AND a <= 5 OR 14295 <= a AND b = 3 OR 14303 <= a; 1431id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14321 SIMPLE t1 NULL range a a 5 NULL 3 100.00 Using where; Using index 1433Warnings: 1434Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (((3 <= `test`.`t1`.`a`) and (`test`.`t1`.`a` <= 5)) or ((`test`.`t1`.`b` = 3) and (5 <= `test`.`t1`.`a`)) or (3 <= `test`.`t1`.`a`)) 1435SELECT * FROM t1 WHERE 14363 <= a AND a <= 5 OR 14373 <= a; 1438a b 14395 0 14409 7 1441EXPLAIN 1442SELECT * FROM t1 WHERE 14433 <= a AND a <= 5 OR 14443 <= a; 1445id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14461 SIMPLE t1 NULL range a a 5 NULL 3 100.00 Using where; Using index 1447Warnings: 1448Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (((3 <= `test`.`t1`.`a`) and (`test`.`t1`.`a` <= 5)) or (3 <= `test`.`t1`.`a`)) 1449SELECT * FROM t2 WHERE 14505 <= a AND a < 10 AND b = 1 OR 145115 <= a AND a < 20 AND b = 3 1452OR 14531 <= a AND b = 1; 1454a b 14551 1 14562 1 14573 1 14584 1 14595 1 14606 1 14617 1 14628 1 14639 1 146410 1 146511 1 146612 1 146713 1 146814 1 146915 1 147015 3 147116 1 147216 3 147317 1 147417 3 147518 1 147618 3 147719 1 147819 3 147920 1 1480EXPLAIN 1481SELECT * FROM t2 WHERE 14825 <= a AND a < 10 AND b = 1 OR 148315 <= a AND a < 20 AND b = 3 1484OR 14851 <= a AND b = 1; 1486id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14871 SIMPLE t2 NULL range a a 10 NULL 50 27.10 Using where; Using index 1488Warnings: 1489Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (((`test`.`t2`.`b` = 1) and (5 <= `test`.`t2`.`a`) and (`test`.`t2`.`a` < 10)) or ((`test`.`t2`.`b` = 3) and (15 <= `test`.`t2`.`a`) and (`test`.`t2`.`a` < 20)) or ((`test`.`t2`.`b` = 1) and (1 <= `test`.`t2`.`a`))) 1490SELECT * FROM t2 WHERE 14915 <= a AND a < 10 AND b = 2 OR 149215 <= a AND a < 20 AND b = 3 1493OR 14941 <= a AND b = 1; 1495a b 14961 1 14972 1 14983 1 14994 1 15005 1 15015 2 15026 1 15036 2 15047 1 15057 2 15068 1 15078 2 15089 1 15099 2 151010 1 151111 1 151212 1 151313 1 151414 1 151515 1 151615 3 151716 1 151816 3 151917 1 152017 3 152118 1 152218 3 152319 1 152419 3 152520 1 1526EXPLAIN 1527SELECT * FROM t2 WHERE 15285 <= a AND a < 10 AND b = 2 OR 152915 <= a AND a < 20 AND b = 3 1530OR 15311 <= a AND b = 1; 1532id select_type table partitions type possible_keys key key_len ref rows filtered Extra 15331 SIMPLE t2 NULL range a a 10 NULL 50 27.10 Using where; Using index 1534Warnings: 1535Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (((`test`.`t2`.`b` = 2) and (5 <= `test`.`t2`.`a`) and (`test`.`t2`.`a` < 10)) or ((`test`.`t2`.`b` = 3) and (15 <= `test`.`t2`.`a`) and (`test`.`t2`.`a` < 20)) or ((`test`.`t2`.`b` = 1) and (1 <= `test`.`t2`.`a`))) 1536SELECT * FROM t3 WHERE 15375 <= a AND a < 10 AND b = 3 OR 1538a < 5 OR 1539a < 10; 1540a b 15411 0 15422 0 15433 0 15444 0 15455 0 15466 0 15477 0 15488 0 15499 0 1550EXPLAIN 1551SELECT * FROM t3 WHERE 15525 <= a AND a < 10 AND b = 3 OR 1553a < 5 OR 1554a < 10; 1555id select_type table partitions type possible_keys key key_len ref rows filtered Extra 15561 SIMPLE t3 NULL range a a 5 NULL 8 100.00 Using where; Using index 1557Warnings: 1558Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where (((`test`.`t3`.`b` = 3) and (5 <= `test`.`t3`.`a`) and (`test`.`t3`.`a` < 10)) or (`test`.`t3`.`a` < 5) or (`test`.`t3`.`a` < 10)) 1559DROP TABLE t1, t2, t3; 1560# 1561# Bug #47123: Endless 100% CPU loop with STRAIGHT_JOIN 1562# 1563CREATE TABLE t1(a INT, KEY(a)); 1564INSERT INTO t1 VALUES (1), (NULL); 1565SELECT * FROM t1 WHERE a <> NULL and (a <> NULL or a <= NULL); 1566a 1567DROP TABLE t1; 1568# 1569# Bug#47925: regression of range optimizer and date comparison in 5.1.39! 1570# 1571CREATE TABLE t1 ( a DATE, KEY ( a ) ); 1572CREATE TABLE t2 ( a DATETIME, KEY ( a ) ); 1573# Make optimizer choose range scan 1574INSERT INTO t1 VALUES ('2009-09-22'), ('2009-09-22'), ('2009-09-22'); 1575INSERT INTO t1 VALUES ('2009-09-23'), ('2009-09-23'), ('2009-09-23'); 1576INSERT INTO t2 VALUES ('2009-09-22 12:00:00'), ('2009-09-22 12:00:00'), 1577('2009-09-22 12:00:00'); 1578INSERT INTO t2 VALUES ('2009-09-23 12:00:00'), ('2009-09-23 12:00:00'), 1579('2009-09-23 12:00:00'); 1580# DATE vs DATE 1581EXPLAIN 1582SELECT * FROM t1 WHERE a >= '2009/09/23'; 1583id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1584X X X NULL range a a X X X 100.00 X 1585Warnings: 1586X X X 1587SELECT * FROM t1 WHERE a >= '2009/09/23'; 1588a 15892009-09-23 15902009-09-23 15912009-09-23 1592SELECT * FROM t1 WHERE a >= '20090923'; 1593a 15942009-09-23 15952009-09-23 15962009-09-23 1597SELECT * FROM t1 WHERE a >= 20090923; 1598a 15992009-09-23 16002009-09-23 16012009-09-23 1602SELECT * FROM t1 WHERE a >= '2009-9-23'; 1603a 16042009-09-23 16052009-09-23 16062009-09-23 1607SELECT * FROM t1 WHERE a >= '2009.09.23'; 1608a 16092009-09-23 16102009-09-23 16112009-09-23 1612SELECT * FROM t1 WHERE a >= '2009:09:23'; 1613a 16142009-09-23 16152009-09-23 16162009-09-23 1617# DATE vs DATETIME 1618EXPLAIN 1619SELECT * FROM t2 WHERE a >= '2009/09/23'; 1620id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1621X X X NULL range a a X X X 100.00 X 1622Warnings: 1623X X X 1624SELECT * FROM t2 WHERE a >= '2009/09/23'; 1625a 16262009-09-23 12:00:00 16272009-09-23 12:00:00 16282009-09-23 12:00:00 1629SELECT * FROM t2 WHERE a >= '2009/09/23'; 1630a 16312009-09-23 12:00:00 16322009-09-23 12:00:00 16332009-09-23 12:00:00 1634SELECT * FROM t2 WHERE a >= '20090923'; 1635a 16362009-09-23 12:00:00 16372009-09-23 12:00:00 16382009-09-23 12:00:00 1639SELECT * FROM t2 WHERE a >= 20090923; 1640a 16412009-09-23 12:00:00 16422009-09-23 12:00:00 16432009-09-23 12:00:00 1644SELECT * FROM t2 WHERE a >= '2009-9-23'; 1645a 16462009-09-23 12:00:00 16472009-09-23 12:00:00 16482009-09-23 12:00:00 1649SELECT * FROM t2 WHERE a >= '2009.09.23'; 1650a 16512009-09-23 12:00:00 16522009-09-23 12:00:00 16532009-09-23 12:00:00 1654SELECT * FROM t2 WHERE a >= '2009:09:23'; 1655a 16562009-09-23 12:00:00 16572009-09-23 12:00:00 16582009-09-23 12:00:00 1659# DATETIME vs DATETIME 1660EXPLAIN 1661SELECT * FROM t2 WHERE a >= '2009/09/23 12:00:00'; 1662id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1663X X X NULL range a a X X X 100.00 X 1664Warnings: 1665X X X 1666SELECT * FROM t2 WHERE a >= '2009/09/23 12:00:00'; 1667a 16682009-09-23 12:00:00 16692009-09-23 12:00:00 16702009-09-23 12:00:00 1671SELECT * FROM t2 WHERE a >= '20090923120000'; 1672a 16732009-09-23 12:00:00 16742009-09-23 12:00:00 16752009-09-23 12:00:00 1676SELECT * FROM t2 WHERE a >= 20090923120000; 1677a 16782009-09-23 12:00:00 16792009-09-23 12:00:00 16802009-09-23 12:00:00 1681SELECT * FROM t2 WHERE a >= '2009-9-23 12:00:00'; 1682a 16832009-09-23 12:00:00 16842009-09-23 12:00:00 16852009-09-23 12:00:00 1686SELECT * FROM t2 WHERE a >= '2009.09.23 12:00:00'; 1687a 16882009-09-23 12:00:00 16892009-09-23 12:00:00 16902009-09-23 12:00:00 1691SELECT * FROM t2 WHERE a >= '2009:09:23 12:00:00'; 1692a 16932009-09-23 12:00:00 16942009-09-23 12:00:00 16952009-09-23 12:00:00 1696# DATETIME vs DATE 1697EXPLAIN 1698SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00'; 1699id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1700X X X NULL range a a X X X 100.00 X 1701Warnings: 1702X X X 1703SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00'; 1704a 17052009-09-23 17062009-09-23 17072009-09-23 1708SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00'; 1709a 17102009-09-23 17112009-09-23 17122009-09-23 1713SELECT * FROM t1 WHERE a >= '20090923000000'; 1714a 17152009-09-23 17162009-09-23 17172009-09-23 1718SELECT * FROM t1 WHERE a >= 20090923000000; 1719a 17202009-09-23 17212009-09-23 17222009-09-23 1723SELECT * FROM t1 WHERE a >= '2009-9-23 00:00:00'; 1724a 17252009-09-23 17262009-09-23 17272009-09-23 1728SELECT * FROM t1 WHERE a >= '2009.09.23 00:00:00'; 1729a 17302009-09-23 17312009-09-23 17322009-09-23 1733SELECT * FROM t1 WHERE a >= '2009:09:23 00:00:00'; 1734a 17352009-09-23 17362009-09-23 17372009-09-23 1738# Test of the new get_date_from_str implementation 1739# Behavior differs slightly between the trunk and mysql-pe. 1740# The former may give errors for the truncated values, while the latter 1741# gives warnings. The purpose of this test is not to interfere, and only 1742# preserve existing behavior. 1743SELECT str_to_date('2007-10-00', '%Y-%m-%d') >= '' AND 1744str_to_date('2007-10-00', '%Y-%m-%d') <= '2007/10/20'; 1745str_to_date('2007-10-00', '%Y-%m-%d') >= '' AND 1746str_to_date('2007-10-00', '%Y-%m-%d') <= '2007/10/20' 17471 1748Warnings: 1749Warning 1292 Truncated incorrect date value: '' 1750SELECT str_to_date('2007-20-00', '%Y-%m-%d') >= '2007/10/20' AND 1751str_to_date('2007-20-00', '%Y-%m-%d') <= ''; 1752str_to_date('2007-20-00', '%Y-%m-%d') >= '2007/10/20' AND 1753str_to_date('2007-20-00', '%Y-%m-%d') <= '' 1754NULL 1755Warnings: 1756Warning 1292 Truncated incorrect date value: '' 1757Warning 1411 Incorrect datetime value: '2007-20-00' for function str_to_date 1758Warning 1411 Incorrect datetime value: '2007-20-00' for function str_to_date 1759SELECT str_to_date('2007-10-00', '%Y-%m-%d') BETWEEN '' AND '2007/10/20'; 1760str_to_date('2007-10-00', '%Y-%m-%d') BETWEEN '' AND '2007/10/20' 17611 1762Warnings: 1763Warning 1292 Truncated incorrect datetime value: '' 1764SELECT str_to_date('2007-20-00', '%Y-%m-%d') BETWEEN '2007/10/20' AND ''; 1765str_to_date('2007-20-00', '%Y-%m-%d') BETWEEN '2007/10/20' AND '' 1766NULL 1767Warnings: 1768Warning 1411 Incorrect datetime value: '2007-20-00' for function str_to_date 1769SELECT str_to_date('', '%Y-%m-%d'); 1770str_to_date('', '%Y-%m-%d') 17710000-00-00 1772DROP TABLE t1, t2; 1773# 1774# Bug#48459: valgrind errors with query using 'Range checked for each 1775# record' 1776# 1777CREATE TABLE t1 ( 1778a INT, 1779b CHAR(2), 1780c INT, 1781d INT, 1782KEY ( c ), 1783KEY ( d, a, b ( 2 ) ), 1784KEY ( b ( 1 ) ) 1785); 1786INSERT INTO t1 VALUES ( NULL, 'a', 1, 2 ), ( NULL, 'a', 1, 2 ), 1787( 1, 'a', 1, 2 ), ( 1, 'a', 1, 2 ); 1788CREATE TABLE t2 ( 1789a INT, 1790c INT, 1791e INT, 1792KEY ( e ) 1793); 1794INSERT INTO t2 VALUES ( 1, 1, NULL ), ( 1, 1, NULL ); 1795# Should not give Valgrind warnings 1796SELECT 1 1797FROM t1, t2 1798WHERE t1.d <> '1' AND t1.b > '1' 1799AND t1.a = t2.a AND t1.c = t2.c; 18001 18011 18021 18031 18041 1805DROP TABLE t1, t2; 1806# 1807# Bug #48665: sql-bench's insert test fails due to wrong result 1808# 1809CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a)); 1810INSERT INTO t1 VALUES (0,0), (1,1); 1811EXPLAIN 1812SELECT * FROM t1 FORCE INDEX (PRIMARY) 1813WHERE (a>=1 AND a<=2) OR (a>=4 AND a<=5) OR (a>=0 AND a <=10); 1814id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1815@ @ @ NULL range @ @ @ @ @ 100.00 @ 1816Warnings: 1817@ @ @ 1818# Should return 2 rows 1819SELECT * FROM t1 FORCE INDEX (PRIMARY) 1820WHERE (a>=1 AND a<=2) OR (a>=4 AND a<=5) OR (a>=0 AND a <=10); 1821a b 18220 0 18231 1 1824DROP TABLE t1; 1825# 1826# Bug #54802: 'NOT BETWEEN' evaluation is incorrect 1827# 1828CREATE TABLE t1 (c_key INT, c_notkey INT, KEY(c_key)); 1829INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3); 1830EXPLAIN SELECT * FROM t1 WHERE 2 NOT BETWEEN c_notkey AND c_key; 1831id select_type table partitions type possible_keys key key_len ref rows filtered Extra 18321 SIMPLE t1 NULL ALL c_key NULL NULL NULL 3 66.67 Using where 1833Warnings: 1834Note 1003 /* select#1 */ select `test`.`t1`.`c_key` AS `c_key`,`test`.`t1`.`c_notkey` AS `c_notkey` from `test`.`t1` where (2 not between `test`.`t1`.`c_notkey` and `test`.`t1`.`c_key`) 1835SELECT * FROM t1 WHERE 2 NOT BETWEEN c_notkey AND c_key; 1836c_key c_notkey 18371 1 18383 3 1839DROP TABLE t1; 1840# 1841# Bug #57030: 'BETWEEN' evaluation is incorrect 1842# 1843CREATE TABLE t1(pk INT PRIMARY KEY, i4 INT); 1844CREATE UNIQUE INDEX i4_uq ON t1(i4); 1845INSERT INTO t1 VALUES (1,10), (2,20), (3,30); 1846EXPLAIN 1847SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 10; 1848id select_type table partitions type possible_keys key key_len ref rows filtered Extra 18491 SIMPLE t1 NULL const i4_uq i4_uq 5 const 1 100.00 NULL 1850Warnings: 1851Note 1003 /* select#1 */ select '1' AS `pk`,'10' AS `i4` from `test`.`t1` where ('10' between 10 and 10) 1852SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 10; 1853pk i4 18541 10 1855EXPLAIN 1856SELECT * FROM t1 WHERE 10 BETWEEN i4 AND i4; 1857id select_type table partitions type possible_keys key key_len ref rows filtered Extra 18581 SIMPLE t1 NULL const i4_uq i4_uq 5 const 1 100.00 NULL 1859Warnings: 1860Note 1003 /* select#1 */ select '1' AS `pk`,'10' AS `i4` from `test`.`t1` where (10 between '10' and '10') 1861SELECT * FROM t1 WHERE 10 BETWEEN i4 AND i4; 1862pk i4 18631 10 1864EXPLAIN 1865SELECT * FROM t1 WHERE 10 BETWEEN 10 AND i4; 1866id select_type table partitions type possible_keys key key_len ref rows filtered Extra 18671 SIMPLE t1 NULL range i4_uq i4_uq 5 NULL 3 100.00 Using index condition; Using MRR 1868Warnings: 1869Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i4` AS `i4` from `test`.`t1` where (10 between 10 and `test`.`t1`.`i4`) 1870SELECT * FROM t1 WHERE 10 BETWEEN 10 AND i4; 1871pk i4 18721 10 18732 20 18743 30 1875EXPLAIN 1876SELECT * FROM t1 WHERE 10 BETWEEN i4 AND 10; 1877id select_type table partitions type possible_keys key key_len ref rows filtered Extra 18781 SIMPLE t1 NULL range i4_uq i4_uq 5 NULL 1 100.00 Using index condition; Using MRR 1879Warnings: 1880Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i4` AS `i4` from `test`.`t1` where (10 between `test`.`t1`.`i4` and 10) 1881SELECT * FROM t1 WHERE 10 BETWEEN i4 AND 10; 1882pk i4 18831 10 1884EXPLAIN 1885SELECT * FROM t1 WHERE 10 BETWEEN 10 AND 10; 1886id select_type table partitions type possible_keys key key_len ref rows filtered Extra 18871 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 100.00 NULL 1888Warnings: 1889Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i4` AS `i4` from `test`.`t1` where 1 1890SELECT * FROM t1 WHERE 10 BETWEEN 10 AND 10; 1891pk i4 18921 10 18932 20 18943 30 1895EXPLAIN 1896SELECT * FROM t1 WHERE 10 BETWEEN 11 AND 11; 1897id select_type table partitions type possible_keys key key_len ref rows filtered Extra 18981 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 1899Warnings: 1900Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i4` AS `i4` from `test`.`t1` where 0 1901SELECT * FROM t1 WHERE 10 BETWEEN 11 AND 11; 1902pk i4 1903EXPLAIN 1904SELECT * FROM t1 WHERE 10 BETWEEN 100 AND 0; 1905id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19061 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 1907Warnings: 1908Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i4` AS `i4` from `test`.`t1` where 0 1909SELECT * FROM t1 WHERE 10 BETWEEN 100 AND 0; 1910pk i4 1911EXPLAIN 1912SELECT * FROM t1 WHERE i4 BETWEEN 100 AND 0; 1913id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19141 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table 1915Warnings: 1916Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i4` AS `i4` from `test`.`t1` where (`test`.`t1`.`i4` between 100 and 0) 1917SELECT * FROM t1 WHERE i4 BETWEEN 100 AND 0; 1918pk i4 1919EXPLAIN 1920SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 99999999999999999; 1921id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19221 SIMPLE t1 NULL range i4_uq i4_uq 5 NULL 3 100.00 Using index condition; Using MRR 1923Warnings: 1924Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i4` AS `i4` from `test`.`t1` where (`test`.`t1`.`i4` between 10 and 99999999999999999) 1925SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 99999999999999999; 1926pk i4 19271 10 19282 20 19293 30 1930EXPLAIN 1931SELECT * FROM t1 WHERE i4 BETWEEN 999999999999999 AND 30; 1932id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19331 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table 1934Warnings: 1935Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i4` AS `i4` from `test`.`t1` where (`test`.`t1`.`i4` between 999999999999999 and 30) 1936SELECT * FROM t1 WHERE i4 BETWEEN 999999999999999 AND 30; 1937pk i4 1938EXPLAIN 1939SELECT * FROM t1 WHERE i4 BETWEEN 10 AND '20'; 1940id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19411 SIMPLE t1 NULL range i4_uq i4_uq 5 NULL 1 100.00 Using index condition; Using MRR 1942Warnings: 1943Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i4` AS `i4` from `test`.`t1` where (`test`.`t1`.`i4` between 10 and '20') 1944SELECT * FROM t1 WHERE i4 BETWEEN 10 AND '20'; 1945pk i4 19461 10 19472 20 1948EXPLAIN 1949SELECT * FROM t1, t1 as t2 WHERE t2.pk BETWEEN t1.i4 AND t1.i4; 1950id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19511 SIMPLE t1 NULL ALL i4_uq NULL NULL NULL 3 100.00 NULL 19521 SIMPLE t2 NULL eq_ref PRIMARY PRIMARY 4 test.t1.i4 1 100.00 Using index condition 1953Warnings: 1954Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i4` AS `i4`,`test`.`t2`.`pk` AS `pk`,`test`.`t2`.`i4` AS `i4` from `test`.`t1` join `test`.`t1` `t2` where (`test`.`t2`.`pk` between `test`.`t1`.`i4` and `test`.`t1`.`i4`) 1955SELECT * FROM t1, t1 as t2 WHERE t2.pk BETWEEN t1.i4 AND t1.i4; 1956pk i4 pk i4 1957EXPLAIN 1958SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk; 1959id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19601 SIMPLE t1 NULL ALL i4_uq NULL NULL NULL 3 100.00 NULL 19611 SIMPLE t2 NULL eq_ref PRIMARY PRIMARY 4 test.t1.i4 1 100.00 Using index condition 1962Warnings: 1963Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i4` AS `i4`,`test`.`t2`.`pk` AS `pk`,`test`.`t2`.`i4` AS `i4` from `test`.`t1` join `test`.`t1` `t2` where (`test`.`t1`.`i4` between `test`.`t2`.`pk` and `test`.`t2`.`pk`) 1964SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk; 1965pk i4 pk i4 1966DROP TABLE t1; 1967# 1968# BUG#13519696 - 62940: SELECT RESULTS VARY WITH VERSION AND 1969# WITH/WITHOUT INDEX RANGE SCAN 1970# 1971create table t1 (id int unsigned not null auto_increment primary key); 1972insert into t1 values (null); 1973insert into t1 select null from t1; 1974insert into t1 select null from t1; 1975insert into t1 select null from t1; 1976insert into t1 select null from t1; 1977insert into t1 select null from t1; 1978insert into t1 select null from t1; 1979insert into t1 select null from t1; 1980insert into t1 select null from t1; 1981create table t2 ( 1982id int unsigned not null auto_increment, 1983val decimal(5,3) not null, 1984primary key (id,val), 1985unique key (val,id), 1986unique key (id)); 1987insert into t2 select null,id*0.0009 from t1; 1988select count(val) from t2 ignore index (val) where val > 0.1155; 1989count(val) 1990128 1991select count(val) from t2 force index (val) where val > 0.1155; 1992count(val) 1993128 1994drop table t2, t1; 1995# 1996# BUG#13453382 - REGRESSION SINCE 5.1.39, RANGE OPTIMIZER WRONG 1997# RESULTS WITH DECIMAL CONVERSION 1998# 1999create table t1 (a int,b int,c int,primary key (a,c)); 2000insert into t1 values (1,1,2),(1,1,3),(1,1,4); 2001select convert(3, signed integer) > 2.9; 2002convert(3, signed integer) > 2.9 20031 2004select * from t1 force index (primary) where a=1 and c>= 2.9; 2005a b c 20061 1 3 20071 1 4 2008select * from t1 ignore index (primary) where a=1 and c>= 2.9; 2009a b c 20101 1 3 20111 1 4 2012select * from t1 force index (primary) where a=1 and c> 2.9; 2013a b c 20141 1 3 20151 1 4 2016select * from t1 ignore index (primary) where a=1 and c> 2.9; 2017a b c 20181 1 3 20191 1 4 2020drop table t1; 2021# 2022# BUG#13463488 - 63437: CHAR & BETWEEN WITH INDEX RETURNS WRONG 2023# RESULT AFTER MYSQL 5.1. 2024# 2025CREATE TABLE t1( 2026F1 CHAR(5) NOT NULL, 2027F2 CHAR(5) NOT NULL, 2028F3 CHAR(5) NOT NULL, 2029PRIMARY KEY(F1), 2030INDEX IDX_F2(F2) 2031); 2032INSERT INTO t1 VALUES 2033('A','A','A'),('AA','AA','AA'),('AAA','AAA','AAA'), 2034('AAAA','AAAA','AAAA'),('AAAAA','AAAAA','AAAAA'); 2035SELECT * FROM t1 WHERE F1 = 'A '; 2036F1 F2 F3 2037A A A 2038SELECT * FROM t1 IGNORE INDEX(PRIMARY) WHERE F1 = 'A '; 2039F1 F2 F3 2040A A A 2041SELECT * FROM t1 WHERE F1 >= 'A '; 2042F1 F2 F3 2043A A A 2044AA AA AA 2045AAA AAA AAA 2046AAAA AAAA AAAA 2047AAAAA AAAAA AAAAA 2048SELECT * FROM t1 WHERE F1 > 'A '; 2049F1 F2 F3 2050AA AA AA 2051AAA AAA AAA 2052AAAA AAAA AAAA 2053AAAAA AAAAA AAAAA 2054SELECT * FROM t1 WHERE F1 BETWEEN 'A ' AND 'AAAAA'; 2055F1 F2 F3 2056A A A 2057AA AA AA 2058AAA AAA AAA 2059AAAA AAAA AAAA 2060AAAAA AAAAA AAAAA 2061SELECT * FROM t1 WHERE F2 BETWEEN 'A ' AND 'AAAAA'; 2062F1 F2 F3 2063A A A 2064AA AA AA 2065AAA AAA AAA 2066AAAA AAAA AAAA 2067AAAAA AAAAA AAAAA 2068SELECT * FROM t1 WHERE F3 BETWEEN 'A ' AND 'AAAAA'; 2069F1 F2 F3 2070A A A 2071AA AA AA 2072AAA AAA AAA 2073AAAA AAAA AAAA 2074AAAAA AAAAA AAAAA 2075SELECT * FROM t1 IGNORE INDEX(PRIMARY) WHERE F1 BETWEEN 'A ' AND 2076'AAAAA'; 2077F1 F2 F3 2078A A A 2079AA AA AA 2080AAA AAA AAA 2081AAAA AAAA AAAA 2082AAAAA AAAAA AAAAA 2083DROP TABLE t1; 2084End of 5.1 tests 2085CREATE TABLE t1 (c1 DECIMAL(10,0),INDEX(c1)); 2086INSERT INTO t1 VALUES (1),(2),(3); 2087SELECT c1 FROM t1 WHERE c1 >= 'A' GROUP BY 1; 2088c1 20891 20902 20913 2092Warnings: 2093Warning 1366 Incorrect decimal value: 'A' for column 'c1' at row 1 2094Warning 1292 Truncated incorrect DECIMAL value: 'A' 2095DROP TABLE t1; 2096create table t1 (a int,b int,key (b),key (a),key (b,a)); 2097insert into t1(a,b) values (1,2),(3,4),(5,6),(7,8); 2098create table t2 (c int); 2099insert into t2(c) values (1),(5),(6),(7),(8); 2100select 1 from (select c from t1,t2 where b >= 1 and a <=> c group by 1 limit 1) as d1; 21011 21021 2103drop table t1, t2; 2104# 2105# Bug #26106: Wrong plan may be chosen when there are several possible 2106# range and ref accesses 2107# 2108# Note: The fix for this bug has been reverted. The code will no longer 2109# select the optimal plan for the two following test queries. This is 2110# not due to a bug but due to minor differences in range estimates 2111# produced by the storage engine. 2112CREATE TABLE t1( 2113a INT, 2114b INT, 2115KEY k ( a ), 2116KEY l ( a, b ) 2117); 2118INSERT INTO t1(a) VALUES (1); 2119INSERT INTO t1 2120VALUES (2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3); 2121INSERT INTO t1 SELECT 3, 4 FROM t1 WHERE a = 2 AND b = 3; 2122INSERT INTO t1 SELECT 4, 1 FROM t1 WHERE a = 2 AND b = 3; 2123ANALYZE TABLE t1; 2124Table Op Msg_type Msg_text 2125test.t1 analyze status OK 2126INSERT INTO t1 VALUES (1, 2); 2127INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; 2128INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; 2129INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; 2130INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; 2131INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; 2132INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; 2133INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; 2134# This must use range over index l, not k. 2135# Update: Due to patch being reverted and minor differences in 2136# range estimates k is selected. 2137EXPLAIN SELECT * FROM t1 WHERE a = 1 AND b >= 2; 2138id select_type table partitions type possible_keys key key_len ref rows filtered Extra 21391 SIMPLE t1 NULL ref k,l k 5 const 134 33.33 Using where 2140Warnings: 2141Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 1) and (`test`.`t1`.`b` >= 2)) 2142CREATE TABLE t2( 2143a INT, 2144b INT, 2145c INT, 2146KEY k ( a ), 2147KEY l ( a, b ), 2148KEY m ( b ), 2149KEY n ( a, c ) 2150); 2151INSERT INTO t2(a) VALUES (1); 2152INSERT INTO t2 2153VALUES (2,3,3),(2,3,3),(2,3,3),(2,3,3),(2,3,3), 2154(2,3,3),(2,3,3),(2,3,3),(2,3,3),(2,3,3); 2155INSERT INTO t2 SELECT 3, 4, 4 FROM t2 WHERE a = 2 AND b = 3; 2156INSERT INTO t2 SELECT 4, 1, 1 FROM t2 WHERE a = 2 AND b = 3; 2157ANALYZE TABLE t2; 2158Table Op Msg_type Msg_text 2159test.t2 analyze status OK 2160INSERT INTO t2 VALUES (1, 2, 2); 2161INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2; 2162INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2; 2163INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2; 2164INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2; 2165INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2; 2166INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2; 2167INSERT INTO t2 VALUES (1, 1, 2); 2168# This must use range over index l, not n. 2169# Update: Due to patch being reverted and minor differences in 2170# range estimates k is selected. 2171EXPLAIN SELECT * FROM t2 WHERE a = 1 AND b >= 2 AND c >= 2; 2172id select_type table partitions type possible_keys key key_len ref rows filtered Extra 21731 SIMPLE t2 NULL ref k,l,m,n k 5 const 66 29.16 Using where 2174Warnings: 2175Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where ((`test`.`t2`.`a` = 1) and (`test`.`t2`.`b` >= 2) and (`test`.`t2`.`c` >= 2)) 2176DROP TABLE t1, t2; 2177# 2178# BUG#11765831: 'RANGE ACCESS' MAY INCORRECTLY FILTER 2179# AWAY QUALIFYING ROWS 2180# 2181CREATE TABLE t10( 2182K INT NOT NULL AUTO_INCREMENT, 2183I INT, J INT, 2184PRIMARY KEY(K), 2185KEY(I,J) 2186); 2187INSERT INTO t10(I,J) VALUES (6,1),(6,2),(6,3),(6,4),(6,5), 2188(6,6),(6,7),(6,8),(6,9),(6,0); 2189CREATE TABLE t100 LIKE t10; 2190INSERT INTO t100(I,J) SELECT X.I, X.K+(10*Y.K) FROM t10 AS X,t10 AS Y; 2191INSERT INTO t100(I,J) VALUES(8,26); 2192 2193EXPLAIN SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5); 2194id select_type table partitions type possible_keys key key_len ref rows filtered Extra 21951 SIMPLE t100 NULL range I I 10 NULL 4 100.00 Using index condition; Using MRR 2196Warnings: 2197Note 1003 /* select#1 */ select `test`.`t100`.`K` AS `K`,`test`.`t100`.`I` AS `I`,`test`.`t100`.`J` AS `J` from `test`.`t100` where ((`test`.`t100`.`I` <> 6) or ((`test`.`t100`.`J` = 5) and (`test`.`t100`.`I` <> 8))) 2198 2199SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5); 2200K I J 2201101 8 26 2202DROP TABLE t10,t100; 2203# 2204# BUG#12698916 - JOIN QUERY GIVES WRONG RESULT AT 2ND EXEC. OR 2205# AFTER FLUSH TABLES [-INT VS NULL] 2206# 2207CREATE TABLE t1 (col_int INT, pk INT) ENGINE=InnoDB STATS_PERSISTENT=0; 2208INSERT INTO t1 VALUES (-100,1),(1,6); 2209CREATE TABLE t2 ( 2210col_int_key INT, 2211col_varchar VARCHAR(100) NOT NULL DEFAULT "DEFAULT", 2212pk INT NOT NULL, 2213PRIMARY KEY (pk), 2214KEY (col_int_key) 2215) ENGINE=InnoDB STATS_PERSISTENT=0; 2216INSERT INTO t2 VALUES 2217(1,"GOOD",1),(100,"",2),(200,"",3),(300,"",4),(400,"",5),(500,"",8); 2218EXPLAIN SELECT t1.*,t2.* FROM t1 straight_join t2 2219ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk; 2220id select_type table partitions type possible_keys key key_len ref rows filtered Extra 22211 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where 22221 SIMPLE t2 NULL ref PRIMARY,col_int_key col_int_key 5 test.t1.col_int 1 33.33 Using index condition 2223Warnings: 2224Note 1003 /* select#1 */ select `test`.`t1`.`col_int` AS `col_int`,`test`.`t1`.`pk` AS `pk`,`test`.`t2`.`col_int_key` AS `col_int_key`,`test`.`t2`.`col_varchar` AS `col_varchar`,`test`.`t2`.`pk` AS `pk` from `test`.`t1` straight_join `test`.`t2` where ((`test`.`t2`.`col_int_key` = `test`.`t1`.`col_int`) and (`test`.`t2`.`pk` < `test`.`t1`.`pk`)) 2225SELECT t1.*,t2.* FROM t1 straight_join t2 2226ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk; 2227col_int pk col_int_key col_varchar pk 22281 6 1 GOOD 1 2229# need FLUSH so that InnoDB statistics change and thus plan changes 2230FLUSH TABLES; 2231EXPLAIN SELECT t1.*,t2.* FROM t1 straight_join t2 2232ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk; 2233id select_type table partitions type possible_keys key key_len ref rows filtered Extra 22341 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 22351 SIMPLE t2 NULL ALL PRIMARY,col_int_key NULL NULL NULL 6 33.33 Range checked for each record (index map: 0x3) 2236Warnings: 2237Note 1003 /* select#1 */ select `test`.`t1`.`col_int` AS `col_int`,`test`.`t1`.`pk` AS `pk`,`test`.`t2`.`col_int_key` AS `col_int_key`,`test`.`t2`.`col_varchar` AS `col_varchar`,`test`.`t2`.`pk` AS `pk` from `test`.`t1` straight_join `test`.`t2` where ((`test`.`t2`.`col_int_key` = `test`.`t1`.`col_int`) and (`test`.`t2`.`pk` < `test`.`t1`.`pk`)) 2238SELECT t1.*,t2.* FROM t1 straight_join t2 2239ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk; 2240col_int pk col_int_key col_varchar pk 22411 6 1 GOOD 1 2242DROP TABLE t1,t2; 2243# 2244# Bug#12694872 - 2245# VALGRIND: 18,816 BYTES IN 196 BLOCKS ARE DEFINITELY LOST IN UNIQUE::GET 2246# 2247CREATE TABLE t1 ( 2248pk INTEGER AUTO_INCREMENT, 2249col_int_nokey INTEGER NOT NULL, 2250col_int_key INTEGER NOT NULL, 2251col_date_key DATE NOT NULL, 2252col_varchar_key VARCHAR(1) NOT NULL, 2253col_varchar_nokey VARCHAR(1) NOT NULL, 2254PRIMARY KEY (pk), 2255KEY (col_int_key), 2256KEY (col_date_key), 2257KEY (col_varchar_key, col_int_key) 2258); 2259INSERT INTO t1 ( 2260col_int_key, 2261col_int_nokey, 2262col_date_key, 2263col_varchar_key, 2264col_varchar_nokey 2265) VALUES 2266(0, 4, '2011-08-25', 'j', 'j'), 2267(8, 6, '2004-09-18', 'v', 'v'), 2268(1, 3, '2009-12-01', 'c', 'c'), 2269(8, 5, '2004-12-17', 'm', 'm'), 2270(9, 3, '2000-03-14', 'd', 'd'), 2271(6, 2, '2006-05-25', 'y', 'y'), 2272(1, 9, '2008-01-23', 't', 't'), 2273(6, 3, '2007-06-18', 'd', 'd'), 2274(2, 8, '2002-10-13', 's', 's'), 2275(4, 1, '1900-01-01', 'r', 'r'), 2276(8, 8, '1959-04-25', 'm', 'm'), 2277(4, 8, '2006-03-09', 'b', 'b'), 2278(4, 5, '2001-06-05', 'x', 'x'), 2279(7, 7, '2006-05-28', 'g', 'g'), 2280(4, 5, '2001-04-19', 'p', 'p'), 2281(1, 1, '1900-01-01', 'q', 'q'), 2282(9, 6, '2004-08-20', 'w', 'w'), 2283(4, 2, '2004-10-10', 'd', 'd'), 2284(8, 9, '2000-04-02', 'e', 'e') 2285; 2286ALTER TABLE t1 DISABLE KEYS; 2287SELECT table2.col_date_key AS field1, 2288CONCAT ( table2.col_varchar_nokey, table1.col_varchar_nokey ) AS field2 2289FROM ( t1 AS table1 INNER JOIN t1 AS table2 2290ON (( table2.pk <> table1.pk ) AND 2291( table2.pk >= table1.col_int_nokey ) ) ) 2292WHERE ( table1.pk > 226 AND 2293table1.pk < ( 226 + 102 ) OR 2294( table1.col_int_key > 226 AND 2295table1.col_int_key < ( 226 + 36 ) OR 2296( table1.col_varchar_key <= 'h' OR 2297table1.col_int_key > 226 AND 2298table1.col_int_key < ( 226 + 227 ) ) 2299) 2300) 2301; 2302field1 field2 23031900-01-01 qb 23041900-01-01 qc 23051900-01-01 qd 23061900-01-01 qd 23071900-01-01 qd 23081900-01-01 qe 23091900-01-01 qg 23101900-01-01 rb 23111900-01-01 rc 23121900-01-01 rd 23131900-01-01 rd 23141900-01-01 rd 23151900-01-01 re 23161900-01-01 rg 23171959-04-25 mb 23181959-04-25 mc 23191959-04-25 md 23201959-04-25 md 23211959-04-25 md 23221959-04-25 me 23231959-04-25 mg 23242000-03-14 dc 23252000-03-14 dd 23262000-03-14 dd 23272000-04-02 eb 23282000-04-02 ec 23292000-04-02 ed 23302000-04-02 ed 23312000-04-02 ed 23322000-04-02 eg 23332001-04-19 pb 23342001-04-19 pc 23352001-04-19 pd 23362001-04-19 pd 23372001-04-19 pd 23382001-04-19 pe 23392001-04-19 pg 23402001-06-05 xb 23412001-06-05 xc 23422001-06-05 xd 23432001-06-05 xd 23442001-06-05 xd 23452001-06-05 xe 23462001-06-05 xg 23472002-10-13 sb 23482002-10-13 sc 23492002-10-13 sd 23502002-10-13 sd 23512002-10-13 sd 23522002-10-13 se 23532002-10-13 sg 23542004-08-20 wb 23552004-08-20 wc 23562004-08-20 wd 23572004-08-20 wd 23582004-08-20 wd 23592004-08-20 we 23602004-08-20 wg 23612004-09-18 vd 23622004-10-10 db 23632004-10-10 dc 23642004-10-10 dd 23652004-10-10 dd 23662004-10-10 de 23672004-10-10 dg 23682004-12-17 mc 23692004-12-17 md 23702004-12-17 md 23712004-12-17 md 23722006-03-09 bc 23732006-03-09 bd 23742006-03-09 bd 23752006-03-09 bd 23762006-03-09 be 23772006-03-09 bg 23782006-05-25 yc 23792006-05-25 yd 23802006-05-25 yd 23812006-05-25 yd 23822006-05-28 gb 23832006-05-28 gc 23842006-05-28 gd 23852006-05-28 gd 23862006-05-28 gd 23872006-05-28 ge 23882007-06-18 db 23892007-06-18 dc 23902007-06-18 dd 23912007-06-18 dd 23922007-06-18 dg 23932008-01-23 tc 23942008-01-23 td 23952008-01-23 td 23962008-01-23 td 23972008-01-23 tg 23982009-12-01 cd 23992009-12-01 cd 24002009-12-01 cd 2401ALTER TABLE t1 ENABLE KEYS; 2402CREATE TABLE t2 SELECT table2.col_date_key AS field1, 2403CONCAT ( table2.col_varchar_nokey, table1.col_varchar_nokey ) AS field2 2404FROM ( t1 AS table1 INNER JOIN t1 AS table2 2405ON (( table2.pk <> table1.pk ) AND 2406( table2.pk >= table1.col_int_nokey ) ) ) 2407WHERE ( table1.pk > 226 AND 2408table1.pk < ( 226 + 102 ) OR 2409( table1.col_int_key > 226 AND 2410table1.col_int_key < ( 226 + 36 ) OR 2411( table1.col_varchar_key <= 'h' OR 2412table1.col_int_key > 226 AND 2413table1.col_int_key < ( 226 + 227 ) ) 2414) 2415) 2416; 2417SELECT * FROM t2 2418WHERE (field1, field2) IN (SELECT table2.col_date_key AS field1, 2419CONCAT ( table2.col_varchar_nokey, table1.col_varchar_nokey ) AS field2 2420FROM ( t1 AS table1 INNER JOIN t1 AS table2 2421ON (( table2.pk <> table1.pk ) AND 2422( table2.pk >= table1.col_int_nokey ) ) ) 2423WHERE ( table1.pk > 226 AND 2424table1.pk < ( 226 + 102 ) OR 2425( table1.col_int_key > 226 AND 2426table1.col_int_key < ( 226 + 36 ) OR 2427( table1.col_varchar_key <= 'h' OR 2428table1.col_int_key > 226 AND 2429table1.col_int_key < ( 226 + 227 ) ) 2430) 2431) 2432); 2433field1 field2 24341900-01-01 qb 24351900-01-01 qc 24361900-01-01 qd 24371900-01-01 qd 24381900-01-01 qd 24391900-01-01 qe 24401900-01-01 qg 24411900-01-01 rb 24421900-01-01 rc 24431900-01-01 rd 24441900-01-01 rd 24451900-01-01 rd 24461900-01-01 re 24471900-01-01 rg 24481959-04-25 mb 24491959-04-25 mc 24501959-04-25 md 24511959-04-25 md 24521959-04-25 md 24531959-04-25 me 24541959-04-25 mg 24552000-03-14 dc 24562000-03-14 dd 24572000-03-14 dd 24582000-04-02 eb 24592000-04-02 ec 24602000-04-02 ed 24612000-04-02 ed 24622000-04-02 ed 24632000-04-02 eg 24642001-04-19 pb 24652001-04-19 pc 24662001-04-19 pd 24672001-04-19 pd 24682001-04-19 pd 24692001-04-19 pe 24702001-04-19 pg 24712001-06-05 xb 24722001-06-05 xc 24732001-06-05 xd 24742001-06-05 xd 24752001-06-05 xd 24762001-06-05 xe 24772001-06-05 xg 24782002-10-13 sb 24792002-10-13 sc 24802002-10-13 sd 24812002-10-13 sd 24822002-10-13 sd 24832002-10-13 se 24842002-10-13 sg 24852004-08-20 wb 24862004-08-20 wc 24872004-08-20 wd 24882004-08-20 wd 24892004-08-20 wd 24902004-08-20 we 24912004-08-20 wg 24922004-09-18 vd 24932004-10-10 db 24942004-10-10 dc 24952004-10-10 dd 24962004-10-10 dd 24972004-10-10 de 24982004-10-10 dg 24992004-12-17 mc 25002004-12-17 md 25012004-12-17 md 25022004-12-17 md 25032006-03-09 bc 25042006-03-09 bd 25052006-03-09 bd 25062006-03-09 bd 25072006-03-09 be 25082006-03-09 bg 25092006-05-25 yc 25102006-05-25 yd 25112006-05-25 yd 25122006-05-25 yd 25132006-05-28 gb 25142006-05-28 gc 25152006-05-28 gd 25162006-05-28 gd 25172006-05-28 gd 25182006-05-28 ge 25192007-06-18 db 25202007-06-18 dc 25212007-06-18 dd 25222007-06-18 dd 25232007-06-18 dg 25242008-01-23 tc 25252008-01-23 td 25262008-01-23 td 25272008-01-23 td 25282008-01-23 tg 25292009-12-01 cd 25302009-12-01 cd 25312009-12-01 cd 2532DROP TABLE t1, t2; 2533# 2534# BUG#12912171 - ASSERTION FAILED: QUICK->HEAD->READ_SET == 2535# SAVE_READ_SET 2536# 2537CREATE TABLE t1 ( 2538a INT, 2539b INT, 2540c INT, 2541PRIMARY KEY (c,a), KEY (a),KEY (a) 2542) ENGINE=INNODB PARTITION BY KEY () PARTITIONS 2; 2543Warnings: 2544Warning 1831 Duplicate index 'a_2' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release. 2545INSERT INTO t1 VALUES (1,5,1),(2,4,1),(3,3,1),(4,2,1),(5,1,1); 2546UPDATE t1 SET b = 0, c=1 WHERE a <=>0; 2547SELECT * FROM t1; 2548a b c 25491 5 1 25502 4 1 25513 3 1 25524 2 1 25535 1 1 2554DROP TABLE t1; 2555# 2556# BUG#13256446 - ASSERTION QUICK->HEAD->READ_SET == 2557# SAVE_READ_SET' FAILED IN OPT_RANGE.CC:1606 2558# 2559CREATE TABLE t1 ( 2560f1 INT AUTO_INCREMENT, 2561f2 INT, 2562f3 INT, 2563f4 INT, 2564PRIMARY KEY (f1),KEY(f2) 2565) ENGINE=INNODB; 2566CREATE ALGORITHM=MERGE VIEW v2 AS SELECT f1,f2,f3,f4 FROM t1 WHERE f2=103; 2567INSERT INTO t1 VALUES (154,0,NULL,0),(0,NULL,9,0), 2568(NULL,102,NULL,3),(0,3,NULL,0), (9,0,NULL,0),(0,9,NULL,157); 2569SELECT * FROM v2; 2570f1 f2 f3 f4 2571UPDATE v2 SET f4=0, f2=NULL, f1=NULL WHERE f1 > 16 ORDER BY f1; 2572SELECT * FROM v2; 2573f1 f2 f3 f4 2574DROP TABLE t1; 2575DROP VIEW v2; 2576CREATE TABLE t1 ( 2577f1 INT AUTO_INCREMENT, 2578f2 INT, 2579f3 INT, 2580f4 INT, 2581PRIMARY KEY (f1),KEY(f2) 2582) ENGINE=INNODB; 2583INSERT INTO t1 VALUES(1,NULL,NULL,0), (2,2,0,3), (9,0,107,18), 2584(10,0,0,0), (231,0,0,0), (232,0,8,0), (234,0,0,NULL), (235,8,0,3); 2585CREATE ALGORITHM=MERGE VIEW v3 AS SELECT f1,f2,f3,f4 FROM t1 WHERE f1<=85 ; 2586SELECT * FROM v3; 2587f1 f2 f3 f4 25881 NULL NULL 0 25892 2 0 3 25909 0 107 18 259110 0 0 0 2592UPDATE v3 SET f3=0, f4=4 WHERE f2=68 ORDER BY f1; 2593SELECT * FROM v3; 2594f1 f2 f3 f4 25951 NULL NULL 0 25962 2 0 3 25979 0 107 18 259810 0 0 0 2599DROP TABLE t1; 2600DROP VIEW v3; 2601# 2602# BUG#13731380: RANGE OPTIMIZER CALLS RECORDS_IN_RANGE() FOR OPEN RANGE 2603# 2604CREATE TABLE t1 (pk INT PRIMARY KEY); 2605INSERT INTO t1 VALUES (1),(3),(5); 2606SELECT * FROM t1 WHERE pk <> 3 OR pk < 4; 2607pk 26081 26093 26105 2611DROP TABLE t1; 2612# 2613# BUG#13803810: TOO FEW ROWS RETURNED FOR RANGE ACCESS IN 2614# VARCHAR INDEX USING DATETIME VALUE 2615 2616CREATE TABLE t1 (a DATETIME); 2617INSERT INTO t1 VALUES ('2001-01-01 00:00:00'); 2618INSERT INTO t1 VALUES ('2001-01-01 11:22:33'); 2619CREATE TABLE t2 (b VARCHAR(64), KEY (b)); 2620INSERT INTO t2 VALUES ('2001-01-01'); 2621INSERT INTO t2 VALUES ('2001.01.01'); 2622INSERT INTO t2 VALUES ('2001#01#01'); 2623INSERT INTO t2 VALUES ('2001-01-01 00:00:00'); 2624INSERT INTO t2 VALUES ('2001-01-01 11:22:33'); 2625 2626# range/ref access cannot be used for this query 2627 2628EXPLAIN SELECT * FROM t2 WHERE b=CAST('2001-01-01' AS DATE); 2629id select_type table partitions type possible_keys key key_len ref rows filtered Extra 26301 SIMPLE t2 NULL index b b 67 NULL 5 20.00 Using where; Using index 2631Warnings: 2632Warning 1739 Cannot use ref access on index 'b' due to type or collation conversion on field 'b' 2633Warning 1739 Cannot use range access on index 'b' due to type or collation conversion on field 'b' 2634Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = <cache>(cast('2001-01-01' as date))) 2635SELECT * FROM t2 WHERE b=CAST('2001-01-01' AS DATE); 2636b 26372001#01#01 26382001-01-01 26392001-01-01 00:00:00 26402001.01.01 2641 2642# range/ref access cannot be used for any of the queries below. 2643# See BUG#13814468 about 'Range checked for each record' 2644 2645EXPLAIN SELECT * FROM t1, t2 WHERE a=b ORDER BY BINARY a, BINARY b; 2646id select_type table partitions type possible_keys key key_len ref rows filtered Extra 26471 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort 26481 SIMPLE t2 NULL index b b 67 NULL 5 20.00 Using where; Using index; Using join buffer (Block Nested Loop) 2649Warnings: 2650Warning 1739 Cannot use ref access on index 'b' due to type or collation conversion on field 'b' 2651Warning 1739 Cannot use range access on index 'b' due to type or collation conversion on field 'b' 2652Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`b`) order by cast(`test`.`t1`.`a` as char charset binary),cast(`test`.`t2`.`b` as char charset binary) 2653SELECT * FROM t1, t2 WHERE a=b ORDER BY BINARY a, BINARY b; 2654a b 26552001-01-01 00:00:00 2001#01#01 26562001-01-01 00:00:00 2001-01-01 26572001-01-01 00:00:00 2001-01-01 00:00:00 26582001-01-01 00:00:00 2001.01.01 26592001-01-01 11:22:33 2001-01-01 11:22:33 2660 2661EXPLAIN SELECT * FROM t1, t2 WHERE b=a ORDER BY BINARY a, BINARY b; 2662id select_type table partitions type possible_keys key key_len ref rows filtered Extra 26631 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort 26641 SIMPLE t2 NULL index b b 67 NULL 5 20.00 Using where; Using index; Using join buffer (Block Nested Loop) 2665Warnings: 2666Warning 1739 Cannot use ref access on index 'b' due to type or collation conversion on field 'b' 2667Warning 1739 Cannot use range access on index 'b' due to type or collation conversion on field 'b' 2668Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`b` = `test`.`t1`.`a`) order by cast(`test`.`t1`.`a` as char charset binary),cast(`test`.`t2`.`b` as char charset binary) 2669SELECT * FROM t1, t2 WHERE b=a ORDER BY BINARY a, BINARY b; 2670a b 26712001-01-01 00:00:00 2001#01#01 26722001-01-01 00:00:00 2001-01-01 26732001-01-01 00:00:00 2001-01-01 00:00:00 26742001-01-01 00:00:00 2001.01.01 26752001-01-01 11:22:33 2001-01-01 11:22:33 2676 2677DROP TABLE t1,t2; 2678# 2679# WL#7019: Add support for row value constructors in in predicates to 2680# range optimizer 2681# 2682CREATE TABLE t1 (a INT, b INT, c INT, KEY x(a, b)); 2683INSERT INTO t1 VALUES (2, 2, 2), (3, 3, 3), (4, 4, 4), (5, 5, 5), (6, 6, 6), 2684(7, 7, 7), (8, 8, 8), (9, 9, 9); 2685INSERT INTO t1 SELECT * FROM t1; 2686INSERT INTO t1 SELECT * FROM t1; 2687INSERT INTO t1 SELECT * FROM t1; 2688INSERT INTO t1 SELECT * FROM t1; 2689INSERT INTO t1 SELECT * FROM t1; 2690INSERT INTO t1 SELECT * FROM t1; 2691INSERT INTO t1 SELECT * FROM t1; 2692INSERT INTO t1 SELECT * FROM t1; 2693INSERT INTO t1 SELECT * FROM t1; 2694INSERT INTO t1 VALUES (0, 0, 0), (1, 1, 1); 2695ANALYZE TABLE t1; 2696Table Op Msg_type Msg_text 2697test.t1 analyze status OK 2698CREATE TABLE t2 (a INT, b INT, c INT, d INT, KEY x(a, b)); 2699INSERT INTO t2 VALUES (2, 2, 2, 2), (3, 3, 3, 3), (4, 4, 4, 4), (5, 5, 5, 5), 2700(6, 6, 6, 6), (7, 7, 7, 7), (8, 8, 8, 8), (9, 9, 9, 9); 2701INSERT INTO t2 SELECT * FROM t2; 2702INSERT INTO t2 SELECT * FROM t2; 2703INSERT INTO t2 SELECT * FROM t2; 2704INSERT INTO t2 SELECT * FROM t2; 2705INSERT INTO t2 SELECT * FROM t2; 2706INSERT INTO t2 SELECT * FROM t2; 2707INSERT INTO t2 SELECT * FROM t2; 2708INSERT INTO t2 SELECT * FROM t2; 2709INSERT INTO t2 SELECT * FROM t2; 2710INSERT INTO t2 VALUES (0, 0, 0, 0), (1, 1, 1, 1); 2711ANALYZE TABLE t2; 2712Table Op Msg_type Msg_text 2713test.t2 analyze status OK 2714CREATE TABLE t3 (a INT, b INT, c INT, d INT, KEY x(a, b, c)); 2715INSERT INTO t3 VALUES (2, 2, 2, 2), (3, 3, 3, 3), (4, 4, 4, 4), (5, 5, 5, 5), 2716(6, 6, 6, 6), (7, 7, 7, 7), (8, 8, 8, 8), (9, 9, 9, 9); 2717INSERT INTO t3 SELECT * FROM t3; 2718INSERT INTO t3 SELECT * FROM t3; 2719INSERT INTO t3 SELECT * FROM t3; 2720INSERT INTO t3 SELECT * FROM t3; 2721INSERT INTO t3 SELECT * FROM t3; 2722INSERT INTO t3 SELECT * FROM t3; 2723INSERT INTO t3 SELECT * FROM t3; 2724INSERT INTO t3 SELECT * FROM t3; 2725INSERT INTO t3 SELECT * FROM t3; 2726INSERT INTO t3 VALUES (0, 0, 0, 0), (1, 1, 1, 1); 2727ANALYZE TABLE t3; 2728Table Op Msg_type Msg_text 2729test.t3 analyze status OK 2730EXPLAIN SELECT a, b FROM t1 WHERE (a, b) IN ((NULL, NULL), (NULL, NULL)); 2731id select_type table partitions type possible_keys key key_len ref rows filtered Extra 27321 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table 2733Warnings: 2734Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a`,`test`.`t1`.`b`) in ((NULL,NULL),(NULL,NULL))) 2735EXPLAIN SELECT a, b FROM t1 WHERE (a = 0 AND b = 0) OR (a = 1 AND b = 1); 2736id select_type table partitions type possible_keys key key_len ref rows filtered Extra 27371 SIMPLE t1 NULL range x x 10 NULL 2 100.00 Using where; Using index 2738Warnings: 2739Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (((`test`.`t1`.`b` = 0) and (`test`.`t1`.`a` = 0)) or ((`test`.`t1`.`b` = 1) and (`test`.`t1`.`a` = 1))) 2740FLUSH STATUS; 2741SELECT a, b FROM t1 WHERE (a = 0 AND b = 0) OR (a = 1 AND b = 1); 2742a b 27430 0 27441 1 2745SHOW STATUS LIKE 'Handler_read_%'; 2746Variable_name Value 2747Handler_read_first 0 2748Handler_read_key 2 2749Handler_read_last 0 2750Handler_read_next 2 2751Handler_read_prev 0 2752Handler_read_rnd 0 2753Handler_read_rnd_next 0 2754EXPLAIN SELECT a, b FROM t1 WHERE (a, b) IN ((0, 0)); 2755id select_type table partitions type possible_keys key key_len ref rows filtered Extra 27561 SIMPLE t1 NULL ref x x 10 const,const 1 100.00 Using index 2757Warnings: 2758Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`b` = 0) and (`test`.`t1`.`a` = 0)) 2759FLUSH STATUS; 2760SELECT a, b FROM t1 WHERE (a, b) IN ((0, 0)); 2761a b 27620 0 2763SHOW STATUS LIKE 'Handler_read_%'; 2764Variable_name Value 2765Handler_read_first 0 2766Handler_read_key 1 2767Handler_read_last 0 2768Handler_read_next 1 2769Handler_read_prev 0 2770Handler_read_rnd 0 2771Handler_read_rnd_next 0 2772EXPLAIN SELECT a, b FROM t1 WHERE (a, b) IN ((0, 0), (1, 1)); 2773id select_type table partitions type possible_keys key key_len ref rows filtered Extra 27741 SIMPLE t1 NULL range x x 10 NULL 2 100.00 Using where; Using index 2775Warnings: 2776Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a`,`test`.`t1`.`b`) in (<cache>((0,0)),<cache>((1,1)))) 2777FLUSH STATUS; 2778SELECT a, b FROM t1 WHERE (a, b) IN ((0, 0), (1, 1)); 2779a b 27800 0 27811 1 2782SHOW STATUS LIKE 'Handler_read_%'; 2783Variable_name Value 2784Handler_read_first 0 2785Handler_read_key 2 2786Handler_read_last 0 2787Handler_read_next 2 2788Handler_read_prev 0 2789Handler_read_rnd 0 2790Handler_read_rnd_next 0 2791EXPLAIN SELECT a, b, c FROM t2 WHERE (a, b, c) IN ((0, 0, 0), (1, 1, 1)); 2792id select_type table partitions type possible_keys key key_len ref rows filtered Extra 27931 SIMPLE t2 NULL range x x 10 NULL 2 20.00 Using where; Using MRR 2794Warnings: 2795Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where ((`test`.`t2`.`a`,`test`.`t2`.`b`,`test`.`t2`.`c`) in (<cache>((0,0,0)),<cache>((1,1,1)))) 2796FLUSH STATUS; 2797SELECT a, b, c FROM t2 WHERE (a, b, c) IN ((0, 0, 0), (1, 1, 1)); 2798a b c 27990 0 0 28001 1 1 2801SHOW STATUS LIKE 'Handler_read_%'; 2802Variable_name Value 2803Handler_read_first 0 2804Handler_read_key 2 2805Handler_read_last 0 2806Handler_read_next 2 2807Handler_read_prev 0 2808Handler_read_rnd 2 2809Handler_read_rnd_next 0 2810EXPLAIN SELECT a, b, c FROM t3 WHERE (a, b, c) IN ((0, 0, 0), (1, 1, 1)); 2811id select_type table partitions type possible_keys key key_len ref rows filtered Extra 28121 SIMPLE t3 NULL range x x 15 NULL 3 100.00 Using where; Using index 2813Warnings: 2814Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t3` where ((`test`.`t3`.`a`,`test`.`t3`.`b`,`test`.`t3`.`c`) in (<cache>((0,0,0)),<cache>((1,1,1)))) 2815FLUSH STATUS; 2816SELECT a, b, c FROM t3 WHERE (a, b, c) IN ((0, 0, 0), (1, 1, 1)); 2817a b c 28180 0 0 28191 1 1 2820SHOW STATUS LIKE 'Handler_read_%'; 2821Variable_name Value 2822Handler_read_first 0 2823Handler_read_key 2 2824Handler_read_last 0 2825Handler_read_next 2 2826Handler_read_prev 0 2827Handler_read_rnd 0 2828Handler_read_rnd_next 0 2829EXPLAIN SELECT a, b FROM t1 WHERE a = 0 AND b = 0 OR (a, b) IN ((1, 1)); 2830id select_type table partitions type possible_keys key key_len ref rows filtered Extra 28311 SIMPLE t1 NULL range x x 10 NULL 2 100.00 Using where; Using index 2832Warnings: 2833Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (((`test`.`t1`.`b` = 0) and (`test`.`t1`.`a` = 0)) or ((`test`.`t1`.`b` = 1) and (`test`.`t1`.`a` = 1))) 2834FLUSH STATUS; 2835SELECT a, b FROM t1 WHERE a = 0 AND b = 0 OR (a, b) IN ((1, 1)); 2836a b 28370 0 28381 1 2839SHOW STATUS LIKE 'Handler_read_%'; 2840Variable_name Value 2841Handler_read_first 0 2842Handler_read_key 2 2843Handler_read_last 0 2844Handler_read_next 2 2845Handler_read_prev 0 2846Handler_read_rnd 0 2847Handler_read_rnd_next 0 2848EXPLAIN SELECT a, b FROM t1 WHERE (a, b) IN ((1, 1)) OR a = 0 AND b = 0; 2849id select_type table partitions type possible_keys key key_len ref rows filtered Extra 28501 SIMPLE t1 NULL range x x 10 NULL 2 100.00 Using where; Using index 2851Warnings: 2852Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (((`test`.`t1`.`b` = 1) and (`test`.`t1`.`a` = 1)) or ((`test`.`t1`.`b` = 0) and (`test`.`t1`.`a` = 0))) 2853FLUSH STATUS; 2854SELECT a, b FROM t1 WHERE (a, b) IN ((1, 1)) OR a = 0 AND b = 0; 2855a b 28560 0 28571 1 2858SHOW STATUS LIKE 'Handler_read_%'; 2859Variable_name Value 2860Handler_read_first 0 2861Handler_read_key 2 2862Handler_read_last 0 2863Handler_read_next 2 2864Handler_read_prev 0 2865Handler_read_rnd 0 2866Handler_read_rnd_next 0 2867EXPLAIN SELECT a, b FROM t2 WHERE (a, b, c) IN ((1, 1, 1)) OR a = 0 AND b = 0 AND c = 0; 2868id select_type table partitions type possible_keys key key_len ref rows filtered Extra 28691 SIMPLE t2 NULL range x x 10 NULL 2 19.00 Using index condition; Using where; Using MRR 2870Warnings: 2871Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (((`test`.`t2`.`c` = 1) and (`test`.`t2`.`b` = 1) and (`test`.`t2`.`a` = 1)) or ((`test`.`t2`.`c` = 0) and (`test`.`t2`.`b` = 0) and (`test`.`t2`.`a` = 0))) 2872FLUSH STATUS; 2873SELECT a, b FROM t2 WHERE (a, b, c) IN ((1, 1, 1)) OR a = 0 AND b = 0 AND c = 0; 2874a b 28750 0 28761 1 2877SHOW STATUS LIKE 'Handler_read_%'; 2878Variable_name Value 2879Handler_read_first 0 2880Handler_read_key 2 2881Handler_read_last 0 2882Handler_read_next 2 2883Handler_read_prev 0 2884Handler_read_rnd 2 2885Handler_read_rnd_next 0 2886EXPLAIN SELECT a, b FROM t3 WHERE (a, b, c) IN ((1, 1, 1)) OR a = 0 AND b = 0 AND c = 0; 2887id select_type table partitions type possible_keys key key_len ref rows filtered Extra 28881 SIMPLE t3 NULL range x x 15 NULL 3 100.00 Using where; Using index 2889Warnings: 2890Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where (((`test`.`t3`.`c` = 1) and (`test`.`t3`.`b` = 1) and (`test`.`t3`.`a` = 1)) or ((`test`.`t3`.`c` = 0) and (`test`.`t3`.`b` = 0) and (`test`.`t3`.`a` = 0))) 2891FLUSH STATUS; 2892SELECT a, b FROM t3 WHERE (a, b, c) IN ((1, 1, 1)) OR a = 0 AND b = 0 AND c = 0; 2893a b 28940 0 28951 1 2896SHOW STATUS LIKE 'Handler_read_%'; 2897Variable_name Value 2898Handler_read_first 0 2899Handler_read_key 2 2900Handler_read_last 0 2901Handler_read_next 2 2902Handler_read_prev 0 2903Handler_read_rnd 0 2904Handler_read_rnd_next 0 2905EXPLAIN SELECT a, b FROM t1 WHERE (a, b) IN (((SELECT 1), 1)); 2906id select_type table partitions type possible_keys key key_len ref rows filtered Extra 29071 SIMPLE t1 NULL ref x x 10 const,const 1 100.00 Using index 2908Warnings: 2909Note 1249 Select 2 was reduced during optimization 2910Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`b` = 1) and (`test`.`t1`.`a` = 1)) 2911FLUSH STATUS; 2912SELECT a, b FROM t1 WHERE (a, b) IN (((SELECT 1), 1)); 2913a b 29141 1 2915SHOW STATUS LIKE 'Handler_read_%'; 2916Variable_name Value 2917Handler_read_first 0 2918Handler_read_key 1 2919Handler_read_last 0 2920Handler_read_next 1 2921Handler_read_prev 0 2922Handler_read_rnd 0 2923Handler_read_rnd_next 0 2924EXPLAIN SELECT a, b FROM t1 WHERE (a, b) IN (('0', 0), (1, 1)); 2925id select_type table partitions type possible_keys key key_len ref rows filtered Extra 29261 SIMPLE t1 NULL range x x 10 NULL 2 100.00 Using where; Using index 2927Warnings: 2928Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a`,`test`.`t1`.`b`) in (<cache>(('0',0)),<cache>((1,1)))) 2929FLUSH STATUS; 2930SELECT a, b FROM t1 WHERE (a, b) IN (('0', 0), (1, 1)); 2931a b 29320 0 29331 1 2934SHOW STATUS LIKE 'Handler_read_%'; 2935Variable_name Value 2936Handler_read_first 0 2937Handler_read_key 2 2938Handler_read_last 0 2939Handler_read_next 2 2940Handler_read_prev 0 2941Handler_read_rnd 0 2942Handler_read_rnd_next 0 2943SET @v = 0; 2944EXPLAIN SELECT a, b FROM t1 WHERE (a, b) IN ((@v, 0), (1, 1)); 2945id select_type table partitions type possible_keys key key_len ref rows filtered Extra 29461 SIMPLE t1 NULL range x x 10 NULL 2 100.00 Using where; Using index 2947Warnings: 2948Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a`,`test`.`t1`.`b`) in (<cache>(((@`v`),0)),<cache>((1,1)))) 2949FLUSH STATUS; 2950SELECT a, b FROM t1 WHERE (a, b) IN ((@v, 0), (1, 1)); 2951a b 29520 0 29531 1 2954SHOW STATUS LIKE 'Handler_read_%'; 2955Variable_name Value 2956Handler_read_first 0 2957Handler_read_key 2 2958Handler_read_last 0 2959Handler_read_next 2 2960Handler_read_prev 0 2961Handler_read_rnd 0 2962Handler_read_rnd_next 0 2963CREATE TABLE t4 ( a INT, b INT ); 2964INSERT INTO t4 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); 2965INSERT INTO t4 SELECT a + 5, b + 5 FROM t4; 2966INSERT INTO t4 SELECT * FROM t4; 2967INSERT INTO t4 SELECT * FROM t4; 2968INSERT INTO t4 SELECT * FROM t4; 2969INSERT INTO t4 SELECT * FROM t4; 2970INSERT INTO t4 SELECT * FROM t4; 2971INSERT INTO t4 SELECT * FROM t4; 2972# Inner table in a nested-loops join 2973EXPLAIN 2974SELECT t4.*, t1.a, t1.b 2975FROM t4 JOIN t1 USING(a, b) 2976WHERE (t4.a, t4.b) IN ((1, 1), (0, 0)); 2977id select_type table partitions type possible_keys key key_len ref rows filtered Extra 29781 SIMPLE t4 NULL ALL NULL NULL NULL NULL 640 2.00 Using where 29791 SIMPLE t1 NULL ref x x 10 test.t4.a,test.t4.b 410 100.00 Using index 2980Warnings: 2981Note 1003 /* select#1 */ select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t4` join `test`.`t1` where ((`test`.`t1`.`b` = `test`.`t4`.`b`) and (`test`.`t1`.`a` = `test`.`t4`.`a`) and ((`test`.`t4`.`a`,`test`.`t4`.`b`) in (<cache>((1,1)),<cache>((0,0))))) 2982# Join on IN 2983EXPLAIN 2984SELECT t4.*, t1.a, t1.b 2985FROM t4 JOIN t1 2986WHERE (t1.a, t1.b) IN ((t4.a, t4.b), (0, 0)); 2987id select_type table partitions type possible_keys key key_len ref rows filtered Extra 29881 SIMPLE t4 NULL ALL NULL NULL NULL NULL 640 100.00 NULL 29891 SIMPLE t1 NULL index x x 10 NULL 4098 2.00 Using where; Using index; Using join buffer (Block Nested Loop) 2990Warnings: 2991Note 1003 /* select#1 */ select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t4` join `test`.`t1` where ((`test`.`t1`.`a`,`test`.`t1`.`b`) in ((`test`.`t4`.`a`,`test`.`t4`.`b`),<cache>((0,0)))) 2992EXPLAIN 2993SELECT t4.*, t1.a, t1.b 2994FROM t4 JOIN t1 2995WHERE (t1.a, t1.b) IN ((t4.a, t4.b), (1, 1)); 2996id select_type table partitions type possible_keys key key_len ref rows filtered Extra 29971 SIMPLE t4 NULL ALL NULL NULL NULL NULL 640 100.00 NULL 29981 SIMPLE t1 NULL index x x 10 NULL 4098 2.00 Using where; Using index; Using join buffer (Block Nested Loop) 2999Warnings: 3000Note 1003 /* select#1 */ select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t4` join `test`.`t1` where ((`test`.`t1`.`a`,`test`.`t1`.`b`) in ((`test`.`t4`.`a`,`test`.`t4`.`b`),<cache>((1,1)))) 3001# 3002# Tests for non-deterministic functions. 3003# 3004CREATE FUNCTION f1() RETURNS INT NOT DETERMINISTIC RETURN 1; 3005# The statement immediately below should not use range access. 3006EXPLAIN SELECT a, b FROM t1 WHERE (a, b, c) IN ((0, 0, 0), (f1(), 1, 1)); 3007id select_type table partitions type possible_keys key key_len ref rows filtered Extra 30081 SIMPLE t1 NULL ALL x NULL NULL NULL 4098 0.20 Using where 3009Warnings: 3010Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a`,`test`.`t1`.`b`,`test`.`t1`.`c`) in (<cache>((0,0,0)),(`f1`(),1,1))) 3011EXPLAIN SELECT a, b FROM t1 WHERE (a, b, c) IN ((0, 0, 0), (1, f1(), 1)); 3012id select_type table partitions type possible_keys key key_len ref rows filtered Extra 30131 SIMPLE t1 NULL range x x 10 NULL 2 20.00 Using where; Using MRR 3014Warnings: 3015Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a`,`test`.`t1`.`b`,`test`.`t1`.`c`) in (<cache>((0,0,0)),(1,`f1`(),1))) 3016EXPLAIN SELECT a, b FROM t1 WHERE (a, b, c) IN ((0, 0, 0), (1, 1, f1())); 3017id select_type table partitions type possible_keys key key_len ref rows filtered Extra 30181 SIMPLE t1 NULL range x x 10 NULL 2 20.00 Using where; Using MRR 3019Warnings: 3020Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a`,`test`.`t1`.`b`,`test`.`t1`.`c`) in (<cache>((0,0,0)),(1,1,`f1`()))) 3021# The statement immediately below should not use range access. 3022EXPLAIN SELECT a, b FROM t1 WHERE (a, b) IN ((f1(), 1)); 3023id select_type table partitions type possible_keys key key_len ref rows filtered Extra 30241 SIMPLE t1 NULL index NULL x 10 NULL 4098 1.00 Using where; Using index 3025Warnings: 3026Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`b` = 1) and (`test`.`t1`.`a` = `f1`())) 3027DROP FUNCTION f1; 3028# The statement immediately below should not use range access. 3029EXPLAIN 3030SELECT a, b 3031FROM t1 AS t1o 3032WHERE EXISTS ( SELECT 1 FROM t1 WHERE (a, b) IN ((t1o.a, t1o.b)) ); 3033id select_type table partitions type possible_keys key key_len ref rows filtered Extra 30341 PRIMARY t1o NULL index NULL x 10 NULL 4098 100.00 Using where; Using index 30352 DEPENDENT SUBQUERY t1 NULL ref x x 10 test.t1o.a,test.t1o.b 410 100.00 Using index 3036Warnings: 3037Note 1276 Field or reference 'test.t1o.a' of SELECT #2 was resolved in SELECT #1 3038Note 1276 Field or reference 'test.t1o.b' of SELECT #2 was resolved in SELECT #1 3039Note 1003 /* select#1 */ select `test`.`t1o`.`a` AS `a`,`test`.`t1o`.`b` AS `b` from `test`.`t1` `t1o` where exists(/* select#2 */ select 1 from `test`.`t1` where ((`test`.`t1`.`a` = `test`.`t1o`.`a`) and (`test`.`t1`.`b` = `test`.`t1o`.`b`))) 3040# 3041# Tests of dynamic range access 3042# 3043CREATE TABLE t5 (a int, b int, KEY (a)); 3044INSERT INTO t5 VALUES (1, 1), (2, 1), (3, 1), (4, 1), (5, 1), (6, 1); 3045CREATE TABLE t6 (a int, b int, KEY (a)); 3046INSERT INTO t6 VALUES (3, 2), (4, 2), (100, 100), (101, 201), (102, 102); 3047CREATE TABLE t7 (a int, b int, KEY (a, b)); 3048INSERT INTO t7 VALUES (1, 1), (2, 2), (1000, 1000), (1001, 1001), (1002, 1002), 3049(1003, 1003), (1004, 1004); 3050EXPLAIN SELECT * 3051FROM t5 JOIN t6 ON t5.a = t6.a JOIN t7 3052WHERE t7.a IN (t5.b, t6.b); 3053id select_type table partitions type possible_keys key key_len ref rows filtered Extra 30541 SIMPLE t6 NULL ALL a NULL NULL NULL 5 100.00 NULL 30551 SIMPLE t5 NULL ALL a NULL NULL NULL 6 16.67 Using where; Using join buffer (Block Nested Loop) 30561 SIMPLE t7 NULL ALL a NULL NULL NULL 7 28.57 Range checked for each record (index map: 0x1) 3057Warnings: 3058Note 1003 /* select#1 */ select `test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b` from `test`.`t5` join `test`.`t6` join `test`.`t7` where ((`test`.`t5`.`a` = `test`.`t6`.`a`) and (`test`.`t7`.`a` in (`test`.`t5`.`b`,`test`.`t6`.`b`))) 3059FLUSH STATUS; 3060SELECT * 3061FROM t5 JOIN t6 ON t5.a = t6.a JOIN t7 3062WHERE t7.a IN (t5.b, t6.b); 3063a b a b a b 30643 1 3 2 1 1 30653 1 3 2 2 2 30664 1 4 2 1 1 30674 1 4 2 2 2 3068SHOW STATUS LIKE 'Handler_read_%'; 3069Variable_name Value 3070Handler_read_first 0 3071Handler_read_key 4 3072Handler_read_last 0 3073Handler_read_next 4 3074Handler_read_prev 0 3075Handler_read_rnd 0 3076Handler_read_rnd_next 13 3077EXPLAIN SELECT * 3078FROM t5 JOIN t6 ON t5.a = t6.a JOIN t7 3079WHERE (t7.a, t7.b) IN ((t5.b, 1), (t6.b, 1)); 3080id select_type table partitions type possible_keys key key_len ref rows filtered Extra 30811 SIMPLE t6 NULL ALL a NULL NULL NULL 5 100.00 NULL 30821 SIMPLE t5 NULL ALL a NULL NULL NULL 6 16.67 Using where; Using join buffer (Block Nested Loop) 30831 SIMPLE t7 NULL ALL a NULL NULL NULL 7 14.29 Range checked for each record (index map: 0x1) 3084Warnings: 3085Note 1003 /* select#1 */ select `test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b` from `test`.`t5` join `test`.`t6` join `test`.`t7` where ((`test`.`t5`.`a` = `test`.`t6`.`a`) and ((`test`.`t7`.`a`,`test`.`t7`.`b`) in ((`test`.`t5`.`b`,1),(`test`.`t6`.`b`,1)))) 3086FLUSH STATUS; 3087SELECT * 3088FROM t5 JOIN t6 ON t5.a = t6.a JOIN t7 3089WHERE (t7.a, t7.b) IN ((t5.b, 1), (t6.b, 1)); 3090a b a b a b 30913 1 3 2 1 1 30924 1 4 2 1 1 3093SHOW STATUS LIKE 'Handler_read_%'; 3094Variable_name Value 3095Handler_read_first 0 3096Handler_read_key 4 3097Handler_read_last 0 3098Handler_read_next 2 3099Handler_read_prev 0 3100Handler_read_rnd 0 3101Handler_read_rnd_next 13 3102# 3103# Regression tests of the worklog below. 3104# 3105# Make sure we process IN predicates only. 3106# The code around this is very unclear. 3107EXPLAIN SELECT a, b FROM t1 WHERE (a, b) != (1, 1); 3108id select_type table partitions type possible_keys key key_len ref rows filtered Extra 31091 SIMPLE t1 NULL index x x 10 NULL 4098 99.00 Using where; Using index 3110Warnings: 3111Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` <> 1) or (`test`.`t1`.`b` <> 1)) 3112# This should give us a ref scan, as it always did. 3113EXPLAIN SELECT a, b FROM t1 WHERE (a, b) IN ((0, 0)); 3114id select_type table partitions type possible_keys key key_len ref rows filtered Extra 31151 SIMPLE t1 NULL ref x x 10 const,const 1 100.00 Using index 3116Warnings: 3117Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`b` = 0) and (`test`.`t1`.`a` = 0)) 3118EXPLAIN SELECT a, b FROM t1 WHERE (a, c) IN ((0, 0)); 3119id select_type table partitions type possible_keys key key_len ref rows filtered Extra 31201 SIMPLE t1 NULL ref x x 5 const 1 10.00 Using where 3121Warnings: 3122Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`c` = 0) and (`test`.`t1`.`a` = 0)) 3123# ref scan on x(a). 3124EXPLAIN SELECT * FROM t1 WHERE (a, c) IN ((0, 0), (1, 1)); 3125id select_type table partitions type possible_keys key key_len ref rows filtered Extra 31261 SIMPLE t1 NULL range x x 5 NULL 2 20.00 Using where; Using MRR 3127Warnings: 3128Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`a`,`test`.`t1`.`c`) in (<cache>((0,0)),<cache>((1,1)))) 3129DROP TABLE t1, t2, t3, t4, t5, t6, t7; 3130# 3131# Bug#17635476: CRASH IN GET_MM_PARTS() OR ASSERT IN 3132# GET_FUNC_MM_TREE_FROM_IN_PREDICATE() 3133# 3134CREATE TABLE t1 ( 3135a INT, 3136b INT, 3137KEY (a) 3138) ENGINE = INNODB; 3139SELECT DISTINCT a FROM t1 WHERE (a, b) IN ((0, 0), (1, 1)); 3140a 3141INSERT INTO t1 VALUES (0, 0), (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), 3142(7, 7), (8, 8), (9, 9); 3143ANALYZE TABLE t1; 3144Table Op Msg_type Msg_text 3145test.t1 analyze status OK 3146CREATE TABLE t2 ( 3147a INT, 3148b INT, 3149KEY (a, b) 3150); 3151INSERT INTO t2 SELECT * FROM t1; 3152ANALYZE TABLE t2; 3153Table Op Msg_type Msg_text 3154test.t2 analyze status Table is already up to date 3155EXPLAIN SELECT DISTINCT a FROM t1 WHERE (a, b) IN ((0, 0), (1, 1)); 3156id select_type table partitions type possible_keys key key_len ref rows filtered Extra 31571 SIMPLE t1 NULL range a a 5 NULL 2 20.00 Using where 3158Warnings: 3159Note 1003 /* select#1 */ select distinct `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a`,`test`.`t1`.`b`) in (<cache>((0,0)),<cache>((1,1)))) 3160FLUSH STATUS; 3161SELECT DISTINCT a FROM t1 WHERE (a, b) IN ((0, 0), (1, 1)); 3162a 31630 31641 3165SHOW STATUS LIKE 'Handler_read_%'; 3166Variable_name Value 3167Handler_read_first 0 3168Handler_read_key 2 3169Handler_read_last 0 3170Handler_read_next 2 3171Handler_read_prev 0 3172Handler_read_rnd 0 3173Handler_read_rnd_next 0 3174EXPLAIN SELECT DISTINCT a FROM t2 WHERE (a, b) IN ((0, 0), (1, 1)); 3175id select_type table partitions type possible_keys key key_len ref rows filtered Extra 31761 SIMPLE t2 NULL range a a 10 NULL 2 100.00 Using where; Using index 3177Warnings: 3178Note 1003 /* select#1 */ select distinct `test`.`t2`.`a` AS `a` from `test`.`t2` where ((`test`.`t2`.`a`,`test`.`t2`.`b`) in (<cache>((0,0)),<cache>((1,1)))) 3179FLUSH STATUS; 3180SELECT DISTINCT a FROM t2 WHERE (a, b) IN ((0, 0), (1, 1)); 3181a 31820 31831 3184SHOW STATUS LIKE 'Handler_read_%'; 3185Variable_name Value 3186Handler_read_first 0 3187Handler_read_key 2 3188Handler_read_last 0 3189Handler_read_next 2 3190Handler_read_prev 0 3191Handler_read_rnd 0 3192Handler_read_rnd_next 0 3193# Should not use range scan. 3194EXPLAIN SELECT DISTINCT a FROM t1 WHERE (NULL, b) IN ((0, 0), (1, 1)); 3195id select_type table partitions type possible_keys key key_len ref rows filtered Extra 31961 SIMPLE t1 NULL index a a 5 NULL 10 20.00 Using where 3197Warnings: 3198Note 1003 /* select#1 */ select distinct `test`.`t1`.`a` AS `a` from `test`.`t1` where ((NULL,`test`.`t1`.`b`) in (<cache>((0,0)),<cache>((1,1)))) 3199# Should not use range scan. 3200EXPLAIN SELECT DISTINCT a FROM t2 WHERE (NULL, b) IN ((0, 0), (1, 1)); 3201id select_type table partitions type possible_keys key key_len ref rows filtered Extra 32021 SIMPLE t2 NULL index a a 10 NULL 10 20.00 Using where; Using index 3203Warnings: 3204Note 1003 /* select#1 */ select distinct `test`.`t2`.`a` AS `a` from `test`.`t2` where ((NULL,`test`.`t2`.`b`) in (<cache>((0,0)),<cache>((1,1)))) 3205DROP TABLE t1, t2; 3206# 3207# Bug#17755540 VALGRIND ERROR WHEN SETTING UP ROW COMPARATORS 3208# 3209CREATE TABLE t2 (a INT, b INT, c INT, d INT, KEY x(a, b)); 3210INSERT INTO t2 VALUES (2, 2, 2, 2), (3, 3, 3, 3), (4, 4, 4, 4), (5, 5, 5, 5), 3211(6, 6, 6, 6), (7, 7, 7, 7), (8, 8, 8, 8), (9, 9, 9, 9); 3212INSERT INTO t2 SELECT * FROM t2; 3213INSERT INTO t2 VALUES (0, 0, 0, 0), (1, 1, 1, 1); 3214ANALYZE TABLE t2; 3215Table Op Msg_type Msg_text 3216test.t2 analyze status OK 3217SELECT a, b FROM t2 WHERE (a, b) IN ((0, 0), (1, 1)); 3218a b 32190 0 32201 1 3221DROP TABLE t2; 3222# 3223# BUG#18364815: OPTIMIZER PREFERS TABLE SCAN WHEN 3224# USING "IN" WITH VALUE OF DIFFERENT TYPE 3225# 3226CREATE TABLE t1 (a INT PRIMARY KEY, b INT); 3227INSERT INTO t1 VALUES (1,1), (2,1), (3,1), (4,1), (5,1), (6,1); 3228EXPLAIN SELECT * FROM t1 WHERE a IN (1, 2); 3229id select_type table partitions type possible_keys key key_len ref rows filtered Extra 32301 SIMPLE t1 NULL range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using MRR 3231Warnings: 3232Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` in (1,2)) 3233EXPLAIN SELECT * FROM t1 WHERE a IN (1, "2"); 3234id select_type table partitions type possible_keys key key_len ref rows filtered Extra 32351 SIMPLE t1 NULL range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using MRR 3236Warnings: 3237Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` in (1,'2')) 3238SELECT * FROM t1 WHERE a IN (1, 2); 3239a b 32401 1 32412 1 3242SELECT * FROM t1 WHERE a IN (1, "2"); 3243a b 32441 1 32452 1 3246DROP TABLE t1; 3247# 3248# Bug#18715670 3249# CRASH IN DECIMAL_ACTUAL_FRACTION COMPARING DECIMAL TO NULLS 3250# 3251CREATE TABLE t1(n DECIMAL(39,19) NOT NULL, KEY(n)) engine=innodb; 3252INSERT INTO t1 SET n=0; 3253SELECT 1 FROM t1 WHERE n NOT IN(NULL, NULL); 32541 3255DROP TABLE t1; 3256# 3257# Bug#18759597 MISSING ROWS ON WHERE .. 3258# IN QUERY WITH VARIABLES AND CONCAT 3259# 3260CREATE TABLE t1 ( 3261col_varchar_key varchar(2), 3262KEY col_varchar_key (col_varchar_key) 3263) ENGINE=InnoDB; 3264INSERT INTO t1 VALUES ('n'),('xm'); 3265SET @var1 = 't', @var2 = 'him', 3266@var3 = 'n', @var4 = 'n', 3267@var5 = 'n', @var6 = 'g', 3268@var7 = 'b', @var8 = 'o', 3269@var9 = 'm', @var10 = 'xm', @var11 = 'u' 3270; 3271SELECT col_varchar_key AS field1 3272FROM t1 3273WHERE ( col_varchar_key, col_varchar_key ) IN ( 3274('m', @var1 ), 3275('n', @var3 ), 3276('a', @var5 ), 3277('l', @var7 ), 3278(CONCAT('x', @var9 ), @var10 ) 3279); 3280field1 3281n 3282xm 3283DROP TABLE t1; 3284# 3285# Bug#18535226 DEBUG CRASH ON QUICK_RANGE_SELECT::RESET 3286# 3287SET @old_tmp_table_size=@@tmp_table_size; 3288SET tmp_table_size=1024; 3289CREATE TABLE t1 ( 3290pk INT NOT NULL, 3291col_int_key INT, 3292col_date_key date, 3293col_date_nokey date, 3294col_time_key time, 3295col_time_nokey time, 3296col_datetime_key datetime, 3297col_datetime_nokey datetime, 3298col_varchar_key varchar(1), 3299col_varchar_nokey varchar(1), 3300PRIMARY KEY (pk), 3301KEY col_int_key (col_int_key) 3302) ENGINE=MyISAM; 3303INSERT INTO t1 VALUES 3304(5,0,'2001-05-06','2001-05-06','16:21:18','16:21:18','2001-11-08 21:02:12', 3305'2001-11-08 21:02:12','x','x'),(6,7,'2006-03-03','2006-03-03','18:56:33', 3306'18:56:33','2003-04-01 00:00:00','2003-04-01 00:00:00','i','i'), 3307(7,7,'2007-12-28','2007-12-28',NULL,NULL,'1900-01-01 00:00:00', 3308'1900-01-01 00:00:00','e','e'),(8,1,'2004-10-20','2004-10-20','09:29:08', 3309'09:29:08','2007-07-12 00:00:00','2007-07-12 00:00:00','p','p'), 3310(9,7,'2008-04-09','2008-04-09','19:11:10','19:11:10', 3311'2005-04-04 01:21:01','2005-04-04 01:21:01','s','s'), 3312(10,1,'2005-12-25','2005-12-25','11:57:26','11:57:26', 3313'1900-01-01 00:00:00','1900-01-01 00:00:00','j','j'); 3314SELECT alias1.col_int_key 3315FROM 3316( SELECT SQ1_alias1.* FROM t1 AS SQ1_alias1, t1 AS SQ1_alias2 ) AS alias1, 3317(SELECT 7 AS col_int_nokey) AS alias2 3318WHERE 3319alias2.col_int_nokey = alias1.pk 3320AND alias1.col_varchar_nokey < alias1.col_varchar_key 3321ORDER BY alias1.col_varchar_key; 3322col_int_key 3323DROP TABLE t1; 3324SET tmp_table_size=@old_tmp_table_size; 3325SET sql_mode = default; 3326# 3327# Bug#19585938 Crash in get_full_func_mm_tree with null 3328# item_field->table_ref 3329# 3330CREATE TABLE t1(id INTEGER, col1 INTEGER, col2 INTEGER, PRIMARY KEY(id)); 3331INSERT INTO t1 VALUES (1,2,3), (3,2,1); 3332SELECT (SELECT 1 3333FROM t1 3334WHERE SUM(1) < id 3335) AS c 3336FROM t1 3337GROUP BY col1; 3338c 33391 3340SELECT (SELECT 1 3341FROM t1 3342WHERE id > SUM(1) 3343) AS c 3344FROM t1 3345GROUP BY col1; 3346c 33471 3348SELECT (SELECT 1 3349FROM t1 3350WHERE SUM(1) BETWEEN id AND id+1 3351) AS c 3352FROM t1 3353GROUP BY col1; 3354c 33551 3356SELECT (SELECT 1 3357FROM t1 3358WHERE id BETWEEN SUM(1) AND SUM(5) 3359) AS c 3360FROM t1 3361GROUP BY col1; 3362c 33631 3364SELECT (SELECT 1 3365FROM t1 3366WHERE SUM(1) BETWEEN COUNT(*) AND id 3367) AS c 3368FROM t1 3369GROUP BY col1; 3370c 33711 3372DROP TABLE t1; 3373# 3374# Bug#21415791 VALGRIND ERROR (CONDITIONAL JUMP) AT KEY_AND 3375# (RANGE_OPT_PARAM*, SEL_ARG*, SEL_AR 3376# 3377CREATE TABLE t1 ( 3378col_varchar_10 VARCHAR(10), 3379pk INTEGER NOT NULL, 3380col_int_key INTEGER, 3381PRIMARY KEY (pk), 3382KEY col_int_key (col_int_key), 3383KEY col_varchar_10 (col_varchar_10) 3384) ENGINE=InnoDB; 3385INSERT INTO t1 ( 3386pk, col_varchar_10, col_int_key) VALUES 3387(1, 'ttttt', 0), (2, 'zzzzz', 0), (3, 'ggggg', 0), 3388(4, 'hhhhh', 0), (5, 'kkkkk', 0), (6, 'lllll', 0); 3389CREATE TABLE t2 ( 3390pk INTEGER NOT NULL, 3391col_varchar_10 VARCHAR(10), 3392PRIMARY KEY (pk), 3393KEY col_varchar_10 (col_varchar_10) 3394) ENGINE=InnoDB; 3395INSERT INTO t2 ( 3396pk, col_varchar_10) VALUES 3397(1, '00000'), (2, '00000'), (3, '44444'), (4, '00000'), 3398(5, NULL), (6, NULL), (7, NULL); 3399SELECT COUNT(t1.col_int_key) 3400FROM t2 RIGHT OUTER JOIN t1 ON t2.col_varchar_10 <= t1.col_varchar_10 3401WHERE t2.pk <> 4 OR t2.pk != t1.col_int_key AND t2.pk <> 1000; 3402COUNT(t1.col_int_key) 340324 3404DROP TABLE t1, t2; 3405# 3406# Bug #20229614: OR CONDITIONS ON MULTI-COLUMN INDEX MAY NOT USE ALL 3407# INDEX COLUMNS TO FILTER ROWS 3408# 3409CREATE TABLE t1 ( 3410c1 INT, 3411c2 INT, 3412c3 INT, 3413PRIMARY KEY(c1, c2, c3) 3414) ENGINE=INNODB; 3415INSERT INTO t1 VALUES (1, 1, 1), (1, 1, 2), (1, 1, 3), 3416(1, 1, 4), (1, 1, 5); 3417INSERT INTO t1 SELECT c1, 2, c3 FROM t1; 3418INSERT INTO t1 SELECT c1, 3, c3 FROM t1 WHERE c2 = 1; 3419ANALYZE TABLE t1; 3420Table Op Msg_type Msg_text 3421test.t1 analyze status OK 3422SELECT COUNT(*) FROM t1; 3423COUNT(*) 342415 3425EXPLAIN SELECT c1, c2, c3 3426FROM t1 3427WHERE (c1 = 1 AND c2 = 1 AND c3 = 1) OR 3428(c1 = 1 AND c2 = 2 AND c3 = 2) OR 3429(c1 = 1 AND c2 = 2 AND c3 = 3); 3430id select_type table partitions type possible_keys key key_len ref rows filtered Extra 34311 SIMPLE t1 NULL range PRIMARY PRIMARY 12 NULL 3 100.00 Using where; Using index 3432Warnings: 3433Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2`,`test`.`t1`.`c3` AS `c3` from `test`.`t1` where (((`test`.`t1`.`c3` = 1) and (`test`.`t1`.`c2` = 1) and (`test`.`t1`.`c1` = 1)) or ((`test`.`t1`.`c3` = 2) and (`test`.`t1`.`c2` = 2) and (`test`.`t1`.`c1` = 1)) or ((`test`.`t1`.`c3` = 3) and (`test`.`t1`.`c2` = 2) and (`test`.`t1`.`c1` = 1))) 3434DROP TABLE t1; 3435# 3436# Bug#21139683: ASSERTION FAILED: TYPE_ARG == MAYBE_KEY || 3437# TYPE_ARG == IMPOSSIBLE 3438# 3439CREATE TABLE t1 ( 3440a BLOB, 3441PRIMARY KEY(a(1)), 3442KEY(a(1)) 3443) ENGINE=INNODB; 3444SELECT 1 FROM t1 WHERE a <> 'a' OR a <> ""; 34451 3446DROP TABLE t1; 3447# 3448# Bug#19333852: RESULT DIFF IN QUERY HAVING DISTINCT 3449# WITH GROUP BY 3450# 3451CREATE TABLE t1 ( 3452v1 VARCHAR(20) CHARACTER SET utf8 NOT NULL, 3453pk INTEGER NOT NULL, 3454PRIMARY KEY (pk), 3455KEY v1_key (v1(10)) 3456) ENGINE=InnoDB; 3457INSERT INTO t1 VALUES ('ABCDE',19), ('JLVGO',14); 3458EXPLAIN SELECT * FROM t1 WHERE t1.v1 < CHAR(128); 3459id select_type table partitions type possible_keys key key_len ref rows filtered Extra 34601 SIMPLE t1 NULL ALL v1_key NULL NULL NULL 2 50.00 Using where 3461Warnings: 3462Warning 1366 Incorrect string value: '\x80' for column 'v1' at row 1 3463Note 1003 /* select#1 */ select `test`.`t1`.`v1` AS `v1`,`test`.`t1`.`pk` AS `pk` from `test`.`t1` where (`test`.`t1`.`v1` < <cache>(char(128))) 3464EXPLAIN SELECT * FROM t1 WHERE t1.v1 = CHAR(128); 3465id select_type table partitions type possible_keys key key_len ref rows filtered Extra 34661 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table 3467Warnings: 3468Warning 1366 Incorrect string value: '\x80' for column 'v1' at row 1 3469Note 1003 /* select#1 */ select `test`.`t1`.`v1` AS `v1`,`test`.`t1`.`pk` AS `pk` from `test`.`t1` where (`test`.`t1`.`v1` = char(128)) 3470EXPLAIN SELECT * FROM t1 WHERE t1.v1 BETWEEN 'f' AND CHAR(128); 3471id select_type table partitions type possible_keys key key_len ref rows filtered Extra 34721 SIMPLE t1 NULL range v1_key v1_key 32 NULL 1 100.00 Using where 3473Warnings: 3474Warning 1366 Incorrect string value: '\x80' for column 'v1' at row 1 3475Note 1003 /* select#1 */ select `test`.`t1`.`v1` AS `v1`,`test`.`t1`.`pk` AS `pk` from `test`.`t1` where (`test`.`t1`.`v1` between 'f' and <cache>(char(128))) 3476SELECT * FROM t1 WHERE t1.v1 < CHAR(128); 3477v1 pk 3478JLVGO 14 3479ABCDE 19 3480Warnings: 3481Warning 1366 Incorrect string value: '\x80' for column 'v1' at row 1 3482SELECT * FROM t1 WHERE t1.v1 = CHAR(128); 3483v1 pk 3484Warnings: 3485Warning 1366 Incorrect string value: '\x80' for column 'v1' at row 1 3486SELECT * FROM t1 WHERE t1.v1 BETWEEN 'f' AND CHAR(128); 3487v1 pk 3488JLVGO 14 3489Warnings: 3490Warning 1366 Incorrect string value: '\x80' for column 'v1' at row 1 3491DROP TABLE t1; 3492# 3493# Bug #21761867: ASSERTION `TYPE_ARG == MAYBE_KEY || 3494# TYPE_ARG == IMPOSSIBLE' FAILED. 3495# 3496CREATE TABLE t1 ( 3497c10 INT NOT NULL, 3498c12 INT NOT NULL, 3499c18 INT, 3500PRIMARY KEY (c10,c12), 3501UNIQUE KEY key_c12(c12), 3502KEY key_c18(c18)); 3503INSERT INTO t1 VALUES(11,5,0), (12,6,1), (13,7,2), (14,8,3), (15,9,4); 3504CREATE TABLE t2 ( 3505c10 INT NOT NULL, 3506c12 INT NOT NULL, 3507PRIMARY KEY(c10,c12)); 3508CREATE TABLE t3 (c10 INT NOT NULL); 3509SELECT t2.c10 3510FROM t1 3511RIGHT JOIN t3 3512LEFT JOIN t2 3513ON t3.c10 = t2.c10 3514ON t1.c12 > t2.c12 3515WHERE 3516t1.c10 <= 25 3517AND 3518t1.c18 IS NOT NULL 3519OR 3520t1.c10 > 5 3521AND 3522t1.c18 IN (15,16,18); 3523c10 3524DROP TABLE t1, t2, t3; 3525# 3526# Bug #21318711: WRONG RESULTS FOR TRUNCATED COLUMN AND AGGREGATION 3527# 3528CREATE TABLE t1 ( 3529col1 VARCHAR(5), 3530col2 INT NOT NULL, 3531PRIMARY KEY (col1, col2) 3532) ENGINE=InnoDB; 3533INSERT INTO t1 VALUES ('abcde', 10); 3534EXPLAIN SELECT MAX(col2) FROM t1 WHERE col1 = 'abcdeaa'; 3535id select_type table partitions type possible_keys key key_len ref rows filtered Extra 35361 SIMPLE t1 NULL ref PRIMARY PRIMARY 7 const 1 100.00 Using where; Using index 3537Warnings: 3538Note 1003 /* select#1 */ select max(`test`.`t1`.`col2`) AS `MAX(col2)` from `test`.`t1` where (`test`.`t1`.`col1` = 'abcdeaa') 3539EXPLAIN SELECT MAX(col2) FROM t1 WHERE col1 = 'abcde '; 3540id select_type table partitions type possible_keys key key_len ref rows filtered Extra 35411 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 3542Warnings: 3543Note 1003 /* select#1 */ select max(`test`.`t1`.`col2`) AS `MAX(col2)` from `test`.`t1` where multiple equal('abcde ', `test`.`t1`.`col1`) 3544SELECT MAX(col2) FROM t1 WHERE col1 = 'abcdeaa'; 3545MAX(col2) 3546NULL 3547SELECT MAX(col2) FROM t1 WHERE col1 = 'abcde '; 3548MAX(col2) 354910 3550DROP TABLE t1; 3551# 3552# Bug# 22283790: RANGE OPTIMIZER UTILIZES TOO MUCH MEMORY WITH 3553# MANY OR CONDITIONS 3554# 3555CREATE TABLE t1 ( 3556f1 INTEGER, 3557KEY (f1) 3558); 3559INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15); 3560SET @orig_range_optimizer_max_mem_size= @@range_optimizer_max_mem_size; 3561SET range_optimizer_max_mem_size= 5000; 3562EXPLAIN SELECT * FROM t1 WHERE f1=1 OR f1=2 OR f1=3 OR f1=4 OR f1=5 3563OR f1=6 OR f1=7 OR f1=8 OR f1=9 OR f1=10 OR f1=11; 3564id select_type table partitions type possible_keys key key_len ref rows filtered Extra 35651 SIMPLE t1 NULL range f1 f1 5 NULL 11 100.00 Using where; Using index 3566Warnings: 3567Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where ((`test`.`t1`.`f1` = 1) or (`test`.`t1`.`f1` = 2) or (`test`.`t1`.`f1` = 3) or (`test`.`t1`.`f1` = 4) or (`test`.`t1`.`f1` = 5) or (`test`.`t1`.`f1` = 6) or (`test`.`t1`.`f1` = 7) or (`test`.`t1`.`f1` = 8) or (`test`.`t1`.`f1` = 9) or (`test`.`t1`.`f1` = 10) or (`test`.`t1`.`f1` = 11)) 3568SET range_optimizer_max_mem_size= @orig_range_optimizer_max_mem_size; 3569DROP TABLE t1; 3570# 3571# Bug #23259872: OPTIMIZER CHOOSES TO USE NON PRIMARY 3572# INDEX, EVEN THOUGH COST IS HIGHER 3573# 3574CREATE TABLE `giant_table` ( 3575`id` int(11) NOT NULL AUTO_INCREMENT, 3576`one_id` int(11) NOT NULL, 3577`other_id` bigint(20) NOT NULL DEFAULT '0', 3578`some_other_id` int(11) DEFAULT 0 NOT NULL, 3579`something` double NOT NULL DEFAULT '0', 3580`comment` text COLLATE utf8_unicode_ci, 3581`flags` int(11) NOT NULL DEFAULT '0', 3582`time_created` int(11) NOT NULL DEFAULT '0', 3583PRIMARY KEY (`id`), 3584KEY `time_created` (`time_created`), 3585KEY `some_other_id` (`some_other_id`), 3586KEY `one_other_idx` (`one_id`,`other_id`), 3587KEY `other_id` (`other_id`,`time_created`) 3588) ENGINE=InnoDB AUTO_INCREMENT=101651329 3589DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ; 3590CREATE TABLE t1 (c1 INT); 3591INSERT INTO t1 VALUES (66136540), (68983250), (89627210), (77869520), 3592(82543190), (67538270), (77282760), (77908170), 3593(70923370), (68066360); 3594CREATE PROCEDURE p() 3595BEGIN 3596SET @x = 1; 3597REPEAT 3598INSERT INTO giant_table(id,one_id) 3599SELECT c1 + @x, 0 3600FROM t1 3601WHERE c1 IN (66136540, 68985250, 89627210, 77869520 , 82543190, 67538270, 360277282760, 77908170, 70923370, 68066360); 3603SET @x = @x + 1; 3604UNTIL @x > 30 END REPEAT; 3605END $ 3606CALL p(); 3607SELECT count(*) FROM giant_table; 3608count(*) 3609270 3610INSERT INTO giant_table (id,one_id) VALUES (66136539, 0), (68983258,1), 3611(89628210,1), (77869520,2); 3612INSERT INTO giant_table (id,one_id, some_other_id) VALUES(84673401, 0, 1), 3613(61069031, 1, 1); 3614EXPLAIN SELECT id, something, comment, time_created, one_id, other_id, 3615some_other_id, flags 3616FROM giant_table 3617WHERE id IN (66136539, 68983258, 89628210, 77869520, 82543198, 67538272, 361884673401, 61069031, 68214385, 77282865, 76991297, 64569216, 361989481638, 74534074, 70396537, 80076375, 63308530, 77908270, 362070923271, 68066180) 3621AND (giant_table.flags & 0x01) = 0 AND giant_table.some_other_id = 0; 3622id select_type table partitions type possible_keys key key_len ref rows filtered Extra 36231 SIMPLE giant_table NULL range PRIMARY,some_other_id some_other_id 8 NULL 20 100.00 Using index condition; Using where; Using MRR 3624Warnings: 3625Note 1003 /* select#1 */ select `test`.`giant_table`.`id` AS `id`,`test`.`giant_table`.`something` AS `something`,`test`.`giant_table`.`comment` AS `comment`,`test`.`giant_table`.`time_created` AS `time_created`,`test`.`giant_table`.`one_id` AS `one_id`,`test`.`giant_table`.`other_id` AS `other_id`,`test`.`giant_table`.`some_other_id` AS `some_other_id`,`test`.`giant_table`.`flags` AS `flags` from `test`.`giant_table` where ((`test`.`giant_table`.`some_other_id` = 0) and (`test`.`giant_table`.`id` in (66136539,68983258,89628210,77869520,82543198,67538272,84673401,61069031,68214385,77282865,76991297,64569216,89481638,74534074,70396537,80076375,63308530,77908270,70923271,68066180)) and ((`test`.`giant_table`.`flags` & 0x01) = 0)) 3626DROP PROCEDURE p; 3627DROP TABLE giant_table, t1; 3628# 3629# Bug #26727773: OPTIMIZER CHOSES COMPOSITE INDEX FOR REF OVER RANGE 3630# 3631CREATE TABLE transactions ( 3632app_trans_id INT DEFAULT NULL, 3633id INT NOT NULL, 3634tbl INT NOT NULL DEFAULT 1, 3635created TIMESTAMP NOT NULL DEFAULT '2017-01-01 01:01:01', 3636trans_type INT NOT NULL, 3637description BLOB, 3638source_lvl1 INT DEFAULT NULL, 3639source_lvl2 INT DEFAULT NULL, 3640KEY tbl_id_idx (tbl,id), 3641KEY created_idx (created), 3642KEY trans_type_created_idx (trans_type,created), 3643KEY app_trans_id_idx (app_trans_id) 3644) ENGINE=INNODB ; 3645CREATE TABLE t1 (c1 INT); 3646INSERT INTO t1 VALUES (1), (1000), (2000), (3000), (4000), (5000), (6000), 3647(7000), (8000), (9000); 3648CREATE PROCEDURE p() 3649BEGIN 3650SET @x = 1; 3651REPEAT 3652INSERT IGNORE INTO transactions(id,trans_type, description) 3653SELECT c1 + @x, @x , 'abcd' 3654 FROM t1; 3655SET @x = @x + 1; 3656UNTIL @x > 300 END REPEAT; 3657END $ 3658CALL p(); 3659SELECT count(*) FROM transactions; 3660count(*) 36613000 3662INSERT IGNORE INTO transactions(id,trans_type, description, created) 3663SELECT 3, 3 , 'abcd', '2018-01-01 01:01:01' 3664 FROM dual; 3665EXPLAIN SELECT '2017-10-23 01:01:01', HOUR(created), source_lvl1, source_lvl2, 3666COUNT(DISTINCT(app_trans_id)) 3667FROM transactions 3668WHERE created > '2017-10-23 01:01:01' AND tbl = 1 AND trans_type in (3) 3669GROUP BY '2017-10-23 01:01:01', HOUR(created), source_lvl1, source_lvl2; 3670id select_type table partitions type possible_keys key key_len ref rows filtered Extra 36711 SIMPLE transactions NULL range tbl_id_idx,created_idx,trans_type_created_idx created_idx 4 NULL # # Using index condition; Using where; Using MRR; Using filesort 3672Warnings: 3673Note 1003 /* select#1 */ select '2017-10-23 01:01:01' AS `2017-10-23 01:01:01`,hour(`test`.`transactions`.`created`) AS `HOUR(created)`,`test`.`transactions`.`source_lvl1` AS `source_lvl1`,`test`.`transactions`.`source_lvl2` AS `source_lvl2`,count(distinct `test`.`transactions`.`app_trans_id`) AS `COUNT(DISTINCT(app_trans_id))` from `test`.`transactions` where ((`test`.`transactions`.`trans_type` = 3) and (`test`.`transactions`.`tbl` = 1) and (`test`.`transactions`.`created` > '2017-10-23 01:01:01')) group by '2017-10-23 01:01:01',hour(`test`.`transactions`.`created`),`test`.`transactions`.`source_lvl1`,`test`.`transactions`.`source_lvl2` 3674SET optimizer_trace="enabled=on"; 3675SELECT '2017-10-23 01:01:01', HOUR(created), source_lvl1, source_lvl2, 3676COUNT(DISTINCT(app_trans_id)) 3677FROM transactions 3678WHERE created > '2017-10-23 01:01:01' AND tbl = 1 AND trans_type in (3) 3679GROUP BY '2017-10-23 01:01:01', HOUR(created), source_lvl1, source_lvl2; 36802017-10-23 01:01:01 HOUR(created) source_lvl1 source_lvl2 COUNT(DISTINCT(app_trans_id)) 36812017-10-23 01:01:01 1 NULL NULL 0 3682SELECT TRACE INTO @trace FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; 3683SELECT @trace RLIKE "uses_more_keyparts"; 3684@trace RLIKE "uses_more_keyparts" 36851 3686SET optimizer_trace="enabled=off"; 3687DROP PROCEDURE p; 3688DROP TABLE t1, transactions; 3689# 3690# Bug #28086754: OPTIMIZER SKIP THE RANG SCAN ON SECOND COLUMN IN A 3691# COMPOSITE INDEX 3692# 3693CREATE TABLE test_ref ( 3694a INT PRIMARY KEY, 3695b VARCHAR(20), 3696c VARCHAR(20) DEFAULT NULL, 3697d VARCHAR(3) DEFAULT NULL, 3698id INT DEFAULT NULL, 3699KEY idx1 (id, c), 3700KEY idx2 (id, d)) ENGINE=INNODB ; 3701ANALYZE TABLE test_ref; 3702Table Op Msg_type Msg_text 3703test.test_ref analyze status OK 3704EXPLAIN SELECT * 3705FROM test_ref 3706WHERE id=3 AND c LIKE 'gh%' 3707ORDER BY c 3708LIMIT 1; 3709id select_type table partitions type possible_keys key key_len ref rows filtered Extra 37101 SIMPLE test_ref NULL ref idx1,idx2 idx1 5 const 48 11.11 Using index condition 3711Warnings: 3712Note 1003 /* select#1 */ select `test`.`test_ref`.`a` AS `a`,`test`.`test_ref`.`b` AS `b`,`test`.`test_ref`.`c` AS `c`,`test`.`test_ref`.`d` AS `d`,`test`.`test_ref`.`id` AS `id` from `test`.`test_ref` where ((`test`.`test_ref`.`id` = 3) and (`test`.`test_ref`.`c` like 'gh%')) order by `test`.`test_ref`.`c` limit 1 3713SELECT * 3714FROM test_ref 3715WHERE id=3 AND c LIKE 'gh%' 3716ORDER BY c 3717LIMIT 1; 3718a b c d id 371934876 D003 gheennse S 3 3720EXPLAIN SELECT * 3721FROM test_ref 3722WHERE id=3 AND c LIKE 'gh%' 3723ORDER BY c DESC 3724LIMIT 1; 3725id select_type table partitions type possible_keys key key_len ref rows filtered Extra 37261 SIMPLE test_ref NULL ref idx1,idx2 idx1 5 const 48 11.11 Using where 3727Warnings: 3728Note 1003 /* select#1 */ select `test`.`test_ref`.`a` AS `a`,`test`.`test_ref`.`b` AS `b`,`test`.`test_ref`.`c` AS `c`,`test`.`test_ref`.`d` AS `d`,`test`.`test_ref`.`id` AS `id` from `test`.`test_ref` where ((`test`.`test_ref`.`id` = 3) and (`test`.`test_ref`.`c` like 'gh%')) order by `test`.`test_ref`.`c` desc limit 1 3729SELECT * 3730FROM test_ref 3731WHERE id=3 AND c LIKE 'gh%' 3732ORDER BY c DESC 3733LIMIT 1; 3734a b c d id 37351770649 D003 gheennse S 3 3736DROP TABLE test_ref; 3737set optimizer_switch=default; 3738