Bug #109585 Sub query not returning the right result
Submitted: 11 Jan 2023 12:06 Modified: 11 Jan 2023 12:25
Reporter: Andrew Dodson Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0.31 OS:Any (Docker image)
Assigned to: CPU Architecture:x86
Tags: regression

[11 Jan 2023 12:06] Andrew Dodson
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
*/
[11 Jan 2023 12:25] MySQL Verification Team
Hello Andrew,

Thank you for the report and feedback.

regards,
Umesh