Bug #14368 UPPER and LOWER functions don't work with DAYNAME

• Submitted: 27 Oct 2005 1:35
• Modified: 4 Dec 2008 11:45
• Reporter: Giorgio Frondoni
• Status: Duplicate
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 5.0.15
• OS: Windows (Windows XP)
• Assigned to: Assigned Account
• CPU Architecture: Any

[27 Oct 2005 1:35] Giorgio Frondoni

Description:
The UPPER and LOWER functions appears not to work in conjuction with DAYNAME function. 

How to repeat:
mysql> select UPPER(DAYNAME(NOW()));
+--------------------------+
| UPPER(DAYNAME(NOW())) |
+--------------------------+
| Wednesday                     |
+--------------------------+
1 row in set (0.00 sec)

mysql> select LOWER(DAYNAME(NOW()));
+--------------------------+
| LOWER(DAYNAME(NOW()))|
+--------------------------+
| Wednesday                     |
+--------------------------+
1 row in set (0.01 sec)

mysql> \s
--------------
mysql  Ver 14.12 Distrib 5.0.15, for Win32 (ia32)
Connection id:          1
Current database:       fmms
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.0.15-nt-log
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:               3306
Uptime:                 1 hour 7 min 33 sec

Threads: 3  Questions: 145  Slow queries: 0  Opens: 5  Flush tables: 1  Open tab
les: 0  Queries per second avg: 0.036
--------------

[27 Oct 2005 2:08] Paul DuBois

Looks like DAYNAME() returns a binary string:

mysql> select charset(dayname(now()));
+-------------------------+
| charset(dayname(now())) |
+-------------------------+
| binary                  |
+-------------------------+

A workaround would be to convert the result to a non-binary
string with CONVERT().  For example:

mysql> select charset(convert(dayname(now()) using latin1));
+-----------------------------------------------+
| charset(convert(dayname(now()) using latin1)) |
+-----------------------------------------------+
| latin1                                        |
+-----------------------------------------------+

UPPER() and LOWER() will case-convert non-binary strings:

mysql> select upper(convert(dayname(now()) using latin1));
+---------------------------------------------+
| upper(convert(dayname(now()) using latin1)) |
+---------------------------------------------+
| WEDNESDAY                                   |
+---------------------------------------------+

[27 Oct 2005 2:19] Jorge del Conde

Thanks for your bug report.  I was able to reproduce this in 5.0.15 and 5.0.16bk:

C:\my>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.15-log

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

mysql> select UPPER(DAYNAME(NOW()));
+-----------------------+
| UPPER(DAYNAME(NOW())) |
+-----------------------+
| Wednesday             |
+-----------------------+
1 row in set (0.00 sec)

mysql> select LOWER(DAYNAME(NOW()));
+-----------------------+
| LOWER(DAYNAME(NOW())) |
+-----------------------+
| Wednesday             |
+-----------------------+
1 row in set (0.00 sec)

This bug happens in our linux distribution too.

[4 Nov 2005 18:02] Giorgio Frondoni

I have applied successfully the proposed work around and moved on with the project.

[19 Mar 2006 18:36] MySQL Verification Team

Bug: http://bugs.mysql.com/bug.php?id=18341 was marked as
duplicate of this one.

[20 Nov 2007 2:41] Mephie Stopheles

on my Mysql vers 5.0.22-log 
Running on centos.

my workaround:

delimiter ||
CREATE FUNCTION mef_upper(x longtext) returns longtext
LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA
begin
set x = upper(convert(x using latin1));
return x;
END;
||

delimiter ;

--Meph

[29 Sep 2008 22:23] Konstantin Osipov

What is the reason this is still not fixed?

[4 Dec 2008 11:45] Alexander Barkov

Duplicate for:
Bug#37575 UCASE fails on monthname and dayname