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: | |
Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
Version: | OS: | Any | |
Assigned to: | Assigned Account | CPU Architecture: | Any |
Tags: | sakila |
[7 Jul 2016 9:03]
Ahmad Fatoum
[7 Jul 2016 9:05]
Ahmad Fatoum
-- 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 $$