Bug #83110 found_rows() returns 1 when no rows found

Submitted: 23 Sep 2016 1:08 Modified: 16 Dec 2016 20:09
Reporter: Yamada Isami Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7.15 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[23 Sep 2016 1:08] Yamada Isami
Description:
found_rows() returns 1 even if a query found no rows.
It happens only if the query matches following conditions.
- a text column exists in select list
- using UNION ALL
- using ORDER BY

How to repeat:
CREATE TABLE tbl (
  col_text text
);

SELECT SQL_CALC_FOUND_ROWS col_text FROM tbl AS tbl1
UNION ALL
SELECT col_text FROM tbl AS tbl2
ORDER BY col_text
LIMIT 0, 2;

Empty set (0.01 sec)

SELECT found_rows();

+--------------+
| found_rows() |
+--------------+
|            1 |
+--------------+
[23 Sep 2016 7:59] MySQL Verification Team
Hello Yamada,

Thank you for the report and test case.
Verified as described.

Thanks,
Umesh
[23 Sep 2016 8:00] MySQL Verification Team
-- Looks like regression to me

-- 4.1.25, 5.0.96, 5.1.77, 5.5.52, 5.6.33

mysql>
mysql> SELECT SQL_CALC_FOUND_ROWS col_text FROM tbl AS tbl1
    -> UNION ALL
    -> SELECT col_text FROM tbl AS tbl2
    -> ORDER BY col_text
    -> LIMIT 0, 2;
Empty set (0.00 sec)

mysql> SELECT found_rows();
+--------------+
| found_rows() |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

-- 5.7.15

mysql>
mysql> SELECT SQL_CALC_FOUND_ROWS col_text FROM tbl AS tbl1
    -> UNION ALL
    -> SELECT col_text FROM tbl AS tbl2
    -> ORDER BY col_text
    -> LIMIT 0, 2;
Empty set (0.02 sec)

mysql>  SELECT found_rows();
+--------------+
| found_rows() |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)
[16 Dec 2016 20:09] Paul DuBois
Posted by developer:
 
Noted in 5.7.18, 8.0.1 changelogs.

If InnoDB statistics were incorrect, FOUND_ROWS() could return 1 even
when the previous SELECT returned no rows.
[22 Apr 2017 8:09] MySQL Verification Team
Bug #86045 marked as duplicate of this one
[30 Nov 2017 3:57] Rick James
Why is this "Closed" without any explanation of what the fix is nor any documentation change?

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.055 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.