diff options
Diffstat (limited to 'mysql-test/include/varchar.inc')
| -rw-r--r-- | mysql-test/include/varchar.inc | 247 |
1 files changed, 247 insertions, 0 deletions
diff --git a/mysql-test/include/varchar.inc b/mysql-test/include/varchar.inc new file mode 100644 index 00000000..7add7113 --- /dev/null +++ b/mysql-test/include/varchar.inc @@ -0,0 +1,247 @@ +# Initialise +--disable_warnings +drop table if exists t1,t2,t3; +--enable_warnings + +disable_query_log; +select "--- Testing varchar ---"; +enable_query_log; + +# +# Simple basic test that endspace is saved +# + +# +# Remember to check that one doesn't get a warning or a note +# from a char field when end spaces get removed. SQL standard! +# + +create table t1 (v varchar(10), c char(10), t text); +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 text, 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 %'; +# Test results differ for BDB, see comments in bdb.test +# and they are also different from MySAM test results. +--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'; + +# Which duplicate entry triggers error is not deterministic. +--replace_regex /Duplicate entry '[^']+' for key/Duplicate entry '{ ' for key/ +--error ER_DUP_ENTRY +alter table t1 add unique(v); +show warnings; +alter table t1 add key(v); +select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a'; +--replace_column 6 # 9 # 10 # +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; + +# +# Test varchar > 255 bytes +# + +alter table t1 modify v varchar(300), drop key v, drop key v_2, add key v (v); +show create table t1; +select count(*) from t1 where v='a'; +select count(*) from t1 where v='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 v like 'a %'; +--replace_column 9 # +explain select count(*) from t1 where v='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'; +--replace_column 9 # 10 # +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 sql_big_result v,count(t) from t1 group by v limit 10; + +# +# Test varchar > 255 bytes, key < 255 +# + +alter table t1 drop key v, add key v (v(30)); +show create table t1; +select count(*) from t1 where v='a'; +select count(*) from t1 where v='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 v like 'a %'; +--replace_column 9 # +explain select count(*) from t1 where v='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'; +--replace_column 9 # 10 # +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 sql_big_result v,count(t) from t1 group by v limit 10; + +# +# Test varchar > 512 (special case for GROUP BY becasue of +# CONVERT_IF_BIGGER_TO_BLOB define) +# + +alter table t1 modify v varchar(600), drop key v, add key v (v); +show create table t1; +select v,count(*) from t1 group by v limit 10; +select v,count(t) from t1 group by v limit 10; +select sql_big_result v,count(t) from t1 group by v 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%'; +select concat(a,'.') from t1; +update t1 set a='abc ' where a like 'a '; +select concat(a,'.') from t1; +update t1 set a='a ' where a like 'a %'; +select concat(a,'.') from t1; +update t1 set a='a ' where a like 'a '; +select concat(a,'.') from t1; +drop table t1; + +# +# test show create table +# + +create table t1 (v varchar(10), c char(10), t text, key(v(5)), key(c(5)), key(t(5))); +show create table t1; +drop table t1; +create table t1 (v char(10) character set utf8); +show create table t1; +drop table t1; + +create table t1 (v varchar(10), c char(10)) row_format=fixed; +show create table t1; +insert into t1 values('a','a'),('a ','a '); +select concat('*',v,'*',c,'*') from t1; +drop table t1; + +# +# Test long varchars +# + +create table t1 (v varchar(65530), key(v(10))); +insert into t1 values(repeat('a',65530)); +select length(v) from t1 where v=repeat('a',65530); +drop table t1; + +# +# Bug #9489: problem with hash indexes +# Bug #10802: Index is not used if table using BDB engine on HP-UX +# + +create table t1(a int, b varchar(12), key ba(b, a)); +insert into t1 values (1, 'A'), (20, NULL); +explain select * from t1 where a=20 and b is null; +select * from t1 where a=20 and b is null; +drop table t1; |