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

ROUND(AVG()) produces different results via window function path vs scalar subquery path

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Critical
    • Resolution: Unresolved
    • 12.2.2
    • None
    • None
    • MariaDB 12.2.2-MariaDB-ubu2404 (Docker mariadb:latest), x86_64, Ubuntu 22.04 host, Linux 6.5.13-5-pve

    Description

      When computing ROUND(AVG(v), 6) over the same partition of data, the window
      function code path and the scalar subquery code path return different values
      in MariaDB 12.2.2.

      The issue occurs when AVG produces a non-terminating decimal (e.g. -49/13 =
      -3.769230769...). The window function path correctly rounds to -3.769231,
      but the scalar subquery path incorrectly rounds to -3.769200 — appearing
      to lose precision before the ROUND is applied.

      == Minimal Reproducer ==

      CREATE TABLE t (id INT PRIMARY KEY, p INT, v INT);
      INSERT INTO t VALUES 
        (1,1,-99),(2,1,-93),(3,1,43),(4,1,-30),(5,1,46),
        (6,1,-23),(7,1,87),(8,1,-55),(9,1,-1),(10,1,8),
        (11,1,30),(12,1,32),(13,1,6),
        (14,2,-29),(15,2,-35),(16,2,96),(17,2,42),(18,2,17),
        (19,2,-46),(20,2,88),(21,2,41),(22,2,-33),(23,2,77),(24,2,1);
       
      -- Should return Empty set (both AVG computations are identical)
      SELECT id, p, wf_avg, scalar_avg
      FROM (
        SELECT id, p, v,
          ROUND(AVG(v) OVER (PARTITION BY p), 6) AS wf_avg,
          ROUND((SELECT AVG(t2.v) FROM t t2 
                 WHERE t2.p <=> t.p), 6)         AS scalar_avg
        FROM t
      ) sub
      WHERE wf_avg <> scalar_avg;
      

      == Expected Result ==

      Empty set — both wf_avg and scalar_avg compute AVG(v) over the same rows
      and apply the same ROUND, so they must be equal.

      == Actual Result (MariaDB 12.2.2) ==

      Returns 22 rows. Examples:

      +------+------+-----------+------------+
      | id   | p    | wf_avg    | scalar_avg |
      +------+------+-----------+------------+
      |    1 |    1 | -3.769231 |  -3.769200 |
      |   14 |    2 | 19.909091 |  19.909100 |
      +------+------+-----------+------------+
      

      • Partition 1 (13 rows, sum=-49): AVG = -49/13 = -3.769230769...
      • Window function: ROUND → -3.769231 ✓
      • Scalar subquery: ROUND → -3.769200 ✗ (precision lost)
      • Partition 2 (11 rows, sum=219): AVG = 219/11 = 19.909090909...
      • Window function: ROUND → 19.909091 ✓
      • Scalar subquery: ROUND → 19.909100 ✗ (precision lost)

      Attachments

        Activity

          People

            Unassigned Unassigned
            imchifan 刘启帆
            Votes:
            0 Vote for this issue
            Watchers:
            1 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.