MySQL Bugs: #24494: dayname() function ignores my collation, uses binary or some Case Sensitive

Bug #24494	dayname() function ignores my collation, uses binary or some Case Sensitive
Submitted:	22 Nov 2006 0:08	Modified:	30 Nov 2006 19:44
Reporter:	Tom Hanlon	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.27	OS:
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
When using the dayname() function to compare a dayname to a string the comparison test is case sensitive regardless of my collation. I have tested on Windows with 5.0.27 and MacOSX 5.0.22. Same issue. 

How to repeat:
 mysql> select "F"="f";
> +---------+
> | "F"="f" |
> +---------+
> |       1 |
> +---------+
> 1 row in set (0.00 sec)
> 
> 
> mysql> select dayname(now())="Thursday";
> +---------------------------+
> | dayname(now())="Thursday" |
> +---------------------------+
> |                         1 |
> +---------------------------+
> 1 row in set (0.00 sec)
> 
> mysql> select dayname(now())="thursday";
> +---------------------------+
> | dayname(now())="thursday" |
> +---------------------------+
> |                         0 |
> +---------------------------+
> 1 row in set (0.00 sec)
> 
> 
> mysql> select dayname(now())="thursday" collate latin1_general_ci;
> +-----------------------------------------------------+
> | dayname(now())="thursday" collate latin1_general_ci |
> +-----------------------------------------------------+
> |                                                   1 |
> +-----------------------------------------------------+
 1 row in set (0.00 sec)
 
 

Suggested fix:
Have the dayname() function honor my collation.

Of course this is only a bug in the sense that it does not perform as  the documentation states..

http://dev.mysql.com/doc/refman/5.0/en/charset-result.html
"For operations that convert to character data, the character set and collation of the strings that result from the operations are defined by the character_set_connection and collation_connection system variables. This applies to CAST(), CONV(), FORMAT(), HEX(), SPACE(). Before MySQL 5.0.15, it also applies to CHAR()."

It may be simpler to clarify/edit the docs..

Thank you for the report.

But what is unclear in the documentation?

We internally discussed to be clearly to rewrite sentence "This applies to CAST(), ..." to "This applies *only* to CAST(), ..."

It is still not clear for you, please, leave a comment.

The documentation is clear , and it states that dayname() uses collation_connection when it seems to use binary. I suggested that placing dayname() in the list of functionsin the documentation,  that force a binary collation would be just as reasonable of a solution, in my opinion , as changing the behavior to honor collation_connection.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Added "only".

Will also add a note that for any string function, you can
determine the charset or collation of the result with the
CHARSET() or COLLATION() function.