« back to all changes in this revision
Viewing changes to tests/r/heap.result
- browse files at revision 520.1.8
- compare with another revision
- compare with revision 2456.1.5
- download diff
- download tarball
- view history from revision 520.1.8
- Committer: Brian Aker
- Date: 2008-10-18 15:43:20 UTC
- mto: (492.3.20 drizzle-clean-code)
- mto: This revision was merged to the branch mainline in revision 530.
- Revision ID: brian@tangent.org-20081018154320-jc9jyij3mdf08abp
Updating tests.
- files removed:
- tests/r/bootstrap.result
- files modified:
- tests/Makefile.am
added
removed
tests/r/heap.result
120
120
1 2
121
121
1 1
122
122
drop table t1;
123
create table t1 (id int unsigned not null, primary key (id)) engine=HEAP;
123
create table t1 (id int not null, primary key (id)) engine=HEAP;
124
124
insert into t1 values(1);
125
125
select max(id) from t1;
126
126
max(id)
169
169
1 SIMPLE t1 ALL btn NULL NULL NULL 14 Using where
170
170
explain select * from t1 where btn="a" and new_col="a";
171
171
id select_type table type possible_keys key key_len ref rows Extra
172
1 SIMPLE t1 ref btn btn 15 const,const 2 Using where
172
1 SIMPLE t1 ref btn btn 48 const,const 2 Using where
173
173
drop table t1;
174
174
CREATE TABLE t1 (
175
175
a int default NULL,
218
218
a
219
219
DROP TABLE t1;
220
220
CREATE TABLE `job_titles` (
221
`job_title_id` int unsigned NOT NULL default '0',
221
`job_title_id` int NOT NULL default '0',
222
222
`job_title` char(18) NOT NULL default '',
223
223
PRIMARY KEY (`job_title_id`),
224
224
UNIQUE KEY `job_title_id` (`job_title_id`,`job_title`)
233
233
a B
234
234
1 1
235
235
DROP TABLE t1;
236
CREATE TABLE t1 (pseudo char(35) PRIMARY KEY, date int unsigned NOT NULL) ENGINE=HEAP;
236
CREATE TABLE t1 (pseudo char(35) PRIMARY KEY, date int NOT NULL) ENGINE=HEAP;
237
237
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);
238
238
DELETE FROM t1 WHERE date<1101106546;
239
239
SELECT * FROM t1;
263
263
*+ *+ *+ *
264
264
show create table t1;
265
265
Table Create Table
266
t1 CREATE TABLE "t1" (
267
"v" varchar(10),
268
"c" varchar(10),
269
"t" varchar(50)
270
) ENGINE=MEMORY DEFAULT CHARSET=latin1
266
t1 CREATE TABLE `t1` (
267
`v` varchar(10),
268
`c` varchar(10),
269
`t` varchar(50)
270
) ENGINE=MEMORY
271
271
create table t2 like t1;
272
272
show create table t2;
273
273
Table Create Table
274
t2 CREATE TABLE "t2" (
275
"v" varchar(10),
276
"c" varchar(10),
277
"t" varchar(50)
278
) ENGINE=MEMORY DEFAULT CHARSET=latin1
274
t2 CREATE TABLE `t2` (
275
`v` varchar(10),
276
`c` varchar(10),
277
`t` varchar(50)
278
) ENGINE=MEMORY
279
279
create table t3 select * from t1;
280
280
show create table t3;
281
281
Table Create Table
282
t3 CREATE TABLE "t3" (
283
"v" varchar(10),
284
"c" varchar(10),
285
"t" varchar(50)
286
) ENGINE=MEMORY DEFAULT CHARSET=latin1
282
t3 CREATE TABLE `t3` (
283
`v` varchar(10),
284
`c` varchar(10),
285
`t` varchar(50)
286
) ENGINE=MEMORY
287
287
alter table t1 modify c varchar(10);
288
288
show create table t1;
289
289
Table Create Table
290
t1 CREATE TABLE "t1" (
291
"v" varchar(10),
292
"c" varchar(10),
293
"t" varchar(50)
294
) ENGINE=MEMORY DEFAULT CHARSET=latin1
290
t1 CREATE TABLE `t1` (
291
`v` varchar(10),
292
`c` varchar(10),
293
`t` varchar(50)
294
) ENGINE=MEMORY
295
295
alter table t1 modify v char(10);
296
296
show create table t1;
297
297
Table Create Table
298
t1 CREATE TABLE "t1" (
299
"v" varchar(10),
300
"c" varchar(10),
301
"t" varchar(50)
302
) ENGINE=MEMORY DEFAULT CHARSET=latin1
298
t1 CREATE TABLE `t1` (
299
`v` varchar(10),
300
`c` varchar(10),
301
`t` varchar(50)
302
) ENGINE=MEMORY
303
303
alter table t1 modify t varchar(50);
304
304
show create table t1;
305
305
Table Create Table
306
t1 CREATE TABLE "t1" (
307
"v" varchar(10),
308
"c" varchar(10),
309
"t" varchar(50)
310
) ENGINE=MEMORY DEFAULT CHARSET=latin1
306
t1 CREATE TABLE `t1` (
307
`v` varchar(10),
308
`c` varchar(10),
309
`t` varchar(50)
310
) ENGINE=MEMORY
311
311
select concat('*',v,'*',c,'*',t,'*') from t1;
312
312
concat('*',v,'*',c,'*',t,'*')
313
313
*+ *+ *+ *
316
316
create table t1 (v varchar(10), c char(10), t varchar(50), key(v), key(c), key(t(10)));
317
317
show create table t1;
318
318
Table Create Table
319
t1 CREATE TABLE "t1" (
320
"v" varchar(10),
321
"c" varchar(10),
322
"t" varchar(50),
323
KEY "v" ("v"),
324
KEY "c" ("c"),
325
KEY "t" ("t"(10))
326
) ENGINE=MEMORY DEFAULT CHARSET=latin1
319
t1 CREATE TABLE `t1` (
320
`v` varchar(10),
321
`c` varchar(10),
322
`t` varchar(50),
323
KEY `v` (`v`),
324
KEY `c` (`c`),
325
KEY `t` (`t`())
326
) ENGINE=MEMORY
327
327
select count(*) from t1;
328
328
count(*)
329
329
270
366
366
9
367
367
explain select count(*) from t1 where v='a ';
368
368
id select_type table type possible_keys key key_len ref rows Extra
369
1 SIMPLE t1 ref v v 13 const 10 Using where
369
1 SIMPLE t1 ref v v 43 const 10 Using where
370
370
explain select count(*) from t1 where c='a ';
371
371
id select_type table type possible_keys key key_len ref rows Extra
372
1 SIMPLE t1 ref c c 13 const 10 Using where
372
1 SIMPLE t1 ref c c 43 const 10 Using where
373
373
explain select count(*) from t1 where t='a ';
374
374
id select_type table type possible_keys key key_len ref rows Extra
375
1 SIMPLE t1 ref t t 13 const 10 Using where
375
1 SIMPLE t1 ref t t 43 const 10 Using where
376
376
explain select count(*) from t1 where v like 'a%';
377
377
id select_type table type possible_keys key key_len ref rows Extra
378
378
1 SIMPLE t1 ALL v NULL NULL NULL 271 Using where
379
379
explain select count(*) from t1 where v between 'a' and 'a ';
380
380
id select_type table type possible_keys key key_len ref rows Extra
381
1 SIMPLE t1 ref v v 13 const 10 Using where
381
1 SIMPLE t1 ref v v 43 const 10 Using where
382
382
explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
383
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
384
1 SIMPLE t1 ref v v 43 const 10 Using where
385
385
alter table t1 add unique(v);
386
386
ERROR 23000: Duplicate entry '{ ' for key 'v_2'
387
387
select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a' order by length(concat('*',v,'*',c,'*',t,'*'));
398
398
*a *a *a *
399
399
explain select * from t1 where v='a';
400
400
id select_type table type possible_keys key key_len ref rows Extra
401
1 SIMPLE t1 ref v v 13 const 10 Using where
401
1 SIMPLE t1 ref v v 43 const 10 Using where
402
402
select v,count(*) from t1 group by v limit 10;
403
403
v count(*)
404
404
a 1
483
483
g 10
484
484
h 10
485
485
i 10
486
select sql_big_result c,count(t) from t1 group by c limit 10;
487
c count(t)
488
a 1
489
a 10
490
b 10
491
c 10
492
d 10
493
e 10
494
f 10
495
g 10
496
h 10
497
i 10
498
486
select t,count(*) from t1 group by t limit 10;
499
487
t count(*)
500
488
a 1
551
539
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)));
552
540
show create table t1;
553
541
Table Create Table
554
t1 CREATE TABLE "t1" (
555
"v" varchar(10),
556
"c" varchar(10),
557
"t" varchar(50),
558
KEY "v" ("v") USING BTREE,
559
KEY "c" ("c") USING BTREE,
560
KEY "t" ("t"(10)) USING BTREE
561
) ENGINE=MEMORY DEFAULT CHARSET=latin1
542
t1 CREATE TABLE `t1` (
543
`v` varchar(10),
544
`c` varchar(10),
545
`t` varchar(50),
546
KEY `v` (`v`) USING BTREE,
547
KEY `c` (`c`) USING BTREE,
548
KEY `t` (`t`()) USING BTREE
549
) ENGINE=MEMORY
562
550
select count(*) from t1;
563
551
count(*)
564
552
270
589
577
10
590
578
explain select count(*) from t1 where v='a ';
591
579
id select_type table type possible_keys key key_len ref rows Extra
592
1 SIMPLE t1 ref v v 13 const # Using where
580
1 SIMPLE t1 ref v v 43 const # Using where
593
581
explain select count(*) from t1 where c='a ';
594
582
id select_type table type possible_keys key key_len ref rows Extra
595
1 SIMPLE t1 ref c c 13 const # Using where
583
1 SIMPLE t1 ref c c 43 const # Using where
596
584
explain select count(*) from t1 where t='a ';
597
585
id select_type table type possible_keys key key_len ref rows Extra
598
1 SIMPLE t1 ref t t 13 const # Using where
586
1 SIMPLE t1 ref t t 43 const # Using where
599
587
explain select count(*) from t1 where v like 'a%';
600
588
id select_type table type possible_keys key key_len ref rows Extra
601
1 SIMPLE t1 range v v 13 NULL # Using where
589
1 SIMPLE t1 range v v 43 NULL # Using where
602
590
explain select count(*) from t1 where v between 'a' and 'a ';
603
591
id select_type table type possible_keys key key_len ref rows Extra
604
1 SIMPLE t1 ref v v 13 const # Using where
592
1 SIMPLE t1 ref v v 43 const # Using where
605
593
explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
606
594
id select_type table type possible_keys key key_len ref rows Extra
607
1 SIMPLE t1 ref v v 13 const # Using where
595
1 SIMPLE t1 ref v v 43 const # Using where
608
596
alter table t1 add unique(v);
609
597
ERROR 23000: Duplicate entry '{ ' for key 'v_2'
610
598
select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a' order by length(concat('*',v,'*',c,'*',t,'*'));
621
609
*a *a *a *
622
610
explain select * from t1 where v='a';
623
611
id select_type table type possible_keys key key_len ref rows Extra
624
1 SIMPLE t1 ref v v 13 const # Using where
612
1 SIMPLE t1 ref v v 43 const # Using where
625
613
drop table t1;
626
614
create table t1 (a char(10), unique using btree (a)) engine=heap;
627
615
insert into t1 values ('a');
642
630
create table t1 (v varchar(10), c char(10), t varchar(50), key(v(5)), key(c(5)), key(t(5)));
643
631
show create table t1;
644
632
Table Create Table
645
t1 CREATE TABLE "t1" (
646
"v" varchar(10),
647
"c" varchar(10),
648
"t" varchar(50),
649
KEY "v" ("v"(5)),
650
KEY "c" ("c"(5)),
651
KEY "t" ("t"(5))
652
) ENGINE=MEMORY DEFAULT CHARSET=latin1
633
t1 CREATE TABLE `t1` (
634
`v` varchar(10),
635
`c` varchar(10),
636
`t` varchar(50),
637
KEY `v` (`v`()),
638
KEY `c` (`c`()),
639
KEY `t` (`t`())
640
) ENGINE=MEMORY
653
641
drop table t1;
654
create table t1 (v varchar(65530), key(v(10)));
642
create table t1 (v varchar(16383), key(v(10)));
655
643
show create table t1;
656
644
Table Create Table
657
t1 CREATE TABLE "t1" (
658
"v" varchar(65530),
659
KEY "v" ("v"(10))
660
) ENGINE=MEMORY DEFAULT CHARSET=latin1
661
insert into t1 values(repeat('a',65530));
662
select length(v) from t1 where v=repeat('a',65530);
645
t1 CREATE TABLE `t1` (
646
`v` varchar(16383),
647
KEY `v` (`v`())
648
) ENGINE=MEMORY
649
insert into t1 values(repeat('a',16383));
650
select length(v) from t1 where v=repeat('a',16383);
663
651
length(v)
664
65530
652
16383
665
653
drop table t1;
666
654
set storage_engine=InnoDB;
667
create table t1 (a bigint unsigned auto_increment primary key, b int,
655
create table t1 (a bigint auto_increment primary key, b int,
668
656
key (b, a)) engine=heap;
669
657
insert t1 (b) values (1),(1),(1),(1),(1),(1),(1),(1);
670
658
select * from t1;
714
702
create table t2 (c varchar(10)) engine=memory;
715
703
show table status like 't_';
716
704
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
717
t1 MEMORY 10 Fixed 0 12 0 # 0 0 NULL NULL NULL NULL latin1_swedish_ci NULL
718
t2 MEMORY 10 Fixed 0 12 0 # 0 0 NULL NULL NULL NULL latin1_swedish_ci NULL
705
t1 MEMORY 10 Fixed 0 42 0 # 0 0 NULL NULL NULL NULL utf8_general_ci NULL
706
t2 MEMORY 10 Fixed 0 42 0 # 0 0 NULL NULL NULL NULL utf8_general_ci NULL
719
707
drop table t1, t2;
720
708
CREATE TABLE t1(a VARCHAR(1), b VARCHAR(2), c VARCHAR(256),
721
709
KEY(a), KEY(b), KEY(c)) ENGINE=MEMORY;
Loggerhead is a web-based interface for Breezy
Version: 2.0.1