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: | |
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
[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.