[MDEV-14538] FOUND_ROWS() returns 1 when no results were found in previous query Created: 2017-11-29  Updated: 2018-01-27  Resolved: 2018-01-27

Status: Closed
Project: MariaDB Server
Component/s: Server, Storage Engine - InnoDB
Affects Version/s: 5.5.56-galera
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Ryan Griggs Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: found_rows, galera, need_feedback, sql_calc_found_rows
Environment:

Centos 7..4.1708 x64


 Description   

After executing a query which includes SQL_CALC_FOUND_ROWS directive, if the query returns zero results, the FOUND_ROWS() function returns '1'.

Example:
SELECT SQL_CALC_FOUND_ROWS * FROM table_name WHERE some_field = 'some value that matches 0 rows';

SELECT FOUND_ROWS() as count_of_rows;

returns count_of_rows = 1

Version 5.5.56 is the latest version available in my distro's packages. Is this bug fixed with newer versions?
This bug seems to be similar to MySQL bug 83110: https://bugs.mysql.com/bug.php?id=83110

 Comments   
Comment by Elena Stepanova [ 2017-11-29 ]

Please provide the table definition, the actual query and the config file.
Test case from the upstream bug does indeed cause a failure on MySQL 5.7.15, but not on any version of MariaDB that I tried.

Comment by Ryan Griggs [ 2017-11-29 ]

Here is the table structure:
CREATE TABLE `field_groups` (
`id` int(10) UNSIGNED NOT NULL,
`group_name` varchar(255) NOT NULL COMMENT 'Name of group to which field belongs',
`field_name` varchar(255) NOT NULL COMMENT 'Name of field',
`comments` text NOT NULL COMMENT 'Comments describing this field'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `field_groups`
ADD PRIMARY KEY (`id`);

ALTER TABLE `field_groups`
MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=25;
COMMIT;

Comment by Ryan Griggs [ 2017-11-29 ]

Here is the query:

SELECT SQL_CALC_FOUND_ROWS * FROM field_groups WHERE field_name = 'some value that is not in the table';
SELECT FOUND_ROWS() as count_of_rows;

The second query returns "1" instead of "0"

Comment by Ryan Griggs [ 2017-11-29 ]

Here is "/etc/my.cnf"

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

symbolic-links=0
innodb_file_per_table = 1
thread_stack = 128K
net_buffer_length = 2K
read_rnd_buffer_size = 256K
read_buffer_size = 256K
sort_buffer_size = 64K
table_open_cache = 4
max_allowed_packet = 1M
key_buffer_size = 16K

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

!includedir /etc/my.cnf.d

Comment by Elena Stepanova [ 2017-11-29 ]

You've set the "affects version" as 5.5.56-galera, and also labeled the report as 'galera', but there is nothing in the config above that suggests you are using the cluster. So, which version do you actually use?

I've tried both 5.5.56 and 5.5.56-galera, and so far I haven't got the problem on either of them, neither with the empty table nor with some contents:

MariaDB [test]> CREATE TABLE `field_groups` (
 
    -> `id` int(10) UNSIGNED NOT NULL,
 
    -> `group_name` varchar(255) NOT NULL COMMENT 'Name of group to which field belongs',
 
    -> `field_name` varchar(255) NOT NULL COMMENT 'Name of field',
 
    -> `comments` text NOT NULL COMMENT 'Comments describing this field'
 
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
Query OK, 0 rows affected (0.06 sec)
 
 
 
MariaDB [test]> ALTER TABLE `field_groups`
 
    -> ADD PRIMARY KEY (`id`);
 
Query OK, 0 rows affected (0.10 sec)
 
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> ALTER TABLE `field_groups`
 
    -> MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=25;
 
Query OK, 0 rows affected (0.10 sec)               
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> COMMIT;
 
Query OK, 0 rows affected (0.00 sec)


MariaDB [test]> SELECT SQL_CALC_FOUND_ROWS * FROM field_groups WHERE field_name = 'some value that is not in the table';
 
Empty set (0.00 sec)
 
 
 
MariaDB [test]> SELECT FOUND_ROWS() as count_of_rows;
 
+---------------+
 
| count_of_rows |
 
+---------------+
 
|             0 |
 
+---------------+
 
1 row in set (0.00 sec)


MariaDB [test]> INSERT INTO field_groups VALUES (NULL,'foo','foo','foo'),(NULL,'bar','bar','bar');
 
Query OK, 2 rows affected (0.02 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> INSERT INTO field_groups SELECT NULL, group_name, field_name, comments from field_groups;
 
Query OK, 2 rows affected (0.06 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> INSERT INTO field_groups SELECT NULL, group_name, field_name, comments from field_groups;
 
Query OK, 4 rows affected (0.01 sec)
 
Records: 4  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> INSERT INTO field_groups SELECT NULL, group_name, field_name, comments from field_groups;
 
Query OK, 8 rows affected (0.02 sec)
 
Records: 8  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> INSERT INTO field_groups SELECT NULL, group_name, field_name, comments from field_groups;
 
Query OK, 16 rows affected (0.02 sec)
 
Records: 16  Duplicates: 0  Warnings: 0


MariaDB [test]>  SELECT SQL_CALC_FOUND_ROWS * FROM field_groups WHERE field_name = 'some value that is not in the table';
 
Empty set (0.00 sec)
 
 
 
MariaDB [test]> SELECT FOUND_ROWS() as count_of_rows;
 
+---------------+
 
| count_of_rows |
 
+---------------+
 
|             0 |
 
+---------------+
 
1 row in set (0.00 sec)


MariaDB [test]> select @@version;
 
+----------------------+
 
| @@version            |
 
+----------------------+
 
| 5.5.56-MariaDB-wsrep |
 
+----------------------+
 
1 row in set (0.00 sec)

Comment by Elena Stepanova [ 2017-12-28 ]

ryangriggs, could you please clarify the question of version?

Generated at Thu Feb 08 08:14:21 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.