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
• Status: Not a Bug
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 5.0.7 Beta
• OS: Both Linux and Windows
• 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>