~drizzle-trunk/drizzle/development

#

# Test of MEMORY tables.

#

--disable_warnings

drop table if exists t1,t2,t3;

--enable_warnings

create temporary 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=1 or a=0;

--replace_column 1 #  6 # 7 # 8 # 9 # 10 #

show table status like "t1";

show keys from t1;

select * from t1;

select * from t1 where a=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;

alter table t1 add c int not null, add key (c,a);

drop table t1;

create temporary 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;

drop table t1;

create temporary 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);

alter table t1 modify a int not null auto_increment, engine=innodb, comment="new innodb table";

--replace_column 1 #  6 # 7 # 8 # 9 # 10 #

show table status like "t1";

select * from t1;

drop table t1;

create temporary table t1 (a int not null) engine=MEMORY;

insert into t1 values (869751),(736494),(226312),(802616),(728912);

select * from t1 where a > 736494;

alter table t1 add unique uniq_id(a);

select * from t1 where a > 736494;

select * from t1 where a = 736494;

select * from t1 where a=869751 or a=736494;

select * from t1 where a in (869751,736494,226312,802616);

create temporary table t2 SELECT * FROM t1;

explain select * from t2 where a in (869751,736494,226312,802616);

drop table t1,t2;

create temporary table t1 (x int not null, y int not null, key x (x), unique y (y))

engine=MEMORY;

insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6);

select * from t1 where x=1;

-- error 1137

select * from t1,t1 as t2 where t1.x=t2.y;

-- error 1137

explain select * from t1,t1 as t2 where t1.x=t2.y;

drop table t1;

create temporary table t1 (a int) engine=MEMORY;

insert into t1 values(1);

select max(a) from t1;

drop table t1;

CREATE TEMPORARY TABLE t1 ( a int not null default 0, b int not null default 0,  key(a),  key(b)  ) ENGINE=MEMORY;

insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6);

select * from t1 where a=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;

drop table t1;

create temporary table t1 (id int not null, primary key (id)) engine=MEMORY;

insert into t1 values(1);

select max(id) from t1; 

insert into t1 values(2);

select max(id) from t1; 

replace into t1 values(1);

drop table t1;

create temporary table t1 (n int) engine=MEMORY;

drop table t1;

create temporary table t1 (n int) engine=MEMORY;

drop table if exists t1;

# Test of non unique index

CREATE TEMPORARY table t1(f1 int not null,f2 char(20) not 

null,index(f2)) engine=MEMORY;

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;

drop table t1;

#

# Test when using part key searches

#

create temporary table t1 (btn char(10) not null, key(btn)) engine=MEMORY;

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%";

select * from t1 where btn like "q%";

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";

explain select * from t1 where btn="a" and new_col="a";

drop table t1;

#

# Test of NULL keys

#

CREATE TEMPORARY TABLE t1 (

  a int default NULL,

  b int default NULL,

  KEY a (a),

  UNIQUE b (b)

) engine=MEMORY;

INSERT INTO t1 VALUES (NULL,99),(99,NULL),(1,1),(2,2),(1,3);

SELECT * FROM t1 WHERE a=NULL;

explain SELECT * FROM t1 WHERE a IS NULL;

SELECT * FROM t1 WHERE a<=>NULL;

SELECT * FROM t1 WHERE b=NULL;

explain SELECT * FROM t1 WHERE b IS NULL;

SELECT * FROM t1 WHERE b<=>NULL;

--error ER_DUP_ENTRY

INSERT INTO t1 VALUES (1,3);

DROP TABLE t1;

CREATE TEMPORARY TABLE t1 (

  a int default NULL,

  key a (a)

) ENGINE=MEMORY;

INSERT INTO t1 VALUES (10), (10), (10);

EXPLAIN SELECT * FROM t1 WHERE a=10;

SELECT * FROM t1 WHERE a=10;

DROP TABLE t1;

#

# Test when deleting all rows

#

CREATE TEMPORARY TABLE t1 (a int not null, primary key(a)) engine=MEMORY;

INSERT into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11);

DELETE from t1 where a < 100;

SELECT * from t1;

DROP TABLE t1;

#

# Bug#4411 Server hangs when trying to SELECT MAX(id) from an empty MEMORY table

#

CREATE TEMPORARY TABLE `job_titles` (

  `job_title_id` int 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=MEMORY;

SELECT MAX(job_title_id) FROM job_titles;

DROP TABLE job_titles;

#

# Test of delete with NOT NULL

# (Bug #6082)

#

CREATE TEMPORARY TABLE t1 (a INT NOT NULL, B INT, KEY(B)) ENGINE=MEMORY;

INSERT INTO t1 VALUES(1,1), (1,NULL);

SELECT * FROM t1 WHERE B is not null;

DROP TABLE t1;

#

# Bug #6748

# heap_rfirst() doesn't work (and never did!)

#

CREATE TEMPORARY TABLE t1 (pseudo char(35) PRIMARY KEY, date int NOT NULL) ENGINE=MEMORY;

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;

DROP TABLE t1;

#

# Bug #6878: a problem with small length records

#

create temporary 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;

drop table t1;

#

# Test varchar

# We can't use varchar.inc becasue MEMORY doesn't support blob's

#

let $default=`select @@storage_engine`;

set storage_engine=MEMORY;

#

# Simple basic test that endspace is saved

#

create temporary table t1 (v varchar(10), c char(10), t varchar(50));

insert into t1 values('+ ', '+ ', '+ ');

set @a=repeat(' ',20);

--error ER_DATA_TOO_LONG

insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a));

set @a=repeat(' ',10);

--error ER_DATA_TOO_LONG

insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a));

set @a=repeat(' ',9);

insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a));

select concat('*',v,'*',c,'*',t,'*') from t1;

# Check how columns are copied

show create table t1;

create temporary table t2 like t1;

show create table t2;

create temporary table t3 select * from t1;

show create table t3;

alter table t1 modify c varchar(10);

show create table t1;

alter table t1 modify v char(10);

show create table t1;

alter table t1 modify t varchar(50);

show create table t1;

select concat('*',v,'*',c,'*',t,'*') from t1;

drop table t1,t2,t3;

#

# Testing of keys

#

create temporary table t1 (v varchar(10), c char(10), t varchar(50), key(v), key(c), key(t(10)));

show create table t1;

disable_query_log;

begin;

let $1=10;

while ($1)

{

  let $2=27;

  eval set @space=repeat(' ',10-$1);

  while ($2)

  {

    eval set @char=char(ascii('a')+$2-1);

    insert into t1 values(concat(@char,@space),concat(@char,@space),concat(@char,@space));

    dec $2;

  }

  dec $1;

}

commit;

enable_query_log;

select count(*) from t1;

insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1)));

select count(*) from t1 where v='a';

select count(*) from t1 where c='a';

select count(*) from t1 where t='a';

select count(*) from t1 where v='a  ';

select count(*) from t1 where c='a  ';

select count(*) from t1 where t='a  ';

select count(*) from t1 where v between 'a' and 'a ';

select count(*) from t1 where v between 'a' and 'a ' and v between 'a  ' and 'b\n';

select count(*) from t1 where v like 'a%';

select count(*) from t1 where c like 'a%';

select count(*) from t1 where t like 'a%';

select count(*) from t1 where v like 'a %';

explain select count(*) from t1 where v='a  ';

explain select count(*) from t1 where c='a  ';

explain select count(*) from t1 where t='a  ';

explain select count(*) from t1 where v like 'a%';

explain select count(*) from t1 where v between 'a' and 'a ';

explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a  ' and 'b\n';

--error ER_DUP_ENTRY

alter table t1 add unique(v);

select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a' order by length(concat('*',v,'*',c,'*',t,'*'));

explain select * from t1 where v='a';

# GROUP BY

select v,count(*) from t1 group by v limit 10;

select v,count(t) from t1 group by v limit 10;

select v,count(c) from t1 group by v limit 10;

select sql_big_result trim(v),count(t) from t1 group by v limit 10;

select sql_big_result trim(v),count(c) from t1 group by v limit 10;

select c,count(*) from t1 group by c limit 10;

select c,count(t) from t1 group by c limit 10;

select t,count(*) from t1 group by t limit 10;

select t,count(t) from t1 group by t limit 10;

select sql_big_result trim(t),count(t) from t1 group by t limit 10;

drop table t1;

#

# Test unique keys

#

create temporary table t1 (a char(10), unique (a));

insert into t1 values ('a');

--error ER_DUP_ENTRY

insert into t1 values ('a ');

alter table t1 modify a varchar(10);

--error ER_DUP_ENTRY

insert into t1 values ('a '),('a  '),('a   '),('a         ');

--error ER_DUP_ENTRY

insert into t1 values ('a     ');

--error ER_DATA_TOO_LONG

insert into t1 values ('a          ');

--error ER_DUP_ENTRY

insert into t1 values ('a ');

update t1 set a='a      ' where a like 'a ';

update t1 set a='a  ' where a like 'a      ';

drop table t1;

#

# Testing of btree keys

#

create temporary 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;

disable_query_log;

let $1=10;

begin;

while ($1)

{

  let $2=27;

  eval set @space=repeat(' ',10-$1);

  while ($2)

  {

    eval set @char=char(ascii('a')+$2-1);

    insert into t1 values(concat(@char,@space),concat(@char,@space),concat(@char,@space));

    dec $2;

  }

  dec $1;

}

commit;

enable_query_log;

select count(*) from t1;

insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1)));

select count(*) from t1 where v='a';

select count(*) from t1 where c='a';

select count(*) from t1 where t='a';

select count(*) from t1 where v='a  ';

select count(*) from t1 where c='a  ';

select count(*) from t1 where t='a  ';

select count(*) from t1 where v between 'a' and 'a ';

--replace_column 9 #

select count(*) from t1 where v between 'a' and 'a ' and v between 'a  ' and 'b\n';

--replace_column 9 #

explain select count(*) from t1 where v='a  ';

--replace_column 9 #

explain select count(*) from t1 where c='a  ';

--replace_column 9 #

explain select count(*) from t1 where t='a  ';

--replace_column 9 #

explain select count(*) from t1 where v like 'a%';

--replace_column 9 #

explain select count(*) from t1 where v between 'a' and 'a ';

--replace_column 9 #

explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a  ' and 'b\n';

--error ER_DUP_ENTRY

alter table t1 add unique(v);

select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a' order by length(concat('*',v,'*',c,'*',t,'*'));

# Number of rows is not constant for b-trees keys

--replace_column 9 #

explain select * from t1 where v='a';

drop table t1;

#

# Test unique btree keys

#

create temporary table t1 (a char(10), unique using btree (a)) engine=MEMORY;

insert into t1 values ('a');

--error ER_DUP_ENTRY

insert into t1 values ('a ');

alter table t1 modify a varchar(10);

--error ER_DUP_ENTRY

insert into t1 values ('a '),('a  '),('a   '),('a         ');

--error ER_DUP_ENTRY

insert into t1 values ('a     ');

--error ER_DATA_TOO_LONG

insert into t1 values ('a          ');

--error ER_DUP_ENTRY

insert into t1 values ('a ');

update t1 set a='a      ' where a like 'a ';

update t1 set a='a  ' where a like 'a      ';

drop table t1;

#

# test show create table

#

create temporary table t1 (v varchar(10), c char(10), t varchar(50), key(v(5)), key(c(5)), key(t(5)));

show create table t1;

drop table t1;

create temporary table t1 (v varchar(16383), key(v(10)));

show create table t1;

insert into t1 values(repeat('a',16383));

select length(v) from t1 where v=repeat('a',16383);

drop table t1;

#

# Reset varchar test

#

eval set storage_engine=$default;

#

# Bug #8489: Strange auto_increment behaviour

#

create temporary table t1 (a bigint auto_increment primary key, b int,

  key (b, a)) engine=MEMORY;

insert t1 (b) values (1),(1),(1),(1),(1),(1),(1),(1);

select * from t1;

drop table t1;

create temporary table t1 (a int not null, b int not null auto_increment,

  primary key(a, b), key(b)) engine=MEMORY;

insert t1 (a) values (1),(1),(1),(1),(1),(1),(1),(1);

select * from t1;

drop table t1;

--error ER_WRONG_AUTO_KEY

create temporary table t1 (a int not null, b int not null auto_increment,

  primary key(a, b)) engine=MEMORY;

#

# Bug #10566: Verify that we can create temporary a prefixed key with length > 255

#

create temporary table t1 (c char(255), primary key(c(90)));

insert into t1 values ("abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz");

--error ER_DUP_ENTRY

insert into t1 values ("abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz");

drop table t1;

#

# Bug 12796: Record doesn't show when selecting through index

#

CREATE TEMPORARY TABLE t1 (a int, key(a)) engine=MEMORY;

insert into t1 values (0);

delete from t1;

select * from t1;

insert into t1 values (0), (1);

select * from t1 where a = 0;

drop table t1;

# End of 4.1 tests

#

# Bug #3094: Row format of memory tables should always be reported as Fixed

#

create temporary table t1 (c char(10)) engine=memory;

create temporary table t2 (c varchar(10)) engine=memory;

--replace_column 1 #  6 # 7 # 8 # 9 # 10 #

show table status like 't_';

drop table t1, t2;

#

# BUG#18233 - Memory tables INDEX USING HASH (a,b) returns 1 row on

#             SELECT WHERE a= AND b=

#

CREATE TEMPORARY 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';

SELECT COUNT(*) FROM t1 WHERE b='aa';

SELECT COUNT(*) FROM t1 WHERE c=REPEAT('a',256);

DROP TABLE t1;

# End of 5.0 tests

#

# BUG#26080 - Memory Storage engine not working properly

#

CREATE TEMPORARY 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;

DROP TABLE t1;