Bug #45196 Some collations do not sort correctly with IBMDB2I
Submitted: 29 May 2009 14:26 Modified: 29 Jun 2009 2:03
Reporter: Tim Clark Email Updates:
Status: Patch pending Impact on me:
None 
Category:MySQL Server: DB2SE for IBM i Severity:S3 (Non-critical)
Version:5.1.33 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: collation, czech, danish, ibmdb2i

[29 May 2009 14:26] Tim Clark
Description:
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.

See "How to repeat" for specific examples.

How to repeat:
For cp1250_czech_cs:

> create table t1 (c char(10), index(c)) collate cp1250_czech_cs engine=ibmdb2i;
> insert into t1 values ("ch"),("h"),("i");
> select * from t1 order by c;
+------+
| c    |
+------+
| ch   |
| h    |
| i    |
+------+
3 rows in set (0.04 sec)

> alter table t1 engine=myisam;
> select * from t1 order by c;
+------+
| c    |
+------+
| h    |
| ch   |
| i    |
+------+
3 rows in set (0.03 sec)

For ucs2_danish_ci:

> 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    |                                  
 +------+                                  
 | aaaa |                                  
 | abc  |                                  
 | abcd |                                  
 +------+                                  
 3 rows in set (0.02 sec)                  

> alter table t1 engine=myisam;             
                                             
> select c from t1 order by c;              
 +------+                                  
 | c    |                                  
 +------+                                  
 | abc  |                                  
 | abcd |                                  
 | aaaa |                                  
 +------+                                      
 3 rows in set (0.00 sec)    

Suggested fix:
Correct these collations or remove them from the list of collations supported by IBMDB2I.
[29 Jun 2009 2:03] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/77416

2982 V Narayanan	2009-06-29
      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.
     @ mysql-test/suite/ibmdb2i/r/ibmdb2i_bug_45196.result
        Bug#45196  Some collations do not sort correctly with IBMDB2I
        
        Result file for the test case.
     @ mysql-test/suite/ibmdb2i/t/ibmdb2i_bug_45196.test
        Bug#45196  Some collations do not sort correctly with IBMDB2I
        
        Adding tests for testing the sort order with the modified collations.
     @ storage/ibmdb2i/db2i_collationSupport.cc
        Bug#45196  Some collations do not sort correctly with IBMDB2I
        
        Remove the 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.
[8 Jul 2009 13:30] Bugs System
Pushed into 5.1.37 (revid:joro@sun.com-20090708131116-kyz8iotbum8w9yic) (version source revid:v.narayanan@sun.com-20090629020217-e8xn0c2xiee2hidj) (merge vers: 5.1.37) (pib:11)
[9 Jul 2009 7:36] Bugs System
Pushed into 5.1.37 (revid:joro@sun.com-20090708131116-kyz8iotbum8w9yic) (version source revid:v.narayanan@sun.com-20090629020217-e8xn0c2xiee2hidj) (merge vers: 5.1.37) (pib:11)
[10 Jul 2009 11:20] Bugs System
Pushed into 5.4.4-alpha (revid:anozdrin@bk-internal.mysql.com-20090710111017-bnh2cau84ug1hvei) (version source revid:v.narayanan@sun.com-20090629093429-51ipymxqrh0n1skw) (merge vers: 5.4.4-alpha) (pib:11)
[26 Aug 2009 13:46] Bugs System
Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[26 Aug 2009 13:46] Bugs System
Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)
[26 Aug 2009 13:48] Bugs System
Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)
[27 Aug 2009 16:32] Bugs System
Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)