Bug #31933 date() function can't handle null value correctly.

• Submitted: 30 Oct 2007 6:38
• Modified: 30 Oct 2007 8:02
• Reporter: Sadao Hiratsuka (Basic Quality Contributor)
• Status: Duplicate
• Category: MySQL Server: General
• Severity: S2 (Serious)
• Version: 5.0.45, 5.0.46
• OS: Any
• CPU Architecture: Any
• Tags: compare, Contribution, date, null, qc

[30 Oct 2007 6:38] Sadao Hiratsuka

Description:
When datetime values are evaluated through date() function,
if some datetime value is null,
the other values following null value are regarded as null, too.

How to repeat:
mysql> create table test (c1 datetime);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values (null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test where date(c1) = '2007-01-01';
Empty set (0.00 sec)

mysql> insert into test values ('2007-01-01 10:00:00');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test where date(c1) = '2007-01-01';
Empty set (0.00 sec) ***** HERE *****

mysql> select * from test;
+---------------------+
| c1                  |
+---------------------+
| NULL                |
| 2007-01-01 10:00:00 |
+---------------------+
2 rows in set (0.00 sec)

Suggested fix:
Here is a sample patch.

*** item_timefunc.cc_org        2007-10-30 15:31:47.000000000 +0900
--- item_timefunc.cc    2007-10-30 15:32:13.000000000 +0900
***************
*** 2667,2672 ****
--- 2667,2673 ----
      null_value= 1;
      return 0;
    }
+   null_value = 0;
    return (longlong) (ltime.year * 10000L + ltime.month * 100 + ltime.day);
  }

It seems time() function doesn't have such problem.
So I think the same coding is more better.

[30 Oct 2007 8:02] Sveta Smirnova

Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Duplicate of bug #31709