Bug #118336 after adding a having clause equivalent to "having 1", the returned result set is wrong

• Submitted: 3 Jun 8:24
• Modified: 3 Jun 8:30
• Reporter: SamonBing SamonBing
• Status: Analyzing
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.41
• OS: Any
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[3 Jun 8:24] SamonBing SamonBing

Description:
after adding a having clause equivalent to "having 1", the returned result set is wrong.

first query:
mysql> SELECT t85.c0,(NOT (((DEFAULT(t85.c0))LIKE((- (t85.c0)))))) FROM  t85 NATURAL RIGHT JOIN t0 WHERE t0.c0 GROUP BY t0.c0 ORDER BY CAST(t85.c0 AS DECIMAL);
+----------+-----------------------------------------------+
| c0       | (NOT (((DEFAULT(t85.c0))LIKE((- (t85.c0)))))) |
+----------+-----------------------------------------------+
|     NULL |                                          NULL |
|     NULL |                                          NULL |
|     NULL |                                          NULL |
| 0.346522 |                                             1 |
+----------+-----------------------------------------------+
4 rows in set (0.00 sec)

second query:
mysql> SELECT t85.c0,(NOT (((DEFAULT(t85.c0))LIKE((- (t85.c0)))))) FROM  t85 NATURAL RIGHT JOIN t0 WHERE t0.c0 GROUP BY t0.c0 HAVING (NOT (((DEFAULT(t85.c0))LIKE((- (t85.c0)))))) ORDER BY CAST(t85.c0 AS DECIMAL);
Empty set (0.00 sec)

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

USE `tidb202506030`;

DROP TABLE IF EXISTS `t0`;
CREATE TABLE `t0` (
  `c0` float NOT NULL DEFAULT '0.887376',
  PRIMARY KEY (`c0`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

LOCK TABLES `t0` WRITE;
INSERT INTO `t0` VALUES (0),(0.0887488),(0.346522),(0.350693),(0.996159);
UNLOCK TABLES;

DROP TABLE IF EXISTS `t85`;
CREATE TABLE `t85` (
  `c0` float NOT NULL DEFAULT '0.887376',
  PRIMARY KEY (`c0`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

LOCK TABLES `t85` WRITE;
INSERT INTO `t85` VALUES (0),(0.0168601),(0.346522),(0.36369),(0.47245),(0.492592),(0.535686),(0.586635),(0.656416),(0.965053),(686378000);
UNLOCK TABLES;

mysql> SELECT t85.c0,(NOT (((DEFAULT(t85.c0))LIKE((- (t85.c0)))))) FROM  t85 NATURAL RIGHT JOIN t0 WHERE t0.c0 GROUP BY t0.c0 ORDER BY CAST(t85.c0 AS DECIMAL);
+----------+-----------------------------------------------+
| c0       | (NOT (((DEFAULT(t85.c0))LIKE((- (t85.c0)))))) |
+----------+-----------------------------------------------+
|     NULL |                                          NULL |
|     NULL |                                          NULL |
|     NULL |                                          NULL |
| 0.346522 |                                             1 |
+----------+-----------------------------------------------+
4 rows in set (0.00 sec)

mysql> SELECT t85.c0,(NOT (((DEFAULT(t85.c0))LIKE((- (t85.c0)))))) FROM  t85 NATURAL RIGHT JOIN t0 WHERE t0.c0 GROUP BY t0.c0 HAVING (NOT (((DEFAULT(t85.c0))LIKE((- (t85.c0)))))) ORDER BY CAST(t85.c0 AS DECIMAL);
Empty set (0.00 sec)

Suggested fix:
the second query shall return such a result set:

mysql> SELECT t85.c0,(NOT (((DEFAULT(t85.c0))LIKE((- (t85.c0)))))) FROM  t85 NATURAL RIGHT JOIN t0 WHERE t0.c0 GROUP BY t0.c0 HAVING (NOT (((DEFAULT(t85.c0))LIKE((- (t85.c0)))))) ORDER BY CAST(t85.c0 AS DECIMAL);
+----------+-----------------------------------------------+
| c0       | (NOT (((DEFAULT(t85.c0))LIKE((- (t85.c0)))))) |
+----------+-----------------------------------------------+
| 0.346522 |                                             1 |
+----------+-----------------------------------------------+