Bug #86045 FOUND_ROWS returns 1 when zero expected from UNION with CASE

• Submitted: 22 Apr 2017 8:03
• Modified: 22 Apr 2017 8:09
• Reporter: Jim Wimble
• Status: Duplicate
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7.17
• OS: MacOS (Sierra)
• CPU Architecture: Any
• Tags: FOUND_ROWS(), sql_calc_found_rows

[22 Apr 2017 8:03] Jim Wimble

Description:
Running the following SQL, which results in zero results, produces a count of 1 for FOUND_ROWS()

(
    SELECT SQL_CALC_FOUND_ROWS * FROM mysql.user WHERE 1=2
) UNION ( 
    SELECT * FROM mysql.user
    WHERE CASE ( NULL ) WHEN 1 THEN 2 END
)
LIMIT 10;
SELECT FOUND_ROWS();

How to repeat:
mysql> (
    ->     SELECT SQL_CALC_FOUND_ROWS * FROM mysql.user WHERE 1=2
    -> ) UNION ( 
    ->     SELECT * FROM mysql.user
    ->     WHERE CASE ( NULL ) WHEN 1 THEN 2 END
    -> )
    -> LIMIT 10;
Empty set (0.00 sec)

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

Suggested fix:
I believe this is a bug in the FOUND_ROWS()

[22 Apr 2017 8:09] MySQL Verification Team

Hello Jim Wimble,

Thank you for the report.
This is most likely duplicate of Bug #83110 which is fixed in 5.7.18, 8.0.1.

Thanks,
Umesh