Bug #13044 BIT_COUNT with NULL values
Submitted: 7 Sep 2005 16:15 Modified: 11 Apr 2006 13:12
Reporter: Vitali Falileev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.24, 4.1.14 OS:Linux (Slackware Linux 10.1, Windows)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[7 Sep 2005 16:15] Vitali Falileev
Description:
It seems there is bug with BIT_COUNT() implementation.

t1 content
id
-----
 1
 2
 3
 4
 5

t2 content
id | t1_id | foo
----------------
 1 |     1 |  7
 2 |     3 |  7
 3 |     4 |  7

SELECT t1.id, t2.id, BIT_COUNT(foo) AS foobits FROM t1 LEFT JOIN t2 ON t2.t1_id = t1.id

Result should be

t1.id | t2.id | foobits
-----------------------
    1 |    1  |     3
    2 | NULL  |  NULL
    3 |    2  |     3
    4 |    3  |     3

But real result for 4.0.24 version will be

t1.id | t2.id | foobits
-----------------------
    1 |    1  |     3
    2 | NULL  |  NULL
    3 | NULL  |  NULL
    4 | NULL  |  NULL

which is wrong.

How to repeat:
CREATE TABLE t1 (
  id INT NOT NULL
);

CREATE TABLE t2 (
  id INT NOT NULL,
  t1_id INT NOT NULL,
  foo INT NOT NULL
);

INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
INSERT INTO t2 VALUES (1, 1, 7), (2, 3, 7), (3, 4, 7), (4, 5, 7);

SELECT t1.id, t2.id, BIT_COUNT(foo) AS foobits FROM t1 LEFT JOIN t2 ON t2.t1_id = t1.id;
[7 Sep 2005 22:20] Hartmut Holzgraefe
works fine for me with 4.0.24 on linux (suse 9.3):

mysql> SELECT t1.id, t2.id, BIT_COUNT(foo) AS foobits FROM t1 LEFT JOIN t2 ON t2.t1_id
    -> = t1.id;
+----+------+---------+
| id | id   | foobits |
+----+------+---------+
|  1 |    1 |       3 |
|  2 | NULL |    NULL |
|  3 |    2 |    NULL |
|  4 |    3 |    NULL |
|  5 |    4 |    NULL |
+----+------+---------+
5 rows in set (0.00 sec)

mysql> 
mysql> select version();
+-----------+
| version() |
+-----------+
| 4.0.24    |
+-----------+
1 row in set (0.00 sec)
[8 Sep 2005 9:09] Vitali Falileev
Sorry, I submit bit wrong result.

mysql> SELECT t1.id, t2.id, BIT_COUNT(foo) AS foobits FROM t1 LEFT JOIN t2 ON
t2.t1_id
    -> = t1.id;
+----+------+---------+
| id | id   | foobits |
+----+------+---------+
|  1 |    1 |       3 |
|  2 | NULL |    NULL |
|  3 |    2 |    NULL |
|  4 |    3 |    NULL |
|  5 |    4 |    NULL |
+----+------+---------+

foobits SHOULD be = 7 but NOT NULL for t1.id (3, 4, 5). Correct result should looks like this:

+----+------+---------+
| id | id   | foobits |
+----+------+---------+
|  1 |    1 |       3 |
|  2 | NULL |    NULL |
|  3 |    2 |    3 |
|  4 |    3 |    3 |
|  5 |    4 |    3 |
+----+------+---------+
[22 Sep 2005 10:00] Valeriy Kravchuk
Yes, I think it is a bug, also present in 4.1.14:

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.14-nt |
+-----------+
1 row in set (0.02 sec)

mysql> CREATE TABLE t1 (
    ->   id INT NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t2 (
    ->   id INT NOT NULL,
    ->   t1_id INT NOT NULL,
    ->   foo INT NOT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t2 VALUES (1, 1, 7), (2, 3, 7), (3, 4, 7), (4, 5, 7);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT t1.id, t2.id, BIT_COUNT(foo) AS foobits FROM t1 LEFT JOIN t2 ON t2.t1_id
    -> = t1.id;
+----+------+---------+
| id | id   | foobits |
+----+------+---------+
|  1 |    1 |       3 |
|  2 | NULL |    NULL |
|  3 |    2 |    NULL |
|  4 |    3 |    NULL |
|  5 |    4 |    NULL |
+----+------+---------+
5 rows in set (0.00 sec)

mysql> select id, foo, bit_count(foo) as foobits from t2;
+----+-----+---------+
| id | foo | foobits |
+----+-----+---------+
|  1 |   7 |       3 |
|  2 |   7 |       3 |
|  3 |   7 |       3 |
|  4 |   7 |       3 |
+----+-----+---------+
4 rows in set (0.00 sec)

So, why bit_count(7) = 3 in a simple select and is NULL in a select with join in all rows just after those that has no correspondent one?
[6 Nov 2005 8:37] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32018
[10 Apr 2006 13:31] Magnus BlÄudd
Patch looks fine to me. Since I'm the second reviewer I set it to "Patch approved"
[11 Apr 2006 6:36] Ramil Kalimullin
fixed in 4.0.27
[11 Apr 2006 13:12] Paul DuBois
Noted in 4.0.27 changelog.

<literal>BIT_COUNT()</literal> could return an incorrect value
for right table columns in a <literal>LEFT JOIN</literal>.
(Bug #13044)