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()`).
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()`).