MySQL Bugs: #24297: LOWER() fails when used together with CHAR()

Bug #24297	LOWER() fails when used together with CHAR()
Submitted:	14 Nov 2006 13:48	Modified:	16 Nov 2006 14:35
Reporter:	Paul Dodd	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.30-BK, 5.0.27-community-nt	OS:	Linux (Linux, windows 2000)
Assigned to:		CPU Architecture:	Any
Tags:	char, LOWER

Description:
When the function LOWER() is used together with CHAR() the result is uppercase instead of lowercase. On linux with mysql 4.1.21-standard works find. On windows does not work. For example on windows:

SELECT LOWER("A") -> a # OK
SELECT REVERSE(LOWER("AB")) -> ba # OK
SELECT CHAR(ORD('A')) -> A # OK
SELECT CHAR(ORD('A')) -> A # OK

SELECT LOWER(CHAR(ORD('A'))) -> A # should be "a"
SELECT CHAR(ORD('A')) -> A # OK
SELECT LOWER(CHAR(77)) -> M # should be "m"
LOWER(QUOTE(CHAR(77))) -> 'M' # should be 'm'

How to repeat:
SELECT LOWER(CHAR(ORD('A')));

The result should be 'a' but it is 'A'.

Suggested fix:
Instead of using
SELECT LOWER(CHAR(column + ORD('A'))) FROM table WHERE ...
use
SELECT CHAR(column + ORD('a')) FROM table WHERE ...

better synopsis

Bug squad, please verify on 4.1.
I have the following on 5.0 and 5.1
Your MySQL connection id is 1 to server version: 5.0.29-valgrind-max-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT LOWER(CHAR(ORD('A')));
+-----------------------+
| LOWER(CHAR(ORD('A'))) |
+-----------------------+
| A                     |
+-----------------------+
1 row in set (0.03 sec)

Your MySQL connection id is 1 to server version: 5.1.13-beta-valgrind-max-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT LOWER(CHAR(ORD('A')));
+-----------------------+
| LOWER(CHAR(ORD('A'))) |
+-----------------------+
| A                     |
+-----------------------+
1 row in set (0.08 sec)

Thank you for a bug report. Verified just as described with 5.0.30-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.30-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT LOWER(CHAR(ORD('A')));
+-----------------------+
| LOWER(CHAR(ORD('A'))) |
+-----------------------+
| A                     |
+-----------------------+
1 row in set (0.02 sec)

mysql> SELECT LOWER(CHAR(ORD('a')));
+-----------------------+
| LOWER(CHAR(ORD('a'))) |
+-----------------------+
| a                     |
+-----------------------+
1 row in set (0.00 sec)

mysql> show variables like 'char%';
+--------------------------+--------------------------------------------+
| Variable_name            | Value                                      |
+--------------------------+--------------------------------------------+
| character_set_client     | latin1                                     |
| character_set_connection | latin1                                     |
| character_set_database   | latin1                                     |
| character_set_filesystem | binary                                     |
| character_set_results    | latin1                                     |
| character_set_server     | latin1                                     |
| character_set_system     | utf8                                       |
| character_sets_dir       | /home/openxs/dbs/5.0/share/mysql/charsets/ |
+--------------------------+--------------------------------------------+
8 rows in set (0.00 sec)

mysql> SELECT LOWER('A');
+------------+
| LOWER('A') |
+------------+
| a          |
+------------+
1 row in set (0.00 sec)

mysql> SELECT CHAR(ORD('A'));
+----------------+
| CHAR(ORD('A')) |
+----------------+
| A              |
+----------------+
1 row in set (0.00 sec)

4.1 is not affected:
mysql> SELECT LOWER(CHAR(ORD('a'))); 
+-----------------------+
| LOWER(CHAR(ORD('a'))) |
+-----------------------+
| a                     |
+-----------------------+

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

This is expected behavior. Try SELECT CHARSET(CHAR(ORD('A')))
CHAR() function returns a string in a binary character set. Characters in this charset are byte values, they are not letters, and the concept of "letter case" is not applicable to them.