Description:
The FOUND_ROWS builtin doesn't play well with builtins or query shapes other than the most trivial SELECT FOUND_ROWS(). Usually it gives 0; in certain UNION contexts (and maybe other subqueries too?) it gives the number of rows found by a subquery.
SELECT FOUND_ROWS() FROM t; -> 0
INSERT INTO t SELECT c, FOUND_ROWS() FROM t; -> 0
SELECT FOUND_ROWS() FROM (SELECT 1) sub; -> 0
SELECT FOUND_ROWS(); -> the real answer
SELECT FOUND_ROWS() FROM DUAL WHERE (SELECT 1); -> the real answer
SELECT FOUND_ROWS() UNION ALL SELECT FOUND_ROWS(); -> one copy of the real answer, and then 0
SELECT FOUND_ROWS() FROM t UNION ALL SELECT FOUND_ROWS(); -> t 0s, and then the number of rows found in t
Good luck explaining that last pair!
How to repeat:
DROP DATABASE IF EXISTS db1;
USE db1;
CREATE TABLE t (id INT, a INT);
INSERT INTO t VALUES (1,0), (2,1), (3,2);
SELECT * FROM t;
SELECT FOUND_ROWS() FROM t; -- 0 0 0; should be 1 1 1
INSERT INTO t SELECT id, FOUND_ROWS() FROM t; -- inserts 1,0 2,0 3,0; should be 1,3 2,3 3,3
SELECT FOUND_ROWS() FROM (SELECT 1) sub; -- 0; should be 3
SELECT FOUND_ROWS(); -- is correctly 1
SELECT FOUND_ROWS() FROM DUAL WHERE (SELECT 1); -- is correctly 1, which torpedos my hypothesis that subselects have something to do with it
SELECT FOUND_ROWS() UNION ALL SELECT FOUND_ROWS(); -- 1 0; should be 1 1
DELETE FROM t;
INSERT INTO t VALUES (), (), ();
SELECT FOUND_ROWS() FROM t UNION ALL SELECT FOUND_ROWS(); -- 0 0 0 3; should be 2 2 2 2
