Bug #45197 cp1250 character set with IBMDB2I generates 2027 error
Submitted: 29 May 2009 15:00 Modified: 14 Jul 2009 15:21
Reporter: Tim Clark Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DB2SE for IBM i Severity:S3 (Non-critical)
Version:5.1.33 OS:Any
Assigned to: Tim Clark CPU Architecture:Any
Tags: cp1250, ibmdb2i
Triage: Triaged: D3 (Medium)

[29 May 2009 15:00] Tim Clark
Description:
Running a SELECT query over an IBMDB2I table with a cp1250 character set produces an error in the MySQL error log:
ibmdb2i error 2027: Error converting single-byte sort sequence to UCS-2 

Further analysis indicates that this is coming from records_in_range(), and that the error prevents records_in_range() from returning correct estimates to the optimizer.

How to repeat:
create table t1 (c char(10), index(c)) character set cp1250 engine=ibmdb2i;
insert into t1 values ("this"),("that"),("the other"); 
select * from t1 where c like "th%";
>> error log shows: "ibmdb2i error 2027: Error converting single-byte sort sequence to UCS-2 "
[1 Jun 2009 7:22] 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/75346

2917 Narayanan V	2009-06-01
      Bug#45197 cp1250 character set with IBMDB2I generates 2027 error
      
      Running a SELECT query over an IBMDB2I table with a cp1250 character set
      was producing an error 2027 (ibmdb2i error 2027: Error converting single-byte
      sort sequence to UCS-2).
      
      The QMY_DESCRIBE_RANGE API was returning error 2027 to the storage engine
      because the CCSID used for a cp1250 column (870) does not match the CCSID
      used by the DB2 sort sequences associated with cp1250_* collations (1153).
      This was because the storage engine relies on a set of system APIs to
      determine which CCSID value most closely matches a particular MySQL
      character set. However, in the case of cp1250, the system is returning
      CCSID 870, which does not have a codepoint for the euro symbol, making it
      an incorrect match.
      
      This patch overrides the selection of a compatible CCSID to always return
      1153 for cp1250.
     @ storage/ibmdb2i/db2i_charsetSupport.cc
        Bug#45197 cp1250 character set with IBMDB2I generates 2027 error
        
        override the selection of a compatible CCSID to
        always return 1153 for cp1250.
[16 Jun 2009 11:03] Bugs System
Pushed into 5.1.36 (revid:joro@sun.com-20090616102155-3zhezogudt4uxdyn) (version source revid:v.narayanan@sun.com-20090601072210-7k9ea0xgqt11dp2b) (merge vers: 5.1.36) (pib:6)
[17 Jun 2009 19:23] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090616183122-chjzbaa30qopdra9) (version source revid:v.narayanan@sun.com-20090601073331-ok8y1rjbmqa3egsj) (merge vers: 6.0.12-alpha) (pib:11)
[14 Jul 2009 15:21] MC Brown
A note has been added to the 5.1.36 and 5.4.4 changelogs:

Running a SELECT query over an IBMDB2I table using the cp1250 character set would produce an error

ibmdb2i error 2027: Error converting single-byte sort sequence to UCS-2
[12 Aug 2009 22:18] Paul Dubois
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[14 Aug 2009 23:09] Paul Dubois
Ignore previous comment about 5.4.2.
[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)
[7 Oct 2009 18:38] Paul Dubois
The 5.4 fix has been pushed to 5.4.2.