~drizzle-trunk/drizzle/development

#

# Test of heap tables.

#

--disable_warnings

drop table if exists t1,t2,t3;

--enable_warnings

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

#show table status like "t1";

select * from t1;

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;

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

alter table t1 engine=myisam;

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

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;

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

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

drop table t1;

create table t1 (a int) engine=heap;

insert into t1 values(1);

select max(a) from t1;

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; 

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 table t1 (id int unsigned not null, primary key (id)) engine=HEAP;

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 table t1 (n int) engine=heap;

drop table t1;

create table t1 (n int) engine=heap;

drop table if exists t1;

# Test of non unique index

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;

drop table t1;

#

# Test when using part key searches

#

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

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

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

SELECT * FROM t1 WHERE a=10;

DROP TABLE t1;

#

# Test when deleting all rows

#

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;

DROP TABLE t1;

#

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

#

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;

DROP TABLE job_titles;

#

# Test of delete with NOT NULL

# (Bug #6082)

#

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;

DROP TABLE t1;

#

# Bug #6748

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

#

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;

DROP TABLE t1;

#

# Bug #6878: a problem with small length records

#

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;

drop table t1;

#

# Test varchar

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

#

let $default=`select @@storage_engine`;

set storage_engine=HEAP;

#

# Simple basic test that endspace is saved

#

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

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

# Check how columns are copied

show create table t1;

create table t2 like t1;

show create table t2;

create 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(10);

show create table t1;

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

drop table t1,t2,t3;

#

# Testing of keys

#

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

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;

}

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 sql_big_result 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 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_DUP_ENTRY

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

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;

}

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 table t1 (a char(10), unique using btree (a)) engine=heap;

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_DUP_ENTRY

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 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 table t1 (v varchar(65530), key(v(10)));

show create table t1;

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

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

drop table t1;

#

# Reset varchar test

#

eval set storage_engine=$default;

#

# Bug #8489: Strange auto_increment behaviour

#

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;

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;

drop table t1;

--error 1075

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

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

#

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

#

create 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 TABLE t1 (a int, key(a)) engine=heap;

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 table t1 (c char(10)) engine=memory;

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

--replace_column 8 #

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