Bug #102875 Tree EXPLAIN shows different row estimate for filtering when sorting is used
Submitted: 9 Mar 8:30 Modified: 9 Mar 8:55
Reporter: Øystein Grøvlen Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.23 OS:Any
Assigned to: CPU Architecture:Any

[9 Mar 8:30] Øystein Grøvlen
Description:
For "EXPLAIN format=tree", the row estimate after filtering is not correct when sorting is used:

mysql> explain format=tree select * from t1 where b=1 or b is null\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: ((t1.b = 1) or (t1.b is null))  (cost=0.85 rows=2)
    -> Table scan on t1  (cost=0.85 rows=6)

1 row in set (0.00 sec)

mysql> explain format=tree select * from t1 where b=1 or b is null order by a\G
*************************** 1. row ***************************
EXPLAIN: -> Sort: t1.a  (cost=0.85 rows=6)
    -> Filter: ((t1.b = 1) or (t1.b is null))  (cost=0.85 rows=6)
        -> Table scan on t1  (cost=0.85 rows=6)

Notice how rows estimate is different for the Filter line.

How to repeat:
create table t1(a int, b int);
insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2);
ANALYZE TABLE t1;
explain format=tree select * from t1 where b=1 or b is null\G
explain format=tree select * from t1 where b=1 or b is null order by a\G
[9 Mar 8:55] MySQL Verification Team
Hello Øystein,

Thank you for the report and feedback.
Verified as described. 

regards,
Umesh