Bug #36744 LOWER / LCASE() not working when concatenating different data types

• Submitted: 15 May 2008 20:49
• Modified: 15 May 2008 21:16
• Reporter: Kouly Delipoglou
• Status: Not a Bug
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 5.0.50sp1a-enterprise
• OS: Linux (Red Hat Enterprise Release 4)
• CPU Architecture: Any
• Tags: concat, lcase, LOWER

[15 May 2008 20:49] Kouly Delipoglou

Description:
lower(X) and lcase(X) functions are not returning lower-cased letters when an integer and string are concatenated and THEN lower-cased.

How to repeat:
[(none)]> select lcase(concat(123,'ABC'));
+--------------------------+
| lcase(concat(123,'ABC')) |
+--------------------------+
| 123ABC                   |
+--------------------------+
1 row in set (0.00 sec)

[(none)]> select lower(concat(123,'ABC'));
+--------------------------+
| lower(concat(123,'ABC')) |
+--------------------------+
| 123ABC                   |
+--------------------------+
1 row in set (0.00 sec)

It appears the result is a string since it's left justified but 'ABC' are never lower-cased as I would expect.

[15 May 2008 20:50] Kouly Delipoglou

I apologize if I have mis-categorized this bug as I simply could NOT tell where this should have been placed.

[15 May 2008 21:16] MySQL Verification Team

Thank you for the bug report. Please see the Manual about concat returns a binary result:

c:\dbs>5.0\bin\mysql -uroot -T
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.62-nt Source distribution

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

mysql> select lcase(concat(123,'ABC'));
Field   1:  `lcase(concat(123,'ABC'))`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     6
Max_length: 6
Decimals:   31
Flags:      NOT_NULL BINARY

+--------------------------+
| lcase(concat(123,'ABC')) |
+--------------------------+
| 123ABC                   |
+--------------------------+
1 row in set (0.03 sec)

mysql>

Thanks in advance.