Hi ALL
  we met a strange case about group_concat and filesort
  Group_concat function of the query results garbled (a lot comma).
—query group_concat
SELECT SQL_NO_CACHE A.partition_by1 AS id,
GROUP_CONCAT(A.object_id) AS i_am_virtual_1
FROM test.ibtest2 AS A
GROUP BY id limit 10;
—result (a lot comma???)
| id                     | i_am_virtual_1                                          
|                      | ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

in brighthouse engine, do not use group_concat result set display normal; to use group_concat garbled.
—query no group_concat
SELECT SQL_NO_CACHE A.partition_by1 AS id,
A.object_id AS i_am_virtual_1
FROM test.ibtest2 AS A
GROUP BY id limit 10;
—result(some utf8 character,but ok)
| id       ||i_am_virtual_1                    
+——————-+——————————————————————————————————————————————————————————+
| 15153039956 | 防水台 松糕 凉鞋 白 |
| 15153047089 | 震动腰带                                        |
| 15153092225 | 配件                   

Myisam engine, all works well.
—query
SELECT SQL_NO_CACHE A.partition_by1 AS id,
GROUP_CONCAT(A.object_id) AS i_am_virtual_1
FROM test.mytest2 AS A
WHERE A.thedate = 20120601   AND A.toprank_id = 25
GROUP BY id limit 10;
—result
| id       | i_am_virtual_1  
| 15153039956 | 防水台 松糕 凉鞋 白,t åž‹ ç²—è·Ÿ 凉鞋,白色 防水台 高跟,粗高跟鱼嘴松糕跟女凉,蜂叶 40161-10001跳跳跑跑也行 韩国舒适款粗跟鱼嘴松糕凉鞋,松糕护士凉鞋,真皮粗跟厚底松糕凉鞋欧美风,2012夏季æ

—table-structure
CREATE TABLE `ibtest2` (
  `thedate` date NOT NULL DEFAULT ‘0000-00-00’,
  `toprank_id` int(10) NOT NULL DEFAULT ‘0’,
  `if_test` int(1) NOT NULL DEFAULT ‘0’,
  `object_id` varchar(1024) NOT NULL DEFAULT ‘’,
  `idx` decimal(18,4) NOT NULL DEFAULT ‘0.0000’,
  `idx_rank` int(10) NOT NULL DEFAULT ‘0’,
  `idx_last` decimal(18,4) NOT NULL DEFAULT ‘0.0000’,
  `idx_rank_last` int(10) NOT NULL DEFAULT ‘0’,
  `idx_rank_change` int(10) NOT NULL DEFAULT ‘0’,
  `chg_idx` decimal(18,4) NOT NULL DEFAULT ‘0.0000’,
  `idx_up_rank` int(10) NOT NULL DEFAULT ‘0’,
  `idx_down_rank` int(10) NOT NULL DEFAULT ‘0’,
  `added_quantity1` decimal(18,4) NOT NULL DEFAULT ‘0.0000’,
  `added_quantity2` decimal(18,4) NOT NULL DEFAULT ‘0.0000’,
  `added_quantity3` decimal(18,4) NOT NULL DEFAULT ‘0.0000’,
  `partition_by1` varchar(1024)  NOT NULL DEFAULT ‘’ COMMENT ‘lookup’,
  `partition_by2` varchar(1024) NOT NULL DEFAULT ‘’ COMMENT ‘lookup’,
  `partition_by3` varchar(1024) NOT NULL DEFAULT ‘’ COMMENT ‘lookup’,
  `partition_by4` varchar(1024) NOT NULL DEFAULT ‘’ COMMENT ‘lookup’,
  `partition_by5` varchar(1024) NOT NULL DEFAULT ‘’ COMMENT ‘lookup’
) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=utf8 ;

CREATE TABLE `mytest2` (
  `thedate` date NOT NULL DEFAULT ‘0000-00-00’,
  `toprank_id` int(10) NOT NULL DEFAULT ‘0’,
  `if_test` int(1) NOT NULL DEFAULT ‘0’,
  `object_id` varchar(1024) NOT NULL DEFAULT ‘’,
  `idx` decimal(18,4) NOT NULL DEFAULT ‘0.0000’,
  `idx_rank` int(10) NOT NULL DEFAULT ‘0’,
  `idx_last` decimal(18,4) NOT NULL DEFAULT ‘0.0000’,
  `idx_rank_last` int(10) NOT NULL DEFAULT ‘0’,
  `idx_rank_change` int(10) NOT NULL DEFAULT ‘0’,
  `chg_idx` decimal(18,4) NOT NULL DEFAULT ‘0.0000’,
  `idx_up_rank` int(10) NOT NULL DEFAULT ‘0’,
  `idx_down_rank` int(10) NOT NULL DEFAULT ‘0’,
  `added_quantity1` decimal(18,4) NOT NULL DEFAULT ‘0.0000’,
  `added_quantity2` decimal(18,4) NOT NULL DEFAULT ‘0.0000’,
  `added_quantity3` decimal(18,4) NOT NULL DEFAULT ‘0.0000’,
  `partition_by1` varchar(1024)  NOT NULL DEFAULT ‘’ COMMENT ‘lookup’,
  `partition_by2` varchar(1024) NOT NULL DEFAULT ‘’ COMMENT ‘lookup’,
  `partition_by3` varchar(1024) NOT NULL DEFAULT ‘’ COMMENT ‘lookup’,
  `partition_by4` varchar(1024) NOT NULL DEFAULT ‘’ COMMENT ‘lookup’,
  `partition_by5` varchar(1024) NOT NULL DEFAULT ‘’ COMMENT ‘lookup’
) ENGINE=myisam DEFAULT CHARSET=utf8 ;
select * from test.mytest limit 20000 into outfile ‘/tmp/raw.data’ FIELDS TERMINATED BY ‘\t’  LINES TERMINATED BY ‘\n’;
load data infile ‘/tmp/raw.data’ into table mytest2 FIELDS TERMINATED BY ‘\t’  LINES TERMINATED BY ‘\n’;
load data infile ‘/tmp/raw.data’ into table ibtest2 FIELDS TERMINATED BY ‘\t’  LINES TERMINATED BY ‘\n’;

SELECT SQL_NO_CACHE A.partition_by1 AS id,
GROUP_CONCAT(A.object_id) AS i_am_virtual_1
FROM test.mytest2 AS A
WHERE A.thedate = 20120601   AND A.toprank_id = 25
GROUP BY id limit 10;

SELECT SQL_NO_CACHE A.partition_by1 AS id,
GROUP_CONCAT(A.object_id) AS i_am_virtual_1
FROM test.ibtest2 AS A
GROUP BY id limit 10;

SELECT SQL_NO_CACHE A.partition_by1 AS id,
A.object_id AS i_am_virtual_1
FROM test.ibtest2 AS A
GROUP BY id limit 10;

Hi !

GROUP_CONCAT is not supported by IB internally. The query is delegated to the MyISAM engine anyway.

The results suggests that the IB tables have a lot of empty strings. Please check the contents of the IB tables.

I tried to load the attached file, but:

THX for your reply
I have reupload a rawdata,u can load it in this way

LOAD DATA LOCAL INFILE “./raw.data” INTO TABLE test.mytest CHARACTER SET UTF8 FIELDS TERMINATED BY “\t” ESCAPED BY “\” ENCLOSED BY “NULL” ;

Hi !

Somehow I still could not load the file, I needed to delete the first and the last line. Then the LOAD succeded.

It looks like GROUP_CONCAT does not work correctly on UTF8 data. I reported a defect concerning that: http://bugs.infobright.org/ticket/2225