Bug #11742 found_rows is returning incorrect row count after update statement
Submitted: 5 Jul 2005 18:25 Modified: 5 Jul 2005 19:34
Reporter: Vivian Fonger Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.7 Beta OS:Both Linux and Windows
Assigned to: CPU Architecture:Any

[5 Jul 2005 18:25] Vivian Fonger
Description:
Please read "How to repeat" on how to reproduce this problem.

How to repeat:
Create the following table:

CREATE TABLE `findrowbug`.`TEST1` (
  `ID` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `T1` VARCHAR(100) NOT NULL,
  PRIMARY KEY(`ID`)
)
ENGINE = InnoDB;

Run this insert statement first:
INSERT INTO TEST1 (T1) VALUES('Something');

Exit out of the client.

Re-log into the mysql client:

Set autocommit = 0;

Execute the following statement:

UPDATE TEST1 SET T1 = 'Something1' where ID = 1;

The client should display the following:
mysql> update TEST1 set T1='Something1' where ID = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Execute the following statement:
select found_rows();

You will see the following count:

mysql> select found_rows();
+--------------+
| found_rows() |
+--------------+
|            2 |
+--------------+
1 row in set (0.01 sec)

That's incorrect.  I would expect 1 instead of 2.  The underline select statement for the update should only return 1 row instead of 2 rows.
[5 Jul 2005 19:34] MySQL Verification Team
According the Manual:

"SQL_CALC_FOUND_ROWS and FOUND_ROWS() are new functions that make it possible to find out the number of rows a SELECT query that includes a LIMIT
clause would have returned without that clause."

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.9-beta-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `TEST1` (
    ->   `ID` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   `T1` VARCHAR(100) NOT NULL,
    ->   PRIMARY KEY(`ID`)
    -> )
    -> ENGINE = InnoDB;
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO TEST1 (T1) VALUES ('Something');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO TEST1 (T1) VALUES ('Something');
Query OK, 1 row affected (0.03 sec)

mysql> exit
Bye

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.9-beta-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT SQL_CALC_FOUND_ROWS (T1) FROM TEST1
    -> WHERE (T1)="Something" LIMIT 1;
+-----------+
| T1        |
+-----------+
| Something |
+-----------+
1 row in set (0.00 sec)

mysql> select found_rows();
+--------------+
| found_rows() |
+--------------+
|            2 |
+--------------+
1 row in set (0.00 sec)

mysql>