MySQL Bugs: #118839: a query including the having subclause that returns NULL, return a wrong nonempty result.

Bug #118839	a query including the having subclause that returns NULL, return a wrong nonempty result.
Submitted:	14 Aug 3:13	Modified:	18 Aug 14:25
Reporter:	Alice Alice	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.41	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
a query including the having subclause that returns NULL, return a wrong nonempty result.

mysql> SELECT /*+ NO_INDEX_MERGE()*/v0.c0,(NOT (((DEFAULT(v0.c0))AND(t0.c0)))) FROM  v0 NATURAL RIGHT JOIN t0 GROUP BY t0.c1, t0.c0, (CASE v0.c0 WHEN t0.c1 THEN (+ (CAST(t0.c1 AS CHAR))) WHEN 311759337 THEN false ELSE t0.c1 END ) HAVING (NOT (((DEFAULT(v0.c0))AND(t0.c0)))) ORDER BY v0.c0;
+------+--------------------------------------+
| c0   | (NOT (((DEFAULT(v0.c0))AND(t0.c0)))) |
+------+--------------------------------------+
| NULL |                                 NULL |
+------+--------------------------------------+
1 row in set, 1 warning (0.00 sec)

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

USE `tidb2025060526`;

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,
  `c1` tinyint unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

INSERT INTO `t0` VALUES (2123084831,0);

DROP TABLE IF EXISTS `v0`;

/*!50001 DROP VIEW IF EXISTS `v0`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8mb4 */;
/*!50001 SET character_set_results     = utf8mb4 */;
/*!50001 SET collation_connection      = utf8mb4_0900_ai_ci */;
/*!50001 CREATE ALGORITHM=TEMPTABLE */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v0` (`c0`) AS select 146888344 AS `146888344` from `t0` order by `t0`.`c1` desc */;

mysql> SELECT /*+ NO_INDEX_MERGE()*/v0.c0,(NOT (((DEFAULT(v0.c0))AND(t0.c0)))) FROM  v0 NATURAL RIGHT JOIN t0 GROUP BY t0.c1, t0.c0, (CASE v0.c0 WHEN t0.c1 THEN (+ (CAST(t0.c1 AS CHAR))) WHEN 311759337 THEN false ELSE t0.c1 END ) HAVING (NOT (((DEFAULT(v0.c0))AND(t0.c0)))) ORDER BY v0.c0;
+------+--------------------------------------+
| c0   | (NOT (((DEFAULT(v0.c0))AND(t0.c0)))) |
+------+--------------------------------------+
| NULL |                                 NULL |
+------+--------------------------------------+
1 row in set, 1 warning (0.00 sec)

Suggested fix:
the query shall return an empty result set.

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

For details on getting support for MySQL products see http://www.mysql.com/support/
You can also check our forums (free) at http://forums.mysql.com/

Thank you for your interest in MySQL.

I insist it's a bug.
because a query with "Having NULL", shall return an empty result set.

Apologies, I think you are actually right

mysql>  SELECT /*+ NO_INDEX_MERGE()*/v0.c0,(NOT (((DEFAULT(v0.c0))AND(t0.c0)))) FROM  v0 NATURAL RIGHT JOIN t0 GROUP BY t0.c1, t0.c0, (CASE v0.c0 WHEN t0.c1 THEN (+ (CAST(t0.c1 AS CHAR))) WHEN 311759337 THEN false ELSE t0.c1 END ) HAVING (NOT (((DEFAULT(v0.c0))AND(t0.c0)))) ORDER BY v0.c0;
+------+--------------------------------------+
| c0   | (NOT (((DEFAULT(v0.c0))AND(t0.c0)))) |
+------+--------------------------------------+
| NULL |                                 NULL |
+------+--------------------------------------+
1 row in set, 1 warning (0.001 sec)

mysql>  SELECT /*+ NO_INDEX_MERGE()*/v0.c0,(NOT (((DEFAULT(v0.c0))AND(t0.c0)))) FROM  v0 NATURAL RIGHT JOIN t0 GROUP BY t0.c1, t0.c0, (CASE v0.c0 WHEN t0.c1 THEN (+ (CAST(t0.c1 AS CHAR))) WHEN 311759337 THEN false ELSE t0.c1 END ) HAVING null ORDER BY v0.c0; 
Empty set, 1 warning (0.001 sec)

mysql>  SELECT /*+ NO_INDEX_MERGE()*/v0.c0,(NOT (((DEFAULT(v0.c0))AND(t0.c0)))) FROM  v0 NATURAL RIGHT JOIN t0 GROUP BY t0.c1, t0.c0, (CASE v0.c0 WHEN t0.c1 THEN (+ (CAST(t0.c1 AS CHAR))) WHEN 311759337 THEN false ELSE t0.c1 END ) HAVING NULL ORDER BY v0.c0;
Empty set, 1 warning (0.001 sec)

mysql> 

Thanks!