Bug#45196 Some collations do not sort correctly with IBMDB2I

Some collations--including cp1250_czech_cs,latin2_czech_cs,
ucs2/utf8_czech_ci, ucs2/utf8_danish_ci--are not being
sorted correctly by the IBMDB2I storage engine. This
was being caused because the sort order used by DB2 is
incompatible with the order expected by MySQL.

This patch removes support for the cp1250_czech_cs and
latin2_czech_cs collations because it has been determined
that the sort order used by DB2 is incompatible with the
order expected by MySQL. Users needing a czech collation
with IBMDB2I are encouraged to use a Unicode-based collation 
instead of these single-byte collations. This patch also
modifies the DB2 sort sequence used for ucs2/utf8_czech_ci
and ucs2/utf8_danish_ci collations to better match the
sorting expected by MySQL. This will only affect indexes
or tables that are newly created through the IBMDB2I storage
engine. Existing IBMDB2I tables will retain the old sort
sequence until recreated.

2 files changed, 59 insertions, 0 deletions

diff --git a/mysql-test/suite/ibmdb2i/r/ibmdb2i_bug_45196.result b/mysql-test/suite/ibmdb2i/r/ibmdb2i_bug_45196.result
new file mode 100644
index 00000000000..916e1d93ee5
--- /dev/null
+++ b/mysql-test/suite/ibmdb2i/r/ibmdb2i_bug_45196.result
@@ -0,0 +1,33 @@
+drop table if exists t1;
+create table t1 (c char(10), index(c)) collate ucs2_czech_ci engine=ibmdb2i;
+insert into t1 values ("ch"),("h"),("i");
+select * from t1 order by c;
+c
+h
+ch
+i
+drop table t1;
+create table t1 (c char(10), index(c)) collate utf8_czech_ci engine=ibmdb2i;
+insert into t1 values ("ch"),("h"),("i");
+select * from t1 order by c;
+c
+h
+ch
+i
+drop table t1;
+create table t1 (c char(10), index(c)) collate ucs2_danish_ci engine=ibmdb2i;
+insert into t1 values("abc"),("abcd"),("aaaa");
+select c from t1 order by c;
+c
+abc
+abcd
+aaaa
+drop table t1;
+create table t1 (c char(10), index(c)) collate utf8_danish_ci engine=ibmdb2i;
+insert into t1 values("abc"),("abcd"),("aaaa");
+select c from t1 order by c;
+c
+abc
+abcd
+aaaa
+drop table t1;
diff --git a/mysql-test/suite/ibmdb2i/t/ibmdb2i_bug_45196.test b/mysql-test/suite/ibmdb2i/t/ibmdb2i_bug_45196.test
new file mode 100644
index 00000000000..17b1d658975
--- /dev/null
+++ b/mysql-test/suite/ibmdb2i/t/ibmdb2i_bug_45196.test
@@ -0,0 +1,26 @@
+source suite/ibmdb2i/include/have_ibmdb2i.inc;
+source suite/ibmdb2i/include/have_i61.inc;
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+
+create table t1 (c char(10), index(c)) collate ucs2_czech_ci engine=ibmdb2i;
+insert into t1 values ("ch"),("h"),("i");
+select * from t1 order by c;
+drop table t1;
+
+create table t1 (c char(10), index(c)) collate utf8_czech_ci engine=ibmdb2i;
+insert into t1 values ("ch"),("h"),("i");
+select * from t1 order by c;
+drop table t1;
+
+create table t1 (c char(10), index(c)) collate ucs2_danish_ci engine=ibmdb2i;           
+insert into t1 values("abc"),("abcd"),("aaaa"); 
+select c from t1 order by c;
+drop table t1;
+
+create table t1 (c char(10), index(c)) collate utf8_danish_ci engine=ibmdb2i;           
+insert into t1 values("abc"),("abcd"),("aaaa"); 
+select c from t1 order by c;
+drop table t1;