Bug #44352 UPPER/LOWER function doesn't work correctly on cp932 and sjis environment.
Submitted: 18 Apr 2009 8:00 Modified: 26 Jun 2009 2:10
Reporter: 佐到士 立岡 Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Charsets Severity:S2 (Serious)
Version:5.0 and later OS:Any
Assigned to: Alexander Barkov
Tags: Contribution, cp932, LOWER, sjis, upper
Triage: Triaged: D2 (Serious) / R2 (Low) / E2 (Low)

[18 Apr 2009 8:00] 佐到士 立岡
Description:
UPPER/LOWER function doesn't work correctly on cp932 and sjis environment.

How to repeat:
mysql> create table t (a varchar(16)) engine=myisam default charset=cp932;
mysql> insert into t values ("ビタ");
mysql> insert into t values ("コタ");
mysql> insert into t values ("ABC");
mysql> insert into t values ("abc");

mysql> SELECT a, HEX(a), HEX(UPPER(a)) FROM t;
+--------+----------+---------------+
| a      | HEX(a)   | HEX(UPPER(a)) |
+--------+----------+---------------+
| ビタ | 8372835E | 8352835E      | <---- second byte is changed!!
| コタ | 8352835E | 8352835E      | 
| ABC    | 414243   | 414243        | 
| abc    | 616263   | 414243        | 
+--------+----------+---------------+
4 rows in set (0.00 sec)

mysql> SELECT a, HEX(a), HEX(LOWER(a)) FROM t;
+--------+----------+---------------+
| a      | HEX(a)   | HEX(LOWER(a)) |
+--------+----------+---------------+
| ビタ | 8372835E | 8372835E      | 
| コタ | 8352835E | 8372835E      | <---- second byte is changed!!
| ABC    | 414243   | 616263        | 
| abc    | 616263   | 616263        | 
+--------+----------+---------------+
4 rows in set (0.00 sec)

Suggested fix:
Here is a patch.

--- mysql-5.0.77-org/strings/ctype-cp932.c	2009-01-30 06:45:47.000000000
+0900
+++ mysql-5.0.77-new/strings/ctype-cp932.c	2009-04-18 12:34:32.000000000
+0900
@@ -5485,10 +5485,10 @@
   my_numcells_cp932,
   my_mb_wc_cp932,	/* mb_wc */
   my_wc_mb_cp932,	/* wc_mb */
-  my_caseup_str_8bit,
-  my_casedn_str_8bit,
-  my_caseup_8bit,
-  my_casedn_8bit,
+  my_caseup_str_mb,
+  my_casedn_str_mb,
+  my_caseup_mb,
+  my_casedn_mb,
   my_snprintf_8bit,
   my_long10_to_str_8bit,
   my_longlong10_to_str_8bit,

mysql-5.0.77-new/strings/ctype-sjis.c
--- mysql-5.0.77-org/strings/ctype-sjis.c	2009-01-30 06:45:48.000000000
+0900
+++ mysql-5.0.77-new/strings/ctype-sjis.c	2009-04-18 12:35:00.000000000
+0900
@@ -4648,10 +4648,10 @@
   my_numcells_sjis,
   my_mb_wc_sjis,	/* mb_wc */
   my_wc_mb_sjis,	/* wc_mb */
-  my_caseup_str_8bit,
-  my_casedn_str_8bit,
-  my_caseup_8bit,
-  my_casedn_8bit,
+  my_caseup_str_mb,
+  my_casedn_str_mb,
+  my_caseup_mb,
+  my_casedn_mb,
   my_snprintf_8bit,
   my_long10_to_str_8bit,
   my_longlong10_to_str_8bit,
[19 Apr 2009 17:33] Valeriy Kravchuk
Thank you for the problem report and patch contributed. What OS do you use? I can not reproduce the problem on Mac OS X, for example.
[20 Apr 2009 2:13] 佐到士 立岡
It is not an OS specific problem.
You should change your terminal setting to utf8 or other multibyte Japansese character set.
'set names utf8' will be needed before testing when you use utf8 terminal client.
[21 Apr 2009 3:18] Kei SAKAI
Hi Valeriy,
If you cannot input the Japanese characters, please try below instead.

insert into t values (0x8372835E);
insert into t values (0x8352835E);

(and try SELECT a, HEX(a), HEX(UPPER(a)) FROM t;  etc..)
[23 Apr 2009 15:51] Valeriy Kravchuk
Verified just as described with recent 5.0 from bzr on Mac OS X. 

"set names utf8" is essential (I had used "set names cp932" initially).
[4 May 2009 8:10] Alexander Barkov
The same problem happens with cp932 (MS version of SJIS).
[4 May 2009 8:52] 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/73277
[4 May 2009 9:05] 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/73279
[5 May 2009 7:04] 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/73352
[5 May 2009 7:09] Alexander Barkov
Pushed into mysql-5.1-bugteam, currently marked as 5.1.35.
[26 May 2009 9:14] Masood Mortazavi
Satoshi -

I do not see your name among MySQL contributors here:
https://sca.dev.java.net/CA_signatories.htm

Since you seem interested in contributing to MySQL, may I suggest that you sign the Sun|MySQL contributor agreement, i.e. the SCA.

The instructions are given here:
http://forge.mysql.com/wiki/Sun_Contributor_Agreement

This will facilitate the handling of your contributions.
[28 May 2009 8:13] Bugs System
Pushed into 5.1.36 (revid:joro@sun.com-20090528073639-yohsb4q1jzg7ycws) (version source revid:mats@sun.com-20090511132802-nnkiyb2huih1tklz) (merge vers: 5.1.35) (pib:6)
[1 Jun 2009 20:17] Paul Dubois
Noted in 5.1.36 changelog.

Lettercase conversion in multibyte cp932 or sjis character sequences
could produce incorrect results.

Setting report to NDI pending push into 6.0.x.
[17 Jun 2009 19:21] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090616183122-chjzbaa30qopdra9) (version source revid:mhansson@mysql.com-20090505082504-f9goof5x1eyrmlyi) (merge vers: 6.0.12-alpha) (pib:11)
[26 Jun 2009 2:10] Paul Dubois
Noted in 5.4.4 changelog.
[12 Aug 2009 22:35] Paul Dubois
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[15 Aug 2009 1:53] 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:33] 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 20:26] Paul Dubois
The 5.4 fix has been pushed to 5.4.2.