Bug #108852 Suspicious Estimated Rows by DISTINCTROW
Submitted: 22 Oct 2022 2:45 Modified: 22 Oct 2022 5:41
Reporter: JINSHENG BA Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:8.0.31 OS:Ubuntu (20.04)
Assigned to: CPU Architecture:x86
Tags: cardinality estimation

[22 Oct 2022 2:45] JINSHENG BA 
Description:
See this test case. The second SELECT (ALL) returns more estimated rows than the first SELECT (DISTINCTROW) which is suspicious.

CREATE TABLE t0(c0 INT, c1 INT UNIQUE) ;
INSERT INTO t0 VALUES(-1, NULL), (1, 2), (NULL, NULL), (3, 4);
ANALYZE TABLE t0 UPDATE HISTOGRAM ON c0, c1;

EXPLAIN SELECT DISTINCTROW t0.c0 FROM t0 WHERE t0.c1; -- rows: 4
EXPLAIN SELECT ALL t0.c0 FROM t0 WHERE t0.c1; -- rows: 3

How to repeat:
$ sudo docker run -it -p 13306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql
$ mysql --host 127.0.0.1 -u root --password=123456 --port 13306

Then create a database and type the above test case.

Suggested fix:
I expect the second SELECT should return no less rows than the first SELECT as ALL should return more rows than DISTINCTROW.
[22 Oct 2022 5:41] MySQL Verification Team 
Hello Jinsheng Ba,

Thank you for the report and test case.

regards,
Umesh