Suspicious Estimated Rows by HAVING
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
The same `SELECT` statement with `HAVING` clause returns bigger number of estimated rows than the same statement without `HAVING` clause.
### 1. Minimal reproduce step (Required)
```sql
CREATE TABLE t0(c0 CHAR);
CREATE TABLE t1(c0 BLOB(10));
INSERT INTO t1(c0) VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j');
INSERT INTO t0 VALUES ('k'),('l'),('m'),('n'),('o'),('p'),('q'),('r'),('s'),('t');
ANALYZE TABLE t0;
ANALYZE TABLE t1;
EXPLAIN SELECT t1.c0 FROM t1 RIGHT OUTER JOIN t0 ON t1.c0 WHERE (1)NOT REGEXP(t1.c0) GROUP BY t1.c0; -- estimated rows: 6.4
EXPLAIN SELECT t1.c0 FROM t1 RIGHT OUTER JOIN t0 ON t1.c0 WHERE (1)NOT REGEXP(t1.c0) GROUP BY t1.c0 HAVING (t1.c0)REGEXP(NULL); -- estimated rows: 8
```
### 2. What did you expect to see? (Required)
The query plan of the first `SELECT`.
```
id estRows task access object operator info
HashAgg_8 6.40 root group by:database2.t1.c0, funcs:firstrow(database2.t1.c0)->database2.t1.c0
└─Selection_9 64.00 root not(regexp("1", database2.t1.c0))
└─HashJoin_11 80.00 root CARTESIAN right outer join
├─TableReader_14(Build) 8.00 root data:Selection_13
│ └─Selection_13 8.00 cop[tikv] database2.t1.c0
│ └─TableFullScan_12 10.00 cop[tikv] table:t1 keep order:false
└─TableReader_16(Probe) 10.00 root data:TableFullScan_15
└─TableFullScan_15 10.00 cop[tikv] table:t0 keep order:false
```
The query plan of the second `SELECT`.
```
id estRows task access object operator info
HashAgg_10 8.00 root group by:database2.t1.c0, funcs:firstrow(database2.t1.c0)->database2.t1.c0
└─HashJoin_12 80.00 root CARTESIAN inner join
├─Selection_16(Build) 6.40 root not(istrue_with_null(regexp("1", database2.t1.c0))), regexp(database2.t1.c0, NULL)
│ └─TableReader_15 8.00 root data:Selection_14
│ └─Selection_14 8.00 cop[tikv] database2.t1.c0
│ └─TableFullScan_13 10.00 cop[tikv] table:t1 keep order:false
└─TableReader_18(Probe) 10.00 root data:TableFullScan_17
└─TableFullScan_17 10.00 cop[tikv] table:t0 keep order:false
```
The difference between the two query plans is the Selection_9 in the first query plan is pushed down to Selection_16 under the Join.
In the first query, the HashJoin_11 returns 80 as two tables return 8 and 10 separately, but in the second query why the HashJoin_12 also return 80 with 6.4 and 10 returned by joined tables? As a result, the Selection_9 in the first query filters out more rows and the first query return smaller number of estimated rows than that of the second one.
Therefore, I think the second `SELECT` statement should not return bigger number of estimated rows than the first `SELECT` statement.
### 3. What did you see instead (Required)
### 4. What is your TiDB version? (Required)
```
| Release Version: v6.4.0-alpha-72-g3ef8352a5
Edition: Community
Git Commit Hash: 3ef8352a5754606e511ca89292a50612c289a501
Git Branch: master
UTC Build Time: 2022-10-14 12:56:17
GoVersion: go1.19.1
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: unistore |
```
Contributor guide
Assessment
This issue has not been assessed yet.