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

Getting wrong result in calculation using variables from derived table

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 5.5, 10.0, 10.1, 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
    • 10.4
    • None
    • 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

            bar Alexander Barkov
            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.