summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorVarun Gupta <varun.gupta@mariadb.com>2020-07-22 14:44:25 +0530
committerVarun Gupta <varun.gupta@mariadb.com>2020-07-22 14:44:25 +0530
commit62d73df6b270cc94ba577e96d3bf325170f306fe (patch)
treed2071cbebbd674ac42b00816a139eb2c1a97c661
parent57ec42bc321dee796ce8e711a4499cd665513009 (diff)
downloadmariadb-git-62d73df6b270cc94ba577e96d3bf325170f306fe.tar.gz
MDEV-19232: Floating point precision / value comparison problem
The issue occurs when the subquery_cache is enabled. When there is a cache miss the division was leading to a value with scale 9. In the case of cache hit the value returned was of scale 9 and due to the different values for the scales the where condition evaluated to FALSE, hence the output was incomplete. To fix this problem we need to round up the decimal to the limit mentioned in Item::decimals. This would make sure the values are compared with the same scale.
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.result24
-rw-r--r--mysql-test/r/type_newdecimal.result5
-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--mysql-test/t/subselect4.test26
-rw-r--r--sql/item_func.cc2
-rw-r--r--storage/tokudb/mysql-test/tokudb/r/type_newdecimal.result5
-rw-r--r--storage/tokudb/mysql-test/tokudb/r/type_ranges.result6
11 files changed, 68 insertions, 30 deletions
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index 06323f5b3bb..d0d413331df 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.21325764
+0.21328517
select std(o1/o2) from bug22555;
std(o1/o2)
0.2132576358664934
select std(e1/e2) from bug22555;
std(e1/e2)
-0.21325764
+0.21328517
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 4330c8a2045..f23295bd61b 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 9.0000
+1.00000000 1.00000000 8.9998
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 22d4938fb78..606ab847028 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -2584,3 +2584,27 @@ l1 i2
e 2
o 6
DROP TABLE t1, t2;
+#
+# MDEV-19232: Floating point precision / value comparison problem
+#
+CREATE TABLE t1 (region varchar(60), area decimal(10,0), population decimal(11,0));
+INSERT INTO t1 VALUES ('Central America and the Caribbean',91,11797);
+INSERT INTO t1 VALUES ('Central America and the Caribbean',442,66422);
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='subquery_cache=on';
+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
+SELECT * FROM t1 A
+WHERE population/area = (SELECT MAX(population/area) from t1 B where A.region = B.region);
+region area population
+Central America and the Caribbean 442 66422
+SET optimizer_switch='subquery_cache=off';
+SELECT * FROM t1 A
+WHERE population/area = (SELECT MAX(population/area) from t1 B where A.region = B.region);
+region area population
+Central America and the Caribbean 442 66422
+SET @@optimizer_switch= @save_optimizer_switch;
+DROP TABLE t1;
diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result
index dc6ef3c59e2..7b6629bc7b2 100644
--- a/mysql-test/r/type_newdecimal.result
+++ b/mysql-test/r/type_newdecimal.result
@@ -1530,11 +1530,8 @@ 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` (
@@ -1649,8 +1646,6 @@ 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
diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result
index 101bf0cfb2c..a10d2a56eae 100644
--- a/mysql-test/r/type_ranges.result
+++ b/mysql-test/r/type_ranges.result
@@ -94,8 +94,6 @@ 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
@@ -136,7 +134,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.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
+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
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
@@ -188,7 +186,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.33333333 ne
+2 0.3333 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 ee8b7c5691d..ffe23eb3cef 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.027
-2 Record_2 501 1.2525
-3 Record_3 210 0.525
+1 Record_1 100011 250.03
+2 Record_2 501 1.25
+3 Record_3 210 0.53
## 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.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
+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
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 06627fccf8b..a3b73d3444f 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.3333333330000000000
+1 2 0.3333000000000000000
select * from t5;
a b v
20141010 2 October
diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test
index b7a9c95abe7..21ec28b1c03 100644
--- a/mysql-test/t/subselect4.test
+++ b/mysql-test/t/subselect4.test
@@ -2112,3 +2112,29 @@ INSERT INTO t2 VALUES ('k'),('rid'),('f'),('x');
EXPLAIN EXTENDED SELECT * FROM t1 where ( t1.l1 < ANY (SELECT MAX(t2.v1) FROM t2));
SELECT * FROM t1 where ( t1.l1 < ANY (SELECT MAX(t2.v1) FROM t2));
DROP TABLE t1, t2;
+
+--echo #
+--echo # MDEV-19232: Floating point precision / value comparison problem
+--echo #
+
+CREATE TABLE t1 (region varchar(60), area decimal(10,0), population decimal(11,0));
+INSERT INTO t1 VALUES ('Central America and the Caribbean',91,11797);
+INSERT INTO t1 VALUES ('Central America and the Caribbean',442,66422);
+
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='subquery_cache=on';
+
+SELECT
+population, area, population/area,
+cast(population/area as DECIMAL(20,9)) FROM t1 LIMIT 1;
+
+SELECT * FROM t1 A
+WHERE population/area = (SELECT MAX(population/area) from t1 B where A.region = B.region);
+
+SET optimizer_switch='subquery_cache=off';
+SELECT * FROM t1 A
+WHERE population/area = (SELECT MAX(population/area) from t1 B where A.region = B.region);
+
+SET @@optimizer_switch= @save_optimizer_switch;
+
+DROP TABLE t1;
diff --git a/sql/item_func.cc b/sql/item_func.cc
index c41d7809bf9..5e605855d80 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -1719,6 +1719,8 @@ 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/storage/tokudb/mysql-test/tokudb/r/type_newdecimal.result b/storage/tokudb/mysql-test/tokudb/r/type_newdecimal.result
index 7d5b555488e..dd47b8a1a5c 100644
--- a/storage/tokudb/mysql-test/tokudb/r/type_newdecimal.result
+++ b/storage/tokudb/mysql-test/tokudb/r/type_newdecimal.result
@@ -1531,11 +1531,8 @@ 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` (
@@ -1650,8 +1647,6 @@ 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
diff --git a/storage/tokudb/mysql-test/tokudb/r/type_ranges.result b/storage/tokudb/mysql-test/tokudb/r/type_ranges.result
index bd8491336b3..395f21a8a8f 100644
--- a/storage/tokudb/mysql-test/tokudb/r/type_ranges.result
+++ b/storage/tokudb/mysql-test/tokudb/r/type_ranges.result
@@ -95,8 +95,6 @@ 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
@@ -137,7 +135,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.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
+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
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
@@ -189,7 +187,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.33333333 ne
+2 0.3333 ne
3 -1 -1 ne
4 -429496729 -4294967295 ne
5 4294967295 4294967295 ne
View Lorry Controller Status