Bug #31616 div_precision_increment description looks wrong
Submitted: 15 Oct 2007 17:33 Modified: 8 Dec 2008 16:46
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.23-beta-debug-log OS:Any
Assigned to: Alexey Botchkov
Triage: Triaged: D2 (Serious)

[15 Oct 2007 17:33] Peter Gulutzan
Description:
The MySQL Reference Manual 11.5.1. Arithmetic Operators
http://dev.mysql.com/doc/refman/5.1/en/arithmetic-functions.html
says:

"In multiplication and division, the precision of the result when using
two exact values is the precision of the first argument + the value of
the div_precision_increment global variable. For example, the expression
5.05 / 0.0014  would have a precision of six decimal places (3607.142857)."

1.
I don't see why 3607.142857 is considered to have a precision of
six decimal places. Should the word be "scale" instead of "precision"?

2.
5.05 / 0.0014 would have a scale of six decimal places
if the old rule applied (that scale = scale of first operator
plus scale of second operator). But it doesn't. Since the
example doesn't say what div_precision_increment is, it's
meaningless now.

3.
Although it affects DML operations (for example if I'm inserting
1/3 into a decimal(3,2) column), div_precision_increment changes
don't go into the binary log. There surely is some place
in the manual where one explains unlogged things.

4.
I am unable to find any effect on multiplication.
Does anyone have any idea why it's mentioned?
If it does affect multiplication, it's a server
error, because according to ANSI the result scale
should be the sum of the argument scales.

How to repeat:
Try to read the manual.
[15 Oct 2007 17:48] Paul Dubois
1. Yes, "scale" looks to be a better word. Perhaps div_precision_increment is misnamed.

2. You're looking to have the default value of 4 mentioned here?

3. Rather than explaining an unlogged thing, isn't it a server bug that something affecting DMR operations is not logged?

4.Need developer input for this one (to indicate whether div_precision_increment should apply only to division).
[15 Oct 2007 18:21] Miguel Solorzano
Thank you for the bug report.
[16 Oct 2007 16:36] Paul Dubois
I have revised the statement in question to:

In division performed with /, the scale of the result when using two
exact values is the scale of the first argument plus the value of the
div_precision_increment global variable (which is 4 by default). For
example, the result of the expression 5.05 / 0.014 has a scale of six
decimal places (360.714286). 

However, I am also changing the bug category to Server and resetting
to Open, for two reasons:

Peter's point 3 points out that div_precision_increment changes are
not logged, even though such changes are relevant to subsequent
DML operations.

In discussion with Peter, the following problem was found:

Test script:

drop table if exists t;
create table t as select 5.05 / 0.014;
show warnings;
show create table t\G
select * from t;

Result:

mysql> drop table if exists t;
Query OK, 0 rows affected (0.58 sec)

mysql> create table t as select 5.05 / 0.014;
Query OK, 1 row affected, 1 warning (0.53 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> show warnings;
+---------+------+-------------------------------------------------------+
| Level   | Code | Message                                               |
+---------+------+-------------------------------------------------------+
| Warning | 1264 | Out of range value for column '5.05 / 0.014' at row 1 | 
+---------+------+-------------------------------------------------------+
1 row in set (0.10 sec)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `5.05 / 0.014` decimal(7,6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.13 sec)

mysql> select * from t;
+--------------+
| 5.05 / 0.014 |
+--------------+
|     9.999999 | 
+--------------+
1 row in set (0.15 sec)

Note that for the resulting table column, the data type is decimal(7,6).
The scale (6) is correct, but the precision should be 9.  Consequently,
the wrong value is stored in the table.  The correct result of the
calculation should be:

mysql> select 5.05 / 0.014;
+--------------+
| 5.05 / 0.014 |
+--------------+
|   360.714286 | 
+--------------+
[28 Jan 2008 21:03] Mikael Ronström
Uploaded patch to wrong bug, should have been uploaded to bug#33182
[29 Feb 2008 15:41] Miguel Solorzano
Verified again with Paul's test case.
[21 Apr 2008 5:13] 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/45741

ChangeSet@1.2602, 2008-04-21 09:04:00+05:00, holyfoot@mysql.com +3 -0
  Bug #31616 div_precision_increment description looks wrong
  
  Item_func_div didn't calculate the precision of the result properly.
  The result of 5/0.0001 is 5000 so we have to add decimals of the divisor
  to the planned precision.
[17 Nov 2008 16:54] 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/58973

2717 Alexey Botchkov	2008-11-17
      Bug#31616 div_precision_increment description looks wrong
      
      Item_func_div didn't calculate the precision of the result properly.
        The result of 5/0.0001 is 5000 so we have to add decimals of the divisor
        to the planned precision.
      
      per-file comments:
        mysql-test/r/type_newdecimal.result
      Bug#31616 div_precision_increment description looks wrong
          test result fixed
      
        mysql-test/t/type_newdecimal.test
      Bug#31616 div_precision_increment description looks wrong
          test case
      
        sql/item_func.cc
      Bug#31616 div_precision_increment description looks wrong
          precision must be increased with args[1]->decimals parameter
[2 Dec 2008 13:01] Bugs System
Pushed into 5.0.74  (revid:holyfoot@mysql.com-20081117154109-zbf2j3m9ip2hdva7) (version source revid:holyfoot@mysql.com-20081117154109-zbf2j3m9ip2hdva7) (pib:5)
[3 Dec 2008 20:56] Paul Dubois
Noted in 5.0.74 changelog.

Some division operations produced a result with incorrect precision.

Resetting report to NDI pending push into 5.1.x, 6.0.x.
[8 Dec 2008 10:20] Bugs System
Pushed into 5.1.31  (revid:holyfoot@mysql.com-20081117154109-zbf2j3m9ip2hdva7) (version source revid:patrick.crews@sun.com-20081126180318-v685u61mpgoc176x) (pib:5)
[8 Dec 2008 11:31] Bugs System
Pushed into 6.0.9-alpha  (revid:holyfoot@mysql.com-20081117154109-zbf2j3m9ip2hdva7) (version source revid:ingo.struewing@sun.com-20081121151447-dtf2ofz2ys0zqed1) (pib:5)
[8 Dec 2008 16:46] Paul Dubois
Noted in 5.1.31, 6.0.9 changelogs.
[19 Jan 2009 11:22] Bugs System
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:00] Bugs System
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 16:06] Bugs System
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)