Bug #13829 UPPER() doesn't seem to always work
Submitted: 7 Oct 2005 7:33 Modified: 10 Oct 2005 18:29
Reporter: Jeremy Barton Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:Ver 14.7 Distrib 4.1.14 readline 5.0 OS:Linux (Gentoo Linux 2.6.12-gentoo-r10)
Assigned to: CPU Architecture:Any

[7 Oct 2005 7:33] Jeremy Barton 
Description:
Twice I ran something through UPPER and it didn't return it in uppercase; yet the input was a valid string.  See cases in repro

How to repeat:
mysql> update Users set Password=upper(sha1(substring(Password, 9)))where Password like 'P%';
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7  Changed: 7  Warnings: 0

mysql> select Password, Username from Users;
+------------------------------------------+-------------+
| Password                                 | Username    |
+------------------------------------------+-------------+
| 54FD1711209FB1C0781092374132C66E79E2241B | xyon        |
| f10e2821bbbea527ea02200352313bc059445190 | ellism      |
| 3DA541559918A808C2402BBA5012F6C60B27661C | joanjett    |
| 3DA541559918A808C2402BBA5012F6C60B27661C | tmctest     |
| F1B699CC9AF3EEB98E5DE244CA7802AE38E77BAE | fmcbar      |
| F10E2821BBBEA527EA02200352313BC059445190 | nocash      |
| DF211CCDD94A63E0BCB9E6AE427A249484A49D60 | stillnocash |
| 3DA541559918A808C2402BBA5012F6C60B27661C | foo         |
+------------------------------------------+-------------+
8 rows in set (0.00 sec)

mysql> update Users set Password=upper(sha1('asdf')) where Username='ellism';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select Password, Username from Users;
+------------------------------------------+-------------+
| Password                                 | Username    |
+------------------------------------------+-------------+
| 54FD1711209FB1C0781092374132C66E79E2241B | xyon        |
| 3da541559918a808c2402bba5012f6c60b27661c | ellism      |
| 3DA541559918A808C2402BBA5012F6C60B27661C | joanjett    |
| 3DA541559918A808C2402BBA5012F6C60B27661C | tmctest     |
| F1B699CC9AF3EEB98E5DE244CA7802AE38E77BAE | fmcbar      |
| F10E2821BBBEA527EA02200352313BC059445190 | nocash      |
| DF211CCDD94A63E0BCB9E6AE427A249484A49D60 | stillnocash |
| 3DA541559918A808C2402BBA5012F6C60B27661C | foo         |
+------------------------------------------+-------------+
8 rows in set (0.00 sec)

mysql> update Users set Password='3DA541559918A808C2402BBA5012F6C60B27661C' where User='ellism';
ERROR 1054 (42S22): Unknown column 'User' in 'where clause'
mysql> update Users set Password='3DA541559918A808C2402BBA5012F6C60B27661C' where Username='ellism';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select Password, Username from Users;
+------------------------------------------+-------------+
| Password                                 | Username    |
+------------------------------------------+-------------+
| 54FD1711209FB1C0781092374132C66E79E2241B | xyon        |
| 3DA541559918A808C2402BBA5012F6C60B27661C | ellism      |
| 3DA541559918A808C2402BBA5012F6C60B27661C | joanjett    |
| 3DA541559918A808C2402BBA5012F6C60B27661C | tmctest     |
| F1B699CC9AF3EEB98E5DE244CA7802AE38E77BAE | fmcbar      |
| F10E2821BBBEA527EA02200352313BC059445190 | nocash      |
| DF211CCDD94A63E0BCB9E6AE427A249484A49D60 | stillnocash |
| 3DA541559918A808C2402BBA5012F6C60B27661C | foo         |
+------------------------------------------+-------------+
[7 Oct 2005 9:31] Hartmut Holzgraefe 
Not a bug, see 

http://bugs.mysql.com/bug.php?id=7008
http://bugs.mysql.com/bug.php?id=6257

SHA1() returns a binary string which has no collation and so no concept of upper/lowercase
[8 Oct 2005 3:15] Jeremy Barton 
If the desired behaviour is to leave it as lowercase, why did it work on all but one of the cases?  Before running update Users set Password=upper(sha1(substring(Password, 9))) where Password like 'P%'; every row except the first was 'PASSWORD<something>' and only one of them was lowercase after the update statement.

So either UPPER() not capitalizing the one case, or UPPER() capitalizing all of the other cases is wrong.
[10 Oct 2005 18:29] Sergei Golubchik 
sorry... (reopen was unintentional)