MySQL Bugs: #37575: UCASE fails on monthname and dayname

Bug #37575	UCASE fails on monthname and dayname
Submitted:	23 Jun 2008 7:37	Modified:	3 Feb 2:15
Reporter:	Jared Sullivan (Silver Quality Contributor)
Status:	Closed
Category:	Server: Charsets	Severity:	S1 (Critical)
Version:	5.0.72, 5.1.30 GA	OS:	Any (Any)
Assigned to:	Sergey Gluhov	Target Version:	5.0+
Tags:	dayname, monthname, upper, ucase, qc
Triage:	Triaged: D2 (Serious)

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

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.

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.

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.

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!

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.

http://lists.mysql.com/commits/60576 is ok to push.

Bug#14368 "UPPER and LOWER functions don't work with DAYNAME"
was marked as duplicate for this bug.

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.

Synced bug version.

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.

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)

Its going into 5.1.X GA tree right?

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.

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)

Noted in 5.1.31 changelog.

Setting report to NDI pending push into 6.0.x.

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)

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)

Setting status back to NDI pending merge to 6.0 tree.

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)

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)

Noted in 6.0.10 changelog.

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

WORKAROUND : SELECT UPPER(DATE_FORMAT('20080606', '%M'));

Bug#29128 has been marked as duplicate for this one.

Bug #43423 was marked as duplicate of this one.

Bug #43403 was marked as duplicate of this one.