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: Stalled (View Workflow)
    • Critical
    • Resolution: Unresolved
    • 10.11.16, 12.2.2
    • 10.11
    • None
    • MariaDB 12.2.2-MariaDB-ubu2404 (Docker mariadb:latest), x86_64, Ubuntu 22.04 host, Linux 6.5.13-5-pve
    • Q3/2026 Server Maintenance

    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

            Gosselin Dave Gosselin
            imchifan 刘启帆
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:

              Time Tracking

                Estimated:
                Original Estimate - 5.5h Original Estimate - 5.5h
                5.5h
                Remaining:
                Time Spent - 2d 6h 10m Remaining Estimate - 7h 23m
                7h 23m
                Logged:
                Time Spent - 2d 6h 10m Remaining Estimate - 7h 23m
                2d 6h 10m

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.