MySQL Bugs: #45837: Logical AND not giving expected results as explained in Manual

Bug #45837	Logical AND not giving expected results as explained in Manual
Submitted:	29 Jun 2009 18:53	Modified:	30 Jun 2009 3:50
Reporter:	Bob Hansen	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.1.35 community	OS:	Windows (XP Pro SP2)
Assigned to:		CPU Architecture:	Any
Tags:	and, boolean, conflicts, documentation, Logic, Logical, match, not, results, wrong

Description:
Using the logical AND "&&" I'm not getting the expected results as denoted in the manual.

For example, for the values 1-8, only 4,5,6, and 7 AND'd with 4 will give you a true. The query seems to say that all values 1-8 AND'd with 4 are true.

Expected:
1 && 4 = 0001 AND 0100 = F
2 && 4 = 0010 AND 0100 = F
3 && 4 = 0011 AND 0100 = F
4 && 4 = 0100 AND 0100 = T
5 && 4 = 0101 AND 0100 = T
6 && 4 = 0110 AND 0100 = T
7 && 4 = 0111 AND 0100 = T
8 && 4 = 1000 AND 0100 = F

Actual:
1 && 4 = T
2 && 4 = T
3 && 4 = T
4 && 4 = T
5 && 4 = T
6 && 4 = T
7 && 4 = T
8 && 4 = T

How to repeat:
create table test (theValue smallint);
insert into test values (1),(2),(3),(4),(5),(6),(7),(8);

// all values
// gives correct results
select * from test;

// '&' not used in documentation
// gives correct results
select * from test where (theValue & 4);

// '&&' used in documentation
// gives INCORRECT results
select * from test where (theValue && 4);

Suggested fix:
One of two things went wrong here. Either the usage of '&&' in MySQL server is working improperly or the documentation meant to say '&' instead of '&&'. I'm guessing the former, but maybe there was a mix-up.

Documentation for Logical Operators:

http://dev.mysql.com/doc/refman/5.1/en/logical-operators.html

Sorry, but this is not a bug. That manual page you mentioned clearly says:

"Note that MySQL evaluates any nonzero or non-NULL value to TRUE."

So, 1, 2, 3 ... 8 all are interpreted as TRUE, same as 4. TRUE AND TRUE correctly gives TRUE.

What you expected from && is bitwise AND. This is done with another operator, &, as you also noted. Read http://dev.mysql.com/doc/refman/5.1/en/bit-functions.html for the details.