Bug #111095 Enhance film_list view by GROUP_CONCATing category.name column values
Submitted: 21 May 2023 9:42 Modified: 22 May 2023 12:43
Reporter: Jin Kwon Email Updates:
Status: Unsupported Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any
Tags: sakila

[21 May 2023 9:42] Jin Kwon
Description:
I hope some is involved with with Sakila sample database.

In the sakila-schema.sql, the `film_list` view defined like this.

```sql
CREATE VIEW film_list
AS
SELECT film.film_id                                                                        AS FID,
       film.title                                                                          AS title,
       film.description                                                                    AS description,
       category.name                                                                       AS category,
       film.rental_rate                                                                    AS price,
       film.length                                                                         AS length,
       film.rating                                                                         AS rating,
       GROUP_CONCAT(CONCAT(actor.first_name, _utf8mb4' ', actor.last_name) SEPARATOR ', ') AS actors
FROM film
         LEFT JOIN film_category ON film_category.film_id = film.film_id
         LEFT JOIN category ON category.category_id = film_category.category_id
         LEFT JOIN film_actor ON film.film_id = film_actor.film_id
         LEFT JOIN actor ON
    film_actor.actor_id = actor.actor_id
GROUP BY film.film_id, category.name;
```

When a film is mapped to more than one category, that view contains multiple rows for each film.

Changing the DDL like this will be nicer.

```sql
CREATE VIEW film_list
AS
SELECT film.film_id                                                                        AS FID,
       film.title                                                                          AS title,
       film.description                                                                    AS description,
       GROUP_CONCAT(category.name SEPARATOR ', ')                                          AS categories,
       film.rental_rate                                                                    AS price,
       film.length                                                                         AS length,
       film.rating                                                                         AS rating,
       GROUP_CONCAT(CONCAT(actor.first_name, _utf8mb4' ', actor.last_name) SEPARATOR ', ') AS actors
FROM film
         LEFT JOIN film_category ON film_category.film_id = film.film_id
         LEFT JOIN category ON category.category_id = film_category.category_id
         LEFT JOIN film_actor ON film.film_id = film_actor.film_id
         LEFT JOIN actor ON film_actor.actor_id = actor.actor_id
GROUP BY film.film_id;
```

How to repeat:
Described in the Description section.
[22 May 2023 12:43] MySQL Verification Team
Hi Mr. Kwon,

Thank you for your bug report.

However, we can not change Sakila database, since it is not maintained any more.

Unsupported.