Bug #60324 COALESCE makes a partial date not match a full date
Submitted: 3 Mar 2011 21:01 Modified: 4 Mar 2011 4:20
Reporter: Max Kanat-Alexander Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.1.55, 5.1.57 OS:Any
Assigned to: CPU Architecture:Any
Tags: COALESCE, SQL
Triage: Needs Triage: D2 (Serious)

[3 Mar 2011 21:01] Max Kanat-Alexander
Description:
In the WHERE clause, with a DATETIME field:

COALESCE(a_date, '1970-01-01 00:00:00') = '1970-01-01' does not work.
a_date = '1970-01-01' does work.

How to repeat:
CREATE TABLE testing (a_date DATETIME);
INSERT INTO testing ('1970-01-01');
INSERT INTO testing VALUES ();

SELECT * FROM testing WHERE COALESCE(a_date, '1970-01-01 00:00:00') = '1970-01-01';
Empty set (0.00 sec)

SELECT * FROM testing WHERE a_date = '1970-01-01';
+---------------------+
| a_date              |
+---------------------+
| 1970-01-01 00:00:00 |
+---------------------+
[4 Mar 2011 4:20] Valeriy Kravchuk
Thank you for the bug report. Verified on Mac OS X:

macbook-pro:5.1 openxs$ bin/mysql -uroot test 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.57-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE testing (a_date DATETIME);
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO testing VALUES ();
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO testing VALUES ('1970-01-01');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM testing WHERE COALESCE(a_date, '1970-01-01 00:00:00') = '1970-01-01';
Empty set (0.00 sec)

mysql> explain SELECT * FROM testing WHERE COALESCE(a_date, '1970-01-01 00:00:00') = '1970-01-01';
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | testing | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM testing WHERE a_date = '1970-01-01';
+---------------------+
| a_date              |
+---------------------+
| 1970-01-01 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from testing;
+---------------------+
| a_date              |
+---------------------+
| NULL                |
| 1970-01-01 00:00:00 |
+---------------------+
2 rows in set (0.00 sec)

mysql> show variables like 'char%';
+--------------------------+---------------------------------------------+
| Variable_name            | Value                                       |
+--------------------------+---------------------------------------------+
| character_set_client     | latin1                                      |
| character_set_connection | latin1                                      |
| character_set_database   | latin1                                      |
| character_set_filesystem | binary                                      |
| character_set_results    | latin1                                      |
| character_set_server     | latin1                                      |
| character_set_system     | utf8                                        |
| character_sets_dir       | /Users/openxs/dbs/5.1/share/mysql/charsets/ |
+--------------------------+---------------------------------------------+
8 rows in set (0.00 sec)