| 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: | |
| 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 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.
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