Details
Major Description
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) |
Attachments
Issue Links
- causes
-
MDEV-29470 MariaDB Community Server 10.8.4 ERROR 1048 (23000): Column 'FIELDID' cannot be null
-
- Closed
-
Activity
The test case is failing on s390x:
|
https://buildd.debian.org/status/fetch.php?pkg=mariadb-10.6&arch=s390x&ver=1%3A10.6.7-3%7Eexp1&stamp=1646685571&raw=0 |
CURRENT_TEST: main.group_min_max
|
--- /<<PKGBUILDDIR>>/mysql-test/main/group_min_max.result 2022-02-10 20:07:03.000000000 +0000
|
+++ /<<PKGBUILDDIR>>/mysql-test/main/group_min_max.reject 2022-03-07 20:34:47.362475782 +0000
|
@@ -4059,7 +4059,7 @@
|
(2, TRUE, "yello"), (2, FALSE, "yello");
|
EXPLAIN SELECT DISTINCT owner_id FROM t1 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 t1 index NULL index_t1_on_owner_id_and_foo 7 NULL 5 Using where; Using index
|
+1 SIMPLE t1 index NULL index_t1_on_owner_id_and_foo 7 NULL 6 Using where; Using index
|
SELECT DISTINCT owner_id FROM t1 WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1);
|
owner_id
|
1
|
Thanks!
I repeated as described on 10.5,10.6 with InnoDB/Myisam: