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

[10 Nov 2003 0:45] Arjen Lentz
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
[10 Nov 2003 1:06] Arjen Lentz
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.
[10 Nov 2003 1:07] Arjen Lentz
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).
[10 Nov 2003 1:43] Arjen Lentz
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.
[14 Nov 2003 16:41] Arjen Lentz
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.
[4 Dec 2003 15:28] Konstantin Osipov
Fixed in 4.0.17: bk commit - 4.0 tree (konstantin:1.1640)
[8 Dec 2003 4:53] Konstantin Osipov
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.
[10 Dec 2003 4:45] Konstantin Osipov
Fixed in 4.0.17: bk commit - 4.0 tree (konstantin:1.1642)