Bug #79131 Get negative FOUND_ROWS() for UNION stmt

• Submitted: 5 Nov 2015 5:34
• Modified: 17 Nov 2015 16:03
• Reporter: Su Dylan
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7.8, 5.7.9
• OS: Any
• CPU Architecture: Any

[5 Nov 2015 5:34] Su Dylan

Description:
Output:
=======
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 ( c1 int) ;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES  (1),(2),(3),(4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> ( SELECT SQL_CALC_FOUND_ROWS  *  FROM t1 WHERE false LIMIT 8 , 1 ) UNION ALL ( SELECT * FROM t1 LIMIT 4, 4 );
Empty set (0.00 sec)

mysql> select found_rows();
+--------------+
| found_rows() |
+--------------+
|           -8 |
+--------------+
1 row in set (0.00 sec)

mysql>
mysql> select version()
    -> ;
+-----------+
| version() |
+-----------+
| 5.7.8-rc  |
+-----------+
1 row in set (0.00 sec)

Problem:
========
In the manual, when UNION is executed, it is very complex.
However, negative should never be returned after a successful SELECT execution.

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( c1 int) ;
INSERT INTO t1 VALUES  (1),(2),(3),(4);
( SELECT SQL_CALC_FOUND_ROWS  *  FROM t1 WHERE false LIMIT 8 , 1 ) UNION ALL ( SELECT * FROM t1 LIMIT 4, 4 );
select found_rows();

Suggested fix:
The return value for FOUND_ROWS() is >= 0

[5 Nov 2015 6:39] Peter Laursen

On MySQL 5.6.13 I get:

found_rows()  
--------------
           341

[5 Nov 2015 7:27] MySQL Verification Team

Hello Su Dylan,

Thank you for the report.

Thanks,
Umesh

[17 Nov 2015 16:03] Paul DuBois

Noted in 5.7.11, 5.8.0 changelogs.

FOUND_ROWS() could return a negative value if the preceding query was
a UNION involving SQL_CALC_FOUND_ROWS and LIMIT ... OFFSET.