| 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: | |
| 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 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)
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;