Bug #103279 Inconsistent byte usage in result set of derived table using utf8m4b charset
Submitted: 12 Apr 8:06 Modified: 12 Apr 8:32
Reporter: Bart Vanderstukken Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Charsets Severity:S3 (Non-critical)
Version:5.7, 5.6, 5.5, 5.7.33 OS:Any
Assigned to: CPU Architecture:Any

[12 Apr 8:06] Bart Vanderstukken
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
[12 Apr 8:32] MySQL Verification Team
Hello Bart Vanderstukken,

Thank you for the report and test case.

regards,
Umesh