where condition and having clause cause the wrong result

[aytrack]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table if exists tc6a2e63d;
CREATE TABLE `tc6a2e63d` (
  `col_19` bigint(20) unsigned DEFAULT NULL,
  KEY `idx_13` (`col_19`),
  UNIQUE KEY `idx_14` (`col_19`),
  UNIQUE KEY `idx_15` (`col_19`),
  KEY `idx_16` (`col_19`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COLLATE=gbk_bin;
 insert into tc6a2e63d values (18266525336711149209);
 select   `tc6a2e63d`.`col_19` AS `r0` FROM `tc6a2e63d` where col_19 > 10 GROUP BY `tc6a2e63d`.`col_19` having  `tc6a2e63d`.`col_19`>9223372036854775807;

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

[17:52:40]MySQL root:test>  select   `tc6a2e63d`.`col_19` AS `r0` FROM `tc6a2e63d` where col_19 > 10 GROUP BY `tc6a2e63d`.`col_19` having  `tc6a2e63d`.`col_19`>9223372036854775807;
+----------------------+
| r0                   |
+----------------------+
| 18266525336711149209 |
+----------------------+

3. What did you see instead (Required)

[17:53:28]TiDB root:test>  select   `tc6a2e63d`.`col_19` AS `r0` FROM `tc6a2e63d` where col_19 > 10 GROUP BY `tc6a2e63d`.`col_19` having `tc6a2e63d`.`col_19`>9223372036854775807;
+----+
| r0 |
+----+
+----+
0 rows in set
Time: 0.044s
[17:54:42]TiDB root:test> explain select   `tc6a2e63d`.`col_19` AS `r0` FROM `tc6a2e63d` where col_19 > 10 GROUP BY `tc6a2e63d`.`col_19` having  `tc6a2e63d`.`col_19`>9223372036854775807;
+----------------+---------+------+---------------+----------------------------------------------------------------------------------------------+
| id             | estRows | task | access object | operator info                                                                                |
+----------------+---------+------+---------------+----------------------------------------------------------------------------------------------+
| StreamAgg_10   | 1.00    | root |               | group by:test.tc6a2e63d.col_19, funcs:firstrow(test.tc6a2e63d.col_19)->test.tc6a2e63d.col_19 |
| └─TableDual_15 | 0.33    | root |               | rows:0                                                                                       |
+----------------+---------+------+---------------+----------------------------------------------------------------------------------------------+
2 rows in set
Time: 0.049s

4. What is your TiDB version? (Required)

[17:53:51]TiDB root:test> select tidb_version();
+-----------------------------------------------------------+
| tidb_version()                                            |
+-----------------------------------------------------------+
| Release Version: v8.1.0                                   |
| Edition: Community                                        |
| Git Commit Hash: c8e6662d10deed030ef864169ae27718f5a61991 |
| Git Branch: HEAD                                          |
| UTC Build Time: 2024-05-06 10:31:31                       |
| GoVersion: go1.21.4                                       |
| Race Enabled: false                                       |
| Check Table Before Drop: false                            |
| Store: tikv                                               |
+-----------------------------------------------------------+
1 row in set
Time: 0.047s

[aytrack]

similar issue #51280

[qw4990]

Fixed by #52225