1set optimizer_switch='index_condition_pushdown=on'; 2drop table if exists t1,t2; 3create table t1 (a int, b int not null,unique key (a,b),index(b)) engine=myisam; 4insert ignore into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(null,7),(9,9),(8,8),(7,7),(null,9),(null,9),(6,6); 5explain select * from t1 where a is null; 6id select_type table type possible_keys key key_len ref rows Extra 71 SIMPLE t1 ref a a 5 const 3 Using where; Using index 8explain select * from t1 where a is null and b = 2; 9id select_type table type possible_keys key key_len ref rows Extra 101 SIMPLE t1 ref a,b a 9 const,const 1 Using where; Using index 11explain select * from t1 where a is null and b = 7; 12id select_type table type possible_keys key key_len ref rows Extra 131 SIMPLE t1 ref a,b a 9 const,const 1 Using where; Using index 14explain select * from t1 where a=2 and b = 2; 15id select_type table type possible_keys key key_len ref rows Extra 161 SIMPLE t1 const a,b a 9 const,const 1 Using index 17explain select * from t1 where a<=>b limit 2; 18id select_type table type possible_keys key key_len ref rows Extra 191 SIMPLE t1 index NULL a 9 NULL 12 Using where; Using index 20explain select * from t1 where (a is null or a > 0 and a < 3) and b < 5 limit 3; 21id select_type table type possible_keys key key_len ref rows Extra 221 SIMPLE t1 range a,b a 9 NULL 3 Using where; Using index 23explain select * from t1 where (a is null or a = 7) and b=7; 24id select_type table type possible_keys key key_len ref rows Extra 251 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using where; Using index 26explain select * from t1 where (a is null or a = 7) and b=7 order by a; 27id select_type table type possible_keys key key_len ref rows Extra 281 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using where; Using index; Using filesort 29explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2; 30id select_type table type possible_keys key key_len ref rows Extra 311 SIMPLE t1 ref a,b a 5 const 3 Using where; Using index 32explain select * from t1 where a is null and b=9 or a is null and b=7 limit 3; 33id select_type table type possible_keys key key_len ref rows Extra 341 SIMPLE t1 range a,b a 9 NULL 3 Using where; Using index 35explain select * from t1 where a > 1 and a < 3 limit 1; 36id select_type table type possible_keys key key_len ref rows Extra 371 SIMPLE t1 range a a 5 NULL 1 Using where; Using index 38explain select * from t1 where a > 8 and a < 9; 39id select_type table type possible_keys key key_len ref rows Extra 401 SIMPLE t1 range a a 5 NULL 1 Using where; Using index 41select * from t1 where a is null; 42a b 43NULL 7 44NULL 9 45NULL 9 46select * from t1 where a is null and b = 7; 47a b 48NULL 7 49select * from t1 where a<=>b limit 2; 50a b 511 1 522 2 53select * from t1 where (a is null or a > 0 and a < 3) and b < 5 limit 3; 54a b 551 1 562 2 57select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3; 58a b 59NULL 9 60NULL 9 61select * from t1 where (a is null or a = 7) and b=7; 62a b 637 7 64NULL 7 65select * from t1 where a is null and b=9 or a is null and b=7 limit 3; 66a b 67NULL 7 68NULL 9 69NULL 9 70select * from t1 where a > 1 and a < 3 limit 1; 71a b 722 2 73select * from t1 where a > 8 and a < 9; 74a b 75create table t2 like t1; 76insert into t2 select * from t1; 77alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10)); 78explain select * from t1 where a is null and b = 2; 79id select_type table type possible_keys key key_len ref rows Extra 801 SIMPLE t1 ref a,b a 5 const 3 Using where 81explain select * from t1 where a is null and b = 2 and c=0; 82id select_type table type possible_keys key key_len ref rows Extra 831 SIMPLE t1 ref a,b a 5 const 3 Using where 84explain select * from t1 where a is null and b = 7 and c=0; 85id select_type table type possible_keys key key_len ref rows Extra 861 SIMPLE t1 ref a,b a 5 const 3 Using where 87explain select * from t1 where a=2 and b = 2; 88id select_type table type possible_keys key key_len ref rows Extra 891 SIMPLE t1 ref a,b a 5 const 1 Using where 90explain select * from t1 where a<=>b limit 2; 91id select_type table type possible_keys key key_len ref rows Extra 921 SIMPLE t1 ALL NULL NULL NULL NULL 12 Using where 93explain select * from t1 where (a is null or a > 0 and a < 3) and b < 5 and c=0 limit 3; 94id select_type table type possible_keys key key_len ref rows Extra 951 SIMPLE t1 range a,b a 5 NULL 5 Using where 96explain select * from t1 where (a is null or a = 7) and b=7 and c=0; 97id select_type table type possible_keys key key_len ref rows Extra 981 SIMPLE t1 ref_or_null a,b a 5 const 4 Using where 99explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2; 100id select_type table type possible_keys key key_len ref rows Extra 1011 SIMPLE t1 ref a,b a 5 const 3 Using where 102explain select * from t1 where a is null and b=9 or a is null and b=7 limit 3; 103id select_type table type possible_keys key key_len ref rows Extra 1041 SIMPLE t1 ref a,b a 5 const 3 Using where 105explain select * from t1 where a > 1 and a < 3 limit 1; 106id select_type table type possible_keys key key_len ref rows Extra 1071 SIMPLE t1 range a a 5 NULL 1 Using where 108explain select * from t1 where a is null and b=7 or a > 1 and a < 3 limit 1; 109id select_type table type possible_keys key key_len ref rows Extra 1101 SIMPLE t1 range a,b a 5 NULL 4 Using where 111explain select * from t1 where a > 8 and a < 9; 112id select_type table type possible_keys key key_len ref rows Extra 1131 SIMPLE t1 range a a 5 NULL 1 Using where 114explain select * from t1 where b like "6%"; 115id select_type table type possible_keys key key_len ref rows Extra 1161 SIMPLE t1 range b b 12 NULL 1 Using where 117select * from t1 where a is null; 118a b c 119NULL 7 0 120NULL 9 0 121NULL 9 0 122select * from t1 where a is null and b = 7 and c=0; 123a b c 124NULL 7 0 125select * from t1 where a<=>b limit 2; 126a b c 1271 1 0 1282 2 0 129select * from t1 where (a is null or a > 0 and a < 3) and b < 5 limit 3; 130a b c 1311 1 0 1322 2 0 133select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3; 134a b c 135NULL 9 0 136NULL 9 0 137select * from t1 where (a is null or a = 7) and b=7 and c=0; 138a b c 1397 7 0 140NULL 7 0 141select * from t1 where a is null and b=9 or a is null and b=7 limit 3; 142a b c 143NULL 7 0 144NULL 9 0 145NULL 9 0 146select * from t1 where b like "6%"; 147a b c 1486 6 0 149drop table t1; 150rename table t2 to t1; 151alter table t1 modify b int null; 152insert into t1 values (7,null), (8,null), (8,7); 153explain select * from t1 where a = 7 and (b=7 or b is null); 154id select_type table type possible_keys key key_len ref rows Extra 1551 SIMPLE t1 ref_or_null a,b a 10 const,const 2 Using where; Using index 156select * from t1 where a = 7 and (b=7 or b is null); 157a b 1587 7 1597 NULL 160explain select * from t1 where (a = 7 or a is null) and (b=7 or b is null); 161id select_type table type possible_keys key key_len ref rows Extra 1621 SIMPLE t1 ref_or_null a,b a 5 const 4 Using where; Using index 163select * from t1 where (a = 7 or a is null) and (b=7 or b is null); 164a b 1657 NULL 1667 7 167NULL 7 168explain select * from t1 where (a = 7 or a is null) and (a = 7 or a is null); 169id select_type table type possible_keys key key_len ref rows Extra 1701 SIMPLE t1 ref_or_null a a 5 const 5 Using where; Using index 171select * from t1 where (a = 7 or a is null) and (a = 7 or a is null); 172a b 1737 NULL 1747 7 175NULL 7 176NULL 9 177NULL 9 178create table t2 (a int); 179insert into t2 values (7),(8); 180explain select * from t2 straight_join t1 where t1.a=t2.a and b is null; 181id select_type table type possible_keys key key_len ref rows Extra 1821 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where 1831 SIMPLE t1 ref a,b a 10 test.t2.a,const 2 Using where; Using index 184drop index b on t1; 185explain select * from t2,t1 where t1.a=t2.a and b is null; 186id select_type table type possible_keys key key_len ref rows Extra 1871 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where 1881 SIMPLE t1 ref a a 10 test.t2.a,const 2 Using where; Using index 189select * from t2,t1 where t1.a=t2.a and b is null; 190a a b 1917 7 NULL 1928 8 NULL 193explain select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null); 194id select_type table type possible_keys key key_len ref rows Extra 1951 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where 1961 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using where; Using index 197select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null); 198a a b 1997 7 7 2007 7 NULL 2018 8 7 2028 8 NULL 203explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7; 204id select_type table type possible_keys key key_len ref rows Extra 2051 SIMPLE t2 ALL NULL NULL NULL NULL 2 NULL 2061 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using where; Using index 207select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7; 208a a b 2097 7 7 2107 NULL 7 2118 8 7 2128 NULL 7 213explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and (b= 7 or b is null); 214id select_type table type possible_keys key key_len ref rows Extra 2151 SIMPLE t2 ALL NULL NULL NULL NULL 2 NULL 2161 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using where; Using index 217select * from t2,t1 where (t1.a=t2.a or t1.a is null) and (b= 7 or b is null); 218a a b 2197 7 NULL 2207 7 7 2217 NULL 7 2228 8 NULL 2238 8 7 2248 NULL 7 225insert into t2 values (null),(6); 226delete from t1 where a=8; 227explain select * from t2,t1 where t1.a=t2.a or t1.a is null; 228id select_type table type possible_keys key key_len ref rows Extra 2291 SIMPLE t2 ALL NULL NULL NULL NULL 4 NULL 2301 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using where; Using index 231explain select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9); 232id select_type table type possible_keys key key_len ref rows Extra 2331 SIMPLE t2 ALL NULL NULL NULL NULL 4 NULL 2341 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using where; Using index 235select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9); 236a a b 2377 7 NULL 2387 7 7 2397 NULL 7 2408 NULL 7 241NULL NULL 7 242NULL NULL 9 243NULL NULL 9 2446 6 6 2456 NULL 7 246drop table t1,t2; 247CREATE TABLE t1 ( 248id int(10) unsigned NOT NULL auto_increment, 249uniq_id int(10) unsigned default NULL, 250PRIMARY KEY (id), 251UNIQUE KEY idx1 (uniq_id) 252) ENGINE=MyISAM; 253CREATE TABLE t2 ( 254id int(10) unsigned NOT NULL auto_increment, 255uniq_id int(10) unsigned default NULL, 256PRIMARY KEY (id) 257) ENGINE=MyISAM; 258INSERT INTO t1 VALUES (1,NULL),(2,NULL),(3,1),(4,2),(5,NULL),(6,NULL),(7,3),(8,4),(9,NULL),(10,NULL); 259INSERT INTO t2 VALUES (1,NULL),(2,NULL),(3,1),(4,2),(5,NULL),(6,NULL),(7,3),(8,4),(9,NULL),(10,NULL); 260explain select id from t1 where uniq_id is null; 261id select_type table type possible_keys key key_len ref rows Extra 2621 SIMPLE t1 ref idx1 idx1 5 const 5 Using index condition 263explain select id from t1 where uniq_id =1; 264id select_type table type possible_keys key key_len ref rows Extra 2651 SIMPLE t1 const idx1 idx1 5 const 1 NULL 266UPDATE t1 SET id=id+100 where uniq_id is null; 267UPDATE t2 SET id=id+100 where uniq_id is null; 268select id from t1 where uniq_id is null; 269id 270101 271102 272105 273106 274109 275110 276select id from t2 where uniq_id is null; 277id 278101 279102 280105 281106 282109 283110 284DELETE FROM t1 WHERE uniq_id IS NULL; 285DELETE FROM t2 WHERE uniq_id IS NULL; 286SELECT * FROM t1 ORDER BY uniq_id, id; 287id uniq_id 2883 1 2894 2 2907 3 2918 4 292SELECT * FROM t2 ORDER BY uniq_id, id; 293id uniq_id 2943 1 2954 2 2967 3 2978 4 298DROP table t1,t2; 299CREATE TABLE `t1` ( 300`order_id` char(32) NOT NULL default '', 301`product_id` char(32) NOT NULL default '', 302`product_type` int(11) NOT NULL default '0', 303PRIMARY KEY (`order_id`,`product_id`,`product_type`) 304) ENGINE=MyISAM; 305CREATE TABLE `t2` ( 306`order_id` char(32) NOT NULL default '', 307`product_id` char(32) NOT NULL default '', 308`product_type` int(11) NOT NULL default '0', 309PRIMARY KEY (`order_id`,`product_id`,`product_type`) 310) ENGINE=MyISAM; 311INSERT INTO t1 (order_id, product_id, product_type) VALUES 312('3d7ce39b5d4b3e3d22aaafe9b633de51',1206029, 3), 313('3d7ce39b5d4b3e3d22aaafe9b633de51',5880836, 3), 314('9d9aad7764b5b2c53004348ef8d34500',2315652, 3); 315INSERT INTO t2 (order_id, product_id, product_type) VALUES 316('9d9aad7764b5b2c53004348ef8d34500',2315652, 3); 317select t1.* from t1 318left join t2 using(order_id, product_id, product_type) 319where t2.order_id=NULL; 320order_id product_id product_type 321select t1.* from t1 322left join t2 using(order_id, product_id, product_type) 323where t2.order_id is NULL; 324order_id product_id product_type 3253d7ce39b5d4b3e3d22aaafe9b633de51 1206029 3 3263d7ce39b5d4b3e3d22aaafe9b633de51 5880836 3 327drop table t1,t2; 328create table t1 (id int); 329insert into t1 values (null), (0); 330create table t2 (id int); 331insert into t2 values (null); 332select * from t1, t2 where t1.id = t2.id; 333id id 334alter table t1 add key id (id); 335select * from t1, t2 where t1.id = t2.id; 336id id 337drop table t1,t2; 338create table t1 ( 339id integer, 340id2 integer not null, 341index (id), 342index (id2) 343); 344insert into t1 values(null,null),(1,1); 345Warnings: 346Warning 1048 Column 'id2' cannot be null 347select * from t1; 348id id2 349NULL 0 3501 1 351select * from t1 where id <=> null; 352id id2 353NULL 0 354select * from t1 where id <=> null or id > 0; 355id id2 356NULL 0 3571 1 358select * from t1 where id is null or id > 0; 359id id2 360NULL 0 3611 1 362select * from t1 where id2 <=> null or id2 > 0; 363id id2 3641 1 365select * from t1 where id2 is null or id2 > 0; 366id id2 3671 1 368delete from t1 where id <=> NULL; 369select * from t1; 370id id2 3711 1 372drop table t1; 373CREATE TABLE t1 (a int); 374CREATE TABLE t2 (a int, b int, INDEX idx(a)); 375CREATE TABLE t3 (b int, INDEX idx(b)); 376CREATE TABLE t4 (b int, INDEX idx(b)); 377INSERT INTO t1 VALUES (1), (2), (3), (4); 378INSERT INTO t2 VALUES (1, 1), (3, 1); 379INSERT INTO t3 VALUES 380(NULL), (NULL), (NULL), (NULL), (NULL), 381(NULL), (NULL), (NULL), (NULL), (NULL); 382INSERT INTO t4 SELECT * FROM t3; 383INSERT INTO t3 SELECT * FROM t4; 384INSERT INTO t4 SELECT * FROM t3; 385INSERT INTO t3 SELECT * FROM t4; 386INSERT INTO t4 SELECT * FROM t3; 387INSERT INTO t3 SELECT * FROM t4; 388INSERT INTO t4 SELECT * FROM t3; 389INSERT INTO t3 SELECT * FROM t4; 390INSERT INTO t4 SELECT * FROM t3; 391INSERT INTO t3 SELECT * FROM t4; 392INSERT INTO t4 SELECT * FROM t3; 393INSERT INTO t3 SELECT * FROM t4; 394INSERT INTO t4 SELECT * FROM t3; 395INSERT INTO t3 SELECT * FROM t4; 396INSERT INTO t4 SELECT * FROM t3; 397INSERT INTO t3 SELECT * FROM t4; 398INSERT INTO t3 VALUES (2), (3); 399ANALYZE table t1, t2, t3; 400Table Op Msg_type Msg_text 401test.t1 analyze status OK 402test.t2 analyze status OK 403test.t3 analyze status OK 404SELECT COUNT(*) FROM t3; 405COUNT(*) 40615972 407EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a 408LEFT JOIN t3 ON t2.b=t3.b; 409id select_type table type possible_keys key key_len ref rows Extra 4101 SIMPLE t1 ALL NULL NULL NULL NULL 4 NULL 4111 SIMPLE t2 ALL idx NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop) 4121 SIMPLE t3 ref idx idx 5 test.t2.b 1 Using index 413FLUSH STATUS ; 414SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a 415LEFT JOIN t3 ON t2.b=t3.b; 416a a b b 4171 1 1 NULL 4183 3 1 NULL 4192 NULL NULL NULL 4204 NULL NULL NULL 421SELECT FOUND_ROWS(); 422FOUND_ROWS() 4234 424SHOW STATUS LIKE "handler_read%"; 425Variable_name Value 426Handler_read_first 0 427Handler_read_key 2 428Handler_read_last 0 429Handler_read_next 0 430Handler_read_prev 0 431Handler_read_rnd 0 432Handler_read_rnd_next 8 433DROP TABLE t1,t2,t3,t4; 434CREATE TABLE t1 ( 435a int(11) default NULL, 436b int(11) default NULL, 437KEY a (a,b) 438); 439INSERT INTO t1 VALUES (0,10),(0,11),(0,12); 440CREATE TABLE t2 ( 441a int(11) default NULL, 442b int(11) default NULL, 443KEY a (a) 444); 445INSERT INTO t2 VALUES (3,NULL),(3,11),(3,12); 446SELECT * FROM t2 inner join t1 WHERE ( t1.a = 0 OR t1.a IS NULL) AND t2.a = 3 AND t2.b = t1.b; 447a b a b 4483 11 0 11 4493 12 0 12 450drop table t1, t2; 451End of 5.0 tests 452# 453# Bug#54608 Query using IN + OR + IS TRUE and IS NULL returns 454# NULL when should be empty 455# 456CREATE TABLE t1 (a INT, KEY (a)); 457INSERT INTO t1 VALUES (1), (2), (NULL); 458explain SELECT a FROM t1 WHERE a IN (42) OR (a IS TRUE AND a IS NULL); 459id select_type table type possible_keys key key_len ref rows Extra 4601 SIMPLE t1 ref_or_null a a 5 const 2 Using where; Using index 461SELECT a FROM t1 WHERE a IN (42) OR (a IS TRUE AND a IS NULL); 462a 463explain SELECT a FROM t1 WHERE a IN (42) OR (a=NULL); 464id select_type table type possible_keys key key_len ref rows Extra 4651 SIMPLE t1 ref_or_null a a 5 const 2 Using where; Using index 466SELECT a FROM t1 WHERE a IN (42) OR (a=NULL); 467a 468drop table t1; 469set optimizer_switch=default; 470