mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-02-04 21:02:17 +01:00
Code Issues Projects Releases Packages Wiki Activity Actions
mariadb/mysql-test/suite/heap/heap.result
Monty 52c29f3bdc MDEV-35469 Heap tables are calling mallocs to often 
Heap tables are allocated blocks to store rows according to
my_default_record_cache (mapped to the server global variable
 read_buffer_size).
This causes performance issues when the record length is big
(> 1000 bytes) and the my_default_record_cache is small.

Changed to instead split the default heap allocation to 1/16 of the
allowed space and not use my_default_record_cache anymore when creating
the heap. The allocation is also aligned to be just under a power of 2.

For some test that I have been running, which was using record length=633,
the speed of the query doubled thanks to this change.

Other things:
- Fixed calculation of max_records passed to hp_create() to take
  into account padding between records.
- Updated calculation of memory needed by heap tables. Before we
  did not take into account internal structures needed to access rows.
- Changed block sized for memory_table from 1 to 16384 to get less
  fragmentation. This also avoids a problem where we need 1K
  to manage index and row storage which was not counted for before.
- Moved heap memory usage to a separate test for 32 bit.
- Allocate all data blocks in heap in powers of 2. Change reported
  memory usage for heap to reflect this.

Reviewed-by: Sergei Golubchik <serg@mariadb.org>
2025-01-05 16:40:11 +02:00

827 lines
22 KiB
Text
Raw Permalink Blame History

This file contains invisible Unicode characters

This file contains invisible Unicode characters that are indistinguishable to humans but may be processed differently by a computer. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

drop table if exists t1,t2,t3;
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;
insert into t1 values(1,1),(2,2),(3,3),(4,4);
delete from t1 where a=1 or a=0;
show keys from t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
t1 0 PRIMARY 1 a NULL 3 NULL NULL HASH NO
select * from t1;
a b
2 2
3 3
4 4
select * from t1 where a=4;
a b
4 4
update t1 set b=5 where a=4;
update t1 set b=b+1 where a>=3;
replace t1 values (3,3);
select * from t1;
a b
2 2
3 3
4 6
alter table t1 add c int not null, add key (c,a);
drop table t1;
create table t1 (a int not null,b int not null, primary key (a)) engine=memory comment="testing heaps";
insert into t1 values(1,1),(2,2),(3,3),(4,4);
delete from t1 where a > 0;
select * from t1;
a b
drop table t1;
create table t1 (a int not null,b int not null, primary key (a)) engine=heap comment="testing heaps";
insert into t1 values(1,1),(2,2),(3,3),(4,4);
alter table t1 modify a int not null auto_increment, engine=myisam, comment="new myisam table";
select * from t1;
a b
1 1
2 2
3 3
4 4
drop table t1;
create table t1 (a int not null) engine=heap;
insert into t1 values (869751),(736494),(226312),(802616),(728912);
select * from t1 where a > 736494;
a
869751
802616
alter table t1 add unique uniq_id(a);
select * from t1 where a > 736494;
a
869751
802616
select * from t1 where a = 736494;
a
736494
select * from t1 where a=869751 or a=736494;
a
736494
869751
select * from t1 where a in (869751,736494,226312,802616);
a
226312
736494
802616
869751
alter table t1 engine=myisam;
explain select * from t1 where a in (869751,736494,226312,802616);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range uniq_id uniq_id 4 NULL 4 Using where; Using index
drop table t1;
create table t1 (x int not null, y int not null, key x (x), unique y (y))
engine=heap;
insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6);
select * from t1 where x=1;
x y
1 3
1 1
select * from t1,t1 as t2 where t1.x=t2.y;
x y x y
1 1 1 1
2 2 2 2
1 3 1 1
2 4 2 2
2 5 2 2
2 6 2 2
explain select * from t1,t1 as t2 where t1.x=t2.y;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL x NULL NULL NULL 6
1 SIMPLE t2 eq_ref y y 4 test.t1.x 1
drop table t1;
create table t1 (a int) engine=heap;
insert into t1 values(1);
select max(a) from t1;
max(a)
1
drop table t1;
CREATE TABLE t1 ( a int not null default 0, b int not null default 0, key(a), key(b) ) ENGINE=HEAP;
insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6);
select * from t1 where a=1;
a b
1 6
1 5
1 4
1 3
1 2
1 1
insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6);
select * from t1 where a=1;
a b
1 6
1 5
1 4
1 3
1 2
1 1
1 6
1 5
1 4
1 3
1 2
1 1
drop table t1;
create table t1 (id int unsigned not null, primary key (id)) engine=HEAP;
insert into t1 values(1);
select max(id) from t1;
max(id)
1
insert into t1 values(2);
select max(id) from t1;
max(id)
2
replace into t1 values(1);
drop table t1;
create table t1 (n int) engine=heap;
drop table t1;
create table t1 (n int) engine=heap;
drop table if exists t1;
CREATE table t1(f1 int not null,f2 char(20) not
null,index(f2)) engine=heap;
INSERT into t1 set f1=12,f2="bill";
INSERT into t1 set f1=13,f2="bill";
INSERT into t1 set f1=14,f2="bill";
INSERT into t1 set f1=15,f2="bill";
INSERT into t1 set f1=16,f2="ted";
INSERT into t1 set f1=12,f2="ted";
INSERT into t1 set f1=12,f2="ted";
INSERT into t1 set f1=12,f2="ted";
INSERT into t1 set f1=12,f2="ted";
delete from t1 where f2="bill";
select * from t1;
f1 f2
16 ted
12 ted
12 ted
12 ted
12 ted
drop table t1;
create table t1 (btn char(10) not null, key(btn)) engine=heap;
insert into t1 values ("hello"),("hello"),("hello"),("hello"),("hello"),("a"),("b"),("c"),("d"),("e"),("f"),("g"),("h"),("i");
explain select * from t1 where btn like "q%";
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL btn NULL NULL NULL 14 Using where
select * from t1 where btn like "q%";
btn
alter table t1 add column new_col char(1) not null, add key (btn,new_col), drop key btn;
update t1 set new_col=left(btn,1);
explain select * from t1 where btn="a";
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL btn NULL NULL NULL 14 Using where
explain select * from t1 where btn="a" and new_col="a";
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref btn btn 11 const,const 2 Using where
drop table t1;
CREATE TABLE t1 (
a int default NULL,
b int default NULL,
KEY a (a),
UNIQUE b (b)
) engine=heap;
INSERT INTO t1 VALUES (NULL,99),(99,NULL),(1,1),(2,2),(1,3);
SELECT * FROM t1 WHERE a=NULL;
a b
explain SELECT * FROM t1 WHERE a IS NULL;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref a a 5 const 2 Using where
SELECT * FROM t1 WHERE a<=>NULL;
a b
NULL 99
SELECT * FROM t1 WHERE b=NULL;
a b
explain SELECT * FROM t1 WHERE b IS NULL;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref b b 5 const 1 Using where
SELECT * FROM t1 WHERE b<=>NULL;
a b
99 NULL
INSERT INTO t1 VALUES (1,3);
ERROR 23000: Duplicate entry '3' for key 'b'
DROP TABLE t1;
CREATE TABLE t1 (
a int default NULL,
key a (a)
) ENGINE=HEAP;
INSERT INTO t1 VALUES (10), (10), (10);
EXPLAIN SELECT * FROM t1 WHERE a=10;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref a a 5 const 3
SELECT * FROM t1 WHERE a=10;
a
10
10
10
DROP TABLE t1;
CREATE TABLE t1 (a int not null, primary key(a)) engine=heap;
INSERT into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11);
DELETE from t1 where a < 100;
SELECT * from t1;
a
DROP TABLE t1;
CREATE TABLE `job_titles` (
`job_title_id` int(6) unsigned NOT NULL default '0',
`job_title` char(18) NOT NULL default '',
PRIMARY KEY (`job_title_id`),
UNIQUE KEY `job_title_id` (`job_title_id`,`job_title`)
) ENGINE=HEAP;
SELECT MAX(job_title_id) FROM job_titles;
MAX(job_title_id)
NULL
DROP TABLE job_titles;
CREATE TABLE t1 (a INT NOT NULL, B INT, KEY(B)) ENGINE=HEAP;
INSERT INTO t1 VALUES(1,1), (1,NULL);
SELECT * FROM t1 WHERE B is not null;
a B
1 1
DROP TABLE t1;
CREATE TABLE t1 (pseudo char(35) PRIMARY KEY, date int(10) unsigned NOT NULL) ENGINE=HEAP;
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);
DELETE FROM t1 WHERE date<1101106546;
SELECT * FROM t1;
pseudo date
ZoomZip 1101106546
DROP TABLE t1;
create table t1(a char(2)) engine=memory;
insert into t1 values (NULL), (NULL);
delete from t1 where a is null;
insert into t1 values ('2'), ('3');
select * from t1;
a
3
2
drop table t1;
set default_storage_engine=HEAP;
create table t1 (v varchar(10), c char(10), t varchar(50));
insert into t1 values('+ ', '+ ', '+ ');
set @a=repeat(' ',20);
insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a));
Warnings:
Note 1265 Data truncated for column 'v' at row 1
select concat('*',v,'*',c,'*',t,'*') from t1;
concat('*',v,'*',c,'*',t,'*')
*+ *+*+ *
*+ *+*+ *
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`v` varchar(10) DEFAULT NULL,
`c` char(10) DEFAULT NULL,
`t` varchar(50) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
create table t2 like t1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`v` varchar(10) DEFAULT NULL,
`c` char(10) DEFAULT NULL,
`t` varchar(50) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
create table t3 select * from t1;
show create table t3;
Table Create Table
t3 CREATE TABLE `t3` (
`v` varchar(10) DEFAULT NULL,
`c` char(10) DEFAULT NULL,
`t` varchar(50) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
alter table t1 modify c varchar(10);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`v` varchar(10) DEFAULT NULL,
`c` varchar(10) DEFAULT NULL,
`t` varchar(50) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
alter table t1 modify v char(10);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`v` char(10) DEFAULT NULL,
`c` varchar(10) DEFAULT NULL,
`t` varchar(50) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
alter table t1 modify t varchar(10);
Warnings:
Warning 1265 Data truncated for column 't' at row 2
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`v` char(10) DEFAULT NULL,
`c` varchar(10) DEFAULT NULL,
`t` varchar(10) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
select concat('*',v,'*',c,'*',t,'*') from t1;
concat('*',v,'*',c,'*',t,'*')
*+*+*+ *
*+*+*+ *
drop table t1,t2,t3;
create table t1 (v varchar(10), c char(10), t varchar(50), key(v), key(c), key(t(10)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`v` varchar(10) DEFAULT NULL,
`c` char(10) DEFAULT NULL,
`t` varchar(50) DEFAULT NULL,
KEY `v` (`v`),
KEY `c` (`c`),
KEY `t` (`t`(10))
) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
select count(*) from t1;
count(*)
270
insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1)));
select count(*) from t1 where v='a';
count(*)
10
select count(*) from t1 where c='a';
count(*)
10
select count(*) from t1 where t='a';
count(*)
10
select count(*) from t1 where v='a ';
count(*)
10
select count(*) from t1 where c='a ';
count(*)
10
select count(*) from t1 where t='a ';
count(*)
10
select count(*) from t1 where v between 'a' and 'a ';
count(*)
10
select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
count(*)
10
select count(*) from t1 where v like 'a%';
count(*)
11
select count(*) from t1 where c like 'a%';
count(*)
11
select count(*) from t1 where t like 'a%';
count(*)
11
select count(*) from t1 where v like 'a %';
count(*)
9
explain select count(*) from t1 where v='a ';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref v v 13 const 10 Using where
explain select count(*) from t1 where c='a ';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref c c 11 const 10 Using where
explain select count(*) from t1 where t='a ';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref t t 13 const 10 Using where
explain select count(*) from t1 where v like 'a%';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL v NULL NULL NULL 271 Using where
explain select count(*) from t1 where v between 'a' and 'a ';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref v v 13 const 10 Using where
explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref v v 13 const 10 Using where
alter table t1 add unique(v);
ERROR 23000: Duplicate entry '{ ' for key 'v_2'
select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a' order by length(concat('*',v,'*',c,'*',t,'*'));
qq
*a*a*a*
*a *a*a *
*a *a*a *
*a *a*a *
*a *a*a *
*a *a*a *
*a *a*a *
*a *a*a *
*a *a*a *
*a *a*a *
explain select * from t1 where v='a';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref v v 13 const 10 Using where
select v,count(*) from t1 group by v limit 10;
v count(*)
a 1
a 10
b 10
c 10
d 10
e 10
f 10
g 10
h 10
i 10
select v,count(t) from t1 group by v limit 10;
v count(t)
a 1
a 10
b 10
c 10
d 10
e 10
f 10
g 10
h 10
i 10
select v,count(c) from t1 group by v limit 10;
v count(c)
a 1
a 10
b 10
c 10
d 10
e 10
f 10
g 10
h 10
i 10
select sql_big_result trim(v),count(t) from t1 group by v limit 10;
trim(v) count(t)
a 1
a 10
b 10
c 10
d 10
e 10
f 10
g 10
h 10
i 10
select sql_big_result trim(v),count(c) from t1 group by v limit 10;
trim(v) count(c)
a 1
a 10
b 10
c 10
d 10
e 10
f 10
g 10
h 10
i 10
select c,count(*) from t1 group by c limit 10;
c count(*)
a 1
a 10
b 10
c 10
d 10
e 10
f 10
g 10
h 10
i 10
select c,count(t) from t1 group by c limit 10;
c count(t)
a 1
a 10
b 10
c 10
d 10
e 10
f 10
g 10
h 10
i 10
select sql_big_result c,count(t) from t1 group by c limit 10;
c count(t)
a 1
a 10
b 10
c 10
d 10
e 10
f 10
g 10
h 10
i 10
select t,count(*) from t1 group by t limit 10;
t count(*)
a 1
a 10
b 10
c 10
d 10
e 10
f 10
g 10
h 10
i 10
select t,count(t) from t1 group by t limit 10;
t count(t)
a 1
a 10
b 10
c 10
d 10
e 10
f 10
g 10
h 10
i 10
select sql_big_result trim(t),count(t) from t1 group by t limit 10;
trim(t) count(t)
a 1
a 10
b 10
c 10
d 10
e 10
f 10
g 10
h 10
i 10
drop table t1;
create table t1 (a char(10), unique (a));
insert into t1 values ('a');
insert into t1 values ('a ');
ERROR 23000: Duplicate entry 'a' for key 'a'
alter table t1 modify a varchar(10);
insert into t1 values ('a '),('a '),('a '),('a ');
ERROR 23000: Duplicate entry 'a ' for key 'a'
insert into t1 values ('a ');
ERROR 23000: Duplicate entry 'a ' for key 'a'
insert into t1 values ('a ');
ERROR 23000: Duplicate entry 'a ' for key 'a'
insert into t1 values ('a ');
ERROR 23000: Duplicate entry 'a ' for key 'a'
update t1 set a='a ' where a like 'a ';
update t1 set a='a ' where a like 'a ';
drop table t1;
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)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`v` varchar(10) DEFAULT NULL,
`c` char(10) DEFAULT NULL,
`t` varchar(50) DEFAULT NULL,
KEY `v` (`v`) USING BTREE,
KEY `c` (`c`) USING BTREE,
KEY `t` (`t`(10)) USING BTREE
) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
select count(*) from t1;
count(*)
267
insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1)));
select count(*) from t1 where v='a';
count(*)
7
select count(*) from t1 where c='a';
count(*)
7
select count(*) from t1 where t='a';
count(*)
7
select count(*) from t1 where v='a ';
count(*)
7
select count(*) from t1 where c='a ';
count(*)
7
select count(*) from t1 where t='a ';
count(*)
7
select count(*) from t1 where v between 'a' and 'a ';
count(*)
7
select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
count(*)
7
explain select count(*) from t1 where v='a ';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref v v 13 const # Using where
explain select count(*) from t1 where c='a ';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref c c 11 const # Using where
explain select count(*) from t1 where t='a ';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref t t 13 const # Using where
explain select count(*) from t1 where v like 'a%';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range v v 13 NULL # Using where
explain select count(*) from t1 where v between 'a' and 'a ';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref v v 13 const # Using where
explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref v v 13 const # Using where
alter table t1 add unique(v);
ERROR 23000: Duplicate entry '{ ' for key 'v_2'
select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a' order by length(concat('*',v,'*',c,'*',t,'*'));
qq
*a*a*a*
*a *a*a *
*a *a*a *
*a *a*a *
*a *a*a *
*a *a*a *
*a *a*a *
explain select * from t1 where v='a';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref v v 13 const # Using where
drop table t1;
create table t1 (a char(10), unique using btree (a)) engine=heap;
insert into t1 values ('a');
insert into t1 values ('a ');
ERROR 23000: Duplicate entry 'a' for key 'a'
alter table t1 modify a varchar(10);
insert into t1 values ('a '),('a '),('a '),('a ');
ERROR 23000: Duplicate entry 'a ' for key 'a'
insert into t1 values ('a ');
ERROR 23000: Duplicate entry 'a ' for key 'a'
insert into t1 values ('a ');
ERROR 23000: Duplicate entry 'a ' for key 'a'
insert into t1 values ('a ');
ERROR 23000: Duplicate entry 'a ' for key 'a'
update t1 set a='a ' where a like 'a ';
update t1 set a='a ' where a like 'a ';
drop table t1;
create table t1 (v varchar(10), c char(10), t varchar(50), key(v(5)), key(c(5)), key(t(5)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`v` varchar(10) DEFAULT NULL,
`c` char(10) DEFAULT NULL,
`t` varchar(50) DEFAULT NULL,
KEY `v` (`v`(5)),
KEY `c` (`c`(5)),
KEY `t` (`t`(5))
) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
drop table t1;
create table t1 (v varchar(65530), key(v(10)));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`v` varchar(65530) DEFAULT NULL,
KEY `v` (`v`(10))
) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
insert into t1 values(repeat('a',65530));
select length(v) from t1 where v=repeat('a',65530);
length(v)
65530
drop table t1;
set default_storage_engine=MyISAM;
create table t1 (a bigint unsigned auto_increment primary key, b int,
key (b, a)) engine=heap;
insert t1 (b) values (1),(1),(1),(1),(1),(1),(1),(1);
select * from t1;
a b
1 1
2 1
3 1
4 1
5 1
6 1
7 1
8 1
drop table t1;
create table t1 (a int not null, b int not null auto_increment,
primary key(a, b), key(b)) engine=heap;
insert t1 (a) values (1),(1),(1),(1),(1),(1),(1),(1);
select * from t1;
a b
1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
drop table t1;
create table t1 (a int not null, b int not null auto_increment,
primary key(a, b)) engine=heap;
ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key
create table t1 (c char(255), primary key(c(90)));
insert into t1 values ("abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz");
insert into t1 values ("abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz");
ERROR 23000: Duplicate entry 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghi...' for key 'PRIMARY'
drop table t1;
CREATE TABLE t1 (a int, key(a)) engine=heap;
insert into t1 values (0);
delete from t1;
select * from t1;
a
insert into t1 values (0), (1);
select * from t1 where a = 0;
a
0
drop table t1;
create table t1 (c char(10)) engine=memory;
create table t2 (c varchar(10)) engine=memory;
show table status like 't_';
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
t1 MEMORY 10 Fixed 0 11 0 # 0 0 NULL # NULL NULL latin1_swedish_ci NULL 0 N
t2 MEMORY 10 Fixed 0 12 0 # 0 0 NULL # NULL NULL latin1_swedish_ci NULL 0 N
drop table t1, t2;
CREATE TABLE t1(a VARCHAR(1), b VARCHAR(2), c VARCHAR(256),
KEY(a), KEY(b), KEY(c)) ENGINE=MEMORY;
INSERT INTO t1 VALUES('a','aa',REPEAT('a', 256)),('a','aa',REPEAT('a',256));
SELECT COUNT(*) FROM t1 WHERE a='a';
COUNT(*)
2
SELECT COUNT(*) FROM t1 WHERE b='aa';
COUNT(*)
2
SELECT COUNT(*) FROM t1 WHERE c=REPEAT('a',256);
COUNT(*)
2
DROP TABLE t1;
CREATE TABLE t1(c1 VARCHAR(100), c2 INT) ENGINE=MEMORY;
INSERT INTO t1 VALUES('', 0);
ALTER TABLE t1 MODIFY c1 VARCHAR(101);
SELECT c2 FROM t1;
c2
0
DROP TABLE t1;
CREATE TABLE t1 (
id int(11) NOT NULL AUTO_INCREMENT,
color enum('GREEN', 'WHITE') DEFAULT NULL,
ts int,
PRIMARY KEY (id),
KEY color (color) USING HASH
) ENGINE=MEMORY DEFAULT CHARSET=utf8;
INSERT INTO t1 VALUES("1","GREEN",1);
INSERT INTO t1 VALUES("2","GREEN",1);
INSERT INTO t1 VALUES("3","GREEN",1);
INSERT INTO t1 VALUES("4","GREEN",1);
INSERT INTO t1 VALUES("5","GREEN",1);
INSERT INTO t1 VALUES("6","GREEN",1);
DELETE FROM t1 WHERE id = 1;
INSERT INTO t1 VALUES("7","GREEN", 2);
DELETE FROM t1 WHERE ts = 1 AND color = 'GREEN';
SELECT * from t1;
id color ts
7 GREEN 2
DROP TABLE t1;
CREATE TABLE t1 (id INT);
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2);
SET @@max_heap_table_size = 1024*1024*1024*20;
CREATE TEMPORARY TABLE tmp ENGINE=MEMORY
SELECT id FROM t1;
DROP TEMPORARY TABLE tmp;
drop table t1;
#
# BUG#11825482: Broken key length calculation for btree index
#
CREATE TABLE h1 (f1 VARCHAR(1), f2 INT NOT NULL,
UNIQUE KEY h1i (f1,f2) USING BTREE ) ENGINE=HEAP;
INSERT INTO h1 VALUES(NULL,0),(NULL,1);
SELECT 'wrong' as 'result' FROM dual WHERE ('h', 0) NOT IN (SELECT * FROM h1);
result
CREATE TABLE t1 (
pk int NOT NULL,
col_int_nokey INT,
col_varchar_nokey VARCHAR(1),
PRIMARY KEY (pk)
);
INSERT INTO t1 VALUES (19,5,'h'),(20,5,'h');
CREATE TABLE t2 (col_int_nokey INT);
INSERT INTO t2 VALUES (1),(2);
CREATE VIEW v1 AS
SELECT col_varchar_nokey, COUNT( col_varchar_nokey )
FROM t1
WHERE col_int_nokey <= 141 AND pk <= 4
;
SELECT col_int_nokey FROM t2
WHERE ('h', 0) NOT IN ( SELECT * FROM v1);
col_int_nokey
# shouldn't crash
EXPLAIN SELECT col_int_nokey FROM t2
WHERE ('h', 0) NOT IN ( SELECT * FROM v1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 2
2 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where
3 DERIVED t1 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using where
DROP TABLE t1,t2,h1;
DROP VIEW v1;
CREATE TABLE t1 (id INT);
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2);
SET @@max_heap_table_size = 1024*1024*1024*20;
CREATE TEMPORARY TABLE tmp ENGINE=MEMORY
SELECT id FROM t1;
DROP TEMPORARY TABLE tmp;
drop table t1;
CREATE TABLE t1 (
id int(11) NOT NULL AUTO_INCREMENT,
color enum('GREEN', 'WHITE') DEFAULT NULL,
ts int,
PRIMARY KEY (id),
KEY color (color) USING HASH
) ENGINE=MEMORY DEFAULT CHARSET=utf8;
INSERT INTO t1 VALUES("1","GREEN",1);
INSERT INTO t1 VALUES("2","GREEN",1);
INSERT INTO t1 VALUES("3","GREEN",1);
INSERT INTO t1 VALUES("4","GREEN",1);
INSERT INTO t1 VALUES("5","GREEN",1);
INSERT INTO t1 VALUES("6","GREEN",1);
DELETE FROM t1 WHERE id = 1;
INSERT INTO t1 VALUES("7","GREEN", 2);
DELETE FROM t1 WHERE ts = 1 AND color = 'GREEN';
SELECT * from t1 WHERE ts = 1 AND color = 'GREEN';
id color ts
DROP TABLE t1;
Reference in a new issue View git blame Copy permalink