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
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7.15
• OS: Any
• 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?