MySQL Bugs: #37754: BIT_AND(NULL) returns wrong

Bug #37754	BIT_AND(NULL) returns wrong
Submitted:	1 Jul 2008 3:45	Modified:	3 Jul 2008 11:13
Reporter:	Sheeri Cabral (Candidate Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S4 (Feature request)
Version:	5.0, 5.1 and 6.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
SELECT BIT_AND(NULL) returns 18446744073709551615 -- this is (2^64)-1

This also holds true for SELECT BIT_AND(expr) FROM tbl.... when no rows match.

How to repeat:
select bit_and(NULL);
+----------------------+
| bit_and(NULL)        |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

create table foo (id int unsigned);

select bit_and(id) from foo;
+----------------------+
| bit_and(NULL)        |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

Tested on:
Linux, version 5.0.18-standard-log
Windows, 5.0.45-community-nt-log, 5.1.22-rc-community, 6.0.4-alpha-community
Solaris, version 5.0.22-log

Suggested fix:
Since bit_or(NULL) and bit_xor(NULL) return NULL, I believe behavior should be consistent.

bit_and(NULL) should be NULL, not (2^64)-1

I get the same result for BIT_AND(NULL) on recent 5.0, 5.1, 6.0 builds from bzr.

In addition, I get 0 for BIT_OR(NULL) and BIT_XOR(NULL) in all 3 versions.

Shouldn't NULL be the result in all these cases?

Forgot to mention I tested on 64-bit Linux only.

Thank you for a bug report. Verified also on 5.0.62, 5.1.25 and 6.0.5 on Windows.

Please when working on this bug consider fixing the behaviour with all of the BIT_* functions. Thanks!

Jon,

I tested the other BIT_* functions and could not find wrong behavior in the other BIT_* functions.  If you have a specific case, please either add it to this bug or create a new one, as I could not verify a problem with anything other than BIT_AND.

My apologies:
bit_or(null)
bit_xor(null)
select bit_xor(id) from foo;
and
select bit_xor(id) from foo;

all return 0, not NULL....but should return NULL.

As it happens, these results are documented as being correct - see http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html. 

Therefore, this is expected behaviour, and not a bug.

Actually, just because it's documented doesn't mean it's not a bug.  There's no mathematical reason a bitwise AND of NULL  should return "the value of an unsigned BIGINT value with all bits set to 1".  If anything, it should return 0 (false) or NULL.

If BIT_AND(NULL) returned the string 'popsicle' I would still consider this a bug even if the manual said that was supposed to happen.

Note the following results using the regular bitwise operators (not the aggregate functions).  I insist that the bitwise aggregation functions should work the same as the regular bitwise operators; anything else is a bug.

mysql> select NULL & NULL;
+-------------+
| NULL & NULL |
+-------------+
|        NULL | 
+-------------+
1 row in set (0.00 sec)

mysql> select NULL | NULL;
+-------------+
| NULL | NULL |
+-------------+
|        NULL | 
+-------------+
1 row in set (0.00 sec)

mysql> select NULL ^ NULL;
+-------------+
| NULL ^ NULL |
+-------------+
|        NULL | 
+-------------+
1 row in set (0.00 sec)

I tend to agree that this is a bug, even if all is implemented as intended and documented. Among other things, I'd say BIT_AND is inconsistent with BIT_OR and BIT_XOR.

Surely the behaviour displayed by the server was long ago implemented and documented as it is for some very good reason?

I see no alternative but to this allow issue's status to remain "Not A Bug" unless and until one of our Software Architects reviews it and says otherwise. No MySQL developer can be expected to make what amounts to a unilateral, arbitrary and major change in a feature that has worked and been documented as working exactly as it does now in many, many release versions of the software for span that can only be measured in years, and which countless MySQL Server users no doubt depend on for this very reason.

I have contacted one of our Architects and requested his review of this issue, but until that's been done and our Architect rules otherwise, this cannot by any definition of the term "bug" be considered one. As I said, surely this was done for a very good reason - even though I (much to my regret) must confess at this juncture to possessing insufficient wisdom to divine it myself - and I feel certain that an Architectural Review will bring this reason to light.

I hope an Architectural Review will bring a satisfactory answer as well; in which case this becomes a documentation issue.  :)

I feel strongly that BIT_AND(NULL) and NULL & NULL should work the same, and cannot fathom a mathematical reason that they should be different, nor that they should be anything other than 0 (false) or NULL.

I have been informed that the documented behaviour is in fact intentional and is the result of a long debate which took place in 2003.

Therefore, while I'm not hopeful of seeing this behaviour change anytime soon, I've changed the status/category of this bug to Open/Feature Request.

Also possibly of interest: 

Bug #1790
Bug #1788