Bug #8461 TRUNCATE: returns incorrect result if 2nd argument is negative
Submitted: 11 Feb 2005 21:58 Modified: 14 Feb 2006 20:16
Reporter: Trudy Pelzer Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.3-alpha-debug 4.1-BK OS:Linux (SuSE 9.1)
Assigned to: Bugs System

[11 Feb 2005 21:58] Trudy Pelzer
Description:
If TRUNCATE is used with two arguments, and the second argument
is a negative number, the result should be that the function returns 
<argument 1> with the integer part of the number zeroed out to the
number of digits specified by <argument 2>. This is not happening
correctly in all cases; probably related to BIGINT values.

This may be related to Bug#6468.

How to repeat:
mysql> create table t1 (col1 bigint);
mysql> insert into t1 values(9000000000000000500);
mysql> insert into t1 values(-9000000000000000500);

mysql> select truncate(col1,-1) from t1;
+----------------------+
| truncate(col1,-1)    |
+----------------------+
|  9000000000000000000 |
| -9000000000000000000 |
+----------------------+
2 rows in set (0.00 sec)
-- This is the incorrect result; only the last digit
should be rounded to zero. The correct result is:
+----------------------+
| col1                 |
+----------------------+
|  9000000000000000500 |
| -9000000000000000500 |
+----------------------+
[24 Jan 2006 10:44] Gunnar von Boehn
verified with 4.1.18-BK

Gunnar
[1 Feb 2006 11:39] Gunnar von Boehn
The problem is that round() and truncate() in mysql 4.1
always worked with doubles internally. 
In 5.0 integer truncation is still done by floor((double(value)/digit)*digit.
So bigint are converted to double resulting in unprecise results.
[7 Feb 2006 12:38] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/2240
[14 Feb 2006 18:01] Gunnar von Boehn
fixed in 5.0.19 and 5.1.6
[14 Feb 2006 20:16] Mike Hillyer
Documented in 5.0.19 and 5.1.6 changelogs:

  <listitem>
        <para>
          Using the <function>TRUNCATE()</function> function with a
          negative number for the second argument on a
          <literal>BIGINT</literal> column returned incorrect results.
          (Bug #8461)
        </para>
      </listitem>