summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorSergei Golubchik <serg@mariadb.org>2020-10-28 20:44:03 +0100
committerSergei Golubchik <serg@mariadb.org>2020-10-29 09:27:56 +0100
commitd6302c9a47615019a3bca5304d3f61c587d826b8 (patch)
tree59767fcd42a2c08a4b03862aedf02812b25ab48b
parent313cf9de2c9036e5acd5fef3be6afebbad471998 (diff)
downloadmariadb-git-d6302c9a47615019a3bca5304d3f61c587d826b8.tar.gz
MDEV-23702 calculating(auto rounding) issue
Implement a different fix for "MDEV-19232: Floating point precision / value comparison problem" Instead of truncating decimal values after every division, truncate them for comparison purposes. This reverts commit 62d73df6b270 but keeps the test.
Diffstat
-rw-r--r--mysql-test/r/func_group.result4
-rw-r--r--mysql-test/r/parser_precedence.result2
-rw-r--r--mysql-test/r/subselect4.result2
-rw-r--r--mysql-test/r/type_newdecimal.result7
-rw-r--r--mysql-test/r/type_ranges.result6
-rw-r--r--mysql-test/suite/sys_vars/r/div_precision_increment_func.result16
-rw-r--r--mysql-test/suite/vcol/r/not_supported.result2
-rw-r--r--sql/item_cmpfunc.cc6
-rw-r--r--sql/item_func.cc2
-rw-r--r--sql/my_decimal.h11
-rw-r--r--storage/tokudb/mysql-test/tokudb/r/type_newdecimal.result7
-rw-r--r--storage/tokudb/mysql-test/tokudb/r/type_ranges.result6
12 files changed, 47 insertions, 24 deletions
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index d0d413331df..06323f5b3bb 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -1186,13 +1186,13 @@ i count(*) std(e1/e2)
3 4 0.00000000
select std(s1/s2) from bug22555;
std(s1/s2)
-0.21328517
+0.21325764
select std(o1/o2) from bug22555;
std(o1/o2)
0.2132576358664934
select std(e1/e2) from bug22555;
std(e1/e2)
-0.21328517
+0.21325764
set @saved_div_precision_increment=@@div_precision_increment;
set div_precision_increment=19;
select i, count(*), std(s1/s2) from bug22555 group by i order by i;
diff --git a/mysql-test/r/parser_precedence.result b/mysql-test/r/parser_precedence.result
index f23295bd61b..4330c8a2045 100644
--- a/mysql-test/r/parser_precedence.result
+++ b/mysql-test/r/parser_precedence.result
@@ -619,7 +619,7 @@ select 4 - 3 * 2, (4 - 3) * 2, 4 - (3 * 2);
Testing that / is left associative
select 15 / 5 / 3, (15 / 5) / 3, 15 / (5 / 3);
15 / 5 / 3 (15 / 5) / 3 15 / (5 / 3)
-1.00000000 1.00000000 8.9998
+1.00000000 1.00000000 9.0000
Testing that / has precedence over |
select 105 / 5 | 2, (105 / 5) | 2, 105 / (5 | 2);
105 / 5 | 2 (105 / 5) | 2 105 / (5 | 2)
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index e7655131fcf..b7161151928 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -2596,7 +2596,7 @@ SELECT
population, area, population/area,
cast(population/area as DECIMAL(20,9)) FROM t1 LIMIT 1;
population area population/area cast(population/area as DECIMAL(20,9))
-11797 91 129.6374 129.637400000
+11797 91 129.6374 129.637362637
SELECT * FROM t1 A
WHERE population/area = (SELECT MAX(population/area) from t1 B where A.region = B.region);
region area population
diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result
index 0c8f4f55442..6c9f3467b97 100644
--- a/mysql-test/r/type_newdecimal.result
+++ b/mysql-test/r/type_newdecimal.result
@@ -1530,8 +1530,11 @@ select (1.20396873 * 0.89550000 * 0.68000000 * 1.08721696 * 0.99500000 *
1.01500000 * 1.01500000 * 0.99500000)
0.812988073953673124592306939480
create table t1 as select 5.05 / 0.014;
+Warnings:
+Note 1265 Data truncated for column '5.05 / 0.014' at row 1
show warnings;
Level Code Message
+Note 1265 Data truncated for column '5.05 / 0.014' at row 1
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
@@ -1646,6 +1649,8 @@ my_col
0.123456789123456789123456789123
DROP TABLE t1;
CREATE TABLE t1 SELECT 1 / .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col;
+Warnings:
+Note 1265 Data truncated for column 'my_col' at row 1
DESCRIBE t1;
Field Type Null Key Default Extra
my_col decimal(65,4) YES NULL
@@ -1831,8 +1836,6 @@ DROP TABLE t1;
CREATE TABLE t1 (a DECIMAL(30,30));
INSERT INTO t1 VALUES (0.1),(0.2),(0.3);
CREATE TABLE t2 SELECT MIN(a + 0.0000000000000000000000000000001) AS c1 FROM t1;
-Warnings:
-Note 1265 Data truncated for column 'c1' at row 4
DESC t2;
Field Type Null Key Default Extra
c1 decimal(33,30) YES NULL
diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result
index a10d2a56eae..101bf0cfb2c 100644
--- a/mysql-test/r/type_ranges.result
+++ b/mysql-test/r/type_ranges.result
@@ -94,6 +94,8 @@ DROP INDEX test ON t1;
insert into t1 values (10, 1,1,1,1,1,1,1,1,1,1,1,1,1,NULL,0,0,0,1,1,1,1,'one','one');
insert into t1 values (NULL,2,2,2,2,2,2,2,2,2,2,2,2,2,NULL,NULL,NULL,NULL,NULL,NULL,2,2,'two','two,one');
insert into t1 values (0,1/3,3,3,3,3,3,3,3,3,3,3,3,3,NULL,'19970303','10:10:10','19970303101010','','','','3',3,3);
+Warnings:
+Warning 1265 Data truncated for column 'string' at row 1
insert into t1 values (0,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,NULL,19970807,080706,19970403090807,-1,-1,-1,'-1',-1,-1);
Warnings:
Warning 1264 Out of range value for column 'utiny' at row 1
@@ -134,7 +136,7 @@ select auto,string,tiny,short,medium,long_int,longlong,real_float,real_double,ut
auto string tiny short medium long_int longlong real_float real_double utiny ushort umedium ulong ulonglong mod(floor(time_stamp/1000000),1000000)-mod(curdate(),1000000) date_field time_field date_time blob_col tinyblob_col mediumblob_col longblob_col
10 1 1 1 1 1 1 1.0 1.0000 1 00001 1 1 1 0 0000-00-00 00:00:00 0000-00-00 00:00:00 1 1 1 1
11 2 2 2 2 2 2 2.0 2.0000 2 00002 2 2 2 0 NULL NULL NULL NULL NULL 2 2
-12 0.3333 3 3 3 3 3 3.0 3.0000 3 00003 3 3 3 0 1997-03-03 10:10:10 1997-03-03 10:10:10 3
+12 0.33333333 3 3 3 3 3 3.0 3.0000 3 00003 3 3 3 0 1997-03-03 10:10:10 1997-03-03 10:10:10 3
13 -1 -1 -1 -1 -1 -1 -1.0 -1.0000 0 00000 0 0 0 0 1997-08-07 08:07:06 1997-04-03 09:08:07 -1 -1 -1 -1
14 -429496729 -128 -32768 -8388608 -2147483648 -4294967295 -4294967296.0 -4294967295.0000 0 00000 0 0 0 0 0000-00-00 00:00:00 0000-00-00 00:00:00 -4294967295 -4294967295 -4294967295 -4294967295
15 4294967295 127 32767 8388607 2147483647 4294967295 4294967296.0 4294967295.0000 255 65535 16777215 4294967295 4294967295 0 0000-00-00 00:00:00 0000-00-00 00:00:00 4294967295 4294967295 4294967295 4294967295
@@ -186,7 +188,7 @@ Warning 1265 Data truncated for column 'new_field' at row 7
select * from t2;
auto string mediumblob_col new_field
1 2 2 ne
-2 0.3333 ne
+2 0.33333333 ne
3 -1 -1 ne
4 -429496729 -4294967295 ne
5 4294967295 4294967295 ne
diff --git a/mysql-test/suite/sys_vars/r/div_precision_increment_func.result b/mysql-test/suite/sys_vars/r/div_precision_increment_func.result
index ffe23eb3cef..ee8b7c5691d 100644
--- a/mysql-test/suite/sys_vars/r/div_precision_increment_func.result
+++ b/mysql-test/suite/sys_vars/r/div_precision_increment_func.result
@@ -50,9 +50,9 @@ INSERT into t1(name, salary, income_tax) values('Record_2', 501, 501*2.5/1000);
INSERT into t1(name, salary, income_tax) values('Record_3', 210, 210*2.5/1000);
SELECT * from t1;
id name salary income_tax
-1 Record_1 100011 250.03
-2 Record_2 501 1.25
-3 Record_3 210 0.53
+1 Record_1 100011 250.027
+2 Record_2 501 1.2525
+3 Record_3 210 0.525
## Creating new connection ##
## Verifying session & global value of variable ##
SELECT @@global.div_precision_increment = 2;
@@ -67,11 +67,11 @@ INSERT into t1(name, salary, income_tax) values('Record_5', 501, 501*2.5/1000);
INSERT into t1(name, salary, income_tax) values('Record_6', 210, 210*2.5/1000);
SELECT * from t1;
id name salary income_tax
-1 Record_1 100011 250.03
-2 Record_2 501 1.25
-3 Record_3 210 0.53
-4 Record_4 100011 250.028
-5 Record_5 501 1.253
+1 Record_1 100011 250.027
+2 Record_2 501 1.2525
+3 Record_3 210 0.525
+4 Record_4 100011 250.027
+5 Record_5 501 1.2525
6 Record_6 210 0.525
## Dropping table t1 ##
drop table t1;
diff --git a/mysql-test/suite/vcol/r/not_supported.result b/mysql-test/suite/vcol/r/not_supported.result
index a3b73d3444f..06627fccf8b 100644
--- a/mysql-test/suite/vcol/r/not_supported.result
+++ b/mysql-test/suite/vcol/r/not_supported.result
@@ -51,7 +51,7 @@ a b v
flush tables;
select * from t1;
a b v
-1 2 0.3333000000000000000
+1 2 0.3333333330000000000
select * from t5;
a b v
20141010 2 October
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 61c6b980551..7e99230723a 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -914,6 +914,8 @@ int Arg_comparator::compare_decimal()
{
if (set_null)
owner->null_value= 0;
+ my_decimal_round_if_needed(E_DEC_FATAL_ERROR, val1, (*a)->decimals, 0);
+ my_decimal_round_if_needed(E_DEC_FATAL_ERROR, val2, (*b)->decimals, 0);
return my_decimal_cmp(val1, val2);
}
}
@@ -938,7 +940,9 @@ int Arg_comparator::compare_e_decimal()
my_decimal *val2= (*b)->val_decimal(&decimal2);
if ((*a)->null_value || (*b)->null_value)
return MY_TEST((*a)->null_value && (*b)->null_value);
- return MY_TEST(my_decimal_cmp(val1, val2) == 0);
+ my_decimal_round_if_needed(E_DEC_FATAL_ERROR, val1, (*a)->decimals, 0);
+ my_decimal_round_if_needed(E_DEC_FATAL_ERROR, val2, (*b)->decimals, 0);
+ return my_decimal_cmp(val1, val2) == 0;
}
diff --git a/sql/item_func.cc b/sql/item_func.cc
index b4cffc84b80..034fb385f05 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -1719,8 +1719,6 @@ my_decimal *Item_func_div::decimal_op(my_decimal *decimal_value)
null_value= 1;
return 0;
}
- my_decimal_round(E_DEC_FATAL_ERROR, decimal_value,
- decimals, FALSE, decimal_value);
return decimal_value;
}
diff --git a/sql/my_decimal.h b/sql/my_decimal.h
index f318bcd1cd3..6a73cd54d50 100644
--- a/sql/my_decimal.h
+++ b/sql/my_decimal.h
@@ -304,6 +304,17 @@ bool my_decimal_is_zero(const my_decimal *decimal_value)
inline
+int my_decimal_round_if_needed(uint mask, my_decimal *dec, int scale,
+ bool truncate)
+{
+ if (scale >= dec->frac)
+ return E_DEC_OK;
+ return check_result(mask, decimal_round(dec, dec, scale,
+ (truncate ? TRUNCATE : HALF_UP)));
+}
+
+
+inline
int my_decimal_round(uint mask, const my_decimal *from, int scale,
bool truncate, my_decimal *to)
{
diff --git a/storage/tokudb/mysql-test/tokudb/r/type_newdecimal.result b/storage/tokudb/mysql-test/tokudb/r/type_newdecimal.result
index dd47b8a1a5c..1f173bbb796 100644
--- a/storage/tokudb/mysql-test/tokudb/r/type_newdecimal.result
+++ b/storage/tokudb/mysql-test/tokudb/r/type_newdecimal.result
@@ -1531,8 +1531,11 @@ select (1.20396873 * 0.89550000 * 0.68000000 * 1.08721696 * 0.99500000 *
1.01500000 * 1.01500000 * 0.99500000)
0.812988073953673124592306939480
create table t1 as select 5.05 / 0.014;
+Warnings:
+Note 1265 Data truncated for column '5.05 / 0.014' at row 1
show warnings;
Level Code Message
+Note 1265 Data truncated for column '5.05 / 0.014' at row 1
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
@@ -1647,6 +1650,8 @@ my_col
0.123456789123456789123456789123
DROP TABLE t1;
CREATE TABLE t1 SELECT 1 / .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col;
+Warnings:
+Note 1265 Data truncated for column 'my_col' at row 1
DESCRIBE t1;
Field Type Null Key Default Extra
my_col decimal(65,4) YES NULL
@@ -1832,8 +1837,6 @@ DROP TABLE t1;
CREATE TABLE t1 (a DECIMAL(30,30));
INSERT INTO t1 VALUES (0.1),(0.2),(0.3);
CREATE TABLE t2 SELECT MIN(a + 0.0000000000000000000000000000001) AS c1 FROM t1;
-Warnings:
-Note 1265 Data truncated for column 'c1' at row 4
DESC t2;
Field Type Null Key Default Extra
c1 decimal(33,30) YES NULL
diff --git a/storage/tokudb/mysql-test/tokudb/r/type_ranges.result b/storage/tokudb/mysql-test/tokudb/r/type_ranges.result
index 395f21a8a8f..bd8491336b3 100644
--- a/storage/tokudb/mysql-test/tokudb/r/type_ranges.result
+++ b/storage/tokudb/mysql-test/tokudb/r/type_ranges.result
@@ -95,6 +95,8 @@ DROP INDEX test ON t1;
insert into t1 values (10, 1,1,1,1,1,1,1,1,1,1,1,1,1,NULL,0,0,0,1,1,1,1,'one','one');
insert into t1 values (NULL,2,2,2,2,2,2,2,2,2,2,2,2,2,NULL,NULL,NULL,NULL,NULL,NULL,2,2,'two','two,one');
insert into t1 values (0,1/3,3,3,3,3,3,3,3,3,3,3,3,3,NULL,'19970303','10:10:10','19970303101010','','','','3',3,3);
+Warnings:
+Warning 1265 Data truncated for column 'string' at row 1
insert into t1 values (0,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,NULL,19970807,080706,19970403090807,-1,-1,-1,'-1',-1,-1);
Warnings:
Warning 1264 Out of range value for column 'utiny' at row 1
@@ -135,7 +137,7 @@ select auto,string,tiny,short,medium,long_int,longlong,real_float,real_double,ut
auto string tiny short medium long_int longlong real_float real_double utiny ushort umedium ulong ulonglong mod(floor(time_stamp/1000000),1000000)-mod(curdate(),1000000) date_field time_field date_time blob_col tinyblob_col mediumblob_col longblob_col
10 1 1 1 1 1 1 1.0 1.0000 1 00001 1 1 1 0 0000-00-00 00:00:00 0000-00-00 00:00:00 1 1 1 1
11 2 2 2 2 2 2 2.0 2.0000 2 00002 2 2 2 0 NULL NULL NULL NULL NULL 2 2
-12 0.3333 3 3 3 3 3 3.0 3.0000 3 00003 3 3 3 0 1997-03-03 10:10:10 1997-03-03 10:10:10 3
+12 0.33333333 3 3 3 3 3 3.0 3.0000 3 00003 3 3 3 0 1997-03-03 10:10:10 1997-03-03 10:10:10 3
13 -1 -1 -1 -1 -1 -1 -1.0 -1.0000 0 00000 0 0 0 0 1997-08-07 08:07:06 1997-04-03 09:08:07 -1 -1 -1 -1
14 -429496729 -128 -32768 -8388608 -2147483648 -4294967295 -4294967296.0 -4294967295.0000 0 00000 0 0 0 0 0000-00-00 00:00:00 0000-00-00 00:00:00 -4294967295 -4294967295 -4294967295 -4294967295
15 4294967295 127 32767 8388607 2147483647 4294967295 4294967296.0 4294967295.0000 255 65535 16777215 4294967295 4294967295 0 0000-00-00 00:00:00 0000-00-00 00:00:00 4294967295 4294967295 4294967295 4294967295
@@ -187,7 +189,7 @@ Warning 1265 Data truncated for column 'new_field' at row 7
select * from t2;
auto string mediumblob_col new_field
1 2 2 ne
-2 0.3333 ne
+2 0.33333333 ne
3 -1 -1 ne
4 -429496729 -4294967295 ne
5 4294967295 4294967295 ne
View Lorry Controller Status