pingcap / pingcap/tidb

incorrect row estimation in having clause

Open
#51,280 1 comment 0 reactions 1 assignee Claimed by @time-and-fate View on GitHub
epic/cardinality-estimation fuzz/randomtest severity/moderate sig/planner type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)

``` SQL
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)
``` SQL

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

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.