Debian Codegraph @

mysql-test/suite/heap/heap.result

raw
1 ALTER DATABASE test CHARACTER SET latin1 COLLATE latin1_swedish_ci;
2 drop table if exists t1,t2,t3;
3 create table t1 (a int not null,b int not null, primary key (a)) engine=heap comment="testing heaps" avg_row_length=100 min_rows=1 max_rows=100;
4 insert into t1 values(1,1),(2,2),(3,3),(4,4);
5 delete from t1 where a=1 or a=0;
6 show keys from t1;
7 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
8 t1 0 PRIMARY 1 a NULL 3 NULL NULL HASH NO
9 select * from t1;
10 a b
11 2 2
12 3 3
13 4 4
14 select * from t1 where a=4;
15 a b
16 4 4
17 update t1 set b=5 where a=4;
18 update t1 set b=b+1 where a>=3;
19 replace t1 values (3,3);
20 select * from t1;
21 a b
22 2 2
23 3 3
24 4 6
25 alter table t1 add c int not null, add key (c,a);
26 drop table t1;
27 create table t1 (a int not null,b int not null, primary key (a)) engine=memory comment="testing heaps";
28 insert into t1 values(1,1),(2,2),(3,3),(4,4);
29 delete from t1 where a > 0;
30 select * from t1;
31 a b
32 drop table t1;
33 create table t1 (a int not null,b int not null, primary key (a)) engine=heap comment="testing heaps";
34 insert into t1 values(1,1),(2,2),(3,3),(4,4);
35 alter table t1 modify a int not null auto_increment, engine=myisam, comment="new myisam table";
36 select * from t1;
37 a b
38 1 1
39 2 2
40 3 3
41 4 4
42 drop table t1;
43 create table t1 (a int not null) engine=heap;
44 insert into t1 values (869751),(736494),(226312),(802616),(728912);
45 select * from t1 where a > 736494;
46 a
47 869751
48 802616
49 alter table t1 add unique uniq_id(a);
50 select * from t1 where a > 736494;
51 a
52 869751
53 802616
54 select * from t1 where a = 736494;
55 a
56 736494
57 select * from t1 where a=869751 or a=736494;
58 a
59 736494
60 869751
61 select * from t1 where a in (869751,736494,226312,802616);
62 a
63 226312
64 736494
65 802616
66 869751
67 alter table t1 engine=myisam;
68 explain select * from t1 where a in (869751,736494,226312,802616);
69 id select_type table type possible_keys key key_len ref rows Extra
70 1 SIMPLE t1 range uniq_id uniq_id 4 NULL 4 Using where; Using index
71 drop table t1;
72 create table t1 (x int not null, y int not null, key x (x), unique y (y))
73 engine=heap;
74 insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6);
75 select * from t1 where x=1;
76 x y
77 1 3
78 1 1
79 select * from t1,t1 as t2 where t1.x=t2.y;
80 x y x y
81 1 1 1 1
82 2 2 2 2
83 1 3 1 1
84 2 4 2 2
85 2 5 2 2
86 2 6 2 2
87 explain select * from t1,t1 as t2 where t1.x=t2.y;
88 id select_type table type possible_keys key key_len ref rows Extra
89 1 SIMPLE t1 ALL x NULL NULL NULL 6
90 1 SIMPLE t2 eq_ref y y 4 test.t1.x 1
91 drop table t1;
92 create table t1 (a int) engine=heap;
93 insert into t1 values(1);
94 select max(a) from t1;
95 max(a)
96 1
97 drop table t1;
98 CREATE TABLE t1 ( a int not null default 0, b int not null default 0, key(a), key(b) ) ENGINE=HEAP;
99 insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6);
100 select * from t1 where a=1;
101 a b
102 1 6
103 1 5
104 1 4
105 1 3
106 1 2
107 1 1
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 6
112 1 5
113 1 4
114 1 3
115 1 2
116 1 1
117 1 6
118 1 5
119 1 4
120 1 3
121 1 2
122 1 1
123 drop table t1;
124 create table t1 (id int unsigned not null, primary key (id)) engine=HEAP;
125 insert into t1 values(1);
126 select max(id) from t1;
127 max(id)
128 1
129 insert into t1 values(2);
130 select max(id) from t1;
131 max(id)
132 2
133 replace into t1 values(1);
134 drop table t1;
135 create table t1 (n int) engine=heap;
136 drop table t1;
137 create table t1 (n int) engine=heap;
138 drop table if exists t1;
139 CREATE table t1(f1 int not null,f2 char(20) not
140 null,index(f2)) engine=heap;
141 INSERT into t1 set f1=12,f2="bill";
142 INSERT into t1 set f1=13,f2="bill";
143 INSERT into t1 set f1=14,f2="bill";
144 INSERT into t1 set f1=15,f2="bill";
145 INSERT into t1 set f1=16,f2="ted";
146 INSERT into t1 set f1=12,f2="ted";
147 INSERT into t1 set f1=12,f2="ted";
148 INSERT into t1 set f1=12,f2="ted";
149 INSERT into t1 set f1=12,f2="ted";
150 delete from t1 where f2="bill";
151 select * from t1;
152 f1 f2
153 16 ted
154 12 ted
155 12 ted
156 12 ted
157 12 ted
158 drop table t1;
159 create table t1 (btn char(10) not null, key(btn)) engine=heap;
160 insert into t1 values ("hello"),("hello"),("hello"),("hello"),("hello"),("a"),("b"),("c"),("d"),("e"),("f"),("g"),("h"),("i");
161 explain select * from t1 where btn like "q%";
162 id select_type table type possible_keys key key_len ref rows Extra
163 1 SIMPLE t1 ALL btn NULL NULL NULL 14 Using where
164 select * from t1 where btn like "q%";
165 btn
166 alter table t1 add column new_col char(1) not null, add key (btn,new_col), drop key btn;
167 update t1 set new_col=left(btn,1);
168 explain select * from t1 where btn="a";
169 id select_type table type possible_keys key key_len ref rows Extra
170 1 SIMPLE t1 ALL btn NULL NULL NULL 14 Using where
171 explain select * from t1 where btn="a" and new_col="a";
172 id select_type table type possible_keys key key_len ref rows Extra
173 1 SIMPLE t1 ref btn btn 11 const,const 2 Using where
174 drop table t1;
175 CREATE TABLE t1 (
176 a int default NULL,
177 b int default NULL,
178 KEY a (a),
179 UNIQUE b (b)
180 ) engine=heap;
181 INSERT INTO t1 VALUES (NULL,99),(99,NULL),(1,1),(2,2),(1,3);
182 SELECT * FROM t1 WHERE a=NULL;
183 a b
184 explain SELECT * FROM t1 WHERE a IS NULL;
185 id select_type table type possible_keys key key_len ref rows Extra
186 1 SIMPLE t1 ref a a 5 const 2 Using where
187 SELECT * FROM t1 WHERE a<=>NULL;
188 a b
189 NULL 99
190 SELECT * FROM t1 WHERE b=NULL;
191 a b
192 explain SELECT * FROM t1 WHERE b IS NULL;
193 id select_type table type possible_keys key key_len ref rows Extra
194 1 SIMPLE t1 ref b b 5 const 1 Using where
195 SELECT * FROM t1 WHERE b<=>NULL;
196 a b
197 99 NULL
198 INSERT INTO t1 VALUES (1,3);
199 ERROR 23000: Duplicate entry '3' for key 'b'
200 DROP TABLE t1;
201 CREATE TABLE t1 (
202 a int default NULL,
203 key a (a)
204 ) ENGINE=HEAP;
205 INSERT INTO t1 VALUES (10), (10), (10);
206 EXPLAIN SELECT * FROM t1 WHERE a=10;
207 id select_type table type possible_keys key key_len ref rows Extra
208 1 SIMPLE t1 ref a a 5 const 3
209 SELECT * FROM t1 WHERE a=10;
210 a
211 10
212 10
213 10
214 DROP TABLE t1;
215 CREATE TABLE t1 (a int not null, primary key(a)) engine=heap;
216 INSERT into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11);
217 DELETE from t1 where a < 100;
218 SELECT * from t1;
219 a
220 DROP TABLE t1;
221 CREATE TABLE `job_titles` (
222 `job_title_id` int(6) unsigned NOT NULL default '0',
223 `job_title` char(18) NOT NULL default '',
224 PRIMARY KEY (`job_title_id`),
225 UNIQUE KEY `job_title_id` (`job_title_id`,`job_title`)
226 ) ENGINE=HEAP;
227 SELECT MAX(job_title_id) FROM job_titles;
228 MAX(job_title_id)
229 NULL
230 DROP TABLE job_titles;
231 CREATE TABLE t1 (a INT NOT NULL, B INT, KEY(B)) ENGINE=HEAP;
232 INSERT INTO t1 VALUES(1,1), (1,NULL);
233 SELECT * FROM t1 WHERE B is not null;
234 a B
235 1 1
236 DROP TABLE t1;
237 CREATE TABLE t1 (pseudo char(35) PRIMARY KEY, date int(10) unsigned NOT NULL) ENGINE=HEAP;
238 INSERT INTO t1 VALUES ('massecot',1101106491),('altec',1101106492),('stitch+',1101106304),('Seb Corgan',1101106305),('beerfilou',1101106263),('flaker',1101106529),('joce8',5),('M4vrick',1101106418),('gabay008',1101106525),('Vamp irX',1101106291),('ZoomZip',1101106546),('rip666',1101106502),('CBP ',1101106397),('guezpard',1101106496);
239 DELETE FROM t1 WHERE date<1101106546;
240 SELECT * FROM t1;
241 pseudo date
242 ZoomZip 1101106546
243 DROP TABLE t1;
244 create table t1(a char(2)) engine=memory;
245 insert into t1 values (NULL), (NULL);
246 delete from t1 where a is null;
247 insert into t1 values ('2'), ('3');
248 select * from t1;
249 a
250 3
251 2
252 drop table t1;
253 set default_storage_engine=HEAP;
254 create table t1 (v varchar(10), c char(10), t varchar(50));
255 insert into t1 values('+ ', '+ ', '+ ');
256 set @a=repeat(' ',20);
257 insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a));
258 Warnings:
259 Note 1265 Data truncated for column 'v' at row 1
260 select concat('*',v,'*',c,'*',t,'*') from t1;
261 concat('*',v,'*',c,'*',t,'*')
262 *+ *+*+ *
263 *+ *+*+ *
264 show create table t1;
265 Table Create Table
266 t1 CREATE TABLE `t1` (
267 `v` varchar(10) DEFAULT NULL,
268 `c` char(10) DEFAULT NULL,
269 `t` varchar(50) DEFAULT NULL
270 ) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
271 create table t2 like t1;
272 show create table t2;
273 Table Create Table
274 t2 CREATE TABLE `t2` (
275 `v` varchar(10) DEFAULT NULL,
276 `c` char(10) DEFAULT NULL,
277 `t` varchar(50) DEFAULT NULL
278 ) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
279 create table t3 select * from t1;
280 show create table t3;
281 Table Create Table
282 t3 CREATE TABLE `t3` (
283 `v` varchar(10) DEFAULT NULL,
284 `c` char(10) DEFAULT NULL,
285 `t` varchar(50) DEFAULT NULL
286 ) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
287 alter table t1 modify c varchar(10);
288 show create table t1;
289 Table Create Table
290 t1 CREATE TABLE `t1` (
291 `v` varchar(10) DEFAULT NULL,
292 `c` varchar(10) DEFAULT NULL,
293 `t` varchar(50) DEFAULT NULL
294 ) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
295 alter table t1 modify v char(10);
296 show create table t1;
297 Table Create Table
298 t1 CREATE TABLE `t1` (
299 `v` char(10) DEFAULT NULL,
300 `c` varchar(10) DEFAULT NULL,
301 `t` varchar(50) DEFAULT NULL
302 ) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
303 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
304 alter table t1 modify t varchar(10);
305 Warnings:
306 Warning 1265 Data truncated for column 't' at row 2
307 show create table t1;
308 Table Create Table
309 t1 CREATE TABLE `t1` (
310 `v` char(10) DEFAULT NULL,
311 `c` varchar(10) DEFAULT NULL,
312 `t` varchar(10) DEFAULT NULL
313 ) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
314 select concat('*',v,'*',c,'*',t,'*') from t1;
315 concat('*',v,'*',c,'*',t,'*')
316 *+*+*+ *
317 *+*+*+ *
318 drop table t1,t2,t3;
319 create table t1 (v varchar(10), c char(10), t varchar(50), key(v), key(c), key(t(10)));
320 show create table t1;
321 Table Create Table
322 t1 CREATE TABLE `t1` (
323 `v` varchar(10) DEFAULT NULL,
324 `c` char(10) DEFAULT NULL,
325 `t` varchar(50) DEFAULT NULL,
326 KEY `v` (`v`),
327 KEY `c` (`c`),
328 KEY `t` (`t`(10))
329 ) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
330 select count(*) from t1;
331 count(*)
332 270
333 insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1)));
334 select count(*) from t1 where v='a';
335 count(*)
336 10
337 select count(*) from t1 where c='a';
338 count(*)
339 10
340 select count(*) from t1 where t='a';
341 count(*)
342 10
343 select count(*) from t1 where v='a ';
344 count(*)
345 10
346 select count(*) from t1 where c='a ';
347 count(*)
348 10
349 select count(*) from t1 where t='a ';
350 count(*)
351 10
352 select count(*) from t1 where v between 'a' and 'a ';
353 count(*)
354 10
355 select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
356 count(*)
357 10
358 select count(*) from t1 where v like 'a%';
359 count(*)
360 11
361 select count(*) from t1 where c like 'a%';
362 count(*)
363 11
364 select count(*) from t1 where t like 'a%';
365 count(*)
366 11
367 select count(*) from t1 where v like 'a %';
368 count(*)
369 9
370 explain select count(*) from t1 where v='a ';
371 id select_type table type possible_keys key key_len ref rows Extra
372 1 SIMPLE t1 ref v v 13 const 10 Using where
373 explain select count(*) from t1 where c='a ';
374 id select_type table type possible_keys key key_len ref rows Extra
375 1 SIMPLE t1 ref c c 11 const 10 Using where
376 explain select count(*) from t1 where t='a ';
377 id select_type table type possible_keys key key_len ref rows Extra
378 1 SIMPLE t1 ref t t 13 const 10 Using where
379 explain select count(*) from t1 where v like 'a%';
380 id select_type table type possible_keys key key_len ref rows Extra
381 1 SIMPLE t1 ALL v NULL NULL NULL 271 Using where
382 explain select count(*) from t1 where v between 'a' and 'a ';
383 id select_type table type possible_keys key key_len ref rows Extra
384 1 SIMPLE t1 ref v v 13 const 10 Using where
385 explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
386 id select_type table type possible_keys key key_len ref rows Extra
387 1 SIMPLE t1 ref v v 13 const 10 Using where
388 alter table t1 add unique(v);
389 ERROR 23000: Duplicate entry '{ ' for key 'v_2'
390 select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a' order by length(concat('*',v,'*',c,'*',t,'*'));
391 qq
392 *a*a*a*
393 *a *a*a *
394 *a *a*a *
395 *a *a*a *
396 *a *a*a *
397 *a *a*a *
398 *a *a*a *
399 *a *a*a *
400 *a *a*a *
401 *a *a*a *
402 flush tables;
403 explain select * from t1 where v='a';
404 id select_type table type possible_keys key key_len ref rows Extra
405 1 SIMPLE t1 ref v v 13 const 9 Using where
406 select v,count(*) from t1 group by v limit 10;
407 v count(*)
408 a 1
409 a 10
410 b 10
411 c 10
412 d 10
413 e 10
414 f 10
415 g 10
416 h 10
417 i 10
418 select v,count(t) from t1 group by v limit 10;
419 v count(t)
420 a 1
421 a 10
422 b 10
423 c 10
424 d 10
425 e 10
426 f 10
427 g 10
428 h 10
429 i 10
430 select v,count(c) from t1 group by v limit 10;
431 v count(c)
432 a 1
433 a 10
434 b 10
435 c 10
436 d 10
437 e 10
438 f 10
439 g 10
440 h 10
441 i 10
442 select sql_big_result trim(v),count(t) from t1 group by v limit 10;
443 trim(v) count(t)
444 a 1
445 a 10
446 b 10
447 c 10
448 d 10
449 e 10
450 f 10
451 g 10
452 h 10
453 i 10
454 select sql_big_result trim(v),count(c) from t1 group by v limit 10;
455 trim(v) count(c)
456 a 1
457 a 10
458 b 10
459 c 10
460 d 10
461 e 10
462 f 10
463 g 10
464 h 10
465 i 10
466 select c,count(*) from t1 group by c limit 10;
467 c count(*)
468 a 1
469 a 10
470 b 10
471 c 10
472 d 10
473 e 10
474 f 10
475 g 10
476 h 10
477 i 10
478 select c,count(t) from t1 group by c limit 10;
479 c count(t)
480 a 1
481 a 10
482 b 10
483 c 10
484 d 10
485 e 10
486 f 10
487 g 10
488 h 10
489 i 10
490 select sql_big_result c,count(t) from t1 group by c limit 10;
491 c count(t)
492 a 1
493 a 10
494 b 10
495 c 10
496 d 10
497 e 10
498 f 10
499 g 10
500 h 10
501 i 10
502 select t,count(*) from t1 group by t limit 10;
503 t count(*)
504 a 1
505 a 10
506 b 10
507 c 10
508 d 10
509 e 10
510 f 10
511 g 10
512 h 10
513 i 10
514 select t,count(t) from t1 group by t limit 10;
515 t count(t)
516 a 1
517 a 10
518 b 10
519 c 10
520 d 10
521 e 10
522 f 10
523 g 10
524 h 10
525 i 10
526 select sql_big_result trim(t),count(t) from t1 group by t limit 10;
527 trim(t) count(t)
528 a 1
529 a 10
530 b 10
531 c 10
532 d 10
533 e 10
534 f 10
535 g 10
536 h 10
537 i 10
538 drop table t1;
539 create table t1 (a char(10), unique (a));
540 insert into t1 values ('a');
541 insert into t1 values ('a ');
542 ERROR 23000: Duplicate entry 'a' for key 'a'
543 alter table t1 modify a varchar(10);
544 insert into t1 values ('a '),('a '),('a '),('a ');
545 ERROR 23000: Duplicate entry 'a ' for key 'a'
546 insert into t1 values ('a ');
547 ERROR 23000: Duplicate entry 'a ' for key 'a'
548 insert into t1 values ('a ');
549 ERROR 23000: Duplicate entry 'a ' for key 'a'
550 insert into t1 values ('a ');
551 ERROR 23000: Duplicate entry 'a ' for key 'a'
552 update t1 set a='a ' where a like 'a ';
553 update t1 set a='a ' where a like 'a ';
554 drop table t1;
555 create table t1 (v varchar(10), c char(10), t varchar(50), key using btree (v), key using btree (c), key using btree (t(10)));
556 show create table t1;
557 Table Create Table
558 t1 CREATE TABLE `t1` (
559 `v` varchar(10) DEFAULT NULL,
560 `c` char(10) DEFAULT NULL,
561 `t` varchar(50) DEFAULT NULL,
562 KEY `v` (`v`) USING BTREE,
563 KEY `c` (`c`) USING BTREE,
564 KEY `t` (`t`(10)) USING BTREE
565 ) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
566 select count(*) from t1;
567 count(*)
568 267
569 insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1)));
570 select count(*) from t1 where v='a';
571 count(*)
572 7
573 select count(*) from t1 where c='a';
574 count(*)
575 7
576 select count(*) from t1 where t='a';
577 count(*)
578 7
579 select count(*) from t1 where v='a ';
580 count(*)
581 7
582 select count(*) from t1 where c='a ';
583 count(*)
584 7
585 select count(*) from t1 where t='a ';
586 count(*)
587 7
588 select count(*) from t1 where v between 'a' and 'a ';
589 count(*)
590 7
591 select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
592 count(*)
593 7
594 explain select count(*) from t1 where v='a ';
595 id select_type table type possible_keys key key_len ref rows Extra
596 1 SIMPLE t1 ref v v 13 const # Using where
597 explain select count(*) from t1 where c='a ';
598 id select_type table type possible_keys key key_len ref rows Extra
599 1 SIMPLE t1 ref c c 11 const # Using where
600 explain select count(*) from t1 where t='a ';
601 id select_type table type possible_keys key key_len ref rows Extra
602 1 SIMPLE t1 ref t t 13 const # Using where
603 explain select count(*) from t1 where v like 'a%';
604 id select_type table type possible_keys key key_len ref rows Extra
605 1 SIMPLE t1 range v v 13 NULL # Using where
606 explain select count(*) from t1 where v between 'a' and 'a ';
607 id select_type table type possible_keys key key_len ref rows Extra
608 1 SIMPLE t1 ref v v 13 const # Using where
609 explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
610 id select_type table type possible_keys key key_len ref rows Extra
611 1 SIMPLE t1 ref v v 13 const # Using where
612 alter table t1 add unique(v);
613 ERROR 23000: Duplicate entry '{ ' for key 'v_2'
614 select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a' order by length(concat('*',v,'*',c,'*',t,'*'));
615 qq
616 *a*a*a*
617 *a *a*a *
618 *a *a*a *
619 *a *a*a *
620 *a *a*a *
621 *a *a*a *
622 *a *a*a *
623 explain select * from t1 where v='a';
624 id select_type table type possible_keys key key_len ref rows Extra
625 1 SIMPLE t1 ref v v 13 const # Using where
626 drop table t1;
627 create table t1 (a char(10), unique using btree (a)) engine=heap;
628 insert into t1 values ('a');
629 insert into t1 values ('a ');
630 ERROR 23000: Duplicate entry 'a' for key 'a'
631 alter table t1 modify a varchar(10);
632 insert into t1 values ('a '),('a '),('a '),('a ');
633 ERROR 23000: Duplicate entry 'a ' for key 'a'
634 insert into t1 values ('a ');
635 ERROR 23000: Duplicate entry 'a ' for key 'a'
636 insert into t1 values ('a ');
637 ERROR 23000: Duplicate entry 'a ' for key 'a'
638 insert into t1 values ('a ');
639 ERROR 23000: Duplicate entry 'a ' for key 'a'
640 update t1 set a='a ' where a like 'a ';
641 update t1 set a='a ' where a like 'a ';
642 drop table t1;
643 create table t1 (v varchar(10), c char(10), t varchar(50), key(v(5)), key(c(5)), key(t(5)));
644 show create table t1;
645 Table Create Table
646 t1 CREATE TABLE `t1` (
647 `v` varchar(10) DEFAULT NULL,
648 `c` char(10) DEFAULT NULL,
649 `t` varchar(50) DEFAULT NULL,
650 KEY `v` (`v`(5)),
651 KEY `c` (`c`(5)),
652 KEY `t` (`t`(5))
653 ) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
654 drop table t1;
655 create table t1 (v varchar(65530), key(v(10)));
656 show create table t1;
657 Table Create Table
658 t1 CREATE TABLE `t1` (
659 `v` varchar(65530) DEFAULT NULL,
660 KEY `v` (`v`(10))
661 ) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
662 insert into t1 values(repeat('a',65530));
663 select length(v) from t1 where v=repeat('a',65530);
664 length(v)
665 65530
666 drop table t1;
667 set default_storage_engine=MyISAM;
668 create table t1 (a bigint unsigned auto_increment primary key, b int,
669 key (b, a)) engine=heap;
670 insert t1 (b) values (1),(1),(1),(1),(1),(1),(1),(1);
671 select * from t1;
672 a b
673 1 1
674 2 1
675 3 1
676 4 1
677 5 1
678 6 1
679 7 1
680 8 1
681 drop table t1;
682 create table t1 (a int not null, b int not null auto_increment,
683 primary key(a, b), key(b)) engine=heap;
684 insert t1 (a) values (1),(1),(1),(1),(1),(1),(1),(1);
685 select * from t1;
686 a b
687 1 1
688 1 2
689 1 3
690 1 4
691 1 5
692 1 6
693 1 7
694 1 8
695 drop table t1;
696 create table t1 (a int not null, b int not null auto_increment,
697 primary key(a, b)) engine=heap;
698 ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key
699 create table t1 (c char(255), primary key(c(90)));
700 insert into t1 values ("abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz");
701 insert into t1 values ("abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz");
702 ERROR 23000: Duplicate entry 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghi...' for key 'PRIMARY'
703 drop table t1;
704 CREATE TABLE t1 (a int, key(a)) engine=heap;
705 insert into t1 values (0);
706 delete from t1;
707 select * from t1;
708 a
709 insert into t1 values (0), (1);
710 select * from t1 where a = 0;
711 a
712 0
713 drop table t1;
714 create table t1 (c char(10)) engine=memory;
715 create table t2 (c varchar(10)) engine=memory;
716 show table status like 't_';
717 Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary
718 t1 MEMORY 10 Fixed 0 11 0 # 0 0 NULL # NULL NULL latin1_swedish_ci NULL 0 N
719 t2 MEMORY 10 Fixed 0 12 0 # 0 0 NULL # NULL NULL latin1_swedish_ci NULL 0 N
720 drop table t1, t2;
721 CREATE TABLE t1(a VARCHAR(1), b VARCHAR(2), c VARCHAR(256),
722 KEY(a), KEY(b), KEY(c)) ENGINE=MEMORY;
723 INSERT INTO t1 VALUES('a','aa',REPEAT('a', 256)),('a','aa',REPEAT('a',256));
724 SELECT COUNT(*) FROM t1 WHERE a='a';
725 COUNT(*)
726 2
727 SELECT COUNT(*) FROM t1 WHERE b='aa';
728 COUNT(*)
729 2
730 SELECT COUNT(*) FROM t1 WHERE c=REPEAT('a',256);
731 COUNT(*)
732 2
733 DROP TABLE t1;
734 CREATE TABLE t1(c1 VARCHAR(100), c2 INT) ENGINE=MEMORY;
735 INSERT INTO t1 VALUES('', 0);
736 ALTER TABLE t1 MODIFY c1 VARCHAR(101);
737 SELECT c2 FROM t1;
738 c2
739 0
740 DROP TABLE t1;
741 CREATE TABLE t1 (
742 id int(11) NOT NULL AUTO_INCREMENT,
743 color enum('GREEN', 'WHITE') DEFAULT NULL,
744 ts int,
745 PRIMARY KEY (id),
746 KEY color (color) USING HASH
747 ) ENGINE=MEMORY DEFAULT CHARSET=utf8;
748 INSERT INTO t1 VALUES("1","GREEN",1);
749 INSERT INTO t1 VALUES("2","GREEN",1);
750 INSERT INTO t1 VALUES("3","GREEN",1);
751 INSERT INTO t1 VALUES("4","GREEN",1);
752 INSERT INTO t1 VALUES("5","GREEN",1);
753 INSERT INTO t1 VALUES("6","GREEN",1);
754 DELETE FROM t1 WHERE id = 1;
755 INSERT INTO t1 VALUES("7","GREEN", 2);
756 DELETE FROM t1 WHERE ts = 1 AND color = 'GREEN';
757 SELECT * from t1;
758 id color ts
759 7 GREEN 2
760 DROP TABLE t1;
761 CREATE TABLE t1 (id INT);
762 INSERT INTO t1 VALUES (1);
763 INSERT INTO t1 VALUES (2);
764 SET @@max_heap_table_size = 1024*1024*1024*20;
765 CREATE TEMPORARY TABLE tmp ENGINE=MEMORY
766 SELECT id FROM t1;
767 DROP TEMPORARY TABLE tmp;
768 drop table t1;
769 #
770 # BUG#11825482: Broken key length calculation for btree index
771 #
772 CREATE TABLE h1 (f1 VARCHAR(1), f2 INT NOT NULL,
773 UNIQUE KEY h1i (f1,f2) USING BTREE ) ENGINE=HEAP;
774 INSERT INTO h1 VALUES(NULL,0),(NULL,1);
775 SELECT 'wrong' as 'result' FROM dual WHERE ('h', 0) NOT IN (SELECT * FROM h1);
776 result
777 CREATE TABLE t1 (
778 pk int NOT NULL,
779 col_int_nokey INT,
780 col_varchar_nokey VARCHAR(1),
781 PRIMARY KEY (pk)
782 );
783 INSERT INTO t1 VALUES (19,5,'h'),(20,5,'h');
784 CREATE TABLE t2 (col_int_nokey INT);
785 INSERT INTO t2 VALUES (1),(2);
786 CREATE VIEW v1 AS
787 SELECT col_varchar_nokey, COUNT( col_varchar_nokey )
788 FROM t1
789 WHERE col_int_nokey <= 141 AND pk <= 4
790 ;
791 SELECT col_int_nokey FROM t2
792 WHERE ('h', 0) NOT IN ( SELECT * FROM v1);
793 col_int_nokey
794 # shouldn't crash
795 EXPLAIN SELECT col_int_nokey FROM t2
796 WHERE ('h', 0) NOT IN ( SELECT * FROM v1);
797 id select_type table type possible_keys key key_len ref rows Extra
798 1 PRIMARY t2 ALL NULL NULL NULL NULL 2
799 2 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where
800 3 DERIVED t1 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using where
801 DROP TABLE t1,t2,h1;
802 DROP VIEW v1;
803 CREATE TABLE t1 (id INT);
804 INSERT INTO t1 VALUES (1);
805 INSERT INTO t1 VALUES (2);
806 SET @@max_heap_table_size = 1024*1024*1024*20;
807 CREATE TEMPORARY TABLE tmp ENGINE=MEMORY
808 SELECT id FROM t1;
809 DROP TEMPORARY TABLE tmp;
810 drop table t1;
811 CREATE TABLE t1 (
812 id int(11) NOT NULL AUTO_INCREMENT,
813 color enum('GREEN', 'WHITE') DEFAULT NULL,
814 ts int,
815 PRIMARY KEY (id),
816 KEY color (color) USING HASH
817 ) ENGINE=MEMORY DEFAULT CHARSET=utf8;
818 INSERT INTO t1 VALUES("1","GREEN",1);
819 INSERT INTO t1 VALUES("2","GREEN",1);
820 INSERT INTO t1 VALUES("3","GREEN",1);
821 INSERT INTO t1 VALUES("4","GREEN",1);
822 INSERT INTO t1 VALUES("5","GREEN",1);
823 INSERT INTO t1 VALUES("6","GREEN",1);
824 DELETE FROM t1 WHERE id = 1;
825 INSERT INTO t1 VALUES("7","GREEN", 2);
826 DELETE FROM t1 WHERE ts = 1 AND color = 'GREEN';
827 SELECT * from t1 WHERE ts = 1 AND color = 'GREEN';
828 id color ts
829 DROP TABLE t1;
830 #
831 # MDEV-22695 Server crashes in heap_rnext upon DELETE from a HEAP table
832 #
833 CREATE TABLE t1 (a VARCHAR(128), b VARCHAR(32), KEY(a) USING BTREE, KEY(b) USING BTREE) ENGINE=HEAP;
834 INSERT INTO t1 VALUES ('foo',NULL),('m','b'),(6,'j'),('bar','qux'),(NULL,NULL);
835 DELETE FROM t1 WHERE a <=> 'm' OR b <=> NULL;
836 DROP TABLE t1;
837 #
838 # MDEV-28130 MariaDB SEGV issue at tree_search_next
839 #
840 CREATE TABLE v(t1 INT, pk INT, KEY(t1), KEY pk using btree (pk), KEY v using btree(t1, pk)) engine=memory;
841 HANDLER v OPEN;
842 HANDLER v READ t1=(2) limit 3;
843 t1 pk
844 HANDLER v READ pk PREV;
845 t1 pk
846 HANDLER v READ pk PREV;
847 t1 pk
848 drop table v;
849 ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;