396 lines
9.1 KiB
Plaintext
396 lines
9.1 KiB
Plaintext
--source include/have_ndb.inc
|
|
|
|
--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
|
|
--error ER_DUP_ENTRY
|
|
insert into t1 values('aaa');
|
|
--error ER_DUP_ENTRY
|
|
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;
|
|
|
|
# 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');
|
|
-- error ER_DUP_ENTRY
|
|
insert into t1 values('b');
|
|
-- error ER_DUP_ENTRY
|
|
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;
|
|
|
|
# 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
|
|
--error ER_DUP_ENTRY
|
|
insert into t1 values(2, 'aaa');
|
|
--error ER_DUP_ENTRY
|
|
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;
|
|
|
|
# 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');
|
|
-- error ER_DUP_ENTRY
|
|
insert into t1 values(99,'b');
|
|
-- error ER_DUP_ENTRY
|
|
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;
|
|
|
|
# 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;
|
|
# plan too flaky
|
|
#--replace_column 10 # 11 #
|
|
#explain select * from t1 where a = 'zZz' order by p;
|
|
# 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;
|
|
# plan too flaky
|
|
#--replace_column 10 # 11 #
|
|
#explain select * from t1 where a = 'zZz' 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;
|
|
# 6
|
|
select * from t1 where a = 'AAA' order by p;
|
|
drop table t1;
|
|
|
|
# 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
|
|
# 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;
|
|
#
|
|
#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');
|
|
#-- error ER_DUP_ENTRY
|
|
#insert into t1 values('d','f');
|
|
#
|
|
#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;
|
|
|
|
# bug#14007
|
|
create table t1 (
|
|
a char(10) primary key
|
|
) 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';
|
|
select * from t1;
|
|
drop table t1;
|
|
|
|
# bug#33158 NDB table name problem(sensitive/insensitive)
|
|
# Check that tables with same letters, but different case
|
|
# don't conflict
|
|
create table t1(a int) engine = ndbcluster;
|
|
|
|
if (`SELECT @@lower_case_table_names = 0`)
|
|
{
|
|
create table T1(a int) engine = ndbcluster;
|
|
drop table T1;
|
|
}
|
|
if (`SELECT @@lower_case_table_names <> 0`)
|
|
{
|
|
--echo create table T1(a int) engine = ndbcluster;
|
|
--echo drop table T1;
|
|
}
|
|
drop table t1;
|
|
|
|
|
|
# End of 4.1 tests
|
|
|
|
|
|
# Start of 8.0 tests
|
|
|
|
# Bug #27477829 UNICODE 9.0 COLLATIONS INCORRECTLY DETECT DUPLICATES
|
|
# Truncation of the xfrm'ed normalized string caused false
|
|
# detection of duplicates.
|
|
|
|
create table t1 (
|
|
c1 varchar(10) collate utf8mb4_0900_as_cs,
|
|
primary key(c1)
|
|
) engine = ndbcluster;
|
|
|
|
|
|
#Inserting short string was OK.
|
|
insert into t1 values('ABC_x'), ('ABC_X');
|
|
|
|
# However, longer strings were truncated when transformed into
|
|
# a normalized form. Thus false duplicates were detected.
|
|
insert into t1 values('ABCDEF_x'), ('ABCDEF_X');
|
|
insert into t1 values('ABCDEFGH_x'), ('ABCDEFGH_X');
|
|
|
|
--sorted_result
|
|
select * from t1;
|
|
|
|
drop table t1;
|
|
|
|
####################################
|
|
# Bug#27515000 ERROR 743: CHARSET UTF8MB4_JA_0900_AS_CS_KS IS NOT SUPPORTED
|
|
#
|
|
# The above character set could not be used in any kind
|
|
# of index or key. Below test cases should pass after fix.
|
|
|
|
create table t1 (
|
|
c1 varchar(10) collate utf8mb4_ja_0900_as_cs_ks,
|
|
primary key(c1)
|
|
) engine = ndbcluster;
|
|
|
|
drop table t1;
|
|
|
|
|
|
create table t1 (
|
|
c1 varchar(10) collate utf8mb4_ja_0900_as_cs_ks,
|
|
primary key using hash(c1)
|
|
) engine = ndbcluster;
|
|
|
|
drop table t1;
|
|
|
|
|
|
create table t1 (
|
|
c1 varchar(10) collate utf8mb4_ja_0900_as_cs_ks,
|
|
unique key(c1)
|
|
) engine = ndbcluster;
|
|
|
|
drop table t1;
|
|
|
|
|
|
create table t1 (
|
|
c1 varchar(10) not null collate utf8mb4_ja_0900_as_cs_ks,
|
|
unique key using hash(c1)
|
|
) engine = ndbcluster;
|
|
|
|
drop table t1;
|
|
|
|
create table t1 (
|
|
c1 varchar(10) collate utf8mb4_ja_0900_as_cs_ks,
|
|
key(c1)
|
|
) engine = ndbcluster;
|
|
|
|
drop table t1;
|
|
|
|
####################################
|
|
# Bug#29322313 ANGSTROM AND A-RING NOT EQUAL WITH CHAR(N) COLUMN AND UTF8MB4_0900_AI_CI
|
|
#
|
|
# NO_PAD collations did not ignore trailing spaces as supposed to
|
|
# when hashing and comparing fix character columns.
|
|
|
|
# Insert two strings being binary different, but compares equal
|
|
# Require 2'nd insert to fail.
|
|
|
|
create table t (
|
|
ck char(1) collate utf8mb4_0900_ai_ci primary key
|
|
) engine=ndb;
|
|
|
|
insert into t values (_utf16 X'212B');
|
|
--error ER_DUP_ENTRY
|
|
insert into t values (_utf16 X'00C5');
|
|
select count(*) from t;
|
|
drop table t;
|
|
|
|
create table t (
|
|
ck char(10) collate utf8mb4_0900_ai_ci primary key
|
|
) engine=ndb;
|
|
|
|
insert into t values (_utf16 X'212B');
|
|
--error ER_DUP_ENTRY
|
|
insert into t values (_utf16 X'00C5');
|
|
select count(*) from t;
|
|
drop table t;
|
|
|
|
create table t (
|
|
ck varchar(1) collate utf8mb4_0900_ai_ci primary key
|
|
) engine=ndb;
|
|
|
|
insert into t values (_utf16 X'212B');
|
|
--error ER_DUP_ENTRY
|
|
insert into t values (_utf16 X'00C5');
|
|
select count(*) from t;
|
|
drop table t;
|
|
|
|
create table t (
|
|
ck varchar(10) collate utf8mb4_0900_ai_ci primary key
|
|
) engine=ndb;
|
|
|
|
insert into t values (_utf16 X'212B');
|
|
--error ER_DUP_ENTRY
|
|
insert into t values (_utf16 X'00C5');
|
|
select count(*) from t;
|
|
drop table t;
|
|
|
|
|
|
# End of 8.0 tests |