MySQL Bugs: #18006: GROUP BY can corrupt BIT values when multiple bits returned

Bug #18006	GROUP BY can corrupt BIT values when multiple bits returned
Submitted:	7 Mar 2006 1:07	Modified:	7 Mar 2006 1:34
Reporter:	J Rabbit	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.16	OS:	Windows (Windows 2003)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
If you return two bit columns that can contain NULL values in a query using a GROUP BY clause, it will return seeminly random incorrect results in the first bit column. Other columns (of any data type) are returned correctly.

I have tested this on both the MYISAM and MEMORY storage engines with identical results on both.

How to repeat:
In the first test below the value of b in the first row should be 1:

mysql> create table test (a int not null, b bit null, c bit null) type=myisam;
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> insert into test values (1,1,1),(2,0,0),(3,NULL,NULL);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select a,b,c from test group by a;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |      | ☺    |
| 2 |      |      |
| 3 | NULL | NULL |
+---+------+------+
3 rows in set (0.00 sec)

=================================
Selecting the bit columns one at a time works ok:

mysql> select a,c from test group by a;
+---+------+
| a | c    |
+---+------+
| 1 | ☺    |
| 2 |      |
| 3 | NULL |
+---+------+
3 rows in set (0.00 sec)

mysql> select a,b from test group by a;
+---+------+
| a | b    |
+---+------+
| 1 | ☺    |
| 2 |      |
| 3 | NULL |
+---+------+
3 rows in set (0.00 sec)

===================

With NULLs inserted in column C, the value of B in row 2 is corrupt:

mysql> truncate table test;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values (1,1,NULL),(2,0,NULL),(3,NULL,NULL);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select a,b,c from test group by a;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 | ☺    | NULL |
| 2 | ☺    | NULL |
| 3 | NULL | NULL |
+---+------+------+
3 rows in set (0.00 sec)

=====================================

In more complex real world situations I have also seen NULL being corrupted to 1. Also the presence of additional non-bit fields in the SELECT list does not make any difference to the behaviour.

Updated title to be more accurate

Thank you for the bug report.
Please read the Manual regarding the use of BIT columns. See below
the query: select a,b+0,c+0 from test group by a;

mysql> use test
Database changed
mysql>  create table test (a int not null, b bit null, c bit null)
    -> type=myisam;
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> insert into test values (1,1,1),(2,0,0),(3,NULL,NULL);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select a,b+0,c+0 from test group by a;
+---+------+------+
| a | b+0  | c+0  |
+---+------+------+
| 1 |    1 |    1 |
| 2 |    0 |    0 |
| 3 | NULL | NULL |
+---+------+------+
3 rows in set (0.00 sec)

mysql> select a,b,c from test group by a;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |      | ☺    |
| 2 |      |      |
| 3 | NULL | NULL |
+---+------+------+
3 rows in set (0.00 sec)

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

mysql>

I have also been able to repeat this without the GROUP BY by using a combination of joins and order by:

mysql> create table test (a int not null, b bit null, c bit null) type=myisam;
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> insert into test values (1,1,1),(2,0,0),(3,NULL,NULL);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select t1.a,t1.b,t1.c from test t1 left join test t2 on t1.a = t2.a;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 | ☺    | ☺    |
| 2 |      |      |
| 3 | NULL | NULL |
+---+------+------+
3 rows in set (0.00 sec)

========================
The value of b in the first row is incorrect in the next two queries:

mysql> select t1.a,t1.b,t1.c from test t1 left join test t2 on t1.a = t2.a order by t1.a;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |      | ☺    |
| 2 |      |      |
| 3 | NULL | NULL |
+---+------+------+
3 rows in set (0.00 sec)

mysql> select t1.a,t1.b,t1.c from test t1 inner join test t2 on t1.a = t2.a order by t1.a;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |      | ☺    |
| 2 |      |      |
| 3 | NULL | NULL |
+---+------+------+
3 rows in set (0.01 sec)

Thank you for the feedback:

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.18-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table test (a int not null, b bit null, c bit null) type=myisam;
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> insert into test values (1,1,1),(2,0,0),(3,NULL,NULL);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select t1.a,t1.b+0,t1.c+0 from test t1 left join test t2 on t1.a = t2.a;
+---+--------+--------+
| a | t1.b+0 | t1.c+0 |
+---+--------+--------+
| 1 |      1 |      1 |
| 2 |      0 |      0 |
| 3 |   NULL |   NULL |
+---+--------+--------+
3 rows in set (0.03 sec)

mysql>