ndb_charset.test 6.07 KB
Newer Older
1
--source include/have_ndb.inc
2
-- source include/have_binlog_format_row.inc
3
-- source include/not_embedded.inc
4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41

--disable_warnings
drop table if exists t1;
--enable_warnings

#
# Minimal NDB charset test.
#

# pk - binary

create table t1 (
  a char(3) character set latin1 collate latin1_bin primary key
) engine=ndb;
# ok
insert into t1 values('aAa');
insert into t1 values('aaa');
insert into t1 values('AAA');
# 3
select * from t1 order by a;
# 1
select * from t1 where a = 'aAa';
# 1
select * from t1 where a = 'aaa';
# 0
select * from t1 where a = 'AaA';
# 1
select * from t1 where a = 'AAA';
drop table t1;

# pk - case insensitive

create table t1 (
  a char(3) character set latin1 collate latin1_swedish_ci primary key
) engine=ndb;
# ok
insert into t1 values('aAa');
# fail
42
--error ER_DUP_ENTRY
43
insert into t1 values('aaa');
44
--error ER_DUP_ENTRY
45 46 47 48 49 50 51 52 53 54 55 56 57
insert into t1 values('AAA');
# 1
select * from t1 order by a;
# 1
select * from t1 where a = 'aAa';
# 1
select * from t1 where a = 'aaa';
# 1
select * from t1 where a = 'AaA';
# 1
select * from t1 where a = 'AAA';
drop table t1;

pekka@mysql.com's avatar
pekka@mysql.com committed
58 59 60 61 62 63 64
# pk - varchar

create table t1 (
  a varchar(20) character set latin1 collate latin1_swedish_ci primary key
) engine=ndb;
#
insert into t1 values ('A'),('b '),('C  '),('d      '),('E'),('f');
65
-- error ER_DUP_ENTRY
pekka@mysql.com's avatar
pekka@mysql.com committed
66
insert into t1 values('b');
67
-- error ER_DUP_ENTRY
pekka@mysql.com's avatar
pekka@mysql.com committed
68 69 70 71 72 73 74 75 76
insert into t1 values('a         ');
#
select a,length(a) from t1 order by a;
select a,length(a) from t1 order by a desc;
select * from t1 where a = 'a';
select * from t1 where a = 'a   ';
select * from t1 where a = 'd';
drop table t1;

77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109
# unique hash index - binary

create table t1 (
  p int primary key,
  a char(3) character set latin1 collate latin1_bin not null,
  unique key(a)
) engine=ndb;
# ok
insert into t1 values(1, 'aAa');
insert into t1 values(2, 'aaa');
insert into t1 values(3, 'AAA');
# 3
select * from t1 order by p;
# 1
select * from t1 where a = 'aAa';
# 1
select * from t1 where a = 'aaa';
# 0
select * from t1 where a = 'AaA';
# 1
select * from t1 where a = 'AAA';
drop table t1;

# unique hash index - case insensitive

create table t1 (
  p int primary key,
  a char(3) character set latin1 collate latin1_swedish_ci not null,
  unique key(a)
) engine=ndb;
# ok
insert into t1 values(1, 'aAa');
# fail
110
--error ER_DUP_ENTRY
111
insert into t1 values(2, 'aaa');
112
--error ER_DUP_ENTRY
113 114 115 116 117 118 119 120 121 122 123 124 125
insert into t1 values(3, 'AAA');
# 1
select * from t1 order by p;
# 1
select * from t1 where a = 'aAa';
# 1
select * from t1 where a = 'aaa';
# 1
select * from t1 where a = 'AaA';
# 1
select * from t1 where a = 'AAA';
drop table t1;

pekka@mysql.com's avatar
pekka@mysql.com committed
126 127 128 129 130 131 132 133 134
# unique hash index - varchar

create table t1 (
  p int primary key,
  a varchar(20) character set latin1 collate latin1_swedish_ci not null,
  unique key(a)
) engine=ndb;
#
insert into t1 values (1,'A'),(2,'b '),(3,'C  '),(4,'d      '),(5,'E'),(6,'f');
135
-- error ER_DUP_ENTRY
pekka@mysql.com's avatar
pekka@mysql.com committed
136
insert into t1 values(99,'b');
137
-- error ER_DUP_ENTRY
pekka@mysql.com's avatar
pekka@mysql.com committed
138 139 140 141 142 143 144 145 146
insert into t1 values(99,'a         ');
#
select a,length(a) from t1 order by a;
select a,length(a) from t1 order by a desc;
select * from t1 where a = 'a';
select * from t1 where a = 'a   ';
select * from t1 where a = 'd';
drop table t1;

147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162
# ordered index - binary

create table t1 (
  p int primary key,
  a char(3) character set latin1 collate latin1_bin not null,
  index(a)
) engine=ndb;
# ok
insert into t1 values(1, 'aAa');
insert into t1 values(2, 'aaa');
insert into t1 values(3, 'AAA');
insert into t1 values(4, 'aAa');
insert into t1 values(5, 'aaa');
insert into t1 values(6, 'AAA');
# 6
select * from t1 order by p;
163 164 165
# plan too flaky
#--replace_column 9 #
#explain select * from t1 where a = 'zZz' order by p;
166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191
# 2
select * from t1 where a = 'aAa' order by p;
# 2
select * from t1 where a = 'aaa' order by p;
# 0
select * from t1 where a = 'AaA' order by p;
# 2
select * from t1 where a = 'AAA' order by p;
drop table t1;

# ordered index - case insensitive

create table t1 (
  p int primary key,
  a char(3) character set latin1 collate latin1_swedish_ci not null,
  index(a)
) engine=ndb;
# ok
insert into t1 values(1, 'aAa');
insert into t1 values(2, 'aaa');
insert into t1 values(3, 'AAA');
insert into t1 values(4, 'aAa');
insert into t1 values(5, 'aaa');
insert into t1 values(6, 'AAA');
# 6
select * from t1 order by p;
192 193 194
# plan too flaky
#--replace_column 9 #
#explain select * from t1 where a = 'zZz' order by p;
195 196 197 198 199 200 201 202 203
# 6
select * from t1 where a = 'aAa' order by p;
# 6
select * from t1 where a = 'aaa' order by p;
# 6
select * from t1 where a = 'AaA' order by p;
# 6
select * from t1 where a = 'AAA' order by p;
drop table t1;
204

pekka@mysql.com's avatar
pekka@mysql.com committed
205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223
# ordered index - varchar

create table t1 (
  p int primary key,
  a varchar(20) character set latin1 collate latin1_swedish_ci not null,
  index(a, p)
) engine=ndb;
#
insert into t1 values (1,'A'),(2,'b '),(3,'C  '),(4,'d      '),(5,'E'),(6,'f');
insert into t1 values (7,'a'),(8,'B '),(9,'c  '),(10,'D'),(11,'e'),(12,'F  ');
select p,a,length(a) from t1 order by a, p;
select * from t1 where a = 'a  ' order by a desc, p desc;
select * from t1 where a >= 'D' order by a, p;
select * from t1 where a < 'D' order by a, p;
#
select count(*) from t1 x, t1 y, t1 z where x.a = y.a and y.a = z.a;
drop table t1;

# minimal multi-byte test
joreland@mysql.com's avatar
ndb -  
joreland@mysql.com committed
224 225 226 227 228 229
# removed by jonas as this requires a configure --with-extra-charsets
#create table t1 (
#  a char(5) character set ucs2,
#  b varchar(7) character set utf8,
#  primary key(a, b)
#) engine=ndb;
pekka@mysql.com's avatar
pekka@mysql.com committed
230
#
joreland@mysql.com's avatar
ndb -  
joreland@mysql.com committed
231 232 233 234
#insert into t1 values
#  ('a','A '),('B   ','b'),('c','C    '),('D','d'),('e  ','E'),('F','f  '),
#  ('A','b '),('b   ','C'),('C','d  '),('d','E'),('E  ','f'),
#  ('a','C '),('B   ','d'),('c','E  '),('D','f');
235
#-- error ER_DUP_ENTRY
joreland@mysql.com's avatar
ndb -  
joreland@mysql.com committed
236
#insert into t1 values('d','f');
pekka@mysql.com's avatar
pekka@mysql.com committed
237
#
joreland@mysql.com's avatar
ndb -  
joreland@mysql.com committed
238 239 240 241
#select a,b,length(a),length(b) from t1 order by a,b limit 3;
#select a,b,length(a),length(b) from t1 order by a desc, b desc limit 3;
#select a,b,length(a),length(b) from t1 where a='c' and b='c';
#drop table t1;
pekka@mysql.com's avatar
pekka@mysql.com committed
242

243
# bug#14007
244
create table t1 (
pekka@mysql.com's avatar
pekka@mysql.com committed
245
  a char(10) primary key
246 247 248 249 250 251 252 253 254
) engine=ndbcluster default charset=latin1;

insert into t1 values ('aaabb');
select * from t1;
replace into t1 set a = 'AAABB';
select * from t1;
replace into t1 set a = 'aAaBb';
select * from t1;
replace into t1 set a = 'aaabb';
255 256
select * from t1;
drop table t1;
257 258

# End of 4.1 tests