MySQL Bugs: #17735: concat of group_concat returns integer values minus one

Bug #17735	concat of group_concat returns integer values minus one
Submitted:	27 Feb 2006 11:08	Modified:	27 Feb 2006 13:10
Reporter:	Taco van den Broek	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.18-nt	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
I have a query in which a concat of a group_concat of an integer column is selected. When I select the integer column seperately next to the concat all is fine, if I don't the resulting string has all integers minus one.

How to repeat:
-- Create test table foo
DROP TABLE IF EXISTS `foo`;
CREATE TABLE IF NOT EXISTS `foo` (
  `foo` int(11) NOT NULL auto_increment,
  `bar` int(11) NOT NULL,
  `baz` int(11) default NULL,
  `qux` int(11) NOT NULL,
  `quux` int(11) NOT NULL,
  PRIMARY KEY  (`qux`,`quux`),
  UNIQUE KEY (`foo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Insert some test data
INSERT INTO foo (bar, baz, qux, quux) VALUES (1, null, 1, 14),(2, 1, 2, 7),(3, 2, 3, 4),(4, 2, 5, 6),(5, 1, 8, 13),(6, 5, 9, 10),(7, 5, 11, 12);

-- Correct select query
SELECT F1.foo, F2.bar, CONCAT(GROUP_CONCAT(F2.bar ORDER BY F2.qux SEPARATOR ':'), ':')
FROM foo F1 LEFT JOIN foo F2 ON F2.qux < F1.qux AND F2.quux > F1.qux 
GROUP BY F1.foo;

-- Failing select query
SELECT F1.foo, CONCAT(GROUP_CONCAT(F2.bar ORDER BY F2.qux SEPARATOR ':'), ':')
FROM foo F1 LEFT JOIN foo F2 ON F2.qux < F1.qux AND F2.quux > F1.qux 
GROUP BY F1.foo;

Thank you for a problem report. Sorry, but I was not able to repeat the problem you described with 5.0.19-BK on Linux:

openxs@suse:~/dbs/5.0> 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 to server version: 5.0.19

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

mysql> DROP TABLE IF EXISTS `foo`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `foo` (
    ->   `foo` int(11) NOT NULL auto_increment,
    ->   `bar` int(11) NOT NULL,
    ->   `baz` int(11) default NULL,
    ->   `qux` int(11) NOT NULL,
    ->   `quux` int(11) NOT NULL,
    ->   PRIMARY KEY  (`qux`,`quux`),
    ->   UNIQUE KEY (`foo`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO foo (bar, baz, qux, quux) VALUES (1, null, 1, 14),(2, 1, 2,
7),(3,
    -> 2, 3, 4),(4, 2, 5, 6),(5, 1, 8, 13),(6, 5, 9, 10),(7, 5, 11, 12);

Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT F1.foo, F2.bar, CONCAT(GROUP_CONCAT(F2.bar ORDER BY F2.qux SEPARATOR
    -> ':'), ':')
    -> FROM foo F1 LEFT JOIN foo F2 ON F2.qux < F1.qux AND F2.quux > F1.qux
    -> GROUP BY F1.foo;
+-----+------+-----------------------------------------------------------------+

| foo | bar  | CONCAT(GROUP_CONCAT(F2.bar ORDER BY F2.qux SEPARATOR
':'), ':') |
+-----+------+-----------------------------------------------------------------+
|   1 | NULL | NULL                                                            |
|   2 |    1 | 1:                                                              |
|   3 |    1 | 1:2:                                                            |
|   4 |    1 | 1:2:                                                            |
|   5 |    1 | 1:                                                              |
|   6 |    1 | 1:5:                                                            |
|   7 |    5 | 1:5:                                                            |
+-----+------+-----------------------------------------------------------------+

7 rows in set (0.01 sec)

mysql> SELECT F1.foo, CONCAT(GROUP_CONCAT(F2.bar ORDER BY F2.qux SEPARATOR ':'), ':')
    -> FROM foo F1 LEFT JOIN foo F2 ON F2.qux < F1.qux AND F2.quux > F1.qux
    -> GROUP BY F1.foo;
+-----+-----------------------------------------------------------------+
| foo | CONCAT(GROUP_CONCAT(F2.bar ORDER BY F2.qux SEPARATOR ':'), ':') |
+-----+-----------------------------------------------------------------+
|   1 | NULL                                                            |
|   2 | 1:                                                              |
|   3 | 1:2:                                                            |
|   4 | 1:2:                                                            |
|   5 | 1:                                                              |
|   6 | 1:5:                                                            |
|   7 | 1:5:                                                            |
+-----+-----------------------------------------------------------------+
7 rows in set (0.01 sec)

Thank you for checking this bug in version 5.0.19-BK on linux. I've also tested this behaviour on Mac OSX 10.4.5 with MySQL 5.0.18-standard and MySQL 5.0.18-debug. Don't know how soon a new stable version is released and if this behavior occurs in that new release, but is seems to me it should be checked a little more than just on one other operating system.