| Bug #82325 | DateTime values rounded in set/insert clause but not in where clause | ||
|---|---|---|---|
| Submitted: | 22 Jul 2016 18:08 | Modified: | 25 Jul 2016 14:38 |
| Reporter: | Ryan Foley | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.7.9 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | datetime, Rounding | ||
[25 Jul 2016 14:38]
MySQL Verification Team
Thank you for the bug report. I could say this is duplicate of: https://bugs.mysql.com/bug.php?id=68760 C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > " Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.33 Source distribution 2016-JUL-09 Copyright (c) 2000, 2016, 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.6 > use test Database changed mysql 5.6 > CREATE TABLE `testing` ( -> `colDate` datetime DEFAULT NULL -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.34 sec) mysql 5.6 > insert into testing values ('2016-07-22 12:49:07.5'); Query OK, 1 row affected (0.15 sec) mysql 5.6 > select * from testing where coldate = '2016-07-22 12:49:07.5'; Empty set (0.10 sec) mysql 5.6 > exit Bye C:\dbs>net start mysqld55 The MySQLD55 service is starting.. The MySQLD55 service was started successfully. C:\dbs>55 C:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > " Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.52 Source distribution 2016-JUL-09 Copyright (c) 2000, 2016, 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 test Database changed mysql 5.5 > CREATE TABLE `testing` ( -> `colDate` datetime DEFAULT NULL -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.20 sec) mysql 5.5 > insert into testing values ('2016-07-22 12:49:07.5'); Query OK, 1 row affected (0.07 sec) mysql 5.5 > select * from testing where coldate = '2016-07-22 12:49:07.5'; +---------------------+ | colDate | +---------------------+ | 2016-07-22 12:49:07 | +---------------------+ 1 row in set (0.03 sec)
[25 Jul 2016 18:50]
Roy Lyseng
SQL standard does not require a warning when assigning a value with higher precision to a target with lower precision. Thus, assigning a datetime(1) value like '2016-07-22 12:49:07.5' to a datetime(0) column will cause automatic rounding, and the inserted value should be '2016-07-22 12:49:08', with no warning issued. However, when doing the comparison, both values are converted to datetime(1), so the comparison is done as '2016-07-22 12:49:07.5' = '2016-07-22 12:49:08.0', which is obviously false. I would say this is not a bug. We might consider adding a warning for the truncation as part of the assignment, but that would be a feature request. Note also that the problem is independent of whether rounding or truncation is used.
Description: A table with a DateTime column with no decimals has different behavior for set/insert clauses vs where clauses. The set/insert clauses get rounded while the where clauses do not (no warnings). Some may say that this also applies to decimal, float, etc columns, but columns of these datatypes are typically truncated and a warning is returned, as opposed to rounded with no warning. How to repeat: CREATE TABLE `testing` ( `colDate` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; insert into testing values ('2016-07-22 12:49:07.5'); select * from testing where coldate = '2016-07-22 12:49:07.5'; The select statement returns no rows despite the where clause containing exactly what we supposedly just inserted. Suggested fix: Make the behavior between set/insert clauses & where clauses the same for DateTime columns, or provide a warning when a DateTime gets rounded.