| Bug #102101 | COUNT(*) returns null in nested query | ||
|---|---|---|---|
| Submitted: | 31 Dec 2020 13:48 | Modified: | 11 Jan 2021 19:40 |
| Reporter: | Eduardo Ubide | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.22 | OS: | Any |
| Assigned to: | CPU Architecture: | x86 | |
| Tags: | regression | ||
[31 Dec 2020 13:58]
MySQL Verification Team
Hello Eduardo Ubide, Thank you for the report and test case. regards, Umesh
[11 Jan 2021 19:40]
Jon Stephens
Fix documented in the MySQL 8.0.24 changelog, as follows:
When the aggregate iterator finds no rows, it calls on each item
in its SELECT list to inform them of this (for example, so that
COUNT(*) can set itself to zero, or SUM(foo) can set itself to
NULL). After internal work done in MySQL 8.0.22, it
could also inadvertently call hidden items. In some queries with
doubly nested subqueries, one such hidden item could become its
own parent subquery (and scalar subqueries in MySQL have special
legacy handling of this call, for queries which are not
ONLY_FULL_GROUP_BY), causing the entire subquery to return NULL
when it should not have done so.
This is fixed by making the call only on visible items, as in
MySQL 8.0.21 and earlier.
Closed.
Description: This query should return 0 if it finds nothing but returns a value of NULL. This only happens in version 8.0.22 and not in previous versions. SELECT (SELECT count(*) + (SELECT count(*) FROM classified_linked WHERE companyId = 1 AND classifiedId2 = c.classifiedId) FROM classified_linked WHERE companyId = 1 AND classifiedId1 = c.classifiedId) AS linked FROM (SELECT 555 AS classifiedId) AS c; How to repeat: Create an example table. CREATE TABLE `classified_linked` ( `companyId` int UNSIGNED NOT NULL, `classifiedId1` int UNSIGNED NOT NULL, `classifiedId2` int UNSIGNED NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; ALTER TABLE `classified_linked` ADD PRIMARY KEY (`companyId`,`classifiedId1`,`classifiedId2`), ADD KEY `classifiedId1` (`classifiedId1`), ADD KEY `classifiedId2` (`classifiedId2`,`companyId`) USING BTREE; The query that returns NULL: SQL> SELECT -> (SELECT count(*) + -> (SELECT count(*) -> FROM classified_linked -> WHERE companyId = 1 -> AND classifiedId2 = c.classifiedId) -> FROM classified_linked -> WHERE companyId = 1 -> AND classifiedId1 = c.classifiedId) AS linked -> FROM -> (SELECT 555 AS classifiedId) AS c; +--------+ | linked | +--------+ | NULL | +--------+ 1 row in set (0.00 sec) In version 8.0.21 and earlier this same query returns 0, which is the expected value. Suggested fix: Replacing innermost c.classifiedId reference with a value fixes it so the problem is related to: SQL> SELECT -> (SELECT count(*) + -> (SELECT count(*) -> FROM classified_linked -> WHERE companyId = 1 -> AND classifiedId2 = 555) -> FROM classified_linked -> WHERE companyId = 1 -> AND classifiedId1 = c.classifiedId) AS linked -> FROM -> (SELECT 555 AS classifiedId) AS c; +--------+ | linked | +--------+ | 0 | +--------+ 1 row in set (0.00 sec) On the other hand, repeating the rewritten query of the explain warning, also returns the correct result. SQL> EXPLAIN SELECT (SELECT count(*) + ... WARNING : Note (Code 1003): /* select#1 */ select (/* select#2 */ select (count(0) + (/* select#3 */ select count(0) from `test`.`classified_linked` where ((`test`.`classified_linked`.`classifiedId2` = '555') and (`test`.`classified_linked`.`companyId` = 1)))) from `test`.`classified_linked` where ((`test`.`classified_linked`.`classifiedId1` = '555') and (`test`.`classified_linked`.`companyId` = 1))) AS `linked` from dual