Bug #33772 Monthname (string) comparison with Month (integer) succeeds where it shouldn't
Submitted: 9 Jan 2008 14:58 Modified: 10 Jan 2008 12:25
Reporter: Vares Pierre-Olivier Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.0-bk, 5.1-bk OS:Any
Assigned to: Evgeny Potemkin CPU Architecture:Any
Tags: comparison, month, monthname
Triage: Triaged: D3 (Medium) / R3 (Medium) / E3 (Medium)

[9 Jan 2008 14:58] Vares Pierre-Olivier
Description:
Just discovered something that could be interesting to know...

When comparing, in a where clause, the result of Monthname with the result of Month, an implicit conversion is carried out. (see "How to repeat" code)
So we got a SELECT with a where clause asking two fields being equal... and although they're not the row is selected.

Okay, it's somehow stupid to compare MONTHNAME with MONTH  (that was actually a mistake), but I can't imagine how mysql can achieve this comparison. It may hide related bugs.

How to repeat:
SELECT MONTHNAME( now( ) ) , MONTH( now( ) )
FROM
  (SELECT 'X'dummy) dummytable
WHERE
  MONTHNAME( now( ) ) = MONTH( now( ) ) 

results in :

MONTHNAME(now()) 	MONTH(now())
----------------        ------------
January 	        1

where it should return "no records".
[9 Jan 2008 15:16] Paul Dubois
The comparison seems to succeed because MONTHNAME(NOW()) becomes 1 (not 0) when converted to numeric form.

mysql> SELECT NOW(), MONTHNAME(NOW()), 'January';
+---------------------+------------------+---------+
| NOW()               | MONTHNAME(NOW()) | January |
+---------------------+------------------+---------+
| 2008-01-09 09:14:11 | January          | January | 
+---------------------+------------------+---------+
1 row in set (0.00 sec)

mysql> SELECT MONTHNAME(NOW())+0, 'January'+0;
+--------------------+-------------+
| MONTHNAME(NOW())+0 | 'January'+0 |
+--------------------+-------------+
|                  1 |           0 | 
+--------------------+-------------+
1 row in set, 1 warning (0.15 sec)

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'January' | 
+---------+------+---------------------------------------------+
1 row in set (0.47 sec)

I would have expected MONTHNAME(NOW())+0 to be 0, not 1...
[9 Jan 2008 15:41] Vares Pierre-Olivier
The same arises with December :
SELECT
  MONTHNAME('2007-12-25'),
  MONTH('2007-12-25')
FROM (SELECT 'x' dummy) dummytable
WHERE MONTHNAME('2007-12-25') = MONTH('2007-12-25')

+-------------------------+---------------------+
| MONTHNAME('2007-12-25') | MONTH('2007-12-25') |
+-------------------------+---------------------+
| December                |                  12 |
+-------------------------+---------------------+

A "SHOW WARNINGS" result in an "empty set" in both case (with now() and with '2007-12-25')

The implicit conversion I was talking about is clearly put in evidence here :
SELECT
  MONTHNAME('2007-12-25')+0,
  MONTH('2007-12-25')+0
FROM (SELECT 'x' dummy) dummytable
WHERE MONTHNAME('2007-12-25') = MONTH('2007-12-25')

+---------------------------+-----------------------+
| MONTHNAME('2007-12-25')+0 | MONTH('2007-12-25')+0 |
+---------------------------+-----------------------+
|                        12 |                    12 |
+---------------------------+-----------------------+
[10 Jan 2008 12:25] Susanne Ebrecht
Verified as described with bk trees from 5.0 and 5.1.