incorrect row estimation in having clause

[wjhuang2016]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `t350f8f71` (
  `col_65` time NOT NULL DEFAULT '14:08:47',
  `col_66` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
INSERT INTO `t350f8f71` VALUES('19:46:28','1976-11-22 00:00:00'),('19:57:28','1976-11-22 00:00:00'),('06:25:03','1976-11-22 00:00:00'),('12:14:25','1976-11-22 00:00:00'),('20:18:16','1976-11-22 00:00:00'),('02:47:14','1976-11-22 00:00:00'),('23:53:19','1976-11-22 00:00:00'),('00:23:37','1976-11-22 00:00:00'),('23:25:10','1976-11-22 00:00:00'),('23:35:47','1976-11-22 00:00:00'),('08:00:50','1977-07-15 00:00:00'),('20:08:37','2004-09-11 00:00:00'),('20:50:58','1987-10-27 00:00:00');

desc SELECT UCASE(`t350f8f71`.`col_65`) AS `r0` FROM `t350f8f71` WHERE `t350f8f71`.`col_65`>='13:51:17.00' GROUP BY `t350f8f71`.`col_65` HAVING 1 LIMIT 11957493;

desc SELECT UCASE(`t350f8f71`.`col_65`) AS `r0` FROM `t350f8f71` WHERE `t350f8f71`.`col_65`>='13:51:17.00' GROUP BY `t350f8f71`.`col_65` HAVING NOT (`t350f8f71`.`col_65` IN ('19:57:28.00','10:02:10.00','02:47:14.00','10:11:33.00')) LIMIT 11957493;

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

The estimated row of Selection_23 should be less than Selection_21

3. What did you see instead (Required)

mysql> desc SELECT UCASE(`t350f8f71`.`col_65`) AS `r0` FROM `t350f8f71` WHERE `t350f8f71`.`col_65`>='13:51:17.00' GROUP BY `t350f8f71`.`col_65` HAVING 1 LIMIT 11957493;
+--------------------------------+---------+-----------+-----------------+----------------------------------------------------------------------------------------------+
| id                             | estRows | task      | access object   | operator info                                                                                |
+--------------------------------+---------+-----------+-----------------+----------------------------------------------------------------------------------------------+
| Projection_9                   | 3.47    | root      |                 | ucase(cast(test.t350f8f71.col_65, var_string(10)))->Column#4                                 |
| └─Limit_12                     | 3.47    | root      |                 | offset:0, count:11957493                                                                     |
|   └─HashAgg_15                 | 3.47    | root      |                 | group by:test.t350f8f71.col_65, funcs:firstrow(test.t350f8f71.col_65)->test.t350f8f71.col_65 |
|     └─TableReader_22           | 4.33    | root      |                 | data:Selection_21                                                                            |
|       └─Selection_21           | 4.33    | cop[tikv] |                 | ge(test.t350f8f71.col_65, 13:51:17.000000)                                                   |
|         └─TableFullScan_20     | 13.00   | cop[tikv] | table:t350f8f71 | keep order:false, stats:pseudo                                                               |
+--------------------------------+---------+-----------+-----------------+----------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

mysql> desc SELECT UCASE(`t350f8f71`.`col_65`) AS `r0` FROM `t350f8f71` WHERE `t350f8f71`.`col_65`>='13:51:17.00' GROUP BY `t350f8f71`.`col_65` HAVING NOT (`t350f8f71`.`col_65` IN ('19:57:28.00','10:02:10.00','02:47:14.00','10:11:33.00')) LIMIT 11957493;
+--------------------------------+---------+-----------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| id                             | estRows | task      | access object   | operator info                                                                                                                                  |
+--------------------------------+---------+-----------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_11                  | 3.73    | root      |                 | ucase(cast(test.t350f8f71.col_65, var_string(10)))->Column#5                                                                                   |
| └─Limit_14                     | 3.73    | root      |                 | offset:0, count:11957493                                                                                                                       |
|   └─HashAgg_17                 | 3.73    | root      |                 | group by:test.t350f8f71.col_65, funcs:firstrow(test.t350f8f71.col_65)->test.t350f8f71.col_65                                                   |
|     └─TableReader_24           | 4.66    | root      |                 | data:Selection_23                                                                                                                              |
|       └─Selection_23           | 4.66    | cop[tikv] |                 | ge(test.t350f8f71.col_65, 13:51:17.000000), not(in(test.t350f8f71.col_65, 19:57:28.000000, 10:02:10.000000, 02:47:14.000000, 10:11:33.000000)) |
|         └─TableFullScan_22     | 13.00   | cop[tikv] | table:t350f8f71 | keep order:false, stats:pseudo                                                                                                                 |
+--------------------------------+---------+-----------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

1fc92b32b05e3f2f04b9d321a9e224b9125a4a82

[qw4990]

@time-and-fate Please help take a look at this issue