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
