Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-4940

Round function fail - query with case statements and subselect

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 6.3.1
    • Component/s: None
    • Labels:
      None
    • Sprint:
      2021-16, 2021-17

      Description

      Summary
      There appears to be a bug with ROUND() on doubles in case statements in conjunction with subqueries. The workaround is to use round(, 3) - or anything but round(,2). Given recent full decimal support there might be a bug or default value that's overriding the round( ,2).

      Simplified Reproduction Steps:

      create table rounding_table ( a int, b double, c double) engine=columnstore;
      insert into rounding_table values (26805, 1252, -9647);
      insert into rounding_table values (26806, 573, -2804.5);
       
      SELECT
          CASE a
          WHEN 26805 THEN ROUND(c/b, 2)
          WHEN 26806 THEN b
      END wrongNumberOfDecimals,
      ROUND(c/b, 2) as correctNumberOfDecimals
      FROM (
          SELECT a, SUM(b) b, SUM(c) c FROM rounding_table
          GROUP BY a
      ) abc ;
      

      Results:

      +-----------------------+-------------------------+
      | wrongNumberOfDecimals | correctNumberOfDecimals |
      +-----------------------+-------------------------+
      |                   573                  |                   -4.89             |
      |    -7.709999999999999 |                   -7.71              |
      +-----------------------+-------------------------+
      

      Expected: -7.71

      Actual: -7.709999999999999

        Attachments

          Activity

            People

            Assignee:
            dleeyh Daniel Lee
            Reporter:
            allen.herrera Allen Herrera
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.