Description:
When a DECIMAL column has more than one copy of
a negative value, the MAX function returns the wrong
result. When the duplicate value is a positive number,
MIN returns the wrong result.
How to repeat:
mysql> create table t1 (col1 decimal(16,12));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values (-5.00000000001);
Query OK, 1 row affected (0.01 sec)
mysql> select col1,sum(col1),max(col1),min(col1) from t1 group by col1\G
*************************** 1. row ***************************
col1: -5.000000000010
sum(col1): -5.000000000010
max(col1): -5.000000000010
min(col1): -5.000000000010
1 row in set (0.00 sec)
-- With only one instance of -5.00000000001 in the table,
the results are all correct.
mysql> insert into t1 select * from t1;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select col1,sum(col1),max(col1),min(col1) from t1 group by col1\G
*************************** 1. row ***************************
col1: -5.000000000010
sum(col1): -10.000000000020
max(col1): 88602.00000000/0//
min(col1): -5.000000000010
1 row in set (0.00 sec)
-- If I insert the same value again, the MAX function
returns the wrong result.
mysql> delete from t1;
Query OK, 2 rows affected (0.00 sec)
mysql> insert into t1 values (-5.00000000001);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values (-5.00000000001);
Query OK, 1 row affected (0.00 sec)
mysql> select col1,sum(col1),max(col1),min(col1) from t1 group by col1\G
*************************** 1. row ***************************
col1: -5.000000000010
sum(col1): -10.000000000020
max(col1): 88602.00000000/0//
min(col1): -5.000000000010
1 row in set (0.01 sec)
-- The MAX result is wrong even when I insert the duplicate value
directly, rather than via INSERT ... SELECT.
mysql> delete from t1;
Query OK, 2 rows affected (0.00 sec)
mysql> insert into t1 values (5.00000000001);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values (5.00000000001);
Query OK, 1 row affected (0.00 sec)
mysql> select col1,sum(col1),max(col1),min(col1) from t1 group by col1\G
*************************** 1. row ***************************
col1: 5.000000000010
sum(col1): 10.000000000020
max(col1): 5.000000000010
min(col1): -88602.00000000/0//
1 row in set (0.00 sec)
-- When the duplicate value is positive rather than negative,
the MIN function returns the wrong result -- but MAX now
returns the correct result.
Description: When a DECIMAL column has more than one copy of a negative value, the MAX function returns the wrong result. When the duplicate value is a positive number, MIN returns the wrong result. How to repeat: mysql> create table t1 (col1 decimal(16,12)); Query OK, 0 rows affected (0.01 sec) mysql> insert into t1 values (-5.00000000001); Query OK, 1 row affected (0.01 sec) mysql> select col1,sum(col1),max(col1),min(col1) from t1 group by col1\G *************************** 1. row *************************** col1: -5.000000000010 sum(col1): -5.000000000010 max(col1): -5.000000000010 min(col1): -5.000000000010 1 row in set (0.00 sec) -- With only one instance of -5.00000000001 in the table, the results are all correct. mysql> insert into t1 select * from t1; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select col1,sum(col1),max(col1),min(col1) from t1 group by col1\G *************************** 1. row *************************** col1: -5.000000000010 sum(col1): -10.000000000020 max(col1): 88602.00000000/0// min(col1): -5.000000000010 1 row in set (0.00 sec) -- If I insert the same value again, the MAX function returns the wrong result. mysql> delete from t1; Query OK, 2 rows affected (0.00 sec) mysql> insert into t1 values (-5.00000000001); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values (-5.00000000001); Query OK, 1 row affected (0.00 sec) mysql> select col1,sum(col1),max(col1),min(col1) from t1 group by col1\G *************************** 1. row *************************** col1: -5.000000000010 sum(col1): -10.000000000020 max(col1): 88602.00000000/0// min(col1): -5.000000000010 1 row in set (0.01 sec) -- The MAX result is wrong even when I insert the duplicate value directly, rather than via INSERT ... SELECT. mysql> delete from t1; Query OK, 2 rows affected (0.00 sec) mysql> insert into t1 values (5.00000000001); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values (5.00000000001); Query OK, 1 row affected (0.00 sec) mysql> select col1,sum(col1),max(col1),min(col1) from t1 group by col1\G *************************** 1. row *************************** col1: 5.000000000010 sum(col1): 10.000000000020 max(col1): 5.000000000010 min(col1): -88602.00000000/0// 1 row in set (0.00 sec) -- When the duplicate value is positive rather than negative, the MIN function returns the wrong result -- but MAX now returns the correct result.