~drizzle-trunk/drizzle/development

drop table if exists t1,t2,t3;

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;

show table status like "t1";

Session	Schema	Name	Type	Engine	Version	Rows	Avg_row_length	Table_size	Auto_increment

#	test	t1	TEMPORARY	MEMORY	#	#	#	#	#

show keys from t1;

Table	Unique	Key_name	Seq_in_index	Column_name

t1	YES	PRIMARY	1	a

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

a	b

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

show table status like "t1";

Session	Schema	Name	Type	Engine	Version	Rows	Avg_row_length	Table_size	Auto_increment

#	test	t1	TEMPORARY	InnoDB	#	#	#	#	#

select * from t1;

a	b

1	1

2	2

3	3

4	4

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;

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

create temporary table t2 SELECT * FROM t1;

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	Using where

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;

x	y

1	3

1	1

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

ERROR HY000: Can't reopen table: 't1'

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

ERROR HY000: Can't reopen table: 't1'

drop table t1;

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

insert into t1 values(1);

select max(a) from t1;

max(a)

1

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;

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

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

drop table t1;

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

drop table if exists t1;

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;

f1	f2

16	ted

12	ted

12	ted

12	ted

12	ted

drop table t1;

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

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	48	const,const	2	Using where

drop table t1;

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;

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1	SIMPLE	t1	ref	a	a	5	const	3	Using where

SELECT * FROM t1 WHERE a=10;

a

10

10

10

DROP TABLE t1;

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;

a

DROP TABLE t1;

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;

MAX(job_title_id)

NULL

DROP TABLE job_titles;

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;

a	B

1	1

DROP TABLE t1;

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;

pseudo	date

ZoomZip	1101106546

DROP TABLE t1;

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;

a

3

2

drop table t1;

set storage_engine=MEMORY;

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

ERROR 22001: Data too long for column 'v' at row 1

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

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

ERROR 22001: Data too long for column 'v' at row 1

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

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

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

concat('*',v,'*',c,'*',t,'*')

*+ *+ *+ *

*+         *+         *+         *

show create table t1;

Table	Create Table

t1	CREATE TEMPORARY TABLE `t1` (

  `v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

  `c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

  `t` VARCHAR(50) COLLATE utf8_general_ci DEFAULT NULL

) ENGINE=MEMORY COLLATE = utf8_general_ci

create temporary table t2 like t1;

show create table t2;

Table	Create Table

t2	CREATE TEMPORARY TABLE `t2` (

  `v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

  `c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

  `t` VARCHAR(50) COLLATE utf8_general_ci DEFAULT NULL

) ENGINE=MEMORY COLLATE = utf8_general_ci

create temporary table t3 select * from t1;

show create table t3;

Table	Create Table

t3	CREATE TEMPORARY TABLE `t3` (

  `v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

  `c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

  `t` VARCHAR(50) COLLATE utf8_general_ci DEFAULT NULL

) ENGINE=MEMORY COLLATE = utf8_general_ci

alter table t1 modify c varchar(10);

show create table t1;

Table	Create Table

t1	CREATE TEMPORARY TABLE `t1` (

  `v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

  `c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

  `t` VARCHAR(50) COLLATE utf8_general_ci DEFAULT NULL

) ENGINE=MEMORY COLLATE = utf8_general_ci

alter table t1 modify v char(10);

show create table t1;

Table	Create Table

t1	CREATE TEMPORARY TABLE `t1` (

  `v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

  `c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

  `t` VARCHAR(50) COLLATE utf8_general_ci DEFAULT NULL

) ENGINE=MEMORY COLLATE = utf8_general_ci

alter table t1 modify t varchar(50);

show create table t1;

Table	Create Table

t1	CREATE TEMPORARY TABLE `t1` (

  `v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

  `c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

  `t` VARCHAR(50) COLLATE utf8_general_ci DEFAULT NULL

) ENGINE=MEMORY COLLATE = utf8_general_ci

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

concat('*',v,'*',c,'*',t,'*')

*+ *+ *+ *

*+         *+         *+         *

drop table t1,t2,t3;

create temporary 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 TEMPORARY TABLE `t1` (

  `v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

  `c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

  `t` VARCHAR(50) COLLATE utf8_general_ci DEFAULT NULL,

  KEY `v` (`v`),

  KEY `c` (`c`),

  KEY `t` (`t`(10))

) ENGINE=MEMORY COLLATE = utf8_general_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	43	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	43	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	43	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	43	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	43	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	43	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 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 temporary 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 22001: Data too long for column 'a' at row 1

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

Table	Create Table

t1	CREATE TEMPORARY TABLE `t1` (

  `v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

  `c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

  `t` VARCHAR(50) COLLATE utf8_general_ci DEFAULT NULL,

  KEY `v` (`v`) USING BTREE,

  KEY `c` (`c`) USING BTREE,

  KEY `t` (`t`(10)) USING BTREE

) ENGINE=MEMORY COLLATE = utf8_general_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

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	43	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	43	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	43	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	ALL	v	NULL	NULL	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	43	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	43	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      *

*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	43	const	#	Using where

drop table t1;

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

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 22001: Data too long for column 'a' at row 1

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

Table	Create Table

t1	CREATE TEMPORARY TABLE `t1` (

  `v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

  `c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

  `t` VARCHAR(50) COLLATE utf8_general_ci DEFAULT NULL,

  KEY `v` (`v`(5)),

  KEY `c` (`c`(5)),

  KEY `t` (`t`(5))

) ENGINE=MEMORY COLLATE = utf8_general_ci

drop table t1;

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

show create table t1;

Table	Create Table

t1	CREATE TEMPORARY TABLE `t1` (

  `v` VARCHAR(16383) COLLATE utf8_general_ci DEFAULT NULL,

  KEY `v` (`v`(10))

) ENGINE=MEMORY COLLATE = utf8_general_ci

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

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

length(v)

16383

drop table t1;

set storage_engine=PBXT;

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;

a	b

1	1

2	1

3	1

4	1

5	1

6	1

7	1

8	1

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;

a	b

1	1

1	2

1	3

1	4

1	5

1	6

1	7

1	8

drop table t1;

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

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

ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key

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

insert into t1 values ("abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz");

insert into t1 values ("abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz");

ERROR 23000: Duplicate entry 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijkl' for key 'PRIMARY'

drop table t1;

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

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

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

show table status like 't_';

Session	Schema	Name	Type	Engine	Version	Rows	Avg_row_length	Table_size	Auto_increment

#	test	t1	TEMPORARY	MEMORY	#	#	#	#	#

#	test	t2	TEMPORARY	MEMORY	#	#	#	#	#

drop table t1, t2;

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

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

c2

0

DROP TABLE t1;