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 Email Updates:
Status: Duplicate Impact on me:
None 
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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.042 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.