Description:
`VAR_SAMP()` (and by the same mechanism, likely `VARIANCE`/`VAR_POP`/`STDDEV*`) over a bitwise expression that overflows into `BIGINT UNSIGNED` range gives **two different numeric answers**.
These answers differ by 4.33e8, which might be unexpected
How to repeat:
CREATE TABLE t (c_pk BIGINT NOT NULL, c_big BIGINT, c_txt VARCHAR(255));
INSERT INTO t VALUES (1, NULL, NULL), (2, 1, 'trailing '), (3, 42, 'o''brien'), (4, 0, NULL),
(5, 2, 'Zed'), (6, 42, 'a'), (7, -7, 'abc'), (8, 1, 'trailing ');
SELECT VAR_SAMP((c_big | -1379963)) FROM t WHERE ('.iw#8[' <= c_txt); -- 317429959884.8 (table scan)
CREATE INDEX t_idx ON t (c_txt(10));
SELECT VAR_SAMP((c_big | -1379963)) FROM t WHERE ('.iw#8[' <= c_txt); -- 317863650918.8 (index range scan)
SELECT VAR_SAMP((c_big | -1379963)) FROM t FORCE INDEX (t_idx) WHERE ('.iw#8[' <= c_txt); -- 317863650918.8
SELECT VAR_SAMP((c_big | -1379963)) FROM t IGNORE INDEX (t_idx) WHERE ('.iw#8[' <= c_txt); -- 317429959884.8
Description: `VAR_SAMP()` (and by the same mechanism, likely `VARIANCE`/`VAR_POP`/`STDDEV*`) over a bitwise expression that overflows into `BIGINT UNSIGNED` range gives **two different numeric answers**. These answers differ by 4.33e8, which might be unexpected How to repeat: CREATE TABLE t (c_pk BIGINT NOT NULL, c_big BIGINT, c_txt VARCHAR(255)); INSERT INTO t VALUES (1, NULL, NULL), (2, 1, 'trailing '), (3, 42, 'o''brien'), (4, 0, NULL), (5, 2, 'Zed'), (6, 42, 'a'), (7, -7, 'abc'), (8, 1, 'trailing '); SELECT VAR_SAMP((c_big | -1379963)) FROM t WHERE ('.iw#8[' <= c_txt); -- 317429959884.8 (table scan) CREATE INDEX t_idx ON t (c_txt(10)); SELECT VAR_SAMP((c_big | -1379963)) FROM t WHERE ('.iw#8[' <= c_txt); -- 317863650918.8 (index range scan) SELECT VAR_SAMP((c_big | -1379963)) FROM t FORCE INDEX (t_idx) WHERE ('.iw#8[' <= c_txt); -- 317863650918.8 SELECT VAR_SAMP((c_big | -1379963)) FROM t IGNORE INDEX (t_idx) WHERE ('.iw#8[' <= c_txt); -- 317429959884.8