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
• Status: Verified
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 5.0bk, 5.1bk
• OS: Any
• CPU Architecture: Any

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