|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (id INT, a BIGINT);
|
INSERT INTO t1 VALUES (1,0x7FFFFFFFFFFFFFFF),(2,0x7FFFFFFFFFFFFFFF);
|
SELECT id, AVG(a) AS avg, CAST(MIN(a) AS SIGNED) AS cast_min FROM t1 GROUP BY id HAVING avg!=123 ORDER BY id;
|
returns
+------+--------------------------+---------------------+
|
| id | avg | cast_min |
|
+------+--------------------------+---------------------+
|
| 1 | 9223372036854775807.0000 | 9223372036854775807 |
|
| 2 | 9223372036854775807.0000 | 9223372036854775807 |
|
+------+--------------------------+---------------------+
|
So far so good. Now if I change MIN to AVG:
SELECT id, AVG(a) AS avg, CAST(AVG(a) AS SIGNED) AS cast_avg FROM t1 GROUP BY id HAVING avg!=123 ORDER BY id;
|
it erroneously returns negative numbers:
+------+--------------------------+----------------------+
|
| id | avg | cast_avg |
|
+------+--------------------------+----------------------+
|
| 1 | 9223372036854775807.0000 | -9223372036854775808 |
|
| 2 | 9223372036854775807.0000 | -9223372036854775808 |
|
+------+--------------------------+----------------------+
|
|