WIP10.6-mdev8306

Checking whether the join cardinality estimates are accurate
should only be done when the order by limit optimization is
enabled.

4 files changed, 313 insertions, 285 deletions

diff --git a/mysql-test/include/join_cardinality.inc b/mysql-test/include/join_cardinality.inc
index 8d258a95324..1385a2f7899 100644
--- a/mysql-test/include/join_cardinality.inc
+++ b/mysql-test/include/join_cardinality.inc
@@ -2,42 +2,42 @@
 --echo # BASIC CASES
 --echo #
 
-EXPLAIN SELECT * FROM t1 WHERE t1.b <> 10;
+EXPLAIN SELECT * FROM t1 WHERE t1.b <> 10 ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
-EXPLAIN SELECT * FROM t1 WHERE t1.b > 10;
+EXPLAIN SELECT * FROM t1 WHERE t1.b > 10 ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
-EXPLAIN SELECT * FROM t1 WHERE t1.b < 10;
+EXPLAIN SELECT * FROM t1 WHERE t1.b < 10 ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
-EXPLAIN SELECT * FROM t1 WHERE t1.b = 10;
+EXPLAIN SELECT * FROM t1 WHERE t1.b = 10 ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
-EXPLAIN SELECT * FROM t1 WHERE t1.b+2 = 10;
+EXPLAIN SELECT * FROM t1 WHERE t1.b+2 = 10 ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
-EXPLAIN SELECT * FROM t1 WHERE a IN (1,2,3);
+EXPLAIN SELECT * FROM t1 WHERE a IN (1,2,3) ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
-EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 and 5;
+EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 and 5 ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
 --echo # Equi-join condition
-EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.b;
+EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.b ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
 --echo # CASE where selectivity estimates are not accurate
 
-EXPLAIN SELECT * FROM t1 WHERE abs(t1.b) = 10;
+EXPLAIN SELECT * FROM t1 WHERE abs(t1.b) = 10 ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
@@ -46,7 +46,7 @@ FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 --echo # selectivity for the predicate t1.c=10
 --echo #
 
-EXPLAIN SELECT * FROM t1 WHERE t1.c = 10;
+EXPLAIN SELECT * FROM t1 WHERE t1.c = 10 ORDER BY t1.a LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
@@ -54,15 +54,16 @@ FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 --echo # BASIC CASES FOR AND CONJUNCT
 --echo #
 
-EXPLAIN SELECT * FROM t1 WHERE a >= 5 and b <= 6;
+EXPLAIN SELECT * FROM t1 WHERE a >= 5 and b <= 6 ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
-EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 AND 5 AND b <= 6;
+EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 AND 5 AND b <= 6
+ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
-EXPLAIN SELECT * FROM t1,t2 WHERE t1.a > 5 AND t2.b < 10;
+EXPLAIN SELECT * FROM t1,t2 WHERE t1.a > 5 AND t2.b < 10 ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
@@ -70,22 +71,24 @@ FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 --echo # BASIC CASES FOR OR CONJUNCT
 --echo #
 
-EXPLAIN SELECT * FROM t1 WHERE a >= 5 OR a <= 2;
+EXPLAIN SELECT * FROM t1 WHERE a >= 5 OR a <= 2 ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
-EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 AND 5 OR a <= 0;
+EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 AND 5 OR a <= 0
+ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
 --echo # The cardinality here would be not accurate because for the
 --echo # OR conjunct all predicates can't be resolved to one column
 
-EXPLAIN SELECT * FROM t1,t2 WHERE t1.a > 5 OR t2.b < 10;
+EXPLAIN SELECT * FROM t1,t2 WHERE t1.a > 5 OR t2.b < 10
+ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
-EXPLAIN SELECT * FROM t1 WHERE a >= 5 OR b <= 6;
+EXPLAIN SELECT * FROM t1 WHERE a >= 5 OR b <= 6 ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
@@ -93,15 +96,18 @@ FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 --echo # COMPLEX CASES WITH AND-OR CONJUNCTS
 --echo #
 
-EXPLAIN SELECT * from t1,t2 WHERE t1.a=t2.b and (t1.a > 5 or t1.a < 10);
+EXPLAIN SELECT * from t1,t2 WHERE t1.a=t2.b and (t1.a > 5 or t1.a < 10)
+ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
-EXPLAIN SELECT * from t1,t2 WHERE t1.a=t2.b and (t1.a > 5 or t2.b < 10);
+EXPLAIN SELECT * from t1,t2 WHERE t1.a=t2.b and (t1.a > 5 or t2.b < 10)
+ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
-EXPLAIN SELECT * from t1,t2 WHERE t1.a=t2.b and (t1.a > 5 or t2.a < 10);
+EXPLAIN SELECT * from t1,t2 WHERE t1.a=t2.b and (t1.a > 5 or t2.a < 10)
+ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
@@ -109,11 +115,13 @@ EXPLAIN SELECT * FROM t1
 WHERE ((t1.a > 20  and t1.a < 30) or (t1.a > 40 and t1.a < 50)) AND
 ((t1.a > 25 and t1.a < 35) or (t1.a > 45 and t1.a < 55)) OR
 ((t1.a > 120 and t1.a < 130) or (t1.a > 140 and t1.a < 150)) AND
-((t1.a > 125 and t1.a < 135) or (t1.a > 145 and t1.a < 155));
+((t1.a > 125 and t1.a < 135) or (t1.a > 145 and t1.a < 155))
+ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
-EXPLAIN EXTENDED SELECT * from t1  WHERE ((t1.a IS NULL) or (t1.a = 5));
+EXPLAIN EXTENDED SELECT * from t1  WHERE ((t1.a IS NULL) or (t1.a = 5))
+ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
@@ -121,16 +129,19 @@ FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 --echo # Test with subquery
 --echo #
 
-EXPLAIN SELECT * from t1  WHERE t1.a = (select t2.a from t2 limit 1);
+EXPLAIN SELECT * from t1  WHERE t1.a = (select t2.a from t2 limit 1)
+ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
 EXPLAIN SELECT * from t1
-WHERE t1.a = (select t2.a from t2 where t1.b=t2.b limit 1);
+WHERE t1.a = (select t2.a from t2 where t1.b=t2.b limit 1)
+ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
 EXPLAIN SELECT * from t1
-WHERE t1.a = (select t2.a from t2 where t2.b > 10 limit 1) AND abs(t1.b)= 11;
+WHERE t1.a = (select t2.a from t2 where t2.b > 10 limit 1) AND abs(t1.b)= 11
+ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
diff --git a/mysql-test/main/join_cardinality.result b/mysql-test/main/join_cardinality.result
index 4249ec27e1b..3edd7f64c63 100644
--- a/mysql-test/main/join_cardinality.result
+++ b/mysql-test/main/join_cardinality.result
@@ -1,6 +1,7 @@
 SET @save_optimizer_switch=@@optimizer_switch;
 SET optimizer_trace=1;
 SET optimizer_switch='rowid_filter=off';
+set optimizer_switch='cost_based_order_by_limit=on';
 CREATE TABLE t1(a INT, b INT, c INT, KEY(b), KEY(a));
 INSERT INTO t1 SELECT seq, seq, seq from seq_1_to_100;
 CREATE TABLE t2(a INT, b INT, key(b));
@@ -12,63 +13,63 @@ INSERT INTO t2 SELECT seq, seq from seq_1_to_100;
 #
 # BASIC CASES
 #
-EXPLAIN SELECT * FROM t1 WHERE t1.b <> 10;
+EXPLAIN SELECT * FROM t1 WHERE t1.b <> 10 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	b	NULL	NULL	NULL	100	Using where
+1	SIMPLE	t1	range	b	b	5	NULL	100	Using index condition; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-EXPLAIN SELECT * FROM t1 WHERE t1.b > 10;
+EXPLAIN SELECT * FROM t1 WHERE t1.b > 10 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	b	NULL	NULL	NULL	100	Using where
+1	SIMPLE	t1	range	b	b	5	NULL	95	Using index condition; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-EXPLAIN SELECT * FROM t1 WHERE t1.b < 10;
+EXPLAIN SELECT * FROM t1 WHERE t1.b < 10 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	b	b	5	NULL	5	Using index condition
+1	SIMPLE	t1	range	b	b	5	NULL	5	Using index condition; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-EXPLAIN SELECT * FROM t1 WHERE t1.b = 10;
+EXPLAIN SELECT * FROM t1 WHERE t1.b = 10 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	b	b	5	const	1	
+1	SIMPLE	t1	ref	b	b	5	const	1	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-EXPLAIN SELECT * FROM t1 WHERE t1.b+2 = 10;
+EXPLAIN SELECT * FROM t1 WHERE t1.b+2 = 10 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     false
 ]
-EXPLAIN SELECT * FROM t1 WHERE a IN (1,2,3);
+EXPLAIN SELECT * FROM t1 WHERE a IN (1,2,3) ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	a	a	5	NULL	3	Using index condition
+1	SIMPLE	t1	range	a	a	5	NULL	3	Using index condition; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 and 5;
+EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 and 5 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	a	a	5	NULL	3	Using index condition
+1	SIMPLE	t1	range	a	a	5	NULL	3	Using index condition; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -76,10 +77,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
     true
 ]
 # Equi-join condition
-EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.b;
+EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.b ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	100	Using where
-1	SIMPLE	t1	ref	a	a	5	test.t2.b	1	
+1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	100	Using where; Using filesort
+1	SIMPLE	t2	ref	b	b	5	test.t1.a	1	
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -87,9 +88,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
     true
 ]
 # CASE where selectivity estimates are not accurate
-EXPLAIN SELECT * FROM t1 WHERE abs(t1.b) = 10;
+EXPLAIN SELECT * FROM t1 WHERE abs(t1.b) = 10 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -100,9 +101,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 # The cardinality here would be not accurate because we don't have
 # selectivity for the predicate t1.c=10
 #
-EXPLAIN SELECT * FROM t1 WHERE t1.c = 10;
+EXPLAIN SELECT * FROM t1 WHERE t1.c = 10 ORDER BY t1.a LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -112,28 +113,29 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 #
 # BASIC CASES FOR AND CONJUNCT
 #
-EXPLAIN SELECT * FROM t1 WHERE a >= 5 and b <= 6;
+EXPLAIN SELECT * FROM t1 WHERE a >= 5 and b <= 6 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	b,a	b	5	NULL	3	Using index condition; Using where
+1	SIMPLE	t1	range	b,a	b	5	NULL	3	Using index condition; Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 AND 5 AND b <= 6;
+EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 AND 5 AND b <= 6
+ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	b,a	b	5	NULL	3	Using index condition; Using where
+1	SIMPLE	t1	range	b,a	b	5	NULL	3	Using index condition; Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-EXPLAIN SELECT * FROM t1,t2 WHERE t1.a > 5 AND t2.b < 10;
+EXPLAIN SELECT * FROM t1,t2 WHERE t1.a > 5 AND t2.b < 10 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	100	Using where; Using filesort
 1	SIMPLE	t2	range	b	b	5	NULL	5	Using index condition
-1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	100	Using where; Using join buffer (flat, BNL join)
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -143,18 +145,19 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 #
 # BASIC CASES FOR OR CONJUNCT
 #
-EXPLAIN SELECT * FROM t1 WHERE a >= 5 OR a <= 2;
+EXPLAIN SELECT * FROM t1 WHERE a >= 5 OR a <= 2 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	100	Using where
+1	SIMPLE	t1	range	a	a	5	NULL	99	Using index condition; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 AND 5 OR a <= 0;
+EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 AND 5 OR a <= 0
+ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	a	a	5	NULL	4	Using index condition
+1	SIMPLE	t1	range	a	a	5	NULL	4	Using index condition; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -163,9 +166,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 ]
 # The cardinality here would be not accurate because for the
 # OR conjunct all predicates can't be resolved to one column
-EXPLAIN SELECT * FROM t1,t2 WHERE t1.a > 5 OR t2.b < 10;
+EXPLAIN SELECT * FROM t1,t2 WHERE t1.a > 5 OR t2.b < 10
+ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	100	
+1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	100	Using temporary; Using filesort
 1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	100	Using where; Using join buffer (flat, BNL join)
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
@@ -173,9 +177,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     false
 ]
-EXPLAIN SELECT * FROM t1 WHERE a >= 5 OR b <= 6;
+EXPLAIN SELECT * FROM t1 WHERE a >= 5 OR b <= 6 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	b,a	NULL	NULL	NULL	100	Using where
+1	SIMPLE	t1	ALL	b,a	NULL	NULL	NULL	100	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -185,30 +189,33 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 #
 # COMPLEX CASES WITH AND-OR CONJUNCTS
 #
-EXPLAIN SELECT * from t1,t2 WHERE t1.a=t2.b and (t1.a > 5 or t1.a < 10);
+EXPLAIN SELECT * from t1,t2 WHERE t1.a=t2.b and (t1.a > 5 or t1.a < 10)
+ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	100	Using where
-1	SIMPLE	t1	ref	a	a	5	test.t2.b	1	
+1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	100	Using where; Using filesort
+1	SIMPLE	t2	ref	b	b	5	test.t1.a	1	
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-EXPLAIN SELECT * from t1,t2 WHERE t1.a=t2.b and (t1.a > 5 or t2.b < 10);
+EXPLAIN SELECT * from t1,t2 WHERE t1.a=t2.b and (t1.a > 5 or t2.b < 10)
+ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	100	Using where
-1	SIMPLE	t1	ref	a	a	5	test.t2.b	1	
+1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	100	Using where; Using filesort
+1	SIMPLE	t2	ref	b	b	5	test.t1.a	1	
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-EXPLAIN SELECT * from t1,t2 WHERE t1.a=t2.b and (t1.a > 5 or t2.a < 10);
+EXPLAIN SELECT * from t1,t2 WHERE t1.a=t2.b and (t1.a > 5 or t2.a < 10)
+ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	100	Using where
-1	SIMPLE	t1	ref	a	a	5	test.t2.b	1	
+1	SIMPLE	t1	range	a	a	5	NULL	100	Using index condition; Using filesort
+1	SIMPLE	t2	ref	b	b	5	test.t1.a	1	Using where
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -219,20 +226,22 @@ EXPLAIN SELECT * FROM t1
 WHERE ((t1.a > 20  and t1.a < 30) or (t1.a > 40 and t1.a < 50)) AND
 ((t1.a > 25 and t1.a < 35) or (t1.a > 45 and t1.a < 55)) OR
 ((t1.a > 120 and t1.a < 130) or (t1.a > 140 and t1.a < 150)) AND
-((t1.a > 125 and t1.a < 135) or (t1.a > 145 and t1.a < 155));
+((t1.a > 125 and t1.a < 135) or (t1.a > 145 and t1.a < 155))
+ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	a	a	5	NULL	6	Using index condition
+1	SIMPLE	t1	range	a	a	5	NULL	6	Using index condition; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-EXPLAIN EXTENDED SELECT * from t1  WHERE ((t1.a IS NULL) or (t1.a = 5));
+EXPLAIN EXTENDED SELECT * from t1  WHERE ((t1.a IS NULL) or (t1.a = 5))
+ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ref_or_null	a	a	5	const	2	100.00	Using index condition
+1	SIMPLE	t1	ref_or_null	a	a	5	const	2	100.00	Using index condition; Using filesort
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`a` is null or `test`.`t1`.`a` = 5
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`a` is null or `test`.`t1`.`a` = 5 order by `test`.`t1`.`c` limit 5
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -242,9 +251,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 #
 # Test with subquery
 #
-EXPLAIN SELECT * from t1  WHERE t1.a = (select t2.a from t2 limit 1);
+EXPLAIN SELECT * from t1  WHERE t1.a = (select t2.a from t2 limit 1)
+ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ref	a	a	5	const	1	Using where
+1	PRIMARY	t1	ref	a	a	5	const	1	Using where; Using filesort
 2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	100	
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
@@ -253,27 +263,27 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
     true
 ]
 EXPLAIN SELECT * from t1
-WHERE t1.a = (select t2.a from t2 where t1.b=t2.b limit 1);
+WHERE t1.a = (select t2.a from t2 where t1.b=t2.b limit 1)
+ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	100	Using where; Using filesort
 2	DEPENDENT SUBQUERY	t2	ref	b	b	5	test.t1.b	1	
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
-    false,
     false
 ]
 EXPLAIN SELECT * from t1
-WHERE t1.a = (select t2.a from t2 where t2.b > 10 limit 1) AND abs(t1.b)= 11;
+WHERE t1.a = (select t2.a from t2 where t2.b > 10 limit 1) AND abs(t1.b)= 11
+ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ref	a	a	5	const	1	Using where
+1	PRIMARY	t1	ref	a	a	5	const	1	Using where; Using filesort
 2	SUBQUERY	t2	range	b	b	5	NULL	95	Using index condition
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
-    true,
     false
 ]
 DROP TABLE t1,t2;
@@ -288,63 +298,63 @@ INSERT INTO t2 SELECT seq, seq from seq_1_to_100;
 #
 # BASIC CASES
 #
-EXPLAIN SELECT * FROM t1 WHERE t1.b <> 10;
+EXPLAIN SELECT * FROM t1 WHERE t1.b <> 10 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-EXPLAIN SELECT * FROM t1 WHERE t1.b > 10;
+EXPLAIN SELECT * FROM t1 WHERE t1.b > 10 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-EXPLAIN SELECT * FROM t1 WHERE t1.b < 10;
+EXPLAIN SELECT * FROM t1 WHERE t1.b < 10 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-EXPLAIN SELECT * FROM t1 WHERE t1.b = 10;
+EXPLAIN SELECT * FROM t1 WHERE t1.b = 10 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-EXPLAIN SELECT * FROM t1 WHERE t1.b+2 = 10;
+EXPLAIN SELECT * FROM t1 WHERE t1.b+2 = 10 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     false
 ]
-EXPLAIN SELECT * FROM t1 WHERE a IN (1,2,3);
+EXPLAIN SELECT * FROM t1 WHERE a IN (1,2,3) ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 and 5;
+EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 and 5 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -352,10 +362,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
     true
 ]
 # Equi-join condition
-EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.b;
+EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.b ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	100	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	100	Using where
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -363,9 +373,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
     true
 ]
 # CASE where selectivity estimates are not accurate
-EXPLAIN SELECT * FROM t1 WHERE abs(t1.b) = 10;
+EXPLAIN SELECT * FROM t1 WHERE abs(t1.b) = 10 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -376,9 +386,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 # The cardinality here would be not accurate because we don't have
 # selectivity for the predicate t1.c=10
 #
-EXPLAIN SELECT * FROM t1 WHERE t1.c = 10;
+EXPLAIN SELECT * FROM t1 WHERE t1.c = 10 ORDER BY t1.a LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -388,28 +398,29 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 #
 # BASIC CASES FOR AND CONJUNCT
 #
-EXPLAIN SELECT * FROM t1 WHERE a >= 5 and b <= 6;
+EXPLAIN SELECT * FROM t1 WHERE a >= 5 and b <= 6 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 AND 5 AND b <= 6;
+EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 AND 5 AND b <= 6
+ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-EXPLAIN SELECT * FROM t1,t2 WHERE t1.a > 5 AND t2.b < 10;
+EXPLAIN SELECT * FROM t1,t2 WHERE t1.a > 5 AND t2.b < 10 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where; Using filesort
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	100	Using where
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where; Using join buffer (flat, BNL join)
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -419,18 +430,19 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 #
 # BASIC CASES FOR OR CONJUNCT
 #
-EXPLAIN SELECT * FROM t1 WHERE a >= 5 OR a <= 2;
+EXPLAIN SELECT * FROM t1 WHERE a >= 5 OR a <= 2 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 AND 5 OR a <= 0;
+EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 AND 5 OR a <= 0
+ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -439,9 +451,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 ]
 # The cardinality here would be not accurate because for the
 # OR conjunct all predicates can't be resolved to one column
-EXPLAIN SELECT * FROM t1,t2 WHERE t1.a > 5 OR t2.b < 10;
+EXPLAIN SELECT * FROM t1,t2 WHERE t1.a > 5 OR t2.b < 10
+ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using temporary; Using filesort
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	100	Using where; Using join buffer (flat, BNL join)
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
@@ -449,9 +462,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     false
 ]
-EXPLAIN SELECT * FROM t1 WHERE a >= 5 OR b <= 6;
+EXPLAIN SELECT * FROM t1 WHERE a >= 5 OR b <= 6 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -461,29 +474,32 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 #
 # COMPLEX CASES WITH AND-OR CONJUNCTS
 #
-EXPLAIN SELECT * from t1,t2 WHERE t1.a=t2.b and (t1.a > 5 or t1.a < 10);
+EXPLAIN SELECT * from t1,t2 WHERE t1.a=t2.b and (t1.a > 5 or t1.a < 10)
+ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	100	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	100	Using where
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-EXPLAIN SELECT * from t1,t2 WHERE t1.a=t2.b and (t1.a > 5 or t2.b < 10);
+EXPLAIN SELECT * from t1,t2 WHERE t1.a=t2.b and (t1.a > 5 or t2.b < 10)
+ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	100	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	100	Using where
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-EXPLAIN SELECT * from t1,t2 WHERE t1.a=t2.b and (t1.a > 5 or t2.a < 10);
+EXPLAIN SELECT * from t1,t2 WHERE t1.a=t2.b and (t1.a > 5 or t2.a < 10)
+ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using temporary; Using filesort
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	100	Using where; Using join buffer (flat, BNL join)
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
@@ -495,20 +511,22 @@ EXPLAIN SELECT * FROM t1
 WHERE ((t1.a > 20  and t1.a < 30) or (t1.a > 40 and t1.a < 50)) AND
 ((t1.a > 25 and t1.a < 35) or (t1.a > 45 and t1.a < 55)) OR
 ((t1.a > 120 and t1.a < 130) or (t1.a > 140 and t1.a < 150)) AND
-((t1.a > 125 and t1.a < 135) or (t1.a > 145 and t1.a < 155));
+((t1.a > 125 and t1.a < 135) or (t1.a > 145 and t1.a < 155))
+ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-EXPLAIN EXTENDED SELECT * from t1  WHERE ((t1.a IS NULL) or (t1.a = 5));
+EXPLAIN EXTENDED SELECT * from t1  WHERE ((t1.a IS NULL) or (t1.a = 5))
+ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	1.00	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	1.00	Using where; Using filesort
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`a` is null or `test`.`t1`.`a` = 5
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`a` is null or `test`.`t1`.`a` = 5 order by `test`.`t1`.`c` limit 5
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -518,9 +536,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 #
 # Test with subquery
 #
-EXPLAIN SELECT * from t1  WHERE t1.a = (select t2.a from t2 limit 1);
+EXPLAIN SELECT * from t1  WHERE t1.a = (select t2.a from t2 limit 1)
+ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	100	Using where; Using filesort
 2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	100	
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
@@ -529,27 +548,27 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
     true
 ]
 EXPLAIN SELECT * from t1
-WHERE t1.a = (select t2.a from t2 where t1.b=t2.b limit 1);
+WHERE t1.a = (select t2.a from t2 where t1.b=t2.b limit 1)
+ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	100	Using where; Using filesort
 2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	100	Using where
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
-    false,
     false
 ]
 EXPLAIN SELECT * from t1
-WHERE t1.a = (select t2.a from t2 where t2.b > 10 limit 1) AND abs(t1.b)= 11;
+WHERE t1.a = (select t2.a from t2 where t2.b > 10 limit 1) AND abs(t1.b)= 11
+ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	100	Using where; Using filesort
 2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	100	Using where
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
-    true,
     false
 ]
 DROP TABLE t1,t2;
@@ -557,11 +576,11 @@ DROP TABLE t1,t2;
 # Combination with statistics from stat tables and
 # statistics from indexes
 #
-CREATE TABLE t1(a INT, b INT, KEY(a));
-INSERT INTO t1 SELECT seq, seq from seq_1_to_100;
-ANALYZE SELECT * from t1  WHERE t1.b > 3 AND t1.a=t1.b;
+CREATE TABLE t1(a INT, b INT, c INT, KEY(a));
+INSERT INTO t1 SELECT seq, seq, seq from seq_1_to_100;
+ANALYZE SELECT * from t1  WHERE t1.b > 3 AND t1.a=t1.b ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	100	100.00	98.00	97.00	Using where
+1	SIMPLE	t1	range	a	a	5	NULL	98	5.00	100.00	100.00	Using index condition; Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -572,27 +591,27 @@ ANALYZE TABLE t1 PERSISTENT FOR COLUMNS (b) INDEXES ();
 Table	Op	Msg_type	Msg_text
 test.t1	analyze	status	Engine-independent statistics collected
 test.t1	analyze	status	Table is already up to date
-ANALYZE SELECT * from t1  WHERE t1.b > 3 AND t1.a=10;
+ANALYZE SELECT * from t1  WHERE t1.b > 3 AND t1.a=10 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	SIMPLE	t1	ref	a	a	5	const	1	1.00	97.66	100.00	Using where
+1	SIMPLE	t1	ref	a	a	5	const	1	1.00	97.66	100.00	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-ANALYZE SELECT * from t1  WHERE t1.b > 3 AND t1.a=t1.b;
+ANALYZE SELECT * from t1  WHERE t1.b > 3 AND t1.a=t1.b ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	100	100.00	95.70	97.00	Using where
+1	SIMPLE	t1	range	a	a	5	NULL	98	5.00	97.66	100.00	Using index condition; Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-ANALYZE SELECT * from t1  WHERE t1.b > 3 OR t1.a=t1.b;
+ANALYZE SELECT * from t1  WHERE t1.b > 3 OR t1.a=t1.b ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	100.00	100.00	100.00	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	5.00	100.00	100.00	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -608,18 +627,18 @@ INSERT INTO t1 SELECT seq, seq, seq from seq_1_to_10;
 #
 # Tests with LIKE PREDICATES
 #
-EXPLAIN SELECT * from t1 WHERE t1.a LIKE '%2%';
+EXPLAIN SELECT * from t1 WHERE t1.a LIKE '%2%' ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     false
 ]
-EXPLAIN SELECT * from t1 WHERE t1.a LIKE '2%';
+EXPLAIN SELECT * from t1 WHERE t1.a LIKE '2%' ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	10	Using where
+1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	10	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -629,18 +648,18 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 #
 # Tests with NULL predicates
 #
-EXPLAIN SELECT * from t1 WHERE t1.a IS NULL;
+EXPLAIN SELECT * from t1 WHERE t1.a IS NULL ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	a	a	5	const	1	Using index condition
+1	SIMPLE	t1	ref	a	a	5	const	1	Using index condition; Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-EXPLAIN SELECT * from t1 WHERE t1.a IS NOT NULL;
+EXPLAIN SELECT * from t1 WHERE t1.a IS NOT NULL ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	10	Using where
+1	SIMPLE	t1	range	a	a	5	NULL	10	Using index condition; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -650,54 +669,54 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 #
 # Tests with </<=/>/>=/BETWEEN
 #
-EXPLAIN SELECT * from t1 WHERE t1.a > 5;
+EXPLAIN SELECT * from t1 WHERE t1.a > 5 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	10	Using where
+1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	10	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-EXPLAIN SELECT * from t1 WHERE 5 < t1.a;
+EXPLAIN SELECT * from t1 WHERE 5 < t1.a ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	10	Using where
+1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	10	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-EXPLAIN SELECT * from t1 WHERE t1.a >= 10 and t1.b <= 10;
+EXPLAIN SELECT * from t1 WHERE t1.a >= 10 and t1.b <= 10 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	a,b	a	5	NULL	1	Using index condition; Using where
+1	SIMPLE	t1	range	a,b	a	5	NULL	1	Using index condition; Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-EXPLAIN SELECT * from t1 WHERE  t1.a BETWEEN 1 AND 5;
+EXPLAIN SELECT * from t1 WHERE  t1.a BETWEEN 1 AND 5 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	10	Using where
+1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	10	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-EXPLAIN SELECT * from t1 WHERE  5 BETWEEN 1 AND t1.a;
+EXPLAIN SELECT * from t1 WHERE  5 BETWEEN 1 AND t1.a ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	10	Using where
+1	SIMPLE	t1	range	a	a	5	NULL	6	Using index condition; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-EXPLAIN SELECT * from t1 WHERE  1 BETWEEN t1.a AND t1.b;
+EXPLAIN SELECT * from t1 WHERE  1 BETWEEN t1.a AND t1.b ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	a,b	a	5	NULL	1	Using where
+1	SIMPLE	t1	range	a,b	a	5	NULL	1	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -707,18 +726,19 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 #
 # Tests with IN predicate
 #
-EXPLAIN SELECT * from t1 WHERE t1.a IN (1,2,3);
+EXPLAIN SELECT * from t1 WHERE t1.a IN (1,2,3) ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	a	a	5	NULL	3	Using index condition
+1	SIMPLE	t1	range	a	a	5	NULL	3	Using index condition; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-EXPLAIN SELECT * from t1 WHERE (t1.a,t1.b) IN ((1,1), (2,2), (3,3));
+EXPLAIN SELECT * from t1 WHERE (t1.a,t1.b) IN ((1,1), (2,2), (3,3))
+ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index_merge	a,b	a,a,a	5,5,5	NULL	3	Using union(a,a,a); Using where
+1	SIMPLE	t1	index_merge	a,b	a,a,a	5,5,5	NULL	3	Using union(a,a,a); Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -726,9 +746,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
     false
 ]
 EXPLAIN SELECT * from t1 WHERE
-t1.a=t1.b AND (t1.a,t1.b) IN ((1,1), (2,2), (3,3));
+t1.a=t1.b AND (t1.a,t1.b) IN ((1,1), (2,2), (3,3))
+ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index_merge	a,b	a,a,a	5,5,5	NULL	3	Using union(a,a,a); Using where
+1	SIMPLE	t1	index_merge	a,b	a,a,a	5,5,5	NULL	3	Using union(a,a,a); Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -736,9 +757,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
     false
 ]
 EXPLAIN SELECT * from t1
-WHERE t1.a=t1.b AND (abs(t1.a),t1.b) IN ((1,1), (2,2), (3,3));
+WHERE t1.a=t1.b AND (abs(t1.a),t1.b) IN ((1,1), (2,2), (3,3))
+ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	a,b	b	5	NULL	3	Using where
+1	SIMPLE	t1	range	a,b	b	5	NULL	3	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -748,27 +770,27 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 #
 # Tests using different functional predicates
 #
-EXPLAIN SELECT * from t1 WHERE a < 5 XOR a > 10;
+EXPLAIN SELECT * from t1 WHERE a < 5 XOR a > 10 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     false
 ]
-EXPLAIN SELECT * from t1 WHERE abs(a) > 10;
+EXPLAIN SELECT * from t1 WHERE abs(a) > 10 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     false
 ]
-EXPLAIN SELECT * from t1 WHERE a > 10+1;
+EXPLAIN SELECT * from t1 WHERE a > 10+1 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	a	a	5	NULL	1	Using index condition
+1	SIMPLE	t1	range	a	a	5	NULL	1	Using index condition; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -786,9 +808,9 @@ mysql.column_stats(db_name, table_name, column_name, nulls_ratio) VALUES ('test'
 #
 # estimates from stat tables not available, all the fields should be NOT NULL
 #
-EXPLAIN SELECT * from t1 WHERE a < 10;
+EXPLAIN SELECT * from t1 WHERE a < 10 ORDER BY t1.b LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -799,8 +821,8 @@ DROP TABLE t1;
 #
 # Using multiple equalities at the top level
 #
-CREATE TABLE t1(a INT, b INT);
-INSERT INTO t1 SELECT seq, seq from seq_1_to_10;
+CREATE TABLE t1(a INT, b INT, c INT);
+INSERT INTO t1 SELECT seq, seq, seq from seq_1_to_10;
 CREATE TABLE t2(a INT, b INT);
 INSERT INTO t2 SELECT seq, seq from seq_1_to_10;
 ANALYZE TABLE t1 PERSISTENT FOR ALL;
@@ -812,9 +834,9 @@ Table	Op	Msg_type	Msg_text
 test.t2	analyze	status	Engine-independent statistics collected
 test.t2	analyze	status	OK
 EXPLAIN
-SELECT * FROM t1,t2 WHERE t1.a= t2.a AND t1.b= t2.b;
+SELECT * FROM t1,t2 WHERE t1.a= t2.a AND t1.b= t2.b ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using temporary; Using filesort
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
@@ -822,19 +844,14 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-EXPLAIN
-SELECT * FROM t1,t2 WHERE t1.a= t2.a AND t1.b= t2.b;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
 #
 # Tests with views (using Item_direct_view_ref)
 #
 CREATE VIEW v1 AS SELECT a, b  FROM t1;
-EXPLAIN SELECT * FROM v1,t1 where v1.b = 10 AND v1.b=t1.b;
+EXPLAIN SELECT * FROM v1,t1 where v1.b = 10 AND v1.b=t1.b ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where; Using filesort
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -846,8 +863,8 @@ DROP VIEW v1;
 #
 # TESTS to get Number of distinct values(ndv) from rec_per_key
 #
-CREATE TABLE t1 (a INT, b INT);
-INSERT INTO t1 SELECT seq, seq FROM seq_1_to_10;
+CREATE TABLE t1 (a INT, b INT, c INT);
+INSERT INTO t1 SELECT seq, seq, seq FROM seq_1_to_10;
 ALTER TABLE t1 ADD KEY k1(a);
 set @save_use_stat_tables= @@use_stat_tables;
 set use_stat_tables='never';
@@ -860,9 +877,9 @@ ANALYZE TABLE t2 PERSISTENT FOR ALL;
 Table	Op	Msg_type	Msg_text
 test.t2	analyze	status	Engine-independent statistics collected
 test.t2	analyze	status	Table is already up to date
-EXPLAIN SELECT * FROM t1, t2 where t1.a=t2.a and t2.a<3;
+EXPLAIN SELECT * FROM t1, t2 where t1.a=t2.a and t2.a<3 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	k1	k1	5	NULL	2	Using index condition
+1	SIMPLE	t1	range	k1	k1	5	NULL	2	Using index condition; Using filesort
 1	SIMPLE	t2	ref	k1	k1	5	test.t1.a	1	
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
@@ -875,11 +892,11 @@ DROP TABLE t1,t2;
 #
 # Tests with multi-component keys
 #
-CREATE TABLE t1 (a INT, b INT, KEY(a,b));
-INSERT INTO t1 SELECT seq, seq FROM seq_1_to_10;
-EXPLAIN SELECT * FROM t1 where t1.a<3 AND t1.b < 10;
+CREATE TABLE t1 (a INT, b INT, c INT, KEY(a,b));
+INSERT INTO t1 SELECT seq, seq, seq FROM seq_1_to_10;
+EXPLAIN SELECT * FROM t1 where t1.a<3 AND t1.b < 10 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	a	a	5	NULL	2	Using where; Using index
+1	SIMPLE	t1	range	a	a	5	NULL	2	Using index condition; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -887,9 +904,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
     false
 ]
 ALTER TABLE t1 ADD KEY k1(a);
-EXPLAIN SELECT * FROM t1 where t1.a<3;
+EXPLAIN SELECT * FROM t1 where t1.a<3 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	a,k1	a	5	NULL	2	Using where; Using index
+1	SIMPLE	t1	range	a,k1	k1	5	NULL	2	Using index condition; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -900,13 +917,13 @@ DROP TABLE t1;
 #
 # Test FOR NOT BETWEEN
 #
-CREATE TABLE t1(a INT, b VARCHAR(10), KEY(a));
-INSERT INTO t1 SELECT seq, seq from seq_1_to_20;
+CREATE TABLE t1(a INT, b VARCHAR(10), c INT, KEY(a));
+INSERT INTO t1 SELECT seq, seq, seq from seq_1_to_20;
 set optimizer_trace=1;
 EXPLAIN
-SELECT * FROM t1 WHERE a NOT BETWEEN 3 AND 5;
+SELECT * FROM t1 WHERE a NOT BETWEEN 3 AND 5 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	20	Using where
+1	SIMPLE	t1	range	a	a	5	NULL	17	Using index condition; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -917,9 +934,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 # Test FOR NOT IN
 #
 EXPLAIN
-SELECT * FROM t1 WHERE a NOT IN (1,2,3,4,5);
+SELECT * FROM t1 WHERE a NOT IN (1,2,3,4,5) ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	20	Using where
+1	SIMPLE	t1	range	a	a	5	NULL	20	Using index condition; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -931,9 +948,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 # For this the range optimizer would not give accurate selectivity estimates
 #
 EXPLAIN
-SELECT * FROM t1 WHERE a NOT LIKE '1%';
+SELECT * FROM t1 WHERE a NOT LIKE '1%' ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	20	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	20	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -941,8 +958,8 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
     false
 ]
 DROP TABLE t1;
-CREATE TABLE t1(a INT, b INT);
-INSERT INTO t1 SELECT seq, seq from seq_1_to_10;
+CREATE TABLE t1(a INT, b INT, c INT);
+INSERT INTO t1 SELECT seq, seq, seq from seq_1_to_10;
 set optimizer_trace=1;
 SET @save_use_stat_tables= @@use_stat_tables;
 SET @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity;
@@ -954,9 +971,9 @@ Table	Op	Msg_type	Msg_text
 test.t1	analyze	status	Engine-independent statistics collected
 test.t1	analyze	status	OK
 SET use_stat_tables='never';
-EXPLAIN SELECT * from t1 WHERE t1.a > 5;
+EXPLAIN SELECT * from t1 WHERE t1.a > 5 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -967,9 +984,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 # Here statistics are available from stat tables and statistics can be used for the query
 #
 SET use_stat_tables='preferably';
-EXPLAIN SELECT * from t1 WHERE t1.a > 5;
+EXPLAIN SELECT * from t1 WHERE t1.a > 5 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -981,9 +998,9 @@ SET optimizer_use_condition_selectivity=1;
 # Here statistics are available from stat tables but cannot be used because statistics from
 # stat tables are only used when optimizer_use_condition_selectivity > 1
 #
-EXPLAIN SELECT * from t1 WHERE t1.a > 5;
+EXPLAIN SELECT * from t1 WHERE t1.a > 5 ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where; Using filesort
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -997,11 +1014,11 @@ SET @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selecti
 # For predicates like these a=a, it would be good to have the removed but for these currently
 # we assume with such predicates we don't have accurate estimates for selectivity
 #
-EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = a;
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = a ORDER BY t1.c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where; Using filesort
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` = `test`.`t1`.`a`
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`a` = `test`.`t1`.`a` order by `test`.`t1`.`c` limit 5
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -1023,22 +1040,22 @@ test.t1	analyze	status	Table is already up to date
 # DELETING the avg_frequency for column a and b from stat tables
 update mysql.column_stats set avg_frequency= NULL
 WHERE table_name='t1' AND (column_name='b' OR column_name='a');
-EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1;
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1 ORDER BY c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ref	a	a	5	const	1	100.00	
+1	SIMPLE	t1	ref	a	a	5	const	1	100.00	Using where; Using filesort
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where `test`.`t1`.`a` = 1
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where `test`.`t1`.`a` = 1 order by `test`.`t1`.`c` limit 5
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 [
     true
 ]
-EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1 AND a=b;
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1 AND a=b ORDER BY c LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ref	a	a	5	const	1	100.00	Using where
+1	SIMPLE	t1	ref	a	a	5	const	1	100.00	Using where; Using filesort
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where `test`.`t1`.`a` = 1 and `test`.`t1`.`b` = 1
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where `test`.`t1`.`a` = 1 and `test`.`t1`.`b` = 1 order by `test`.`t1`.`c` limit 5
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
@@ -1063,12 +1080,12 @@ ANALYZE TABLE t2 PERSISTENT FOR ALL;
 Table	Op	Msg_type	Msg_text
 test.t2	analyze	status	Engine-independent statistics collected
 test.t2	analyze	status	OK
-EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE t1.a= t2.a;
+EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE t1.a= t2.a ORDER BY t1.b LIMIT 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	100.00	Using temporary; Using filesort
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where; Using join buffer (flat, BNL join)
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a`
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` order by `test`.`t1`.`b` limit 10
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
diff --git a/mysql-test/main/join_cardinality.test b/mysql-test/main/join_cardinality.test
index 3c43a3223dc..f0e532d80b6 100644
--- a/mysql-test/main/join_cardinality.test
+++ b/mysql-test/main/join_cardinality.test
@@ -50,24 +50,24 @@ DROP TABLE t1,t2;
 --echo # statistics from indexes
 --echo #
 
-CREATE TABLE t1(a INT, b INT, KEY(a));
-INSERT INTO t1 SELECT seq, seq from seq_1_to_100;
+CREATE TABLE t1(a INT, b INT, c INT, KEY(a));
+INSERT INTO t1 SELECT seq, seq, seq from seq_1_to_100;
 
-ANALYZE SELECT * from t1  WHERE t1.b > 3 AND t1.a=t1.b;
+ANALYZE SELECT * from t1  WHERE t1.b > 3 AND t1.a=t1.b ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
 ANALYZE TABLE t1 PERSISTENT FOR COLUMNS (b) INDEXES ();
 
-ANALYZE SELECT * from t1  WHERE t1.b > 3 AND t1.a=10;
+ANALYZE SELECT * from t1  WHERE t1.b > 3 AND t1.a=10 ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
-ANALYZE SELECT * from t1  WHERE t1.b > 3 AND t1.a=t1.b;
+ANALYZE SELECT * from t1  WHERE t1.b > 3 AND t1.a=t1.b ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
-ANALYZE SELECT * from t1  WHERE t1.b > 3 OR t1.a=t1.b;
+ANALYZE SELECT * from t1  WHERE t1.b > 3 OR t1.a=t1.b ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
@@ -84,10 +84,10 @@ INSERT INTO t1 SELECT seq, seq, seq from seq_1_to_10;
 --echo # Tests with LIKE PREDICATES
 --echo #
 
-EXPLAIN SELECT * from t1 WHERE t1.a LIKE '%2%';
+EXPLAIN SELECT * from t1 WHERE t1.a LIKE '%2%' ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
-EXPLAIN SELECT * from t1 WHERE t1.a LIKE '2%';
+EXPLAIN SELECT * from t1 WHERE t1.a LIKE '2%' ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
@@ -95,11 +95,11 @@ FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 --echo # Tests with NULL predicates
 --echo #
 
-EXPLAIN SELECT * from t1 WHERE t1.a IS NULL;
+EXPLAIN SELECT * from t1 WHERE t1.a IS NULL ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
-EXPLAIN SELECT * from t1 WHERE t1.a IS NOT NULL;
+EXPLAIN SELECT * from t1 WHERE t1.a IS NOT NULL ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
@@ -107,27 +107,27 @@ FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 --echo # Tests with </<=/>/>=/BETWEEN
 --echo #
 
-EXPLAIN SELECT * from t1 WHERE t1.a > 5;
+EXPLAIN SELECT * from t1 WHERE t1.a > 5 ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
-EXPLAIN SELECT * from t1 WHERE 5 < t1.a;
+EXPLAIN SELECT * from t1 WHERE 5 < t1.a ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
-EXPLAIN SELECT * from t1 WHERE t1.a >= 10 and t1.b <= 10;
+EXPLAIN SELECT * from t1 WHERE t1.a >= 10 and t1.b <= 10 ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
-EXPLAIN SELECT * from t1 WHERE  t1.a BETWEEN 1 AND 5;
+EXPLAIN SELECT * from t1 WHERE  t1.a BETWEEN 1 AND 5 ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
-EXPLAIN SELECT * from t1 WHERE  5 BETWEEN 1 AND t1.a;
+EXPLAIN SELECT * from t1 WHERE  5 BETWEEN 1 AND t1.a ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
-EXPLAIN SELECT * from t1 WHERE  1 BETWEEN t1.a AND t1.b;
+EXPLAIN SELECT * from t1 WHERE  1 BETWEEN t1.a AND t1.b ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
@@ -135,21 +135,24 @@ FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 --echo # Tests with IN predicate
 --echo #
 
-EXPLAIN SELECT * from t1 WHERE t1.a IN (1,2,3);
+EXPLAIN SELECT * from t1 WHERE t1.a IN (1,2,3) ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
-EXPLAIN SELECT * from t1 WHERE (t1.a,t1.b) IN ((1,1), (2,2), (3,3));
+EXPLAIN SELECT * from t1 WHERE (t1.a,t1.b) IN ((1,1), (2,2), (3,3))
+ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
 EXPLAIN SELECT * from t1 WHERE
-t1.a=t1.b AND (t1.a,t1.b) IN ((1,1), (2,2), (3,3));
+t1.a=t1.b AND (t1.a,t1.b) IN ((1,1), (2,2), (3,3))
+ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
 EXPLAIN SELECT * from t1
-WHERE t1.a=t1.b AND (abs(t1.a),t1.b) IN ((1,1), (2,2), (3,3));
+WHERE t1.a=t1.b AND (abs(t1.a),t1.b) IN ((1,1), (2,2), (3,3))
+ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
@@ -157,15 +160,15 @@ FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 --echo # Tests using different functional predicates
 --echo #
 
-EXPLAIN SELECT * from t1 WHERE a < 5 XOR a > 10;
+EXPLAIN SELECT * from t1 WHERE a < 5 XOR a > 10 ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
-EXPLAIN SELECT * from t1 WHERE abs(a) > 10;
+EXPLAIN SELECT * from t1 WHERE abs(a) > 10 ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
-EXPLAIN SELECT * from t1 WHERE a > 10+1;
+EXPLAIN SELECT * from t1 WHERE a > 10+1 ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
@@ -185,7 +188,7 @@ mysql.column_stats(db_name, table_name, column_name, nulls_ratio) VALUES ('test'
 --echo # estimates from stat tables not available, all the fields should be NOT NULL
 --echo #
 
-EXPLAIN SELECT * from t1 WHERE a < 10;
+EXPLAIN SELECT * from t1 WHERE a < 10 ORDER BY t1.b LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
@@ -195,8 +198,8 @@ DROP TABLE t1;
 --echo # Using multiple equalities at the top level
 --echo #
 
-CREATE TABLE t1(a INT, b INT);
-INSERT INTO t1 SELECT seq, seq from seq_1_to_10;
+CREATE TABLE t1(a INT, b INT, c INT);
+INSERT INTO t1 SELECT seq, seq, seq from seq_1_to_10;
 CREATE TABLE t2(a INT, b INT);
 INSERT INTO t2 SELECT seq, seq from seq_1_to_10;
 
@@ -204,20 +207,17 @@ ANALYZE TABLE t1 PERSISTENT FOR ALL;
 ANALYZE TABLE t2 PERSISTENT FOR ALL;
 
 EXPLAIN
-SELECT * FROM t1,t2 WHERE t1.a= t2.a AND t1.b= t2.b;
+SELECT * FROM t1,t2 WHERE t1.a= t2.a AND t1.b= t2.b ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
-EXPLAIN
-SELECT * FROM t1,t2 WHERE t1.a= t2.a AND t1.b= t2.b;
-
 --echo #
 --echo # Tests with views (using Item_direct_view_ref)
 --echo #
 
 CREATE VIEW v1 AS SELECT a, b  FROM t1;
 
-EXPLAIN SELECT * FROM v1,t1 where v1.b = 10 AND v1.b=t1.b;
+EXPLAIN SELECT * FROM v1,t1 where v1.b = 10 AND v1.b=t1.b ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
@@ -229,8 +229,8 @@ DROP VIEW v1;
 --echo # TESTS to get Number of distinct values(ndv) from rec_per_key
 --echo #
 
-CREATE TABLE t1 (a INT, b INT);
-INSERT INTO t1 SELECT seq, seq FROM seq_1_to_10;
+CREATE TABLE t1 (a INT, b INT, c INT);
+INSERT INTO t1 SELECT seq, seq, seq FROM seq_1_to_10;
 ALTER TABLE t1 ADD KEY k1(a);
 
 set @save_use_stat_tables= @@use_stat_tables;
@@ -240,7 +240,7 @@ analyze table t1;
 CREATE TABLE t2 like t1;
 INSERT INTO t2 SELECT * FROM t1;
 ANALYZE TABLE t2 PERSISTENT FOR ALL;
-EXPLAIN SELECT * FROM t1, t2 where t1.a=t2.a and t2.a<3;
+EXPLAIN SELECT * FROM t1, t2 where t1.a=t2.a and t2.a<3 ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 set use_stat_tables=@save_use_stat_tables;
@@ -251,15 +251,15 @@ DROP TABLE t1,t2;
 --echo # Tests with multi-component keys
 --echo #
 
-CREATE TABLE t1 (a INT, b INT, KEY(a,b));
-INSERT INTO t1 SELECT seq, seq FROM seq_1_to_10;
+CREATE TABLE t1 (a INT, b INT, c INT, KEY(a,b));
+INSERT INTO t1 SELECT seq, seq, seq FROM seq_1_to_10;
 
-EXPLAIN SELECT * FROM t1 where t1.a<3 AND t1.b < 10;
+EXPLAIN SELECT * FROM t1 where t1.a<3 AND t1.b < 10 ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
 ALTER TABLE t1 ADD KEY k1(a);
-EXPLAIN SELECT * FROM t1 where t1.a<3;
+EXPLAIN SELECT * FROM t1 where t1.a<3 ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
@@ -269,12 +269,12 @@ DROP TABLE t1;
 --echo # Test FOR NOT BETWEEN
 --echo #
 
-CREATE TABLE t1(a INT, b VARCHAR(10), KEY(a));
-INSERT INTO t1 SELECT seq, seq from seq_1_to_20;
+CREATE TABLE t1(a INT, b VARCHAR(10), c INT, KEY(a));
+INSERT INTO t1 SELECT seq, seq, seq from seq_1_to_20;
 set optimizer_trace=1;
 
 EXPLAIN
-SELECT * FROM t1 WHERE a NOT BETWEEN 3 AND 5;
+SELECT * FROM t1 WHERE a NOT BETWEEN 3 AND 5 ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
@@ -283,7 +283,7 @@ FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 --echo #
 
 EXPLAIN
-SELECT * FROM t1 WHERE a NOT IN (1,2,3,4,5);
+SELECT * FROM t1 WHERE a NOT IN (1,2,3,4,5) ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
@@ -293,14 +293,14 @@ FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 --echo #
 
 EXPLAIN
-SELECT * FROM t1 WHERE a NOT LIKE '1%';
+SELECT * FROM t1 WHERE a NOT LIKE '1%' ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
 DROP TABLE t1;
 
-CREATE TABLE t1(a INT, b INT);
-INSERT INTO t1 SELECT seq, seq from seq_1_to_10;
+CREATE TABLE t1(a INT, b INT, c INT);
+INSERT INTO t1 SELECT seq, seq, seq from seq_1_to_10;
 set optimizer_trace=1;
 SET @save_use_stat_tables= @@use_stat_tables;
 SET @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity;
@@ -313,7 +313,7 @@ ANALYZE TABLE t1 PERSISTENT FOR ALL;
 
 SET use_stat_tables='never';
 
-EXPLAIN SELECT * from t1 WHERE t1.a > 5;
+EXPLAIN SELECT * from t1 WHERE t1.a > 5 ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
@@ -322,7 +322,7 @@ FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 --echo #
 
 SET use_stat_tables='preferably';
-EXPLAIN SELECT * from t1 WHERE t1.a > 5;
+EXPLAIN SELECT * from t1 WHERE t1.a > 5 ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
@@ -333,7 +333,7 @@ SET optimizer_use_condition_selectivity=1;
 --echo # stat tables are only used when optimizer_use_condition_selectivity > 1
 --echo #
 
-EXPLAIN SELECT * from t1 WHERE t1.a > 5;
+EXPLAIN SELECT * from t1 WHERE t1.a > 5 ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
@@ -347,7 +347,7 @@ SET @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selecti
 --echo # we assume with such predicates we don't have accurate estimates for selectivity
 --echo #
 
-EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = a;
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = a ORDER BY t1.c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
@@ -368,11 +368,11 @@ ANALYZE TABLE t1 PERSISTENT FOR ALL;
 update mysql.column_stats set avg_frequency= NULL
 WHERE table_name='t1' AND (column_name='b' OR column_name='a');
 
-EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1;
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1 ORDER BY c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
-EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1 AND a=b;
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1 AND a=b ORDER BY c LIMIT 5;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.cardinality_accurate'))
 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index c54e3f9af0a..af63ab7176c 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -8486,7 +8486,7 @@ choose_plan(JOIN *join, table_map join_tables)
 
   Json_writer_object wrapper(thd);
 
-  if (join->conds && (join->sort_nest_possible || thd->trace_started()))
+  if (join->conds && join->sort_nest_possible)
   {
     bool cardinality_accurate;
     SAME_FIELD arg= {NULL, FALSE};