MySQL Bugs: #56570: Incorrect behaviour when combining bitfields (less than 8 bits) and group

Bug #56570	Incorrect behaviour when combining bitfields (less than 8 bits) and group_concat
Submitted:	5 Sep 2010 15:03	Modified:	9 Nov 2010 21:17
Reporter:	Thomas van Gulick	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.1.36,5.1.49,5.1.50	OS:	Linux (debian squeeze)
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any
Tags:	bitfield group_concat

Description:
It seems combining bitfields with grouping and group_concat (sum or avg work fine), wrong results are returned.

How to repeat:
create table bittest1 (ID int unsigned not null default 0 primary key,TEST bit(1) not null default 0);
create table bittest2 (ID int unsigned not null default 0 primary key);
insert into bittest2 values (1),(2),(3);
insert into bittest1 values (1,1),(2,0);

select ID,TEST,group_concat(ID) from bittest1 join bittest2 using (id) group by id;
returns:

+----+------+------------------+
| ID | TEST | group_concat(ID) |
+----+------+------------------+
|  1 |      | 1                |
|  2 |      | 2                |
+----+------+------------------+

Two times b'0' (NULL), where b'1' and b'0' are expected.

insert into bittest1 values (3,1);
select ID,TEST,group_concat(ID) from bittest1 join bittest2 using (id) group by id;

returns:

+----+------+------------------+
| ID | TEST | group_concat(ID) |
+----+------+------------------+
|  1 |      | 1                |
|  2 |     | 2                |
|  3 |     | 3                |
+----+------+------------------+

b'0' followed by to times b'1' where b'1',b'0',b'1' is expected.

Same behaviour happens using other clients (php mysqli) so it is not bound to the 'mysql' client app, though I'm not sure whether the problem is in the client interface code or somewhere within the mysql daemon.

Suggested fix:
Internally I have no idea.

One fix is to convert the bitfield to an integer.

Another maybe a bit dirtier fix is to cast the bitfield to an integer while querying, like so:

select ID,0+TEST,group_concat(ID) from bittest1 join bittest2 using (id) group by id;

which gives the expected:

+----+--------+------------------+
| ID | 0+TEST | group_concat(ID) |
+----+--------+------------------+
|  1 |      1 | 1                |
|  2 |      0 | 2                |
|  3 |      1 | 3                |
+----+--------+------------------+

Sorry, but this is NOT repeatable for me with current version, 5.1.50:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.50-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> create table bittest1 (ID int unsigned not null default 0 primary key,TES
T bit(1) not
    -> null default 0);
Query OK, 0 rows affected (0.22 sec)

mysql> create table bittest2 (ID int unsigned not null default 0 primary key);
Query OK, 0 rows affected (0.09 sec)

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

mysql> insert into bittest1 values (1,1),(2,0);
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> select ID,TEST,group_concat(ID) from bittest1 join bittest2 using (id) gr
oup by id;
+----+------+------------------+
| ID | TEST | group_concat(ID) |
+----+------+------------------+
|  1 | ☺    | 1                |
|  2 |      | 2                |
+----+------+------------------+
2 rows in set (0.06 sec)

mysql> select ID,hex(TEST),group_concat(ID) from bittest1 join bittest2 using (i
d) group by id;
+----+-----------+------------------+
| ID | hex(TEST) | group_concat(ID) |
+----+-----------+------------------+
|  1 | 1         | 1                |
|  2 | 0         | 2                |
+----+-----------+------------------+
2 rows in set (0.08 sec)

mysql> insert into bittest1 values (3,1);
Query OK, 1 row affected (0.03 sec)

mysql> select ID,TEST,group_concat(ID) from bittest1 join bittest2 using (id) gr
oup by id;
+----+------+------------------+
| ID | TEST | group_concat(ID) |
+----+------+------------------+
|  1 | ☺    | 1                |
|  2 |      | 2                |
|  3 | ☺    | 3                |
+----+------+------------------+
3 rows in set (0.00 sec)

mysql> select ID,hex(TEST),group_concat(ID) from bittest1 join bittest2 using (i
d) group by id;
+----+-----------+------------------+
| ID | hex(TEST) | group_concat(ID) |
+----+-----------+------------------+
|  1 | 1         | 1                |
|  2 | 0         | 2                |
|  3 | 1         | 3                |
+----+-----------+------------------+
3 rows in set (0.02 sec)

Am I missing something?

No you're not missing something as far as I can see.

Meanwhile, I've tested in with 5.1.36 compiled by MySQL with icc 64bit and it als returns the same erroneous results.

I've also downloaded 5.1.50 for 32bit and checked there, it also gives the wrong results.

All or on linux though. Anything else we can check? If time allows I could prep a Windows machine and test there.

Now, 5.1.50 on Ubuntu:

openxs@ubuntu:/home2/openxs/dbs/5.1$ bin/mysql --no-defaults -uroot --socket=/tmp/next-mr.sock 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 3
Server version: 5.1.50-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> create table bittest1 (ID int unsigned not null default 0 primary key,TEST bit(1) not
    -> null default 0);
Query OK, 0 rows affected (0.00 sec)

mysql> create table bittest2 (ID int unsigned not null default 0 primary key);
Query OK, 0 rows affected (0.00 sec)

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

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

mysql> select ID,TEST,group_concat(ID) from bittest1 join bittest2 using (id) group by id;
+----+------+------------------+
| ID | TEST | group_concat(ID) |
+----+------+------------------+
|  1 |      | 1                |
|  2 |      | 2                |
+----+------+------------------+
2 rows in set (0.00 sec)

mysql> select ID,hex(TEST),group_concat(ID) from bittest1 join bittest2 using (id) group by id;
+----+-----------+------------------+
| ID | hex(TEST) | group_concat(ID) |
+----+-----------+------------------+
|  1 | 1         | 1                |
|  2 | 0         | 2                |
+----+-----------+------------------+
2 rows in set (0.00 sec)

mysql> insert into bittest1 values (3,1);
Query OK, 1 row affected (0.00 sec)

mysql> select ID,TEST,group_concat(ID) from bittest1 join bittest2 using (id) group by id;
+----+------+------------------+
| ID | TEST | group_concat(ID) |
+----+------+------------------+
|  1 |      | 1                |
|  2 |     | 2                |
|  3 |     | 3                |
+----+------+------------------+
3 rows in set (0.01 sec)

mysql> select ID,hex(TEST),group_concat(ID) from bittest1 join bittest2 using (id) group by id;
+----+-----------+------------------+
| ID | hex(TEST) | group_concat(ID) |
+----+-----------+------------------+
|  1 | 1         | 1                |
|  2 | 0         | 2                |
|  3 | 1         | 3                |
+----+-----------+------------------+
3 rows in set (0.00 sec)

Check with hex(TEXT) as I did, please.

Yes that works, just like casting to int works.

But you have just shown you replicated the exact same erroneous result when not casting or using hex().

Seems to be a problem with the linux versions somehow.

If you mean this result:

mysql> select ID,TEST,group_concat(ID) from bittest1 join bittest2 using (id) group by
id;
+----+------+------------------+
| ID | TEST | group_concat(ID) |
+----+------+------------------+
|  1 |      | 1                |
|  2 |     | 2                |
|  3 |     | 3                |
+----+------+------------------+
3 rows in set (0.01 sec)

then yes, this is what I've got on Linux. Looks wrong.

I've noticed it doesn't happen for bitfields of 8 or more bits.

I've used the ord('a') to fill the bitfield. It fits within 7 bits, so no problem there.

create table bittest1 (ID int unsigned not null default 0 primary key,TEST bit(7) not
null default 0);
create table bittest2 (ID int unsigned not null default 0 primary key);
insert into bittest2 values (1),(2),(3);
insert into bittest1 values (1,ord('a')),(2,0);

select ID,TEST,group_concat(ID) from bittest1 join bittest2 using (id) group by id;
+----+------+------------------+
| ID | TEST | group_concat(ID) |
+----+------+------------------+
|  1 | a    | 1                |
|  2 |      | 2                |
+----+------+------------------+

alter table bittest1 modify TEST bit(8) not null default 0;

select ID,TEST,group_concat(ID) from bittest1 join bittest2 using (id) group by id;

+----+------+------------------+
| ID | TEST | group_concat(ID) |
+----+------+------------------+
|  1 | a    | 1                |
|  2 |      | 2                |
+----+------+------------------+

select ID,TEST,group_concat(ID) from bittest1 join bittest2 using (id) group by id;
+----+------+------------------+
| ID | TEST | group_concat(ID) |
+----+------+------------------+
|  1 |  a   | 1                |
|  2 |      | 2                |
+----+------+------------------+

Oops, I've noticed my previous comment shows the wrong results for the first select operation, so for correctness I'll repost with the right result.

create table bittest1 (ID int unsigned not null default 0 primary key,TEST bit(7) not
null default 0);
create table bittest2 (ID int unsigned not null default 0 primary key);
insert into bittest2 values (1),(2),(3);
insert into bittest1 values (1,ord('a')),(2,0);
select ID,TEST,group_concat(ID) from bittest1 join bittest2 using (id) group by id;
+----+------+------------------+
| ID | TEST | group_concat(ID) |
+----+------+------------------+
|  1 |      | 1                |
|  2 |      | 2                |
+----+------+------------------+
select ID,TEST,group_concat(ID) from bittest1 join bittest2 using (id) group by id;
+----+------+------------------+
| ID | TEST | group_concat(ID) |
+----+------+------------------+
|  1 | a    | 1                |
|  2 |      | 2                |
+----+------+------------------+

Alexander, I think it's not a bug.
Could you please take a look?

A smaller test demonstrating the same behaviour.

drop table if exists t1;
create table t1 (a bit(1));
insert into t1 values (0),(1);
select a, cast(a as unsigned) from t1; 

+------+---------------------+
| a    | cast(a as unsigned) |
+------+---------------------+
|      |                   0 | 
|     |                   1 | 
+------+---------------------+
2 rows in set (0.00 sec)

The problem is not really with GROUP_CONCAT,
the question is how BIT type should work in string context.

http://dev.mysql.com/doc/refman/5.5/en/bit-field-values.html

"Bit values are returned as binary values. To display them in printable form, add 0 or use a conversion function such as BIN()".

Might not be a real bug then, for the record and for people encountering the same problem and search for a solution, I would like to note that at least PHP mysqli module somehow fetches all fields as strings.

Presumably other interfaces might do that too, at least the 'mysql' command line client does so too, which results in really strange behaviour in returning empty strings in 'mysql' client and 0 using PHP mysqli.

Also note that the example by Alexander Barkov does NOT display the problem! It actually show a logical b0 an b1 an 0 and 1 for the select, the problem is that a non zero bitfields get translated to 0 or empty string on joins!

I still thing its incorrect behaviour and thus a bug, but that's not up to me.

Thanks for your time anyway :)

Another recap because my last comment was not as clear as I had hoped.

The problem IS group_concat and not the bitfield, as leaving out the group_concat does not result in strange behaviour!

First create the tables:

create table bittest1 (ID int unsigned not null default 0 primary key,TEST bit(7) not
null default 0);
create table bittest2 (ID int unsigned not null default 0 primary key);
insert into bittest2 values (1),(2),(3);
insert into bittest1 values (1,ord('a')),(2,0);

RIGHT behaviour:

select ID,TEST from bittest1 join bittest2 using (id) group by id;
+----+------+
| ID | TEST |
+----+------+
|  1 | a    |
|  2 |      |
+----+------+

STRANGE behaviour (nothing changed except for the extra group_concat):

select ID,TEST,group_concat(ID) from bittest1 join bittest2 using (id) group by id;
+----+------+------------------+
| ID | TEST | group_concat(ID) |
+----+------+------------------+
|  1 |      | 1                |
|  2 |      | 2                |
+----+------+------------------+

SOLUTION 1: cast to integer (using bin() is no sulution, it returns a bit string instead of the actual binary number)

select ID,0+TEST,group_concat(ID) from bittest1 join bittest2 using (id) group by id;
+----+--------+------------------+
| ID | 0+TEST | group_concat(ID) |
+----+--------+------------------+
|  1 |     97 | 1                |
|  2 |      0 | 2                |
+----+--------+------------------+

SOLUTION 2: use bitfields of at least 8 bits:

alter table bittest1 modify TEST bit(9) not null default 0;
select ID,TEST,group_concat(ID) from bittest1 join bittest2 using (id) group by id;
+----+------+------------------+
| ID | TEST | group_concat(ID) |
+----+------+------------------+
|  1 |  a   | 1                |
|  2 |      | 2                |
+----+------+------------------+

CONCLUSION: might be some strange conversion somewhere, but it has to do with a combination of bitfield of less than 8 bits AND group_concat, not the bitfield alone.