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
*/