Bug #73458 | No error or warning on truncation of TIMESTAMP/DATETIME | ||
---|---|---|---|
Submitted: | 3 Aug 2014 10:23 | Modified: | 9 Sep 2014 15:50 |
Reporter: | Peter Laursen (Basic Quality Contributor) | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
Version: | 5.6.19, 5.6.21, 5.0.97 | OS: | Any |
Assigned to: | Jon Stephens | CPU Architecture: | Any |
Tags: | regression |
[3 Aug 2014 10:23]
Peter Laursen
[5 Aug 2014 20:05]
Sveta Smirnova
Thank you for the report. Verified as described. I tested with couple of versions, results are surprising: 5.0.97 - bug exists 5.1.74 - first INSERT query rejected 5.5.39 - first INSERT query rejected 5.6.21 - bug exists
[6 Aug 2014 6:58]
Roy Lyseng
This is in accordance with the SQL standard. The standard says that when storing a datetime value into a datetime field, if the value can be represented in the type of the field with rounding or truncation, then the field is set to that value. It is implementation-defined whether rounding or truncation is used, MySQL has chosen to use rounding.
[7 Aug 2014 10:07]
Roy Lyseng
This is a documentation issue. The manual lacks description on what happens when a datetime value is copied into a datetime field with a smaller FSP. This should probably be a paragraph in section 11.3.6
[7 Aug 2014 10:29]
Peter Laursen
@Roy .. You have to realize that MySQL 5.5 and 5.6 are inconsistent. If 5.6 is OK, a fix in 5.5 is required - and vice versa. Besides, I definitely thnk that at least a warning should occur.
[7 Aug 2014 11:04]
Roy Lyseng
@Peter: In 5.5, fractional seconds was not even supported, so erring out was OK. In 5.6, MySQL implemented FSP with semantics given by SQL standard. The SQL standard has the same requirement for DECIMAL numbers: If truncation/rounding is necessary because the target has less precision than the source, then no warning is given. I think this is reasonable, as no significant information is lost. OTOH, if an overflow occurs, an exception should be given, because significant information is lost.
[5 Sep 2014 13:26]
Jon Stephens
Reassigning to myself. Status unchanged.
[9 Sep 2014 15:50]
Jon Stephens
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.