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

        1. cia.sql
          16 kB
          Michael Lackner

        Issue Links

          Activity

            People

              varun Varun Gupta (Inactive)
              GrandAdmiralThrawn Michael Lackner
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.