Missing Test Case - 596. Classes More Than 5 Students

[ameyasamak]

LeetCode Username

ameya samak

Problem Number, Title, and Link

596. Classes More Than 5 Students, https://leetcode.com/problems/classes-more-than-5-students/description/

Bug Category

Missing test case (Incorrect/Inefficient Code getting accepted because of missing test cases)

Bug Description

The bug in your SQL query arises from the use of COUNT(class) in the HAVING clause, which is not accurately reflecting the number of students enrolled in each class. The issue stems from the fact that you're counting the class column instead of the student column, meaning the query is counting how many times a class appears in the table rather than how many unique students are enrolled in each class.

In the context of the problem, you are required to find classes with at least five students. Using COUNT(class) doesn't properly handle cases where the student column may contain NULL values or where the number of class rows is not a direct reflection of student enrollment. This leads to incorrect results when filtering for classes with five or more students.

Language Used for Code

MySQL

Code used for Submit/Run operation

SELECT class
FROM Courses
GROUP BY class
HAVING COUNT(class) >= 5

Expected behavior

The query should return all classes that have at least five enrolled students. The expected behavior is as follows:

The query should group records by the class. It should count only the students in each class, ignoring any rows where the student is NULL. It should filter classes where the count of students is greater than or equal to 5. The final result should display the names of all classes that meet this condition.

Screenshots

Additional context

No response

[exalate-issue-sync[bot]]

LeetCode Support commented: It appears that the test case that exposes the issue with the code is missing. Could you please provide a specific test case where the current code fails to produce the correct output? This will help us understand the problem better and assist in making necessary improvements. Thank you for your cooperation.

LeetCode Support Team.

[ameyasamak]

Here’s a specific test case that will help identify the bug in the code where the current query incorrectly uses COUNT(class) instead of COUNT(student) in the HAVING clause.

Test Case:

Table Courses:

student	class
S1	Math
S2	Math
S3	Math
S4	Math
S5	Math
S6	Math
NULL	Science
S7	Science
S8	Science
S9	Science
S10	Science
S11	Science

Expected Output:

The correct result should include both Math and Science because both classes have at least 5 non-NULL students. The correct output is:

class
Math
Science

Explanation:

• Math has 6 students (S1 to S6), so it meets the requirement of having at least 5 students.
• Science has 5 students (S7 to S11), even though one entry has a NULL value for the student, which should be ignored in the count.

Incorrect Output: If the current code is using COUNT(class) instead of COUNT(student), it might incorrectly return only Math because COUNT(class) counts all rows regardless of whether the student field is NULL. Since Science has a NULL student, using COUNT(class) could result in fewer rows being counted or ignoring important logic related to student enrollment.

How This Test Case Exposes the Bug:

• COUNT(class) counts all rows for a class, even if the student field is NULL, which could lead to incorrect results when filtering for classes with at least 5 students.
• By using COUNT(student), the query correctly counts only non-NULL students, ensuring that the right number of students is used in the filter.

[exalate-issue-sync[bot]]

LeetCode Support commented: Hello,

Your reported issue has been relayed to our team for thorough investigation. We appreciate your patience as we work to address and resolve this matter. We will reach out to you when we have updates regarding the issue.

If you have any further questions or concerns in the meantime, please feel free to let us know.

Best regards, LeetCode Support Team