CODEWITHSUNDEEP
mysqlselectmariadbsql-calc-found-rows
Ryan Griggs
Ryan Griggs

Reputation: 2758

MySQL/MariaDB FOUND_ROWS returns 1 even if no rows were returned

I'm running MariaDB version 5.5.56 and seem to be experiencing a bug: FOUND_ROWS() returns '1' after a SELECT which returns zero rows.

According to bug report 83110, MySQL had this problem as of version 5.7.15, but appears to be a closed issue, so I assumed it was fixed. However, I'm not sure how MySQL and MariaDB version numbers correlate, or whether fixes to MySQL are applied to MariaDB.

My distribution's packages include MariaDB 5.5.56 as the latest version available.

Am I doing something wrong? Why would FOUND_ROWS return 1 when the previous query returned zero results? (Yes I did include SQL_CALC_FOUND_ROWS statement in the previous query.

Example:

SELECT SQL_CALC_FOUND_ROWS * FROM table1
    WHERE field = 'something that would not match any records' LIMIT 5;
SELECT FOUND_ROWS() as count_of_rows;

-- count_of_rows = 1

Could this be the same bug, simply unpatched in MariaDB, or do you think I'm doing something wrong?

Thanks!

Upvotes: 3

Views: 1509

Answers (1)

lucygucy
lucygucy

Reputation: 56

MariaDB 5.5 matches MySQL 5.7 (including all the bugs I presume). MariaDB uses JIRA (https://jira.mariadb.org/) and you can see that a few different bugs with SQL_CALC_FOUND_ROWS were addressed.

I tried it on MariaDB 10.3 (the current GA release) and created some sample schema:

create table foo (id integer primary key, name varchar(50));

and inserted a few rows:

insert into foo(id, name) values (1, 'Bob');
insert into foo(id, name) values (2, 'Chris');
insert into foo(id, name) values (3, 'David');
insert into foo(id, name) values (4, 'Esther');
insert into foo(id, name) values (5, 'Fred');
insert into foo(id, name) values (6, 'Ginger');

and the query worked for me: select sql_calc_found_rows * from foo where name = 'x' limit 5;

SELECT FOUND_ROWS() as count_of_rows;
+---------------+
| count_of_rows |
+---------------+
|             0 |
+---------------+
1 row in set (0.010 sec)

Upvotes: 1

Related Questions