1set optimizer_switch='mrr=on,mrr_cost_based=on'; 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 where; 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 where; 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 where; 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 where; 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 where; 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 where; 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 where 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 where 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 where 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 where 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 where 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 where 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 where 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 ALL OXLEFT,OXROOTID NULL NULL NULL 6 16.67 Using where; Using join buffer (Block Nested Loop) 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 where 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 where 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 where 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 where 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 where 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 where 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 where 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 where 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 where 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 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 where 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 where 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); 1359select 'In following EXPLAIN the access method should be ref or range, #rows~=500 (and not 2)' Z; 1360Z 1361In following EXPLAIN the access method should be ref or range, #rows~=500 (and not 2) 1362explain select * from t2 where a=1000 and b<11; 1363id select_type table partitions type possible_keys key key_len ref rows filtered Extra 13641 SIMPLE t2 NULL range a a 10 NULL 502 100.00 Using where 1365Warnings: 1366Note 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)) 1367drop table t1, t2; 1368CREATE TABLE t1( a INT, b INT, KEY( a, b ) ); 1369CREATE TABLE t2( a INT, b INT, KEY( a, b ) ); 1370CREATE TABLE t3( a INT, b INT, KEY( a, b ) ); 1371INSERT INTO t1( a, b ) 1372VALUES (0, 1), (1, 2), (1, 4), (2, 3), (5, 0), (9, 7); 1373INSERT INTO t2( a, b ) 1374VALUES ( 1, 1), ( 2, 1), ( 3, 1), ( 4, 1), ( 5, 1), 1375( 6, 1), ( 7, 1), ( 8, 1), ( 9, 1), (10, 1), 1376(11, 1), (12, 1), (13, 1), (14, 1), (15, 1), 1377(16, 1), (17, 1), (18, 1), (19, 1), (20, 1); 1378INSERT INTO t2 SELECT a, 2 FROM t2 WHERE b = 1; 1379INSERT INTO t2 SELECT a, 3 FROM t2 WHERE b = 1; 1380INSERT INTO t2 SELECT -1, -1 FROM t2; 1381INSERT INTO t2 SELECT -1, -1 FROM t2; 1382INSERT INTO t2 SELECT -1, -1 FROM t2; 1383INSERT INTO t3 1384VALUES (1, 0), (2, 0), (3, 0), (4, 0), (5, 0), 1385(6, 0), (7, 0), (8, 0), (9, 0), (10, 0); 1386INSERT INTO t3 SELECT * FROM t3 WHERE a = 10; 1387INSERT INTO t3 SELECT * FROM t3 WHERE a = 10; 1388SELECT * FROM t1 WHERE 13893 <= a AND a < 5 OR 13905 < a AND b = 3 OR 13913 <= a; 1392a b 13935 0 13949 7 1395EXPLAIN 1396SELECT * FROM t1 WHERE 13973 <= a AND a < 5 OR 13985 < a AND b = 3 OR 13993 <= a; 1400id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14011 SIMPLE t1 NULL range a a 5 NULL 3 100.00 Using where; Using index 1402Warnings: 1403Note 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`)) 1404SELECT * FROM t1 WHERE 14053 <= a AND a < 5 OR 14065 <= a AND b = 3 OR 14073 <= a; 1408a b 14095 0 14109 7 1411EXPLAIN 1412SELECT * FROM t1 WHERE 14133 <= a AND a < 5 OR 14145 <= a AND b = 3 OR 14153 <= a; 1416id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14171 SIMPLE t1 NULL range a a 5 NULL 4 100.00 Using where; Using index 1418Warnings: 1419Note 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`)) 1420SELECT * FROM t1 WHERE 14213 <= a AND a <= 5 OR 14225 <= a AND b = 3 OR 14233 <= a; 1424a b 14255 0 14269 7 1427EXPLAIN 1428SELECT * FROM t1 WHERE 14293 <= a AND a <= 5 OR 14305 <= a AND b = 3 OR 14313 <= a; 1432id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14331 SIMPLE t1 NULL range a a 5 NULL 3 100.00 Using where; Using index 1434Warnings: 1435Note 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`)) 1436SELECT * FROM t1 WHERE 14373 <= a AND a <= 5 OR 14383 <= a; 1439a b 14405 0 14419 7 1442EXPLAIN 1443SELECT * FROM t1 WHERE 14443 <= a AND a <= 5 OR 14453 <= a; 1446id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14471 SIMPLE t1 NULL range a a 5 NULL 3 100.00 Using where; Using index 1448Warnings: 1449Note 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`)) 1450SELECT * FROM t2 WHERE 14515 <= a AND a < 10 AND b = 1 OR 145215 <= a AND a < 20 AND b = 3 1453OR 14541 <= a AND b = 1; 1455a b 14561 1 14572 1 14583 1 14594 1 14605 1 14616 1 14627 1 14638 1 14649 1 146510 1 146611 1 146712 1 146813 1 146914 1 147015 1 147115 3 147216 1 147316 3 147417 1 147517 3 147618 1 147718 3 147819 1 147919 3 148020 1 1481EXPLAIN 1482SELECT * FROM t2 WHERE 14835 <= a AND a < 10 AND b = 1 OR 148415 <= a AND a < 20 AND b = 3 1485OR 14861 <= a AND b = 1; 1487id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14881 SIMPLE t2 NULL range a a 10 NULL 50 27.10 Using where; Using index 1489Warnings: 1490Note 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`))) 1491SELECT * FROM t2 WHERE 14925 <= a AND a < 10 AND b = 2 OR 149315 <= a AND a < 20 AND b = 3 1494OR 14951 <= a AND b = 1; 1496a b 14971 1 14982 1 14993 1 15004 1 15015 1 15025 2 15036 1 15046 2 15057 1 15067 2 15078 1 15088 2 15099 1 15109 2 151110 1 151211 1 151312 1 151413 1 151514 1 151615 1 151715 3 151816 1 151916 3 152017 1 152117 3 152218 1 152318 3 152419 1 152519 3 152620 1 1527EXPLAIN 1528SELECT * FROM t2 WHERE 15295 <= a AND a < 10 AND b = 2 OR 153015 <= a AND a < 20 AND b = 3 1531OR 15321 <= a AND b = 1; 1533id select_type table partitions type possible_keys key key_len ref rows filtered Extra 15341 SIMPLE t2 NULL range a a 10 NULL 50 27.10 Using where; Using index 1535Warnings: 1536Note 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`))) 1537SELECT * FROM t3 WHERE 15385 <= a AND a < 10 AND b = 3 OR 1539a < 5 OR 1540a < 10; 1541a b 15421 0 15432 0 15443 0 15454 0 15465 0 15476 0 15487 0 15498 0 15509 0 1551EXPLAIN 1552SELECT * FROM t3 WHERE 15535 <= a AND a < 10 AND b = 3 OR 1554a < 5 OR 1555a < 10; 1556id select_type table partitions type possible_keys key key_len ref rows filtered Extra 15571 SIMPLE t3 NULL range a a 5 NULL 8 100.00 Using where; Using index 1558Warnings: 1559Note 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)) 1560DROP TABLE t1, t2, t3; 1561# 1562# Bug #47123: Endless 100% CPU loop with STRAIGHT_JOIN 1563# 1564CREATE TABLE t1(a INT, KEY(a)); 1565INSERT INTO t1 VALUES (1), (NULL); 1566SELECT * FROM t1 WHERE a <> NULL and (a <> NULL or a <= NULL); 1567a 1568DROP TABLE t1; 1569# 1570# Bug#47925: regression of range optimizer and date comparison in 5.1.39! 1571# 1572CREATE TABLE t1 ( a DATE, KEY ( a ) ); 1573CREATE TABLE t2 ( a DATETIME, KEY ( a ) ); 1574# Make optimizer choose range scan 1575INSERT INTO t1 VALUES ('2009-09-22'), ('2009-09-22'), ('2009-09-22'); 1576INSERT INTO t1 VALUES ('2009-09-23'), ('2009-09-23'), ('2009-09-23'); 1577INSERT INTO t2 VALUES ('2009-09-22 12:00:00'), ('2009-09-22 12:00:00'), 1578('2009-09-22 12:00:00'); 1579INSERT INTO t2 VALUES ('2009-09-23 12:00:00'), ('2009-09-23 12:00:00'), 1580('2009-09-23 12:00:00'); 1581# DATE vs DATE 1582EXPLAIN 1583SELECT * FROM t1 WHERE a >= '2009/09/23'; 1584id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1585X X X NULL range a a X X X 100.00 X 1586Warnings: 1587X X X 1588SELECT * FROM t1 WHERE a >= '2009/09/23'; 1589a 15902009-09-23 15912009-09-23 15922009-09-23 1593SELECT * FROM t1 WHERE a >= '20090923'; 1594a 15952009-09-23 15962009-09-23 15972009-09-23 1598SELECT * FROM t1 WHERE a >= 20090923; 1599a 16002009-09-23 16012009-09-23 16022009-09-23 1603SELECT * FROM t1 WHERE a >= '2009-9-23'; 1604a 16052009-09-23 16062009-09-23 16072009-09-23 1608SELECT * FROM t1 WHERE a >= '2009.09.23'; 1609a 16102009-09-23 16112009-09-23 16122009-09-23 1613SELECT * FROM t1 WHERE a >= '2009:09:23'; 1614a 16152009-09-23 16162009-09-23 16172009-09-23 1618# DATE vs DATETIME 1619EXPLAIN 1620SELECT * FROM t2 WHERE a >= '2009/09/23'; 1621id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1622X X X NULL range a a X X X 100.00 X 1623Warnings: 1624X X X 1625SELECT * FROM t2 WHERE a >= '2009/09/23'; 1626a 16272009-09-23 12:00:00 16282009-09-23 12:00:00 16292009-09-23 12:00:00 1630SELECT * FROM t2 WHERE a >= '2009/09/23'; 1631a 16322009-09-23 12:00:00 16332009-09-23 12:00:00 16342009-09-23 12:00:00 1635SELECT * FROM t2 WHERE a >= '20090923'; 1636a 16372009-09-23 12:00:00 16382009-09-23 12:00:00 16392009-09-23 12:00:00 1640SELECT * FROM t2 WHERE a >= 20090923; 1641a 16422009-09-23 12:00:00 16432009-09-23 12:00:00 16442009-09-23 12:00:00 1645SELECT * FROM t2 WHERE a >= '2009-9-23'; 1646a 16472009-09-23 12:00:00 16482009-09-23 12:00:00 16492009-09-23 12:00:00 1650SELECT * FROM t2 WHERE a >= '2009.09.23'; 1651a 16522009-09-23 12:00:00 16532009-09-23 12:00:00 16542009-09-23 12:00:00 1655SELECT * FROM t2 WHERE a >= '2009:09:23'; 1656a 16572009-09-23 12:00:00 16582009-09-23 12:00:00 16592009-09-23 12:00:00 1660# DATETIME vs DATETIME 1661EXPLAIN 1662SELECT * FROM t2 WHERE a >= '2009/09/23 12:00:00'; 1663id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1664X X X NULL range a a X X X 100.00 X 1665Warnings: 1666X X X 1667SELECT * FROM t2 WHERE a >= '2009/09/23 12:00:00'; 1668a 16692009-09-23 12:00:00 16702009-09-23 12:00:00 16712009-09-23 12:00:00 1672SELECT * FROM t2 WHERE a >= '20090923120000'; 1673a 16742009-09-23 12:00:00 16752009-09-23 12:00:00 16762009-09-23 12:00:00 1677SELECT * FROM t2 WHERE a >= 20090923120000; 1678a 16792009-09-23 12:00:00 16802009-09-23 12:00:00 16812009-09-23 12:00:00 1682SELECT * FROM t2 WHERE a >= '2009-9-23 12:00:00'; 1683a 16842009-09-23 12:00:00 16852009-09-23 12:00:00 16862009-09-23 12:00:00 1687SELECT * FROM t2 WHERE a >= '2009.09.23 12:00:00'; 1688a 16892009-09-23 12:00:00 16902009-09-23 12:00:00 16912009-09-23 12:00:00 1692SELECT * FROM t2 WHERE a >= '2009:09:23 12:00:00'; 1693a 16942009-09-23 12:00:00 16952009-09-23 12:00:00 16962009-09-23 12:00:00 1697# DATETIME vs DATE 1698EXPLAIN 1699SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00'; 1700id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1701X X X NULL range a a X X X 100.00 X 1702Warnings: 1703X X X 1704SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00'; 1705a 17062009-09-23 17072009-09-23 17082009-09-23 1709SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00'; 1710a 17112009-09-23 17122009-09-23 17132009-09-23 1714SELECT * FROM t1 WHERE a >= '20090923000000'; 1715a 17162009-09-23 17172009-09-23 17182009-09-23 1719SELECT * FROM t1 WHERE a >= 20090923000000; 1720a 17212009-09-23 17222009-09-23 17232009-09-23 1724SELECT * FROM t1 WHERE a >= '2009-9-23 00:00:00'; 1725a 17262009-09-23 17272009-09-23 17282009-09-23 1729SELECT * FROM t1 WHERE a >= '2009.09.23 00:00:00'; 1730a 17312009-09-23 17322009-09-23 17332009-09-23 1734SELECT * FROM t1 WHERE a >= '2009:09:23 00:00:00'; 1735a 17362009-09-23 17372009-09-23 17382009-09-23 1739# Test of the new get_date_from_str implementation 1740# Behavior differs slightly between the trunk and mysql-pe. 1741# The former may give errors for the truncated values, while the latter 1742# gives warnings. The purpose of this test is not to interfere, and only 1743# preserve existing behavior. 1744SELECT str_to_date('2007-10-00', '%Y-%m-%d') >= '' AND 1745str_to_date('2007-10-00', '%Y-%m-%d') <= '2007/10/20'; 1746str_to_date('2007-10-00', '%Y-%m-%d') >= '' AND 1747str_to_date('2007-10-00', '%Y-%m-%d') <= '2007/10/20' 17481 1749Warnings: 1750Warning 1292 Truncated incorrect date value: '' 1751SELECT str_to_date('2007-20-00', '%Y-%m-%d') >= '2007/10/20' AND 1752str_to_date('2007-20-00', '%Y-%m-%d') <= ''; 1753str_to_date('2007-20-00', '%Y-%m-%d') >= '2007/10/20' AND 1754str_to_date('2007-20-00', '%Y-%m-%d') <= '' 1755NULL 1756Warnings: 1757Warning 1292 Truncated incorrect date value: '' 1758Warning 1411 Incorrect datetime value: '2007-20-00' for function str_to_date 1759Warning 1411 Incorrect datetime value: '2007-20-00' for function str_to_date 1760SELECT str_to_date('2007-10-00', '%Y-%m-%d') BETWEEN '' AND '2007/10/20'; 1761str_to_date('2007-10-00', '%Y-%m-%d') BETWEEN '' AND '2007/10/20' 17621 1763Warnings: 1764Warning 1292 Truncated incorrect datetime value: '' 1765SELECT str_to_date('2007-20-00', '%Y-%m-%d') BETWEEN '2007/10/20' AND ''; 1766str_to_date('2007-20-00', '%Y-%m-%d') BETWEEN '2007/10/20' AND '' 1767NULL 1768Warnings: 1769Warning 1411 Incorrect datetime value: '2007-20-00' for function str_to_date 1770SELECT str_to_date('', '%Y-%m-%d'); 1771str_to_date('', '%Y-%m-%d') 17720000-00-00 1773DROP TABLE t1, t2; 1774# 1775# Bug#48459: valgrind errors with query using 'Range checked for each 1776# record' 1777# 1778CREATE TABLE t1 ( 1779a INT, 1780b CHAR(2), 1781c INT, 1782d INT, 1783KEY ( c ), 1784KEY ( d, a, b ( 2 ) ), 1785KEY ( b ( 1 ) ) 1786); 1787INSERT INTO t1 VALUES ( NULL, 'a', 1, 2 ), ( NULL, 'a', 1, 2 ), 1788( 1, 'a', 1, 2 ), ( 1, 'a', 1, 2 ); 1789CREATE TABLE t2 ( 1790a INT, 1791c INT, 1792e INT, 1793KEY ( e ) 1794); 1795INSERT INTO t2 VALUES ( 1, 1, NULL ), ( 1, 1, NULL ); 1796# Should not give Valgrind warnings 1797SELECT 1 1798FROM t1, t2 1799WHERE t1.d <> '1' AND t1.b > '1' 1800AND t1.a = t2.a AND t1.c = t2.c; 18011 18021 18031 18041 18051 1806DROP TABLE t1, t2; 1807# 1808# Bug #48665: sql-bench's insert test fails due to wrong result 1809# 1810CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a)); 1811INSERT INTO t1 VALUES (0,0), (1,1); 1812EXPLAIN 1813SELECT * FROM t1 FORCE INDEX (PRIMARY) 1814WHERE (a>=1 AND a<=2) OR (a>=4 AND a<=5) OR (a>=0 AND a <=10); 1815id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1816@ @ @ NULL range @ @ @ @ @ 100.00 @ 1817Warnings: 1818@ @ @ 1819# Should return 2 rows 1820SELECT * FROM t1 FORCE INDEX (PRIMARY) 1821WHERE (a>=1 AND a<=2) OR (a>=4 AND a<=5) OR (a>=0 AND a <=10); 1822a b 18230 0 18241 1 1825DROP TABLE t1; 1826# 1827# Bug #54802: 'NOT BETWEEN' evaluation is incorrect 1828# 1829CREATE TABLE t1 (c_key INT, c_notkey INT, KEY(c_key)); 1830INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3); 1831EXPLAIN SELECT * FROM t1 WHERE 2 NOT BETWEEN c_notkey AND c_key; 1832id select_type table partitions type possible_keys key key_len ref rows filtered Extra 18331 SIMPLE t1 NULL ALL c_key NULL NULL NULL 3 66.67 Using where 1834Warnings: 1835Note 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`) 1836SELECT * FROM t1 WHERE 2 NOT BETWEEN c_notkey AND c_key; 1837c_key c_notkey 18381 1 18393 3 1840DROP TABLE t1; 1841# 1842# Bug #57030: 'BETWEEN' evaluation is incorrect 1843# 1844CREATE TABLE t1(pk INT PRIMARY KEY, i4 INT); 1845CREATE UNIQUE INDEX i4_uq ON t1(i4); 1846INSERT INTO t1 VALUES (1,10), (2,20), (3,30); 1847EXPLAIN 1848SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 10; 1849id select_type table partitions type possible_keys key key_len ref rows filtered Extra 18501 SIMPLE t1 NULL const i4_uq i4_uq 5 const 1 100.00 NULL 1851Warnings: 1852Note 1003 /* select#1 */ select '1' AS `pk`,'10' AS `i4` from `test`.`t1` where ('10' between 10 and 10) 1853SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 10; 1854pk i4 18551 10 1856EXPLAIN 1857SELECT * FROM t1 WHERE 10 BETWEEN i4 AND i4; 1858id select_type table partitions type possible_keys key key_len ref rows filtered Extra 18591 SIMPLE t1 NULL const i4_uq i4_uq 5 const 1 100.00 NULL 1860Warnings: 1861Note 1003 /* select#1 */ select '1' AS `pk`,'10' AS `i4` from `test`.`t1` where (10 between '10' and '10') 1862SELECT * FROM t1 WHERE 10 BETWEEN i4 AND i4; 1863pk i4 18641 10 1865EXPLAIN 1866SELECT * FROM t1 WHERE 10 BETWEEN 10 AND i4; 1867id select_type table partitions type possible_keys key key_len ref rows filtered Extra 18681 SIMPLE t1 NULL range i4_uq i4_uq 5 NULL 3 100.00 Using where 1869Warnings: 1870Note 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`) 1871SELECT * FROM t1 WHERE 10 BETWEEN 10 AND i4; 1872pk i4 18731 10 18742 20 18753 30 1876EXPLAIN 1877SELECT * FROM t1 WHERE 10 BETWEEN i4 AND 10; 1878id select_type table partitions type possible_keys key key_len ref rows filtered Extra 18791 SIMPLE t1 NULL range i4_uq i4_uq 5 NULL 1 100.00 Using where 1880Warnings: 1881Note 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) 1882SELECT * FROM t1 WHERE 10 BETWEEN i4 AND 10; 1883pk i4 18841 10 1885EXPLAIN 1886SELECT * FROM t1 WHERE 10 BETWEEN 10 AND 10; 1887id select_type table partitions type possible_keys key key_len ref rows filtered Extra 18881 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 100.00 NULL 1889Warnings: 1890Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i4` AS `i4` from `test`.`t1` where 1 1891SELECT * FROM t1 WHERE 10 BETWEEN 10 AND 10; 1892pk i4 18931 10 18942 20 18953 30 1896EXPLAIN 1897SELECT * FROM t1 WHERE 10 BETWEEN 11 AND 11; 1898id select_type table partitions type possible_keys key key_len ref rows filtered Extra 18991 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 1900Warnings: 1901Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i4` AS `i4` from `test`.`t1` where 0 1902SELECT * FROM t1 WHERE 10 BETWEEN 11 AND 11; 1903pk i4 1904EXPLAIN 1905SELECT * FROM t1 WHERE 10 BETWEEN 100 AND 0; 1906id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19071 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 1908Warnings: 1909Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i4` AS `i4` from `test`.`t1` where 0 1910SELECT * FROM t1 WHERE 10 BETWEEN 100 AND 0; 1911pk i4 1912EXPLAIN 1913SELECT * FROM t1 WHERE i4 BETWEEN 100 AND 0; 1914id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19151 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table 1916Warnings: 1917Note 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) 1918SELECT * FROM t1 WHERE i4 BETWEEN 100 AND 0; 1919pk i4 1920EXPLAIN 1921SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 99999999999999999; 1922id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19231 SIMPLE t1 NULL range i4_uq i4_uq 5 NULL 3 100.00 Using where 1924Warnings: 1925Note 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) 1926SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 99999999999999999; 1927pk i4 19281 10 19292 20 19303 30 1931EXPLAIN 1932SELECT * FROM t1 WHERE i4 BETWEEN 999999999999999 AND 30; 1933id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19341 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table 1935Warnings: 1936Note 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) 1937SELECT * FROM t1 WHERE i4 BETWEEN 999999999999999 AND 30; 1938pk i4 1939EXPLAIN 1940SELECT * FROM t1 WHERE i4 BETWEEN 10 AND '20'; 1941id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19421 SIMPLE t1 NULL range i4_uq i4_uq 5 NULL 1 100.00 Using where 1943Warnings: 1944Note 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') 1945SELECT * FROM t1 WHERE i4 BETWEEN 10 AND '20'; 1946pk i4 19471 10 19482 20 1949EXPLAIN 1950SELECT * FROM t1, t1 as t2 WHERE t2.pk BETWEEN t1.i4 AND t1.i4; 1951id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19521 SIMPLE t1 NULL ALL i4_uq NULL NULL NULL 3 100.00 NULL 19531 SIMPLE t2 NULL eq_ref PRIMARY PRIMARY 4 test.t1.i4 1 100.00 Using where 1954Warnings: 1955Note 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`) 1956SELECT * FROM t1, t1 as t2 WHERE t2.pk BETWEEN t1.i4 AND t1.i4; 1957pk i4 pk i4 1958EXPLAIN 1959SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk; 1960id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19611 SIMPLE t1 NULL ALL i4_uq NULL NULL NULL 3 100.00 NULL 19621 SIMPLE t2 NULL eq_ref PRIMARY PRIMARY 4 test.t1.i4 1 100.00 Using where 1963Warnings: 1964Note 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`) 1965SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk; 1966pk i4 pk i4 1967DROP TABLE t1; 1968# 1969# BUG#13519696 - 62940: SELECT RESULTS VARY WITH VERSION AND 1970# WITH/WITHOUT INDEX RANGE SCAN 1971# 1972create table t1 (id int unsigned not null auto_increment primary key); 1973insert into t1 values (null); 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; 1981insert into t1 select null from t1; 1982create table t2 ( 1983id int unsigned not null auto_increment, 1984val decimal(5,3) not null, 1985primary key (id,val), 1986unique key (val,id), 1987unique key (id)); 1988insert into t2 select null,id*0.0009 from t1; 1989select count(val) from t2 ignore index (val) where val > 0.1155; 1990count(val) 1991128 1992select count(val) from t2 force index (val) where val > 0.1155; 1993count(val) 1994128 1995drop table t2, t1; 1996# 1997# BUG#13453382 - REGRESSION SINCE 5.1.39, RANGE OPTIMIZER WRONG 1998# RESULTS WITH DECIMAL CONVERSION 1999# 2000create table t1 (a int,b int,c int,primary key (a,c)); 2001insert into t1 values (1,1,2),(1,1,3),(1,1,4); 2002select convert(3, signed integer) > 2.9; 2003convert(3, signed integer) > 2.9 20041 2005select * from t1 force index (primary) where a=1 and c>= 2.9; 2006a b c 20071 1 3 20081 1 4 2009select * from t1 ignore index (primary) where a=1 and c>= 2.9; 2010a b c 20111 1 3 20121 1 4 2013select * from t1 force index (primary) where a=1 and c> 2.9; 2014a b c 20151 1 3 20161 1 4 2017select * from t1 ignore index (primary) where a=1 and c> 2.9; 2018a b c 20191 1 3 20201 1 4 2021drop table t1; 2022# 2023# BUG#13463488 - 63437: CHAR & BETWEEN WITH INDEX RETURNS WRONG 2024# RESULT AFTER MYSQL 5.1. 2025# 2026CREATE TABLE t1( 2027F1 CHAR(5) NOT NULL, 2028F2 CHAR(5) NOT NULL, 2029F3 CHAR(5) NOT NULL, 2030PRIMARY KEY(F1), 2031INDEX IDX_F2(F2) 2032); 2033INSERT INTO t1 VALUES 2034('A','A','A'),('AA','AA','AA'),('AAA','AAA','AAA'), 2035('AAAA','AAAA','AAAA'),('AAAAA','AAAAA','AAAAA'); 2036SELECT * FROM t1 WHERE F1 = 'A '; 2037F1 F2 F3 2038A A A 2039SELECT * FROM t1 IGNORE INDEX(PRIMARY) WHERE F1 = 'A '; 2040F1 F2 F3 2041A A A 2042SELECT * FROM t1 WHERE F1 >= 'A '; 2043F1 F2 F3 2044A A A 2045AA AA AA 2046AAA AAA AAA 2047AAAA AAAA AAAA 2048AAAAA AAAAA AAAAA 2049SELECT * FROM t1 WHERE F1 > 'A '; 2050F1 F2 F3 2051AA AA AA 2052AAA AAA AAA 2053AAAA AAAA AAAA 2054AAAAA AAAAA AAAAA 2055SELECT * FROM t1 WHERE F1 BETWEEN 'A ' AND 'AAAAA'; 2056F1 F2 F3 2057A A A 2058AA AA AA 2059AAA AAA AAA 2060AAAA AAAA AAAA 2061AAAAA AAAAA AAAAA 2062SELECT * FROM t1 WHERE F2 BETWEEN 'A ' AND 'AAAAA'; 2063F1 F2 F3 2064A A A 2065AA AA AA 2066AAA AAA AAA 2067AAAA AAAA AAAA 2068AAAAA AAAAA AAAAA 2069SELECT * FROM t1 WHERE F3 BETWEEN 'A ' AND 'AAAAA'; 2070F1 F2 F3 2071A A A 2072AA AA AA 2073AAA AAA AAA 2074AAAA AAAA AAAA 2075AAAAA AAAAA AAAAA 2076SELECT * FROM t1 IGNORE INDEX(PRIMARY) WHERE F1 BETWEEN 'A ' AND 2077'AAAAA'; 2078F1 F2 F3 2079A A A 2080AA AA AA 2081AAA AAA AAA 2082AAAA AAAA AAAA 2083AAAAA AAAAA AAAAA 2084DROP TABLE t1; 2085End of 5.1 tests 2086CREATE TABLE t1 (c1 DECIMAL(10,0),INDEX(c1)); 2087INSERT INTO t1 VALUES (1),(2),(3); 2088SELECT c1 FROM t1 WHERE c1 >= 'A' GROUP BY 1; 2089c1 20901 20912 20923 2093Warnings: 2094Warning 1366 Incorrect decimal value: 'A' for column 'c1' at row 1 2095Warning 1292 Truncated incorrect DECIMAL value: 'A' 2096DROP TABLE t1; 2097create table t1 (a int,b int,key (b),key (a),key (b,a)); 2098insert into t1(a,b) values (1,2),(3,4),(5,6),(7,8); 2099create table t2 (c int); 2100insert into t2(c) values (1),(5),(6),(7),(8); 2101select 1 from (select c from t1,t2 where b >= 1 and a <=> c group by 1 limit 1) as d1; 21021 21031 2104drop table t1, t2; 2105# 2106# Bug #26106: Wrong plan may be chosen when there are several possible 2107# range and ref accesses 2108# 2109# Note: The fix for this bug has been reverted. The code will no longer 2110# select the optimal plan for the two following test queries. This is 2111# not due to a bug but due to minor differences in range estimates 2112# produced by the storage engine. 2113CREATE TABLE t1( 2114a INT, 2115b INT, 2116KEY k ( a ), 2117KEY l ( a, b ) 2118); 2119INSERT INTO t1(a) VALUES (1); 2120INSERT INTO t1 2121VALUES (2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3); 2122INSERT INTO t1 SELECT 3, 4 FROM t1 WHERE a = 2 AND b = 3; 2123INSERT INTO t1 SELECT 4, 1 FROM t1 WHERE a = 2 AND b = 3; 2124ANALYZE TABLE t1; 2125Table Op Msg_type Msg_text 2126test.t1 analyze status OK 2127INSERT INTO t1 VALUES (1, 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; 2134INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; 2135# This must use range over index l, not k. 2136# Update: Due to patch being reverted and minor differences in 2137# range estimates k is selected. 2138EXPLAIN SELECT * FROM t1 WHERE a = 1 AND b >= 2; 2139id select_type table partitions type possible_keys key key_len ref rows filtered Extra 21401 SIMPLE t1 NULL ref k,l k 5 const 134 33.33 Using where 2141Warnings: 2142Note 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)) 2143CREATE TABLE t2( 2144a INT, 2145b INT, 2146c INT, 2147KEY k ( a ), 2148KEY l ( a, b ), 2149KEY m ( b ), 2150KEY n ( a, c ) 2151); 2152INSERT INTO t2(a) VALUES (1); 2153INSERT INTO t2 2154VALUES (2,3,3),(2,3,3),(2,3,3),(2,3,3),(2,3,3), 2155(2,3,3),(2,3,3),(2,3,3),(2,3,3),(2,3,3); 2156INSERT INTO t2 SELECT 3, 4, 4 FROM t2 WHERE a = 2 AND b = 3; 2157INSERT INTO t2 SELECT 4, 1, 1 FROM t2 WHERE a = 2 AND b = 3; 2158ANALYZE TABLE t2; 2159Table Op Msg_type Msg_text 2160test.t2 analyze status OK 2161INSERT INTO t2 VALUES (1, 2, 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 SELECT a, b, c FROM t2 WHERE a=1 AND b=2; 2168INSERT INTO t2 VALUES (1, 1, 2); 2169# This must use range over index l, not n. 2170# Update: Due to patch being reverted and minor differences in 2171# range estimates k is selected. 2172EXPLAIN SELECT * FROM t2 WHERE a = 1 AND b >= 2 AND c >= 2; 2173id select_type table partitions type possible_keys key key_len ref rows filtered Extra 21741 SIMPLE t2 NULL ref k,l,m,n k 5 const 66 29.16 Using where 2175Warnings: 2176Note 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)) 2177DROP TABLE t1, t2; 2178# 2179# BUG#11765831: 'RANGE ACCESS' MAY INCORRECTLY FILTER 2180# AWAY QUALIFYING ROWS 2181# 2182CREATE TABLE t10( 2183K INT NOT NULL AUTO_INCREMENT, 2184I INT, J INT, 2185PRIMARY KEY(K), 2186KEY(I,J) 2187); 2188INSERT INTO t10(I,J) VALUES (6,1),(6,2),(6,3),(6,4),(6,5), 2189(6,6),(6,7),(6,8),(6,9),(6,0); 2190CREATE TABLE t100 LIKE t10; 2191INSERT INTO t100(I,J) SELECT X.I, X.K+(10*Y.K) FROM t10 AS X,t10 AS Y; 2192INSERT INTO t100(I,J) VALUES(8,26); 2193 2194EXPLAIN SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5); 2195id select_type table partitions type possible_keys key key_len ref rows filtered Extra 21961 SIMPLE t100 NULL range I I 10 NULL 4 100.00 Using where 2197Warnings: 2198Note 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))) 2199 2200SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5); 2201K I J 2202101 8 26 2203DROP TABLE t10,t100; 2204# 2205# BUG#12698916 - JOIN QUERY GIVES WRONG RESULT AT 2ND EXEC. OR 2206# AFTER FLUSH TABLES [-INT VS NULL] 2207# 2208CREATE TABLE t1 (col_int INT, pk INT) ENGINE=InnoDB STATS_PERSISTENT=0; 2209INSERT INTO t1 VALUES (-100,1),(1,6); 2210CREATE TABLE t2 ( 2211col_int_key INT, 2212col_varchar VARCHAR(100) NOT NULL DEFAULT "DEFAULT", 2213pk INT NOT NULL, 2214PRIMARY KEY (pk), 2215KEY (col_int_key) 2216) ENGINE=InnoDB STATS_PERSISTENT=0; 2217INSERT INTO t2 VALUES 2218(1,"GOOD",1),(100,"",2),(200,"",3),(300,"",4),(400,"",5),(500,"",8); 2219EXPLAIN SELECT t1.*,t2.* FROM t1 straight_join t2 2220ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk; 2221id select_type table partitions type possible_keys key key_len ref rows filtered Extra 22221 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where 22231 SIMPLE t2 NULL ref PRIMARY,col_int_key col_int_key 5 test.t1.col_int 1 33.33 Using where 2224Warnings: 2225Note 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`)) 2226SELECT t1.*,t2.* FROM t1 straight_join t2 2227ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk; 2228col_int pk col_int_key col_varchar pk 22291 6 1 GOOD 1 2230# need FLUSH so that InnoDB statistics change and thus plan changes 2231FLUSH TABLES; 2232EXPLAIN SELECT t1.*,t2.* FROM t1 straight_join t2 2233ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk; 2234id select_type table partitions type possible_keys key key_len ref rows filtered Extra 22351 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 22361 SIMPLE t2 NULL ALL PRIMARY,col_int_key NULL NULL NULL 6 33.33 Range checked for each record (index map: 0x3) 2237Warnings: 2238Note 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`)) 2239SELECT t1.*,t2.* FROM t1 straight_join t2 2240ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk; 2241col_int pk col_int_key col_varchar pk 22421 6 1 GOOD 1 2243DROP TABLE t1,t2; 2244# 2245# Bug#12694872 - 2246# VALGRIND: 18,816 BYTES IN 196 BLOCKS ARE DEFINITELY LOST IN UNIQUE::GET 2247# 2248CREATE TABLE t1 ( 2249pk INTEGER AUTO_INCREMENT, 2250col_int_nokey INTEGER NOT NULL, 2251col_int_key INTEGER NOT NULL, 2252col_date_key DATE NOT NULL, 2253col_varchar_key VARCHAR(1) NOT NULL, 2254col_varchar_nokey VARCHAR(1) NOT NULL, 2255PRIMARY KEY (pk), 2256KEY (col_int_key), 2257KEY (col_date_key), 2258KEY (col_varchar_key, col_int_key) 2259); 2260INSERT INTO t1 ( 2261col_int_key, 2262col_int_nokey, 2263col_date_key, 2264col_varchar_key, 2265col_varchar_nokey 2266) VALUES 2267(0, 4, '2011-08-25', 'j', 'j'), 2268(8, 6, '2004-09-18', 'v', 'v'), 2269(1, 3, '2009-12-01', 'c', 'c'), 2270(8, 5, '2004-12-17', 'm', 'm'), 2271(9, 3, '2000-03-14', 'd', 'd'), 2272(6, 2, '2006-05-25', 'y', 'y'), 2273(1, 9, '2008-01-23', 't', 't'), 2274(6, 3, '2007-06-18', 'd', 'd'), 2275(2, 8, '2002-10-13', 's', 's'), 2276(4, 1, '1900-01-01', 'r', 'r'), 2277(8, 8, '1959-04-25', 'm', 'm'), 2278(4, 8, '2006-03-09', 'b', 'b'), 2279(4, 5, '2001-06-05', 'x', 'x'), 2280(7, 7, '2006-05-28', 'g', 'g'), 2281(4, 5, '2001-04-19', 'p', 'p'), 2282(1, 1, '1900-01-01', 'q', 'q'), 2283(9, 6, '2004-08-20', 'w', 'w'), 2284(4, 2, '2004-10-10', 'd', 'd'), 2285(8, 9, '2000-04-02', 'e', 'e') 2286; 2287ALTER TABLE t1 DISABLE KEYS; 2288SELECT table2.col_date_key AS field1, 2289CONCAT ( table2.col_varchar_nokey, table1.col_varchar_nokey ) AS field2 2290FROM ( t1 AS table1 INNER JOIN t1 AS table2 2291ON (( table2.pk <> table1.pk ) AND 2292( table2.pk >= table1.col_int_nokey ) ) ) 2293WHERE ( table1.pk > 226 AND 2294table1.pk < ( 226 + 102 ) OR 2295( table1.col_int_key > 226 AND 2296table1.col_int_key < ( 226 + 36 ) OR 2297( table1.col_varchar_key <= 'h' OR 2298table1.col_int_key > 226 AND 2299table1.col_int_key < ( 226 + 227 ) ) 2300) 2301) 2302; 2303field1 field2 23041900-01-01 qb 23051900-01-01 qc 23061900-01-01 qd 23071900-01-01 qd 23081900-01-01 qd 23091900-01-01 qe 23101900-01-01 qg 23111900-01-01 rb 23121900-01-01 rc 23131900-01-01 rd 23141900-01-01 rd 23151900-01-01 rd 23161900-01-01 re 23171900-01-01 rg 23181959-04-25 mb 23191959-04-25 mc 23201959-04-25 md 23211959-04-25 md 23221959-04-25 md 23231959-04-25 me 23241959-04-25 mg 23252000-03-14 dc 23262000-03-14 dd 23272000-03-14 dd 23282000-04-02 eb 23292000-04-02 ec 23302000-04-02 ed 23312000-04-02 ed 23322000-04-02 ed 23332000-04-02 eg 23342001-04-19 pb 23352001-04-19 pc 23362001-04-19 pd 23372001-04-19 pd 23382001-04-19 pd 23392001-04-19 pe 23402001-04-19 pg 23412001-06-05 xb 23422001-06-05 xc 23432001-06-05 xd 23442001-06-05 xd 23452001-06-05 xd 23462001-06-05 xe 23472001-06-05 xg 23482002-10-13 sb 23492002-10-13 sc 23502002-10-13 sd 23512002-10-13 sd 23522002-10-13 sd 23532002-10-13 se 23542002-10-13 sg 23552004-08-20 wb 23562004-08-20 wc 23572004-08-20 wd 23582004-08-20 wd 23592004-08-20 wd 23602004-08-20 we 23612004-08-20 wg 23622004-09-18 vd 23632004-10-10 db 23642004-10-10 dc 23652004-10-10 dd 23662004-10-10 dd 23672004-10-10 de 23682004-10-10 dg 23692004-12-17 mc 23702004-12-17 md 23712004-12-17 md 23722004-12-17 md 23732006-03-09 bc 23742006-03-09 bd 23752006-03-09 bd 23762006-03-09 bd 23772006-03-09 be 23782006-03-09 bg 23792006-05-25 yc 23802006-05-25 yd 23812006-05-25 yd 23822006-05-25 yd 23832006-05-28 gb 23842006-05-28 gc 23852006-05-28 gd 23862006-05-28 gd 23872006-05-28 gd 23882006-05-28 ge 23892007-06-18 db 23902007-06-18 dc 23912007-06-18 dd 23922007-06-18 dd 23932007-06-18 dg 23942008-01-23 tc 23952008-01-23 td 23962008-01-23 td 23972008-01-23 td 23982008-01-23 tg 23992009-12-01 cd 24002009-12-01 cd 24012009-12-01 cd 2402ALTER TABLE t1 ENABLE KEYS; 2403CREATE TABLE t2 SELECT table2.col_date_key AS field1, 2404CONCAT ( table2.col_varchar_nokey, table1.col_varchar_nokey ) AS field2 2405FROM ( t1 AS table1 INNER JOIN t1 AS table2 2406ON (( table2.pk <> table1.pk ) AND 2407( table2.pk >= table1.col_int_nokey ) ) ) 2408WHERE ( table1.pk > 226 AND 2409table1.pk < ( 226 + 102 ) OR 2410( table1.col_int_key > 226 AND 2411table1.col_int_key < ( 226 + 36 ) OR 2412( table1.col_varchar_key <= 'h' OR 2413table1.col_int_key > 226 AND 2414table1.col_int_key < ( 226 + 227 ) ) 2415) 2416) 2417; 2418SELECT * FROM t2 2419WHERE (field1, field2) IN (SELECT table2.col_date_key AS field1, 2420CONCAT ( table2.col_varchar_nokey, table1.col_varchar_nokey ) AS field2 2421FROM ( t1 AS table1 INNER JOIN t1 AS table2 2422ON (( table2.pk <> table1.pk ) AND 2423( table2.pk >= table1.col_int_nokey ) ) ) 2424WHERE ( table1.pk > 226 AND 2425table1.pk < ( 226 + 102 ) OR 2426( table1.col_int_key > 226 AND 2427table1.col_int_key < ( 226 + 36 ) OR 2428( table1.col_varchar_key <= 'h' OR 2429table1.col_int_key > 226 AND 2430table1.col_int_key < ( 226 + 227 ) ) 2431) 2432) 2433); 2434field1 field2 24351900-01-01 qb 24361900-01-01 qc 24371900-01-01 qd 24381900-01-01 qd 24391900-01-01 qd 24401900-01-01 qe 24411900-01-01 qg 24421900-01-01 rb 24431900-01-01 rc 24441900-01-01 rd 24451900-01-01 rd 24461900-01-01 rd 24471900-01-01 re 24481900-01-01 rg 24491959-04-25 mb 24501959-04-25 mc 24511959-04-25 md 24521959-04-25 md 24531959-04-25 md 24541959-04-25 me 24551959-04-25 mg 24562000-03-14 dc 24572000-03-14 dd 24582000-03-14 dd 24592000-04-02 eb 24602000-04-02 ec 24612000-04-02 ed 24622000-04-02 ed 24632000-04-02 ed 24642000-04-02 eg 24652001-04-19 pb 24662001-04-19 pc 24672001-04-19 pd 24682001-04-19 pd 24692001-04-19 pd 24702001-04-19 pe 24712001-04-19 pg 24722001-06-05 xb 24732001-06-05 xc 24742001-06-05 xd 24752001-06-05 xd 24762001-06-05 xd 24772001-06-05 xe 24782001-06-05 xg 24792002-10-13 sb 24802002-10-13 sc 24812002-10-13 sd 24822002-10-13 sd 24832002-10-13 sd 24842002-10-13 se 24852002-10-13 sg 24862004-08-20 wb 24872004-08-20 wc 24882004-08-20 wd 24892004-08-20 wd 24902004-08-20 wd 24912004-08-20 we 24922004-08-20 wg 24932004-09-18 vd 24942004-10-10 db 24952004-10-10 dc 24962004-10-10 dd 24972004-10-10 dd 24982004-10-10 de 24992004-10-10 dg 25002004-12-17 mc 25012004-12-17 md 25022004-12-17 md 25032004-12-17 md 25042006-03-09 bc 25052006-03-09 bd 25062006-03-09 bd 25072006-03-09 bd 25082006-03-09 be 25092006-03-09 bg 25102006-05-25 yc 25112006-05-25 yd 25122006-05-25 yd 25132006-05-25 yd 25142006-05-28 gb 25152006-05-28 gc 25162006-05-28 gd 25172006-05-28 gd 25182006-05-28 gd 25192006-05-28 ge 25202007-06-18 db 25212007-06-18 dc 25222007-06-18 dd 25232007-06-18 dd 25242007-06-18 dg 25252008-01-23 tc 25262008-01-23 td 25272008-01-23 td 25282008-01-23 td 25292008-01-23 tg 25302009-12-01 cd 25312009-12-01 cd 25322009-12-01 cd 2533DROP TABLE t1, t2; 2534# 2535# BUG#12912171 - ASSERTION FAILED: QUICK->HEAD->READ_SET == 2536# SAVE_READ_SET 2537# 2538CREATE TABLE t1 ( 2539a INT, 2540b INT, 2541c INT, 2542PRIMARY KEY (c,a), KEY (a),KEY (a) 2543) ENGINE=INNODB PARTITION BY KEY () PARTITIONS 2; 2544Warnings: 2545Warning 1831 Duplicate index 'a_2' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release. 2546INSERT INTO t1 VALUES (1,5,1),(2,4,1),(3,3,1),(4,2,1),(5,1,1); 2547UPDATE t1 SET b = 0, c=1 WHERE a <=>0; 2548SELECT * FROM t1; 2549a b c 25501 5 1 25512 4 1 25523 3 1 25534 2 1 25545 1 1 2555DROP TABLE t1; 2556# 2557# BUG#13256446 - ASSERTION QUICK->HEAD->READ_SET == 2558# SAVE_READ_SET' FAILED IN OPT_RANGE.CC:1606 2559# 2560CREATE TABLE t1 ( 2561f1 INT AUTO_INCREMENT, 2562f2 INT, 2563f3 INT, 2564f4 INT, 2565PRIMARY KEY (f1),KEY(f2) 2566) ENGINE=INNODB; 2567CREATE ALGORITHM=MERGE VIEW v2 AS SELECT f1,f2,f3,f4 FROM t1 WHERE f2=103; 2568INSERT INTO t1 VALUES (154,0,NULL,0),(0,NULL,9,0), 2569(NULL,102,NULL,3),(0,3,NULL,0), (9,0,NULL,0),(0,9,NULL,157); 2570SELECT * FROM v2; 2571f1 f2 f3 f4 2572UPDATE v2 SET f4=0, f2=NULL, f1=NULL WHERE f1 > 16 ORDER BY f1; 2573SELECT * FROM v2; 2574f1 f2 f3 f4 2575DROP TABLE t1; 2576DROP VIEW v2; 2577CREATE TABLE t1 ( 2578f1 INT AUTO_INCREMENT, 2579f2 INT, 2580f3 INT, 2581f4 INT, 2582PRIMARY KEY (f1),KEY(f2) 2583) ENGINE=INNODB; 2584INSERT INTO t1 VALUES(1,NULL,NULL,0), (2,2,0,3), (9,0,107,18), 2585(10,0,0,0), (231,0,0,0), (232,0,8,0), (234,0,0,NULL), (235,8,0,3); 2586CREATE ALGORITHM=MERGE VIEW v3 AS SELECT f1,f2,f3,f4 FROM t1 WHERE f1<=85 ; 2587SELECT * FROM v3; 2588f1 f2 f3 f4 25891 NULL NULL 0 25902 2 0 3 25919 0 107 18 259210 0 0 0 2593UPDATE v3 SET f3=0, f4=4 WHERE f2=68 ORDER BY f1; 2594SELECT * FROM v3; 2595f1 f2 f3 f4 25961 NULL NULL 0 25972 2 0 3 25989 0 107 18 259910 0 0 0 2600DROP TABLE t1; 2601DROP VIEW v3; 2602# 2603# BUG#13731380: RANGE OPTIMIZER CALLS RECORDS_IN_RANGE() FOR OPEN RANGE 2604# 2605CREATE TABLE t1 (pk INT PRIMARY KEY); 2606INSERT INTO t1 VALUES (1),(3),(5); 2607SELECT * FROM t1 WHERE pk <> 3 OR pk < 4; 2608pk 26091 26103 26115 2612DROP TABLE t1; 2613# 2614# BUG#13803810: TOO FEW ROWS RETURNED FOR RANGE ACCESS IN 2615# VARCHAR INDEX USING DATETIME VALUE 2616 2617CREATE TABLE t1 (a DATETIME); 2618INSERT INTO t1 VALUES ('2001-01-01 00:00:00'); 2619INSERT INTO t1 VALUES ('2001-01-01 11:22:33'); 2620CREATE TABLE t2 (b VARCHAR(64), KEY (b)); 2621INSERT INTO t2 VALUES ('2001-01-01'); 2622INSERT INTO t2 VALUES ('2001.01.01'); 2623INSERT INTO t2 VALUES ('2001#01#01'); 2624INSERT INTO t2 VALUES ('2001-01-01 00:00:00'); 2625INSERT INTO t2 VALUES ('2001-01-01 11:22:33'); 2626 2627# range/ref access cannot be used for this query 2628 2629EXPLAIN SELECT * FROM t2 WHERE b=CAST('2001-01-01' AS DATE); 2630id select_type table partitions type possible_keys key key_len ref rows filtered Extra 26311 SIMPLE t2 NULL index b b 67 NULL 5 20.00 Using where; Using index 2632Warnings: 2633Warning 1739 Cannot use ref access on index 'b' due to type or collation conversion on field 'b' 2634Warning 1739 Cannot use range access on index 'b' due to type or collation conversion on field 'b' 2635Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = <cache>(cast('2001-01-01' as date))) 2636SELECT * FROM t2 WHERE b=CAST('2001-01-01' AS DATE); 2637b 26382001#01#01 26392001-01-01 26402001-01-01 00:00:00 26412001.01.01 2642 2643# range/ref access cannot be used for any of the queries below. 2644# See BUG#13814468 about 'Range checked for each record' 2645 2646EXPLAIN SELECT * FROM t1, t2 WHERE a=b ORDER BY BINARY a, BINARY b; 2647id select_type table partitions type possible_keys key key_len ref rows filtered Extra 26481 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort 26491 SIMPLE t2 NULL index b b 67 NULL 5 20.00 Using where; Using index; Using join buffer (Block Nested Loop) 2650Warnings: 2651Warning 1739 Cannot use ref access on index 'b' due to type or collation conversion on field 'b' 2652Warning 1739 Cannot use range access on index 'b' due to type or collation conversion on field 'b' 2653Note 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) 2654SELECT * FROM t1, t2 WHERE a=b ORDER BY BINARY a, BINARY b; 2655a b 26562001-01-01 00:00:00 2001#01#01 26572001-01-01 00:00:00 2001-01-01 26582001-01-01 00:00:00 2001-01-01 00:00:00 26592001-01-01 00:00:00 2001.01.01 26602001-01-01 11:22:33 2001-01-01 11:22:33 2661 2662EXPLAIN SELECT * FROM t1, t2 WHERE b=a ORDER BY BINARY a, BINARY b; 2663id select_type table partitions type possible_keys key key_len ref rows filtered Extra 26641 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort 26651 SIMPLE t2 NULL index b b 67 NULL 5 20.00 Using where; Using index; Using join buffer (Block Nested Loop) 2666Warnings: 2667Warning 1739 Cannot use ref access on index 'b' due to type or collation conversion on field 'b' 2668Warning 1739 Cannot use range access on index 'b' due to type or collation conversion on field 'b' 2669Note 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) 2670SELECT * FROM t1, t2 WHERE b=a ORDER BY BINARY a, BINARY b; 2671a b 26722001-01-01 00:00:00 2001#01#01 26732001-01-01 00:00:00 2001-01-01 26742001-01-01 00:00:00 2001-01-01 00:00:00 26752001-01-01 00:00:00 2001.01.01 26762001-01-01 11:22:33 2001-01-01 11:22:33 2677 2678DROP TABLE t1,t2; 2679# 2680# WL#7019: Add support for row value constructors in in predicates to 2681# range optimizer 2682# 2683CREATE TABLE t1 (a INT, b INT, c INT, KEY x(a, b)); 2684INSERT INTO t1 VALUES (2, 2, 2), (3, 3, 3), (4, 4, 4), (5, 5, 5), (6, 6, 6), 2685(7, 7, 7), (8, 8, 8), (9, 9, 9); 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 SELECT * FROM t1; 2695INSERT INTO t1 VALUES (0, 0, 0), (1, 1, 1); 2696ANALYZE TABLE t1; 2697Table Op Msg_type Msg_text 2698test.t1 analyze status OK 2699CREATE TABLE t2 (a INT, b INT, c INT, d INT, KEY x(a, b)); 2700INSERT INTO t2 VALUES (2, 2, 2, 2), (3, 3, 3, 3), (4, 4, 4, 4), (5, 5, 5, 5), 2701(6, 6, 6, 6), (7, 7, 7, 7), (8, 8, 8, 8), (9, 9, 9, 9); 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 SELECT * FROM t2; 2711INSERT INTO t2 VALUES (0, 0, 0, 0), (1, 1, 1, 1); 2712ANALYZE TABLE t2; 2713Table Op Msg_type Msg_text 2714test.t2 analyze status OK 2715CREATE TABLE t3 (a INT, b INT, c INT, d INT, KEY x(a, b, c)); 2716INSERT INTO t3 VALUES (2, 2, 2, 2), (3, 3, 3, 3), (4, 4, 4, 4), (5, 5, 5, 5), 2717(6, 6, 6, 6), (7, 7, 7, 7), (8, 8, 8, 8), (9, 9, 9, 9); 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 SELECT * FROM t3; 2727INSERT INTO t3 VALUES (0, 0, 0, 0), (1, 1, 1, 1); 2728ANALYZE TABLE t3; 2729Table Op Msg_type Msg_text 2730test.t3 analyze status OK 2731EXPLAIN SELECT a, b FROM t1 WHERE (a, b) IN ((NULL, NULL), (NULL, NULL)); 2732id select_type table partitions type possible_keys key key_len ref rows filtered Extra 27331 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table 2734Warnings: 2735Note 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))) 2736EXPLAIN SELECT a, b FROM t1 WHERE (a = 0 AND b = 0) OR (a = 1 AND b = 1); 2737id select_type table partitions type possible_keys key key_len ref rows filtered Extra 27381 SIMPLE t1 NULL range x x 10 NULL 2 100.00 Using where; Using index 2739Warnings: 2740Note 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))) 2741FLUSH STATUS; 2742SELECT a, b FROM t1 WHERE (a = 0 AND b = 0) OR (a = 1 AND b = 1); 2743a b 27440 0 27451 1 2746SHOW STATUS LIKE 'Handler_read_%'; 2747Variable_name Value 2748Handler_read_first 0 2749Handler_read_key 2 2750Handler_read_last 0 2751Handler_read_next 2 2752Handler_read_prev 0 2753Handler_read_rnd 0 2754Handler_read_rnd_next 0 2755EXPLAIN SELECT a, b FROM t1 WHERE (a, b) IN ((0, 0)); 2756id select_type table partitions type possible_keys key key_len ref rows filtered Extra 27571 SIMPLE t1 NULL ref x x 10 const,const 1 100.00 Using index 2758Warnings: 2759Note 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)) 2760FLUSH STATUS; 2761SELECT a, b FROM t1 WHERE (a, b) IN ((0, 0)); 2762a b 27630 0 2764SHOW STATUS LIKE 'Handler_read_%'; 2765Variable_name Value 2766Handler_read_first 0 2767Handler_read_key 1 2768Handler_read_last 0 2769Handler_read_next 1 2770Handler_read_prev 0 2771Handler_read_rnd 0 2772Handler_read_rnd_next 0 2773EXPLAIN SELECT a, b FROM t1 WHERE (a, b) IN ((0, 0), (1, 1)); 2774id select_type table partitions type possible_keys key key_len ref rows filtered Extra 27751 SIMPLE t1 NULL range x x 10 NULL 2 100.00 Using where; Using index 2776Warnings: 2777Note 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)))) 2778FLUSH STATUS; 2779SELECT a, b FROM t1 WHERE (a, b) IN ((0, 0), (1, 1)); 2780a b 27810 0 27821 1 2783SHOW STATUS LIKE 'Handler_read_%'; 2784Variable_name Value 2785Handler_read_first 0 2786Handler_read_key 2 2787Handler_read_last 0 2788Handler_read_next 2 2789Handler_read_prev 0 2790Handler_read_rnd 0 2791Handler_read_rnd_next 0 2792EXPLAIN SELECT a, b, c FROM t2 WHERE (a, b, c) IN ((0, 0, 0), (1, 1, 1)); 2793id select_type table partitions type possible_keys key key_len ref rows filtered Extra 27941 SIMPLE t2 NULL range x x 10 NULL 2 20.00 Using where 2795Warnings: 2796Note 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)))) 2797FLUSH STATUS; 2798SELECT a, b, c FROM t2 WHERE (a, b, c) IN ((0, 0, 0), (1, 1, 1)); 2799a b c 28000 0 0 28011 1 1 2802SHOW STATUS LIKE 'Handler_read_%'; 2803Variable_name Value 2804Handler_read_first 0 2805Handler_read_key 2 2806Handler_read_last 0 2807Handler_read_next 2 2808Handler_read_prev 0 2809Handler_read_rnd 0 2810Handler_read_rnd_next 0 2811EXPLAIN SELECT a, b, c FROM t3 WHERE (a, b, c) IN ((0, 0, 0), (1, 1, 1)); 2812id select_type table partitions type possible_keys key key_len ref rows filtered Extra 28131 SIMPLE t3 NULL range x x 15 NULL 3 100.00 Using where; Using index 2814Warnings: 2815Note 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)))) 2816FLUSH STATUS; 2817SELECT a, b, c FROM t3 WHERE (a, b, c) IN ((0, 0, 0), (1, 1, 1)); 2818a b c 28190 0 0 28201 1 1 2821SHOW STATUS LIKE 'Handler_read_%'; 2822Variable_name Value 2823Handler_read_first 0 2824Handler_read_key 2 2825Handler_read_last 0 2826Handler_read_next 2 2827Handler_read_prev 0 2828Handler_read_rnd 0 2829Handler_read_rnd_next 0 2830EXPLAIN SELECT a, b FROM t1 WHERE a = 0 AND b = 0 OR (a, b) IN ((1, 1)); 2831id select_type table partitions type possible_keys key key_len ref rows filtered Extra 28321 SIMPLE t1 NULL range x x 10 NULL 2 100.00 Using where; Using index 2833Warnings: 2834Note 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))) 2835FLUSH STATUS; 2836SELECT a, b FROM t1 WHERE a = 0 AND b = 0 OR (a, b) IN ((1, 1)); 2837a b 28380 0 28391 1 2840SHOW STATUS LIKE 'Handler_read_%'; 2841Variable_name Value 2842Handler_read_first 0 2843Handler_read_key 2 2844Handler_read_last 0 2845Handler_read_next 2 2846Handler_read_prev 0 2847Handler_read_rnd 0 2848Handler_read_rnd_next 0 2849EXPLAIN SELECT a, b FROM t1 WHERE (a, b) IN ((1, 1)) OR a = 0 AND b = 0; 2850id select_type table partitions type possible_keys key key_len ref rows filtered Extra 28511 SIMPLE t1 NULL range x x 10 NULL 2 100.00 Using where; Using index 2852Warnings: 2853Note 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))) 2854FLUSH STATUS; 2855SELECT a, b FROM t1 WHERE (a, b) IN ((1, 1)) OR a = 0 AND b = 0; 2856a b 28570 0 28581 1 2859SHOW STATUS LIKE 'Handler_read_%'; 2860Variable_name Value 2861Handler_read_first 0 2862Handler_read_key 2 2863Handler_read_last 0 2864Handler_read_next 2 2865Handler_read_prev 0 2866Handler_read_rnd 0 2867Handler_read_rnd_next 0 2868EXPLAIN SELECT a, b FROM t2 WHERE (a, b, c) IN ((1, 1, 1)) OR a = 0 AND b = 0 AND c = 0; 2869id select_type table partitions type possible_keys key key_len ref rows filtered Extra 28701 SIMPLE t2 NULL range x x 10 NULL 2 19.00 Using where 2871Warnings: 2872Note 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))) 2873FLUSH STATUS; 2874SELECT a, b FROM t2 WHERE (a, b, c) IN ((1, 1, 1)) OR a = 0 AND b = 0 AND c = 0; 2875a b 28760 0 28771 1 2878SHOW STATUS LIKE 'Handler_read_%'; 2879Variable_name Value 2880Handler_read_first 0 2881Handler_read_key 2 2882Handler_read_last 0 2883Handler_read_next 2 2884Handler_read_prev 0 2885Handler_read_rnd 0 2886Handler_read_rnd_next 0 2887EXPLAIN SELECT a, b FROM t3 WHERE (a, b, c) IN ((1, 1, 1)) OR a = 0 AND b = 0 AND c = 0; 2888id select_type table partitions type possible_keys key key_len ref rows filtered Extra 28891 SIMPLE t3 NULL range x x 15 NULL 3 100.00 Using where; Using index 2890Warnings: 2891Note 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))) 2892FLUSH STATUS; 2893SELECT a, b FROM t3 WHERE (a, b, c) IN ((1, 1, 1)) OR a = 0 AND b = 0 AND c = 0; 2894a b 28950 0 28961 1 2897SHOW STATUS LIKE 'Handler_read_%'; 2898Variable_name Value 2899Handler_read_first 0 2900Handler_read_key 2 2901Handler_read_last 0 2902Handler_read_next 2 2903Handler_read_prev 0 2904Handler_read_rnd 0 2905Handler_read_rnd_next 0 2906EXPLAIN SELECT a, b FROM t1 WHERE (a, b) IN (((SELECT 1), 1)); 2907id select_type table partitions type possible_keys key key_len ref rows filtered Extra 29081 SIMPLE t1 NULL ref x x 10 const,const 1 100.00 Using index 2909Warnings: 2910Note 1249 Select 2 was reduced during optimization 2911Note 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)) 2912FLUSH STATUS; 2913SELECT a, b FROM t1 WHERE (a, b) IN (((SELECT 1), 1)); 2914a b 29151 1 2916SHOW STATUS LIKE 'Handler_read_%'; 2917Variable_name Value 2918Handler_read_first 0 2919Handler_read_key 1 2920Handler_read_last 0 2921Handler_read_next 1 2922Handler_read_prev 0 2923Handler_read_rnd 0 2924Handler_read_rnd_next 0 2925EXPLAIN SELECT a, b FROM t1 WHERE (a, b) IN (('0', 0), (1, 1)); 2926id select_type table partitions type possible_keys key key_len ref rows filtered Extra 29271 SIMPLE t1 NULL range x x 10 NULL 2 100.00 Using where; Using index 2928Warnings: 2929Note 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)))) 2930FLUSH STATUS; 2931SELECT a, b FROM t1 WHERE (a, b) IN (('0', 0), (1, 1)); 2932a b 29330 0 29341 1 2935SHOW STATUS LIKE 'Handler_read_%'; 2936Variable_name Value 2937Handler_read_first 0 2938Handler_read_key 2 2939Handler_read_last 0 2940Handler_read_next 2 2941Handler_read_prev 0 2942Handler_read_rnd 0 2943Handler_read_rnd_next 0 2944SET @v = 0; 2945EXPLAIN SELECT a, b FROM t1 WHERE (a, b) IN ((@v, 0), (1, 1)); 2946id select_type table partitions type possible_keys key key_len ref rows filtered Extra 29471 SIMPLE t1 NULL range x x 10 NULL 2 100.00 Using where; Using index 2948Warnings: 2949Note 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)))) 2950FLUSH STATUS; 2951SELECT a, b FROM t1 WHERE (a, b) IN ((@v, 0), (1, 1)); 2952a b 29530 0 29541 1 2955SHOW STATUS LIKE 'Handler_read_%'; 2956Variable_name Value 2957Handler_read_first 0 2958Handler_read_key 2 2959Handler_read_last 0 2960Handler_read_next 2 2961Handler_read_prev 0 2962Handler_read_rnd 0 2963Handler_read_rnd_next 0 2964CREATE TABLE t4 ( a INT, b INT ); 2965INSERT INTO t4 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); 2966INSERT INTO t4 SELECT a + 5, b + 5 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; 2972INSERT INTO t4 SELECT * FROM t4; 2973# Inner table in a nested-loops join 2974EXPLAIN 2975SELECT t4.*, t1.a, t1.b 2976FROM t4 JOIN t1 USING(a, b) 2977WHERE (t4.a, t4.b) IN ((1, 1), (0, 0)); 2978id select_type table partitions type possible_keys key key_len ref rows filtered Extra 29791 SIMPLE t4 NULL ALL NULL NULL NULL NULL 640 2.00 Using where 29801 SIMPLE t1 NULL ref x x 10 test.t4.a,test.t4.b 410 100.00 Using index 2981Warnings: 2982Note 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))))) 2983# Join on IN 2984EXPLAIN 2985SELECT t4.*, t1.a, t1.b 2986FROM t4 JOIN t1 2987WHERE (t1.a, t1.b) IN ((t4.a, t4.b), (0, 0)); 2988id select_type table partitions type possible_keys key key_len ref rows filtered Extra 29891 SIMPLE t4 NULL ALL NULL NULL NULL NULL 640 100.00 NULL 29901 SIMPLE t1 NULL index x x 10 NULL 4098 2.00 Using where; Using index; Using join buffer (Block Nested Loop) 2991Warnings: 2992Note 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)))) 2993EXPLAIN 2994SELECT t4.*, t1.a, t1.b 2995FROM t4 JOIN t1 2996WHERE (t1.a, t1.b) IN ((t4.a, t4.b), (1, 1)); 2997id select_type table partitions type possible_keys key key_len ref rows filtered Extra 29981 SIMPLE t4 NULL ALL NULL NULL NULL NULL 640 100.00 NULL 29991 SIMPLE t1 NULL index x x 10 NULL 4098 2.00 Using where; Using index; Using join buffer (Block Nested Loop) 3000Warnings: 3001Note 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)))) 3002# 3003# Tests for non-deterministic functions. 3004# 3005CREATE FUNCTION f1() RETURNS INT NOT DETERMINISTIC RETURN 1; 3006# The statement immediately below should not use range access. 3007EXPLAIN SELECT a, b FROM t1 WHERE (a, b, c) IN ((0, 0, 0), (f1(), 1, 1)); 3008id select_type table partitions type possible_keys key key_len ref rows filtered Extra 30091 SIMPLE t1 NULL ALL x NULL NULL NULL 4098 0.20 Using where 3010Warnings: 3011Note 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))) 3012EXPLAIN SELECT a, b FROM t1 WHERE (a, b, c) IN ((0, 0, 0), (1, f1(), 1)); 3013id select_type table partitions type possible_keys key key_len ref rows filtered Extra 30141 SIMPLE t1 NULL range x x 10 NULL 2 20.00 Using where 3015Warnings: 3016Note 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))) 3017EXPLAIN SELECT a, b FROM t1 WHERE (a, b, c) IN ((0, 0, 0), (1, 1, f1())); 3018id select_type table partitions type possible_keys key key_len ref rows filtered Extra 30191 SIMPLE t1 NULL range x x 10 NULL 2 20.00 Using where 3020Warnings: 3021Note 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`()))) 3022# The statement immediately below should not use range access. 3023EXPLAIN SELECT a, b FROM t1 WHERE (a, b) IN ((f1(), 1)); 3024id select_type table partitions type possible_keys key key_len ref rows filtered Extra 30251 SIMPLE t1 NULL index NULL x 10 NULL 4098 1.00 Using where; Using index 3026Warnings: 3027Note 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`())) 3028DROP FUNCTION f1; 3029# The statement immediately below should not use range access. 3030EXPLAIN 3031SELECT a, b 3032FROM t1 AS t1o 3033WHERE EXISTS ( SELECT 1 FROM t1 WHERE (a, b) IN ((t1o.a, t1o.b)) ); 3034id select_type table partitions type possible_keys key key_len ref rows filtered Extra 30351 PRIMARY t1o NULL index NULL x 10 NULL 4098 100.00 Using where; Using index 30362 DEPENDENT SUBQUERY t1 NULL ref x x 10 test.t1o.a,test.t1o.b 410 100.00 Using index 3037Warnings: 3038Note 1276 Field or reference 'test.t1o.a' of SELECT #2 was resolved in SELECT #1 3039Note 1276 Field or reference 'test.t1o.b' of SELECT #2 was resolved in SELECT #1 3040Note 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`))) 3041# 3042# Tests of dynamic range access 3043# 3044CREATE TABLE t5 (a int, b int, KEY (a)); 3045INSERT INTO t5 VALUES (1, 1), (2, 1), (3, 1), (4, 1), (5, 1), (6, 1); 3046CREATE TABLE t6 (a int, b int, KEY (a)); 3047INSERT INTO t6 VALUES (3, 2), (4, 2), (100, 100), (101, 201), (102, 102); 3048CREATE TABLE t7 (a int, b int, KEY (a, b)); 3049INSERT INTO t7 VALUES (1, 1), (2, 2), (1000, 1000), (1001, 1001), (1002, 1002), 3050(1003, 1003), (1004, 1004); 3051EXPLAIN SELECT * 3052FROM t5 JOIN t6 ON t5.a = t6.a JOIN t7 3053WHERE t7.a IN (t5.b, t6.b); 3054id select_type table partitions type possible_keys key key_len ref rows filtered Extra 30551 SIMPLE t6 NULL ALL a NULL NULL NULL 5 100.00 NULL 30561 SIMPLE t5 NULL ALL a NULL NULL NULL 6 16.67 Using where; Using join buffer (Block Nested Loop) 30571 SIMPLE t7 NULL ALL a NULL NULL NULL 7 28.57 Range checked for each record (index map: 0x1) 3058Warnings: 3059Note 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`))) 3060FLUSH STATUS; 3061SELECT * 3062FROM t5 JOIN t6 ON t5.a = t6.a JOIN t7 3063WHERE t7.a IN (t5.b, t6.b); 3064a b a b a b 30653 1 3 2 1 1 30663 1 3 2 2 2 30674 1 4 2 1 1 30684 1 4 2 2 2 3069SHOW STATUS LIKE 'Handler_read_%'; 3070Variable_name Value 3071Handler_read_first 0 3072Handler_read_key 4 3073Handler_read_last 0 3074Handler_read_next 4 3075Handler_read_prev 0 3076Handler_read_rnd 0 3077Handler_read_rnd_next 13 3078EXPLAIN SELECT * 3079FROM t5 JOIN t6 ON t5.a = t6.a JOIN t7 3080WHERE (t7.a, t7.b) IN ((t5.b, 1), (t6.b, 1)); 3081id select_type table partitions type possible_keys key key_len ref rows filtered Extra 30821 SIMPLE t6 NULL ALL a NULL NULL NULL 5 100.00 NULL 30831 SIMPLE t5 NULL ALL a NULL NULL NULL 6 16.67 Using where; Using join buffer (Block Nested Loop) 30841 SIMPLE t7 NULL ALL a NULL NULL NULL 7 14.29 Range checked for each record (index map: 0x1) 3085Warnings: 3086Note 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)))) 3087FLUSH STATUS; 3088SELECT * 3089FROM t5 JOIN t6 ON t5.a = t6.a JOIN t7 3090WHERE (t7.a, t7.b) IN ((t5.b, 1), (t6.b, 1)); 3091a b a b a b 30923 1 3 2 1 1 30934 1 4 2 1 1 3094SHOW STATUS LIKE 'Handler_read_%'; 3095Variable_name Value 3096Handler_read_first 0 3097Handler_read_key 4 3098Handler_read_last 0 3099Handler_read_next 2 3100Handler_read_prev 0 3101Handler_read_rnd 0 3102Handler_read_rnd_next 13 3103# 3104# Regression tests of the worklog below. 3105# 3106# Make sure we process IN predicates only. 3107# The code around this is very unclear. 3108EXPLAIN SELECT a, b FROM t1 WHERE (a, b) != (1, 1); 3109id select_type table partitions type possible_keys key key_len ref rows filtered Extra 31101 SIMPLE t1 NULL index x x 10 NULL 4098 99.00 Using where; Using index 3111Warnings: 3112Note 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)) 3113# This should give us a ref scan, as it always did. 3114EXPLAIN SELECT a, b FROM t1 WHERE (a, b) IN ((0, 0)); 3115id select_type table partitions type possible_keys key key_len ref rows filtered Extra 31161 SIMPLE t1 NULL ref x x 10 const,const 1 100.00 Using index 3117Warnings: 3118Note 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)) 3119EXPLAIN SELECT a, b FROM t1 WHERE (a, c) IN ((0, 0)); 3120id select_type table partitions type possible_keys key key_len ref rows filtered Extra 31211 SIMPLE t1 NULL ref x x 5 const 1 10.00 Using where 3122Warnings: 3123Note 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)) 3124# ref scan on x(a). 3125EXPLAIN SELECT * FROM t1 WHERE (a, c) IN ((0, 0), (1, 1)); 3126id select_type table partitions type possible_keys key key_len ref rows filtered Extra 31271 SIMPLE t1 NULL range x x 5 NULL 2 20.00 Using where 3128Warnings: 3129Note 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)))) 3130DROP TABLE t1, t2, t3, t4, t5, t6, t7; 3131# 3132# Bug#17635476: CRASH IN GET_MM_PARTS() OR ASSERT IN 3133# GET_FUNC_MM_TREE_FROM_IN_PREDICATE() 3134# 3135CREATE TABLE t1 ( 3136a INT, 3137b INT, 3138KEY (a) 3139) ENGINE = INNODB; 3140SELECT DISTINCT a FROM t1 WHERE (a, b) IN ((0, 0), (1, 1)); 3141a 3142INSERT INTO t1 VALUES (0, 0), (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), 3143(7, 7), (8, 8), (9, 9); 3144ANALYZE TABLE t1; 3145Table Op Msg_type Msg_text 3146test.t1 analyze status OK 3147CREATE TABLE t2 ( 3148a INT, 3149b INT, 3150KEY (a, b) 3151); 3152INSERT INTO t2 SELECT * FROM t1; 3153ANALYZE TABLE t2; 3154Table Op Msg_type Msg_text 3155test.t2 analyze status Table is already up to date 3156EXPLAIN SELECT DISTINCT a FROM t1 WHERE (a, b) IN ((0, 0), (1, 1)); 3157id select_type table partitions type possible_keys key key_len ref rows filtered Extra 31581 SIMPLE t1 NULL range a a 5 NULL 2 20.00 Using where 3159Warnings: 3160Note 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)))) 3161FLUSH STATUS; 3162SELECT DISTINCT a FROM t1 WHERE (a, b) IN ((0, 0), (1, 1)); 3163a 31640 31651 3166SHOW STATUS LIKE 'Handler_read_%'; 3167Variable_name Value 3168Handler_read_first 0 3169Handler_read_key 2 3170Handler_read_last 0 3171Handler_read_next 2 3172Handler_read_prev 0 3173Handler_read_rnd 0 3174Handler_read_rnd_next 0 3175EXPLAIN SELECT DISTINCT a FROM t2 WHERE (a, b) IN ((0, 0), (1, 1)); 3176id select_type table partitions type possible_keys key key_len ref rows filtered Extra 31771 SIMPLE t2 NULL range a a 10 NULL 2 100.00 Using where; Using index 3178Warnings: 3179Note 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)))) 3180FLUSH STATUS; 3181SELECT DISTINCT a FROM t2 WHERE (a, b) IN ((0, 0), (1, 1)); 3182a 31830 31841 3185SHOW STATUS LIKE 'Handler_read_%'; 3186Variable_name Value 3187Handler_read_first 0 3188Handler_read_key 2 3189Handler_read_last 0 3190Handler_read_next 2 3191Handler_read_prev 0 3192Handler_read_rnd 0 3193Handler_read_rnd_next 0 3194# Should not use range scan. 3195EXPLAIN SELECT DISTINCT a FROM t1 WHERE (NULL, b) IN ((0, 0), (1, 1)); 3196id select_type table partitions type possible_keys key key_len ref rows filtered Extra 31971 SIMPLE t1 NULL index a a 5 NULL 10 20.00 Using where 3198Warnings: 3199Note 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)))) 3200# Should not use range scan. 3201EXPLAIN SELECT DISTINCT a FROM t2 WHERE (NULL, b) IN ((0, 0), (1, 1)); 3202id select_type table partitions type possible_keys key key_len ref rows filtered Extra 32031 SIMPLE t2 NULL index a a 10 NULL 10 20.00 Using where; Using index 3204Warnings: 3205Note 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)))) 3206DROP TABLE t1, t2; 3207# 3208# Bug#17755540 VALGRIND ERROR WHEN SETTING UP ROW COMPARATORS 3209# 3210CREATE TABLE t2 (a INT, b INT, c INT, d INT, KEY x(a, b)); 3211INSERT INTO t2 VALUES (2, 2, 2, 2), (3, 3, 3, 3), (4, 4, 4, 4), (5, 5, 5, 5), 3212(6, 6, 6, 6), (7, 7, 7, 7), (8, 8, 8, 8), (9, 9, 9, 9); 3213INSERT INTO t2 SELECT * FROM t2; 3214INSERT INTO t2 VALUES (0, 0, 0, 0), (1, 1, 1, 1); 3215ANALYZE TABLE t2; 3216Table Op Msg_type Msg_text 3217test.t2 analyze status OK 3218SELECT a, b FROM t2 WHERE (a, b) IN ((0, 0), (1, 1)); 3219a b 32200 0 32211 1 3222DROP TABLE t2; 3223# 3224# BUG#18364815: OPTIMIZER PREFERS TABLE SCAN WHEN 3225# USING "IN" WITH VALUE OF DIFFERENT TYPE 3226# 3227CREATE TABLE t1 (a INT PRIMARY KEY, b INT); 3228INSERT INTO t1 VALUES (1,1), (2,1), (3,1), (4,1), (5,1), (6,1); 3229EXPLAIN SELECT * FROM t1 WHERE a IN (1, 2); 3230id select_type table partitions type possible_keys key key_len ref rows filtered Extra 32311 SIMPLE t1 NULL range PRIMARY PRIMARY 4 NULL 2 100.00 Using where 3232Warnings: 3233Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` in (1,2)) 3234EXPLAIN SELECT * FROM t1 WHERE a IN (1, "2"); 3235id select_type table partitions type possible_keys key key_len ref rows filtered Extra 32361 SIMPLE t1 NULL range PRIMARY PRIMARY 4 NULL 2 100.00 Using where 3237Warnings: 3238Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` in (1,'2')) 3239SELECT * FROM t1 WHERE a IN (1, 2); 3240a b 32411 1 32422 1 3243SELECT * FROM t1 WHERE a IN (1, "2"); 3244a b 32451 1 32462 1 3247DROP TABLE t1; 3248# 3249# Bug#18715670 3250# CRASH IN DECIMAL_ACTUAL_FRACTION COMPARING DECIMAL TO NULLS 3251# 3252CREATE TABLE t1(n DECIMAL(39,19) NOT NULL, KEY(n)) engine=innodb; 3253INSERT INTO t1 SET n=0; 3254SELECT 1 FROM t1 WHERE n NOT IN(NULL, NULL); 32551 3256DROP TABLE t1; 3257# 3258# Bug#18759597 MISSING ROWS ON WHERE .. 3259# IN QUERY WITH VARIABLES AND CONCAT 3260# 3261CREATE TABLE t1 ( 3262col_varchar_key varchar(2), 3263KEY col_varchar_key (col_varchar_key) 3264) ENGINE=InnoDB; 3265INSERT INTO t1 VALUES ('n'),('xm'); 3266SET @var1 = 't', @var2 = 'him', 3267@var3 = 'n', @var4 = 'n', 3268@var5 = 'n', @var6 = 'g', 3269@var7 = 'b', @var8 = 'o', 3270@var9 = 'm', @var10 = 'xm', @var11 = 'u' 3271; 3272SELECT col_varchar_key AS field1 3273FROM t1 3274WHERE ( col_varchar_key, col_varchar_key ) IN ( 3275('m', @var1 ), 3276('n', @var3 ), 3277('a', @var5 ), 3278('l', @var7 ), 3279(CONCAT('x', @var9 ), @var10 ) 3280); 3281field1 3282n 3283xm 3284DROP TABLE t1; 3285# 3286# Bug#18535226 DEBUG CRASH ON QUICK_RANGE_SELECT::RESET 3287# 3288SET @old_tmp_table_size=@@tmp_table_size; 3289SET tmp_table_size=1024; 3290CREATE TABLE t1 ( 3291pk INT NOT NULL, 3292col_int_key INT, 3293col_date_key date, 3294col_date_nokey date, 3295col_time_key time, 3296col_time_nokey time, 3297col_datetime_key datetime, 3298col_datetime_nokey datetime, 3299col_varchar_key varchar(1), 3300col_varchar_nokey varchar(1), 3301PRIMARY KEY (pk), 3302KEY col_int_key (col_int_key) 3303) ENGINE=MyISAM; 3304INSERT INTO t1 VALUES 3305(5,0,'2001-05-06','2001-05-06','16:21:18','16:21:18','2001-11-08 21:02:12', 3306'2001-11-08 21:02:12','x','x'),(6,7,'2006-03-03','2006-03-03','18:56:33', 3307'18:56:33','2003-04-01 00:00:00','2003-04-01 00:00:00','i','i'), 3308(7,7,'2007-12-28','2007-12-28',NULL,NULL,'1900-01-01 00:00:00', 3309'1900-01-01 00:00:00','e','e'),(8,1,'2004-10-20','2004-10-20','09:29:08', 3310'09:29:08','2007-07-12 00:00:00','2007-07-12 00:00:00','p','p'), 3311(9,7,'2008-04-09','2008-04-09','19:11:10','19:11:10', 3312'2005-04-04 01:21:01','2005-04-04 01:21:01','s','s'), 3313(10,1,'2005-12-25','2005-12-25','11:57:26','11:57:26', 3314'1900-01-01 00:00:00','1900-01-01 00:00:00','j','j'); 3315SELECT alias1.col_int_key 3316FROM 3317( SELECT SQ1_alias1.* FROM t1 AS SQ1_alias1, t1 AS SQ1_alias2 ) AS alias1, 3318(SELECT 7 AS col_int_nokey) AS alias2 3319WHERE 3320alias2.col_int_nokey = alias1.pk 3321AND alias1.col_varchar_nokey < alias1.col_varchar_key 3322ORDER BY alias1.col_varchar_key; 3323col_int_key 3324DROP TABLE t1; 3325SET tmp_table_size=@old_tmp_table_size; 3326SET sql_mode = default; 3327# 3328# Bug#19585938 Crash in get_full_func_mm_tree with null 3329# item_field->table_ref 3330# 3331CREATE TABLE t1(id INTEGER, col1 INTEGER, col2 INTEGER, PRIMARY KEY(id)); 3332INSERT INTO t1 VALUES (1,2,3), (3,2,1); 3333SELECT (SELECT 1 3334FROM t1 3335WHERE SUM(1) < id 3336) AS c 3337FROM t1 3338GROUP BY col1; 3339c 33401 3341SELECT (SELECT 1 3342FROM t1 3343WHERE id > SUM(1) 3344) AS c 3345FROM t1 3346GROUP BY col1; 3347c 33481 3349SELECT (SELECT 1 3350FROM t1 3351WHERE SUM(1) BETWEEN id AND id+1 3352) AS c 3353FROM t1 3354GROUP BY col1; 3355c 33561 3357SELECT (SELECT 1 3358FROM t1 3359WHERE id BETWEEN SUM(1) AND SUM(5) 3360) AS c 3361FROM t1 3362GROUP BY col1; 3363c 33641 3365SELECT (SELECT 1 3366FROM t1 3367WHERE SUM(1) BETWEEN COUNT(*) AND id 3368) AS c 3369FROM t1 3370GROUP BY col1; 3371c 33721 3373DROP TABLE t1; 3374# 3375# Bug#21415791 VALGRIND ERROR (CONDITIONAL JUMP) AT KEY_AND 3376# (RANGE_OPT_PARAM*, SEL_ARG*, SEL_AR 3377# 3378CREATE TABLE t1 ( 3379col_varchar_10 VARCHAR(10), 3380pk INTEGER NOT NULL, 3381col_int_key INTEGER, 3382PRIMARY KEY (pk), 3383KEY col_int_key (col_int_key), 3384KEY col_varchar_10 (col_varchar_10) 3385) ENGINE=InnoDB; 3386INSERT INTO t1 ( 3387pk, col_varchar_10, col_int_key) VALUES 3388(1, 'ttttt', 0), (2, 'zzzzz', 0), (3, 'ggggg', 0), 3389(4, 'hhhhh', 0), (5, 'kkkkk', 0), (6, 'lllll', 0); 3390CREATE TABLE t2 ( 3391pk INTEGER NOT NULL, 3392col_varchar_10 VARCHAR(10), 3393PRIMARY KEY (pk), 3394KEY col_varchar_10 (col_varchar_10) 3395) ENGINE=InnoDB; 3396INSERT INTO t2 ( 3397pk, col_varchar_10) VALUES 3398(1, '00000'), (2, '00000'), (3, '44444'), (4, '00000'), 3399(5, NULL), (6, NULL), (7, NULL); 3400SELECT COUNT(t1.col_int_key) 3401FROM t2 RIGHT OUTER JOIN t1 ON t2.col_varchar_10 <= t1.col_varchar_10 3402WHERE t2.pk <> 4 OR t2.pk != t1.col_int_key AND t2.pk <> 1000; 3403COUNT(t1.col_int_key) 340424 3405DROP TABLE t1, t2; 3406# 3407# Bug #20229614: OR CONDITIONS ON MULTI-COLUMN INDEX MAY NOT USE ALL 3408# INDEX COLUMNS TO FILTER ROWS 3409# 3410CREATE TABLE t1 ( 3411c1 INT, 3412c2 INT, 3413c3 INT, 3414PRIMARY KEY(c1, c2, c3) 3415) ENGINE=INNODB; 3416INSERT INTO t1 VALUES (1, 1, 1), (1, 1, 2), (1, 1, 3), 3417(1, 1, 4), (1, 1, 5); 3418INSERT INTO t1 SELECT c1, 2, c3 FROM t1; 3419INSERT INTO t1 SELECT c1, 3, c3 FROM t1 WHERE c2 = 1; 3420ANALYZE TABLE t1; 3421Table Op Msg_type Msg_text 3422test.t1 analyze status OK 3423SELECT COUNT(*) FROM t1; 3424COUNT(*) 342515 3426EXPLAIN SELECT c1, c2, c3 3427FROM t1 3428WHERE (c1 = 1 AND c2 = 1 AND c3 = 1) OR 3429(c1 = 1 AND c2 = 2 AND c3 = 2) OR 3430(c1 = 1 AND c2 = 2 AND c3 = 3); 3431id select_type table partitions type possible_keys key key_len ref rows filtered Extra 34321 SIMPLE t1 NULL range PRIMARY PRIMARY 12 NULL 3 100.00 Using where; Using index 3433Warnings: 3434Note 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))) 3435DROP TABLE t1; 3436# 3437# Bug#21139683: ASSERTION FAILED: TYPE_ARG == MAYBE_KEY || 3438# TYPE_ARG == IMPOSSIBLE 3439# 3440CREATE TABLE t1 ( 3441a BLOB, 3442PRIMARY KEY(a(1)), 3443KEY(a(1)) 3444) ENGINE=INNODB; 3445SELECT 1 FROM t1 WHERE a <> 'a' OR a <> ""; 34461 3447DROP TABLE t1; 3448# 3449# Bug#19333852: RESULT DIFF IN QUERY HAVING DISTINCT 3450# WITH GROUP BY 3451# 3452CREATE TABLE t1 ( 3453v1 VARCHAR(20) CHARACTER SET utf8 NOT NULL, 3454pk INTEGER NOT NULL, 3455PRIMARY KEY (pk), 3456KEY v1_key (v1(10)) 3457) ENGINE=InnoDB; 3458INSERT INTO t1 VALUES ('ABCDE',19), ('JLVGO',14); 3459EXPLAIN SELECT * FROM t1 WHERE t1.v1 < CHAR(128); 3460id select_type table partitions type possible_keys key key_len ref rows filtered Extra 34611 SIMPLE t1 NULL ALL v1_key NULL NULL NULL 2 50.00 Using where 3462Warnings: 3463Warning 1366 Incorrect string value: '\x80' for column 'v1' at row 1 3464Note 1003 /* select#1 */ select `test`.`t1`.`v1` AS `v1`,`test`.`t1`.`pk` AS `pk` from `test`.`t1` where (`test`.`t1`.`v1` < <cache>(char(128))) 3465EXPLAIN SELECT * FROM t1 WHERE t1.v1 = CHAR(128); 3466id select_type table partitions type possible_keys key key_len ref rows filtered Extra 34671 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table 3468Warnings: 3469Warning 1366 Incorrect string value: '\x80' for column 'v1' at row 1 3470Note 1003 /* select#1 */ select `test`.`t1`.`v1` AS `v1`,`test`.`t1`.`pk` AS `pk` from `test`.`t1` where (`test`.`t1`.`v1` = char(128)) 3471EXPLAIN SELECT * FROM t1 WHERE t1.v1 BETWEEN 'f' AND CHAR(128); 3472id select_type table partitions type possible_keys key key_len ref rows filtered Extra 34731 SIMPLE t1 NULL range v1_key v1_key 32 NULL 1 100.00 Using where 3474Warnings: 3475Warning 1366 Incorrect string value: '\x80' for column 'v1' at row 1 3476Note 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))) 3477SELECT * FROM t1 WHERE t1.v1 < CHAR(128); 3478v1 pk 3479JLVGO 14 3480ABCDE 19 3481Warnings: 3482Warning 1366 Incorrect string value: '\x80' for column 'v1' at row 1 3483SELECT * FROM t1 WHERE t1.v1 = CHAR(128); 3484v1 pk 3485Warnings: 3486Warning 1366 Incorrect string value: '\x80' for column 'v1' at row 1 3487SELECT * FROM t1 WHERE t1.v1 BETWEEN 'f' AND CHAR(128); 3488v1 pk 3489JLVGO 14 3490Warnings: 3491Warning 1366 Incorrect string value: '\x80' for column 'v1' at row 1 3492DROP TABLE t1; 3493# 3494# Bug #21761867: ASSERTION `TYPE_ARG == MAYBE_KEY || 3495# TYPE_ARG == IMPOSSIBLE' FAILED. 3496# 3497CREATE TABLE t1 ( 3498c10 INT NOT NULL, 3499c12 INT NOT NULL, 3500c18 INT, 3501PRIMARY KEY (c10,c12), 3502UNIQUE KEY key_c12(c12), 3503KEY key_c18(c18)); 3504INSERT INTO t1 VALUES(11,5,0), (12,6,1), (13,7,2), (14,8,3), (15,9,4); 3505CREATE TABLE t2 ( 3506c10 INT NOT NULL, 3507c12 INT NOT NULL, 3508PRIMARY KEY(c10,c12)); 3509CREATE TABLE t3 (c10 INT NOT NULL); 3510SELECT t2.c10 3511FROM t1 3512RIGHT JOIN t3 3513LEFT JOIN t2 3514ON t3.c10 = t2.c10 3515ON t1.c12 > t2.c12 3516WHERE 3517t1.c10 <= 25 3518AND 3519t1.c18 IS NOT NULL 3520OR 3521t1.c10 > 5 3522AND 3523t1.c18 IN (15,16,18); 3524c10 3525DROP TABLE t1, t2, t3; 3526# 3527# Bug #21318711: WRONG RESULTS FOR TRUNCATED COLUMN AND AGGREGATION 3528# 3529CREATE TABLE t1 ( 3530col1 VARCHAR(5), 3531col2 INT NOT NULL, 3532PRIMARY KEY (col1, col2) 3533) ENGINE=InnoDB; 3534INSERT INTO t1 VALUES ('abcde', 10); 3535EXPLAIN SELECT MAX(col2) FROM t1 WHERE col1 = 'abcdeaa'; 3536id select_type table partitions type possible_keys key key_len ref rows filtered Extra 35371 SIMPLE t1 NULL ref PRIMARY PRIMARY 7 const 1 100.00 Using where; Using index 3538Warnings: 3539Note 1003 /* select#1 */ select max(`test`.`t1`.`col2`) AS `MAX(col2)` from `test`.`t1` where (`test`.`t1`.`col1` = 'abcdeaa') 3540EXPLAIN SELECT MAX(col2) FROM t1 WHERE col1 = 'abcde '; 3541id select_type table partitions type possible_keys key key_len ref rows filtered Extra 35421 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 3543Warnings: 3544Note 1003 /* select#1 */ select max(`test`.`t1`.`col2`) AS `MAX(col2)` from `test`.`t1` where multiple equal('abcde ', `test`.`t1`.`col1`) 3545SELECT MAX(col2) FROM t1 WHERE col1 = 'abcdeaa'; 3546MAX(col2) 3547NULL 3548SELECT MAX(col2) FROM t1 WHERE col1 = 'abcde '; 3549MAX(col2) 355010 3551DROP TABLE t1; 3552# 3553# Bug# 22283790: RANGE OPTIMIZER UTILIZES TOO MUCH MEMORY WITH 3554# MANY OR CONDITIONS 3555# 3556CREATE TABLE t1 ( 3557f1 INTEGER, 3558KEY (f1) 3559); 3560INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15); 3561SET @orig_range_optimizer_max_mem_size= @@range_optimizer_max_mem_size; 3562SET range_optimizer_max_mem_size= 5000; 3563EXPLAIN SELECT * FROM t1 WHERE f1=1 OR f1=2 OR f1=3 OR f1=4 OR f1=5 3564OR f1=6 OR f1=7 OR f1=8 OR f1=9 OR f1=10 OR f1=11; 3565id select_type table partitions type possible_keys key key_len ref rows filtered Extra 35661 SIMPLE t1 NULL range f1 f1 5 NULL 11 100.00 Using where; Using index 3567Warnings: 3568Note 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)) 3569SET range_optimizer_max_mem_size= @orig_range_optimizer_max_mem_size; 3570DROP TABLE t1; 3571# 3572# Bug #23259872: OPTIMIZER CHOOSES TO USE NON PRIMARY 3573# INDEX, EVEN THOUGH COST IS HIGHER 3574# 3575CREATE TABLE `giant_table` ( 3576`id` int(11) NOT NULL AUTO_INCREMENT, 3577`one_id` int(11) NOT NULL, 3578`other_id` bigint(20) NOT NULL DEFAULT '0', 3579`some_other_id` int(11) DEFAULT 0 NOT NULL, 3580`something` double NOT NULL DEFAULT '0', 3581`comment` text COLLATE utf8_unicode_ci, 3582`flags` int(11) NOT NULL DEFAULT '0', 3583`time_created` int(11) NOT NULL DEFAULT '0', 3584PRIMARY KEY (`id`), 3585KEY `time_created` (`time_created`), 3586KEY `some_other_id` (`some_other_id`), 3587KEY `one_other_idx` (`one_id`,`other_id`), 3588KEY `other_id` (`other_id`,`time_created`) 3589) ENGINE=InnoDB AUTO_INCREMENT=101651329 3590DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ; 3591CREATE TABLE t1 (c1 INT); 3592INSERT INTO t1 VALUES (66136540), (68983250), (89627210), (77869520), 3593(82543190), (67538270), (77282760), (77908170), 3594(70923370), (68066360); 3595CREATE PROCEDURE p() 3596BEGIN 3597SET @x = 1; 3598REPEAT 3599INSERT INTO giant_table(id,one_id) 3600SELECT c1 + @x, 0 3601FROM t1 3602WHERE c1 IN (66136540, 68985250, 89627210, 77869520 , 82543190, 67538270, 360377282760, 77908170, 70923370, 68066360); 3604SET @x = @x + 1; 3605UNTIL @x > 30 END REPEAT; 3606END $ 3607CALL p(); 3608SELECT count(*) FROM giant_table; 3609count(*) 3610270 3611INSERT INTO giant_table (id,one_id) VALUES (66136539, 0), (68983258,1), 3612(89628210,1), (77869520,2); 3613INSERT INTO giant_table (id,one_id, some_other_id) VALUES(84673401, 0, 1), 3614(61069031, 1, 1); 3615EXPLAIN SELECT id, something, comment, time_created, one_id, other_id, 3616some_other_id, flags 3617FROM giant_table 3618WHERE id IN (66136539, 68983258, 89628210, 77869520, 82543198, 67538272, 361984673401, 61069031, 68214385, 77282865, 76991297, 64569216, 362089481638, 74534074, 70396537, 80076375, 63308530, 77908270, 362170923271, 68066180) 3622AND (giant_table.flags & 0x01) = 0 AND giant_table.some_other_id = 0; 3623id select_type table partitions type possible_keys key key_len ref rows filtered Extra 36241 SIMPLE giant_table NULL range PRIMARY,some_other_id some_other_id 8 NULL 20 100.00 Using where 3625Warnings: 3626Note 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)) 3627DROP PROCEDURE p; 3628DROP TABLE giant_table, t1; 3629# 3630# Bug #26727773: OPTIMIZER CHOSES COMPOSITE INDEX FOR REF OVER RANGE 3631# 3632CREATE TABLE transactions ( 3633app_trans_id INT DEFAULT NULL, 3634id INT NOT NULL, 3635tbl INT NOT NULL DEFAULT 1, 3636created TIMESTAMP NOT NULL DEFAULT '2017-01-01 01:01:01', 3637trans_type INT NOT NULL, 3638description BLOB, 3639source_lvl1 INT DEFAULT NULL, 3640source_lvl2 INT DEFAULT NULL, 3641KEY tbl_id_idx (tbl,id), 3642KEY created_idx (created), 3643KEY trans_type_created_idx (trans_type,created), 3644KEY app_trans_id_idx (app_trans_id) 3645) ENGINE=INNODB ; 3646CREATE TABLE t1 (c1 INT); 3647INSERT INTO t1 VALUES (1), (1000), (2000), (3000), (4000), (5000), (6000), 3648(7000), (8000), (9000); 3649CREATE PROCEDURE p() 3650BEGIN 3651SET @x = 1; 3652REPEAT 3653INSERT IGNORE INTO transactions(id,trans_type, description) 3654SELECT c1 + @x, @x , 'abcd' 3655 FROM t1; 3656SET @x = @x + 1; 3657UNTIL @x > 300 END REPEAT; 3658END $ 3659CALL p(); 3660SELECT count(*) FROM transactions; 3661count(*) 36623000 3663INSERT IGNORE INTO transactions(id,trans_type, description, created) 3664SELECT 3, 3 , 'abcd', '2018-01-01 01:01:01' 3665 FROM dual; 3666EXPLAIN SELECT '2017-10-23 01:01:01', HOUR(created), source_lvl1, source_lvl2, 3667COUNT(DISTINCT(app_trans_id)) 3668FROM transactions 3669WHERE created > '2017-10-23 01:01:01' AND tbl = 1 AND trans_type in (3) 3670GROUP BY '2017-10-23 01:01:01', HOUR(created), source_lvl1, source_lvl2; 3671id select_type table partitions type possible_keys key key_len ref rows filtered Extra 36721 SIMPLE transactions NULL range tbl_id_idx,created_idx,trans_type_created_idx trans_type_created_idx 8 NULL 1 100.00 Using where; Using filesort 3673Warnings: 3674Note 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` 3675SET optimizer_trace="enabled=on"; 3676SELECT '2017-10-23 01:01:01', HOUR(created), source_lvl1, source_lvl2, 3677COUNT(DISTINCT(app_trans_id)) 3678FROM transactions 3679WHERE created > '2017-10-23 01:01:01' AND tbl = 1 AND trans_type in (3) 3680GROUP BY '2017-10-23 01:01:01', HOUR(created), source_lvl1, source_lvl2; 36812017-10-23 01:01:01 HOUR(created) source_lvl1 source_lvl2 COUNT(DISTINCT(app_trans_id)) 36822017-10-23 01:01:01 1 NULL NULL 0 3683SELECT TRACE INTO @trace FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; 3684SELECT @trace RLIKE "uses_more_keyparts"; 3685@trace RLIKE "uses_more_keyparts" 36861 3687SET optimizer_trace="enabled=off"; 3688DROP PROCEDURE p; 3689DROP TABLE t1, transactions; 3690# 3691# Bug #28086754: OPTIMIZER SKIP THE RANG SCAN ON SECOND COLUMN IN A 3692# COMPOSITE INDEX 3693# 3694CREATE TABLE test_ref ( 3695a INT PRIMARY KEY, 3696b VARCHAR(20), 3697c VARCHAR(20) DEFAULT NULL, 3698d VARCHAR(3) DEFAULT NULL, 3699id INT DEFAULT NULL, 3700KEY idx1 (id, c), 3701KEY idx2 (id, d)) ENGINE=INNODB ; 3702ANALYZE TABLE test_ref; 3703Table Op Msg_type Msg_text 3704test.test_ref analyze status OK 3705EXPLAIN SELECT * 3706FROM test_ref 3707WHERE id=3 AND c LIKE 'gh%' 3708ORDER BY c 3709LIMIT 1; 3710id select_type table partitions type possible_keys key key_len ref rows filtered Extra 37111 SIMPLE test_ref NULL range idx1,idx2 idx1 28 NULL 27 100.00 Using where 3712Warnings: 3713Note 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 3714SELECT * 3715FROM test_ref 3716WHERE id=3 AND c LIKE 'gh%' 3717ORDER BY c 3718LIMIT 1; 3719a b c d id 372034876 D003 gheennse S 3 3721EXPLAIN SELECT * 3722FROM test_ref 3723WHERE id=3 AND c LIKE 'gh%' 3724ORDER BY c DESC 3725LIMIT 1; 3726id select_type table partitions type possible_keys key key_len ref rows filtered Extra 37271 SIMPLE test_ref NULL range idx1,idx2 idx1 28 NULL 27 100.00 Using where 3728Warnings: 3729Note 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 3730SELECT * 3731FROM test_ref 3732WHERE id=3 AND c LIKE 'gh%' 3733ORDER BY c DESC 3734LIMIT 1; 3735a b c d id 37361770649 D003 gheennse S 3 3737DROP TABLE test_ref; 3738set optimizer_switch=default; 3739