Bug #31795 lower function gives uppercase result with concat

• Submitted: 23 Oct 2007 18:05
• Modified: 23 Oct 2007 18:22
• Reporter: Michael Del Monte
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.37
• OS: Windows (XP)
• CPU Architecture: Any
• Tags: lower concat

[23 Oct 2007 18:05] Michael Del Monte

Description:
lower(concat(x,y)) gives uppercase result

How to repeat:
select lower(concat(123,'HELLO')) returns 123HELLO

Order of arguments is irrelevant.

select lower(concat('HELLO',123)) returns HELLO123

Suggested fix:
This bug is probably related to #28759, a similar problem using lower with a date function.

Solution:  Fix lower, upper, and other similar functions to coerce the input arguments to string types.

[23 Oct 2007 18:15] Paul DuBois

CONCAT() returns a binary string if any of its arguments are binary strings. Binary strings don't have a concept of lettercase, so UPPER() and LOWER() do nothing.

The argument of 123 produces a binary string when converted to string form, so the CONCAT() call returns a binary string.

To get lettercase conversion to work, do this:

mysql> select lower(concat('HELLO','123'));
+------------------------------+
| lower(concat('HELLO','123')) |
+------------------------------+
| hello123                     | 
+------------------------------+

Or this:

mysql> select lower(convert(concat('HELLO',123) using latin1));
+--------------------------------------------------+
| lower(convert(concat('HELLO',123) using latin1)) |
+--------------------------------------------------+
| hello123                                         | 
+--------------------------------------------------+

[23 Oct 2007 18:22] MySQL Verification Team

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

c:\dev\5.0>bin\mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.52-nt-log Source distribution

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

mysql> select charset(concat(123,'HELLO'));
+------------------------------+
| charset(concat(123,'HELLO')) |
+------------------------------+
| binary                       |
+------------------------------+
1 row in set (0.00 sec)

mysql>