MySQL Bugs: #16645: FOUND

Bug #16645	FOUND_ROWS() Bug
Submitted:	19 Jan 2006 18:07	Modified:	7 Mar 2006 22:53
Reporter:	Stéphane Petitcolas	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	1.0.7	OS:	Windows (WIN XP)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
Hi !

I've tried to do these queries :
SELECT SQL_CALC_FOUND_ROWS * FROM test LIMIT 0, 10;
SELECT FOUND_ROWS();

If we suppose that there is 900 records in the table test.
With a 4.1.11 version of MySQL , FOUND_ROWS() returns 900 (that is correct). But, with 5.0.15, 5.0.16 and 5.0.18 versions, FOUND_ROWS() returns always the number 124 (that is not correct).

How to repeat:
Create a table test, provide its and execute these queries :

SELECT SQL_CALC_FOUND_ROWS * FROM test LIMIT 0, 10;
SELECT FOUND_ROWS();

Thank you for a problem report. Please, send the SHOW CREATE TABLE test results, to be sure.

CREATE TABLE `test` (
  `testID` int(11) NOT NULL auto_increment,
  `testName` varchar(100) default '',
  PRIMARY KEY  (`testID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

it works properly at least on linux. Going to test it on very win xp.

bin/mysql -u root test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.18-max

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

mysql> CREATE TABLE `test` (
    ->   `testID` int(11) NOT NULL auto_increment,
    ->   `testName` varchar(100) default '',
    ->   PRIMARY KEY  (`testID`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter //
mysql> create procedure filltest(IN counter INT) 
      begin   
        declare i integer; 
            set i = 1; 
            while i <= counter do
                 insert into test (testName) values('bebe');    
                set i = i + 1; 
           END WHILE ; 
      END//
Query OK, 0 rows affected (0.00 sec)

mysql> call filltest(1000)//
Query OK, 1 row affected (1.05 sec)

mysql> select count(*) from test//
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM test LIMIT 0, 10 //
+--------+----------+
| testID | testName |
+--------+----------+
|      1 | bebe     |
|      2 | bebe     |
|      3 | bebe     |
|      4 | bebe     |
|      5 | bebe     |
|      6 | bebe     |
|      7 | bebe     |
|      8 | bebe     |
|      9 | bebe     |
|     10 | bebe     |
+--------+----------+
10 rows in set (0.00 sec)

mysql> SELECT FOUND_ROWS()//
+--------------+
| FOUND_ROWS() |
+--------------+
|         1000 |
+--------------+
1 row in set (0.00 sec)

Sorry.
After tests, the bug is not due to MySQL server.
But, the bug is due to the MySQL Connector Net 1.0.7. So, the bug still running in my project.

More precision :
the query is correct with :  MySQL Connector Net 1.0.7 + MySQL Server 5.0.18.
there is a bug with : MySQL Connector Net 1.0.7 + MySQL Server 4.1.

It's the same with MySQL Connector Net 1.0.6

UPDATE

More precision :
the query is correct with :  MySQL Connector Net 1.0.7 + MySQL Server 4.1.
there is a bug with : MySQL Connector Net 1.0.7 + MySQL Server 5.0.18.

It's the same with MySQL Connector Net 1.0.6

Unable to reproduce.  Can you post a test case and table script that shows the problem.  Here is the test case I used that works against MySql 5.0.18-nt

[Test]
public void FoundRows()
{
execSQL("DROP TABLE IF EXISTS test");
execSQL("CREATE TABLE test (testID int(11) NOT NULL auto_increment, " +
             "testName varchar(100) default '',  PRIMARY KEY  (testID)) " +
             " ENGINE=InnoDB DEFAULT CHARSET=latin1");		
MySqlCommand cmd = new MySqlCommand("INSERT INTO test VALUES
                                                          (NULL, 'test')", conn);
for (int i=0; i < 1000; i++)
        cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT SQL_CALC_FOUND_ROWS * FROM test LIMIT 0, 10";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT FOUND_ROWS()";
object cnt = cmd.ExecuteScalar();
Assert.AreEqual(1000, cnt);
}

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/3566