MySQL Bugs: #33772: Monthname (string) comparison with Month (integer) succeeds where it shouldn't

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:	Assigned Account	CPU Architecture:	Any
Tags:	comparison, month, monthname

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

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

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 |
+---------------------------+-----------------------+

Verified as described with bk trees from 5.0 and 5.1.