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