Description:
When using a concat with a group_concat inside in a derived table, then selecting that concat result is cut off when using a utf8mb4 charset. It seems that each char in the result is taking up four bytes, while that is not happening without the concat, or when the derived table's query is run separately.
This is also not happening in MySQL8.
How to repeat:
Starting from this table with a `utf8mb4_unicode_ci` collation:
```
DROP TABLE IF EXISTS preferences;
CREATE TABLE IF NOT EXISTS preferences
(
id int auto_increment primary key,
user_id int not null,
title varchar(150) not null
) collate = utf8mb4_unicode_ci;
```
using these values:
```
INSERT INTO preferences (user_id, title) VALUES (1, 'Preference 1'), (1, 'Preference 2'), (1, 'Preference 3'), (1, 'Preference 4'), (1, 'Preference 5');
```
This query
```
SELECT CONCAT('"', GROUP_CONCAT(p.title SEPARATOR '","'), '"') AS title
FROM preferences AS p
GROUP BY p.user_id
```
with a `group_concat_max_len=64` results in
| title |
| ------------------------------------------------------------------ |
| "Preference 1","Preference 2","Preference 3","Preference 4","Pref" |
But when using that query in a derived table, like so:
```
SELECT preferences_grouped.title
FROM (
SELECT CONCAT('"', GROUP_CONCAT(p.title SEPARATOR '","'), '"') AS title
FROM preferences AS p
GROUP BY p.user_id
) as preferences_grouped
```
you'll get only 16 chars (64/4)
| title |
| ------------------ |
| "Preference 1","Pr |
This is not happening when the `concat` is removed or moved to the primary query:
```
SELECT CONCAT('"', preferences_grouped.title, '"')
FROM (
SELECT GROUP_CONCAT(p.title SEPARATOR '","') AS title
FROM preferences AS p
GROUP BY p.user_id
) as preferences_grouped;
```
| CONCAT('"', preferences_grouped.title, '"') |
| ------------------------------------------------------------------ |
| "Preference 1","Preference 2","Preference 3","Preference 4","Pref" |
Check the fiddle here: https://www.db-fiddle.com/f/hQD6r3pE5pmgR3n2XA7VtX/5
Description: When using a concat with a group_concat inside in a derived table, then selecting that concat result is cut off when using a utf8mb4 charset. It seems that each char in the result is taking up four bytes, while that is not happening without the concat, or when the derived table's query is run separately. This is also not happening in MySQL8. How to repeat: Starting from this table with a `utf8mb4_unicode_ci` collation: ``` DROP TABLE IF EXISTS preferences; CREATE TABLE IF NOT EXISTS preferences ( id int auto_increment primary key, user_id int not null, title varchar(150) not null ) collate = utf8mb4_unicode_ci; ``` using these values: ``` INSERT INTO preferences (user_id, title) VALUES (1, 'Preference 1'), (1, 'Preference 2'), (1, 'Preference 3'), (1, 'Preference 4'), (1, 'Preference 5'); ``` This query ``` SELECT CONCAT('"', GROUP_CONCAT(p.title SEPARATOR '","'), '"') AS title FROM preferences AS p GROUP BY p.user_id ``` with a `group_concat_max_len=64` results in | title | | ------------------------------------------------------------------ | | "Preference 1","Preference 2","Preference 3","Preference 4","Pref" | But when using that query in a derived table, like so: ``` SELECT preferences_grouped.title FROM ( SELECT CONCAT('"', GROUP_CONCAT(p.title SEPARATOR '","'), '"') AS title FROM preferences AS p GROUP BY p.user_id ) as preferences_grouped ``` you'll get only 16 chars (64/4) | title | | ------------------ | | "Preference 1","Pr | This is not happening when the `concat` is removed or moved to the primary query: ``` SELECT CONCAT('"', preferences_grouped.title, '"') FROM ( SELECT GROUP_CONCAT(p.title SEPARATOR '","') AS title FROM preferences AS p GROUP BY p.user_id ) as preferences_grouped; ``` | CONCAT('"', preferences_grouped.title, '"') | | ------------------------------------------------------------------ | | "Preference 1","Preference 2","Preference 3","Preference 4","Pref" | Check the fiddle here: https://www.db-fiddle.com/f/hQD6r3pE5pmgR3n2XA7VtX/5