Bug #118836 adding a having clause that return 1/TRUE, the query returns a different and wrong empty result.
Submitted: 14 Aug 3:01 Modified: 18 Aug 16:21
Reporter: Alice Alice Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.41 OS:Any
Assigned to: CPU Architecture:Any

[14 Aug 3:01] Alice Alice 
Description:
adding a having clause that return 1/TRUE, the query returns a different and wrong empty result.

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 |
+----------+-----------------------------------------------+
[18 Aug 15:54] MySQL Verification Team 
mysql> select * from t0;
+-----------+
| c0        |
+-----------+
|         0 |
| 0.0887488 |
|  0.346522 |
|  0.350693 |
|  0.996159 |
+-----------+
5 rows in set (0.001 sec)

mysql> select * from t85;
+-----------+
| c0        |
+-----------+
|         0 |
| 0.0168601 |
|  0.346522 |
|   0.36369 |
|   0.47245 |
|  0.492592 |
|  0.535686 |
|  0.586635 |
|  0.656416 |
|  0.965053 |
| 686378000 |
+-----------+
11 rows in set (0.001 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 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.000 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.001 sec)

mysql> select @@version
    -> ;
+-----------+
| @@version |
+-----------+
| 9.3.0     |
+-----------+
1 row in set (0.000 sec)

mysql>
[18 Aug 16:21] MySQL Verification Team 
Query 1: No HAVING clause.
Query 2: With HAVING (NOT (((DEFAULT(t85.c0)) LIKE ((- (t85.c0))))))

Last row has this HAVING value of 1 but is not shown. I believe this bug is verified.

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.002 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.001 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
    ->     ( (((DEFAULT(t85.c0)) LIKE ((- (t85.c0))))))
    -> ORDER BY 
    ->     CAST(t85.c0 AS DECIMAL);
Empty set (0.002 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
    ->     1
    -> 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.002 sec)

mysql>
[18 Aug 16:22] MySQL Verification Team 
Thank you for the report!