MySQL Bugs: #50591: bit(31) causes Duplicate entry '1-NULL' for key 'group

Bug #50591	bit(31) causes Duplicate entry '1-NULL' for key 'group_key'
Submitted:	25 Jan 2010 14:15	Modified:	17 Jun 2010 22:44
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0.89,5.1.42, 5.5.1-m2, 5.5.99-m3	OS:	Any
Assigned to:	Sergei Glukhov	CPU Architecture:	Any
Tags:	bit, group_key, regression

Description:
mysql> select sum(`a`) from `t1` group by `b`,`a`;
ERROR 1062 (23000): Duplicate entry '1-NULL' for key 'group_key'
mysql>

4.1.25 didn't return an error. all versions after that show above error.

How to repeat:
set sql_mode='';
drop table if exists `t1`;
create table `t1`(`a` bigint,`b` bit(31) not null);
insert into `t1` values (),();
select sum(`a`) from `t1` group by `b`,`a`;

Verified just as described with recent 5.1.44 from bzr:

77-52-24-143:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.44-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists `t1`;
Query OK, 0 rows affected (0.00 sec)

mysql> create table `t1`(`a` bigint,`b` bit(31) not null);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into `t1` values (),();
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select sum(`a`) from `t1` group by `b`,`a`;
ERROR 1062 (23000): Duplicate entry '1-NULL' for key 'group_key'

The only possibly related bug I had found is bug #31349. If I remove NOT NULL property of BIT column, there is no error message:

mysql> drop table if exists `t1`;
Query OK, 0 rows affected (0.00 sec)

mysql> create table `t1`(`a` bigint,`b` bit(31));
Query OK, 0 rows affected (0.37 sec)

mysql> insert into `t1` values (),();
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select sum(`a`) from `t1` group by `b`,`a`;
+----------+
| sum(`a`) |
+----------+
|     NULL |
+----------+
1 row in set (0.00 sec)

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/commits/99901

3372 Sergey Glukhov	2010-02-11
      Bug#50591 bit(31) causes Duplicate entry '1-NULL' for key 'group_key'
      The problem is that during temporary table creation uneven bits
      are not taken into account for hidden fields. It leads to incorrect
      calculation&allocation of null bytes size for table record. And
      if grouped value is null we set wrong bit for this value(see end_update()).
      Fixed by adding separate calculation of uneven bit for hidden fields.
     @ mysql-test/r/type_bit.result
        test result
     @ mysql-test/t/type_bit.test
        test case
     @ sql/sql_select.cc
        added separate calculation of uneven bit for hidden fields

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/commits/100468

3341 Sergey Glukhov	2010-02-16
      Bug#50591 bit(31) causes Duplicate entry '1-NULL' for key 'group_key'
      The problem is that during temporary table creation uneven bits
      are not taken into account for hidden fields. It leads to incorrect
      calculation&allocation of null bytes size for table record. And
      if grouped value is null we set wrong bit for this value(see end_update()).
      Fixed by adding separate calculation of uneven bit for hidden fields.
     @ mysql-test/r/type_bit.result
        test case
     @ mysql-test/t/type_bit.test
        test case
     @ sql/sql_select.cc
        added separate calculation of uneven bit for hidden fields

Pushed into 5.1.45 (revid:joro@sun.com-20100301083827-xnimmrjg6bh33o1o) (version source revid:sergey.glukhov@sun.com-20100216091349-bybuzm5ese3tqwyw) (merge vers: 5.1.45) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100302142746-u1gxdf5yk2bjrq3e) (version source revid:alik@sun.com-20100225090938-2j5ybqoau570mytu) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into 5.5.3-m2 (revid:alik@sun.com-20100302072233-t3uqgjzdukt1pyhe) (version source revid:alexey.kopytov@sun.com-20100221213311-xf5nyv391dsw9v6j) (merge vers: 5.5.2-m2) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100302072432-k8xvfkgcggkwgi94) (version source revid:alik@sun.com-20100224135227-rcqs9pe9b2in80pf) (pib:16)

Noted in 5.1.45, 5.5.3, 6.0.14 changelogs.

Incorrect handling of BIT columns in temporary tables could lead to
spurious duplicate-key errors.

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609140708-52rvuyq4q500sxkq) (merge vers: 5.1.45-ndb-6.2.19) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)