Add support for up to VARCHAR (size up to 65535)

Renamed HA_VAR_LENGTH to HA_VAR_LENGTH_PART
Renamed in all files FIELD_TYPE_STRING and FIELD_TYPE_VAR_STRING to MYSQL_TYPE_STRING and MYSQL_TYPE_VAR_STRING to make it easy to catch all possible errors
Added support for VARCHAR KEYS to heap
Removed support for ISAM
Now only long VARCHAR columns are changed to TEXT on demand (not CHAR)
Internal temporary files can now use fixed length tables if the used VARCHAR columns are short

10 files changed, 282 insertions, 19 deletions

diff --git a/mysql-test/t/bdb.test b/mysql-test/t/bdb.test
index 069ec758ba2..668adc3f5cc 100644
--- a/mysql-test/t/bdb.test
+++ b/mysql-test/t/bdb.test
@@ -929,3 +929,12 @@ SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterb");
 SELECT id FROM t1 WHERE (list_id = 1) AND (term = "lettera");
 SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterd");
 DROP TABLE t1;
+
+#
+# Test varchar
+#
+
+let $default=`select @@storage_engine`;
+set storage_engine=bdb;
+source include/varchar.inc;
+eval set storage_engine=$default;
diff --git a/mysql-test/t/ctype_ucs.test b/mysql-test/t/ctype_ucs.test
index 4c6d1bbebef..c39c5424e2f 100644
--- a/mysql-test/t/ctype_ucs.test
+++ b/mysql-test/t/ctype_ucs.test
@@ -16,17 +16,19 @@ SET CHARACTER SET koi8r;
 # which contains 0x20 in the high byte.
 #
 
-CREATE TABLE t1 (word VARCHAR(64) CHARACTER SET ucs2);
-INSERT INTO t1 VALUES (_koi8r'ò'), (X'2004');
+CREATE TABLE t1 (word VARCHAR(64) CHARACTER SET ucs2, word2 CHAR(64) CHARACTER SET ucs2);
+INSERT INTO t1 VALUES (_koi8r'ò',_koi8r'ò'), (X'2004',X'2004');
 SELECT hex(word) FROM t1 ORDER BY word;
+SELECT hex(word2) FROM t1 ORDER BY word2;
 DELETE FROM t1;
 
 #
 # Check that real spaces are correctly trimmed.
 #
 
-INSERT INTO t1 VALUES (X'042000200020'), (X'200400200020');
+INSERT INTO t1 VALUES (X'042000200020',X'042000200020'), (X'200400200020', X'200400200020');
 SELECT hex(word) FROM t1 ORDER BY word;
+SELECT hex(word2) FROM t1 ORDER BY word2;
 DROP TABLE t1;
 
 #
@@ -48,7 +50,6 @@ RPAD(_ucs2 X'0420',10,_ucs2 X'0421') r;
 SHOW CREATE TABLE t1;
 DROP TABLE t1;
 
-
 #
 # BUG3946
 #
@@ -57,6 +58,7 @@ create table t2(f1 Char(30));
 insert into t2 values ("103000"), ("22720000"), ("3401200"), ("78000");
 select lpad(f1, 12, "-o-/") from t2;
 drop table t2;
+
 ######################################################
 #
 # Test of like
diff --git a/mysql-test/t/endspace.test b/mysql-test/t/endspace.test
index 9ee5e32967a..6616a1e288a 100644
--- a/mysql-test/t/endspace.test
+++ b/mysql-test/t/endspace.test
@@ -47,16 +47,16 @@ alter table t1 modify text1 text not null, pack_keys=1;
 select concat('|', text1, '|') from t1 where text1='teststring';
 select concat('|', text1, '|') from t1 where text1='teststring ';
 explain select concat('|', text1, '|') from t1 where text1='teststring ';
-select * from t1 where text1 like 'teststring_%';
-select * from t1 where text1='teststring' or text1 like 'teststring_%';
+select concat('|', text1, '|') from t1 where text1 like 'teststring_%';
+select concat('|', text1, '|') from t1 where text1='teststring' or text1 like 'teststring_%';
 select concat('|', text1, '|') from t1 where text1='teststring' or text1 > 'teststring\t';
 select concat('|', text1, '|') from t1 order by text1;
 drop table t1;
 
 create table t1 (text1 varchar(32) not NULL, KEY key1 (text1)) pack_keys=0;
 insert into t1 values ('teststring'), ('nothing'), ('teststring\t');
-select * from t1 where text1='teststring' or text1 like 'teststring_%';
-select * from t1 where text1='teststring' or text1 >= 'teststring\t';
+select concat('|', text1, '|') from t1 where text1='teststring' or text1 like 'teststring_%';
+select concat('|', text1, '|') from t1 where text1='teststring' or text1 >= 'teststring\t';
 drop table t1;
 
 # Test HEAP tables (with BTREE keys)
diff --git a/mysql-test/t/heap.test b/mysql-test/t/heap.test
index e1776245d9e..d9e12b6789a 100644
--- a/mysql-test/t/heap.test
+++ b/mysql-test/t/heap.test
@@ -3,7 +3,7 @@
 #
 
 --disable_warnings
-drop table if exists t1;
+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;
@@ -174,3 +174,214 @@ 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;
+
+#
+# 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 1062
+alter table t1 add unique(v);
+alter table t1 add key(v);
+select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a';
+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 v,count(t) from t1 group by v limit 10;
+select sql_big_result 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 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 1062
+insert into t1 values ('a ');
+
+alter table t1 modify a varchar(10);
+--error 1062
+insert into t1 values ('a '),('a  '),('a   '),('a         ');
+--error 1062
+insert into t1 values ('a     ');
+--error 1062
+insert into t1 values ('a          ');
+--error 1062
+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 1062
+alter table t1 add unique(v);
+alter table t1 add key(v);
+select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a';
+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 1062
+insert into t1 values ('a ');
+
+alter table t1 modify a varchar(10);
+--error 1062
+insert into t1 values ('a '),('a  '),('a   '),('a         ');
+--error 1062
+insert into t1 values ('a     ');
+--error 1062
+insert into t1 values ('a          ');
+--error 1062
+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;
diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test
index cc11539a9b0..62a2ad5daea 100644
--- a/mysql-test/t/innodb.test
+++ b/mysql-test/t/innodb.test
@@ -1180,3 +1180,15 @@ select count(*) from t1 where x = 18446744073709551601;
 
 drop table t1;
 
+#
+# Test varchar
+#
+
+#let $default=`select @@storage_engine`;
+#set storage_engine=INNODB;
+#source include/varchar.inc;
+#eval set storage_engine=$default;
+
+# InnoDB specific varchar tests
+--error 1074
+create table t1 (v varchar(16384)) engine=innodb;
diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test
index f9081e8769b..cbfdb6f5dc1 100644
--- a/mysql-test/t/myisam.test
+++ b/mysql-test/t/myisam.test
@@ -450,11 +450,14 @@ drop table t1;
 # Test text and unique
 #
 create table t1 (a int not null auto_increment primary key, b text not null, unique b (b(20)));
-insert into t1 (b) values ('a'),('a '),('a  ');
+insert into t1 (b) values ('a'),('b'),('c');
 select concat(b,'.') from t1;
 update t1 set b='b ' where a=2;
 --error 1062
 update t1 set b='b  ' where a > 1;
+--error 1062
+insert into t1 (b) values ('b');
+select * from t1;
 delete from t1 where b='b';
 select a,concat(b,'.') from t1;
 drop table t1;
@@ -523,3 +526,15 @@ explain select count(*) from t1 where a is null;
 select count(*) from t1 where a is null;
 drop table t1;
 
+#
+# Test varchar
+#
+
+let $default=`select @@storage_engine`;
+set storage_engine=MyISAM;
+source include/varchar.inc;
+eval set storage_engine=$default;
+
+# MyISAM specific varchar tests
+--error 1118
+create table t1 (v varchar(65535));
diff --git a/mysql-test/t/ps_1general.test b/mysql-test/t/ps_1general.test
index 89c49d087b7..975ec516861 100644
--- a/mysql-test/t/ps_1general.test
+++ b/mysql-test/t/ps_1general.test
@@ -311,7 +311,7 @@ prepare stmt4 from ' show engine bdb logs ';
 execute stmt4;
 --enable_result_log
 prepare stmt4 from ' show full processlist ';
---replace_column 1 number
+--replace_column 1 number 6 seconds
 execute stmt4;
 prepare stmt4 from ' show grants for user ';
 --error 1295
diff --git a/mysql-test/t/ps_4heap.test b/mysql-test/t/ps_4heap.test
index a7b2e332af4..04d995dacb9 100644
--- a/mysql-test/t/ps_4heap.test
+++ b/mysql-test/t/ps_4heap.test
@@ -33,9 +33,9 @@ eval create table t9
   c9  double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4),
   c13 date, c14 datetime, c15 timestamp(14), c16 time,
   c17 year, c18 bit, c19 bool, c20 char,
-  c21 char(10), c22 varchar(30), c23 char(100), c24 char(100),
-  c25 char(100), c26 char(100), c27 char(100), c28 char(100),
-  c29 char(100), c30 char(100), c31 enum('one', 'two', 'three'),
+  c21 char(10), c22 varchar(30), c23 varchar(100), c24 varchar(100),
+  c25 varchar(100), c26 varchar(100), c27 varchar(100), c28 varchar(100),
+  c29 varchar(100), c30 varchar(100), c31 enum('one', 'two', 'three'),
   c32 set('monday', 'tuesday', 'wednesday'),
   primary key(c1)
 ) engine = $type ;
diff --git a/mysql-test/t/strict.test b/mysql-test/t/strict.test
index d9f4f4f2d0c..43de4a7c1e9 100644
--- a/mysql-test/t/strict.test
+++ b/mysql-test/t/strict.test
@@ -563,13 +563,15 @@ DROP TABLE t1;
 
 # Testing INSERT with CHAR/VARCHAR
 
-CREATE TABLE t1 (col1 CHAR(5), col2 VARCHAR(5));
-INSERT INTO t1 VALUES ('hello', 'hello'),('he', 'he'),('hello   ', 'hello   ');
+CREATE TABLE t1 (col1 CHAR(5), col2 VARCHAR(6));
+INSERT INTO t1 VALUES ('hello', 'hello'),('he', 'he'),('hello   ', 'hello ');
 --error 1265
 INSERT INTO t1 (col1) VALUES ('hellobob');
 --error 1265
 INSERT INTO t1 (col2) VALUES ('hellobob');
 --error 1265
+INSERT INTO t1 (col2) VALUES ('hello  ');
+--error 1265
 UPDATE t1 SET col1 ='hellobob' WHERE col1 ='he';
 --error 1265
 UPDATE t1 SET col2 ='hellobob' WHERE col2 ='he';
diff --git a/mysql-test/t/type_blob.test b/mysql-test/t/type_blob.test
index b67fa7a552d..947e805d26a 100644
--- a/mysql-test/t/type_blob.test
+++ b/mysql-test/t/type_blob.test
@@ -18,12 +18,14 @@ CREATE TABLE t1 (a blob, b text, c blob(250), d text(70000), e text(70000000));
 show columns from t1;
 # PS doesn't give errors on prepare yet
 --disable_ps_protocol
-CREATE TABLE t2 (a char(257), b varbinary(70000), c varchar(70000000));
+CREATE TABLE t2 (a char(255), b varbinary(70000), c varchar(70000000));
+CREATE TABLE t4 (c varchar(65530) character set utf8 not null);
 --enable_ps_protocol
 show columns from t2;
 create table t3 (a long, b long byte);
 show create TABLE t3;
-drop table t1,t2,t3
+show create TABLE t4;
+drop table t1,t2,t3,t4;
 
 #
 # Check errors with blob
@@ -31,6 +33,10 @@ drop table t1,t2,t3
 
 --error 1074
 CREATE TABLE t1 (a char(257) default "hello");
+--error 1074
+CREATE TABLE t2 (a char(256));
+--error 1074
+CREATE TABLE t1 (a varchar(70000) default "hello");
 --error 1101
 CREATE TABLE t2 (a blob default "hello");
 
@@ -316,10 +322,16 @@ drop table t1;
 
 #
 # Test blob's with end space (Bug #1651)
+# This is a bit changed since we now have true varchar
 #
 
 create table t1 (id integer primary key auto_increment, txt text not null, unique index txt_index (txt (20)));
+--error 1062
 insert into t1 (txt) values ('Chevy'), ('Chevy ');
+--error 1062
+insert into t1 (txt) values ('Chevy'), ('CHEVY');
+alter table t1 drop index txt_index, add index txt_index (txt(20));
+insert into t1 (txt) values ('Chevy ');
 select * from t1 where txt='Chevy';
 select * from t1 where txt='Chevy ';
 select * from t1 where txt='Chevy ' or txt='Chevy';
@@ -340,7 +352,7 @@ select * from t1 where txt > 'Chevy';
 select * from t1 where txt >= 'Chevy';
 drop table t1;
 
-create table t1 (id integer primary key auto_increment, txt text, unique index txt_index (txt (20)));
+create table t1 (id integer primary key auto_increment, txt text, index txt_index (txt (20)));
 insert into t1 (txt) values ('Chevy'), ('Chevy '), (NULL);
 select * from t1 where txt='Chevy' or txt is NULL;
 explain select * from t1 where txt='Chevy' or txt is NULL;