MySQL Bugs: #23938: ISNULL on DATE AND CAST AS DATE returns false for null values

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

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.

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

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.

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.

Available in 4.1.23, 5.0.36, and 5.1.16-beta.

Noted in 4.1.23, 5.0.36, 5.1.16 changelogs.

ISNULL(DATE(NULL)) and ISNULL(CAST(NULL AS DATE)) erroneously
returned false.

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