MySQL Bugs: #8461: TRUNCATE: returns incorrect result if 2nd argument is negative

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:	Gunnar von Boehn	CPU Architecture:	Any

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

verified with 4.1.18-BK

Gunnar

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.

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

fixed in 5.0.19 and 5.1.6

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>