Bug #100393 The division precision is inconsistent
Submitted: 31 Jul 2020 4:10 Modified: 31 Jul 2020 8:26
Reporter: jiaona chen Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S7 (Test Cases)
Version: 8.0.19, 5.7.31, 8.0.21 OS:Any
Assigned to: CPU Architecture:Any

[31 Jul 2020 4:10] jiaona chen
Description:
In different partition tables, the precision of division is inconsistent.
mysql> create table bug22555 (id  smallint, s1 smallint, s2 smallint, o1 double, o2 double, e1 decimal, e2 decimal) engine=innodb
    -> partition by key(id) partitions 10;
Query OK, 0 rows affected (0.59 sec)

mysql> insert into bug22555 values (1,53,78,53,78,53,78),(2,17,78,17,78,17,78),(3,18,76,18,76,18,76);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select STD(s1/s2) from bug22555;
+---------------------+
| STD(s1/s2)          |
+---------------------+
| 0.21325763593256278 |
+---------------------+
1 row in set (0.00 sec)

mysql> select STD(e1/e2) from bug22555;
+---------------------+
| STD(e1/e2)          |
+---------------------+
| 0.21325763593256278 |
+---------------------+
1 row in set (0.00 sec)

mysql> drop table bug22555;
Query OK, 0 rows affected (0.31 sec)

mysql> create table bug22555 (id  smallint, s1 smallint, s2 smallint, o1 double, o2 double, e1 decimal, e2 decimal) engine=innodb
    -> partition by key(id) partitions 8;
Query OK, 0 rows affected (0.39 sec)

mysql> insert into bug22555 values (1,53,78,53,78,53,78),(2,17,78,17,78,17,78),(3,18,76,18,76,18,76);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select STD(s1/s2) from bug22555;
+--------------------+
| STD(s1/s2)         |
+--------------------+
| 0.2132576359325628 |
+--------------------+
1 row in set (0.00 sec)

mysql> select STD(e1/e2) from bug22555;
+--------------------+
| STD(e1/e2)         |
+--------------------+
| 0.2132576359325628 |
+--------------------+
1 row in set (0.00 sec)

IN partition 8, the results of S1 and S2 have different precision.

How to repeat:
source test:
let $i=11;
while ($i > 0)
{

eval
create table bug22555 (i smallint, s1 smallint, s2 smallint, o1 double, o2 double, e1 decimal, e2 decimal) engine=innodb
partition by key(i) partitions $i;
insert into bug22555 values (1,53,78,53,78,53,78),(2,17,78,17,78,17,78),(3,18,76,18,76,18,76);
select std(s1/s2) from bug22555; #error
select std(e1/e2) from bug22555; #error
drop table bug22555;

dec $i;
}
[31 Jul 2020 8:26] MySQL Verification Team
Hello jiaona chen,

Thank you for the report and test case.
Observed with both 5.7.31 and 8.0.21.

regards,
Umesh