Bug #31013 Fractions used with INTERVAL are rounded without error or warning
Submitted: 14 Sep 2007 4:04 Modified: 14 Sep 2007 10:59
Reporter: Tobias Asplund Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.0bk, 5.1bk OS:Any
Assigned to:
Triage: Triaged: D2 (Serious) / R3 (Medium) / E3 (Medium)

[14 Sep 2007 4:04] Tobias Asplund
Description:
The server accepts fractional numbers for the the INTERVAL temporal functionality, but rounds it quietly without any indication it actually does.

How to repeat:
DROP TABLE IF EXISTS t1bug;
CREATE TABLE t1bug ( a DATETIME );
SET SQL_MODE = 'STRICT_ALL_TABLES';
INSERT INTO t1bug VALUES (NOW() + INTERVAL 0.5 HOUR), (NOW() + INTERVAL 0.4 HOUR);
SELECT * FROM t1bug;
DROP TABLE t1bug;

Suggested fix:
Since it accepts something like 1/2 HOUR it should (especially in STRICT mode) let me know it's not using the number I submitted.
[14 Sep 2007 10:59] Hartmut Holzgraefe
Verified on Linux with both current 5.0 and 5.1 bk builds
[14 Sep 2007 11:00] Hartmut Holzgraefe
mysqltest test case (using fixed dates instead of NOW())

Attachment: bug31013.tgz (application/x-gtar, text), 861 bytes.

[4 Jun 2009 11:36] Konstantin Osipov
See also Bug#8523
[23 Dec 2010 13:27] Daniƫl van Eeden
Version 5.5.8 with STRICT_ALL_TABLES returns a WARNING for incorrect DECIMAL values.

And there is no warning for rounded DECIMAL values.

mysql> SET sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT NOW() + INTERVAL -999999999999999999999999999999999999999999999 DAY;
+---------------------------------------------------------------------+
| NOW() + INTERVAL -999999999999999999999999999999999999999999999 DAY |
+---------------------------------------------------------------------+
| 2010-12-23 14:23:48                                                 |
+---------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DECIMAL value: '' |
+---------+------+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW() + INTERVAL 0 DAY, NOW() + INTERVAL 0.4 DAY, NOW() + INTERVAL 0.9 DAY;
+------------------------+--------------------------+--------------------------+
| NOW() + INTERVAL 0 DAY | NOW() + INTERVAL 0.4 DAY | NOW() + INTERVAL 0.9 DAY |
+------------------------+--------------------------+--------------------------+
| 2010-12-23 14:26:14    | 2010-12-23 14:26:14      | 2010-12-24 14:26:14      |
+------------------------+--------------------------+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT VERSION();
+--------------------------------------+
| VERSION()                            |
+--------------------------------------+
| 5.5.8-enterprise-commercial-advanced |
+--------------------------------------+
1 row in set (0.00 sec)

mysql>