diff options
Diffstat (limited to 'mysql-test/suite/heap/heap.result')
| -rw-r--r-- | mysql-test/suite/heap/heap.result | 879 |
1 files changed, 879 insertions, 0 deletions
diff --git a/mysql-test/suite/heap/heap.result b/mysql-test/suite/heap/heap.result new file mode 100644 index 00000000..bef3913d --- /dev/null +++ b/mysql-test/suite/heap/heap.result @@ -0,0 +1,879 @@ +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 (a int, index(a)) engine=heap min_rows=10 max_rows=100; +insert into t1 values(1); +select data_length,index_length from information_schema.tables where table_schema="test" and table_name="t1"; +data_length index_length +1600 2400 +drop table t1; +CREATE TABLE t1 (a int, index(a)) engine=heap min_rows=10 max_rows=10000; +insert into t1 values(1); +select data_length,index_length from information_schema.tables where table_schema="test" and table_name="t1"; +data_length index_length +16000 24000 +drop table t1; +CREATE TABLE t1 (a int, index(a)) engine=heap min_rows=3000 max_rows=3000; +insert into t1 values(1); +select data_length,index_length from information_schema.tables where table_schema="test" and table_name="t1"; +data_length index_length +48000 72000 +drop table t1; +CREATE TABLE t1 (a int, index(a)) engine=heap max_rows=15000; +insert into t1 values(1); +select data_length,index_length from information_schema.tables where table_schema="test" and table_name="t1"; +data_length index_length +24000 36000 +drop table t1; +create table t1 (c1 int, index(c1)) engine=heap max_rows=10000; +insert into t1 select rand(100000000); +insert into t1 select rand(100000000) from t1; +insert into t1 select rand(100000000) from t1; +insert into t1 select rand(100000000) from t1; +insert into t1 select rand(100000000) from t1; +insert into t1 select rand(100000000) from t1; +insert into t1 select rand(100000000) from t1; +insert into t1 select rand(100000000) from t1; +insert into t1 select rand(100000000) from t1; +insert into t1 select rand(100000000) from t1; +insert into t1 select rand(100000000) from t1 limit 488; +select data_length,index_length from information_schema.tables where table_schema="test" and table_name="t1"; +data_length index_length +16000 24000 +insert into t1 select rand(100000000) from t1 limit 1; +select data_length,index_length from information_schema.tables where table_schema="test" and table_name="t1"; +data_length index_length +33024 49024 +insert into t1 select rand(100000000) from t1 limit 1000; +select data_length,index_length from information_schema.tables where table_schema="test" and table_name="t1"; +data_length index_length +49024 73024 +insert into t1 select rand(100000000) from t1; +select data_length,index_length from information_schema.tables where table_schema="test" and table_name="t1"; +data_length index_length +81024 121024 +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; +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; |