Bug #23938 ISNULL on DATE AND CAST AS DATE returns false for null values
Submitted: 3 Nov 2006 12:53 Modified: 30 Jan 2008 10:25
Reporter: Karl Nyberg Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:4.1, 5.0-BK, 5.1-BK OS:Linux (Linux, RHEL4 AS)
Assigned to: Ramil Kalimullin CPU Architecture:Any
Triage: D2 (Serious)

[3 Nov 2006 12:53] Karl Nyberg
Description:
I have date and datetime columns that contains null values. 
Running DATE(col) and CAST(col AS DATE) returns null for the null valued rows. 
ISNULL(DATE(col)) and ISNULL(CAST(col AS date)) are always 0 which means that the null valued rows stop being null when casted.

This is a major problem in nested views where one have done formatting on the first view and then builds a WHERE clause in the second view using the first.

How to repeat:

CREATE TABLE `table1` (
  `pk1` int(11) unsigned NOT NULL auto_increment,
  `double1` double default '0',
  `date1` date default NULL,
  PRIMARY KEY  (`pk1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `table1` VALUES ('1', '1.3', null);
INSERT INTO `table1` VALUES ('2', null, '2006-11-22');
INSERT INTO `table1` VALUES ('3', '1.4', '2006-11-14');

SELECT 
date1, 
CAST(date1 AS DATE), 
ISNULL(CAST(date1 AS DATE)), 
double1, 
CAST(double1 AS decimal), 
ISNULL(CAST(double1 AS decimal)) 
FROM table1;

Suggested fix:
I'd like ISNULL to work for CASTED dates. This worked some releases ago but might have been changed in the 5.0.26 release although I upgraded from 5.0.22.
[3 Nov 2006 13:21] Karl Nyberg
An easier way to repeat the bug is:

SELECT ISNULL(DATE(null)); # 0
SELECT ISNULL(TIME(null)); # 1
SELECT ISNULL(CAST(null AS DATE)); # 0
SELECT ISNULL(CAST(null AS TIME)); #1
[5 Nov 2006 12:58] Valeriy Kravchuk
Thank you for a bug report. Verified just as described in the last comment with 5.0.29-BK on Linux:

openxs@suse:~/dbs/5.0> 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 1
Server version: 5.0.29-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT ISNULL(DATE(null));
+--------------------+
| ISNULL(DATE(null)) |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT ISNULL(TIME(null));
+--------------------+
| ISNULL(TIME(null)) |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.01 sec)

mysql> SELECT ISNULL(CAST(null AS DATE));
+----------------------------+
| ISNULL(CAST(null AS DATE)) |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT ISNULL(CAST(null AS TIME));
+----------------------------+
| ISNULL(CAST(null AS TIME)) |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

mysql> show variables like 'sql%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| sql_big_selects | ON    |
| sql_mode        |       |
| sql_notes       | ON    |
| sql_warnings    | OFF   |
+-----------------+-------+
4 rows in set (0.01 sec)

NULL is NULL, whatever type it had casted to.
[4 Dec 2006 13:11] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/16379

ChangeSet@1.2555, 2006-12-04 17:06:25+04:00, ramil@mysql.com +3 -0
  fix for bug #23938: ISNULL on DATE AND CAST AS DATE returns false for null values
  
  Set null_value in case of wrong data.
[14 Feb 2007 14:59] Chad MILLER
Available in 4.1.23, 5.0.36, and 5.1.16-beta.
[26 Feb 2007 20:30] Paul Dubois
Noted in 4.1.23, 5.0.36, 5.1.16 changelogs.

ISNULL(DATE(NULL)) and ISNULL(CAST(NULL AS DATE)) erroneously
returned false.
[31 Jan 2008 1:55] Timothy Smith
Gary,

This bug was fixed in 5.0.36, and the regression test added to the test suite to confirm that:

select isnull(date(NULL)), isnull(cast(NULL as DATE));

returns true for both.

I think probably you're wanting to investigate bug #29898, "DATE() function returns inconsistent results", which was fixed in 5.0.48.

Regards,

Timothy