MySQL Bugs: #82141: Sakila's get_customer_balance returns wrong balance

Bug #82141	Sakila's get_customer_balance returns wrong balance
Submitted:	7 Jul 2016 9:03	Modified:	9 Aug 2016 12:35
Reporter:	Ahmad Fatoum	Email Updates:
Status:	Analyzing	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	sakila

Description:
The comments in the get_customer_balance function don’t reflect what the code does:

> 2) ONE DOLLAR FOR EVERY DAY THE PREVIOUS RENTALS ARE OVERDUE

currently overdue is calculated till return_date even if the effective date is earlier

> 3) IF A FILM IS MORE THAN RENTAL_DURATION * 2 OVERDUE, CHARGE THE REPLACEMENT_COST

Isn’t implemented in code.

> 4) SUBTRACT ALL PAYMENTS MADE BEFORE THE DATE SPECIFIED

The payments should be subtracted from to get the balance, not the other way round.

How to repeat:
Using 
-- Sakila Sample Database Schema
-- Version 1.0
available from
https://dev.mysql.com/doc/index-other.html
downloaded
2016-06-06

Run:
SELECT get_customer_balance(98, '2005-07-30')

It returns 0.00.

Instead, it should return +7.00, because on the specified date, customer had paid more than was due.

For example, the film 'PILOT HOOSIERS', the copy with inventory_id=3090 (rental on 29.07.2005 with rental_duration of 6 days) was rentedon 2005-07-29 and customer 98 paid 5,99 although till 2005-07-30  only the rental_rate of 2,99 was due.

Suggested fix:
attached patch addresses above points.

-- Apparently Contributions aren't accepted to Documentation bugs.
-- My version of get_customer_data is following:

CREATE FUNCTION get_customer_balance(p_customer_id INT, p_effective_date DATETIME) RETURNS DECIMAL(5,2)
    DETERMINISTIC
    READS SQL DATA
BEGIN

       #OK, WE NEED TO CALCULATE THE CURRENT BALANCE GIVEN A CUSTOMER_ID AND A DATE
       #THAT WE WANT THE BALANCE TO BE EFFECTIVE FOR. THE BALANCE IS:
       #   1) RENTAL FEES FOR ALL PREVIOUS RENTALS
       #   2) ONE DOLLAR FOR EVERY DAY THE PREVIOUS RENTALS ARE OVERDUE
       #   3) IF A FILM IS MORE THAN RENTAL_DURATION * 2 OVERDUE, CHARGE THE REPLACEMENT_COST
       #   4) SUBTRACT FROM ALL PAYMENTS MADE BEFORE THE DATE SPECIFIED

  DECLARE v_rentfees    DECIMAL(5,2); #FEES PAID TO RENT THE VIDEOS INITIALLY
  DECLARE v_overfees    INTEGER;      #LATE FEES FOR PRIOR RENTALS
  DECLARE v_replacement DECIMAL(5,2);      #REPLACEMENT COUNT
  DECLARE v_payments    DECIMAL(5,2); #SUM OF PAYMENTS MADE PREVIOUSLY

  SELECT IFNULL(SUM(film.rental_rate),0) INTO v_rentfees
    FROM film, inventory, rental
    WHERE film.film_id = inventory.film_id
      AND inventory.inventory_id = rental.inventory_id
      AND rental.rental_date <= p_effective_date
      AND rental.customer_id = p_customer_id;

  SELECT IFNULL(SUM(IF((TO_DAYS(LEAST(rental.return_date, p_effective_date)) - TO_DAYS(rental.rental_date)) > film.rental_duration 
            AND (TO_DAYS(LEAST(rental.return_date, p_effective_date)) - TO_DAYS(rental.rental_date)) <= 2*film.rental_duration,
        ((TO_DAYS(LEAST(rental.return_date, p_effective_date)) - TO_DAYS(rental.rental_date)) - film.rental_duration),0)),0) INTO v_overfees
    FROM rental, inventory, film
    WHERE film.film_id = inventory.film_id
      AND inventory.inventory_id = rental.inventory_id
      AND rental.rental_date <= p_effective_date
      AND rental.customer_id = p_customer_id;

  SELECT IFNULL(SUM(IF((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) > 2*film.rental_duration,
        film.replacement_cost, 0)),0) INTO v_replacement
    FROM rental, inventory, film
    WHERE film.film_id = inventory.film_id
      AND inventory.inventory_id = rental.inventory_id
      AND rental.return_date < p_effective_date
      AND rental.customer_id = p_customer_id;

  SELECT IFNULL(SUM(payment.amount),0) INTO v_payments
    FROM payment

    WHERE payment.payment_date <= p_effective_date
    AND payment.customer_id = p_customer_id;

  RETURN v_payments - v_rentfees - v_overfees - v_replacement;
END $$