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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.051 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.