|
Assume the following settings (defaults):
select @@SQL_MODE;
|
+-------------------------------------------------------------------------------------------+
|
| @@SQL_MODE |
|
+-------------------------------------------------------------------------------------------+
|
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
|
+-------------------------------------------------------------------------------------------+
|
1 row in set (0.000 sec)
|
|
select @@GLOBAL.SQL_MODE;
|
+-------------------------------------------------------------------------------------------+
|
| @@GLOBAL.SQL_MODE |
|
+-------------------------------------------------------------------------------------------+
|
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
|
+-------------------------------------------------------------------------------------------+
|
1 row in set (0.000 sec)
|
and the following database state:
/* create the database */
|
CREATE DATABASE IF NOT EXISTS bug_investigation;
|
use bug_investigation;
|
|
/* create the table */
|
DROP TABLE IF EXISTS `cats`;
|
CREATE TABLE `cats` (
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
`owner_id` int(11) DEFAULT NULL,
|
`foo` tinyint(1) DEFAULT 0,
|
`whatever` varchar(255) DEFAULT NULL,
|
PRIMARY KEY (`id`),
|
KEY `index_cats_on_owner_id_and_foo` (`owner_id`,`foo`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
/* Insert data */
|
INSERT INTO cats (owner_id, foo, whatever) VALUES (1, TRUE, "yello");
|
INSERT INTO cats (owner_id, foo, whatever) VALUES (1, FALSE, "yello");
|
INSERT INTO cats (owner_id, foo, whatever) VALUES (2, TRUE, "yello");
|
INSERT INTO cats (owner_id, foo, whatever) VALUES (2, TRUE, "yello");
|
INSERT INTO cats (owner_id, foo, whatever) VALUES (2, FALSE, "yello");
|
|
SELECT * FROM cats;
|
+----+----------+------+----------+
|
| id | owner_id | foo | whatever |
|
+----+----------+------+----------+
|
| 1 | 1 | 1 | yello |
|
| 2 | 1 | 0 | yello |
|
| 3 | 2 | 1 | yello |
|
| 4 | 2 | 1 | yello |
|
| 5 | 2 | 0 | yello |
|
+----+----------+------+----------+
|
5 rows in set (0.001 sec)
|
|
and note the following query:
SELECT DISTINCT owner_id FROM cats WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1);
|
10.3
The aforementioned query gives the following results:
MariaDB [bug_investigation]> SELECT DISTINCT owner_id FROM cats WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1);
|
+----------+
|
| owner_id |
|
+----------+
|
| 1 |
|
+----------+
|
1 row in set (0.000 sec)
|
and explanation:
MariaDB [bug_investigation]> EXPLAIN SELECT DISTINCT owner_id FROM cats WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1);
|
+------+-------------+-------+-------+---------------+--------------------------------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+--------------------------------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | cats | index | NULL | index_cats_on_owner_id_and_foo | 7 | NULL | 5 | Using where; Using index |
|
+------+-------------+-------+-------+---------------+--------------------------------+---------+------+------+--------------------------+
|
1 row in set (0.001 sec)
|
10.5.x
Starting from 10.5.3 and after (at least till 10.6.4 which I checked), the results are different.
MariaDB [bug_investigation]> SELECT DISTINCT owner_id FROM cats WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1);
|
Empty set (0.000 sec)
|
and explained:
MariaDB [bug_investigation]> EXPLAIN SELECT DISTINCT owner_id FROM cats WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1);
|
+------+-------------+-------+-------+---------------+--------------------------------+---------+------+------+---------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+--------------------------------+---------+------+------+---------------------------------------+
|
| 1 | SIMPLE | cats | range | NULL | index_cats_on_owner_id_and_foo | 7 | NULL | 6 | Using where; Using index for group-by |
|
+------+-------------+-------+-------+---------------+--------------------------------+---------+------+------+---------------------------------------+
|
1 row in set (0.001 sec)
|
The same query but ignoring the used index, returns the correct results.
MariaDB [bug_investigation]> SELECT DISTINCT owner_id FROM cats IGNORE INDEX (index_cats_on_owner_id_and_foo) WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1);
|
+----------+
|
| owner_id |
|
+----------+
|
| 1 |
|
+----------+
|
1 row in set (0.000 sec)
|
|