Bug #118369 "having column" and "having MIN(column)" return different result.something wrong during Aggregate using temporary table

• Submitted: 5 Jun 11:21
• Modified: 5 Jun 13:08
• 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

[5 Jun 11:21] SamonBing SamonBing

Description:
the query with "having MIN(column)" clause return a wrong result;

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