1# 2# Check null keys 3 4--disable_warnings 5drop table if exists t1,t2; 6--enable_warnings 7 8create table t1 (a int, b int not null,unique key (a,b),index(b)) engine=myisam; 9insert 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); 10explain select * from t1 where a is null; 11explain select * from t1 where a is null and b = 2; 12explain select * from t1 where a is null and b = 7; 13explain select * from t1 where a=2 and b = 2; 14explain select * from t1 where a<=>b limit 2; 15explain select * from t1 where (a is null or a > 0 and a < 2) and b < 5 limit 3; 16explain select * from t1 where (a is null or a = 7) and b=7; 17explain select * from t1 where (a is null or a = 7) and b=7 order by a; 18explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2; 19explain select * from t1 where a is null and b=9 or a is null and b=7 limit 3; 20explain select * from t1 where a > 1 and a < 3 limit 1; 21explain select * from t1 where a > 8 and a < 9; 22select * from t1 where a is null; 23select * from t1 where a is null and b = 7; 24select * from t1 where a<=>b limit 2; 25select * from t1 where (a is null or a > 0 and a < 3) and b < 5 limit 3; 26select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3; 27select * from t1 where (a is null or a = 7) and b=7; 28select * from t1 where a is null and b=9 or a is null and b=7 limit 3; 29select * from t1 where a > 1 and a < 3 limit 1; 30select * from t1 where a > 8 and a < 9; 31create table t2 like t1; 32insert into t2 select * from t1; 33alter 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)); 34explain select * from t1 where a is null and b = 2; 35explain select * from t1 where a is null and b = 2 and c=0; 36explain select * from t1 where a is null and b = 7 and c=0; 37explain select * from t1 where a=2 and b = 2; 38explain select * from t1 where a<=>b limit 2; 39explain select * from t1 where (a is null or a > 0 and a < 3) and b < 5 and c=0 limit 3; 40explain select * from t1 where (a is null or a = 7) and b=7 and c=0; 41explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2; 42explain select * from t1 where a is null and b=9 or a is null and b=7 limit 3; 43explain select * from t1 where a > 1 and a < 3 limit 1; 44explain select * from t1 where a is null and b=7 or a > 1 and a < 3 limit 1; 45explain select * from t1 where a > 8 and a < 9; 46explain select * from t1 where b like "6%"; 47select * from t1 where a is null; 48select * from t1 where a is null and b = 7 and c=0; 49select * from t1 where a<=>b limit 2; 50select * from t1 where (a is null or a > 0 and a < 3) and b < 5 limit 3; 51select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3; 52select * from t1 where (a is null or a = 7) and b=7 and c=0; 53select * from t1 where a is null and b=9 or a is null and b=7 limit 3; 54select * from t1 where b like "6%"; 55 56# 57# Test ref_or_null optimization 58# 59drop table t1; 60rename table t2 to t1; 61alter table t1 modify b int null; 62insert into t1 values (7,null), (8,null), (8,7); 63explain select * from t1 where a = 7 and (b=7 or b is null); 64select * from t1 where a = 7 and (b=7 or b is null); 65explain select * from t1 where (a = 7 or a is null) and (b=7 or b is null); 66select * from t1 where (a = 7 or a is null) and (b=7 or b is null); 67explain select * from t1 where (a = 7 or a is null) and (a = 7 or a is null); 68select * from t1 where (a = 7 or a is null) and (a = 7 or a is null); 69create table t2 (a int); 70insert into t2 values (7),(8); 71explain select * from t2 straight_join t1 where t1.a=t2.a and b is null; 72drop index b on t1; 73explain select * from t2,t1 where t1.a=t2.a and b is null; 74select * from t2,t1 where t1.a=t2.a and b is null; 75explain select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null); 76select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null); 77explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7; 78select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7; 79explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and (b= 7 or b is null); 80select * from t2,t1 where (t1.a=t2.a or t1.a is null) and (b= 7 or b is null); 81insert into t2 values (null),(6); 82delete from t1 where a=8; 83explain select * from t2,t1 where t1.a=t2.a or t1.a is null; 84explain select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9); 85select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9); 86drop table t1,t2; 87 88# 89# The following failed for Matt Loschert 90# 91 92CREATE TABLE t1 ( 93 id int(10) unsigned NOT NULL auto_increment, 94 uniq_id int(10) unsigned default NULL, 95 PRIMARY KEY (id), 96 UNIQUE KEY idx1 (uniq_id) 97) ENGINE=MyISAM; 98 99CREATE TABLE t2 ( 100 id int(10) unsigned NOT NULL auto_increment, 101 uniq_id int(10) unsigned default NULL, 102 PRIMARY KEY (id) 103) ENGINE=MyISAM; 104 105INSERT INTO t1 VALUES (1,NULL),(2,NULL),(3,1),(4,2),(5,NULL),(6,NULL),(7,3),(8,4),(9,NULL),(10,NULL); 106INSERT INTO t1 VALUES (11,5),(12,6),(13,7),(14,8),(15,9); 107INSERT INTO t2 VALUES (1,NULL),(2,NULL),(3,1),(4,2),(5,NULL),(6,NULL),(7,3),(8,4),(9,NULL),(10,NULL); 108# 109# Check IS NULL optimization 110# 111explain select id from t1 where uniq_id is null; 112explain select id from t1 where uniq_id =1; 113# 114# Check updates 115# 116UPDATE t1 SET id=id+100 where uniq_id is null; 117UPDATE t2 SET id=id+100 where uniq_id is null; 118select id from t1 where uniq_id is null; 119select id from t2 where uniq_id is null; 120# 121# Delete all records from each table where the uniq_id field is null 122# 123DELETE FROM t1 WHERE uniq_id IS NULL; 124DELETE FROM t2 WHERE uniq_id IS NULL; 125# 126# Select what is left -- notice the difference 127# 128SELECT * FROM t1 ORDER BY uniq_id, id; 129SELECT * FROM t2 ORDER BY uniq_id, id; 130DROP table t1,t2; 131 132# 133# This crashed MySQL 3.23.47 134# 135 136CREATE TABLE `t1` ( 137 `order_id` char(32) NOT NULL default '', 138 `product_id` char(32) NOT NULL default '', 139 `product_type` int(11) NOT NULL default '0', 140 PRIMARY KEY (`order_id`,`product_id`,`product_type`) 141) ENGINE=MyISAM; 142CREATE TABLE `t2` ( 143 `order_id` char(32) NOT NULL default '', 144 `product_id` char(32) NOT NULL default '', 145 `product_type` int(11) NOT NULL default '0', 146 PRIMARY KEY (`order_id`,`product_id`,`product_type`) 147) ENGINE=MyISAM; 148INSERT INTO t1 (order_id, product_id, product_type) VALUES 149('3d7ce39b5d4b3e3d22aaafe9b633de51',1206029, 3), 150('3d7ce39b5d4b3e3d22aaafe9b633de51',5880836, 3), 151('9d9aad7764b5b2c53004348ef8d34500',2315652, 3); 152INSERT INTO t2 (order_id, product_id, product_type) VALUES 153('9d9aad7764b5b2c53004348ef8d34500',2315652, 3); 154 155select t1.* from t1 156left join t2 using(order_id, product_id, product_type) 157where t2.order_id=NULL; 158select t1.* from t1 159left join t2 using(order_id, product_id, product_type) 160where t2.order_id is NULL; 161drop table t1,t2; 162 163# 164# The last select returned wrong results in 3.23.52 165# 166 167create table t1 (id int); 168insert into t1 values (null), (0); 169create table t2 (id int); 170insert into t2 values (null); 171select * from t1, t2 where t1.id = t2.id; 172alter table t1 add key id (id); 173select * from t1, t2 where t1.id = t2.id; 174drop table t1,t2; 175 176# 177# Check bug when doing <=> NULL on an indexed null field 178# 179 180create table t1 ( 181 id integer, 182 id2 integer not null, 183 index (id), 184 index (id2) 185); 186insert ignore into t1 values(null,null),(1,1); 187select * from t1; 188select * from t1 where id <=> null; 189select * from t1 where id <=> null or id > 0; 190select * from t1 where id is null or id > 0; 191select * from t1 where id2 <=> null or id2 > 0; 192select * from t1 where id2 is null or id2 > 0; 193delete from t1 where id <=> NULL; 194select * from t1; 195drop table t1; 196 197# 198# Test for bug #12144: optimizations for key access with null keys 199# used for outer joins 200# 201 202CREATE TABLE t1 (a int); 203CREATE TABLE t2 (a int, b int, INDEX idx(a)); 204CREATE TABLE t3 (b int, INDEX idx(b)); 205CREATE TABLE t4 (b int, INDEX idx(b)); 206INSERT INTO t1 VALUES (1), (2), (3), (4); 207INSERT INTO t2 VALUES (1, 1), (3, 1); 208INSERT INTO t3 VALUES 209 (NULL), (NULL), (NULL), (NULL), (NULL), 210 (NULL), (NULL), (NULL), (NULL), (NULL); 211INSERT INTO t4 SELECT * FROM t3; 212INSERT INTO t3 SELECT * FROM t4; 213INSERT INTO t4 SELECT * FROM t3; 214INSERT INTO t3 SELECT * FROM t4; 215INSERT INTO t4 SELECT * FROM t3; 216INSERT INTO t3 SELECT * FROM t4; 217INSERT INTO t4 SELECT * FROM t3; 218INSERT INTO t3 SELECT * FROM t4; 219INSERT INTO t4 SELECT * FROM t3; 220INSERT INTO t3 SELECT * FROM t4; 221INSERT INTO t4 SELECT * FROM t3; 222INSERT INTO t3 SELECT * FROM t4; 223INSERT INTO t4 SELECT * FROM t3; 224INSERT INTO t3 SELECT * FROM t4; 225INSERT INTO t4 SELECT * FROM t3; 226INSERT INTO t3 SELECT * FROM t4; 227INSERT INTO t3 VALUES (2), (3); 228 229ANALYZE table t1, t2, t3; 230 231SELECT COUNT(*) FROM t3; 232 233EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a 234 LEFT JOIN t3 ON t2.b=t3.b; 235FLUSH STATUS ; 236SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a 237 LEFT JOIN t3 ON t2.b=t3.b; 238SELECT FOUND_ROWS(); 239SHOW STATUS LIKE "handler_read%"; 240 241DROP TABLE t1,t2,t3,t4; 242# End of 4.1 tests 243 244# 245# BUG#34945 "ref_or_null queries that are null_rejecting and have a null value crash mysql" 246# 247CREATE TABLE t1 ( 248 a int(11) default NULL, 249 b int(11) default NULL, 250 KEY a (a,b) 251); 252INSERT INTO t1 VALUES (0,10),(0,11),(0,12); 253 254CREATE TABLE t2 ( 255 a int(11) default NULL, 256 b int(11) default NULL, 257 KEY a (a) 258); 259INSERT INTO t2 VALUES (3,NULL),(3,11),(3,12); 260 261SELECT * FROM t2 inner join t1 WHERE ( t1.a = 0 OR t1.a IS NULL) AND t2.a = 3 AND t2.b = t1.b; 262 263drop table t1, t2; 264-- echo End of 5.0 tests 265 266--echo # 267--echo # BUG#727667 Wrong result with OR + NOT NULL in maria-5.3 268--echo # 269 270CREATE TABLE t1 ( 271 f3 int(11), 272 f10 varchar(1), 273 KEY (f3) 274); 275INSERT INTO t1 VALUES ('9','k'),(NULL,'r'); 276SELECT * FROM t1 WHERE (f3 = 83) OR (f10 = 'z' AND f3 IS NULL); 277DROP TABLE t1; 278 279