Bug #84054 too many warnings are produced
Submitted: 5 Dec 2016 8:55 Modified: 6 Oct 2020 22:56
Reporter: 帅 Bang Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version:5.6, 5.7 OS:Linux
Assigned to: CPU Architecture:Any

[5 Dec 2016 8:55] 帅 Bang 
Description:
mysql> select space('被');
+--------------+
| space('被') |
+--------------+
|              |
+--------------+
1 row in set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: '被' |
| Warning | 1292 | Truncated incorrect INTEGER value: '被' |
+---------+------+------------------------------------------+
2 rows in set (0.00 sec)

IMHO, 1 rather than 2 warnings should be prouced

How to repeat:
 select space('被');

Suggested fix:
mysql> select space('被');
+--------------+
| space('被') |
+--------------+
|              |
+--------------+
1 row in set, 1 warnings (0.00 sec)
[5 Dec 2016 15:18] MySQL Verification Team 
Thank you for the bug report.
[6 Dec 2016 8:15] Knut Anders Hatlen 
Posted by developer:
 
Also seen with some other string functions:

select sha2('abc', '?');
select right('abc', '?');
select left('abc', '?');
select substr('abc', '?', '?');
select repeat('a', '?');
select rpad('abc', '?', 'a');
select lpad('abc', '?', 'a');
[6 Oct 2020 22:56] Roy Lyseng 
Posted by developer:
 
We currently evaluate the argument once during resolving to determine metadata, as long as the argument is constant and once per row during execution. It is certainly possible to eliminate the evaluations during execution, however the solution is tedious and not considered worthwhile. It is also necessary with explicit logic per function that suffer from this problem.
This is also just a minor nuisance, and users should be encouraged to provide arguments of correct types, instead of
relying upon implicit type conversions.
Hence, we consider this problem not feasible to fix.