| Bug #83596 | select with group_concat will resturn wrong result of bit fields | ||
|---|---|---|---|
| Submitted: | 28 Oct 2016 8:06 | Modified: | 21 Nov 2016 13:47 |
| Reporter: | Feng Shen | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S2 (Serious) |
| Version: | 5.6.27-ndb-7.4.8-cluster-gpl | OS: | CentOS (CentOS release 6.4) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | bit, cluster, GROUP BY, group_concat | ||
[21 Nov 2016 13:47]
MySQL Verification Team
Hi,
verified as stated. Thanks for your submission
kind regards
Bogdan Kecman
mysql> CREATE TABLE `t3` (
-> `c1` int(11) DEFAULT NULL,
-> `c2` bit(1) DEFAULT NULL,
-> `c3` varchar(30) DEFAULT NULL
-> ) ENGINE=ndbcluster DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.21 sec)
mysql> insert into t3 values (1,0,'test'),(2,1,'good');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select c1,bin(c2+0),c3 from t3;
+------+-----------+------+
| c1 | bin(c2+0) | c3 |
+------+-----------+------+
| 1 | 0 | test |
| 2 | 1 | good |
+------+-----------+------+
2 rows in set (0.01 sec)
mysql> select c1,bin(c2+0) from (select c1,c2,group_concat(c3) from t3 group by c1,c2) t;
+------+-----------+
| c1 | bin(c2+0) |
+------+-----------+
| 1 | 1 |
| 2 | 1 |
+------+-----------+
2 rows in set (0.01 sec)
mysql> CREATE TABLE `t5` (
-> `c1` int(11) DEFAULT NULL,
-> `c2` bit(1) DEFAULT NULL,
-> `c3` varchar(30) DEFAULT NULL
-> ) ENGINE=innodb DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.18 sec)
mysql>
mysql> insert into t5 values (1,0,'test'),(2,1,'good');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select c1,bin(c2+0),c3 from t5;
+------+-----------+------+
| c1 | bin(c2+0) | c3 |
+------+-----------+------+
| 1 | 0 | test |
| 2 | 1 | good |
+------+-----------+------+
2 rows in set (0.00 sec)
mysql> select c1,bin(c2+0) from (select c1,c2,group_concat(c3) from t5 group by c1,c2) t;
+------+-----------+
| c1 | bin(c2+0) |
+------+-----------+
| 1 | 0 |
| 2 | 1 |
+------+-----------+
2 rows in set (0.00 sec)
mysql> select @@version
-> ;
+-------------------------------+
| @@version |
+-------------------------------+
| 5.6.29-ndb-7.4.11-cluster-gpl |
+-------------------------------+
1 row in set (0.00 sec)
mysql>
Description: when select with group_concat in a group by query, if the select fields include bit(1) type, this fields will return a wrong value. How to repeat: -- when use ndbcluster CREATE TABLE `t3` ( `c1` int(11) DEFAULT NULL, `c2` bit(1) DEFAULT NULL, `c3` varchar(30) DEFAULT NULL ) ENGINE=ndbcluster DEFAULT CHARSET=utf8; insert into t3 values (1,0,'test'),(2,1,'good'); mysql> select c1,bin(c2+0),c3 from t3; +------+-----------+------+ | c1 | bin(c2+0) | c3 | +------+-----------+------+ | 2 | 1 | good | | 1 | 0 | test | +------+-----------+------+ 2 rows in set (0.00 sec) mysql> select c1,bin(c2+0) from ( -> select c1,c2,group_concat(c3) from t3 group by c1,c2) t; +------+-----------+ | c1 | bin(c2+0) | +------+-----------+ | 1 | 1 | | 2 | 1 | +------+-----------+ 2 rows in set (0.00 sec) -- when use innodb CREATE TABLE `t5` ( `c1` int(11) DEFAULT NULL, `c2` bit(1) DEFAULT NULL, `c3` varchar(30) DEFAULT NULL ) ENGINE=innodb DEFAULT CHARSET=utf8; insert into t5 values (1,0,'test'),(2,1,'good'); mysql> select c1,bin(c2+0),c3 from t5; +------+-----------+------+ | c1 | bin(c2+0) | c3 | +------+-----------+------+ | 1 | 0 | test | | 2 | 1 | good | +------+-----------+------+ 2 rows in set (0.00 sec) mysql> select c1,bin(c2+0) from ( -> select c1,c2,group_concat(c3) from t5 group by c1,c2) t; +------+-----------+ | c1 | bin(c2+0) | +------+-----------+ | 1 | 0 | | 2 | 1 | +------+-----------+ 2 rows in set (0.00 sec) Suggested fix: The query with innodb table and ndb table behavior the same, return the same result.