MySQL Bugs: #62843: bin() function and "Bit functions"

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:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	any	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
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.

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.

Posted by developer:
 
The behaviour here is according to documentation, except for point 4:

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

Strict mode applies only to data change statements, not to SELECT. This may be unfortunate, but is according to documentation.

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

Truncation of numeric data to fit a field with smaller number of decimals is not a bug. Rounding will occur.
You will see the same behaviour when attempting to insert a decimal number into an integer column.
In fact, this is according the SQL standard.

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

We give warning when a string value is implicitly converted to a numeric value, but the string cannot directly be interpreted as a number. In 8.0, this gives a warning also for the BIN() function.

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.

There is an inconsistency here, the argument to BIN() is truncated, whereas the argument to the OR operator is rounded.