Merge with new VARCHAR code

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;