Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-19232

Floating point precision / value comparison problem

    XMLWordPrintable

    Details

      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

          Issue Links

            Activity

              People

              Assignee:
              varun Varun Gupta
              Reporter:
              GrandAdmiralThrawn Michael Lackner
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: