[MDEV-19232] Floating point precision / value comparison problem Created: 2019-04-10  Updated: 2020-10-29  Resolved: 2020-07-22

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery, Platform RedHat
Affects Version/s: 5.5, 10.0, 10.1, 5.5.60, 10.2, 10.3, 10.4
Fix Version/s: 10.1.46, 10.2.33, 10.3.24, 10.4.14, 10.5.5

Type: Bug Priority: Major
Reporter: Michael Lackner Assignee: Varun Gupta (Inactive)
Resolution: Fixed Votes: 0
Labels: datatype
Environment:

CentOS Linux release 7.5.1804 (Core)
Linux 3.10.0-862.11.6.el7.x86_64
MariaDB Server 5.5.60-1.el7_5

Attachments: File cia.sql    
Issue Links:
Problem/Incident
causes MDEV-23702 calculating(auto rounding) issue Closed
causes MDEV-23762 Strange type-inference/rounding probl... Closed
Relates
relates to MDEV-23582 Unexpected result upon division of de... Closed

 Description   

In regards to the attached database/table, the following query returns only 9 rows and skips values for some regions, unlike older versions of MySQL (such as 5.0.95 on CentOS 5 Linux):

SELECT region, NAME, population/area AS dichte FROM cia c WHERE
population/area = (SELECT MAX(population/area) FROM cia cc WHERE
c.region = cc.region);

This appears to be a result of precision issues with floating point values affecting value comparisons.

The following two queries which are equivalent give different result sets:

SELECT region, NAME, population/area FROM cia c WHERE population/area =
(SELECT MAX(population/area) FROM cia cc WHERE area<150000 AND c.region
= cc.region) AND AREA<150000;

SELECT region, NAME, population/area FROM cia c WHERE AREA<150000 AND
population/area = (SELECT MAX(population/area) FROM cia cc WHERE
area<150000 AND c.region = cc.region);

 Comments   
Comment by Alice Sherepa [ 2019-04-11 ]

Thanks for the report! Reproducible on 5.5-10.4:

CREATE TABLE t1 (region varchar(60), area decimal(10,0), population decimal(11,0));
 
INSERT INTO t1 VALUES ('Asia',652000,25838797),('Europe',28748,3490435),('Africa',2381740,31193917),('Oceania',199,65446),('Europe',468,66824),('Africa',1246700,10145267),('Central America and the Caribbean',91,11797),('Antarctic Region',14000000,0),('Central America and the Caribbean',442,66422),('South America',2766890,36955182),('Commonwealth of Independent States',29800,3344336),('Central America and the Caribbean',193,69539),('Southeast Asia',5,0),('Oceania',7686850,19169083),('Europe',83858,8131111),('Commonwealth of Independent States',86600,7748163),('Central America and the Caribbean',13940,294982),('Middle East',620,634137),('Oceania',1,0),('Asia',144000,129194224),('Central America and the Caribbean',430,274540),('Commonwealth of Independent States',207600,10366719),('Europe',30510,10241506),('Central America and the Caribbean',22960,249183),('Africa',112620,6395919),('North America',58,62997),('Asia',47000,2005222),('South America',1098580,8152620),('Bosnia and Herzegovina, Europe',51129,3835777),('Africa',600370,1576470),('Antarctic Region',58,0),('South America',8511965,172860370),('World',60,0),('Central America and the Caribbean',150,19615),('Southeast Asia',5770,336376),('Europe',110910,7796694),('Africa',274200,11946065),('Southeast Asia',678500,41734853),('Africa',27830,6054714),('Southeast Asia',181040,12212306),('Africa',475440,15421937),('North America',9976140,31281092),('World',4033,401343),('Africa',259,34763),('Africa',622984,3512751),('Africa',1284000,8424504),('South America',756950,15153797),('Asia',9596960,1261832482),('Southeast Asia',135,2564),('World',7,0),('Southeast Asia',14,635),('South America, Central America and the Caribbean',1138910,39685655),('Africa',2170,578400),('Africa',2345410,51964999),('Africa',342000,2830961),('Oceania',240,20407),('Central America and the Caribbean',51100,3710558),('Africa',322460,15980950),('Europe',56538,4282216),('Central America and the Caribbean',110860,11141997),('Middle East',9250,758363),('Europe',78866,10272179),('Europe',43094,5336394),('Africa',22000,451442),('Central America and the Caribbean',754,71540),('Central America and the Caribbean',48730,8442533),('South America',283560,12920092),('Africa',1001450,68359979),('Central America and the Caribbean',21040,6122515),('Africa',28051,474214),('Africa',121320,4135933),('Europe',45226,1431471),('Africa',1127127,64117452),('Africa',28,0),('South America',12173,2826),('Europe',1399,45296),('Oceania',18270,832494),('Europe',337030,5167486),('Europe',547030,59329691),('South America',91000,172605),('Oceania',4167,249110),('Antarctic Region',7781,0),('Africa',267667,1208436),('Africa',11300,1367124),('Middle East',360,1132063),('Commonwealth of Independent States',69700,5019538),('Europe',357021,82797408),('Africa',238540,19533560),('Europe',6,29481),('Africa',5,0),('Europe',131940,10601527),('Arctic Region',2175600,56309),('Central America and the Caribbean',340,89018),('Central America and the Caribbean',1780,426493),('Oceania',541,154623),('Central America and the Caribbean',108890,12639939),('Europe',194,64080),('Africa',245857,7466200),('Africa',36120,1285715),('South America',214970,697286),('Central America and the Caribbean',27750,6867995),('Antarctic Region',412,0),('Europe',1880,921),('Central America and the Caribbean',112090,6249598),('Southeast Asia',1092,7116302),('Oceania',1,0),('Europe',93030,10138844),('Arctic Region',103000,276365),('Asia',3287590,1014003817),('Southeast Asia',1919440,224784210),('Middle East',1648000,65619636),('Middle East',437072,22675617),('Europe',70280,3797257),('Middle East',20770,5842454),('Europe',301230,57634327),('Central America and the Caribbean',10990,2652689),('Arctic Region',373,0),('Asia',377835,126549976),('Oceania',4,0),('Europe',116,88915),('Oceania',2,0),('Middle East',89213,4998564),('Africa',4,0),('Commonwealth of Independent States',2717300,16733227),('Africa',582650,30339770),('Oceania',1,0),('Oceania',717,91985),('Asia',120540,21687550),('Asia',98480,47470969),('Middle East',17820,1973572),('Commonwealth of Independent States',198500,4685230),('Southeast Asia',236800,5497459),('Europe',64589,2404926),('Middle East',10400,3578036),('Africa',30355,2143141),('Africa',111370,3164156),('Africa',1759540,5115450),('Europe',160,32207),('Europe',65200,3620756),('Europe',2586,437389),('Southeast Asia',21,445594),('Europe',25333,2041467),('Africa',587040,15506472),('Africa',118480,10385849),('Southeast Asia',329750,21793293),('Asia',300,301475),('Africa',1240000,10685948),('Europe',316,391670),('Europe',572,73117),('Oceania',181,68126),('Central America and the Caribbean',1100,414516),('Africa',1030700,2667859),('World',1860,1179368),('Africa',374,155911),('North America',1972550,100349766),('Oceania',702,133144),('Oceania',6,0),('Commonwealth of Independent States',33843,4430654),('Europe',2,31693),('Asia',1565000,2650952),('Central America and the Caribbean',100,6409),('Africa',446550,30122350),('Africa',801590,19104696),('Africa',825418,1771327),('Oceania',21,11845),('Central America and the Caribbean',5,0),('Asia',140800,24702119),('Europe',41532,15892237),('Central America and the Caribbean',960,210134),('Oceania',19060,201816),('Oceania',268680,3819762),('Central America and the Caribbean',129494,4812569),('Africa',1267000,10075511),('Africa',923768,123337822),('Oceania',260,2113),('Oceania',34,1892),('Oceania',477,71912),('Europe',324220,4481162),('Middle East',212460,2533389),('Asia',803940,141553775),('Oceania',458,18766),('Oceania',11,0),('Central America and the Caribbean',78200,2808268),('Oceania',462840,4926984),('South America',406750,5585828),('South America',1280000,27012899),('Southeast Asia',300000,81159644),('Oceania',47,54),('Europe',312685,38646023),('Europe',92391,10048232),('Central America and the Caribbean',9104,3915798),('Middle East',11437,744483),('World',2512,720934),('Europe',237500,22411121),('Asia',17075200,146001176),('Africa',26338,7229129),('Africa',410,7212),('Central America and the Caribbean',261,38819),('Central America and the Caribbean',620,156260),('North America',242,6896),('Central America and the Caribbean',389,115461),('Oceania',2860,179466),('Europe',60,26937),('Africa',1001,159883),('Middle East',1960582,22023506),('Africa',196190,9987494),('Europe',102350,10662087),('Africa',455,79326),('Africa',71740,5232624),('Southeast Asia',647,4151264),('Europe',48845,5407956),('Europe',20253,1927593),('Oceania',28450,466194),('Africa',637657,7253137),('Africa',1219912,43421021),('Antarctic Region',4066,0),('Europe',504782,39996671),('Asia',65610,19238575),('Africa',2505810,35079814),('South America',163270,431303),('Arctic Region',62049,2416),('Africa',17363,1083289),('Europe',449964,8873052),('Europe',41290,7262372),('Middle East',185180,16305659),('Southeast Asia',35980,22191087),('Commonwealth of Independent States',143100,6440732),('Africa',945087,35306126),('Southeast Asia',514000,61230874),('Africa',56785,5018502),('Oceania',10,1458),('Oceania',748,102321),('Central America and the Caribbean',5128,1175523),('Africa',1,0),('Middle East',163610,9593402),('Europe',780580,65666677),('Commonwealth of Independent States',488100,4518268),('Central America and the Caribbean',430,17502),('Oceania',26,10838),('Africa',236040,23317560),('Commonwealth of Independent States',603700,49153027),('Middle East',82880,2369153),('Europe',244820,59511464),('North America',9629091,275562673),('South America',176220,3334074),('Commonwealth of Independent States',447400,24755519),('Oceania',14760,189618),('South America, Central America and the Caribbean',912050,23542649),('Southeast Asia',329560,78773873),('Central America and the Caribbean',352,120917),('Oceania',6,0),('Oceania',274,15283),('Middle East',5860,2020298),('Africa',266000,244943),('Middle East',527970,17479206),('Africa',752614,9582418),('Africa',390580,11342521);
 
 
 
select count(*)
 
from t1 c
 
where population/area = (select max(population/area) from t1 cc where area<150000 and c.region = cc.region) and area<150000;
 
 
 
select count(*)
 
from t1 c
 
where area<150000 and population/area = (select max(population/area) from t1 cc where area<150000 and c.region = cc.region);

10.3

 
MariaDB [test]> select count(*)
 
    -> from t1 c
 
    -> where population/area = (select max(population/area) from t1 cc where area<150000 and c.region = cc.region) and area<150000;
 
+----------+
 
| count(*) |
 
+----------+
 
|        7 |
 
+----------+
 
1 row in set (0.004 sec)
 
 
 
MariaDB [test]> select count(*)
 
    -> from t1 c
 
    -> where area<150000 and population/area = (select max(population/area) from t1 cc where area<150000 and c.region = cc.region);
 
+----------+
 
| count(*) |
 
+----------+
 
|        8 |
 
+----------+
 
1 row in set (0.003 sec)
 
 
 
MariaDB [test]> analyze 
    -> select count(*)
 
    -> from t1 c
 
    -> where population/area = (select max(population/area) from t1 cc where area<150000 and c.region = cc.region) and area<150000;
 
+------+--------------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
 
| id   | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra       |
 
+------+--------------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
 
|    1 | PRIMARY            | c     | ALL  | NULL          | NULL | NULL    | NULL |  257 | 257.00 |   100.00 |       2.72 | Using where |
 
|    2 | DEPENDENT SUBQUERY | cc    | ALL  | NULL          | NULL | NULL    | NULL |  257 | 257.00 |   100.00 |       4.25 | Using where |
 
+------+--------------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
 
2 rows in set (0.006 sec)
 
 
 
MariaDB [test]> analyze
 
    -> select count(*)
 
    -> from t1 c
 
    -> where area<150000 and population/area = (select max(population/area) from t1 cc where area<150000 and c.region = cc.region);
 
+------+--------------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
 
| id   | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra       |
 
+------+--------------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
 
|    1 | PRIMARY            | c     | ALL  | NULL          | NULL | NULL    | NULL |  257 | 257.00 |   100.00 |       3.11 | Using where |
 
|    2 | DEPENDENT SUBQUERY | cc    | ALL  | NULL          | NULL | NULL    | NULL |  257 | 257.00 |   100.00 |       4.56 | Using where |
 
+------+--------------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
 
2 rows in set (0.002 sec)

Mysql 8.0.15 /Postgres 10.0:

mysql> select count(*)
 
    -> from t1 c
 
    -> where population/area = (select max(population/area) from t1 cc where area<150000 and c.region = cc.region) and area<150000;
 
+----------+
 
| count(*) |
 
+----------+
 
|       17 |
 
+----------+
 
1 row in set (0.05 sec)
 
 
 
mysql> select count(*)
 
    -> from t1 c
 
    -> where area<150000 and population/area = (select max(population/area) from t1 cc where area<150000 and c.region = cc.region);
 
+----------+
 
| count(*) |
 
+----------+
 
|       17 |
 
+----------+
 
1 row in set (0.03 sec)

Comment by Varun Gupta (Inactive) [ 2020-07-17 ]

Reduced test case:

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);
 
 
 
SELECT * FROM t1 A
 
WHERE population/area = (SELECT MAX(population/area) from t1 B where A.region = B.region);


MariaDB [test]> select version();
 
+---------------------------+
 
| version()                 |
 
+---------------------------+
 
| 10.2.33-MariaDB-debug-log |
 
+---------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> SELECT * FROM t1 A WHERE population/area = (SELECT MAX(population/area) from t1 B where A.region = B.region);
 
Empty set (0.01 sec)

A workaround to this problem could be to turn off subquery_cache

MariaDB [test]> set optimizer_switch='subquery_cache=off';
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> 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 |
 
+-----------------------------------+------+------------+
 
1 row in set (0.00 sec)

Comment by Varun Gupta (Inactive) [ 2020-07-20 ]

Patch
http://lists.askmonty.org/pipermail/commits/2020-July/014292.html

Comment by Alexander Barkov [ 2020-07-21 ]

The patch http://lists.askmonty.org/pipermail/commits/2020-July/014292.html is OK to push. Thanks.

Generated at Thu Feb 08 08:50:02 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.