MySQL Bugs: #1790: BIT_AND() result in GROUP BY different when SQL_BIG

Bug #1790	BIT_AND() result in GROUP BY different when SQL_BIG_RESULT used
Submitted:	10 Nov 2003 0:45	Modified:	10 Dec 2003 4:45
Reporter:	Arjen Lentz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	3.23,4.0,4.1	OS:	Any (any)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
BIT_AND() produces different result in GROUP BY when SQL_BIG_RESULT option is used. This is actually part of the test suite, so someone committed incorrect results: clearly the results should be the same regardless of whether SQL_BIG_RESULT is used or not! See mysql-test/r/func_group.result

The same problem exists in 4.1, it's possible that it also exists in 3.23.

How to repeat:
-- The three tests below are simplified versions of what's in t/func_group.test
-- You will notice that the results differ if SQL_BIG_RESULT is specified.
-- That of course should never happen!

CREATE TABLE t1 (a int, b int);
insert into t1 values (1,null);
insert into t1 values (1,null);
insert into t1 values (2,null);

select a,bit_and(b) from t1 group by a;
select SQL_BIG_RESULT a,bit_and(b) from t1 group by a;

insert into t1 values (2,1);
select a,bit_and(b) group by a;
select SQL_BIG_RESULT a,bit_and(b) group by a;

insert into t1 values (3,1);
select a,bit_and(b) group by a;
select SQL_BIG_RESULT a,bit_and(b) group by a;

Suggested fix:
Fix BIG_AND() in all appropriate versions, and update func_group.results

The original changeset was 1.1430 in the 4.0 tree where Monty fixed a bug in SUM() with NULLs and added these tests/results.
It is of course possible that the bug existed already before this fix.

I think the SQL_BIG_RESULT results are correct (for an empty set/group, it is documented that BIT_AND() returns -1), which means the "normal" results are wrong (the server is returning 0 instead of -1).

Version 3.23 exhibits the same behaviour as 4.0, the result differs if SQL_BIG_RESULT is used.
So that monty's changeset did not cause this bug, but simply made it visible.

It is possible that this bug is only triggered in aggregate functions that don't rest to 0. BIT_AND() resets to all 1s.
I stumbled on all this while adding another aggregate function, one that happens to reset to 1. And it exhibits the same inconsistent behaviour - Sanja reviewed the new code and it looks ok. So that might indicate that it's old code, one of the parent classes. My new class derived from Item_add_num (like SUM() and not from the bit-aggregate class) though, so it might be a parent higher up.

Fixed in 4.0.17: bk commit - 4.0 tree (konstantin:1.1640)

though the fix itself is correct, not all cases are fixes.
A sample case would be:>> DROP TABLE IF EXISTS t;

>> CREATE TABLE t (i INT);
>> INSERT INTO t SET i = NULL;
>> SELECT BIT_AND(i) FROM t;
>> SELECT BIT_AND(i) FROM t WHERE i IS NULL;

How to fix:
Something is rotten in Item::send() that unsigned long long variable is sent as signed to user.

Fixed in 4.0.17: bk commit - 4.0 tree (konstantin:1.1642)