Bug #62843 bin() function and "Bit functions"
Submitted: 20 Oct 2011 11:25 Modified: 25 Oct 2011 12:44
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:any OS:Any
Assigned to: CPU Architecture:Any
Tags: qc
Triage: Needs Triage: D3 (Medium)

[20 Oct 2011 11:25] Peter Laursen
I find undocumented behaviour with with the bin() function and some "bit functions" (http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_bin) where an argument|operand is not and integer and also a few inconsistencies.

How to repeat:
SET SQL_MODE = 'strict_all_tables';

SELECT BIN(8); -- '1000'
SELECT BIN(8.8); -- '1000'
SHOW WARNINGS; -- Empty set
SELECT BIN('b'); -- '0'
SHOW WARNINGS; -- empty set'

SELECT 8 | 0; -- 8
SELECT 8.1 | 0; -- 8
SHOW WARNINGS; -- empty set
SELECT 8.8 | 0; -- 9
SHOW WARNINGS; -- empty set
SELECT 'a' | 1; -- 0
SHOW WARNINGS -- Truncated incorrect INTEGER value: 'a';

(same on MySQL 5.0.90 and 5.5.16)

Suggested fix:
What I find wrong is;

1) In strict mode an error and not a warning should be returned.

2) No warning or error with the bin() function using a non-integer argument (decimal number or string) though it obviously truncates.

3) No warning or error with the | operator on decimal numbers.  But warning when using string as argument.

4) Inconsistence between bin() and "bit functions" with decimal numbers where the fractional part > 0.5. "8.8" is 'rounded down' by bin() and 'rounded up' by |-operator.

I think the all are bugs and not just documentation issues. In particular when the client is a non-attended application this could cause very weird results in case the application by mistake passes a non-integer argument or operand.
[25 Oct 2011 12:44] Valeriy Kravchuk
Verified with 5.5.17 on Mac OS X. 

At least some of your points are likely valid. If not changed, they (like truncating double instead of rounding) should be clearly explained in the manual.