Bug #107158 in Sakila database, some rows in payment table have null for rental_id
Submitted: 29 Apr 2022 5:54 Modified: 4 Oct 2022 21:03
Reporter: tim stewart Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:8.0.28 OS:Any
Assigned to: CPU Architecture:Any
Tags: sakila

[29 Apr 2022 5:54] tim stewart
Description:
In the Sakila sample database, in the `payment` table, there are five rows with null values for `rental_id`. This has two main consequences:

- The Sakila database basically has $9.95 worth of transactions that aren't connected to any rental. At first I wondered if this was intentional so that an intentional tutorial element of the Sakila database is for budding accountants to "spot the embezzlement" but probably that's not the case.

- The Sakila database contains an inconsistency in the total amount of rental sales that might trip up MySQL beginners who are executing complex queries and expecting the rental sales total to be the same no matter how their queries are structured. To wit, these two queries produce rental sales totals that differ by $9.95:

    select sum(amount) from payment;

    select sum(amount) from rental join payment where rental.rental_id = payment.rental_id;

You know we would just hate for some MySQL beginner to get points knocked off their project grade because their particular query happened to produce an incorrect total, amirite?! :-D

How to repeat:
Two ways to reproduce:

Method A. Examining the .sql files in an MSI installer file.

1. Download https://cdn.mysql.com/Downloads/MySQLInstaller/mysql-examples-8.0.28.msi

2. Decompress the sakila_data.sql file from the MSI file.

3. Examine these five rows that appear in the `INSERT INTO payment` block that show payment amounts being inserted that lack a `rental_id` FK:

    (424,16,1,NULL,'1.99','2005-06-18 04:56:12','2006-02-15 22:12:32'),

    (7011,259,2,NULL,'1.99','2005-08-23 06:13:16','2006-02-15 22:14:58'),

    (10840,401,1,NULL,'0.99','2005-07-12 06:26:10','2006-02-15 22:17:49'),

    (14675,546,1,NULL,'3.99','2005-07-30 21:16:20','2006-02-15 22:21:38'),

    (15458,577,2,NULL,'0.99','2005-05-27 00:46:39','2006-02-15 22:22:32'),

Method B. Using a live installation of MySQL 8.0.28 that has the `sakila` database loaded.

1. mysql> use sakila;

2. mysql> select * from payment where rental_id is null;

3. Examine the resulting five rows, noting the `rental_id` column is `NULL`

Suggested fix:
Honestly, my suggested fix is:

    DELETE FROM payment WHERE rental_id is null;

This ties up the five loose ends and makes for a cleaner, leaner, more-consistent Sakila for the upcoming 8.0.29 release.
[29 Apr 2022 6:29] MySQL Verification Team
Hello tim stewart,

Thank you for the report and feedback.

regards,
Umesh
[1 Aug 2022 15:55] Philip Olson
Posted by developer:
 
An update: while this is fixed, the MSI hasn't caught up yet. I'm unsure when it will but Sakila 1.3 is available online and here's the changelog:

https://dev.mysql.com/doc/sakila/en/sakila-news.html#sakila-news-1-3

Thanks Tim for the entertaining bug report and sorry for the long delay :)
[4 Oct 2022 21:03] Philip Olson
Posted by developer:
 
Confirmed that the upcoming Installer release (for the upcoming MySQL 8.0.31 release) contains the fixed Sakila database.

Closing this bug report, thanks for reporting it!