MySQL Bugs: #31616: div_precision_increment description looks wrong

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

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.

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).

Thank you for the bug report.

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

Uploaded patch to wrong bug, should have been uploaded to bug#33182

Verified again with Paul's test case.

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.

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

Pushed into 5.0.74  (revid:holyfoot@mysql.com-20081117154109-zbf2j3m9ip2hdva7) (version source revid:holyfoot@mysql.com-20081117154109-zbf2j3m9ip2hdva7) (pib:5)

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.

Pushed into 5.1.31  (revid:holyfoot@mysql.com-20081117154109-zbf2j3m9ip2hdva7) (version source revid:patrick.crews@sun.com-20081126180318-v685u61mpgoc176x) (pib:5)

Pushed into 6.0.9-alpha  (revid:holyfoot@mysql.com-20081117154109-zbf2j3m9ip2hdva7) (version source revid:ingo.struewing@sun.com-20081121151447-dtf2ofz2ys0zqed1) (pib:5)

Noted in 5.1.31, 6.0.9 changelogs.

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)

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)

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)