~drizzle-trunk/drizzle/development

1 by brian
clean slate
1
drop table if exists t1,t2;
1063.9.18 by Stewart Smith
null_key for MyISAM temp only: use temp myisam tables
2
create temporary table t1 (a int, b int not null,unique key (a,b),index(b)) engine=myisam;
1 by brian
clean slate
3
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);
4
explain select * from t1 where a is null;
5
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
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;
8
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
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;
11
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
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;
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	Using index
16
explain select * from t1 where a<=>b limit 2;
17
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
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;
20
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
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;
23
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1685.7.6 by Patrick Crews
Updated test results with changes due to optimizer bug fix. EXPLAIN output now includes 'Using where' for several queries that didn't previously have this output
24
1	SIMPLE	t1	ref_or_null	a,b	a	9	const,const	2	Using where; Using index
1 by brian
clean slate
25
explain select * from t1 where (a is null or a = 7) and b=7 order by a;
26
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1685.7.6 by Patrick Crews
Updated test results with changes due to optimizer bug fix. EXPLAIN output now includes 'Using where' for several queries that didn't previously have this output
27
1	SIMPLE	t1	ref_or_null	a,b	a	9	const,const	2	Using where; Using index; Using filesort
1 by brian
clean slate
28
explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2;
29
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
30
1	SIMPLE	t1	ref	a,b	a	5	const	3	Using where; Using index
31
explain select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
32
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
33
1	SIMPLE	t1	range	a,b	a	9	NULL	3	Using where; Using index
34
explain select * from t1 where a > 1 and a < 3 limit 1;
35
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
36
1	SIMPLE	t1	range	a	a	5	NULL	1	Using where; Using index
37
explain select * from t1 where a > 8 and a < 9;
38
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
39
1	SIMPLE	t1	range	a	a	5	NULL	1	Using where; Using index
40
select * from t1 where a is null;
41
a	b
42
NULL	7
43
NULL	9
44
NULL	9
45
select * from t1 where a is null and b = 7;
46
a	b
47
NULL	7
48
select * from t1 where a<=>b limit 2;
49
a	b
50
1	1
51
2	2
52
select * from t1 where (a is null or a > 0 and a < 3) and b < 5 limit 3;
53
a	b
54
1	1
55
2	2
56
select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3;
57
a	b
58
NULL	9
59
NULL	9
60
select * from t1 where (a is null or a = 7) and b=7;
61
a	b
62
7	7
63
NULL	7
64
select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
65
a	b
66
NULL	7
67
NULL	9
68
NULL	9
69
select * from t1 where a > 1 and a < 3 limit 1;
70
a	b
71
2	2
72
select * from t1 where a > 8 and a < 9;
73
a	b
74
create table t2 like t1;
1222.1.1 by Brian Aker
Second pass through bugs related to CREATE TABLE LIKE
75
ERROR HY000: Can't create table 'test.t2' (errno: 138)
76
create temporary table t2 like t1;
77
show create table t2;
78
Table	Create Table
79
t2	CREATE TEMPORARY TABLE `t2` (
1743.5.2 by LinuxJedi
Alter many test cases for the new SHOW CREATE TABLE output
80
  `a` INT DEFAULT NULL,
81
  `b` INT NOT NULL,
1222.1.1 by Brian Aker
Second pass through bugs related to CREATE TABLE LIKE
82
  UNIQUE KEY `a` (`a`,`b`),
83
  KEY `b` (`b`)
1638.10.83 by Stewart Smith
fix some more tests for explicit COLLATE in CREATE TABLE
84
) ENGINE=MyISAM COLLATE = utf8_general_ci
1 by brian
clean slate
85
insert into t2 select * from t1;
86
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));
87
explain select * from t1 where a is null and b = 2;
88
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
730 by jay
Forgot to --record the damn null_key test after adding regex-replaces for the InnoDB rows output in EXPLAIN
89
1	SIMPLE	t1	ref	a,b	a	5	const	X	Using where
1 by brian
clean slate
90
explain select * from t1 where a is null and b = 2 and c=0;
91
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
730 by jay
Forgot to --record the damn null_key test after adding regex-replaces for the InnoDB rows output in EXPLAIN
92
1	SIMPLE	t1	ref	a,b	a	5	const	X	Using where
1 by brian
clean slate
93
explain select * from t1 where a is null and b = 7 and c=0;
94
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
730 by jay
Forgot to --record the damn null_key test after adding regex-replaces for the InnoDB rows output in EXPLAIN
95
1	SIMPLE	t1	ref	a,b	a	5	const	X	Using where
1 by brian
clean slate
96
explain select * from t1 where a=2 and b = 2;
97
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
730 by jay
Forgot to --record the damn null_key test after adding regex-replaces for the InnoDB rows output in EXPLAIN
98
1	SIMPLE	t1	ref	a,b	a	5	const	X	Using where
1 by brian
clean slate
99
explain select * from t1 where a<=>b limit 2;
100
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
730 by jay
Forgot to --record the damn null_key test after adding regex-replaces for the InnoDB rows output in EXPLAIN
101
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	X	Using where
1 by brian
clean slate
102
explain select * from t1 where (a is null or a > 0 and a < 3) and b < 5 and c=0 limit 3;
103
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
730 by jay
Forgot to --record the damn null_key test after adding regex-replaces for the InnoDB rows output in EXPLAIN
104
1	SIMPLE	t1	range	a,b	a	5	NULL	X	Using where
1 by brian
clean slate
105
explain select * from t1 where (a is null or a = 7) and b=7 and c=0;
106
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
730 by jay
Forgot to --record the damn null_key test after adding regex-replaces for the InnoDB rows output in EXPLAIN
107
1	SIMPLE	t1	ref_or_null	a,b	a	5	const	X	Using where
1 by brian
clean slate
108
explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2;
109
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
730 by jay
Forgot to --record the damn null_key test after adding regex-replaces for the InnoDB rows output in EXPLAIN
110
1	SIMPLE	t1	ref	a,b	a	5	const	X	Using where
1 by brian
clean slate
111
explain select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
112
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
730 by jay
Forgot to --record the damn null_key test after adding regex-replaces for the InnoDB rows output in EXPLAIN
113
1	SIMPLE	t1	ref	a,b	a	5	const	X	Using where
1 by brian
clean slate
114
explain select * from t1 where a > 1 and a < 3 limit 1;
115
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
730 by jay
Forgot to --record the damn null_key test after adding regex-replaces for the InnoDB rows output in EXPLAIN
116
1	SIMPLE	t1	range	a	a	5	NULL	X	Using where
1 by brian
clean slate
117
explain select * from t1 where a is null and b=7 or a > 1 and a < 3 limit 1;
118
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
730 by jay
Forgot to --record the damn null_key test after adding regex-replaces for the InnoDB rows output in EXPLAIN
119
1	SIMPLE	t1	range	a,b	a	5	NULL	X	Using where
1 by brian
clean slate
120
explain select * from t1 where a > 8 and a < 9;
121
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
730 by jay
Forgot to --record the damn null_key test after adding regex-replaces for the InnoDB rows output in EXPLAIN
122
1	SIMPLE	t1	range	a	a	5	NULL	X	Using where
1 by brian
clean slate
123
explain select * from t1 where b like "6%";
124
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
730 by jay
Forgot to --record the damn null_key test after adding regex-replaces for the InnoDB rows output in EXPLAIN
125
1	SIMPLE	t1	range	b	b	12	NULL	X	Using where
1 by brian
clean slate
126
select * from t1 where a is null;
127
a	b	c
128
NULL	7	0
129
NULL	9	0
130
NULL	9	0
131
select * from t1 where a is null and b = 7 and c=0;
132
a	b	c
133
NULL	7	0
134
select * from t1 where a<=>b limit 2;
135
a	b	c
136
1	1	0
137
2	2	0
138
select * from t1 where (a is null or a > 0 and a < 3) and b < 5 limit 3;
139
a	b	c
140
1	1	0
141
2	2	0
142
select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3;
143
a	b	c
144
NULL	9	0
145
NULL	9	0
146
select * from t1 where (a is null or a = 7) and b=7 and c=0;
147
a	b	c
148
7	7	0
149
NULL	7	0
150
select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
151
a	b	c
152
NULL	7	0
153
NULL	9	0
154
NULL	9	0
155
select * from t1 where b like "6%";
156
a	b	c
157
6	6	0
158
drop table t1;
685.4.17 by Jay Pipes
Fixes null_key.test. Mostly differences in EXPLAIN output which may be looked at later. Also, adds exception check for NULL values in multi-row INSERT
159
drop table t2;
1063.9.18 by Stewart Smith
null_key for MyISAM temp only: use temp myisam tables
160
create temporary table t1 (a int, b int not null,unique key (a,b),index(b)) engine=myisam;
1 by brian
clean slate
161
alter table t1 modify b int null;
162
insert into t1 values (7,null), (8,null), (8,7);
163
explain select * from t1 where a = 7 and (b=7 or b is null);
164
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1685.7.6 by Patrick Crews
Updated test results with changes due to optimizer bug fix. EXPLAIN output now includes 'Using where' for several queries that didn't previously have this output
165
1	SIMPLE	t1	ref_or_null	a,b	a	10	const,const	2	Using where; Using index
1 by brian
clean slate
166
select * from t1 where a = 7 and (b=7 or b is null);
167
a	b
168
7	NULL
169
explain select * from t1 where (a = 7 or a is null) and (b=7 or b is null);
170
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
685.4.17 by Jay Pipes
Fixes null_key.test. Mostly differences in EXPLAIN output which may be looked at later. Also, adds exception check for NULL values in multi-row INSERT
171
1	SIMPLE	t1	ref_or_null	a,b	a	5	const	2	Using where; Using index
1 by brian
clean slate
172
select * from t1 where (a = 7 or a is null) and (b=7 or b is null);
173
a	b
174
7	NULL
175
explain select * from t1 where (a = 7 or a is null) and (a = 7 or a is null);
176
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1685.7.6 by Patrick Crews
Updated test results with changes due to optimizer bug fix. EXPLAIN output now includes 'Using where' for several queries that didn't previously have this output
177
1	SIMPLE	t1	ref_or_null	a	a	5	const	2	Using where; Using index
1 by brian
clean slate
178
select * from t1 where (a = 7 or a is null) and (a = 7 or a is null);
179
a	b
180
7	NULL
181
create table t2 (a int);
182
insert into t2 values (7),(8);
183
explain select * from t2 straight_join t1 where t1.a=t2.a and b is null;
184
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
730 by jay
Forgot to --record the damn null_key test after adding regex-replaces for the InnoDB rows output in EXPLAIN
185
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	X	
186
1	SIMPLE	t1	ref	a,b	b	5	const	X	Using where
1 by brian
clean slate
187
drop index b on t1;
188
explain select * from t2,t1 where t1.a=t2.a and b is null;
189
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
730 by jay
Forgot to --record the damn null_key test after adding regex-replaces for the InnoDB rows output in EXPLAIN
190
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	X	
191
1	SIMPLE	t1	ref	a	a	10	test.t2.a,const	X	Using where; Using index
1 by brian
clean slate
192
select * from t2,t1 where t1.a=t2.a and b is null;
193
a	a	b
194
7	7	NULL
195
8	8	NULL
196
explain select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null);
197
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
730 by jay
Forgot to --record the damn null_key test after adding regex-replaces for the InnoDB rows output in EXPLAIN
198
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	X	
1685.7.6 by Patrick Crews
Updated test results with changes due to optimizer bug fix. EXPLAIN output now includes 'Using where' for several queries that didn't previously have this output
199
1	SIMPLE	t1	ref_or_null	a	a	10	test.t2.a,const	X	Using where; Using index
1 by brian
clean slate
200
select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null);
201
a	a	b
202
7	7	NULL
203
8	8	7
204
8	8	NULL
205
explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7;
206
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
730 by jay
Forgot to --record the damn null_key test after adding regex-replaces for the InnoDB rows output in EXPLAIN
207
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	X	
1685.7.6 by Patrick Crews
Updated test results with changes due to optimizer bug fix. EXPLAIN output now includes 'Using where' for several queries that didn't previously have this output
208
1	SIMPLE	t1	ref_or_null	a	a	10	test.t2.a,const	X	Using where; Using index
1 by brian
clean slate
209
select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7;
210
a	a	b
211
8	8	7
212
explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and (b= 7 or b is null);
213
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
730 by jay
Forgot to --record the damn null_key test after adding regex-replaces for the InnoDB rows output in EXPLAIN
214
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	X	
215
1	SIMPLE	t1	ref_or_null	a	a	5	test.t2.a	X	Using where; Using index
1 by brian
clean slate
216
select * from t2,t1 where (t1.a=t2.a or t1.a is null) and (b= 7 or b is null);
217
a	a	b
218
7	7	NULL
219
8	8	NULL
220
8	8	7
221
insert into t2 values (null),(6);
222
delete from t1 where a=8;
223
explain select * from t2,t1 where t1.a=t2.a or t1.a is null;
224
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
730 by jay
Forgot to --record the damn null_key test after adding regex-replaces for the InnoDB rows output in EXPLAIN
225
1	SIMPLE	t1	system	a	NULL	NULL	NULL	X	
226
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	X	Using where
1 by brian
clean slate
227
explain select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9);
228
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
730 by jay
Forgot to --record the damn null_key test after adding regex-replaces for the InnoDB rows output in EXPLAIN
229
1	SIMPLE	t1	system	a	NULL	NULL	NULL	X	
230
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	X	Using where
1 by brian
clean slate
231
select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9);
232
a	a	b
233
7	7	NULL
234
drop table t1,t2;
1063.9.18 by Stewart Smith
null_key for MyISAM temp only: use temp myisam tables
235
CREATE TEMPORARY TABLE t1 (
685.4.17 by Jay Pipes
Fixes null_key.test. Mostly differences in EXPLAIN output which may be looked at later. Also, adds exception check for NULL values in multi-row INSERT
236
id int NOT NULL auto_increment,
237
uniq_id int default NULL,
1 by brian
clean slate
238
PRIMARY KEY  (id),
239
UNIQUE KEY idx1 (uniq_id)
240
) ENGINE=MyISAM;
1063.9.18 by Stewart Smith
null_key for MyISAM temp only: use temp myisam tables
241
CREATE TEMPORARY TABLE t2 (
685.4.17 by Jay Pipes
Fixes null_key.test. Mostly differences in EXPLAIN output which may be looked at later. Also, adds exception check for NULL values in multi-row INSERT
242
id int NOT NULL auto_increment,
243
uniq_id int default NULL,
1 by brian
clean slate
244
PRIMARY KEY  (id)
245
) ENGINE=MyISAM;
246
INSERT INTO t1 VALUES (1,NULL),(2,NULL),(3,1),(4,2),(5,NULL),(6,NULL),(7,3),(8,4),(9,NULL),(10,NULL);
247
INSERT INTO t2 VALUES (1,NULL),(2,NULL),(3,1),(4,2),(5,NULL),(6,NULL),(7,3),(8,4),(9,NULL),(10,NULL);
248
explain select id from t1 where uniq_id is null;
249
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
685.4.17 by Jay Pipes
Fixes null_key.test. Mostly differences in EXPLAIN output which may be looked at later. Also, adds exception check for NULL values in multi-row INSERT
250
1	SIMPLE	t1	ref	idx1	idx1	5	const	5	Using where
1 by brian
clean slate
251
explain select id from t1 where uniq_id =1;
252
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
253
1	SIMPLE	t1	const	idx1	idx1	5	const	1	
254
UPDATE t1 SET id=id+100 where uniq_id is null;
255
UPDATE t2 SET id=id+100 where uniq_id is null;
256
select id from t1 where uniq_id is null;
257
id
258
101
259
102
260
105
261
106
262
109
263
110
264
select id from t2 where uniq_id is null;
265
id
266
101
267
102
268
105
269
106
270
109
271
110
272
DELETE FROM t1 WHERE uniq_id IS NULL;
273
DELETE FROM t2 WHERE uniq_id IS NULL;
274
SELECT * FROM t1 ORDER BY uniq_id, id;
275
id	uniq_id
276
3	1
277
4	2
278
7	3
279
8	4
280
SELECT * FROM t2 ORDER BY uniq_id, id;
281
id	uniq_id
282
3	1
283
4	2
284
7	3
285
8	4
286
DROP table t1,t2;
1063.9.18 by Stewart Smith
null_key for MyISAM temp only: use temp myisam tables
287
CREATE TEMPORARY TABLE `t1` (
1 by brian
clean slate
288
`order_id` char(32) NOT NULL default '',
289
`product_id` char(32) NOT NULL default '',
685.4.17 by Jay Pipes
Fixes null_key.test. Mostly differences in EXPLAIN output which may be looked at later. Also, adds exception check for NULL values in multi-row INSERT
290
`product_type` int NOT NULL default '0',
1 by brian
clean slate
291
PRIMARY KEY  (`order_id`,`product_id`,`product_type`)
292
) ENGINE=MyISAM;
1063.9.18 by Stewart Smith
null_key for MyISAM temp only: use temp myisam tables
293
CREATE TEMPORARY TABLE `t2` (
1 by brian
clean slate
294
`order_id` char(32) NOT NULL default '',
295
`product_id` char(32) NOT NULL default '',
685.4.17 by Jay Pipes
Fixes null_key.test. Mostly differences in EXPLAIN output which may be looked at later. Also, adds exception check for NULL values in multi-row INSERT
296
`product_type` int NOT NULL default '0',
1 by brian
clean slate
297
PRIMARY KEY  (`order_id`,`product_id`,`product_type`)
298
) ENGINE=MyISAM;
299
INSERT INTO t1 (order_id, product_id, product_type) VALUES
300
('3d7ce39b5d4b3e3d22aaafe9b633de51',1206029, 3),
301
('3d7ce39b5d4b3e3d22aaafe9b633de51',5880836, 3),
302
('9d9aad7764b5b2c53004348ef8d34500',2315652, 3);
303
INSERT INTO t2 (order_id, product_id, product_type) VALUES
304
('9d9aad7764b5b2c53004348ef8d34500',2315652, 3);
305
select t1.* from t1
306
left join t2 using(order_id, product_id, product_type)
307
where t2.order_id=NULL;
308
order_id	product_id	product_type
309
select t1.* from t1
310
left join t2 using(order_id, product_id, product_type)
311
where t2.order_id is NULL;
312
order_id	product_id	product_type
313
3d7ce39b5d4b3e3d22aaafe9b633de51	1206029	3
314
3d7ce39b5d4b3e3d22aaafe9b633de51	5880836	3
315
drop table t1,t2;
316
create table t1 (id int);
317
insert into t1 values (null), (0);
318
create table t2 (id int);
319
insert into t2 values (null);
320
select * from t1, t2 where t1.id = t2.id;
321
id	id
322
alter table t1 add key id (id);
323
select * from t1, t2 where t1.id = t2.id;
324
id	id
325
drop table t1,t2;
326
create table t1 (
327
id  integer,
328
id2 integer not null,
329
index (id),
330
index (id2)
331
);
332
insert into t1 values(null,null),(1,1);
685.4.17 by Jay Pipes
Fixes null_key.test. Mostly differences in EXPLAIN output which may be looked at later. Also, adds exception check for NULL values in multi-row INSERT
333
ERROR 23000: Column 'id2' cannot be null
1 by brian
clean slate
334
select * from t1;
335
id	id2
336
select * from t1 where id <=> null;
337
id	id2
338
select * from t1 where id <=> null or id > 0;
339
id	id2
340
select * from t1 where id is null or id > 0;
341
id	id2
342
select * from t1 where id2 <=> null or id2 > 0;
343
id	id2
344
select * from t1 where id2 is null or id2 > 0;
345
id	id2
346
delete from t1 where id <=> NULL;
347
select * from t1;
348
id	id2
349
drop table t1;
350
CREATE TABLE t1 (a int);
351
CREATE TABLE t2 (a int, b int, INDEX idx(a));
352
CREATE TABLE t3 (b int, INDEX idx(b));
353
CREATE TABLE t4 (b int, INDEX idx(b));
354
INSERT INTO t1 VALUES (1), (2), (3), (4);
355
INSERT INTO t2 VALUES (1, 1), (3, 1);
356
INSERT INTO t3 VALUES 
357
(NULL), (NULL), (NULL), (NULL), (NULL),
358
(NULL), (NULL), (NULL), (NULL), (NULL);
359
INSERT INTO t4 SELECT * FROM t3;
360
INSERT INTO t3 SELECT * FROM t4;
361
INSERT INTO t4 SELECT * FROM t3;
362
INSERT INTO t3 SELECT * FROM t4;
363
INSERT INTO t4 SELECT * FROM t3;
364
INSERT INTO t3 SELECT * FROM t4;
365
INSERT INTO t4 SELECT * FROM t3;
366
INSERT INTO t3 SELECT * FROM t4;
367
INSERT INTO t4 SELECT * FROM t3;
368
INSERT INTO t3 SELECT * FROM t4;
369
INSERT INTO t4 SELECT * FROM t3;
370
INSERT INTO t3 SELECT * FROM t4;
371
INSERT INTO t4 SELECT * FROM t3;
372
INSERT INTO t3 SELECT * FROM t4;
373
INSERT INTO t4 SELECT * FROM t3;
374
INSERT INTO t3 SELECT * FROM t4;
375
INSERT INTO t3 VALUES (2), (3);
376
ANALYZE table t1, t2, t3;
377
Table	Op	Msg_type	Msg_text
378
test.t1	analyze	status	OK
379
test.t2	analyze	status	OK
380
test.t3	analyze	status	OK
381
SELECT COUNT(*) FROM t3;
382
COUNT(*)
383
15972
384
EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
385
LEFT JOIN t3 ON t2.b=t3.b;
386
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
731 by jay
Had a --regex-replace by accident. Should have been --replace_column call. Only showed up in make test, not running single test, because InnoDB key numbers were different with multiple test running.
387
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	X	
388
1	SIMPLE	t2	ref	idx	idx	5	test.t1.a	X	
389
1	SIMPLE	t3	ref	idx	idx	5	test.t2.b	X	Using index
1 by brian
clean slate
390
FLUSH STATUS ;
391
SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
392
LEFT JOIN t3 ON t2.b=t3.b;
393
a	a	b	b
394
1	1	1	NULL
395
2	NULL	NULL	NULL
396
3	3	1	NULL
397
4	NULL	NULL	NULL
398
SELECT FOUND_ROWS();
399
FOUND_ROWS()
400
4
401
SHOW STATUS LIKE "handler_read%";
402
Variable_name	Value
685.4.17 by Jay Pipes
Fixes null_key.test. Mostly differences in EXPLAIN output which may be looked at later. Also, adds exception check for NULL values in multi-row INSERT
403
Handler_read_first	1
404
Handler_read_key	10
1 by brian
clean slate
405
Handler_read_next	2
406
Handler_read_prev	0
407
Handler_read_rnd	0
1561.3.11 by Joe Daly
get tests working
408
Handler_read_rnd_next	5
1 by brian
clean slate
409
DROP TABLE t1,t2,t3,t4;
410
CREATE TABLE t1 (
685.4.17 by Jay Pipes
Fixes null_key.test. Mostly differences in EXPLAIN output which may be looked at later. Also, adds exception check for NULL values in multi-row INSERT
411
a int default NULL,
412
b int default NULL,
1 by brian
clean slate
413
KEY a (a,b)
414
);
415
INSERT INTO t1 VALUES (0,10),(0,11),(0,12);
416
CREATE TABLE t2 (
685.4.17 by Jay Pipes
Fixes null_key.test. Mostly differences in EXPLAIN output which may be looked at later. Also, adds exception check for NULL values in multi-row INSERT
417
a int default NULL,
418
b int default NULL,
1 by brian
clean slate
419
KEY a (a)
420
);
421
INSERT INTO t2 VALUES (3,NULL),(3,11),(3,12);
422
SELECT * FROM t2 inner join t1 WHERE ( t1.a = 0 OR t1.a IS NULL) AND t2.a = 3 AND t2.b = t1.b;
423
a	b	a	b
424
3	11	0	11
425
3	12	0	12
426
drop table t1, t2;
427
End of 5.0 tests