Bug #118835 A query statement that returns 0/FALSE in the having clause, the returned result is not null.

• Submitted: 14 Aug 2:56
• Modified: 19 Aug 0:13
• Reporter: Alice Alice
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.41
• OS: Any
• CPU Architecture: Any

[14 Aug 2:56] Alice Alice

Description:
A query statement that returns 0/FALSE in the having clause, the returned result is not null.

mysql> select ((NULLIF(-430099078, AVG(SCHEMA()))) IS NULL) from t0;
+-----------------------------------------------+
| ((NULLIF(-430099078, AVG(SCHEMA()))) IS NULL) |
+-----------------------------------------------+
|                                             0 |
+-----------------------------------------------+
1 row in set, 3 warnings (0.00 sec)

mysql> SELECT /*+ READ_FROM_STORAGE(TIFLASH[t0])*/t0.c0 FROM t0 WHERE t0.c0 GROUP BY t0.c0 HAVING 0;
Empty set, 1 warning (0.00 sec)

mysql> SELECT /*+ READ_FROM_STORAGE(TIFLASH[t0])*/t0.c0 FROM t0 WHERE t0.c0 GROUP BY t0.c0 HAVING ((NULLIF(-430099078, AVG(SCHEMA()))) IS NULL);
+------------+
| c0         |
+------------+
| -589456629 |
| -503198307 |
|  662364767 |
+------------+
3 rows in set, 1 warning (0.00 sec)

How to repeat:
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `tidb202505291` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `tidb202505291`;

DROP TABLE IF EXISTS `t0`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `t0` (
  `c0` decimal(10,0) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `t0` VALUES (-589456629),(-503198307),(662364767);

SELECT /*+ READ_FROM_STORAGE(TIFLASH[t0])*/t0.c0 FROM t0 WHERE t0.c0 GROUP BY t0.c0 HAVING 0;

SELECT /*+ READ_FROM_STORAGE(TIFLASH[t0])*/t0.c0 FROM t0 WHERE t0.c0 GROUP BY t0.c0 HAVING ((NULLIF(-430099078, AVG(SCHEMA()))) IS NULL);

[18 Aug 16:39] MySQL Verification Team

Thank you for report. Verified as described.

mysql> SELECT t0.c0, ((NULLIF(-430099078, AVG(SCHEMA()))) IS NULL) FROM t0 WHERE t0.c0 GROUP BY t0.c0 HAVING ((NULLIF(-430099078, AVG(SCHEMA()))) IS NULL);
+------------+-----------------------------------------------+
| c0         | ((NULLIF(-430099078, AVG(SCHEMA()))) IS NULL) |
+------------+-----------------------------------------------+
| -589456629 |                                             0 |
| -503198307 |                                             0 |
|  662364767 |                                             0 |
+------------+-----------------------------------------------+
3 rows in set, 3 warnings (0.001 sec)

mysql>