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)