1# Initialise 2--disable_warnings 3drop table if exists t1,t2,t3; 4--enable_warnings 5 6disable_query_log; 7select "--- Testing varchar ---"; 8enable_query_log; 9 10# 11# Simple basic test that endspace is saved 12# 13 14# 15# Remember to check that one doesn't get a warning or a note 16# from a char field when end spaces get removed. SQL standard! 17# 18 19create table t1 (v varchar(10), c char(10), t text); 20insert into t1 values('+ ', '+ ', '+ '); 21set @a=repeat(' ',20); 22insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a)); 23select concat('*',v,'*',c,'*',t,'*') from t1; 24 25# Check how columns are copied 26show create table t1; 27create table t2 like t1; 28show create table t2; 29create table t3 select * from t1; 30show create table t3; 31alter table t1 modify c varchar(10); 32show create table t1; 33alter table t1 modify v char(10); 34show create table t1; 35alter table t1 modify t varchar(10); 36show create table t1; 37select concat('*',v,'*',c,'*',t,'*') from t1; 38drop table t1,t2,t3; 39 40# 41# Testing of keys 42# 43create table t1 (v varchar(10), c char(10), t text, key(v), key(c), key(t(10))); 44show create table t1; 45disable_query_log; 46let $1=10; 47while ($1) 48{ 49 let $2=27; 50 eval set @space=repeat(' ',10-$1); 51 while ($2) 52 { 53 eval set @char=char(ascii('a')+$2-1); 54 insert into t1 values(concat(@char,@space),concat(@char,@space),concat(@char,@space)); 55 dec $2; 56 } 57 dec $1; 58} 59enable_query_log; 60select count(*) from t1; 61insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1))); 62select count(*) from t1 where v='a'; 63select count(*) from t1 where c='a'; 64select count(*) from t1 where t='a'; 65select count(*) from t1 where v='a '; 66select count(*) from t1 where c='a '; 67select count(*) from t1 where t='a '; 68select count(*) from t1 where v between 'a' and 'a '; 69select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 70select count(*) from t1 where v like 'a%'; 71select count(*) from t1 where c like 'a%'; 72select count(*) from t1 where t like 'a%'; 73select count(*) from t1 where v like 'a %'; 74# Test results differ for BDB, see comments in bdb.test 75# and they are also different from MySAM test results. 76--replace_column 9 # 77explain select count(*) from t1 where v='a '; 78--replace_column 9 # 79explain select count(*) from t1 where c='a '; 80--replace_column 9 # 81explain select count(*) from t1 where t='a '; 82--replace_column 9 # 83explain select count(*) from t1 where v like 'a%'; 84--replace_column 9 # 85explain select count(*) from t1 where v between 'a' and 'a '; 86--replace_column 9 # 87explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 88 89# Which duplicate entry triggers error is not deterministic. 90--replace_regex /Duplicate entry '[^']+' for key/Duplicate entry '{ ' for key/ 91--error ER_DUP_ENTRY 92alter table t1 add unique(v); 93show warnings; 94alter table t1 add key(v); 95select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a'; 96--replace_column 6 # 9 # 10 # 97explain select * from t1 where v='a'; 98 99# GROUP BY 100 101select v,count(*) from t1 group by v limit 10; 102select v,count(t) from t1 group by v limit 10; 103select v,count(c) from t1 group by v limit 10; 104select sql_big_result v,count(t) from t1 group by v limit 10; 105select sql_big_result v,count(c) from t1 group by v limit 10; 106select c,count(*) from t1 group by c limit 10; 107select c,count(t) from t1 group by c limit 10; 108select sql_big_result c,count(t) from t1 group by c limit 10; 109select t,count(*) from t1 group by t limit 10; 110select t,count(t) from t1 group by t limit 10; 111select sql_big_result t,count(t) from t1 group by t limit 10; 112 113# 114# Test varchar > 255 bytes 115# 116 117alter table t1 modify v varchar(300), drop key v, drop key v_2, add key v (v); 118show create table t1; 119select count(*) from t1 where v='a'; 120select count(*) from t1 where v='a '; 121select count(*) from t1 where v between 'a' and 'a '; 122select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 123select count(*) from t1 where v like 'a%'; 124select count(*) from t1 where v like 'a %'; 125--replace_column 9 # 126explain select count(*) from t1 where v='a '; 127--replace_column 9 # 128explain select count(*) from t1 where v like 'a%'; 129--replace_column 9 # 130explain select count(*) from t1 where v between 'a' and 'a '; 131--replace_column 9 # 132explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 133--replace_column 9 # 10 # 134explain select * from t1 where v='a'; 135 136# GROUP BY 137 138select v,count(*) from t1 group by v limit 10; 139select v,count(t) from t1 group by v limit 10; 140select sql_big_result v,count(t) from t1 group by v limit 10; 141 142# 143# Test varchar > 255 bytes, key < 255 144# 145 146alter table t1 drop key v, add key v (v(30)); 147show create table t1; 148select count(*) from t1 where v='a'; 149select count(*) from t1 where v='a '; 150select count(*) from t1 where v between 'a' and 'a '; 151select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 152select count(*) from t1 where v like 'a%'; 153select count(*) from t1 where v like 'a %'; 154--replace_column 9 # 155explain select count(*) from t1 where v='a '; 156--replace_column 9 # 157explain select count(*) from t1 where v like 'a%'; 158--replace_column 9 # 159explain select count(*) from t1 where v between 'a' and 'a '; 160--replace_column 9 # 161explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; 162--replace_column 9 # 10 # 163explain select * from t1 where v='a'; 164 165# GROUP BY 166 167select v,count(*) from t1 group by v limit 10; 168select v,count(t) from t1 group by v limit 10; 169select sql_big_result v,count(t) from t1 group by v limit 10; 170 171# 172# Test varchar > 512 (special case for GROUP BY becasue of 173# CONVERT_IF_BIGGER_TO_BLOB define) 174# 175 176alter table t1 modify v varchar(600), drop key v, add key v (v); 177show create table t1; 178select v,count(*) from t1 group by v limit 10; 179select v,count(t) from t1 group by v limit 10; 180select sql_big_result v,count(t) from t1 group by v limit 10; 181 182drop table t1; 183 184# 185# Test unique keys 186# 187 188create table t1 (a char(10), unique (a)); 189insert into t1 values ('a '); 190--error ER_DUP_ENTRY 191insert into t1 values ('a '); 192 193alter table t1 modify a varchar(10); 194--error ER_DUP_ENTRY 195insert into t1 values ('a '),('a '),('a '),('a '); 196--error ER_DUP_ENTRY 197insert into t1 values ('a '); 198--error ER_DUP_ENTRY 199insert into t1 values ('a '); 200--error ER_DUP_ENTRY 201insert into t1 values ('a '); 202update t1 set a='a ' where a like 'a%'; 203select concat(a,'.') from t1; 204update t1 set a='abc ' where a like 'a '; 205select concat(a,'.') from t1; 206update t1 set a='a ' where a like 'a %'; 207select concat(a,'.') from t1; 208update t1 set a='a ' where a like 'a '; 209select concat(a,'.') from t1; 210drop table t1; 211 212# 213# test show create table 214# 215 216create table t1 (v varchar(10), c char(10), t text, key(v(5)), key(c(5)), key(t(5))); 217show create table t1; 218drop table t1; 219create table t1 (v char(10) character set utf8); 220show create table t1; 221drop table t1; 222 223create table t1 (v varchar(10), c char(10)) row_format=fixed; 224show create table t1; 225insert into t1 values('a','a'),('a ','a '); 226select concat('*',v,'*',c,'*') from t1; 227drop table t1; 228 229# 230# Test long varchars 231# 232 233create table t1 (v varchar(65530), key(v(10))); 234insert into t1 values(repeat('a',65530)); 235select length(v) from t1 where v=repeat('a',65530); 236drop table t1; 237 238# 239# Bug #9489: problem with hash indexes 240# Bug #10802: Index is not used if table using BDB engine on HP-UX 241# 242 243create table t1(a int, b varchar(12), key ba(b, a)); 244insert into t1 values (1, 'A'), (20, NULL); 245explain select * from t1 where a=20 and b is null; 246select * from t1 where a=20 and b is null; 247drop table t1; 248