Debian Codegraph @

mysql-test/suite/heap/heap_btree.result

raw
1 drop table if exists t1;
2 create table t1 (a int not null,b int not null, primary key using BTREE (a)) engine=heap comment="testing heaps" avg_row_length=100 min_rows=1 max_rows=100;
3 insert into t1 values(1,1),(2,2),(3,3),(4,4);
4 delete from t1 where a=1 or a=0;
5 show keys from t1;
6 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
7 t1 0 PRIMARY 1 a A NULL NULL NULL BTREE NO
8 select * from t1;
9 a b
10 2 2
11 3 3
12 4 4
13 select * from t1 where a=4;
14 a b
15 4 4
16 update t1 set b=5 where a=4;
17 update t1 set b=b+1 where a>=3;
18 replace t1 values (3,3);
19 select * from t1;
20 a b
21 2 2
22 3 3
23 4 6
24 alter table t1 add c int not null, add key using BTREE (c,a);
25 drop table t1;
26 create table t1 (a int not null,b int not null, primary key using BTREE (a)) engine=heap comment="testing heaps";
27 insert into t1 values(-2,-2),(-1,-1),(0,0),(1,1),(2,2),(3,3),(4,4);
28 delete from t1 where a > -3;
29 select * from t1;
30 a b
31 drop table t1;
32 create table t1 (a int not null,b int not null, primary key using BTREE (a)) engine=heap comment="testing heaps";
33 insert into t1 values(1,1),(2,2),(3,3),(4,4);
34 alter table t1 modify a int not null auto_increment, engine=myisam, comment="new myisam table";
35 select * from t1;
36 a b
37 1 1
38 2 2
39 3 3
40 4 4
41 drop table t1;
42 create table t1 (a int not null) engine=heap;
43 insert into t1 values (869751),(736494),(226312),(802616),(728912);
44 select * from t1 where a > 736494;
45 a
46 869751
47 802616
48 alter table t1 add unique uniq_id using BTREE (a);
49 select * from t1 where a > 736494;
50 a
51 802616
52 869751
53 select * from t1 where a = 736494;
54 a
55 736494
56 select * from t1 where a=869751 or a=736494;
57 a
58 736494
59 869751
60 select * from t1 where a in (869751,736494,226312,802616);
61 a
62 226312
63 736494
64 802616
65 869751
66 explain select * from t1 where a in (869751,736494,226312,802616);
67 id select_type table type possible_keys key key_len ref rows Extra
68 1 SIMPLE t1 range uniq_id uniq_id 4 NULL 4 Using where
69 alter table t1 engine=myisam;
70 explain select * from t1 where a in (869751,736494,226312,802616);
71 id select_type table type possible_keys key key_len ref rows Extra
72 1 SIMPLE t1 range uniq_id uniq_id 4 NULL 4 Using where; Using index
73 insert into t1 values (1),(2),(3),(4),(5),(6);
74 explain select * from t1 where a in (869751,736494,226312,802616);
75 id select_type table type possible_keys key key_len ref rows Extra
76 1 SIMPLE t1 range uniq_id uniq_id 4 NULL 4 Using where; Using index
77 drop table t1;
78 create table t1 (x int not null, y int not null, key x using BTREE (x,y), unique y using BTREE (y))
79 engine=heap;
80 insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6);
81 explain select * from t1 where x=1;
82 id select_type table type possible_keys key key_len ref rows Extra
83 1 SIMPLE t1 ref x x 4 const 1
84 select * from t1 where x=1;
85 x y
86 1 1
87 1 3
88 select * from t1,t1 as t2 where t1.x=t2.y;
89 x y x y
90 1 1 1 1
91 2 2 2 2
92 1 3 1 1
93 2 4 2 2
94 2 5 2 2
95 2 6 2 2
96 explain select * from t1,t1 as t2 where t1.x=t2.y;
97 id select_type table type possible_keys key key_len ref rows Extra
98 1 SIMPLE t1 ALL x NULL NULL NULL 6
99 1 SIMPLE t2 eq_ref y y 4 test.t1.x 1
100 drop table t1;
101 create table t1 (a int) engine=heap;
102 insert into t1 values(1);
103 select max(a) from t1;
104 max(a)
105 1
106 drop table t1;
107 CREATE TABLE t1 ( a int not null default 0, b int not null default 0, key using BTREE (a,b), key using BTREE (b) ) ENGINE=HEAP;
108 insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6);
109 select * from t1 where a=1;
110 a b
111 1 1
112 1 2
113 1 3
114 1 4
115 1 5
116 1 6
117 insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6);
118 select * from t1 where a=1;
119 a b
120 1 1
121 1 1
122 1 2
123 1 2
124 1 3
125 1 3
126 1 4
127 1 4
128 1 5
129 1 5
130 1 6
131 1 6
132 explain select * from tx where a=x order by a,b;
133 id select_type table type possible_keys key key_len ref rows Extra
134 x SIMPLE tx ref a a x const x Using where
135 explain select * from tx where a=x order by b;
136 id select_type table type possible_keys key key_len ref rows Extra
137 x SIMPLE tx ref a a x const x Using where
138 select * from t1 where b=1;
139 a b
140 1 1
141 1 1
142 explain select * from tx where b=x;
143 id select_type table type possible_keys key key_len ref rows Extra
144 x SIMPLE tx ref b b x const x
145 drop table t1;
146 create table t1 (id int unsigned not null, primary key using BTREE (id)) engine=HEAP;
147 insert into t1 values(1);
148 select max(id) from t1;
149 max(id)
150 1
151 insert into t1 values(2);
152 select max(id) from t1;
153 max(id)
154 2
155 replace into t1 values(1);
156 drop table t1;
157 create table t1 (n int) engine=heap;
158 drop table t1;
159 create table t1 (n int) engine=heap;
160 drop table if exists t1;
161 CREATE table t1(f1 int not null,f2 char(20) not
162 null,index(f2)) engine=heap;
163 INSERT into t1 set f1=12,f2="bill";
164 INSERT into t1 set f1=13,f2="bill";
165 INSERT into t1 set f1=14,f2="bill";
166 INSERT into t1 set f1=15,f2="bill";
167 INSERT into t1 set f1=16,f2="ted";
168 INSERT into t1 set f1=12,f2="ted";
169 INSERT into t1 set f1=12,f2="ted";
170 INSERT into t1 set f1=12,f2="ted";
171 INSERT into t1 set f1=12,f2="ted";
172 delete from t1 where f2="bill";
173 select * from t1;
174 f1 f2
175 16 ted
176 12 ted
177 12 ted
178 12 ted
179 12 ted
180 drop table t1;
181 create table t1 (btn char(10) not null, key using BTREE (btn)) engine=heap;
182 insert into t1 values ("hello"),("hello"),("hello"),("hello"),("hello"),("hello"),("hello"), ("a"),("b"),("c"),("d"),("e"),("f"),("g"),("h"),("i");
183 explain select * from t1 where btn like "i%";
184 id select_type table type possible_keys key key_len ref rows Extra
185 1 SIMPLE t1 range btn btn 40 NULL 1 Using where
186 explain select * from t1 where btn like "h%";
187 id select_type table type possible_keys key key_len ref rows Extra
188 1 SIMPLE t1 range btn btn 40 NULL # Using where
189 explain select * from t1 where btn like "a%";
190 id select_type table type possible_keys key key_len ref rows Extra
191 1 SIMPLE t1 range btn btn 40 NULL 1 Using where
192 explain select * from t1 where btn like "b%";
193 id select_type table type possible_keys key key_len ref rows Extra
194 1 SIMPLE t1 range btn btn 40 NULL 1 Using where
195 select * from t1 where btn like "ff%";
196 btn
197 select * from t1 where btn like " %";
198 btn
199 select * from t1 where btn like "q%";
200 btn
201 alter table t1 add column new_col char(1) not null, add key using BTREE (btn,new_col), drop key btn;
202 update t1 set new_col=left(btn,1);
203 explain select * from t1 where btn="a";
204 id select_type table type possible_keys key key_len ref rows Extra
205 1 SIMPLE t1 ref btn btn 40 const 1 Using where
206 explain select * from t1 where btn="a" and new_col="a";
207 id select_type table type possible_keys key key_len ref rows Extra
208 1 SIMPLE t1 ref btn btn 44 const,const 1 Using where
209 drop table t1;
210 CREATE TABLE t1 (
211 a int default NULL,
212 b int default NULL,
213 KEY a using BTREE (a),
214 UNIQUE b using BTREE (b)
215 ) engine=heap;
216 INSERT INTO t1 VALUES (NULL,99),(99,NULL),(1,1),(2,2),(1,3);
217 SELECT * FROM t1 WHERE a=NULL;
218 a b
219 explain SELECT * FROM t1 WHERE a IS NULL;
220 id select_type table type possible_keys key key_len ref rows Extra
221 1 SIMPLE t1 ref a a 5 const 1 Using where
222 SELECT * FROM t1 WHERE a<=>NULL;
223 a b
224 NULL 99
225 SELECT * FROM t1 WHERE b=NULL;
226 a b
227 explain SELECT * FROM t1 WHERE b IS NULL;
228 id select_type table type possible_keys key key_len ref rows Extra
229 1 SIMPLE t1 ref b b 5 const 1 Using where
230 SELECT * FROM t1 WHERE b<=>NULL;
231 a b
232 99 NULL
233 INSERT INTO t1 VALUES (1,3);
234 ERROR 23000: Duplicate entry '3' for key 'b'
235 DROP TABLE t1;
236 CREATE TABLE t1 (a int, b int, c int, key using BTREE (a, b, c)) engine=heap;
237 INSERT INTO t1 VALUES (1, NULL, NULL), (1, 1, NULL), (1, NULL, 1);
238 SELECT * FROM t1 WHERE a=1 and b IS NULL;
239 a b c
240 1 NULL NULL
241 1 NULL 1
242 SELECT * FROM t1 WHERE a=1 and c IS NULL;
243 a b c
244 1 NULL NULL
245 1 1 NULL
246 SELECT * FROM t1 WHERE a=1 and b IS NULL and c IS NULL;
247 a b c
248 1 NULL NULL
249 DROP TABLE t1;
250 CREATE TABLE t1 (a int not null, primary key using BTREE (a)) engine=heap;
251 INSERT into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11);
252 DELETE from t1 where a < 100;
253 SELECT * from t1;
254 a
255 DROP TABLE t1;
256 create table t1(a int not null, key using btree(a)) engine=heap;
257 insert into t1 values (2), (2), (2), (1), (1), (3), (3), (3), (3);
258 select a from t1 where a > 2 order by a;
259 a
260 3
261 3
262 3
263 3
264 delete from t1 where a < 4;
265 select a from t1 order by a;
266 a
267 insert into t1 values (2), (2), (2), (1), (1), (3), (3), (3), (3);
268 select a from t1 where a > 4 order by a;
269 a
270 delete from t1 where a > 4;
271 select a from t1 order by a;
272 a
273 1
274 1
275 2
276 2
277 2
278 3
279 3
280 3
281 3
282 select a from t1 where a > 3 order by a;
283 a
284 delete from t1 where a >= 2;
285 select a from t1 order by a;
286 a
287 1
288 1
289 drop table t1;
290 CREATE TABLE t1 (
291 c1 CHAR(3),
292 c2 INTEGER,
293 KEY USING BTREE(c1),
294 KEY USING BTREE(c2)
295 ) ENGINE= MEMORY;
296 INSERT INTO t1 VALUES ('ABC',0), ('A',0), ('B',0), ('C',0);
297 UPDATE t1 SET c2= c2 + 1 WHERE c1 = 'A';
298 SELECT * FROM t1;
299 c1 c2
300 ABC 0
301 A 1
302 B 0
303 C 0
304 DROP TABLE t1;
305 CREATE TABLE t1 (
306 c1 ENUM('1', '2'),
307 UNIQUE USING BTREE(c1)
308 ) ENGINE= MEMORY DEFAULT CHARSET= utf8;
309 INSERT INTO t1 VALUES('1'), ('2');
310 DROP TABLE t1;
311 CREATE TABLE t1 (
312 c1 SET('1', '2'),
313 UNIQUE USING BTREE(c1)
314 ) ENGINE= MEMORY DEFAULT CHARSET= utf8;
315 INSERT INTO t1 VALUES('1'), ('2');
316 DROP TABLE t1;
317 CREATE TABLE t1 (a INT, KEY USING BTREE(a)) ENGINE=MEMORY;
318 INSERT INTO t1 VALUES(1),(2),(2);
319 DELETE FROM t1 WHERE a=2;
320 SELECT * FROM t1;
321 a
322 1
323 DROP TABLE t1;
324 End of 4.1 tests
325 CREATE TABLE t1(val INT, KEY USING BTREE(val)) ENGINE=memory;
326 INSERT INTO t1 VALUES(0);
327 SELECT INDEX_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='t1';
328 INDEX_LENGTH
329 21
330 UPDATE t1 SET val=1;
331 SELECT INDEX_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='t1';
332 INDEX_LENGTH
333 21
334 DROP TABLE t1;
335 CREATE TABLE t1 (a INT, UNIQUE USING BTREE(a)) ENGINE=MEMORY;
336 INSERT INTO t1 VALUES(NULL),(NULL);
337 DROP TABLE t1;
338 create table t1(a varchar(255), b varchar(255),
339 key using btree (a,b)) engine=memory;
340 insert into t1 values (1, 1), (3, 3), (2, 2), (NULL, 1), (NULL, NULL), (0, 0);
341 select * from t1 where a is null;
342 a b
343 NULL NULL
344 NULL 1
345 drop table t1;
346 #
347 # bug#39918 - memory (heap) engine crashing while executing self join with delete
348 #
349 CREATE TABLE t1(a INT, KEY USING BTREE (a)) ENGINE=MEMORY;
350 INSERT INTO t1 VALUES(1),(1);
351 DELETE a1 FROM t1 AS a1, t1 AS a2 WHERE a1.a=a2.a;
352 DROP TABLE t1;
353 End of 5.0 tests
354 # bit index in heap tables
355 create table t1 (a bit(63) not null) engine=heap;
356 insert into t1 values (869751),(736494),(226312),(802616),(728912);
357 alter table t1 add unique uniq_id using BTREE (a);
358 select 0+a from t1 where a > 736494;
359 0+a
360 802616
361 869751
362 explain select 0+a from t1 where a > 736494;
363 id select_type table type possible_keys key key_len ref rows Extra
364 1 SIMPLE t1 range uniq_id uniq_id 8 NULL 3 Using where
365 select 0+a from t1 where a = 736494;
366 0+a
367 736494
368 explain select 0+a from t1 where a = 736494;
369 id select_type table type possible_keys key key_len ref rows Extra
370 1 SIMPLE t1 const uniq_id uniq_id 8 const 1
371 select 0+a from t1 where a=869751 or a=736494;
372 0+a
373 736494
374 869751
375 explain select 0+a from t1 where a=869751 or a=736494;
376 id select_type table type possible_keys key key_len ref rows Extra
377 1 SIMPLE t1 range uniq_id uniq_id 8 NULL 2 Using where
378 insert into t1 (a) select * from seq_1_to_100;
379 select 0+a from t1 where a in (869751,736494,226312,802616);
380 0+a
381 226312
382 736494
383 802616
384 869751
385 explain select 0+a from t1 where a in (869751,736494,226312,802616);
386 id select_type table type possible_keys key key_len ref rows Extra
387 1 SIMPLE t1 range uniq_id uniq_id 8 NULL 4 Using where
388 drop table t1;
389 End of 5.3 tests
390 create table t1 (id int, a varchar(300) not null, key using btree(a)) engine=heap;
391 insert t1 values (1, repeat('a', 300));
392 drop table t1;
393 End of 5.5 tests