stddev_samp function return NULL when its argument's number is one
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
Please answer these questions before submitting your issue. Thanks!
### 1. Minimal reproduce step (Required)
drop database test;
create database test;
use test;
CREATE TABLE t3 (c1 VARCHAR(10), c2 INT);
INSERT INTO t3 VALUES
('a', 100), -- single row
('b', 200), ('b', 200), -- two same values
('c', 300), ('c', 301), -- two different values
('d', 400), ('d', 400), ('d', 400); -- three same values
SELECT
c1,
COUNT(*) as cnt,
VARIANCE(c2) as v,
STDDEV_SAMP(c2) as s
FROM t3
GROUP BY c1;
### 2. What did you expect to see? (Required)
the second group column s's value should be 0
mysql> SELECT
-> c1,
-> COUNT(*) as cnt,
-> VARIANCE(c2) as v,
-> STDDEV_SAMP(c2) as s
-> FROM t3
-> GROUP BY c1;
+------+-----+------+--------------------+
| c1 | cnt | v | s |
+------+-----+------+--------------------+
| c | 2 | 0.25 | 0.7071067811865476 |
| a | 1 | 0 | NULL |
| b | 2 | 0 | 0 |
| d | 3 | 0 | 0 |
+------+-----+------+--------------------+
4 rows in set (0.00 sec)
### 3. What did you see instead (Required)
mysql> SELECT
-> c1,
-> COUNT(*) as cnt,
-> VARIANCE(c2) as v,
-> STDDEV_SAMP(c2) as s
-> FROM t3
-> GROUP BY c1;
+------+-----+------+--------------------+
| c1 | cnt | v | s |
+------+-----+------+--------------------+
| c | 2 | 0.25 | 0.7071067811865476 |
| a | 1 | 0 | NULL |
| b | 2 | 0 | 0 |
| d | 3 | 0 | 0 |
+------+-----+------+--------------------+
4 rows in set (0.00 sec)
### 4. What is your TiDB version? (Required)
mysql> SELECT tidb_version();
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v7.5.1
Edition: Community
Git Commit Hash: 7d16cc79e81bbf573124df3fd9351c26963f3e70
Git Branch: heads/refs/tags/v7.5.1
UTC Build Time: 2024-02-27 14:28:32
GoVersion: go1.21.6
Race Enabled: false
Check Table Before Drop: false
Store: unistore |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Contributor guide
Assessment
This issue has not been assessed yet.