Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0(EOL), 10.1(EOL)
-
None
Description
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 |
|
+------+--------------------------+----------------------+
|
Attachments
Issue Links
- relates to
-
-
- Open
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Summary | Wrong result for CAST(AVG(a) AS SIGNED) | Wrong result for CAST(AVG(bigint_column) AS SIGNED) |
| Fix Version/s | 10.1.8 [ 19605 ] | |
| Fix Version/s | 10.1 [ 16100 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 71911 ] | MariaDB v4 [ 149700 ] |