SP not seeing INSERTs within WHILE loop

[brian stone <skye0507@xxxxxxxxx>]

I have an issue where inserts are not being seen properly while in a SP.  The goal is to track payments made by customers, payment distribution.  I narrowed down the issue to the below, so I was able to exclude many columns and other tables in hopes it is simple enough to get some help. 

I have a payment table and a payment distribution table.  By joining the two, I can determine how much money is left for a payment.   I can then apply those monies to a customer charge.  

I created a simple procedure that loops and distributes $1 5 times.  

Problem: 
the SELECT returns $10 twice in a row.  Unless I am missing something, it should be returning $9 on the second SELECT.  After the second select, it returns 8, then 7, etc...  For some reason, that second select is wrong?

CREATE TABLE payment
(
  payment_id INT PRIMARY KEY AUTO_INCREMENT,
  amount DECIMAL(15,5),
  date_dist DATETIME NULL DEFAULT NULL -- date fully distributed
);

CREATE TABLE payment_dist
(
  payment_id INT NOT NULL DEFAULT 0,
  amount DECIMAL(15,5)
);

-- make a $10 payment
INSERT INTO PAYMENT VALUES (DEFAULT, 10.00, DEFAULT);

DROP PROCEDURE p;
DROP FUNCTION safe_decimal;
delimiter //
CREATE FUNCTION safe_decimal(d DECIMAL(15,5))
RETURNS DECIMAL(15,5)
BEGIN
  IF d IS NULL THEN
    RETURN 0;
  END IF;
  RETURN d;
END;
//
delimiter ;

delimiter //
CREATE PROCEDURE p ()
BEGIN
  DECLARE count INT DEFAULT 0;

  WHILE count < 5 DO
    SELECT payment.payment_id AS payment_id,
      (payment.amount - SUM(safe_decimal(d.amount))) AS amount
      FROM payment LEFT JOIN payment_dist d 
      ON payment.payment_id = d.payment_id
      WHERE payment.date_dist IS NULL GROUP BY 1 ORDER BY 1;
      
    SET count = count + 1;
    INSERT INTO payment_dist (payment_id, amount) VALUES (1, 1.00);
  END WHILE;
END;
//
delimiter ;
CALL p();

mysql> CALL p();
+------------+----------+
| payment_id | amount   |
+------------+----------+
|          1 | 10.00000 | 
+------------+----------+
1 row in set (0.00 sec)

+------------+----------+
| payment_id | amount   |
+------------+----------+
|          1 | 10.00000 | 
+------------+----------+
1 row in set (0.00 sec)

+------------+---------+
| payment_id | amount  |
+------------+---------+
|          1 | 8.00000 | 
+------------+---------+
1 row in set (0.00 sec)

+------------+---------+
| payment_id | amount  |
+------------+---------+
|          1 | 7.00000 | 
+------------+---------+
1 row in set (0.00 sec)

+------------+---------+
| payment_id | amount  |
+------------+---------+
|          1 | 6.00000 | 
+------------+---------+
1 row in set (0.00 sec)

Query OK, 1 row affected (0.00 sec)


mysql> SELECT payment.payment_id AS payment_id,
    -> (payment.amount - SUM(safe_decimal(d.amount))) AS amount
    -> FROM payment LEFT JOIN payment_dist d 
    -> ON payment.payment_id = d.payment_id
    -> WHERE payment.date_dist IS NULL GROUP BY 1 ORDER BY 1;
+------------+---------+
| payment_id | amount  |
+------------+---------+
|          1 | 5.00000 | 
+------------+---------+
1 row in set (0.00 sec)

I end up with the correct number but am getting the wrong result after the first insert.   

any ideas what is happening here?


 __________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com