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
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