Bug #118837 After having(col) in the query statement is changed to having min(col), the returned result is incorrect.
Submitted: 14 Aug 3:05 Modified: 18 Aug 15:41
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:05] Alice Alice 
Description:
After having(col) in the query statement is changed to having min(col), the returned result is incorrect.

mysql> SELECT t7.c0 FROM t7 WHERE CAST(t7.c0 AS SIGNED) GROUP BY t7.c0, CAST(t7.c0 AS SIGNED), INSERT(SYSTEM_USER(), ((t7.c0) IS NOT NULL), t7.c0, (((('2005630594')OR(CAST(0.8556818850770639 AS DECIMAL)))) IS NOT NULL)) HAVING t7.c0 ORDER BY (CASE (('1')^(t7.c0)) WHEN -392222765 THEN '718035651' WHEN false THEN ((-898937006)NOT LIKE('*YA')) ELSE 576863694 END );
+------+
| c0   |
+------+
| 6    |
| 2    |
| 2    |
| 8    |
| 2    |
+------+
5 rows in set, 1 warning (0.00 sec)

mysql> SELECT t7.c0 FROM t7 WHERE CAST(t7.c0 AS SIGNED) GROUP BY t7.c0, CAST(t7.c0 AS SIGNED), INSERT(SYSTEM_USER(), ((t7.c0) IS NOT NULL), t7.c0, (((('2005630594')OR(CAST(0.8556818850770639 AS DECIMAL)))) IS NOT NULL)) HAVING MIN(t7.c0) ORDER BY (CASE (('1')^(t7.c0)) WHEN -392222765 THEN '718035651' WHEN false THEN ((-898937006)NOT LIKE('*YA')) ELSE 576863694 END );

+------+
| c0   |
+------+
| 6    |
| 2    |
| 2    |
| 8    |
+------+
4 rows in set, 1 warning (0.00 sec)

How to repeat:
DROP TABLE IF EXISTS `t7`;
CREATE TABLE `t7` (
  `c0` char(1) DEFAULT '{'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT;

INSERT INTO `t7` VALUES ('6'),('⓫'),('2'),('2'),('8'),(NULL),('0'),('0'),('0'),('2'),('0'),('0');

mysql> SELECT t7.c0 FROM t7 WHERE CAST(t7.c0 AS SIGNED) GROUP BY t7.c0, CAST(t7.c0 AS SIGNED), INSERT(SYSTEM_USER(), ((t7.c0) IS NOT NULL), t7.c0, (((('2005630594')OR(CAST(0.8556818850770639 AS DECIMAL)))) IS NOT NULL)) HAVING t7.c0 ORDER BY (CASE (('1')^(t7.c0)) WHEN -392222765 THEN '718035651' WHEN false THEN ((-898937006)NOT LIKE('*YA')) ELSE 576863694 END );
+------+
| c0   |
+------+
| 6    |
| 2    |
| 2    |
| 8    |
| 2    |
+------+
5 rows in set, 1 warning (0.00 sec)

mysql> SELECT t7.c0 FROM t7 WHERE CAST(t7.c0 AS SIGNED) GROUP BY t7.c0, CAST(t7.c0 AS SIGNED), INSERT(SYSTEM_USER(), ((t7.c0) IS NOT NULL), t7.c0, (((('2005630594')OR(CAST(0.8556818850770639 AS DECIMAL)))) IS NOT NULL)) HAVING MIN(t7.c0) ORDER BY (CASE (('1')^(t7.c0)) WHEN -392222765 THEN '718035651' WHEN false THEN ((-898937006)NOT LIKE('*YA')) ELSE 576863694 END );

+------+
| c0   |
+------+
| 6    |
| 2    |
| 2    |
| 8    |
+------+
4 rows in set, 1 warning (0.00 sec)

execute plan comparation:
explain analyze SELECT t7.c0 FROM t7 WHERE CAST(t7.c0 AS SIGNED) GROUP BY t7.c0, CAST(t7.c0 AS SIGNED), INSERT(SYSTEM_USER(), ((t7.c0) IS NOT NULL), t7.c0, (((('2005630594')OR(CAST(0.8556818850770639 AS DECIMAL)))) IS NOT NULL)) HAVING t7.c0 ORDER BY (CASE (('1')^(t7.c0)) WHEN -392222765 THEN '718035651' WHEN false THEN ((-898937006)NOT LIKE('*YA')) ELSE 576863694 END );
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Sort: `(case ('1' ^ t7.c0) when -(392222765) then '718035651' when false then (not((-(898937006) like '*YA'))) else 576863694 end)`  (actual time=0.0839..0.0844 rows=5 loops=1)
    -> Filter: (0 <> t7.c0)  (actual time=0.0594..0.0609 rows=5 loops=1)
        -> Table scan on <temporary>  (cost=2.87..5.3 rows=12) (actual time=0.0584..0.0593 rows=5 loops=1)
            -> Temporary table with deduplication  (cost=2.65..2.65 rows=12) (actual time=0.0574..0.0574 rows=5 loops=1)
                -> Filter: (0 <> cast(t7.c0 as signed))  (cost=1.45 rows=12) (actual time=0.0186..0.0322 rows=5 loops=1)
                    -> Table scan on t7  (cost=1.45 rows=12) (actual time=0.017..0.0255 rows=12 loops=1)
 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

explain analyze SELECT t7.c0 FROM t7 WHERE CAST(t7.c0 AS SIGNED) GROUP BY t7.c0, CAST(t7.c0 AS SIGNED), INSERT(SYSTEM_USER(), ((t7.c0) IS NOT NULL), t7.c0, (((('2005630594')OR(CAST(0.8556818850770639 AS DECIMAL)))) IS NOT NULL)) HAVING MIN(t7.c0) ORDER BY (CASE (('1')^(t7.c0)) WHEN -392222765 THEN '718035651' WHEN false THEN ((-898937006)NOT LIKE('*YA')) ELSE 576863694 END );
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Sort: `(case ('1' ^ t7.c0) when -(392222765) then '718035651' when false then (not((-(898937006) like '*YA'))) else 576863694 end)`  (actual time=0.0979..0.0984 rows=4 loops=1)
    -> Filter: (0 <> `min(t7.c0)`)  (actual time=0.0681..0.0694 rows=4 loops=1)
        -> Table scan on <temporary>  (actual time=0.0655..0.0663 rows=4 loops=1)
            -> Aggregate using temporary table  (actual time=0.0646..0.0646 rows=4 loops=1)
                -> Filter: (0 <> cast(t7.c0 as signed))  (cost=1.45 rows=12) (actual time=0.0197..0.0333 rows=5 loops=1)
                    -> Table scan on t7  (cost=1.45 rows=12) (actual time=0.0174..0.0257 rows=12 loops=1)
 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
[18 Aug 15:41] MySQL Verification Team 
Thanks for the report.

Because you GROUP BY t7.c0, every group contains rows with the same value of t7.c0. Thus, MIN(t7.c0) for the group is t7.c0. So, HAVING MIN(t7.c0) is functionally equivalent to HAVING t7.c0 in this context and we have different output hence this is a verified bug.

mysql> SELECT t7.c0 FROM t7 WHERE CAST(t7.c0 AS SIGNED) GROUP BY t7.c0, CAST(t7.c0 AS SIGNED), INSERT(SYSTEM_USER(), ((t7.c0) IS NOT NULL), t7.c0, (((('2005630594')OR(CAST(0.8556818850770639 AS DECIMAL)))) IS NOT NULL)) HAVING t7.c0 ORDER BY (CASE (('1')^(t7.c0)) WHEN -392222765 THEN '718035651' WHEN false THEN ((-898937006)NOT LIKE('*YA')) ELSE 576863694 END );
+------+
| c0   |
+------+
| 6    |
| 2    |
| 2    |
| 8    |
| 2    |
+------+
5 rows in set, 1 warning (0.001 sec)

mysql> SELECT t7.c0 FROM t7 WHERE CAST(t7.c0 AS SIGNED) GROUP BY t7.c0, CAST(t7.c0 AS SIGNED), INSERT(SYSTEM_USER(), ((t7.c0) IS NOT NULL), t7.c0, (((('2005630594')OR(CAST(0.8556818850770639 AS DECIMAL)))) IS NOT NULL)) HAVING MIN(t7.c0) ORDER BY (CASE (('1')^(t7.c0)) WHEN -392222765 THEN '718035651' WHEN false THEN ((-898937006)NOT LIKE('*YA')) ELSE 576863694 END );
+------+
| c0   |
+------+
| 6    |
| 2    |
| 2    |
| 8    |
+------+
4 rows in set, 1 warning (0.001 sec)

mysql>