Description:
Subquery returns 0 for "Count" values, when it should return correct values.
```SQL
SELECT
a.id,
(
SELECT COUNT(DISTINCT m.user_id)
FROM userContent m
WHERE m.status = 'completed' AND m.domain_id = 1 AND m.content_id = a.id
LIMIT 1
) AS 'Count'
FROM content a;
```
When this query is tweaked; removing one or more of the subquery's where conditions; removing the LIMIT, or setting as LIMIT 2 (greater than 1). It returns the correct values.
Note: I did notice flaky tests around this. But using the SQL attached it has been consistently repeatable for me.
How to repeat:
/**
* Bug found in MySQL 8.0.23, present in mysql 8.0.31
*/
DROP TABLE IF EXISTS userContent, domains, users, content;
-- ---------------------------------
-- CREATE
-- ---------------------------------
CREATE TABLE `content` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `users` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `domains` (
`id` smallint unsigned NOT NULL AUTO_INCREMENT,
`name` varchar DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `userContent` (
`id` int NOT NULL AUTO_INCREMENT,
`content_id` int NOT NULL,
`user_id` int NOT NULL,
`domain_id` smallint unsigned NOT NULL,
`status` enum('notstarted','opened','completed') NOT NULL DEFAULT 'notstarted',
PRIMARY KEY (`id`),
UNIQUE KEY `content_user_domain_id` (`content_id`,`user_id`,`domain_id`),
KEY `userContent_user` (`user_id`),
KEY `fk_userContent_domain_id` (`domain_id`),
CONSTRAINT `fk_userContent_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `domains` (`id`),
CONSTRAINT `userContent_content` FOREIGN KEY (`content_id`) REFERENCES `content` (`id`) ON DELETE CASCADE,
CONSTRAINT `userContent_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ---------------------------------
-- INSERT
-- ---------------------------------
INSERT INTO domains (id, `name`) VALUES (1, 'Test');
INSERT INTO content (id, `name`) VALUES (1, 'A'), (2, 'B'), (3, 'C');
INSERT INTO users (`id`,`name`)
VALUES
(11, 'devuser11@example.com'),
(12, 'devuser12@example.com');
INSERT INTO userContent (`domain_id`,`content_id`,`user_id`,`status`)
VALUES
(1, 3, 11, 'completed'),
(1, 3, 12, 'completed'),
(1, 2, 12, 'completed');
-- ---------------------------------
-- QUERY
-- ---------------------------------
SELECT
a.id,
(
SELECT COUNT(DISTINCT m.user_id)
FROM userContent m
WHERE m.status = 'completed' AND m.domain_id = 1 AND m.content_id = a.id
LIMIT 1
) AS 'Count'
FROM content a;
/**
In MySQL 8.0.31 this wrongfully returns...
id count
1 0
2 0
3 0
Whilst in MySQL 5.7.36 it works as expected
id Count
1 0
2 1
3 2
*/
Description: Subquery returns 0 for "Count" values, when it should return correct values. ```SQL SELECT a.id, ( SELECT COUNT(DISTINCT m.user_id) FROM userContent m WHERE m.status = 'completed' AND m.domain_id = 1 AND m.content_id = a.id LIMIT 1 ) AS 'Count' FROM content a; ``` When this query is tweaked; removing one or more of the subquery's where conditions; removing the LIMIT, or setting as LIMIT 2 (greater than 1). It returns the correct values. Note: I did notice flaky tests around this. But using the SQL attached it has been consistently repeatable for me. How to repeat: /** * Bug found in MySQL 8.0.23, present in mysql 8.0.31 */ DROP TABLE IF EXISTS userContent, domains, users, content; -- --------------------------------- -- CREATE -- --------------------------------- CREATE TABLE `content` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `users` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `domains` ( `id` smallint unsigned NOT NULL AUTO_INCREMENT, `name` varchar DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `userContent` ( `id` int NOT NULL AUTO_INCREMENT, `content_id` int NOT NULL, `user_id` int NOT NULL, `domain_id` smallint unsigned NOT NULL, `status` enum('notstarted','opened','completed') NOT NULL DEFAULT 'notstarted', PRIMARY KEY (`id`), UNIQUE KEY `content_user_domain_id` (`content_id`,`user_id`,`domain_id`), KEY `userContent_user` (`user_id`), KEY `fk_userContent_domain_id` (`domain_id`), CONSTRAINT `fk_userContent_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `domains` (`id`), CONSTRAINT `userContent_content` FOREIGN KEY (`content_id`) REFERENCES `content` (`id`) ON DELETE CASCADE, CONSTRAINT `userContent_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- --------------------------------- -- INSERT -- --------------------------------- INSERT INTO domains (id, `name`) VALUES (1, 'Test'); INSERT INTO content (id, `name`) VALUES (1, 'A'), (2, 'B'), (3, 'C'); INSERT INTO users (`id`,`name`) VALUES (11, 'devuser11@example.com'), (12, 'devuser12@example.com'); INSERT INTO userContent (`domain_id`,`content_id`,`user_id`,`status`) VALUES (1, 3, 11, 'completed'), (1, 3, 12, 'completed'), (1, 2, 12, 'completed'); -- --------------------------------- -- QUERY -- --------------------------------- SELECT a.id, ( SELECT COUNT(DISTINCT m.user_id) FROM userContent m WHERE m.status = 'completed' AND m.domain_id = 1 AND m.content_id = a.id LIMIT 1 ) AS 'Count' FROM content a; /** In MySQL 8.0.31 this wrongfully returns... id count 1 0 2 0 3 0 Whilst in MySQL 5.7.36 it works as expected id Count 1 0 2 1 3 2 */