Bug #61378 NULLIF sometimes causes timestamp field text output to be truncated
Submitted: 1 Jun 2011 14:57 Modified: 12 Apr 2018 14:23
Reporter: Chris Sowden Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.5.12, 5.5.14 OS:Any (CentOS Linux 5.6, Mac OS X)
Assigned to: CPU Architecture:Any

[1 Jun 2011 14:57] Chris Sowden
Description:
Under certain circumstances using NULLIF on a timestamp field causes the text output to be truncated.

How to repeat:
(Apologies for the long example, but I couldn't easily find a simpler example that showed the problem.)

mysql  Ver 14.14 Distrib 5.5.12, for Linux (x86_64) using readline 5.1

mysql> CREATE TEMPORARY TABLE `t1` (
    ->   `order_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `user_id` int(10) unsigned DEFAULT NULL,
    ->   PRIMARY KEY (`order_id`),
    ->   KEY `user_id` (`user_id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `t1` (`order_id`, `user_id`) VALUES (4, 26065), (3, 26063);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE TEMPORARY TABLE `t2` (
    ->   `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    ->   PRIMARY KEY (`user_id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `t2` (`user_id`, `time`) VALUES (26063, '2011-05-17 10:58:53'), (26065, '2011-02-10 15:58:55');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE TEMPORARY TABLE `t3` (
    ->   `order_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ->   `user_id` int(10) unsigned DEFAULT NULL,
    ->   PRIMARY KEY (`order_id`),
    ->   KEY `user_id` (`user_id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `t3` (`order_id`, `time`, `user_id`) VALUES (1, '2009-10-29 16:16:46', 15628);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT
    -> NULLIF(`t2`.`time`, '0000-00-00') AS `time`,
    -> IF(`t2`.`time` = '0000-00-00', NULL, `t2`.`time`) AS `time`
    -> FROM  `t1`
    -> LEFT JOIN `t2` USING (`user_id`)
    -> LEFT JOIN `t3` USING (`user_id`)
    -> ORDER BY `t3`.`time` DESC;
+--------+---------------------+
| time   | time                |
+--------+---------------------+
| 2011-0 | 2011-05-17 10:58:53 |
| 2011-0 | 2011-02-10 15:58:55 |
+--------+---------------------+
2 rows in set (0.00 sec)

mysql> 

Suggested fix:
Workaround: use IF as shown in the example.
[1 Jun 2011 15:42] Valeriy Kravchuk
Thank you for the bug report. Verified with current mysql-5.1 from bzr on Mac OS X:

macbook-pro:5.5 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 4
Server version: 5.5.14-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> CREATE TEMPORARY TABLE `t1` (
    ->    `order_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->    `user_id` int(10) unsigned DEFAULT NULL,
    ->    PRIMARY KEY (`order_id`),
    ->    KEY `user_id` (`user_id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `t1` (`order_id`, `user_id`) VALUES (4, 26065), (3, 26063);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE TEMPORARY TABLE `t2` (
    ->    `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->    `time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    ->    PRIMARY KEY (`user_id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO `t2` (`user_id`, `time`) VALUES (26063, '2011-05-17 10:58:53'),
    -> (26065, '2011-02-10 15:58:55');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE TEMPORARY TABLE `t3` (
    ->    `order_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->    `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ->    `user_id` int(10) unsigned DEFAULT NULL,
    ->    PRIMARY KEY (`order_id`),
    ->    KEY `user_id` (`user_id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `t3` (`order_id`, `time`, `user_id`) VALUES (1, '2009-10-29 16:16:46',
    -> 15628);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT
    -> NULLIF(`t2`.`time`, '0000-00-00') AS `time`,
    -> IF(`t2`.`time` = '0000-00-00', NULL, `t2`.`time`) AS `time`
    -> FROM  `t1`
    -> LEFT JOIN `t2` USING (`user_id`)
    -> LEFT JOIN `t3` USING (`user_id`)
    -> ORDER BY `t3`.`time` DESC;
+--------+---------------------+
| time   | time                |
+--------+---------------------+
| 2011-0 | 2011-05-17 10:58:53 |
| 2011-0 | 2011-02-10 15:58:55 |
+--------+---------------------+
2 rows in set (0.02 sec)
[1 Jun 2011 15:46] MySQL Verification Team
I couldn't repeat on Windows:

C:\DBS>c:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.5.14-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql 5.5 >use d11
Database changed
mysql 5.5 >CREATE TEMPORARY TABLE `t1` (
    -> `order_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->  `user_id` int(10) unsigned DEFAULT NULL,
    ->  PRIMARY KEY (`order_id`),
    ->  KEY `user_id` (`user_id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql 5.5 >INSERT INTO `t1` (`order_id`, `user_id`) VALUES (4, 26065), (3, 26063);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.5 >CREATE TEMPORARY TABLE `t2` (
    ->   `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->    `time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    ->    PRIMARY KEY (`user_id`)
    ->  ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql 5.5 > INSERT INTO `t2` (`user_id`, `time`) VALUES (26063, '2011-05-17 10:58:53'),
    -> (26065, '2011-02-10 15:58:55');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.5 >
mysql 5.5 >CREATE TEMPORARY TABLE `t3` (
    ->    `order_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->    `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ->    `user_id` int(10) unsigned DEFAULT NULL,
    ->    PRIMARY KEY (`order_id`),
    ->    KEY `user_id` (`user_id`)
    ->  ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql 5.5 > INSERT INTO `t3` (`order_id`, `time`, `user_id`) VALUES (1, '2009-10-29 16:16:46',
    -> 15628);
Query OK, 1 row affected (0.00 sec)

mysql 5.5 > SELECT
    ->  NULLIF(`t2`.`time`, '0000-00-00') AS `time`,
    ->   IF(`t2`.`time` = '0000-00-00', NULL, `t2`.`time`) AS `time`
    ->  FROM  `t1`
    ->  LEFT JOIN `t2` USING (`user_id`)
    ->   LEFT JOIN `t3` USING (`user_id`)
    ->  ORDER BY `t3`.`time` DESC;
+---------------------+---------------------+
| time                | time                |
+---------------------+---------------------+
| 2011-05-17 10:58:53 | 2011-05-17 10:58:53 |
| 2011-02-10 15:58:55 | 2011-02-10 15:58:55 |
+---------------------+---------------------+
2 rows in set (0.00 sec)
[24 Nov 2017 9:09] Roy Lyseng
Seems fixed in 8.0
[12 Apr 2018 14:23] Paul DuBois
Posted by developer:
 
Fixed in 5.6.40.

NULLIF() with a TIMESTAMP argument could produce truncated output.