unexpected query result using aggregation

[wjhuang2016]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

use test;
CREATE TABLE `t910beff5` (
  `col_40` tinyint unsigned DEFAULT '95',
  `col_41` tinytext COLLATE gbk_bin NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk COLLATE=gbk_bin;
INSERT INTO `t910beff5` VALUES(7,'CWHObI8-!amnYA'),(1,'g+!OHa@OTdsA2#JN'),(249,'cwx!P4xaX)U'),(118,'mlDJX^n+'),(0,'EOZ9*QUsH%qi)%'),(183,'9*Lzmwg%pxy'),(139,'!ME5dBDrOG5'),(127,'BNb8SajqZ'),(209,'IWS^j'),(68,'U9v99'),(187,'dE*Zzjz#0&'),(10,'iC'),(87,'jIgUfpWzE9#oQhn&#&'),(4,'BLmM2'),(153,'(BG(8nIFNyE$%i'),(51,'EDhm)%Fie~qReM');

SELECT AVG(DISTINCT `t910beff5`.`col_40`) AS `r0` FROM `t910beff5` WHERE `t910beff5`.`col_41`>='D1$9+VTpEe)' OR `t910beff5`.`col_41` BETWEEN 'znRD*2pkmtm4' AND 'PBueg(&tWY%dzsT(_' GROUP BY `t910beff5`.`col_40`,`t910beff5`.`col_41` HAVING `t910beff5`.`col_41`='cwx!P4xaX)U';

2. What did you expect to see? (Required)

One row

3. What did you see instead (Required)

mysql> SELECT AVG(DISTINCT `t910beff5`.`col_40`) AS `r0` FROM `t910beff5` WHERE `t910beff5`.`col_41`>='D1$9+VTpEe)' OR `t910beff5`.`col_41` BETWEEN 'znRD*2pkmtm4' AND 'PBueg(&tWY%dzsT(_' GROUP BY `t910beff5`.`col_40`,`t910beff5`.`col_41` HAVING `t910beff5`.`col_41`='cwx!P4xaX)U';
Empty set (0.02 sec)

4. What is your TiDB version? (Required)

e87b8a9

[hawkingrei]

v8.1.2 is good
v8.5.6 is bad

[hawkingrei]

v8.2.0 is good

[hawkingrei]

v8.3.0 is good.

[hawkingrei]

v8.4.0 is bad

[hawkingrei]

caused by #56136

[hawkingrei]

the bad plan

+-----------------------------+----------+-----------+-----------------+---------------------------------------------------------------------------------------------------------------------------------------+
| id                          | estRows  | task      | access object   | operator info                                                                                                                         |
+-----------------------------+----------+-----------+-----------------+---------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_8                   | 1.00     | root      |                 | group by:Column#7, Column#8, funcs:avg(distinct Column#6)->Column#4                                                                   |
| └─Projection_12             | 0.00     | root      |                 | cast(test.t910beff5.col_40, decimal(3,0) UNSIGNED BINARY)->Column#6, test.t910beff5.col_40->Column#7, test.t910beff5.col_41->Column#8 |
|   └─TableReader_11          | 0.00     | root      |                 | data:Selection_10                                                                                                                     |
|     └─Selection_10          | 0.00     | cop[tikv] |                 | eq(test.t910beff5.col_41, "cwx!P4xaX)U"), ge(test.t910beff5.col_41, "znRD*2pkmtm4"), le(test.t910beff5.col_41, "PBueg(&tWY%dzsT(_")   |
|       └─TableFullScan_9     | 10000.00 | cop[tikv] | table:t910beff5 | keep order:false, stats:pseudo                                                                                                        |
+-----------------------------+----------+-----------+-----------------+---------------------------------------------------------------------------------------------------------------------------------------+

the good plan

+-----------------------------+----------+-----------+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                          | estRows  | task      | access object   | operator info                                                                                                                                                                          |
+-----------------------------+----------+-----------+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_8                   | 8.00     | root      |                 | group by:Column#7, Column#8, funcs:avg(distinct Column#6)->Column#4                                                                                                                    |
| └─Projection_12             | 10.00    | root      |                 | cast(test.t910beff5.col_40, decimal(3,0) UNSIGNED BINARY)->Column#6, test.t910beff5.col_40->Column#7, test.t910beff5.col_41->Column#8                                                  |
|   └─TableReader_11          | 10.00    | root      |                 | data:Selection_10                                                                                                                                                                      |
|     └─Selection_10          | 10.00    | cop[tikv] |                 | eq(test.t910beff5.col_41, "cwx!P4xaX)U"), or(ge(test.t910beff5.col_41, "D1$9+VTpEe)"), and(ge(test.t910beff5.col_41, "znRD*2pkmtm4"), le(test.t910beff5.col_41, "PBueg(&tWY%dzsT(_"))) |
|       └─TableFullScan_9     | 10000.00 | cop[tikv] | table:t910beff5 | keep order:false, stats:pseudo                                                                                                                                                         |
+-----------------------------+----------+-----------+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+