Details

    • Type: Bug
    • Status: Closed
    • Severity: Urgent
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 6.0.0 GA
    • Component/s: Aggregation Tables
    • Labels:
      None
    • Notice:
      When an issue is open, the "Fix Version/s" field conveys a target, not necessarily a commitment. When an issue is closed, the "Fix Version/s" field conveys the version that the issue was fixed in.
    • Sprint Team:
      Maintenance

      Description

      When aggregate tables are used and AVG is calculated among measures, it is calculated incorrectly.

      The SQL query looks like this :

      select
      sum(`id`) as `m0`,
      sum(`measure_avg`) / sum(`fact_count`) as `m1`,
      sum(`measure_sum`) as `m2`
      from
      `agg_c_101_something`

      Indeed, sum of averages divided by sum of number of facts is not equal to average.

      Possible solution: always calculate sum in aggregate tables instead of avg, using

      INSERT INTO agg_l_101_something (
      id,
      measure_avg,
      measure_sum,
      fact_count)
      SELECT
      COUNT AS `id`,
      SUM(`measure`) AS `measure_avg`, /* NOW AVG */
      SUM(`measure`) AS `measure_sum`,
      COUNT AS `fact_count`
      FROM
      etc.

      in this case the result will be correct.

      ----------------

      NOTE: created 2 new cases (MONDRIAN-2398, MONDRIAN-2399) to cover variations of bad behavior with averages in aggregate tables. We've focused this case specifically on the issue of average values from aggregate tables being mishandled when a query hits the lowest granularity of the table. NULL handling and issues with aggregation method selection are covered under the other 2 cases.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                aliaksandr Aliaksandr Bialkevich (Inactive)
                Reporter:
                dedushka_shubin dedushka_shubin
              • Votes:
                3 Vote for this issue
                Watchers:
                8 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: