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

Getting wrong result in calculation using variables from derived table

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.2.22, 10.1.41, 10.4.7, 10.4
    • Fix Version/s: 10.2, 10.3, 10.4
    • Labels:
      None
    • Environment:
      eg. 10.2.22-MariaDB-log openSUSE package on SUSE Linux Enterprise Server 15 (x86_64) patchlevel 1

      Description

      Doing some calculations to get weighted percentiles I ran into following issue.
      I was able to tear it down to following expression:
      SELECT 0.1*(@num-1.0) FROM (SELECT @num:=62) AS data;
      The expected result is of course 6.1.
      But executing via the cli client:
      mysql -NBe "SELECT 0.1*(@num-1.0) FROM (SELECT @num:=62) AS data;"
      6.1000000000000005

      Doing the same on mysql 5.5.62 returns 6.10.

      Here some outputs from MariaDB from some workarounds:
      1) Preinit the variable:
      mysql -NBe "SET @num:=0; SELECT 0.1*(@num-1.0) FROM (SELECT @num:=62) AS data;"
      6.10
      2) Execute it twice in a session
      mysql -NBe "SELECT 0.1*(@num-1.0) FROM (SELECT @num:=62) AS data; SELECT 0.1*(@num-1.0) FROM (SELECT @num:=62) AS data;"
      6.1000000000000005
      6.10
      3) Convert @num to integer
      mysql -NBe "SELECT 0.1*(@num DIV 1-1.0) FROM (SELECT @num:=62) AS data;"
      6.10

      btw.

      If you do not use a variable mariadb returns the expected result:
      mysql -NBe "SELECT 0.1*(num-1.0) FROM (SELECT 62 AS num) AS data;"
      6.10

        Attachments

          Activity

            People

            Assignee:
            bar Alexander Barkov
            Reporter:
            Whoopsadaisy Alexander Griehl
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

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