MySQL Bugs: #120465: AVG(ASCII()) result truncated to 99.9999 with SQL_BUFFER_RESULT due to incorrect decimal precision

Bug #120465	AVG(ASCII()) result truncated to 99.9999 with SQL_BUFFER_RESULT due to incorrect decimal precision
Submitted:	13 May 8:31	Modified:	13 Aug 16:54
Reporter:	Kliar Ma	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.46	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	ASCII, decimal, FUNCTION

Description:
When using `SQL_BUFFER_RESULT` with `AVG(ASCII(column))`, the result is truncated to a maximum of 99.9999 instead of correctly returning values >= 100.

The root cause is that the `ASCII()` function returns values in the range 0-127 (requiring 3 decimal digits), but it incorrectly declares a result decimal precision of 2 digits. Since `ASCII()` does not set `unsigned_flag`, the precision calculation reserves one digit for a sign that will never be used, effectively reducing the usable integer portion from 3 digits to 2.

When `SQL_BUFFER_RESULT` forces materialization into a temporary table, the `AVG()` result column is created as `DECIMAL(6,4)` (2 integer digits + 4 fractional), capping the maximum at 99.9999. The correct type should be `DECIMAL(7,4)` (3 integer digits + 4 fractional).

This issue likely affects other functions that always return non-negative values but do not set `unsigned_flag`, such as `ORD()`, `LENGTH()`, `CHAR_LENGTH()`, `BIT_LENGTH()`, `INSTR()`, etc.

How to repeat:
```sql
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
) ENGINE=InnoDB;

INSERT INTO users (id, username) VALUES
(1, 'alice'), (2, 'bob'), (3, 'charlie'), (4, 'diana'),
(5, 'edward'), (6, 'fiona'), (7, 'george'), (8, 'hannah'),
(9, 'ivan'), (10, 'julia'), (11, 'kevin'), (12, 'lisa'),
(13, 'mike'), (14, 'nancy'), (15, 'oscar');

SELECT SQL_BUFFER_RESULT id, AVG(ASCII(username)) AS avg_val
FROM users GROUP BY id;
```

Expected: avg_val for id=4 ('diana') should be 100.0000, id=5 ('edward') should be 101.0000, etc.

Actual: All values >= 100 are truncated to 99.9999.

```
 SELECT SQL_BUFFER_RESULT   id, AVG(ASCII(username)) as avg_val FROM users GROUP BY id;
+----+---------+
| id | avg_val |
+----+---------+
|  1 | 97.0000 |
|  2 | 98.0000 |
|  3 | 99.0000 |
|  4 | 99.9999 |
|  5 | 99.9999 |
|  6 | 99.9999 |
|  7 | 99.9999 |
|  8 | 99.9999 |
|  9 | 99.9999 |
| 10 | 99.9999 |
| 11 | 99.9999 |
| 12 | 99.9999 |
| 13 | 99.9999 |
| 14 | 99.9999 |
| 15 | 99.9999 |
+----+---------+
```

Suggested fix:
Either:

1. Set `unsigned_flag` on the return type of `ASCII()` (and similar inherently non-negative functions) so that the precision calculation does not wastefully reserve a digit for a sign, or

2. Correct the `max_length` (decimal digit count) of `ASCII()` to 4 to properly account for its full return range (0-127).

Option 1 is preferred as it would also fix other functions with the same class of issue (e.g. `ORD()`, `LENGTH()`, `CHAR_LENGTH()`, `BIT_LENGTH()`, `INSTR()`).

Decimal precision calculated by other functions such as `field`, `length`, `char_length`, `FIND_IN_SET`, `UNCOMPRESSED_LENGTH` and so on is inaccurate too.

Thank you for the bug report.
Verified as described.

Posted by developer: Bug status updated to 'Patch pending'

Posted by developer: Bug status updated to 'Documenting'

Posted by developer:
 
Added the following note to the MySQL Server 9.7.2 and 26.7.0 release notes:

	AVG() of functions such as ASCII() could return a truncated
        value when used with SQL_BUFFER_RESULT, because the calculated
        decimal precision did not reserve space for a sign.

        As of this release, decimal precision calculations correctly
        account for signed values, preventing truncation of valid
        results such as 255.0000.

I see this was addressed in 9.7.2 and 26.7.0, but I do not find the in 8.4.11 release notes.  Will this be done in 8.4, and if not, can we have an explanation why not ?

For reference, the commit fixing this below.

https://github.com/mysql/mysql-server/commit/023cef634905f81bd6a8fa29c211d7a2a4542f60