summaryrefslogtreecommitdiff
path: root/mysql-test/r/heap.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r/heap.result')
-rw-r--r--mysql-test/r/heap.result420
1 files changed, 419 insertions, 1 deletions
diff --git a/mysql-test/r/heap.result b/mysql-test/r/heap.result
index 6756be84bb0..e0e6c5fb3de 100644
--- a/mysql-test/r/heap.result
+++ b/mysql-test/r/heap.result
@@ -1,4 +1,4 @@
-drop table if exists t1;
+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;
@@ -249,3 +249,421 @@ a
3
2
drop table t1;
+set 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:
+Warning 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=HEAP DEFAULT CHARSET=latin1
+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=HEAP DEFAULT CHARSET=latin1
+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=HEAP DEFAULT CHARSET=latin1
+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=HEAP DEFAULT CHARSET=latin1
+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=HEAP DEFAULT CHARSET=latin1
+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=HEAP DEFAULT CHARSET=latin1
+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=HEAP DEFAULT CHARSET=latin1
+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 ALL v NULL NULL NULL 271 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 ALL v NULL NULL NULL 271 Using where
+alter table t1 add unique(v);
+ERROR 23000: Duplicate entry '{ ' for key 1
+alter table t1 add key(v);
+select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a';
+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_2 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 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 sql_big_result 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 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 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
+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 1
+alter table t1 modify a varchar(10);
+insert into t1 values ('a '),('a '),('a '),('a ');
+ERROR 23000: Duplicate entry 'a ' for key 1
+insert into t1 values ('a ');
+ERROR 23000: Duplicate entry 'a ' for key 1
+insert into t1 values ('a ');
+ERROR 23000: Duplicate entry 'a ' for key 1
+insert into t1 values ('a ');
+ERROR 23000: Duplicate entry 'a ' for key 1
+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` TYPE BTREE (`v`),
+ KEY `c` TYPE BTREE (`c`),
+ KEY `t` TYPE BTREE (`t`(10))
+) ENGINE=HEAP DEFAULT CHARSET=latin1
+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
+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 range v v 13 NULL # 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 range v v 13 NULL # Using where
+alter table t1 add unique(v);
+ERROR 23000: Duplicate entry '{ ' for key 1
+alter table t1 add key(v);
+select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a';
+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_2 v 13 const 7 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 1
+alter table t1 modify a varchar(10);
+insert into t1 values ('a '),('a '),('a '),('a ');
+ERROR 23000: Duplicate entry 'a ' for key 1
+insert into t1 values ('a ');
+ERROR 23000: Duplicate entry 'a ' for key 1
+insert into t1 values ('a ');
+ERROR 23000: Duplicate entry 'a ' for key 1
+insert into t1 values ('a ');
+ERROR 23000: Duplicate entry 'a ' for key 1
+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=HEAP DEFAULT CHARSET=latin1
+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=HEAP DEFAULT CHARSET=latin1
+insert into t1 values(repeat('a',65530));
+select length(v) from t1 where v=repeat('a',65530);
+length(v)
+65530
+drop table t1;
+set storage_engine=MyISAM;
View Lorry Controller Status