Bug #118836 adding a having clause that return 1/TRUE, the query returns a different and wrong empty result.

Submitted:	14 Aug 2025 3:01	Modified:	18 Aug 2025 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

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 |
+----------+-----------------------------------------------+

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>

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>

Thank you for the report!

Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.