| Bug #121499 | count(column) is counting null values in some cases | ||
|---|---|---|---|
| Submitted: | 14 Oct 3:38 | Modified: | 16 Oct 10:38 |
| Reporter: | Yoseph Phillips | Email Updates: | |
| Status: | Need Feedback | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 8.0.46 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | count | ||
[14 Oct 3:39]
Yoseph Phillips
Dump of the test data
Attachment: dump.sql (application/octet-stream, text), 47.87 KiB.
[16 Oct 10:38]
Knut Anders Hatlen
Thank you for the report. Using the supplied data, the query returns: t2.column1 t2.column2 COUNT(t1.column1) t1.column1 4095 4094 1 NULL 4512 4353 1 NULL Inspecting the joined rows before grouping, each of these two groups contains exactly one non-NULL t1.column1 value: 640339 and 698232, respectively. All other 97 groups contain no non-NULL values in that column. Thus, both COUNT(t1.column1) and the HAVING condition appear to produce the correct results. The displayed NULL values in the last column are possible because t1.column1 is selected without being grouped or aggregated. With ONLY_FULL_GROUP_BY disabled, MySQL may choose any value from that column within each group, including NULL. This does not mean that COUNT() counted a NULL. Could you clarify which part of this result you believe is incorrect, and what result you expect instead?
Description: count(column) is counting null values in some cases. How to repeat: Restore the dump. Run the following query: SELECT t2.column1, t2.column2, count(t1.column1) AS count_test_table_1_column1, t1.column1 AS test_table_1_column1 FROM test_table_2 t2 INNER JOIN test_table_3 t3 ON t3.column1 = (SELECT max(t.column1) FROM test_table_3 t WHERE t.column2 = t2.column1) INNER JOIN test_table_4 t4 ON t4.column1 = t3.column1 LEFT JOIN test_table_2 t5 ON t5.column2 = t2.column2 LEFT JOIN test_table_1 t1 ON t1.column3 = t5.column1 AND str_to_date(t1.column2, '%Y-%m-%d %H:%i') = str_to_date(concat(date(t2.column3), ' ', t4.column2), '%Y-%m-%d %H:%i') GROUP BY t2.column1, t2.column2 HAVING count(t1.column1) = 1 ORDER BY t1.column2; Observe that both results show that there is one not null value yet the count is incorrectly one for both of them.