| Bug #965 | GROUP_CONCAT gibberish using string functions with joined tables | ||
|---|---|---|---|
| Submitted: | 31 Jul 2003 7:34 | Modified: | 9 Aug 2003 13:28 |
| Reporter: | Jaime Almeida | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 4.1 alpha | OS: | Linux (Linux (Intel)) |
| Assigned to: | Vasily Kishkin | CPU Architecture: | Any |
[31 Jul 2003 7:34]
Jaime Almeida
[31 Jul 2003 11:03]
Vasily Kishkin
What are types of fields you use ? char or varchar ?
[1 Aug 2003 4:11]
Allen Morris
The code below results in too different answers.
The firest select gives:
+--------------------------------+-----------+-----------+-------------------+
| GROUP_CONCAT(name) | MIN(name) | MAX(name) | product |
+--------------------------------+-----------+-----------+-------------------+
| 3 | name 1 | name 3 | This is a title 1 |
| 1 | name 1 | name 3 | This is a title 2 |
| 1 | name 1 | name 3 | This is a title 3 |
+--------------------------------+-----------+-----------+-------------------+
And the second select gives:
+-----------+-----------+--------------------------------+-------------------+
| MIN(name) | MAX(name) | GROUP_CONCAT(name) | product |
+-----------+-----------+--------------------------------+-------------------+
| NULL | NULL | ÿ ÿ | This is a title 1 |
| NULL | NULL | ÿ ÿ | This is a title 2 |
| NULL | NULL | ÿ ÿ | This is a title 3 |
+-----------+-----------+--------------------------------+-------------------+
The final select works a expected.
--------------------
DROP TABLE IF EXISTS product;
CREATE TABLE product (
product_id mediumint(8) unsigned NOT NULL auto_increment,
product varchar(255) default NULL,
PRIMARY KEY (product_id)
) TYPE=InnoDB CHARSET=latin1;
DROP TABLE IF EXISTS name;
CREATE TABLE name (
name_id mediumint(8) unsigned NOT NULL auto_increment,
product_id mediumint(8) unsigned NOT NULL,
name varchar(255) default NULL,
PRIMARY KEY (name_id)
) TYPE=InnoDB CHARSET=latin1;
insert into product ( product ) values ('This is a title 1');
SET @id=LAST_INSERT_ID();
insert into name ( product_id, name ) values (@id, 'name 1');
insert into name ( product_id, name ) values (@id, 'name 2');
insert into name ( product_id, name ) values (@id, 'name 3');
insert into product ( product ) values ('This is a title 2');
SET @id=LAST_INSERT_ID();
insert into name ( product_id, name ) values (@id, 'name 1');
insert into name ( product_id, name ) values (@id, 'name 2');
insert into name ( product_id, name ) values (@id, 'name 3');
insert into product ( product ) values ('This is a title 3');
SET @id=LAST_INSERT_ID();
insert into name ( product_id, name ) values (@id, 'name 1');
insert into name ( product_id, name ) values (@id, 'name 2');
insert into name ( product_id, name ) values (@id, 'name 3');
set group_concat_max_len = 30;
SELECT GROUP_CONCAT(name),
MIN(name),
MAX(name),
product
FROM product
JOIN name ON product.product_id = name.product_id
GROUP BY product.product_id;
SELECT MIN(name),
MAX(name),
GROUP_CONCAT(name),
product
FROM product
JOIN name ON product.product_id = name.product_id
GROUP BY product.product_id;
SELECT GROUP_CONCAT(name),
MIN(name),
MAX(name)
FROM name
GROUP BY product_id;
[1 Aug 2003 11:29]
Jaime Almeida
All my fields are varchar. Ive found other cases where putting the GROUP_CONCAT as the first select statement also returns gibrberish.
[8 Aug 2003 20:26]
Allen Morris
I am getting a cut warning on my failures. however make int varchars 10 and doing set group_concat_max_len=4096; do not seem to have much affect. Warning 1254 3 line(s) was(were) cut by group_concat()
[9 Aug 2003 13:28]
Vasily Kishkin
Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at
http://www.mysql.com/doc/en/Installing_source_tree.html
4.1 from bk tree returns correct results for both queries.
Chance is that it is fixed after 4.1.0 with some of other GROUP_CONCAT fixes
[20 Sep 2003 16:46]
Patrick Shoaf
I have a similar case. GROUP_CONCAT was working with one table, but not two. GROUP_CONCAT was in the middle of the SELECT. I placed GROUP_CONCAT first and works fine. Here is my working select... SELECT GROUP_CONCAT(Item_Size SEPARATOR ",") as sizes, Cat_Item_Img, Cat_Price, Product.Item_Code, Item_Img, Description, Category, Retail_Price, Short_Desc, Product.Item_Color FROM Cat_Items,Product WHERE cat_code ="BoltTech" and Cat_Items.Item_Code = Product.Item_Code and Category ="Casual Wear" group by Product.Item_Code My tables are still extremely small & I could email or attach them for other to test. I am currently running MySQL 4.1 on RedHat Linux ES Server 2.1
