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

Round function fail - query with case statements and subselect

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • None
    • 6.3.1
    • None
    • None
    • 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

            dleeyh Daniel Lee (Inactive)
            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.