Very odd loop behaviour

Please consider the appended stored procedure.

The SELECT statements within the loop shown are simply to verify that
the stored procedure simple_rebalance_portfolio is working. It is!

The first select in the function gets a series of dates, in sequence,
ordered by year, and by month within the year. So much is obvious.
Obviously, also, each date returned must be unique (guaranteed by the
use of MIN()). I tested it separately, replacing p_id by actual
values. IT ALWAYS WORKS CORRECTLY AS EXPECTED!

What is odd is that all works well from the start of the loop through
almost to the end. But at the end of the loop, the loop is executed
TWICE for the very last date! Why?!!!

What did I miss? More importantly, how can I fix it so that I can
guarantee that the loop will be executed only once for each date, even
the last date?

Thanks

Ted
========problematic stored procedure==================
DROP PROCEDURE IF EXISTS `create_portfolio_history`;
CREATE PROCEDURE `create_portfolio_history`(p_id INTEGER)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE tdate DATE DEFAULT get_portfolio_inception_date(p_id);
DECLARE cur CURSOR FOR
SELECT MIN(signal_date) FROM etf_signals
WHERE signal_date > get_portfolio_inception_date(p_id)
GROUP BY YEAR(signal_date),MONTH(signal_date);
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur;
# REPEAT
WHILE NOT done DO
FETCH cur into tdate;
SELECT "Two records: one before rebalance and one after";
SELECT tdate,
get_portfolio_cash_at_date(p_id,tdate) AS cash,
get_etf_portfolio_value_at_rebalance_date(p_id,tda te) AS
total_value;
CALL simple_rebalance_portfolio(p_id,tdate);
SELECT tdate,
compute_total_commissions(p_id,tdate) As commissions,
get_portfolio_cash_at_date(p_id,tdate) AS cash,
get_etf_portfolio_value_at_rebalance_date(p_id,tda te) AS
total_value;
# UNTIL done END REPEAT;
END WHILE;
END
//

On Jan 14, 6:25 pm, Ted <r.ted.by...@rogers.com> wrote:
> Please consider the appended stored procedure.
>
> The SELECT statements within the loop shown are simply to verify that
> the stored procedure simple_rebalance_portfolio is working. It is!
>
> The first select in the function gets a series of dates, in sequence,
> ordered by year, and by month within the year. So much is obvious.
> Obviously, also, each date returned must be unique (guaranteed by the
> use of MIN()). I tested it separately, replacing p_id by actual
> values. IT ALWAYS WORKS CORRECTLY AS EXPECTED!
>
> What is odd is that all works well from the start of the loop through
> almost to the end. But at the end of the loop, the loop is executed
> TWICE for the very last date! Why?!!!
>
> What did I miss? More importantly, how can I fix it so that I can
> guarantee that the loop will be executed only once for each date, even
> the last date?
>
> Thanks
>
> Ted
> ========problematic stored procedure==================
> DROP PROCEDURE IF EXISTS `create_portfolio_history`;
> CREATE PROCEDURE `create_portfolio_history`(p_id INTEGER)
> BEGIN
> DECLARE done INT DEFAULT 0;
> DECLARE tdate DATE DEFAULT get_portfolio_inception_date(p_id);
> DECLARE cur CURSOR FOR
> SELECT MIN(signal_date) FROM etf_signals
> WHERE signal_date > get_portfolio_inception_date(p_id)
> GROUP BY YEAR(signal_date),MONTH(signal_date);
> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
> OPEN cur;
> # REPEAT
> WHILE NOT done DO
> FETCH cur into tdate;
> SELECT "Two records: one before rebalance and one after";
> SELECT tdate,
> get_portfolio_cash_at_date(p_id,tdate) AS cash,
> get_etf_portfolio_value_at_rebalance_date(p_id,tda te) AS
> total_value;
> CALL simple_rebalance_portfolio(p_id,tdate);
> SELECT tdate,
> compute_total_commissions(p_id,tdate) As commissions,
> get_portfolio_cash_at_date(p_id,tdate) AS cash,
> get_etf_portfolio_value_at_rebalance_date(p_id,tda te) AS
> total_value;
> # UNTIL done END REPEAT;
> END WHILE;
> END
> //

This is now solved. I had treated "DECLARE CONTINUE HANDLER FOR
SQLSTATE '02000' SET done = 1;" as a kind of exception; expecting that
when done is set to 1, I'd exit the loop. Instead, as written,
execution of the block continues to END WHILE using the value that had
previously been stored in tdate. Adding a call to FETCH prior to
entering the loop, and moving the call to fetch to the very end of the
loop, solved the problem. Clearly I have a thing or two to learn
about continue handlers.

Cheers

Ted