MySQL Bugs: #12690: HANDLER FOR 1329 triggered by SELECT INTO variable...

Bug #12690	HANDLER FOR 1329 triggered by SELECT INTO variable...
Submitted:	19 Aug 2005 20:49	Modified:	15 Dec 2010 10:19
Reporter:	[ name withheld ]	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S4 (Feature request)
Version:	5.0.11, 5.1.53	OS:	MacOS (Mac OSX 10.4)
Assigned to:		CPU Architecture:	Any

Description:
I have a stored procedure that uses a cursor, so I set up a continue handler to handle error 1329 (No data to FETCH).  I also have a SELECT INTO variable statement in this procedure.  If there is no data for the SELECT INTO variable, it causes a silent error which envokes my handler, bur returns no error or warning.  After I run the SELECT INTO varaible I want tio use the FOUND_ROWS() function expecting to get 0 and keep processing my cursor.  Since it is triggering error 1329, my cursor loop exits.

How to repeat:
CREATE TABLE tblteams (
  pKey mediumint(9) NOT NULL auto_increment,
  PRIMARY KEY  (pKey)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO tblteams (pKey)
VALUES(1),(2),(3);

CREATE TABLE tbltransactionrequests (
  pKey mediumint(9) NOT NULL auto_increment,
  TransactionID mediumint(9) NOT NULL default '0',
  Team mediumint(9) NOT NULL default '0',
  Action char(10) NOT NULL default '',
  TransDate date NOT NULL default '0000-00-00',
  Priority tinyint(4) NOT NULL default '0',
  ProcessedDate date NOT NULL default '0000-00-00',
  AcceptedDate date NOT NULL default '0000-00-00',
  PRIMARY KEY  (pKey),
  KEY Team (Team)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO tbltransactionrequests (TransactionID, Team)
VALUES(1,2),(2,2),(3,3),(4,3);

CREATE TABLE tbltest (
  test mediumint(9) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

delimiter //;

CREATE PROCEDURE `bfbtest`.`spTest`()
BEGIN
	DECLARE currentTrans int;
	DECLARE CONTINUE HANDLER FOR 1329 SET currentTrans = -1;
	
	SELECT t.TransactionID
	INTO currentTrans
	FROM tblTransactionRequests t
	WHERE t.ProcessedDate = '0000-00-00' AND t.Team = 1
	ORDER BY t.Priority, t.TransactionID
	LIMIT 1;
	
	INSERT tblTest(test)
	VALUES (currentTrans);
END//

delimiter ; //\

call spTest();

May be an OS X specific bug, but I thried to reproduce it (on Windows) with no luck (I used test database).

mysql> select * from tblTest;
Empty set (0.00 sec)

mysql> select routine_definition from information_schema.routines where routine_
name = 'spTest';
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------+
| routine_definition

                 |
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------+
| BEGIN
        DECLARE currentTrans int;
  DECLARE CONTINUE HANDLER FOR 1329 SET currentTrans = -1;

  SELECT t.TransactionID
        INTO currentTrans
        FROM tblTransactionRequests t
        WHERE t.ProcessedDate = '0000-00-00' AND t.Team = 1
        ORDER BY t.Priority, t.TransactionID
        LIMIT 1;

        INSERT tblTest(test)
        VALUES (currentTrans);

END |
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------+
1 row in set (0.01 sec)

mysql> call spTest();
Query OK, 1 row affected (0.00 sec)

mysql> select * from tblTest;
+------+
| test |
+------+
|   -1 |
+------+
1 row in set (0.00 sec)

mysql> select version();
+----------------+
| version()      |
+----------------+
| 5.0.11-beta-nt |
+----------------+
1 row in set (0.00 sec)

So, everything works just as explained in the manual (http://dev.mysql.com/doc/mysql/en/declare-handlers.html) for me from the command line.

I'm not questioning whether the handler is working, I'm questioning whether error code 1329 should be generated in this case.  The error is no data to FETCH, so I would think that it should only be triggered when using a cursor.  The first comment in the user manual says the same thing I am trying to say here.  The point is, that if i was running this statement inside of a cursor loop, it would exit my cursor loop which is not what I would want.  I propose that this statement issue some other error code, so that I would be able to continue my cursor loop.

OK, I've got your point. But it was not clear from your test case what did you expect to get. So, please, next time provide a complete test case with the expected results description.

SELECT INTO is a so called "implicit" cursor (cursor is declared implictely, opened, and 1 row fetched, then, usually next 1 row fetched just to check there is no more than one Then cursos is closed). So why do you think you need other error code?

As for the first comment in http://dev.mysql.com/doc/mysql/en/declare-handlers.html, it is just implemented in such a way now. You are requesting a new feature, more fine-grained handling of errors? Than sey just so, and not report a serious bug.

As a workaround, you may use a separate routine within your cursor loop, with it's own handler for the error.

Please add the ability to differentiate between a cursir no fetch and an implicit cursor no fetch.  The workaround adds uneccessary complexity.

SQL Standard clearly says that the same "no data" condition should be raised by both FETCH ... INTO and SELECT ... INTO. Thus this feature request will not be implemented.