MySQL Bugs: #40497: found_rows() function delivers unexpected result when used without SQL_CALC

Bug #40497	found_rows() function delivers unexpected result when used without SQL_CALC_FOUN
Submitted:	4 Nov 2008 12:45	Modified:	26 Nov 2008 16:50
Reporter:	Gustaf Thorslund	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0, 5.1, 6.0	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
The documentation in http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows says:

"In the absence of the SQL_CALC_FOUND_ROWS option in the most recent successful SELECT statement, FOUND_ROWS() returns the number of rows in the result set returned by that statement."

This is not what happens with resent versions.

How to repeat:
mysql> CREATE TABLE t (i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, r TEXT);
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> LOAD DATA INFILE '/usr/share/common-licenses/GPL' INTO TABLE t FIELDS TERMINATED BY '' LINES TERMINATED BY '\n' (r);
Query OK, 676 rows affected, 121 warnings (0.03 sec)
Records: 676  Deleted: 0  Skipped: 0  Warnings: 121

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1261 | Row 1 doesn't contain data for all columns   | 
| Warning | 1261 | Row 4 doesn't contain data for all columns   | 
| Warning | 1261 | Row 8 doesn't contain data for all columns   | 

(ignore this, just need some test data)

mysql> SELECT COUNT(*) FROM t;
+----------+
| COUNT(*) |
+----------+
|      676 | 
+----------+
1 row in set (0.01 sec)

mysql> SELECT * FROM t LIMIT 50, 10;
+----+--------------------------------------------------------------------------+
| i  | r                                                                        |
+----+--------------------------------------------------------------------------+
| 51 |   Some devices are designed to deny users access to install or run       | 
| 52 | modified versions of the software inside them, although the manufacturer | 
| 53 | can do so.  This is fundamentally incompatible with the aim of           | 
| 54 | protecting users' freedom to change the software.  The systematic        | 
| 55 | pattern of such abuse occurs in the area of products for individuals to  | 
| 56 | use, which is precisely where it is most unacceptable.  Therefore, we    | 
| 57 | have designed this version of the GPL to prohibit the practice for those | 
| 58 | products.  If such problems arise substantially in other domains, we     | 
| 59 | stand ready to extend this provision to those domains in future versions | 
| 60 | of the GPL, as needed to protect the freedom of users.                   | 
+----+--------------------------------------------------------------------------+
10 rows in set (0.01 sec)

mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|           60 | 
+--------------+
1 row in set (0.00 sec)

mysql> SELECT VERSION();
+---------------------------+
| VERSION()                 |
+---------------------------+
| 5.1.27-ndb-6.3.17-2hardy1 | 
+---------------------------+
1 row in set (0.01 sec)

Suggested fix:
Fix the behaviour or document the misbehaviour.

create table t(id serial, i integer);

delimiter §
create procedure p_t()
begin
declare i integer default 1;
while i < 101 do
insert into t(i) values(i);
set i = i + 1;
end while;
end §

delimiter ;

call p_t;

select count(*) from t;
+----------+
| count(*) |
+----------+
|      100 | 
+----------+
1 row in set (0.03 sec)

SELECT * FROM t LIMIT 50, 10;
+----+------+
| id | i    |
+----+------+
| 51 |   51 | 
| 52 |   52 | 
| 53 |   53 | 
| 54 |   54 | 
| 55 |   55 | 
| 56 |   56 | 
| 57 |   57 | 
| 58 |   58 | 
| 59 |   59 | 
| 60 |   60 | 
+----+------+
10 rows in set (0.00 sec)

SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|           60 | 
+--------------+
1 row in set (0.02 sec)

The behaviour is contrary to documentation. Fixing the behaviour would break lots of user code so fixing the documentation would make more sense.

The documentation in
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows
says:

"In the absence of the SQL_CALC_FOUND_ROWS option in the most recent successful SELECT statement, FOUND_ROWS() returns the number of rows in the result set returned by that statement."

It would be nice to add here that when LIMIT is used in the SELECT statement is will return the number of rows up to the limit.

With an example LIMIT 50,10 will return 60 rows.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

New wording:

In the absence of the SQL_CALC_FOUND_ROWS option in the most recent
successful SELECT statement, FOUND_ROWS() returns the number of rows
in the result set returned by that statement. If the statement
includes a LIMIT clause, FOUND_ROWS() returns the number of rows up
to the limit. For example, FOUND_ROWS() returns 10 or 60,
respectively, if the statement includes LIMIT 10 or LIMIT 50, 10.