MySQL Bugs: #121070: VAR_SAMP (and VAR_POP / STDDEV_SAMP) over BIGINT UNSIGNED values near 2^64 is insertion-order dependent

Bug #121070	VAR_SAMP (and VAR_POP / STDDEV_SAMP) over BIGINT UNSIGNED values near 2^64 is insertion-order dependent
Submitted:	11 Aug 0:30	Modified:	13 Aug 7:11
Reporter:	Junwen An	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.46, 8.4.11, 9.7.2	OS:	Linux
Assigned to:		CPU Architecture:	ARM

View
Add Comment
Files
Developer
Edit Submission
View Progress Log
Contributions

Description:
I noticed that VAR_SAMP (and VAR_POP / STDDEV_SAMP) functions over BIGINT UNSIGNED values near 2^64 is insertion-order dependent, making the results non-deterministic. This might be unexpected. It seems MariaDB has this issue as well.

How to repeat:
CREATE TABLE t (sh BIGINT UNSIGNED);
INSERT INTO t VALUES (18446744073709551588), (18446744073709549824);
SELECT VAR_SAMP(sh) FROM t;   -- 4194304.0
 
CREATE TABLE t (sh BIGINT UNSIGNED);
INSERT INTO t VALUES (18446744073709549824), (18446744073709551588);
SELECT VAR_SAMP(sh) FROM t;   -- 0.0

Hi Junwen An,

Thanks for the test case. Verified as described.