← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/t/null_key.test

Removed/replaced DBUG symbols and TRUE/FALSE

Show diffs side-by-side

added added

removed removed

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