[MDEV-19232] Floating point precision / value comparison problem - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5.60, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.1.46, 10.2.33, 10.3.24, 10.4.14, 10.5.5
Component/s: Data Manipulation - Subquery, Platform RedHat
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

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);

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

cia.sql
16 kB
2019-04-10 11:35

Issue Links

causes

MDEV-23702 calculating(auto rounding) issue

Closed

MDEV-23762 Strange type-inference/rounding problem since >10.3.23

Closed

relates to

MDEV-23582 Unexpected result upon division of decimal values (comparing to other implementations)

Closed

Activity

Ascending order - Click to sort in descending order

Alice Sherepa added a comment - 2019-04-11 11:27

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)

Alice Sherepa added a comment - 2019-04-11 11:27 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)

Varun Gupta (Inactive) added a comment - 2020-07-17 13:01

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)

Varun Gupta (Inactive) added a comment - 2020-07-17 13:01 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)

Varun Gupta (Inactive) added a comment - 2020-07-20 10:56

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

Varun Gupta (Inactive) added a comment - 2020-07-20 10:56 Patch http://lists.askmonty.org/pipermail/commits/2020-July/014292.html

Alexander Barkov added a comment - 2020-07-21 01:35

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

Alexander Barkov added a comment - 2020-07-21 01:35 The patch http://lists.askmonty.org/pipermail/commits/2020-July/014292.html is OK to push. Thanks.

MariaDB Server

Floating point precision / value comparison problem

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration