Details
Description
Description
MariaDB Server 12.3.2 incorrectly filters all three singleton groups when `STDDEV_POP
IS NOT NULL` is used in HAVING alongside a projected SUM DISTINCT. Without HAVING, STDDEV_POP returns non-NULL 0 for every group. Adding SELECT DISTINCT also restores all groups.
A derived-table filter reproduces the failure with default condition pushdown, while disabling `condition_pushdown_for_derived` restores the correct result. The direct HAVING query already demonstrates the incorrect result independently of that transformation.
How to repeat
|
|
CREATE TEMPORARY TABLE stddev_probe (id INT,k VARCHAR(10),x DOUBLE,txt LONGTEXT); |
INSERT INTO stddev_probe VALUES
|
(1,'a',3.71,'one'),(2,'b',5.25,'two'),(3,'c',9.12,'three'); |
|
|
SELECT STDDEV_POP(x) AS sd,SUM(DISTINCT x) AS s,txt
|
FROM stddev_probe GROUP BY txt,k HAVING STDDEV_POP(x) IS NOT NULL;
|
Expected result
Three rows: `(0,3.71,'one'),(0,9.12,'three'),(0,5.25,'two')`, order unspecified. Each group has exactly one non-NULL x, whose population standard deviation is 0, not NULL.
Actual result
No rows.