Bug #20207 charset output problem with group_concat, jointure and order by
Submitted: 1 Jun 2006 14:32 Modified: 20 Jul 2006 15:34
Reporter: Fabien Chaillou Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.22 OS:Linux (Linux ubuntu dapper drake 6.06)
Assigned to: CPU Architecture:Any

[1 Jun 2006 14:32] Fabien Chaillou
Description:
In the database's table there is text with latin1 accents, when you fetch them using an utf8 client, depending on the query made, the text is converted to utf8 or it is still displayed as latin1 and so you have accents' quirk on the output.

How to repeat:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16 to server version: 5.0.22

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

mysql> CREATE TABLE e ( e1 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, e2 INT NOT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0,01 sec)

mysql> CREATE TABLE f ( f1 VARCHAR(255), e1 INT UNSIGNED NOT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0,00 sec)

mysql>  SHOW VARIABLES LIKE '%char%';
+--------------------------+-----------------------------------------+
| Variable_name            | Value                                   |
+--------------------------+-----------------------------------------+
| character_set_client     | latin1                                  |
| character_set_connection | latin1                                  |
| character_set_database   | latin1                                  |
| character_set_filesystem | binary                                  |
| character_set_results    | latin1                                  |
| character_set_server     | latin1                                  |
| character_set_system     | utf8                                    |
| character_sets_dir       | /opt/mysql-5.0.22/share/mysql/charsets/ |
+--------------------------+-----------------------------------------+
8 rows in set (0,00 sec)

Insert data in latin1 as the data are kept in latin1 in the database

mysql> INSERT INTO f VALUES ('Buggué',1);
Query OK, 1 row affected (0,01 sec)

mysql> INSERT INTO e VALUES (1,0);
Query OK, 1 row affected (0,00 sec)

let's change the character set to simulate the client fetching data in utf8

mysql> set character set 'utf8';
Query OK, 0 rows affected (0,00 sec)

mysql>  SHOW VARIABLES LIKE '%char%'; +--------------------------+-----------------------------------------+
| Variable_name            | Value                                   |
+--------------------------+-----------------------------------------+
| character_set_client     | utf8                                    |
| character_set_connection | latin1                                  |
| character_set_database   | latin1                                  |
| character_set_filesystem | binary                                  |
| character_set_results    | utf8                                    |
| character_set_server     | latin1                                  |
| character_set_system     | utf8                                    |
| character_sets_dir       | /opt/mysql-5.0.22/share/mysql/charsets/ |
+--------------------------+-----------------------------------------+
8 rows in set (0,00 sec)

mysql> SELECT GROUP_CONCAT(f.f1) FROM e LEFT JOIN f USING (e1) GROUP BY e.e1 ORDER BY e2;
+--------------------+
| GROUP_CONCAT(f.f1) |
+--------------------+
| Buggué            |
+--------------------+
1 row in set (0,00 sec)

here it is ok the output is in ut8

mysql> SELECT GROUP_CONCAT(f.f1) FROM e LEFT JOIN f USING (e1) GROUP BY e.e1 ORDER BY e.e1;
+--------------------+
| GROUP_CONCAT(f.f1) |
+--------------------+
| Buggué             |
+--------------------+
1 row in set (0,00 sec)

Problem here : the output is still in latin1 whereas it should have been the same as earlier

mysql> SELECT GROUP_CONCAT(f.f1) FROM f LEFT JOIN e USING (e1) GROUP BY e.e1 ORDER BY e1;
+--------------------+
| GROUP_CONCAT(f.f1) |
+--------------------+
| Buggué            |
+--------------------+
1 row in set (0,00 sec)

The problem is gone by switching the tables in the from and join statements.

mysql> SELECT GROUP_CONCAT(f.f1) FROM f LEFT JOIN e USING (e1) GROUP BY e.e1;
+--------------------+
| GROUP_CONCAT(f.f1) |
+--------------------+
| Buggué             |
+--------------------+
1 row in set (0,00 sec)

Same problem here.
It appears every time without the order by clause.
[1 Jun 2006 14:41] Fabien Chaillou
The request before the last is wrong, it works not because i switched the table in the from and join part but because i made the order by e1 and not e.e1, 

It seems it works with ORDER BY f.e1 and it misrenders with ORDER BY e.e1

mysql> SELECT GROUP_CONCAT(f.f1) FROM f LEFT JOIN e USING (e1) GROUP BY e.e1
ORDER BY e1;
+--------------------+
| GROUP_CONCAT(f.f1) |
+--------------------+
| Buggué            |
+--------------------+
1 row in set (0,00 sec)

mysql> SELECT GROUP_CONCAT(f.f1) FROM f LEFT JOIN e USING (e1) GROUP BY e.e1 ORDER BY e.e1;
+--------------------+
| GROUP_CONCAT(f.f1) |
+--------------------+
| Buggué             |
+--------------------+
1 row in set (0,00 sec)

mysql> SELECT GROUP_CONCAT(f.f1) FROM f LEFT JOIN e USING (e1) GROUP BY e.e1 ORDER BY f.e1;
+--------------------+
| GROUP_CONCAT(f.f1) |
+--------------------+
| Buggué            |
+--------------------+
1 row in set (0,00 sec)

Sorry for the typo error
[1 Jun 2006 14:45] Fabien Chaillou
I also forgot to say that everything works fine without the group_concat otherwise i wouldn't have put it in the synopsis
[1 Jun 2006 17:12] Valeriy Kravchuk
Thank you for a problem report. As group_concat is important here, please, check  bug #16712. Is it related?
[2 Jun 2006 9:14] Fabien Chaillou
I don't think it is related to this bug.
It is showing longtext and not longblob here is the ouput :

mysql> create view vvv1 as SELECT GROUP_CONCAT(f.f1) FROM e LEFT JOIN f USING (e1) GROUP BY e.e1 ORDER BY e.e1;
Query OK, 0 rows affected (0,00 sec)

mysql> create view vvv2 as SELECT GROUP_CONCAT(f.f1) FROM e LEFT JOIN f USING (e1) GROUP BY e.e1 ORDER BY e2;
Query OK, 0 rows affected (0,00 sec)

mysql> desc vvv1;
+--------------------+----------+------+-----+---------+-------+
| Field              | Type     | Null | Key | Default | Extra |
+--------------------+----------+------+-----+---------+-------+
| GROUP_CONCAT(f.f1) | longtext | YES  |     | NULL    |       |
+--------------------+----------+------+-----+---------+-------+
1 row in set (0,00 sec)

mysql> desc vvv2;
+--------------------+----------+------+-----+---------+-------+
| Field              | Type     | Null | Key | Default | Extra |
+--------------------+----------+------+-----+---------+-------+
| GROUP_CONCAT(f.f1) | longtext | YES  |     | NULL    |       |
+--------------------+----------+------+-----+---------+-------+
1 row in set (0,00 sec)

So it is still longtext on the case it works and on those it doesn't.
[20 Jun 2006 15:34] Valeriy Kravchuk
Can you, please, try to repeat with 'set names utf8' instead of 'set character set ...' and inform about the results? 

In my case I've got consistent results (in utf8) for all your queries.
[20 Jul 2006 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".