Bug #37575 UCASE fails on monthname and dayname
Submitted: 23 Jun 2008 5:37 Modified: 3 Feb 2009 1:15
Reporter: Jared S (Silver Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Charsets Severity:S1 (Critical)
Version:5.0.72, 5.1.30 GA OS:Any (Any)
Assigned to: Sergei Glukhov CPU Architecture:Any
Tags: dayname, monthname, qc, ucase, upper

[23 Jun 2008 5:37] Jared S
Description:
When UCASE/UPPER statement is used in conjunction with MONTHNAME the results are returned in CamelCase as if the function had no effect.

How to repeat:
select upper(monthname('20080606'));
select ucase(monthname('20080606'));

select upper(dayname('20080606'));
select ucase(dayname('20080606'));

Suggested fix:
Plz fix for dayname aswell ..ect
[23 Jun 2008 6:58] Valeriy Kravchuk
Thank you for a problem report. Verified just as described. The reason of the problem is that dayname and monthname returns string in binary collation:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 -T test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.1.25-rc-community MySQL Community Server (GPL)

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

mysql> select upper('Friday'), upper('June');
Field   1:  `upper('Friday')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     18
Max_length: 6
Decimals:   31
Flags:

Field   2:  `upper('June')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     12
Max_length: 4
Decimals:   31
Flags:

+-----------------+---------------+
| upper('Friday') | upper('June') |
+-----------------+---------------+
| FRIDAY          | JUNE          |
+-----------------+---------------+
1 row in set (0.00 sec)

mysql> select dayname('20080606');
Field   1:  `dayname('20080606')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     9
Max_length: 6
Decimals:   0
Flags:      BINARY

+---------------------+
| dayname('20080606') |
+---------------------+
| Friday              |
+---------------------+
1 row in set (0.00 sec)

I am not sure it is a bug. But as http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_dayname does not say explicitely what collation result will have, it is at least a documentation request.
[14 Nov 2008 11:53] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/58771

2714 Sergey Glukhov	2008-11-14
      Bug#37575 UCASE fails on monthname
      The MONTHNAME/DAYNAME functions
      returns binary string, so the LOWER/UPPER functions
      are not effective on the result of MONTHNAME/DAYNAME
      call.  
      Character set of the MONTHNAME/DAYNAME function
      result has been changed to connection character set.
[26 Nov 2008 12:48] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/59920

2714 Sergey Glukhov	2008-11-26
      Bug#37575 UCASE fails on monthname
      The MONTHNAME/DAYNAME functions
      returns binary string, so the LOWER/UPPER functions
      are not effective on the result of MONTHNAME/DAYNAME call.  
      Character set of the MONTHNAME/DAYNAME function
      result has been changed to connection character set.
[3 Dec 2008 14:10] Alexander Barkov
Hi Sergey,

The patch looks ok to push.

http://lists.mysql.com/commits/59920

I've found only one problem:

Please modify DERIVATION_EXPLICIT to DERIVATION_COERCIBLE here:

collation.set(cs, DERIVATION_EXPLICIT, repertoire);

(two instances)

Also, please add:

SELECT coercibility(monthname('some-date')), coercibility(dayname('some-date'));

into some test to cover this issue.

Thanks!
[4 Dec 2008 9:19] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/60576

2714 Sergey Glukhov	2008-12-04
      Bug#37575 UCASE fails on monthname
      The MONTHNAME/DAYNAME functions
      returns binary string, so the LOWER/UPPER functions
      are not effective on the result of MONTHNAME/DAYNAME call.  
      Character set of the MONTHNAME/DAYNAME function
      result has been changed to connection character set.
[4 Dec 2008 9:23] Alexander Barkov
http://lists.mysql.com/commits/60576 is ok to push.
[4 Dec 2008 11:45] Alexander Barkov
Bug#14368 "UPPER and LOWER functions don't work with DAYNAME"
was marked as duplicate for this bug.
[16 Dec 2008 10:05] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/61739

2746 Sergey Glukhov	2008-12-16
      Bug#37575 UCASE fails on monthname
      The MONTHNAME/DAYNAME functions
      returns binary string, so the LOWER/UPPER functions
      are not effective on the result of MONTHNAME/DAYNAME call.  
      Character set of the MONTHNAME/DAYNAME function
      result has been changed to connection character set.
[16 Dec 2008 21:54] Jared S
Synced bug version.
[23 Dec 2008 14:08] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/62259

2719 Sergey Glukhov	2008-12-23
      Bug#37575 UCASE fails on monthname
      The MONTHNAME/DAYNAME functions
      returns binary string, so the LOWER/UPPER functions
      are not effective on the result of MONTHNAME/DAYNAME call.  
      Character set of the MONTHNAME/DAYNAME function
      result has been changed to connection character set.
[6 Jan 2009 13:56] Bugs System
Pushed into 5.0.76 (revid:joro@sun.com-20090105160414-8q9j4bi1klkfwiup) (version source revid:azundris@mysql.com-20081230114734-nmsc37ak330zlygn) (merge vers: 5.0.76) (pib:6)
[6 Jan 2009 21:18] Jared S
Its going into 5.1.X GA tree right?
[9 Jan 2009 1:01] Paul DuBois
Noted in 5.0.76 changelog.

The MONTHNAME() and DAYNAME() functions returned a binary string, so
that using LOWER() or UPPER() had no effect. Now MONTHNAME() and
DAYNAME() return a value in character_set_connection character set.

Setting report to NDI pending push into 5.1.x/6.0.x.
[15 Jan 2009 6:35] Bugs System
Pushed into 5.1.31 (revid:joro@sun.com-20090115053147-tx1oapthnzgvs1ro) (version source revid:azundris@mysql.com-20081230114838-cn52tu180wcrvh0h) (merge vers: 5.1.31) (pib:6)
[15 Jan 2009 16:26] Paul DuBois
Noted in 5.1.31 changelog.

Setting report to NDI pending push into 6.0.x.
[19 Jan 2009 11:30] Bugs System
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090115073240-1wanl85vlvw2she1) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:08] Bugs System
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 14:43] Jon Stephens
Setting status back to NDI pending merge to 6.0 tree.
[19 Jan 2009 16:14] Bugs System
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)
[20 Jan 2009 19:00] Bugs System
Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:azundris@mysql.com-20081230114916-c290n83z25wkt6e4) (merge vers: 6.0.9-alpha) (pib:6)
[28 Jan 2009 22:00] Paul DuBois
Noted in 6.0.10 changelog.
[1 Feb 2009 21:38] MySQL Verification Team
Bug: http://bugs.mysql.com/bug.php?id=42489 marked as duplicate of this one.
[3 Feb 2009 1:15] Jared S
WORKAROUND : SELECT UPPER(DATE_FORMAT('20080606', '%M'));
[27 Feb 2009 5:31] Alexander Barkov
Bug#29128 has been marked as duplicate for this one.
[5 Mar 2009 20:22] Sveta Smirnova
Bug #43423 was marked as duplicate of this one.
[6 Mar 2009 7:24] Sveta Smirnova
Bug #43403 was marked as duplicate of this one.
[5 Jan 2010 19:13] Jose Luis Martinez
DATE_FORMAT is also affected by this bug

mysql> SELECT UCASE(DATE_FORMAT('2009-01-02', '%a %b %D %M %W')), LCASE(DATE_FORMAT('2009-01-02', '%a %b %D %M %W'));
+----------------------------------------------------+----------------------------------------------------+
| UCASE(DATE_FORMAT('2009-01-02', '%a %b %D %M %W')) | LCASE(DATE_FORMAT('2009-01-02', '%a %b %D %M %W')) |
+----------------------------------------------------+----------------------------------------------------+
| Fri Jan 2nd January Friday                         | Fri Jan 2nd January Friday                         |
+----------------------------------------------------+----------------------------------------------------+
1 row in set (0.00 sec)
[10 Jan 2010 12:09] Sveta Smirnova
Jose,

which version do you use?
[10 Jan 2010 13:25] Jose Luis Martinez
Hi,

Here are the version details:

| version                 | 5.0.32-Debian_7etch10-log |
| version_comment         | Debian etch distribution  |
| version_compile_machine | i486                      |
| version_compile_os      | pc-linux-gnu              |

I originally detected the issue when applying UCASE to DATE_FORMAT. Went to file a new bug, and found in the bug database that UCASE was failing on other date functions, and that appling UCASE to DATE_FORMAT was proposed as a workaround for this bug (that didn't work for me).

mysql> SELECT UCASE(DATE_FORMAT('2009-01-02', '%a %b %D %M %W')) as ucase, LCASE(DATE_FORMAT('2009-01-02', '%a %b %D %M %W')) as lcase;
+----------------------------+----------------------------+
| ucase                      | lcase                      |
+----------------------------+----------------------------+
| Fri Jan 2nd January Friday | Fri Jan 2nd January Friday |
+----------------------------+----------------------------+

I found a workaround, seeing that the problem was that MySQL is returning a string in binary collation:

mysql> SELECT UCASE(CAST(DATE_FORMAT('2009-01-03', '%a %b %D %M %W') AS CHAR)) as ucase, LCASE(CAST(DATE_FORMAT('2009-01-03', '%a %b %D %M %W') AS CHAR)) as lcase;
+------------------------------+------------------------------+
| ucase                        | lcase                        |
+------------------------------+------------------------------+
| SAT JAN 3RD JANUARY SATURDAY | sat jan 3rd january saturday |
+------------------------------+------------------------------+
[10 Jan 2010 13:39] Sveta Smirnova
Please upgrade to version 5.0.76: this bug has been fixed.