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

Floating point precision / value comparison problem

    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

          Activity

            People

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

              Dates

              • Created:
                Updated: