Description:
while the having subquery return NULL actually, the query using this having query returns a wrong 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 retuan an empty result set.
