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] Umesh Shastry
Hello Yamada,

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

Thanks,
Umesh
[23 Sep 2016 8:00] Umesh Shastry
-- 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] Umesh Shastry
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?