Bug #73283 FOUND_ROWS() returns 0 on SELECT COUNT(*) of impossible where

• Submitted: 13 Jul 2014 18:22
• Modified: 19 Nov 2019 22:22
• Reporter: Bill Karwin (Candidate Quality Contributor) (OCA)
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.6.17, 5.6.20, 5.7.4
• OS: Any
• CPU Architecture: Any

[13 Jul 2014 18:22] Bill Karwin

Description:
FOUND_ROWS() should return 1 after any simple query that does SELECT COUNT(*) with no GROUP BY. That is, the SELECT COUNT(*) returns 1 row, whether the query matched any rows or not.

But FOUND_ROWS() returns 0 if the previous query was optimized to a NULL join type because of an impossible where.

How to repeat:
mysql> SELECT COUNT(*) FROM mysql.user;
+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+

mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|            1 |
+--------------+

mysql> SELECT COUNT(*) FROM mysql.user WHERE user='root';
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+

mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|            1 |
+--------------+

mysql> SELECT COUNT(*) FROM mysql.user WHERE user='boot';
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|            1 |
+--------------+

mysql> SELECT COUNT(*) FROM mysql.user WHERE 'root'='boot';
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|            0 |
+--------------+

mysql> EXPLAIN SELECT COUNT(*) FROM mysql.user WHERE 'root'='boot';
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+

[13 Jul 2014 18:26] Bill Karwin

Edited synopsis to mention impossible where.

To be clear about what I posted in the steps to reproduce, I included several tests that behave correctly. Then I showed the query with an impossible where, which unexpectedly returns 0.

[14 Jul 2014 8:09] MySQL Verification Team

Hello Bill,

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

Thanks,
Umesh

[19 Nov 2019 22:22] Roy Lyseng

Posted by developer:
 
Fixed in 5.7.29 and later