title: Mysql FETCH CURSOR result ununderstood
body: <p>I've been Googleing around for a while and I am sure that the problem is that I don't understand clearly how CURSORs in MySQL work.</p>
  
  <p>A short explanation of the problem: I'm writing such function (simplified):</p>
  
  <pre><code>CREATE DEFINER=`me`@`localhost` FUNCTION `product_move`(prID INT, tr_type VARCHAR(2), clID INT, am INT, dnID INT, usrID INT, price FLOAT(10,2), ti DATETIME, barc TINYTEXT, cmt TINYTEXT, lnID INT)
  BEGIN
      DECLARE done INT DEFAULT FALSE;
      DECLARE cur_id INT;
      DECLARE net_pr FLOAT(10,2);
      DECLARE cur_r INT;
      DECLARE remaind INT DEFAULT 0;
      DECLARE avg_price FLOAT(10,2) DEFAULT 0;
      DECLARE curs CURSOR FOR SELECT  `products_transactionsID`,
                                                  `price`,
                                                  `remains`
                                          FROM `products_transactions`
                                          WHERE `productID`=prID AND `remains`&gt;0 AND `type`='V'
                                          ORDER BY `products_transactionsID` ASC;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
   OPEN curs;
             SET remaind=am;
             read_loop:LOOP
                  FETCH curs INTO cur_id,net_pr,cur_r;
                   IF done THEN
                    LEAVE read_loop;
                  END IF;
                  IF (cur_r&gt;=remaind) THEN
                      SET avg_price = avg_price + (net_pr * remaind);
                      UPDATE `products_transactions` SET `remains`=`remains`-remaind WHERE products_transactionsID=cur_id;
                      LEAVE read_loop;
                  ELSE
  
                      SET avg_price = avg_price + (net_pr * cur_r);
                      SET remaind=remaind-cur_r;
                      UPDATE `products_transactions` SET `remains`=0 WHERE products_transactionsID=cur_id;
                  END IF;
             END LOOP;
  
  CLOSE curs;
             SET avg_price=avg_price/am;
   INSERT INTO products_transactions
                         (`products_transactionsID`,`clientID`,`date_created`,`delivery_notesID`,`type`,`productID`,`amountIN`,`amountOUT`,`barcodes`,`in_stock`,`out_stock`,`out_repair`,`out_loss`,`booked`,`ordered`,`userID`,`price`,`comments`,`fifo_buy_price`)
                     SELECT NULL, clID, ti, dnID , tr_type, prID, 0, am, barc, products_transactions.in_stock-am, products_transactions.out_stock,
                         products_transactions.out_repair, products_transactions.out_loss, products_transactions.booked, products_transactions.ordered,usrID,price,cmt,avg_price
                     FROM
                         products_transactions WHERE productID=prID ORDER BY products_transactionsID DESC LIMIT 1;
  </code></pre>
  
  <p>So, we insert a new row in this table, based upon some calculations from the previously selected rows and updating these rows meanwhile.</p>
  
  <p>The problem is with the <code>avg_price</code> variable, which should be calculated based on the <code>net_pr</code> variable which is FETCH'ed from the cursor. But somehow, instead of being FETCH'ed from the SELECT, the <code>net_pr</code> variable takes the value of the <code>price</code> input parameter of my function! How is that possible?</p>
  
  <p>My guesses have been so far:</p>
  
  <ul>
  <li><p>a variable name conflict? Searched through the code but I can't find any.</p></li>
  <li><p>updating the table within the LOOP could make the CURSOR loose its position? It would make sense, but that wouldn't result in this, either...</p></li>
  </ul>
  
  <p>I'd apreciate any ideas.</p>