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

Submitted: 9 Jan 2008 14:58 Modified: 24 Mar 20:50
Reporter: Vares Pierre-Olivier Email Updates:
Status: Can't repeat 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

[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.
[24 Mar 20:50] Roy Lyseng
Posted by developer:
 
Not reproducible in release 9.7.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.041 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.