| Bug #74184 | sakila sample database view nicer_but_slower_film_list broken | ||
|---|---|---|---|
| Submitted: | 1 Oct 2014 23:56 | Modified: | 12 Nov 2014 22:37 |
| Reporter: | Morgan Tocker | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | OS: | Any | |
| Assigned to: | Paul DuBois | CPU Architecture: | Any |
[6 Oct 2014 8:48]
MySQL Verification Team
Hello Morgan, Thank you for the bug report. Thanks, Umesh
[6 Oct 2014 8:49]
MySQL Verification Team
// 5.7.6 mysql> select version(); +----------------------------------------------+ | version() | +----------------------------------------------+ | 5.7.6-m16-enterprise-commercial-advanced-log | +----------------------------------------------+ 1 row in set (0.00 sec) mysql> use sakila Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> mysql> select * from nicer_but_slower_film_list; ERROR 1055 (42000): Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sakila.category.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by mysql> mysql> show variables like 'sql_mode'; +---------------+---------------------------------------------------------------+ | Variable_name | Value | +---------------+---------------------------------------------------------------+ | sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | +---------------+---------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>
[7 Oct 2014 7:50]
Guilhem Bichot
The error message is correct. Imagine that there are two categories: two rows C1 and C2 in the "category" table, and no film of such categories. Then this part of the FROM clause:
FROM category LEFT JOIN film_category
ON category.category_id = film_category.category_id
LEFT JOIN film ON film_category.film_id = film.film_id
will produce two rows: one for category C1 with film.* filled with NULL ("no match"), and a similar one for category C2. Then, we group by film.film_id: we have two rows with film.film_id=NULL (so, they go into the same group) but different values of category.*: which value (C1's or C2's?) do we choose for category.name - it's random.
Would it make more sense to
GROUP BY film.film_id, category.name;
(any_value() would not be used) ?
I think that it would make the query be accepted.
[3 Nov 2014 15:59]
Morgan Tocker
Confirming that modifying the group by works fine:
CREATE VIEW nicer_but_slower_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(CONCAT(UCASE(SUBSTR(actor.first_name,1,1)),
LCASE(SUBSTR(actor.first_name,2,LENGTH(actor.first_name))),_utf8' ',CONCAT(UCASE(SUBSTR(actor.last_name,1,1)),
LCASE(SUBSTR(actor.last_name,2,LENGTH(actor.last_name)))))) SEPARATOR ', ') AS actors
FROM category LEFT JOIN film_category ON category.category_id = film_category.category_id LEFT JOIN film ON film_category.film_id = film.film_id
JOIN film_actor ON film.film_id = film_actor.film_id
JOIN actor ON film_actor.actor_id = actor.actor_id
GROUP BY film.film_id, category.name;
[12 Nov 2014 22:37]
Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly. Fixed using Guilhem's change, as verified by Morgan. Thanks, guys!
[12 Nov 2014 22:37]
Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly. Fixed using Guilhem's change, as verified by Morgan. Thanks, guys!
[14 Nov 2014 17:33]
Frank Cizmich
This fix also works for table "film_list", which has exactly the same issue.
[18 Nov 2014 16:05]
Paul DuBois
Will modify distribution to fix film_list view similiarly.
Description: The definition of nicer_but_slower_film_list does not work with only_full_group_by, which is now enabled by default. How to repeat: mysql [localhost] {msandbox} (sakila) > select version(); +-----------+ | version() | +-----------+ | 5.7.5-m15 | +-----------+ 1 row in set (0.00 sec) mysql [localhost] {msandbox} (sakila) > select * from nicer_but_slower_film_list; ERROR 1055 (42000): Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sakila.category.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by Suggested fix: Can be retrofitted by adding ANY_VALUE on view definition. CREATE VIEW nicer_but_slower_film_list AS SELECT film.film_id AS FID, film.title AS title, film.description AS description, ANY_VALUE(category.name) AS category, film.rental_rate AS price, film.length AS length, film.rating AS rating, GROUP_CONCAT(CONCAT(CONCAT(UCASE(SUBSTR(actor.first_name,1,1)), LCASE(SUBSTR(actor.first_name,2,LENGTH(actor.first_name))),_utf8' ',CONCAT(UCASE(SUBSTR(actor.last_name,1,1)), LCASE(SUBSTR(actor.last_name,2,LENGTH(actor.last_name)))))) SEPARATOR ', ') AS actors FROM category LEFT JOIN film_category ON category.category_id = film_category.category_id LEFT JOIN film ON film_category.film_id = film.film_id JOIN film_actor ON film.film_id = film_actor.film_id JOIN actor ON film_actor.actor_id = actor.actor_id GROUP BY film.film_id;