Description:
I'm getting some strange issue/bugs when using a COUNT() query on MySQL Community Server 5.6.30.
We have another installation of 5.6.24 where this issue doesn't occur, so I assume this is a bug within version 5.6.24 - 5.6.30.
The following query (id is the primary key) should return a number of rows, but always returns 0:
SELECT COUNT(id) FROM job WHERE field1=1 AND field2=1;
Replacing "COUNT(id)" with "*" returns all data for the expected number of rows, indicating this is a COUNT() issue.
The following also return 0:
SELECT COUNT(*) FROM job WHERE field1=1 AND field2=1;
SELECT COUNT(id) FROM job WHERE field1=1 AND field2=1;
SELECT COUNT(field1) FROM job WHERE field1=1 AND field2=1;
SELECT COUNT(field2) FROM job WHERE field1=1 AND field2=1;
However, the following returns the expected number:
SELECT COUNT(field3) FROM job WHERE field1=1 AND field2=1;
SELECT * FROM job WHERE field1=1 AND field2=1;
Using one clause works as expected:
SELECT COUNT(id) FROM job WHERE field1=1;
SELECT COUNT(id) FROM job WHERE field2=1;
I appreciate this is a mix of what works and what doesn't, but what is clear is that the initial SQL using COUNT(id) does not work on 5.6.30 but does on 5.6.24.
How to repeat:
SQL
---
SELECT COUNT(id) FROM job WHERE field1=1 AND field2=1;
Keys
----
Index Type Unique packed Column Cardinality Collation Null
field1 BTREE No No field1 2 A No
field2 BTREE No No field2 8 A No
Table
-----
CREATE TABLE `job` (
`id` int(11) NOT NULL,
`field1` int(11) NOT NULL,
`field2` int(11) NOT NULL,
`actioned_by` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Suggested fix:
SELECT COUNT(field3) FROM job WHERE field1=1 AND field2=1;
