MySQL Bugs: #23760: row_count() and store procedure not working together

Bug #23760	row_count() and store procedure not working together
Submitted:	30 Oct 2006 6:41	Modified:	18 Dec 2006 19:01
Reporter:	D R	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.17-nt-max-log, 5.1 BK	OS:	Windows (WinXP ServicePack 2)
Assigned to:	Andrey Hristov	CPU Architecture:	Any

Description:
row_count(), maybe other function(not verified), dont work when passed as argument for store procedure.

To reproduce, set up the table and data like "How to repeat".
Example 1 : 
  This is meant to show what i would expect from row_count normally;

Example 2 : 
  row_count(), when used as an argument for a store procedure, doesn't seen to be executed here. I don't think it should nehave that way....

Example 3 :
  Exactly the same as example 2, but with my work around, by adding a layer between the procedure and the function, I get it to work....

How to repeat:
-- Setup table
DROP TABLE IF EXISTS TableX;
DROP TABLE IF EXISTS LogX;

CREATE TABLE `TableX` (
`ID` INT NOT NULL AUTO_INCREMENT ,
`Number` INT NOT NULL ,
PRIMARY KEY ( `ID` ) 
);

CREATE TABLE `LogX` (
`ID` INT NOT NULL AUTO_INCREMENT ,
`Reason` VARCHAR(50)NULL ,
`Amount` INT NOT NULL ,
PRIMARY KEY ( `ID` ) 
);

-- Setup data - Whatever data
insert into TableX(Number) VALUES(0);
insert into TableX(Number) VALUES(0);
insert into TableX(Number) VALUES(0);
insert into TableX(Number) VALUES(0);
insert into TableX(Number) VALUES(0);

-- Exemple 1
UPDATE TableX SET Number = 1;
SELECT row_count();
-- result 5

-- Exemple 2
DROP PROCEDURE IF EXISTS LogUpdate;
DROP PROCEDURE IF EXISTS testUpdate;

DELIMITER $$

CREATE PROCEDURE LogUpdate(lReason Varchar(50), lAmount INT)
BEGIN
  insert into LogX(Reason, Amount) VALUES(lReason, lAmount);
END $$

CREATE PROCEDURE testUpdate()
BEGIN
  UPDATE TableX SET Number = 2;
  CALL LogUpdate('Test not working', row_count());
END $$

DELIMITER ;

CALL testUpdate();
SELECT * FROM LogX; -- should return 0 in all entry at this point

-- Exemple 3

DROP PROCEDURE IF EXISTS LogUpdate;
DROP PROCEDURE IF EXISTS testUpdate;

DELIMITER $$

CREATE PROCEDURE LogUpdate(lReason Varchar(50), lAmount INT)
BEGIN
  insert into LogX(Reason, Amount) VALUES(lReason, lAmount);
END $$

CREATE PROCEDURE testUpdate()
BEGIN
  UPDATE TableX SET Number = 3;
  SET @a = row_count(); -- added this line
  CALL LogUpdate('Test working', @a);
END $$

DELIMITER ;

CALL testUpdate();
SELECT * FROM LogX; -- last row should return 5 change

Suggested fix:
see [Description] and [How to repeat]

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15296

ChangeSet@1.2334, 2006-11-14 14:18:10+01:00, andrey@example.com +4 -0
  Fix for bug#23760 ROW_COUNT() and store procedure not owrking together
  
  The problem was that THD::row_count_func was zeroed too early. It's zeroed
  as a fix for bug 4905 "Stored procedure doesn't clear for "Rows affected"

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15300

ChangeSet@1.2334, 2006-11-14 14:05:48+01:00, andrey@example.com +4 -0
  Fix for bug#23760 ROW_COUNT() and store procedure not owrking together
  
  The problem was that THD::row_count_func was zeroed too early. It's zeroed
  as a fix for bug 4905 "Stored procedure doesn't clear for "Rows affected"

Sent review by mail.  After investigation it turned out that the solution won't work, and bug#4905 should be fixed a different way.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15307

ChangeSet@1.2334, 2006-11-14 18:40:11+01:00, andrey@example.com +3 -0
  Fix for bug#23760 ROW_COUNT() and store procedure not owrking together
  
  The problem was that THD::row_count_func was zeroed too. It was zeroed
  as a fix for bug 4905 "Stored procedure doesn't clear for "Rows affected"
  However, the proper solution is not to zero, because THD::row_count_func has
  been set to -1 already in mysql_execute_command(), a later fix, which obsoletes
  the incorrect fix of #4095

The fix will be part of 5.0.32 and 5.1.15

Noted in 5.0.32, 5.1.15 changelogs.

ROW_COUNT() did not work properly as an argument to a stored
procedure.