MySQL Bugs: #77634: returned value overflows decimal data type

Bug #77634	returned value overflows decimal data type
Submitted:	6 Jul 2015 23:46	Modified:	15 Oct 2015 14:07
Reporter:	Son Nguyen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.5.45, 5.6.25, 5.7.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi,

I have the query below that returns the value 2.5186 that fits into the described data type decimal(31,4)

select  distinct `T0`.`C0`, `T0`.`C1`, 
`T0`.`C0` / nullif(`T0`.`C1`, 0)  
from 
(
select sum(distinct `ORDERDETAIL`.`QUANTITY`) as "C0" , 
count(`ORDERDETAIL`.`ORDERDETAILCODE`) as "C1"  
from `ORDERDETAIL` `ORDERDETAIL` 
) `T0`

SQLDescribeCol
*ColumnName = "`T0`.`C0` / nullif(`T0`.`C1`, 0)", *NameLengthPtr = 32, *DataTypePtr = SQL_DECIMAL=3, *ColumnSizePtr = 31,
*DecimalDigits = 4, *NullablePtr = SQL_NULLABLE=1

Get Data All:
"C0", "C1", "`T0`.`C0` / nullif(`T0`.`C1`, 0)"
108460, 43063, 2.5186

Now I just put an unrelated query as cross product with the original query, I got the same description of the column `T0`.`C0` / nullif(`T0`.`C1`, 0) as decimal(31, 4), but the returned value is 2.518635487 which overflows the decimal(31,4)

select  distinct `T0`.`C0`  , `T0`.`C1`  ,  `T0`.`C0` / nullif(`T0`.`C1`, 0)  
from 
(
select sum(distinct `ORDERDETAIL`.`QUANTITY`) as "C0" , 
count(`ORDERDETAIL`.`ORDERDETAILCODE`) as "C1"  
from `ORDERDETAIL` `ORDERDETAIL` 
) `T0`
,( select 0  from `COUNTRY`  ) T1

Get Data All:
"C0", "C1", "`T0`.`C0` / nullif(`T0`.`C1`, 0)"
108460, 43063, 2.518635487

Here are database and driver versions:
SQL_DBMS_NAME=17, 10, "MySQL"
SQL_DBMS_VER=18, 24, "5.7.7-rc-log"
SQL_DRIVER_NAME=6, 24, "myodbc5a.dll"
SQL_DRIVER_VER=7, 20, "05.02.0004"

This is new behavior from server version 5.7.7.
I don't have issue with server version 5.6

How to repeat:
Run the query in description using any utility such as MS ODBCTest utility on Windows.

Hello Son Nguyen,

Thank you for the bug report.
I could not repeat the issue on MySQL 5.7.7 and ODBC 5.2.7, 5.3.4. 
Can you please share us result of this same query in  MySQL.exe command line to confirm this issue at our end?

Thanks,
Chiranjeevi.

Hi,

I just provided the dump file of our database in the bug #77631. 

Please use the dump file to reproduce the issue.

Thanks

Son Nguyen

Hello  Son Nguyen,

Thank you for your feedback.
Verified this behavior on Visual Studio 2013  with  MySQL Connector / ODBC 5.3.4, and also confirmed with MySQL clients.
Dump file was taken from  Bug#77632

Thanks,
Chiranjeevi.

Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.7-rc-enterprise-commercial-advanced-log MySQL Enterprise Ser
ver - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use gosales1
Database changed
mysql> select  distinct `T0`.`C0`  , `T0`.`C1`  ,  `T0`.`C0` / nullif(`T0`.`C1`, 0) from ( select sum(distinct `ORDERDETAIL`.`QUANTITY`)  as "C0" , co
unt(`ORDERDETAIL`.`ORDERDETAILCODE`) as "C1"  from `ORDERDETAIL` `ORDERDETAIL` ) `T0` ,( select 0  from `COUNTRY`  ) T1;
+--------+-------+----------------------------------+
| C0     | C1    | `T0`.`C0` / nullif(`T0`.`C1`, 0) |
+--------+-------+----------------------------------+
| 108460 | 43063 |                      2.518635487 |
+--------+-------+----------------------------------+
1 row in set (0.11 sec)

mysql> select  distinct `T0`.`C0`  , `T0`.`C1`  ,  `T0`.`C0` / nullif(`T0`.`C1`, 0) from ( select sum(distinct `ORDERDETAIL`.`QUANTITY`)  as "C0" , co
unt(`ORDERDETAIL`.`ORDERDETAILCODE`) as "C1"  from `ORDERDETAIL` `ORDERDETAIL` ) `T0` ;
+--------+-------+----------------------------------+
| C0     | C1    | `T0`.`C0` / nullif(`T0`.`C1`, 0) |
+--------+-------+----------------------------------+
| 108460 | 43063 |                           2.5186 |
+--------+-------+----------------------------------+
1 row in set (0.06 sec)

screenshot

Attachment: 77634.PNG (image/png, text), 68.21 KiB.

Test results in 5.5 and 5.6

Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.25-enterprise-commercial-advanced-log MySQL Enterprise Serve
r - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use gosales1;
Database changed
mysql> select  distinct `T0`.`C0`  , `T0`.`C1`  ,  `T0`.`C0` / nullif(`T0`.`C1`, 0) from ( select sum(distinct `ORDERDETAIL`.`QUANTITY`)  as "C0" , co
unt(`ORDERDETAIL`.`ORDERDETAILCODE`) as "C1"  from `ORDERDETAIL` `ORDERDETAIL` ) `T0` ,( select 0  from `COUNTRY`  ) T1;
+--------+-------+----------------------------------+
| C0     | C1    | `T0`.`C0` / nullif(`T0`.`C1`, 0) |
+--------+-------+----------------------------------+
| 108460 | 43063 |                      2.518635487 |
+--------+-------+----------------------------------+
1 row in set (0.05 sec)

mysql>  select  distinct `T0`.`C0`  , `T0`.`C1`  ,  `T0`.`C0` / nullif(`T0`.`C1`, 0) from ( select sum(distinct `ORDERDETAIL`.`QUANTITY`)  as "C0" , c
ount(`ORDERDETAIL`.`ORDERDETAILCODE`) as "C1"  from `ORDERDETAIL` `ORDERDETAIL` ) `T0` ;
+--------+-------+----------------------------------+
| C0     | C1    | `T0`.`C0` / nullif(`T0`.`C1`, 0) |
+--------+-------+----------------------------------+
| 108460 | 43063 |                           2.5186 |
+--------+-------+----------------------------------+
1 row in set (0.04 sec)

mysql> select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.6.25-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql>

Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.45-enterprise-commercial-advanced-log MySQL Enterprise Serve
r - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use gosales1;
Database changed
mysql> select  distinct `T0`.`C0`  , `T0`.`C1`  ,  `T0`.`C0` / nullif(`T0`.`C1`, 0) from ( select sum(distinct `ORDERDETAIL`.`QUANTITY`)  as "C0" , co
unt(`ORDERDETAIL`.`ORDERDETAILCODE`) as "C1"  from `ORDERDETAIL` `ORDERDETAIL` ) `T0` ,( select 0  from `COUNTRY`  ) T1;
+--------+-------+----------------------------------+
| C0     | C1    | `T0`.`C0` / nullif(`T0`.`C1`, 0) |
+--------+-------+----------------------------------+
| 108460 | 43063 |                      2.518635487 |
+--------+-------+----------------------------------+
1 row in set (0.04 sec)

mysql>
mysql>  select  distinct `T0`.`C0`  , `T0`.`C1`  ,  `T0`.`C0` / nullif(`T0`.`C1`, 0) from ( select sum(distinct `ORDERDETAIL`.`QUANTITY`)  as "C0" , c
ount(`ORDERDETAIL`.`ORDERDETAILCODE`) as "C1"  from `ORDERDETAIL` `ORDERDETAIL` ) `T0` ;
+--------+-------+----------------------------------+
| C0     | C1    | `T0`.`C0` / nullif(`T0`.`C1`, 0) |
+--------+-------+----------------------------------+
| 108460 | 43063 |                           2.5186 |
+--------+-------+----------------------------------+
1 row in set (0.05 sec)

mysql> select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.5.45-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql>

Hi  Chiranjeevi,

Thanks for testing the query against 5.5 and 5.6 databases.  Actually the query is my simplified version.  So the simplified query shows issue exist back in 5.5.

Please use the original query in our regression that does not show issue in 5.6 (ie returning 2.5186), but shows issue in 5.7 (ie returning 2.518635487).

select distinct `T0`.`C0` / nullif(`T0`.`C1`, 0 )
from
(
select `T0`.`C0` as "C0" , `T0`.`C1` as "C1"
from
(
select 
sum(distinct `ORDERDETAIL`.`QUANTITY`) as "C0" , count(`ORDERDETAIL`.`ORDERDETAILCODE`) as "C1"
from `ORDERDETAIL` `ORDERDETAIL` 
having sum(distinct `ORDERDETAIL`.`QUANTITY`) / nullif(count(`ORDERDETAIL`.`ORDERDETAILCODE`), 0) <> 0
) `T0`,
(select '0' as "C0"  from `ORDERDETAIL` `ORDERDETAIL` ) `T1`) `T0`

Thanks

Son Nguyen

Noted in 5.7.10, 5.8.0 changelogs.

Item_copy_decimal::copy() did not take the div_precision_increment
system variable value into account, resulting in DECIMAL values being
returned with incorrect precision from some queries.