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.
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.