null_key.result 8.79 KB
Newer Older
1
drop table if exists t1,t2;
2 3 4
create table t1 (a int, b int not null,unique key (a,b),index(b)) type=myisam;
insert 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);
explain select * from t1 where a is null;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
5
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
6
1	SIMPLE	t1	ref	a	a	5	const	3	Using where; Using index
7
explain select * from t1 where a is null and b = 2;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
8
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
9
1	SIMPLE	t1	ref	a,b	a	9	const,const	1	Using where; Using index
10
explain select * from t1 where a is null and b = 7;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
11
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
12
1	SIMPLE	t1	ref	a,b	a	9	const,const	1	Using where; Using index
13
explain select * from t1 where a=2 and b = 2;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
14
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
15
1	SIMPLE	t1	const	a,b	a	9	const,const	1	
16
explain select * from t1 where a<=>b limit 2;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
17
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
18
1	SIMPLE	t1	index	NULL	a	9	NULL	12	Using where; Using index
19
explain select * from t1 where (a is null or a > 0 and a < 3) and b < 5 limit 3;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
20
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
21
1	SIMPLE	t1	range	a,b	a	9	NULL	3	Using where; Using index
22
explain select * from t1 where (a is null or a = 7) and b=7;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
23
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
24
1	SIMPLE	t1	ref	a,b	b	4	const	2	Using where
25
explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
26
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
27
1	SIMPLE	t1	ref	a,b	a	5	const	3	Using where; Using index
28
explain select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
29
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
30
1	SIMPLE	t1	range	a,b	a	9	NULL	2	Using where; Using index
31
explain select * from t1 where a > 1 and a < 3 limit 1;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
32
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
33
1	SIMPLE	t1	range	a	a	5	NULL	1	Using where; Using index
34
explain select * from t1 where a > 8 and a < 9;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
35
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
36
1	SIMPLE	t1	range	a	a	5	NULL	1	Using where; Using index
37
select * from t1 where a is null;
38 39 40 41
a	b
NULL	7
NULL	9
NULL	9
42
select * from t1 where a is null and b = 7;
43 44
a	b
NULL	7
45
select * from t1 where a<=>b limit 2;
46 47 48
a	b
1	1
2	2
49
select * from t1 where (a is null or a > 0 and a < 3) and b < 5 limit 3;
50 51 52
a	b
1	1
2	2
53
select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3;
54 55 56
a	b
NULL	9
NULL	9
57
select * from t1 where (a is null or a = 7) and b=7;
58 59 60
a	b
NULL	7
7	7
61
select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
62 63 64 65
a	b
NULL	7
NULL	9
NULL	9
66 67
alter 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));
explain select * from t1 where a is null and b = 2;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
68
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
69
1	SIMPLE	t1	ref	a,b	a	5	const	3	Using where
70
explain select * from t1 where a is null and b = 2 and c=0;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
71
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
72
1	SIMPLE	t1	ref	a,b	a	5	const	3	Using where
73
explain select * from t1 where a is null and b = 7 and c=0;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
74
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
75
1	SIMPLE	t1	ref	a,b	a	5	const	3	Using where
76
explain select * from t1 where a=2 and b = 2;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
77
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
78
1	SIMPLE	t1	ref	a,b	a	5	const	1	Using where
79
explain select * from t1 where a<=>b limit 2;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
80
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
81
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
82
explain select * from t1 where (a is null or a > 0 and a < 3) and b < 5 and c=0 limit 3;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
83
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
84
1	SIMPLE	t1	range	a,b	a	5	NULL	5	Using where
85
explain select * from t1 where (a is null or a = 7) and b=7 and c=0;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
86
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
87
1	SIMPLE	t1	range	a,b	a	5	NULL	4	Using where
88
explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
89
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
90
1	SIMPLE	t1	ref	a,b	a	5	const	3	Using where
91
explain select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
92
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
93
1	SIMPLE	t1	ref	a,b	a	5	const	3	Using where
94
explain select * from t1 where a > 1 and a < 3 limit 1;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
95
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
96
1	SIMPLE	t1	range	a	a	5	NULL	1	Using where
97
explain select * from t1 where a is null and b=7 or a > 1 and a < 3 limit 1;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
98
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
99
1	SIMPLE	t1	range	a,b	a	5	NULL	4	Using where
100
explain select * from t1 where a > 8 and a < 9;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
101
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
102
1	SIMPLE	t1	range	a	a	5	NULL	1	Using where
103
explain select * from t1 where b like "6%";
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
104
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
105
1	SIMPLE	t1	range	b	b	12	NULL	1	Using where
106
select * from t1 where a is null;
107 108 109 110
a	b	c
NULL	7	0
NULL	9	0
NULL	9	0
111
select * from t1 where a is null and b = 7 and c=0;
112 113
a	b	c
NULL	7	0
114
select * from t1 where a<=>b limit 2;
115 116 117
a	b	c
1	1	0
2	2	0
118
select * from t1 where (a is null or a > 0 and a < 3) and b < 5 limit 3;
119 120 121
a	b	c
1	1	0
2	2	0
122
select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3;
123 124 125
a	b	c
NULL	9	0
NULL	9	0
126
select * from t1 where (a is null or a = 7) and b=7 and c=0;
127 128 129
a	b	c
NULL	7	0
7	7	0
130
select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
131 132 133 134
a	b	c
NULL	7	0
NULL	9	0
NULL	9	0
135
select * from t1 where b like "6%";
136 137
a	b	c
6	6	0
138 139 140 141 142 143 144 145 146 147 148 149 150 151 152
drop table t1;
CREATE TABLE t1 (
id int(10) unsigned NOT NULL auto_increment,
uniq_id int(10) unsigned default NULL,
PRIMARY KEY  (id),
UNIQUE KEY idx1 (uniq_id)
) TYPE=MyISAM;
CREATE TABLE t2 (
id int(10) unsigned NOT NULL auto_increment,
uniq_id int(10) unsigned default NULL,
PRIMARY KEY  (id)
) TYPE=MyISAM;
INSERT INTO t1 VALUES (1,NULL),(2,NULL),(3,1),(4,2),(5,NULL),(6,NULL),(7,3),(8,4),(9,NULL),(10,NULL);
INSERT INTO t2 VALUES (1,NULL),(2,NULL),(3,1),(4,2),(5,NULL),(6,NULL),(7,3),(8,4),(9,NULL),(10,NULL);
explain select id from t1 where uniq_id is null;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
153
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
154
1	SIMPLE	t1	ref	idx1	idx1	5	const	1	Using where
155
explain select id from t1 where uniq_id =1;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
156
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
157
1	SIMPLE	t1	const	idx1	idx1	5	const	1	
158 159 160
UPDATE t1 SET id=id+100 where uniq_id is null;
UPDATE t2 SET id=id+100 where uniq_id is null;
select id from t1 where uniq_id is null;
161 162 163 164 165 166 167
id
101
102
105
106
109
110
168
select id from t2 where uniq_id is null;
169 170 171 172 173 174 175
id
101
102
105
106
109
110
176 177 178
DELETE FROM t1 WHERE uniq_id IS NULL;
DELETE FROM t2 WHERE uniq_id IS NULL;
SELECT * FROM t1 ORDER BY uniq_id, id;
179 180 181 182 183
id	uniq_id
3	1
4	2
7	3
8	4
184
SELECT * FROM t2 ORDER BY uniq_id, id;
185 186 187 188 189
id	uniq_id
3	1
4	2
7	3
8	4
190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211
DROP table t1,t2;
CREATE TABLE `t1` (
`order_id` char(32) NOT NULL default '',
`product_id` char(32) NOT NULL default '',
`product_type` int(11) NOT NULL default '0',
PRIMARY KEY  (`order_id`,`product_id`,`product_type`)
) TYPE=MyISAM;
CREATE TABLE `t2` (
`order_id` char(32) NOT NULL default '',
`product_id` char(32) NOT NULL default '',
`product_type` int(11) NOT NULL default '0',
PRIMARY KEY  (`order_id`,`product_id`,`product_type`)
) TYPE=MyISAM;
INSERT INTO t1 (order_id, product_id, product_type) VALUES
('3d7ce39b5d4b3e3d22aaafe9b633de51',1206029, 3),
('3d7ce39b5d4b3e3d22aaafe9b633de51',5880836, 3),
('9d9aad7764b5b2c53004348ef8d34500',2315652, 3);
INSERT INTO t2 (order_id, product_id, product_type) VALUES
('9d9aad7764b5b2c53004348ef8d34500',2315652, 3);
select t1.* from t1
left join t2 using(order_id, product_id, product_type)
where t2.order_id=NULL;
212
order_id	product_id	product_type
213 214 215
select t1.* from t1
left join t2 using(order_id, product_id, product_type)
where t2.order_id is NULL;
216 217 218
order_id	product_id	product_type
3d7ce39b5d4b3e3d22aaafe9b633de51	1206029	3
3d7ce39b5d4b3e3d22aaafe9b633de51	5880836	3
219 220 221 222 223 224
drop table t1,t2;
create table t1 (id int);
insert into t1 values (null), (0);
create table t2 (id int);
insert into t2 values (null);
select * from t1, t2 where t1.id = t2.id;
225
id	id
226 227
alter table t1 add key id (id);
select * from t1, t2 where t1.id = t2.id;
228
id	id
229
drop table t1,t2;
230 231 232 233 234 235 236
create table t1 (
id  integer,
id2 integer not null,
index (id),
index (id2)
);
insert into t1 values(null,null),(1,1);
venu@myvenu.com's avatar
venu@myvenu.com committed
237 238
Warnings:
Warning	1261	Data truncated, NULL supplied to NOT NULL column 'id2' at row 1
239
select * from t1;
240 241 242
id	id2
NULL	0
1	1
243
select * from t1 where id <=> null;
244 245
id	id2
NULL	0
246
select * from t1 where id <=> null or id > 0;
247 248 249
id	id2
NULL	0
1	1
250
select * from t1 where id is null or id > 0;
251 252 253
id	id2
NULL	0
1	1
254
select * from t1 where id2 <=> null or id2 > 0;
255 256
id	id2
1	1
257
select * from t1 where id2 is null or id2 > 0;
258 259
id	id2
1	1
260 261
delete from t1 where id <=> NULL;
select * from t1;
262 263
id	id2
1	1
264
drop table t1;