Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
12.3.2, 12.2.2
-
None
-
None
-
None
-
Ubuntu 24.04
Description
A correlated scalar subquery using STDDEV_POP() returns inconsistent values across rows for the same partition when subquery_cache=on.
For the same partition p = 1, the mathematically correct result is:
57.0087712549569
MariaDB behavior observed:
STDDEV_POP(v) OVER (PARTITION BY p) returns the correct value for every row
Plain scalar aggregate SELECT STDDEV_POP(v) FROM t WHERE p = 1 also returns the correct value
But the correlated scalar subquery
(SELECT STDDEV_POP(t2.v) FROM t t2 WHERE t2.p = t1.p)
returns:
correct value on the first row
then a truncated/rounded value 57.008800000000000 on later rows in the same result set
POC:
DROP DATABASE IF EXISTS st; |
CREATE DATABASE st; |
USE st; |
|
|
CREATE TABLE t ( |
id INT PRIMARY KEY, |
p INT, |
v INT |
);
|
|
|
INSERT INTO t VALUES |
(1,1,12),(2,1,99),(3,1,-45),(4,1,7),(5,1,-88),(6,1,23),(7,1,55),
|
(8,2,1),(9,2,2),(10,2,3);
|
|
|
-- Baseline: correct scalar aggregate
|
SELECT STDDEV_POP(v) AS sd |
FROM t |
WHERE p = 1; |
-- Expected / actual correct:
|
-- 57.0087712549569 (display format may vary)
|
|
|
-- Baseline: correct window result
|
SELECT id, |
CAST(STDDEV_POP(v) OVER (PARTITION BY p) AS DECIMAL(30,15)) AS wf_sd |
FROM t |
WHERE p = 1 |
ORDER BY id; |
-- Expected: same value on every row
|
-- 57.008771254956900
|
|
|
-- Problematic query: correlated scalar subquery
|
SELECT id, |
CAST((SELECT STDDEV_POP(t2.v) |
FROM t t2 |
WHERE t2.p = t1.p) AS DECIMAL(30,15)) AS sc_sd |
FROM t AS t1 |
WHERE p = 1 |
ORDER BY id; |
Actual result on MariaDB 12.2.2:
id sc_sd
1 57.008771254956900
2 57.008800000000000
3 57.008800000000000
4 57.008800000000000
5 57.008800000000000
6 57.008800000000000
7 57.008800000000000