Introduced an optimizer_switch for ORDER BY LIMIT

8 files changed, 20 insertions, 1212 deletions

diff --git a/mysql-test/main/sort_nest.result b/mysql-test/main/sort_nest.result
index 965f3f95c5b..3bc69cd5625 100644
--- a/mysql-test/main/sort_nest.result
+++ b/mysql-test/main/sort_nest.result
@@ -1,4 +1,4 @@
-set use_sort_nest=1;
+set optimizer_switch='cost_based_order_by_limit=on';
 CREATE TABLE t0 (a int);
 INSERT INTO t0 SELECT seq-1 from seq_1_to_10;
 CREATE TABLE t1 (a int, b int);
@@ -1772,7 +1772,6 @@ INSERT INTO t0 SELECT seq-1 from seq_1_to_10;
 CREATE TABLE t1 (a int, b int);
 INSERT INTO t1 SELECT a, a from t0;
 CREATE TABLE t2 as SELECT * from t1;
-set use_sort_nest=1;
 EXPLAIN SELECT *
 FROM t1,t2
 WHERE t1.a=t2.a and t1.b= 4
@@ -1804,7 +1803,6 @@ ANALYZE TABLE t1 PERSISTENT FOR ALL;
 ANALYZE TABLE t2 PERSISTENT FOR ALL;
 ANALYZE TABLE t3 PERSISTENT FOR ALL;
 ANALYZE TABLE t4 PERSISTENT FOR ALL;
-set use_sort_nest=1;
 EXPLAIN SELECT t1.a, t2.b, t1.b, t1.b + t2.b
 FROM t1,t2, t3
 WHERE t1.a=t2.b and t2.a=t3.a
@@ -1826,18 +1824,6 @@ a	b	b	t1.b + t2.b
 2	2	2	4
 1	1	1	2
 0	0	0	0
-set use_sort_nest=0;
-SELECT t1.a, t2.b, t1.b, t1.b + t2.b
-FROM t1,t2, t3
-WHERE t1.a=t2.b and t2.a=t3.a
-ORDER BY abs(t3.a+t1.b) DESC
-LIMIT 5;
-a	b	b	t1.b + t2.b
-4	4	4	8
-3	3	3	6
-2	2	2	4
-1	1	1	2
-0	0	0	0
 #
 # No sort nest where ORDER BY item are expensive to compute like 
 # stored functions, subqueries etc
@@ -1845,74 +1831,6 @@ CREATE FUNCTION f1(a INT) RETURNS INT
 BEGIN
 RETURN a;
 END|
-set use_sort_nest=1;
-EXPLAIN SELECT t1.a, t2.b, t1.b, t1.b + t2.b
-FROM t1,t2, t3
-WHERE t1.a=t2.b and t2.a=t3.a
-ORDER BY f1(t3.a+t1.b) DESC
-LIMIT 5;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	Using temporary; Using filesort
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	Using where; Using join buffer (flat, BNL join)
-1	SIMPLE	t3	ref	a	a	5	test.t2.a	1	Using index
-EXPLAIN FORMAT=JSON SELECT t1.a, t2.b, t1.b, t1.b + t2.b
-FROM t1,t2, t3
-WHERE t1.a=t2.b and t2.a=t3.a
-ORDER BY f1(t3.a+t1.b) DESC
-LIMIT 5;
-EXPLAIN
-{
-  "query_block": {
-    "select_id": 1,
-    "filesort": {
-      "sort_key": "f1(t3.a + t1.b) desc",
-      "temporary_table": {
-        "table": {
-          "table_name": "t1",
-          "access_type": "ALL",
-          "rows": 5,
-          "filtered": 100
-        },
-        "block-nl-join": {
-          "table": {
-            "table_name": "t2",
-            "access_type": "ALL",
-            "rows": 5,
-            "filtered": 100
-          },
-          "buffer_type": "flat",
-          "buffer_size": "119",
-          "join_type": "BNL",
-          "attached_condition": "t2.b = t1.a and t2.a is not null"
-        },
-        "table": {
-          "table_name": "t3",
-          "access_type": "ref",
-          "possible_keys": ["a"],
-          "key": "a",
-          "key_length": "5",
-          "used_key_parts": ["a"],
-          "ref": ["test.t2.a"],
-          "rows": 1,
-          "filtered": 100,
-          "using_index": true
-        }
-      }
-    }
-  }
-}
-SELECT t1.a, t2.b, t1.b, t1.b + t2.b
-FROM t1,t2, t3
-WHERE t1.a=t2.b and t2.a=t3.a
-ORDER BY f1(t3.a+t1.b) DESC
-LIMIT 5;
-a	b	b	t1.b + t2.b
-4	4	4	8
-3	3	3	6
-2	2	2	4
-1	1	1	2
-0	0	0	0
-set use_sort_nest=0;
 EXPLAIN SELECT t1.a, t2.b, t1.b, t1.b + t2.b
 FROM t1,t2, t3
 WHERE t1.a=t2.b and t2.a=t3.a
@@ -1937,7 +1855,6 @@ drop function f1;
 #
 # Window function in order by clause, sort-nest not allowed
 #
-set use_sort_nest=1;
 EXPLAIN SELECT t1.a, t2.b, t1.b, t1.b + t2.b
 FROM t1,t2, t3
 WHERE t1.a=t2.b AND t2.a=t3.a
@@ -1947,71 +1864,6 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	Using temporary; Using filesort
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	Using where; Using join buffer (flat, BNL join)
 1	SIMPLE	t3	ref	a	a	5	test.t2.a	1	Using index
-EXPLAIN FORMAT=JSON SELECT t1.a, t2.b, t1.b, t1.b + t2.b
-FROM t1,t2, t3
-WHERE t1.a=t2.b AND t2.a=t3.a
-ORDER BY row_number() OVER (ORDER BY t1.a) DESC
-LIMIT 5;
-EXPLAIN
-{
-  "query_block": {
-    "select_id": 1,
-    "filesort": {
-      "sort_key": "row_number() over ( order by t1.a) desc",
-      "window_functions_computation": {
-        "sorts": {
-          "filesort": {
-            "sort_key": "t1.a"
-          }
-        },
-        "temporary_table": {
-          "table": {
-            "table_name": "t1",
-            "access_type": "ALL",
-            "rows": 5,
-            "filtered": 100
-          },
-          "block-nl-join": {
-            "table": {
-              "table_name": "t2",
-              "access_type": "ALL",
-              "rows": 5,
-              "filtered": 100
-            },
-            "buffer_type": "flat",
-            "buffer_size": "119",
-            "join_type": "BNL",
-            "attached_condition": "t2.b = t1.a and t2.a is not null"
-          },
-          "table": {
-            "table_name": "t3",
-            "access_type": "ref",
-            "possible_keys": ["a"],
-            "key": "a",
-            "key_length": "5",
-            "used_key_parts": ["a"],
-            "ref": ["test.t2.a"],
-            "rows": 1,
-            "filtered": 100,
-            "using_index": true
-          }
-        }
-      }
-    }
-  }
-}
-SELECT t1.a, t2.b, t1.b, t1.b + t2.b
-FROM t1,t2, t3
-WHERE t1.a=t2.b AND t2.a=t3.a
-ORDER BY row_number() OVER (ORDER BY t1.a) DESC
-LIMIT 5;
-a	b	b	t1.b + t2.b
-4	4	4	8
-3	3	3	6
-2	2	2	4
-1	1	1	2
-0	0	0	0
-set use_sort_nest=0;
 SELECT t1.a, t2.b, t1.b, t1.b + t2.b
 FROM t1,t2, t3
 WHERE t1.a=t2.b AND t2.a=t3.a
@@ -2029,7 +1881,6 @@ a	b	b	t1.b + t2.b
 #
 # Dependent subqueries cannot have a sort-nest
 #
-set use_sort_nest=1;
 EXPLAIN SELECT *
 FROM t1
 WHERE
@@ -2053,23 +1904,9 @@ a	b	c
 2	2	2
 3	3	3
 4	4	4
-set use_sort_nest= 0;
-SELECT *
-FROM t1
-WHERE
-t1.b = (select t2.b from t2,t3,t4
-where t2.a=t1.a and t3.b=t4.b
-ORDER BY t2.c,t3.c limit 1);
-a	b	c
-0	0	0
-1	1	1
-2	2	2
-3	3	3
-4	4	4
 #
 # Independent subqueries can have a sort-nest
 #
-set use_sort_nest=1;
 EXPLAIN SELECT *
 FROM t1
 WHERE
@@ -2090,19 +1927,9 @@ where t2.a=t3.a and t3.b=t4.b
 ORDER BY t2.c,t3.c limit 1);
 a	b	c
 0	0	0
-set use_sort_nest= 0;
-SELECT *
-FROM t1
-WHERE
-t1.b = (select t2.b from t2,t3,t4
-where t2.a=t3.a and t3.b=t4.b
-ORDER BY t2.c,t3.c limit 1);
-a	b	c
-0	0	0
 #
 # Sort nest inside a derived table
 #
-set use_sort_nest=1;
 EXPLAIN SELECT *
 FROM (SELECT t1.a as t, t2.b as u, t1.b as v, t3.a as x
 FROM t1,t2,t3
@@ -2127,23 +1954,9 @@ t	u	v	x
 2	2	2	2
 1	1	1	1
 0	0	0	0
-set use_sort_nest=0;
-SELECT *
-FROM (SELECT t1.a as t, t2.b as u, t1.b as v, t3.a as x
-FROM t1,t2,t3
-WHERE t1.a=t2.a AND t2.b=t3.a
-ORDER BY t2.b DESC, t1.b DESC
-LIMIT 5)q;
-t	u	v	x
-4	4	4	4
-3	3	3	3
-2	2	2	2
-1	1	1	1
-0	0	0	0
 #
 # sort-nest(t2,t1) and independent subquery in the SELECT list
 #
-set use_sort_nest=1;
 EXPLAIN SELECT (SELECT A.a FROM t3 A WHERE A.a > 5 limit 1) as x,
 t2.b, t1.b, t3.a
 FROM t1,t2,t3
@@ -2171,7 +1984,6 @@ x	b	b	a
 #
 # sort-nest(t2,t1) and dependent subquery in the SELECT list
 #
-set use_sort_nest=1;
 EXPLAIN SELECT (SELECT A.a FROM t3 A WHERE A.a = t1.b limit 1) as x,
 t2.b, t1.b, t3.a
 FROM t1,t2,t3
@@ -2196,24 +2008,10 @@ x	b	b	a
 2	2	2	2
 1	1	1	1
 0	0	0	0
-set use_sort_nest= 0;
-SELECT (SELECT A.a FROM t3 A WHERE A.a = t1.b limit 1) as x,
-t2.b, t1.b, t3.a
-FROM t1,t2,t3
-WHERE t1.a = t2.a and t2.c=t3.c
-ORDER BY t2.b DESC, t1.b DESC
-LIMIT 5;
-x	b	b	a
-4	4	4	4
-3	3	3	3
-2	2	2	2
-1	1	1	1
-0	0	0	0
 #
 # Dependent Subquery attached outside the sort-nest, references inside
 # subquery changed to sort-nest field items
 #
-set use_sort_nest=1;
 EXPLAIN SELECT t2.b, t1.b, t3.a
 FROM t1,t2,t3
 WHERE t1.a = t2.a AND t2.c = t3.c AND
@@ -2300,20 +2098,10 @@ ORDER BY t2.b DESC, t1.b DESC
 LIMIT 5;
 b	b	a
 2	2	2
-set use_sort_nest= 0;
-SELECT t2.b, t1.b, t3.a
-FROM t1,t2,t3
-WHERE t1.a = t2.a AND t2.c = t3.c AND
-t1.b=(SELECT A.a FROM t3 A WHERE A.b > 1 and t3.b=t1.b limit 1)
-ORDER BY t2.b DESC, t1.b DESC
-LIMIT 5;
-b	b	a
-2	2	2
 #
 # sort-nest(t2,t1) and independent subquery in the ORDER BY clause
 #
 # Subquery will be a constant in ORDER BY and will be removed
-set use_sort_nest=1;
 EXPLAIN SELECT t2.b, t1.b, t3.c
 FROM t1,t2,t3
 WHERE t1.a = t2.a and t2.c=t3.c
@@ -2338,24 +2126,10 @@ b	b	c
 2	2	2
 3	3	3
 4	4	4
-set use_sort_nest= 0;
-SELECT t2.b, t1.b, t3.c
-FROM t1,t2,t3
-WHERE t1.a = t2.a and t2.c=t3.c
-ORDER BY t2.b, t1.b,
-(SELECT A.a FROM t3 A WHERE A.a < 5 limit 1)
-LIMIT 5;
-b	b	c
-0	0	0
-1	1	1
-2	2	2
-3	3	3
-4	4	4
 #
 # sort-nest(t2,t1) and dependent subquery in the ORDER BY clause
 #
 # Subquery is expensive to evaluate, so don't use a sort-nest
-set use_sort_nest=1;
 EXPLAIN SELECT t2.b, t1.b, t3.c
 FROM t1,t2,t3
 WHERE t1.a = t2.a and t2.c=t3.c
@@ -2379,19 +2153,6 @@ b	b	c
 2	2	2
 3	3	3
 4	4	4
-set use_sort_nest= 0;
-SELECT t2.b, t1.b, t3.c
-FROM t1,t2,t3
-WHERE t1.a = t2.a and t2.c=t3.c
-ORDER BY t2.b, t1.b,
-(SELECT A.a FROM t3 A WHERE A.a = t1.c limit 1)
-LIMIT 5;
-b	b	c
-0	0	0
-1	1	1
-2	2	2
-3	3	3
-4	4	4
 drop table t0,t1,t2,t3,t4;
 CREATE TABLE t1 (a int, b int, c int, KEY a_b (a,b), KEY a_c (a,c));
 insert into t1 values (0,1,0), (0,2,0), (0,3,0), (0,4,0), (0,5,0), (0,6,0);
@@ -2401,7 +2162,6 @@ insert into t1 values (1,7,2), (1,8,2), (1,9,2), (1,10,2), (1,11,2), (1,12,2);
 #
 # index a_b should be used, no need for filesort
 #
-set use_sort_nest= 1;
 select a,b,c from t1 where a=1 and c=2 order by b limit 5;
 a	b	c
 1	7	2
@@ -2412,10 +2172,6 @@ a	b	c
 explain select a,b,c from t1 where a=1 and c=2 order by b limit 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	a_b,a_c	a_b	5	NULL	10	Using index condition; Using where
-set use_sort_nest= 0;
-explain select a,b,c from t1 where a=1 and c=2 order by b limit 5;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	a_b,a_c	a_b	5	NULL	18	Using where
 drop table t1;
 #
 # Tests where Index(scan, ref or range access) satisfies the ORDERING
@@ -2428,9 +2184,8 @@ insert into t1 values (1,7,2), (1,8,2), (1,9,2), (1,10,2), (1,11,2), (1,12,2);
 insert into t1 values (1,1,2);
 #
 # index key a_b, no need for filesort
-# Also index condition pushdown is used with use_sort_nest=1
+# Also index condition pushdown is used here
 #
-set use_sort_nest=1;
 select a,b from t1 where a=1 and c=2 order by b limit 10;
 a	b
 1	1
@@ -2446,10 +2201,6 @@ a	b
 explain select a,b from t1 where a=1 and c=2 order by b limit 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	a_b,a_c	a_b	5	NULL	19	Using index condition; Using where
-set use_sort_nest=0;
-explain select a,b from t1 where a=1 and c=2 order by b limit 10;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	a_b,a_c	a_b	5	NULL	19	Using where
 drop table t1;
 CREATE TABLE t1(
 a int NOT NULL,
@@ -2460,7 +2211,6 @@ INSERT INTO t1 VALUES (1,'a'), (2,'b'), (3,'c'), (4,'d');
 #
 # Should use index condition
 #
-set use_sort_nest= 1;
 SELECT * FROM t1 WHERE a BETWEEN 1 and 2 ORDER BY a LIMIT 2;
 a	b
 1	a
@@ -2468,14 +2218,9 @@ a	b
 EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 and 2 ORDER BY a LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition
-set use_sort_nest= 0;
-EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 and 2 ORDER BY a LIMIT 2;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition
 #
 # Should not use index condition as ORDER by DESC is used
 #
-set use_sort_nest= 1;
 EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 and 2 ORDER BY a DESC LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	2	Using where
@@ -2483,10 +2228,6 @@ SELECT * FROM t1 WHERE a BETWEEN 1 and 2 ORDER BY a DESC LIMIT 2;
 a	b
 2	b
 1	a
-set use_sort_nest= 0;
-EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 and 2 ORDER BY a DESC LIMIT 2;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	2	Using where
 drop table t1;
 create table t1(a int, b int, c int, key(a), key a_b(a,b));
 insert into t1 values (0,1,0), (0,2,0), (0,3,0);
@@ -2501,7 +2242,6 @@ insert into t2 select a, b, c from t1;
 #
 #  Using range scan
 #
-set use_sort_nest= 1;
 SELECT * FROM t1,t2
 WHERE
 t1.a=2 AND t2.b > 8 AND
@@ -2518,19 +2258,9 @@ ORDER BY t1.b LIMIT 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	a,a_b	a_b	10	NULL	2	Using index condition
 1	SIMPLE	t2	ref	b	b	5	test.t1.b	1	
-set use_sort_nest= 0;
-EXPLAIN SELECT * FROM t1,t2
-WHERE
-t1.a=2 AND t2.b > 8 AND
-t1.b=t2.b
-ORDER BY t1.b LIMIT 10;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	a,a_b	a_b	10	NULL	2	Using index condition
-1	SIMPLE	t2	ref	b	b	5	test.t1.b	1	
 #
 #  Using ref access
 #
-set use_sort_nest= 1;
 SELECT * FROM t1,t2
 WHERE
 t1.a=2 AND t2.c >= 1 AND
@@ -2548,18 +2278,8 @@ ORDER BY t1.b LIMIT 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	a,a_b	a_b	5	const	3	Using index condition; Using where
 1	SIMPLE	t2	ref	b,c	b	5	test.t1.b	1	Using where
-set use_sort_nest= 0;
-EXPLAIN SELECT * FROM t1,t2
-WHERE
-t1.a=2 AND t2.c >= 1 AND
-t1.b=t2.b
-ORDER BY t1.b LIMIT 10;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	a,a_b	a_b	5	const	3	Using where
-1	SIMPLE	t2	ref	b,c	b	5	test.t1.b	1	Using where
 drop table t1,t2;
 # TESTS with INDEX HINTS
-set use_sort_nest=1;
 create table t0 (a int);
 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 create table t1 (a int, b int,c int, key idx1(a), key idx2(a,b), key idx3(c));
@@ -2568,7 +2288,6 @@ analyze table t1 persistent for all;
 #
 # Index idx1 to be used for index scan
 #
-set use_sort_nest=1;
 SELECT * from t1 where b > 0 order by t1.a limit 2;
 a	b	c
 1	1	1
@@ -2576,14 +2295,9 @@ a	b	c
 EXPLAIN SELECT * from t1 where b > 0 order by t1.a limit 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	idx1	5	NULL	2	Using where
-set use_sort_nest=0;
-EXPLAIN SELECT * from t1 where b > 0 order by t1.a limit 2;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	idx1	5	NULL	2	Using where
 #
 # Index idx2 to be used for index scan(USE INDEX is used)
 #
-set use_sort_nest=1;
 SELECT * from t1 USE INDEX(idx2) WHERE b > 0 ORDER BY t1.a LIMIT 2;
 a	b	c
 1	1	1
@@ -2591,14 +2305,9 @@ a	b	c
 EXPLAIN SELECT * from t1 USE INDEX(idx2) WHERE b > 0 ORDER BY t1.a LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	idx2	10	NULL	2	Using where
-set use_sort_nest=0;
-EXPLAIN SELECT * from t1 USE INDEX(idx2) WHERE b > 0 ORDER BY t1.a LIMIT 2;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	idx2	10	NULL	2	Using where
 #
 # Index idx2 to be used for index scan(USE INDEX for ORDER BY  is used)
 #
-set use_sort_nest=1;
 SELECT * from t1 USE INDEX FOR ORDER BY(idx2)
 WHERE b > 0
 ORDER BY t1.a LIMIT 2;
@@ -2610,16 +2319,9 @@ WHERE b > 0
 ORDER BY t1.a LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	idx2	10	NULL	2	Using where
-set use_sort_nest=0;
-EXPLAIN SELECT * from t1 USE INDEX FOR ORDER BY(idx2)
-WHERE b > 0
-ORDER BY t1.a LIMIT 2;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	idx2	10	NULL	2	Using where
 #
 # Use Filesort as idx3 does not resolve ORDER BY clause
 #
-set use_sort_nest=1;
 SELECT * from t1 USE INDEX FOR ORDER BY(idx3)
 WHERE b > 0
 ORDER BY t1.a LIMIT 2;
@@ -2631,16 +2333,9 @@ WHERE b > 0
 ORDER BY t1.a LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	Using where; Using filesort
-set use_sort_nest=0;
-EXPLAIN SELECT * from t1 USE INDEX FOR ORDER BY(idx3)
-WHERE b > 0
-ORDER BY t1.a LIMIT 2;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	Using where; Using filesort
 #
 # Using index idx2 as idx1 is ignored
 #
-set use_sort_nest=1;
 SELECT * from t1 IGNORE INDEX(idx1)
 WHERE b > 0
 ORDER BY t1.a LIMIT 2;
@@ -2652,16 +2347,9 @@ WHERE b > 0
 ORDER BY t1.a LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	idx2	10	NULL	2	Using where
-set use_sort_nest=0;
-EXPLAIN SELECT * from t1 IGNORE INDEX(idx1)
-WHERE b > 0
-ORDER BY t1.a LIMIT 2;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	idx2	10	NULL	2	Using where
 #
 # Use index idx2 for sorting, it is forced here
 #
-set use_sort_nest=1;
 SELECT * from t1 FORCE INDEX(idx2)
 WHERE b > 0
 ORDER BY t1.a LIMIT 2;
@@ -2673,16 +2361,9 @@ WHERE b > 0
 ORDER BY t1.a LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	idx2	10	NULL	2	Using where
-set use_sort_nest=0;
-EXPLAIN SELECT * from t1 FORCE INDEX(idx2)
-WHERE b > 0
-ORDER BY t1.a LIMIT 2;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	idx2	10	NULL	2	Using where
 #
 # Use FILESORT as idx3 cannot resolve ORDER BY clause
 #
-set use_sort_nest=1;
 SELECT * from t1 FORCE INDEX FOR ORDER BY(idx3)
 WHERE b > 0
 ORDER BY t1.a LIMIT 2;
@@ -2694,12 +2375,6 @@ WHERE b > 0
 ORDER BY t1.a LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	Using where; Using filesort
-set use_sort_nest=0;
-EXPLAIN SELECT * from t1 FORCE INDEX FOR ORDER BY(idx3)
-WHERE b > 0
-ORDER BY t1.a LIMIT 2;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	Using where; Using filesort
 drop table t0,t1;
 #
 # SORT-NEST WITH SEMI JOINS
@@ -2724,7 +2399,6 @@ ANALYZE TABLE t4 PERSISTENT FOR ALL;
 #
 # SJM scan inside the sort-nest
 #
-set use_sort_nest=1;
 EXPLAIN SELECT t1.a, t2.a, t1.b,t2.b
 FROM t1, t2, t2 ot
 WHERE t1.a=t2.a AND t2.c=ot.b AND t2.b < 4 AND
@@ -2840,24 +2514,10 @@ a	a	b	b
 2	2	2	2
 1	1	1	1
 0	0	0	0
-set use_sort_nest=0;
-SELECT t1.a, t2.a, t1.b,t2.b
-FROM t1, t2, t2 ot
-WHERE t1.a=t2.a AND t2.c=ot.b AND t2.b < 4 AND
-t1.b IN (SELECT it1.b FROM t3 it1,t4 it2
-WHERE it1.a < 4 AND it1.a=it2.a)
-ORDER BY t2.b DESC ,t1.b DESC
-LIMIT 5;
-a	a	b	b
-3	3	3	3
-2	2	2	2
-1	1	1	1
-0	0	0	0
 #
 # SJM Lookup with sort-nest, where SJM lookup table is outside the
 # sort-nest
 #
-set use_sort_nest=1;
 EXPLAIN SELECT t1.a, t2.a, t1.b,t2.b
 FROM t1, t2, t2 ot
 WHERE t1.a=t2.a AND t2.b=ot.a AND t2.b < 4 AND
@@ -2977,19 +2637,6 @@ a	a	b	b
 2	2	2	2
 1	1	1	1
 0	0	0	0
-set use_sort_nest=0;
-SELECT t1.a, t2.a, t1.b,t2.b
-FROM t1, t2, t2 ot
-WHERE t1.a=t2.a AND t2.b=ot.a AND t2.b < 4 AND
-t1.b IN (SELECT it1.b FROM t3 it1,t4 it2
-WHERE it1.a < 4 AND it1.a=it2.a)
-ORDER BY t2.b DESC ,t1.b DESC
-LIMIT 5;
-a	a	b	b
-3	3	3	3
-2	2	2	2
-1	1	1	1
-0	0	0	0
 drop table t0,t1,t2,t3,t4;
 #
 # Firstmatch strategy
@@ -3011,7 +2658,6 @@ ANALYZE TABLE t1 PERSISTENT FOR ALL;
 ANALYZE TABLE t2 PERSISTENT FOR ALL;
 ANALYZE TABLE t3 PERSISTENT FOR ALL;
 ANALYZE TABLE t4 PERSISTENT FOR ALL;
-set use_sort_nest=1;
 EXPLAIN SELECT t1.a, t2.a, t1.b,t2.b
 FROM t1, t2
 WHERE t1.a=t2.a AND
@@ -3036,36 +2682,11 @@ a	a	b	b
 2	2	2	2
 1	1	1	1
 0	0	0	0
-set use_sort_nest=0;
-EXPLAIN SELECT t1.a, t2.a, t1.b,t2.b
-FROM t1, t2
-WHERE t1.a=t2.a AND
-t1.b IN (SELECT t3.b FROM t3,t4 WHERE t3.a <= 5 and t3.c = t2.b)
-ORDER BY t2.b DESC, t1.b DESC
-LIMIT 5;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	a	NULL	NULL	NULL	10	Using temporary; Using filesort
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	100	Using where; Using join buffer (flat, BNL join)
-1	PRIMARY	t3	ref	b	b	5	test.t1.b	2	Using where
-1	PRIMARY	t4	index	NULL	a	5	NULL	10	Using index; FirstMatch(t1)
-SELECT t1.a, t2.a, t1.b,t2.b
-FROM t1, t2
-WHERE t1.a=t2.a AND
-t1.b IN (SELECT t3.b FROM t3,t4 WHERE t3.a <= 5 and t3.c = t2.b)
-ORDER BY t2.b DESC, t1.b DESC
-LIMIT 5;
-a	a	b	b
-4	4	4	4
-3	3	3	3
-2	2	2	2
-1	1	1	1
-0	0	0	0
 #
 # Duplicate weedout
 #
 set @save_optimizer_switch= @@optimizer_switch;
 set optimizer_switch='firstmatch=off';
-set use_sort_nest=1;
 EXPLAIN SELECT t1.a, t2.a, t1.b,t2.b
 FROM t1, t2
 WHERE t1.a=t2.a AND
@@ -3090,30 +2711,6 @@ a	a	b	b
 2	2	2	2
 1	1	1	1
 0	0	0	0
-set use_sort_nest=0;
-EXPLAIN SELECT t1.a, t2.a, t1.b,t2.b
-FROM t1, t2
-WHERE t1.a=t2.a AND
-t1.b IN (SELECT t3.b FROM t3,t4 WHERE t3.a <= 5 and t3.c = t2.b)
-ORDER BY t2.b DESC, t1.b DESC
-LIMIT 5;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t3	ALL	b	NULL	NULL	NULL	10	Using where; Start temporary; Using temporary; Using filesort
-1	PRIMARY	t2	ALL	a	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
-1	PRIMARY	t4	index	NULL	a	5	NULL	10	Using index; Using join buffer (incremental, BNL join)
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	100	Using where; End temporary; Using join buffer (incremental, BNL join)
-SELECT t1.a, t2.a, t1.b,t2.b
-FROM t1, t2
-WHERE t1.a=t2.a AND
-t1.b IN (SELECT t3.b FROM t3,t4 WHERE t3.a <= 5 and t3.c = t2.b)
-ORDER BY t2.b DESC, t1.b DESC
-LIMIT 5;
-a	a	b	b
-4	4	4	4
-3	3	3	3
-2	2	2	2
-1	1	1	1
-0	0	0	0
 set @@optimizer_switch= @save_optimizer_switch;
 drop table t0,t1,t2,t3,t4;
 
@@ -3127,7 +2724,6 @@ create table t2 as SELECT * from t1 where a < 5;
 create table t3(a int, b int);
 INSERT INTO t3 SELECT seq-1, seq-1 FROM seq_1_to_100;
 <subquery2> outside the sort-nest
-set use_sort_nest=1;
 EXPLAIN SELECT * from t2,t1
 WHERE t2.b=t1.b
 AND
@@ -3218,21 +2814,7 @@ a	b	a	b
 2	2	2	2
 1	1	1	1
 0	0	0	0
-set use_sort_nest=0;
-SELECT * from t2,t1
-WHERE t2.b=t1.b
-AND
-t1.a IN (SELECT max(t3.a) FROM t3 GROUP BY t3.b)
-ORDER BY t2.a DESC,t1.a DESC
-LIMIT 5;
-a	b	a	b
-4	4	4	4
-3	3	3	3
-2	2	2	2
-1	1	1	1
-0	0	0	0
 <subquery2> inside the sort-nest
-set use_sort_nest=1;
 EXPLAIN SELECT * FROM t3,t2
 WHERE t3.b=t2.b AND
 t3.a IN (SELECT max(t1.a) FROM t1 GROUP BY t1.b)
@@ -3316,16 +2898,4 @@ a	b	a	b
 2	2	2	2
 1	1	1	1
 0	0	0	0
-set use_sort_nest=0;
-SELECT * FROM t3,t2
-WHERE t3.b=t2.b AND
-t3.a IN (SELECT max(t1.a) FROM t1 GROUP BY t1.b)
-ORDER BY t3.a DESC,t2.a DESC
-LIMIT 5;
-a	b	a	b
-4	4	4	4
-3	3	3	3
-2	2	2	2
-1	1	1	1
-0	0	0	0
 DROP TABLE t0,t1,t2,t3;
diff --git a/mysql-test/main/sort_nest.test b/mysql-test/main/sort_nest.test
index 6658432fec1..02f384d3957 100644
--- a/mysql-test/main/sort_nest.test
+++ b/mysql-test/main/sort_nest.test
@@ -1,6 +1,6 @@
 --source include/have_sequence.inc
 
-set use_sort_nest=1;
+set optimizer_switch='cost_based_order_by_limit=on';
 
 CREATE TABLE t0 (a int);
 INSERT INTO t0 SELECT seq-1 from seq_1_to_10;
@@ -594,7 +594,6 @@ WHERE t1.a=t2.a and t1.b= 4
 ORDER BY t1.b DESC
 LIMIT 5;
 
-set use_sort_nest=1;
 eval EXPLAIN $query;
 eval $query;
 
@@ -627,13 +626,9 @@ WHERE t1.a=t2.b and t2.a=t3.a
 ORDER BY abs(t3.a+t1.b) DESC
 LIMIT 5;
 
-set use_sort_nest=1;
 eval EXPLAIN $query;
 eval $query;
 
-set use_sort_nest=0;
-eval $query;
-
 --echo #
 --echo # No sort nest where ORDER BY item are expensive to compute like 
 --echo # stored functions, subqueries etc
@@ -654,12 +649,6 @@ WHERE t1.a=t2.b and t2.a=t3.a
 ORDER BY f1(t3.a+t1.b) DESC
 LIMIT 5;
 
-set use_sort_nest=1;
-eval EXPLAIN $query;
-eval EXPLAIN FORMAT=JSON $query;
-eval $query;
-
-set use_sort_nest=0;
 eval EXPLAIN $query;
 eval $query;
 
@@ -676,12 +665,7 @@ WHERE t1.a=t2.b AND t2.a=t3.a
 ORDER BY row_number() OVER (ORDER BY t1.a) DESC
 LIMIT 5;
 
-set use_sort_nest=1;
 eval EXPLAIN $query;
-eval EXPLAIN FORMAT=JSON $query;
-eval $query;
-
-set use_sort_nest=0;
 eval $query;
 
 --echo #
@@ -700,13 +684,9 @@ t1.b = (select t2.b from t2,t3,t4
         where t2.a=t1.a and t3.b=t4.b
         ORDER BY t2.c,t3.c limit 1);
 
-set use_sort_nest=1;
 eval EXPLAIN $query;
 eval $query;
 
-set use_sort_nest= 0;
-eval $query;
-
 --echo #
 --echo # Independent subqueries can have a sort-nest
 --echo #
@@ -719,14 +699,9 @@ t1.b = (select t2.b from t2,t3,t4
         where t2.a=t3.a and t3.b=t4.b
         ORDER BY t2.c,t3.c limit 1);
 
-set use_sort_nest=1;
-
 eval EXPLAIN $query;
 eval $query;
 
-set use_sort_nest= 0;
-eval $query;
-
 --echo #
 --echo # Sort nest inside a derived table
 --echo #
@@ -739,13 +714,9 @@ FROM (SELECT t1.a as t, t2.b as u, t1.b as v, t3.a as x
       ORDER BY t2.b DESC, t1.b DESC
       LIMIT 5)q;
 
-set use_sort_nest=1;
 eval EXPLAIN $query;
 eval $query;
 
-set use_sort_nest=0;
-eval $query;
-
 --echo #
 --echo # sort-nest(t2,t1) and independent subquery in the SELECT list
 --echo #
@@ -758,7 +729,6 @@ WHERE t1.a = t2.a
 ORDER BY t2.b DESC, t1.b DESC
 LIMIT 5;
 
-set use_sort_nest=1;
 eval EXPLAIN $query;
 eval $query;
 
@@ -774,13 +744,9 @@ WHERE t1.a = t2.a and t2.c=t3.c
 ORDER BY t2.b DESC, t1.b DESC
 LIMIT 5;
 
-set use_sort_nest=1;
 eval EXPLAIN $query;
 eval $query;
 
-set use_sort_nest= 0;
-eval $query;
-
 --echo #
 --echo # Dependent Subquery attached outside the sort-nest, references inside
 --echo # subquery changed to sort-nest field items
@@ -794,14 +760,10 @@ t1.b=(SELECT A.a FROM t3 A WHERE A.b > 1 and t3.b=t1.b limit 1)
 ORDER BY t2.b DESC, t1.b DESC
 LIMIT 5;
 
-set use_sort_nest=1;
 eval EXPLAIN $query;
 eval EXPLAIN FORMAT=JSON $query;
 eval $query;
 
-set use_sort_nest= 0;
-eval $query;
-
 --echo #
 --echo # sort-nest(t2,t1) and independent subquery in the ORDER BY clause
 --echo #
@@ -816,13 +778,9 @@ ORDER BY t2.b, t1.b,
          (SELECT A.a FROM t3 A WHERE A.a < 5 limit 1)
 LIMIT 5;
 
-set use_sort_nest=1;
 eval EXPLAIN $query;
 eval $query;
 
-set use_sort_nest= 0;
-eval $query;
-
 --echo #
 --echo # sort-nest(t2,t1) and dependent subquery in the ORDER BY clause
 --echo #
@@ -837,13 +795,9 @@ ORDER BY t2.b, t1.b,
          (SELECT A.a FROM t3 A WHERE A.a = t1.c limit 1)
 LIMIT 5;
 
-set use_sort_nest=1;
 eval EXPLAIN $query;
 eval $query;
 
-set use_sort_nest= 0;
-eval $query;
-
 drop table t0,t1,t2,t3,t4;
 
 CREATE TABLE t1 (a int, b int, c int, KEY a_b (a,b), KEY a_c (a,c));
@@ -858,12 +812,9 @@ insert into t1 values (1,7,2), (1,8,2), (1,9,2), (1,10,2), (1,11,2), (1,12,2);
 --echo #
 
 let $query= select a,b,c from t1 where a=1 and c=2 order by b limit 5;
-set use_sort_nest= 1;
 eval $query;
 eval explain $query;
 
-set use_sort_nest= 0;
-eval explain $query;
 drop table t1;
 
 --echo #
@@ -880,18 +831,14 @@ insert into t1 values (1,1,2);
 
 --echo #
 --echo # index key a_b, no need for filesort
---echo # Also index condition pushdown is used with use_sort_nest=1
+--echo # Also index condition pushdown is used here
 --echo #
 
 let $query= select a,b from t1 where a=1 and c=2 order by b limit 10;
 
-set use_sort_nest=1;
 eval $query;
 eval explain $query;
 
-set use_sort_nest=0;
-eval explain $query;
-
 drop table t1;
 
 CREATE TABLE t1(
@@ -908,26 +855,18 @@ INSERT INTO t1 VALUES (1,'a'), (2,'b'), (3,'c'), (4,'d');
 
 let $query= SELECT * FROM t1 WHERE a BETWEEN 1 and 2 ORDER BY a LIMIT 2;
 
-set use_sort_nest= 1;
 eval $query;
 eval EXPLAIN $query;
 
-set use_sort_nest= 0;
-eval EXPLAIN $query;
-
 --echo #
 --echo # Should not use index condition as ORDER by DESC is used
 --echo #
 
 let $query= SELECT * FROM t1 WHERE a BETWEEN 1 and 2 ORDER BY a DESC LIMIT 2;
 
-set use_sort_nest= 1;
 eval EXPLAIN $query;
 eval $query;
 
-set use_sort_nest= 0;
-eval EXPLAIN $query;
-
 drop table t1;
 
 create table t1(a int, b int, c int, key(a), key a_b(a,b)); # 10 rows
@@ -954,13 +893,9 @@ WHERE
   t1.b=t2.b
 ORDER BY t1.b LIMIT 10;
 
-set use_sort_nest= 1;
 eval $query;
 eval EXPLAIN $query;
 
-set use_sort_nest= 0;
-eval EXPLAIN $query;
-
 --echo #
 --echo #  Using ref access
 --echo #
@@ -971,19 +906,14 @@ WHERE
   t1.b=t2.b
 ORDER BY t1.b LIMIT 10;
 
-set use_sort_nest= 1;
 eval $query;
 eval EXPLAIN $query;
 
-set use_sort_nest= 0;
-eval EXPLAIN $query;
-
 drop table t1,t2;
 
 
 --echo # TESTS with INDEX HINTS
 
-set use_sort_nest=1;
 create table t0 (a int);
 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 create table t1 (a int, b int,c int, key idx1(a), key idx2(a,b), key idx3(c));
@@ -998,25 +928,17 @@ analyze table t1 persistent for all;
 --echo #
 
 let $query= SELECT * from t1 where b > 0 order by t1.a limit 2;
-set use_sort_nest=1;
 eval $query;
 eval EXPLAIN $query;
 
-set use_sort_nest=0;
-eval EXPLAIN $query;
-
 --echo #
 --echo # Index idx2 to be used for index scan(USE INDEX is used)
 --echo #
 
 let $query= SELECT * from t1 USE INDEX(idx2) WHERE b > 0 ORDER BY t1.a LIMIT 2;
-set use_sort_nest=1;
 eval $query;
 eval EXPLAIN $query;
 
-set use_sort_nest=0;
-eval EXPLAIN $query;
-
 --echo #
 --echo # Index idx2 to be used for index scan(USE INDEX for ORDER BY  is used)
 --echo #
@@ -1026,13 +948,9 @@ SELECT * from t1 USE INDEX FOR ORDER BY(idx2)
 WHERE b > 0
 ORDER BY t1.a LIMIT 2;
 
-set use_sort_nest=1;
 eval $query;
 eval EXPLAIN $query;
 
-set use_sort_nest=0;
-eval EXPLAIN $query;
-
 --echo #
 --echo # Use Filesort as idx3 does not resolve ORDER BY clause
 --echo #
@@ -1042,13 +960,9 @@ SELECT * from t1 USE INDEX FOR ORDER BY(idx3)
 WHERE b > 0
 ORDER BY t1.a LIMIT 2;
 
-set use_sort_nest=1;
 eval $query;
 eval EXPLAIN $query;
 
-set use_sort_nest=0;
-eval EXPLAIN $query;
-
 --echo #
 --echo # Using index idx2 as idx1 is ignored
 --echo #
@@ -1058,13 +972,9 @@ SELECT * from t1 IGNORE INDEX(idx1)
 WHERE b > 0
 ORDER BY t1.a LIMIT 2;
 
-set use_sort_nest=1;
 eval $query;
 eval EXPLAIN $query;
 
-set use_sort_nest=0;
-eval EXPLAIN $query;
-
 --echo #
 --echo # Use index idx2 for sorting, it is forced here
 --echo #
@@ -1074,13 +984,9 @@ SELECT * from t1 FORCE INDEX(idx2)
 WHERE b > 0
 ORDER BY t1.a LIMIT 2;
 
-set use_sort_nest=1;
 eval $query;
 eval EXPLAIN $query;
 
-set use_sort_nest=0;
-eval EXPLAIN $query;
-
 --echo #
 --echo # Use FILESORT as idx3 cannot resolve ORDER BY clause
 --echo #
@@ -1090,13 +996,9 @@ SELECT * from t1 FORCE INDEX FOR ORDER BY(idx3)
 WHERE b > 0
 ORDER BY t1.a LIMIT 2;
 
-set use_sort_nest=1;
 eval $query;
 eval EXPLAIN $query;
 
-set use_sort_nest=0;
-eval EXPLAIN $query;
-
 drop table t0,t1;
 
 --echo #
@@ -1138,14 +1040,10 @@ FROM t1, t2, t2 ot
 ORDER BY t2.b DESC ,t1.b DESC
 LIMIT 5;
 
-set use_sort_nest=1;
 eval EXPLAIN $query;
 eval EXPLAIN FORMAT=JSON $query;
 eval $query;
 
-set use_sort_nest=0;
-eval $query;
-
 --echo #
 --echo # SJM Lookup with sort-nest, where SJM lookup table is outside the
 --echo # sort-nest
@@ -1160,14 +1058,10 @@ FROM t1, t2, t2 ot
 ORDER BY t2.b DESC ,t1.b DESC
 LIMIT 5;
 
-set use_sort_nest=1;
 eval EXPLAIN $query;
 eval EXPLAIN FORMAT=JSON $query;
 eval $query;
 
-set use_sort_nest=0;
-eval $query;
-
 drop table t0,t1,t2,t3,t4;
 
 
@@ -1205,15 +1099,9 @@ FROM t1, t2
 ORDER BY t2.b DESC, t1.b DESC
 LIMIT 5;
 
-set use_sort_nest=1;
-eval EXPLAIN $query;
-eval $query;
-
-set use_sort_nest=0;
 eval EXPLAIN $query;
 eval $query;
 
-
 --echo #
 --echo # Duplicate weedout
 --echo #
@@ -1229,11 +1117,6 @@ FROM t1, t2
 ORDER BY t2.b DESC, t1.b DESC
 LIMIT 5;
 
-set use_sort_nest=1;
-eval EXPLAIN $query;
-eval $query;
-
-set use_sort_nest=0;
 eval EXPLAIN $query;
 eval $query;
 
@@ -1262,14 +1145,10 @@ t1.a IN (SELECT max(t3.a) FROM t3 GROUP BY t3.b)
 ORDER BY t2.a DESC,t1.a DESC
 LIMIT 5;
 
-set use_sort_nest=1;
 eval EXPLAIN $query;
 eval EXPLAIN FORMAT=JSON $query;
 eval $query;
 
-set use_sort_nest=0;
-eval $query;
-
 --echo <subquery2> inside the sort-nest
 
 let $query=
@@ -1279,12 +1158,8 @@ t3.a IN (SELECT max(t1.a) FROM t1 GROUP BY t1.b)
 ORDER BY t3.a DESC,t2.a DESC
 LIMIT 5;
 
-set use_sort_nest=1;
 eval EXPLAIN $query;
 eval EXPLAIN FORMAT=JSON $query;
 eval $query;
 
-set use_sort_nest=0;
-eval $query;
-
 DROP TABLE t0,t1,t2,t3;
diff --git a/mysql-test/main/sort_nest_dbt3.result b/mysql-test/main/sort_nest_dbt3.result
index e20b338fbda..2962b628568 100644
--- a/mysql-test/main/sort_nest_dbt3.result
+++ b/mysql-test/main/sort_nest_dbt3.result
@@ -12,42 +12,15 @@ dbt3.lineitem	analyze	status	OK
 Table	Op	Msg_type	Msg_text
 dbt3.nation	analyze	status	Engine-independent statistics collected
 dbt3.nation	analyze	status	OK
-# done to avoid filter for now
+# done use filter for now
 set optimizer_switch='rowid_filter=off';
-set use_sort_nest=1;
+set optimizer_switch='cost_based_order_by_limit=on';
 #
 # USING INDEXES FOR ORDERING
 #
 #
 # Using index scan on first table
 #
-set use_sort_nest=1;
-explain SELECT
-c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal
-FROM
-orders, customer
-WHERE
-c_custkey= o_custkey
-ORDER BY o_orderDATE DESC 
-LIMIT 5;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	orders	index	i_o_custkey	i_o_orderdate	4	NULL	5	Using where
-1	SIMPLE	customer	eq_ref	PRIMARY	PRIMARY	4	dbt3.orders.o_custkey	1	
-SELECT
-c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal
-FROM
-orders, customer
-WHERE
-c_custkey= o_custkey
-ORDER BY o_orderDATE DESC 
-LIMIT 5;
-c_nationkey	c_name	o_orderDate	o_totalprice	c_acctbal
-16	Customer#000000088	1998-08-02	131752.07	8031.44
-0	Customer#000000080	1998-07-30	141858.97	7383.53
-10	Customer#000000049	1998-07-29	37776.79	4573.94
-3	Customer#000000022	1998-07-28	139104.17	591.98
-3	Customer#000000022	1998-07-27	82746.74	591.98
-set use_sort_nest=0;
 explain SELECT
 c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal
 FROM
@@ -78,35 +51,6 @@ c_nationkey	c_name	o_orderDate	o_totalprice	c_acctbal
 # the ordering
 #
 alter table orders add key o_orderdate(o_orderDATE, o_custkey);
-set use_sort_nest=1;
-explain SELECT
-o_custkey, c_name, o_orderDate, o_totalprice, c_acctbal
-FROM
-orders, customer 
-WHERE
-c_custkey= o_custkey AND
-o_orderDATE >='1997-07-30' and o_orderDATE <= '1998-07-30' 
-ORDER BY o_orderDATE, o_custkey DESC 
-LIMIT 5;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	orders	range	i_o_orderdate,i_o_custkey,o_orderdate	i_o_orderdate	4	NULL	242	Using index condition; Using where; Using filesort
-1	SIMPLE	customer	eq_ref	PRIMARY	PRIMARY	4	dbt3.orders.o_custkey	1	
-SELECT
-o_custkey, c_name, o_orderDate, o_totalprice, c_acctbal
-FROM
-orders, customer 
-WHERE
-c_custkey= o_custkey AND
-o_orderDATE >='1997-07-30' and o_orderDATE <= '1998-07-30' 
-ORDER BY o_orderDATE, o_custkey DESC 
-LIMIT 5;
-o_custkey	c_name	o_orderDate	o_totalprice	c_acctbal
-130	Customer#000000130	1997-07-30	67979.49	5073.58
-101	Customer#000000101	1997-07-31	79189.58	7470.96
-13	Customer#000000013	1997-07-31	125188.72	3857.34
-64	Customer#000000064	1997-08-03	76164.41	-646.64
-50	Customer#000000050	1997-08-03	20791.5	4266.13
-set use_sort_nest=0;
 explain SELECT
 o_custkey, c_name, o_orderDate, o_totalprice, c_acctbal
 FROM
@@ -141,41 +85,6 @@ alter table orders drop key o_orderDate;
 #
 # Filesort on first table (orders)
 #
-set use_sort_nest=1;
-explain SELECT
-c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal, n_name
-FROM
-orders, customer, nation 
-WHERE
-c_custkey= o_custkey AND c_nationkey=n_nationkey AND
-o_orderDATE >='1997-01-30' and o_orderDATE <= '1997-12-31' 
-ORDER BY o_totalprice DESC 
-LIMIT 10;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	orders	range	i_o_orderdate,i_o_custkey	i_o_orderdate	4	NULL	197	Using index condition; Using where; Using filesort
-1	SIMPLE	customer	eq_ref	PRIMARY,i_c_nationkey	PRIMARY	4	dbt3.orders.o_custkey	1	Using where
-1	SIMPLE	nation	eq_ref	PRIMARY	PRIMARY	4	dbt3.customer.c_nationkey	1	
-SELECT
-c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal, n_name
-FROM
-orders, customer, nation 
-WHERE
-c_custkey= o_custkey AND c_nationkey=n_nationkey AND
-o_orderDATE >='1997-01-30' and o_orderDATE <= '1997-12-31' 
-ORDER BY o_totalprice DESC 
-LIMIT 10;
-c_nationkey	c_name	o_orderDate	o_totalprice	c_acctbal	n_name
-5	Customer#000000010	1997-04-04	258779.02	2753.54	ETHIOPIA
-0	Customer#000000076	1997-08-24	231831.35	5745.33	ALGERIA
-9	Customer#000000094	1997-10-17	224382.57	5500.11	INDONESIA
-6	Customer#000000046	1997-05-26	216826.73	5744.59	FRANCE
-3	Customer#000000005	1997-04-23	210643.96	794.47	CANADA
-11	Customer#000000148	1997-07-25	206179.68	2135.6	IRAQ
-2	Customer#000000101	1997-07-28	204163.1	7470.96	BRAZIL
-3	Customer#000000005	1997-11-09	190142.17	794.47	CANADA
-9	Customer#000000094	1997-11-30	187516.29	5500.11	INDONESIA
-3	Customer#000000005	1997-11-12	185496.66	794.47	CANADA
-set use_sort_nest=0;
 explain SELECT
 c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal, n_name
 FROM
@@ -212,56 +121,6 @@ c_nationkey	c_name	o_orderDate	o_totalprice	c_acctbal	n_name
 #
 # Filesort on first table (lineitem)
 #
-show create table orders;
-Table	Create Table
-orders	CREATE TABLE `orders` (
-  `o_orderkey` int(11) NOT NULL,
-  `o_custkey` int(11) DEFAULT NULL,
-  `o_orderstatus` char(1) DEFAULT NULL,
-  `o_totalprice` double DEFAULT NULL,
-  `o_orderDATE` date DEFAULT NULL,
-  `o_orderpriority` char(15) DEFAULT NULL,
-  `o_clerk` char(15) DEFAULT NULL,
-  `o_shippriority` int(11) DEFAULT NULL,
-  `o_comment` varchar(79) DEFAULT NULL,
-  PRIMARY KEY (`o_orderkey`),
-  KEY `i_o_orderdate` (`o_orderDATE`),
-  KEY `i_o_custkey` (`o_custkey`)
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
-set use_sort_nest=1;
-explain SELECT
-l_extendedprice, o_orderkey, o_totalprice, l_shipDATE
-FROM
-orders, lineitem
-WHERE
-o_orderkey = l_orderkey AND
-l_shipDATE >= '1996-01-01' AND l_shipDATE <= '1996-12-31'
-            ORDER BY l_extendedprice DESC
-LIMIT 10;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	lineitem	range	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	i_l_shipdate	4	NULL	812	Using index condition; Using filesort
-1	SIMPLE	orders	eq_ref	PRIMARY	PRIMARY	4	dbt3.lineitem.l_orderkey	1	
-SELECT
-l_extendedprice, o_orderkey, o_totalprice, l_shipDATE
-FROM
-orders, lineitem
-WHERE
-o_orderkey = l_orderkey AND
-l_shipDATE >= '1996-01-01' AND l_shipDATE <= '1996-12-31'
-            ORDER BY l_extendedprice DESC
-LIMIT 10;
-l_extendedprice	o_orderkey	o_totalprice	l_shipDATE
-54559.5	1574	179923.54	1996-12-14
-53508.5	2342	104038.78	1996-08-28
-53458	1153	220727.97	1996-06-27
-53075.82	2721	59180.25	1996-02-14
-52830.33	1284	106122.38	1996-04-11
-52657.5	2276	141159.63	1996-07-13
-52290.84	4773	196080.26	1996-01-26
-51752.16	1607	166335.03	1996-02-22
-51751.35	1700	89143.36	1996-09-26
-51709.4	1254	94649.25	1996-03-07
-set use_sort_nest=0;
 explain SELECT
 l_extendedprice, o_orderkey, o_totalprice, l_shipDATE
 FROM
@@ -300,7 +159,6 @@ l_extendedprice	o_orderkey	o_totalprice	l_shipDATE
 #
 #  FILESORT USING SORT-NEST on (orders, customer)
 #
-set use_sort_nest=1;
 explain SELECT
 c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal, n_name
 FROM
@@ -335,44 +193,9 @@ c_nationkey	c_name	o_orderDate	o_totalprice	c_acctbal	n_name
 11	Customer#000000148	1997-07-25	206179.68	2135.6	IRAQ
 18	Customer#000000082	1997-07-20	15082.82	9468.34	CHINA
 10	Customer#000000055	1997-07-16	46380.69	4572.11	IRAN
-set use_sort_nest=0;
-explain SELECT
-c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal, n_name
-FROM
-orders, customer, nation 
-WHERE
-c_custkey= o_custkey AND c_nationkey=n_nationkey AND
-o_orderDATE >='1997-07-01' and o_orderDATE <= '1997-07-31' 
-ORDER BY o_orderDATE DESC, c_acctbal DESC
-LIMIT 10;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	orders	range	i_o_orderdate,i_o_custkey	i_o_orderdate	4	NULL	20	Using index condition; Using where; Using temporary; Using filesort
-1	SIMPLE	customer	eq_ref	PRIMARY,i_c_nationkey	PRIMARY	4	dbt3.orders.o_custkey	1	Using where
-1	SIMPLE	nation	eq_ref	PRIMARY	PRIMARY	4	dbt3.customer.c_nationkey	1	
-SELECT
-c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal, n_name
-FROM
-orders, customer, nation 
-WHERE
-c_custkey= o_custkey AND c_nationkey=n_nationkey AND
-o_orderDATE >='1997-07-01' and o_orderDATE <= '1997-07-31' 
-ORDER BY o_orderDATE DESC, c_acctbal DESC
-LIMIT 10;
-c_nationkey	c_name	o_orderDate	o_totalprice	c_acctbal	n_name
-2	Customer#000000101	1997-07-31	79189.58	7470.96	BRAZIL
-3	Customer#000000013	1997-07-31	125188.72	3857.34	CANADA
-9	Customer#000000130	1997-07-30	67979.49	5073.58	INDONESIA
-11	Customer#000000148	1997-07-29	88448.24	2135.6	IRAQ
-2	Customer#000000101	1997-07-28	204163.1	7470.96	BRAZIL
-11	Customer#000000052	1997-07-28	46393.97	5630.28	IRAQ
-4	Customer#000000004	1997-07-25	11405.4	2866.83	EGYPT
-11	Customer#000000148	1997-07-25	206179.68	2135.6	IRAQ
-18	Customer#000000082	1997-07-20	15082.82	9468.34	CHINA
-10	Customer#000000055	1997-07-16	46380.69	4572.11	IRAN
 #
 # Sort-nest on table (lineitem, orders)
 #
-set use_sort_nest=1;
 explain SELECT
 c_acctbal, c_name, o_orderDate, l_extendedprice, l_orderkey
 FROM
@@ -427,65 +250,9 @@ c_acctbal	c_name	o_orderDate	l_extendedprice	l_orderkey
 9904.28	Customer#000000043	1998-02-06	47339.52	5671
 8959.65	Customer#000000149	1996-11-12	47247.52	5606
 5744.59	Customer#000000046	1998-04-14	45589.72	5604
-set use_sort_nest=0;
-explain SELECT
-c_acctbal, c_name, o_orderDate, l_extendedprice, l_orderkey
-FROM
-orders, customer, lineitem
-WHERE
-c_custkey= o_custkey
-AND
-l_orderkey = o_orderkey
-AND
-l_orderkey between 5500 AND 6000
-AND
-l_shipDATE >= '1997-01-01' and l_shipDATE <=  '1998-08-10'
-            ORDER BY l_extendedprice DESC, o_orderDATE DESC
-LIMIT 20;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	lineitem	range	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	i_l_orderkey_quantity	4	NULL	338	Using index condition; Using where; Using temporary; Using filesort
-1	SIMPLE	orders	eq_ref	PRIMARY,i_o_custkey	PRIMARY	4	dbt3.lineitem.l_orderkey	1	Using where
-1	SIMPLE	customer	eq_ref	PRIMARY	PRIMARY	4	dbt3.orders.o_custkey	1	
-SELECT
-c_acctbal, c_name, o_orderDate, l_extendedprice, l_orderkey
-FROM
-orders, customer, lineitem
-WHERE
-c_custkey= o_custkey
-AND
-l_orderkey = o_orderkey
-AND
-l_orderkey between 5500 AND 6000
-AND
-l_shipDATE >= '1997-01-01' and l_shipDATE <=  '1998-08-10'
-            ORDER BY l_extendedprice DESC, o_orderDATE DESC
-LIMIT 20;
-c_acctbal	c_name	o_orderDate	l_extendedprice	l_orderkey
-1842.49	Customer#000000124	1997-11-06	54759.5	5857
-2135.6	Customer#000000148	1997-04-14	53909.8	5952
-4681.03	Customer#000000016	1998-07-06	53811.31	5761
-794.47	Customer#000000005	1997-04-23	53758.5	5859
--234.12	Customer#000000125	1997-01-11	53468.31	5829
-5121.28	Customer#000000079	1998-05-31	53208	5633
-5744.59	Customer#000000046	1998-04-14	50770.37	5604
--917.75	Customer#000000037	1997-07-13	50310.72	5793
-121.65	Customer#000000002	1998-05-28	49830.24	5507
-121.65	Customer#000000002	1998-05-28	49542.24	5507
-7470.96	Customer#000000101	1997-05-27	49411.82	5923
-5327.38	Customer#000000095	1997-10-04	48859.36	5505
-1842.49	Customer#000000124	1997-11-06	48661.41	5857
-4573.94	Customer#000000049	1997-02-14	48557.11	5762
--646.64	Customer#000000064	1997-01-01	48219.92	5895
--646.64	Customer#000000064	1997-01-01	48039.64	5895
-5121.28	Customer#000000079	1998-05-31	48004.8	5633
-9904.28	Customer#000000043	1998-02-06	47339.52	5671
-8959.65	Customer#000000149	1996-11-12	47247.52	5606
-5744.59	Customer#000000046	1998-04-14	45589.72	5604
-########################################################################
 #
 # Sort-nest on table (customer, orders, lineitem)
 #
-set use_sort_nest=1;
 explain SELECT
 l_extendedprice, c_acctbal, o_totalprice, o_orderDate, l_orderkey
 FROM
@@ -547,65 +314,4 @@ l_extendedprice	c_acctbal	o_totalprice	o_orderDate	l_orderkey
 28948.59	6819.74	182966.39	1994-07-17	5572
 26732.43	9561.95	101429.61	1993-04-21	5670
 24590.68	9561.95	95312.81	1992-03-28	5953
-set use_sort_nest=0;
-explain SELECT
-l_extendedprice, c_acctbal, o_totalprice, o_orderDate, l_orderkey
-FROM
-orders, customer, lineitem, nation
-WHERE
-c_custkey= o_custkey
-AND
-l_orderkey = o_orderkey
-AND
-n_nationkey = c_nationkey
-AND
-l_orderkey between 5500 AND 6000
-AND
-c_custkey between 1 and 10
-ORDER BY
-l_extendedprice DESC, c_acctbal DESC, o_totalprice DESC
-LIMIT 20;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	customer	range	PRIMARY,i_c_nationkey	PRIMARY	4	NULL	7	Using index condition; Using where; Using temporary; Using filesort
-1	SIMPLE	nation	eq_ref	PRIMARY	PRIMARY	4	dbt3.customer.c_nationkey	1	Using index
-1	SIMPLE	orders	ref	PRIMARY,i_o_custkey	i_o_custkey	5	dbt3.customer.c_custkey	15	Using where
-1	SIMPLE	lineitem	ref	PRIMARY,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	dbt3.orders.o_orderkey	4	
-SELECT
-l_extendedprice, c_acctbal, o_totalprice, o_orderDate, l_orderkey
-FROM
-orders, customer, lineitem, nation
-WHERE
-c_custkey= o_custkey
-AND
-l_orderkey = o_orderkey
-AND
-n_nationkey = c_nationkey
-AND
-l_orderkey between 5500 AND 6000
-AND
-c_custkey between 1 and 10
-ORDER BY
-l_extendedprice DESC, c_acctbal DESC, o_totalprice DESC
-LIMIT 20;
-l_extendedprice	c_acctbal	o_totalprice	o_orderDate	l_orderkey
-53758.5	794.47	210643.96	1997-04-23	5859
-49830.24	121.65	140363.7	1998-05-28	5507
-49542.24	121.65	140363.7	1998-05-28	5507
-48745.11	6819.74	122823.78	1993-04-05	5794
-47992.64	6819.74	140838.11	1998-06-26	5763
-47615.98	6819.74	182966.39	1994-07-17	5572
-46705.74	9561.95	101429.61	1993-04-21	5670
-44467.59	121.65	44777.63	1992-07-08	5893
-44442.3	6819.74	122823.78	1993-04-05	5794
-39723.6	794.47	210643.96	1997-04-23	5859
-37048.32	9561.95	95312.81	1992-03-28	5953
-36860.25	794.47	210643.96	1997-04-23	5859
-32996.16	6819.74	140838.11	1998-06-26	5763
-31416.68	6819.74	182966.39	1994-07-17	5572
-31219.32	794.47	210643.96	1997-04-23	5859
-31042.34	9561.95	95312.81	1992-03-28	5953
-29462.13	794.47	210643.96	1997-04-23	5859
-28948.59	6819.74	182966.39	1994-07-17	5572
-26732.43	9561.95	101429.61	1993-04-21	5670
-24590.68	9561.95	95312.81	1992-03-28	5953
 drop database dbt3;
diff --git a/mysql-test/main/sort_nest_dbt3.test b/mysql-test/main/sort_nest_dbt3.test
index e32e7d7528c..cb81ab518b7 100644
--- a/mysql-test/main/sort_nest_dbt3.test
+++ b/mysql-test/main/sort_nest_dbt3.test
@@ -10,9 +10,9 @@ analyze table lineitem persistent for all;
 analyze table nation   persistent for all;
 --enable_query_log
 
---echo # done to avoid filter for now
+--echo # done use filter for now
 set optimizer_switch='rowid_filter=off';
-set use_sort_nest=1;
+set optimizer_switch='cost_based_order_by_limit=on';
 
 --echo #
 --echo # USING INDEXES FOR ORDERING
@@ -32,11 +32,6 @@ let $query= SELECT
             ORDER BY o_orderDATE DESC 
             LIMIT 5;
 
-set use_sort_nest=1;
-eval explain $query;
-eval $query;
-
-set use_sort_nest=0;
 eval explain $query;
 eval $query;
 
@@ -57,18 +52,11 @@ let $query= SELECT
             ORDER BY o_orderDATE, o_custkey DESC 
             LIMIT 5;
 
-set use_sort_nest=1;
-eval explain $query;
-eval $query;
-
-set use_sort_nest=0;
 eval explain $query;
 eval $query;
 
 alter table orders drop key o_orderDate;
 
-###############################################################################
-
 --echo #
 --echo # USING FILESORT
 --echo #
@@ -87,11 +75,6 @@ let $query= SELECT
             ORDER BY o_totalprice DESC 
             LIMIT 10;
 
-set use_sort_nest=1;
-eval explain $query;
-eval $query;
-
-set use_sort_nest=0;
 eval explain $query;
 eval $query;
 
@@ -100,8 +83,6 @@ eval $query;
 --echo # Filesort on first table (lineitem)
 --echo #
 
-show create table orders;
-
 let $query= SELECT
               l_extendedprice, o_orderkey, o_totalprice, l_shipDATE
             FROM
@@ -112,17 +93,9 @@ let $query= SELECT
             ORDER BY l_extendedprice DESC
             LIMIT 10;
 
-set use_sort_nest=1;
 eval explain $query;
 eval $query;
 
-set use_sort_nest=0;
-eval explain $query;
-eval $query;
-
-###############################################################################
-
-
 --echo #
 --echo # Using Filesort with Sort Nest
 --echo #
@@ -141,16 +114,9 @@ let $query= SELECT
             ORDER BY o_orderDATE DESC, c_acctbal DESC
             LIMIT 10;
 
-set use_sort_nest=1;
-eval explain $query;
-eval $query;
-
-set use_sort_nest=0;
 eval explain $query;
 eval $query;
 
-
-
 --echo #
 --echo # Sort-nest on table (lineitem, orders)
 --echo #
@@ -171,16 +137,9 @@ let $query= SELECT
             LIMIT 20;
 
 
-set use_sort_nest=1;
-eval explain $query;
-eval $query;
-
-set use_sort_nest=0;
 eval explain $query;
 eval $query;
 
---echo ########################################################################
-
 --echo #
 --echo # Sort-nest on table (customer, orders, lineitem)
 --echo #
@@ -204,13 +163,7 @@ let $query= SELECT
             LIMIT 20;
 
 
-set use_sort_nest=1;
 eval explain $query;
 eval $query;
 
-set use_sort_nest=0;
-eval explain $query;
-eval $query;
-
-
 drop database dbt3;
diff --git a/mysql-test/main/sort_nest_dbt3_innodb.result b/mysql-test/main/sort_nest_dbt3_innodb.result
index 9c4c8144225..e449dfe1bcf 100644
--- a/mysql-test/main/sort_nest_dbt3_innodb.result
+++ b/mysql-test/main/sort_nest_dbt3_innodb.result
@@ -13,42 +13,15 @@ dbt3.lineitem	analyze	status	OK
 Table	Op	Msg_type	Msg_text
 dbt3.nation	analyze	status	Engine-independent statistics collected
 dbt3.nation	analyze	status	OK
-# done to avoid filter for now
+# done use filter for now
 set optimizer_switch='rowid_filter=off';
-set use_sort_nest=1;
+set optimizer_switch='cost_based_order_by_limit=on';
 #
 # USING INDEXES FOR ORDERING
 #
 #
 # Using index scan on first table
 #
-set use_sort_nest=1;
-explain SELECT
-c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal
-FROM
-orders, customer
-WHERE
-c_custkey= o_custkey
-ORDER BY o_orderDATE DESC 
-LIMIT 5;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	orders	index	i_o_custkey	i_o_orderdate	4	NULL	5	Using where
-1	SIMPLE	customer	eq_ref	PRIMARY	PRIMARY	4	dbt3.orders.o_custkey	1	
-SELECT
-c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal
-FROM
-orders, customer
-WHERE
-c_custkey= o_custkey
-ORDER BY o_orderDATE DESC 
-LIMIT 5;
-c_nationkey	c_name	o_orderDate	o_totalprice	c_acctbal
-16	Customer#000000088	1998-08-02	131752.07	8031.44
-0	Customer#000000080	1998-07-30	141858.97	7383.53
-10	Customer#000000049	1998-07-29	37776.79	4573.94
-3	Customer#000000022	1998-07-28	139104.17	591.98
-3	Customer#000000022	1998-07-27	82746.74	591.98
-set use_sort_nest=0;
 explain SELECT
 c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal
 FROM
@@ -79,35 +52,6 @@ c_nationkey	c_name	o_orderDate	o_totalprice	c_acctbal
 # the ordering
 #
 alter table orders add key o_orderdate(o_orderDATE, o_custkey);
-set use_sort_nest=1;
-explain SELECT
-o_custkey, c_name, o_orderDate, o_totalprice, c_acctbal
-FROM
-orders, customer 
-WHERE
-c_custkey= o_custkey AND
-o_orderDATE >='1997-07-30' and o_orderDATE <= '1998-07-30' 
-ORDER BY o_orderDATE, o_custkey DESC 
-LIMIT 5;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	orders	range	i_o_orderdate,i_o_custkey,o_orderdate	i_o_orderdate	4	NULL	217	Using index condition; Using where; Using filesort
-1	SIMPLE	customer	eq_ref	PRIMARY	PRIMARY	4	dbt3.orders.o_custkey	1	
-SELECT
-o_custkey, c_name, o_orderDate, o_totalprice, c_acctbal
-FROM
-orders, customer 
-WHERE
-c_custkey= o_custkey AND
-o_orderDATE >='1997-07-30' and o_orderDATE <= '1998-07-30' 
-ORDER BY o_orderDATE, o_custkey DESC 
-LIMIT 5;
-o_custkey	c_name	o_orderDate	o_totalprice	c_acctbal
-130	Customer#000000130	1997-07-30	67979.49	5073.58
-101	Customer#000000101	1997-07-31	79189.58	7470.96
-13	Customer#000000013	1997-07-31	125188.72	3857.34
-64	Customer#000000064	1997-08-03	76164.41	-646.64
-50	Customer#000000050	1997-08-03	20791.5	4266.13
-set use_sort_nest=0;
 explain SELECT
 o_custkey, c_name, o_orderDate, o_totalprice, c_acctbal
 FROM
@@ -142,41 +86,6 @@ alter table orders drop key o_orderDate;
 #
 # Filesort on first table (orders)
 #
-set use_sort_nest=1;
-explain SELECT
-c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal, n_name
-FROM
-orders, customer, nation 
-WHERE
-c_custkey= o_custkey AND c_nationkey=n_nationkey AND
-o_orderDATE >='1997-01-30' and o_orderDATE <= '1997-12-31' 
-ORDER BY o_totalprice DESC 
-LIMIT 10;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	orders	range	i_o_orderdate,i_o_custkey	i_o_orderdate	4	NULL	201	Using index condition; Using where; Using filesort
-1	SIMPLE	customer	eq_ref	PRIMARY,i_c_nationkey	PRIMARY	4	dbt3.orders.o_custkey	1	Using where
-1	SIMPLE	nation	eq_ref	PRIMARY	PRIMARY	4	dbt3.customer.c_nationkey	1	
-SELECT
-c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal, n_name
-FROM
-orders, customer, nation 
-WHERE
-c_custkey= o_custkey AND c_nationkey=n_nationkey AND
-o_orderDATE >='1997-01-30' and o_orderDATE <= '1997-12-31' 
-ORDER BY o_totalprice DESC 
-LIMIT 10;
-c_nationkey	c_name	o_orderDate	o_totalprice	c_acctbal	n_name
-5	Customer#000000010	1997-04-04	258779.02	2753.54	ETHIOPIA
-0	Customer#000000076	1997-08-24	231831.35	5745.33	ALGERIA
-9	Customer#000000094	1997-10-17	224382.57	5500.11	INDONESIA
-6	Customer#000000046	1997-05-26	216826.73	5744.59	FRANCE
-3	Customer#000000005	1997-04-23	210643.96	794.47	CANADA
-11	Customer#000000148	1997-07-25	206179.68	2135.6	IRAQ
-2	Customer#000000101	1997-07-28	204163.1	7470.96	BRAZIL
-3	Customer#000000005	1997-11-09	190142.17	794.47	CANADA
-9	Customer#000000094	1997-11-30	187516.29	5500.11	INDONESIA
-3	Customer#000000005	1997-11-12	185496.66	794.47	CANADA
-set use_sort_nest=0;
 explain SELECT
 c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal, n_name
 FROM
@@ -213,56 +122,6 @@ c_nationkey	c_name	o_orderDate	o_totalprice	c_acctbal	n_name
 #
 # Filesort on first table (lineitem)
 #
-show create table orders;
-Table	Create Table
-orders	CREATE TABLE `orders` (
-  `o_orderkey` int(11) NOT NULL,
-  `o_custkey` int(11) DEFAULT NULL,
-  `o_orderstatus` char(1) DEFAULT NULL,
-  `o_totalprice` double DEFAULT NULL,
-  `o_orderDATE` date DEFAULT NULL,
-  `o_orderpriority` char(15) DEFAULT NULL,
-  `o_clerk` char(15) DEFAULT NULL,
-  `o_shippriority` int(11) DEFAULT NULL,
-  `o_comment` varchar(79) DEFAULT NULL,
-  PRIMARY KEY (`o_orderkey`),
-  KEY `i_o_orderdate` (`o_orderDATE`),
-  KEY `i_o_custkey` (`o_custkey`)
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-set use_sort_nest=1;
-explain SELECT
-l_extendedprice, o_orderkey, o_totalprice, l_shipDATE
-FROM
-orders, lineitem
-WHERE
-o_orderkey = l_orderkey AND
-l_shipDATE >= '1996-01-01' AND l_shipDATE <= '1996-12-31'
-            ORDER BY l_extendedprice DESC
-LIMIT 10;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	lineitem	range	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	i_l_shipdate	4	NULL	910	Using index condition; Using filesort
-1	SIMPLE	orders	eq_ref	PRIMARY	PRIMARY	4	dbt3.lineitem.l_orderkey	1	
-SELECT
-l_extendedprice, o_orderkey, o_totalprice, l_shipDATE
-FROM
-orders, lineitem
-WHERE
-o_orderkey = l_orderkey AND
-l_shipDATE >= '1996-01-01' AND l_shipDATE <= '1996-12-31'
-            ORDER BY l_extendedprice DESC
-LIMIT 10;
-l_extendedprice	o_orderkey	o_totalprice	l_shipDATE
-54559.5	1574	179923.54	1996-12-14
-53508.5	2342	104038.78	1996-08-28
-53458	1153	220727.97	1996-06-27
-53075.82	2721	59180.25	1996-02-14
-52830.33	1284	106122.38	1996-04-11
-52657.5	2276	141159.63	1996-07-13
-52290.84	4773	196080.26	1996-01-26
-51752.16	1607	166335.03	1996-02-22
-51751.35	1700	89143.36	1996-09-26
-51709.4	1254	94649.25	1996-03-07
-set use_sort_nest=0;
 explain SELECT
 l_extendedprice, o_orderkey, o_totalprice, l_shipDATE
 FROM
@@ -301,7 +160,6 @@ l_extendedprice	o_orderkey	o_totalprice	l_shipDATE
 #
 #  FILESORT USING SORT-NEST on (orders, customer)
 #
-set use_sort_nest=1;
 explain SELECT
 c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal, n_name
 FROM
@@ -336,98 +194,9 @@ c_nationkey	c_name	o_orderDate	o_totalprice	c_acctbal	n_name
 11	Customer#000000148	1997-07-25	206179.68	2135.6	IRAQ
 18	Customer#000000082	1997-07-20	15082.82	9468.34	CHINA
 10	Customer#000000055	1997-07-16	46380.69	4572.11	IRAN
-set use_sort_nest=0;
-explain SELECT
-c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal, n_name
-FROM
-orders, customer, nation 
-WHERE
-c_custkey= o_custkey AND c_nationkey=n_nationkey AND
-o_orderDATE >='1997-07-01' and o_orderDATE <= '1997-07-31' 
-ORDER BY o_orderDATE DESC, c_acctbal DESC
-LIMIT 10;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	orders	range	i_o_orderdate,i_o_custkey	i_o_orderdate	4	NULL	20	Using index condition; Using where; Using temporary; Using filesort
-1	SIMPLE	customer	eq_ref	PRIMARY,i_c_nationkey	PRIMARY	4	dbt3.orders.o_custkey	1	Using where
-1	SIMPLE	nation	eq_ref	PRIMARY	PRIMARY	4	dbt3.customer.c_nationkey	1	
-SELECT
-c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal, n_name
-FROM
-orders, customer, nation 
-WHERE
-c_custkey= o_custkey AND c_nationkey=n_nationkey AND
-o_orderDATE >='1997-07-01' and o_orderDATE <= '1997-07-31' 
-ORDER BY o_orderDATE DESC, c_acctbal DESC
-LIMIT 10;
-c_nationkey	c_name	o_orderDate	o_totalprice	c_acctbal	n_name
-2	Customer#000000101	1997-07-31	79189.58	7470.96	BRAZIL
-3	Customer#000000013	1997-07-31	125188.72	3857.34	CANADA
-9	Customer#000000130	1997-07-30	67979.49	5073.58	INDONESIA
-11	Customer#000000148	1997-07-29	88448.24	2135.6	IRAQ
-2	Customer#000000101	1997-07-28	204163.1	7470.96	BRAZIL
-11	Customer#000000052	1997-07-28	46393.97	5630.28	IRAQ
-4	Customer#000000004	1997-07-25	11405.4	2866.83	EGYPT
-11	Customer#000000148	1997-07-25	206179.68	2135.6	IRAQ
-18	Customer#000000082	1997-07-20	15082.82	9468.34	CHINA
-10	Customer#000000055	1997-07-16	46380.69	4572.11	IRAN
 #
-# Sort-nest on table (lineitem, customer)
+# Sort-nest on table (lineitem, orders)
 #
-set use_sort_nest=1;
-explain SELECT
-c_acctbal, c_name, o_orderDate, l_extendedprice, l_orderkey
-FROM
-orders, customer, lineitem
-WHERE
-c_custkey= o_custkey
-AND
-l_orderkey = o_orderkey
-AND
-l_orderkey between 5500 AND 6000
-AND
-l_shipDATE >= '1997-01-01' and l_shipDATE <=  '1998-08-10'
-            ORDER BY l_extendedprice DESC, c_acctbal DESC
-LIMIT 20;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	orders	range	PRIMARY,i_o_custkey	PRIMARY	4	NULL	125	Using where; Using temporary; Using filesort
-1	SIMPLE	lineitem	ref	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	dbt3.orders.o_orderkey	4	Using where
-1	SIMPLE	customer	eq_ref	PRIMARY	PRIMARY	4	dbt3.orders.o_custkey	1	
-SELECT
-c_acctbal, c_name, o_orderDate, l_extendedprice, l_orderkey
-FROM
-orders, customer, lineitem
-WHERE
-c_custkey= o_custkey
-AND
-l_orderkey = o_orderkey
-AND
-l_orderkey between 5500 AND 6000
-AND
-l_shipDATE >= '1997-01-01' and l_shipDATE <=  '1998-08-10'
-            ORDER BY l_extendedprice DESC, c_acctbal DESC
-LIMIT 20;
-c_acctbal	c_name	o_orderDate	l_extendedprice	l_orderkey
-1842.49	Customer#000000124	1997-11-06	54759.5	5857
-2135.6	Customer#000000148	1997-04-14	53909.8	5952
-4681.03	Customer#000000016	1998-07-06	53811.31	5761
-794.47	Customer#000000005	1997-04-23	53758.5	5859
--234.12	Customer#000000125	1997-01-11	53468.31	5829
-5121.28	Customer#000000079	1998-05-31	53208	5633
-5744.59	Customer#000000046	1998-04-14	50770.37	5604
--917.75	Customer#000000037	1997-07-13	50310.72	5793
-121.65	Customer#000000002	1998-05-28	49830.24	5507
-121.65	Customer#000000002	1998-05-28	49542.24	5507
-7470.96	Customer#000000101	1997-05-27	49411.82	5923
-5327.38	Customer#000000095	1997-10-04	48859.36	5505
-1842.49	Customer#000000124	1997-11-06	48661.41	5857
-4573.94	Customer#000000049	1997-02-14	48557.11	5762
--646.64	Customer#000000064	1997-01-01	48219.92	5895
--646.64	Customer#000000064	1997-01-01	48039.64	5895
-5121.28	Customer#000000079	1998-05-31	48004.8	5633
-9904.28	Customer#000000043	1998-02-06	47339.52	5671
-8959.65	Customer#000000149	1996-11-12	47247.52	5606
-5744.59	Customer#000000046	1998-04-14	45589.72	5604
-set use_sort_nest=0;
 explain SELECT
 c_acctbal, c_name, o_orderDate, l_extendedprice, l_orderkey
 FROM
@@ -440,12 +209,13 @@ AND
 l_orderkey between 5500 AND 6000
 AND
 l_shipDATE >= '1997-01-01' and l_shipDATE <=  '1998-08-10'
-            ORDER BY l_extendedprice DESC, c_acctbal DESC
+            ORDER BY l_extendedprice DESC, o_orderDATE DESC
 LIMIT 20;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	orders	range	PRIMARY,i_o_custkey	PRIMARY	4	NULL	125	Using where; Using temporary; Using filesort
-1	SIMPLE	customer	eq_ref	PRIMARY	PRIMARY	4	dbt3.orders.o_custkey	1	
+1	SIMPLE	orders	range	PRIMARY,i_o_custkey	PRIMARY	4	NULL	125	Using where
 1	SIMPLE	lineitem	ref	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	dbt3.orders.o_orderkey	4	Using where
+1	SIMPLE	<sort-nest>	ALL	NULL	NULL	NULL	NULL	20	Using filesort
+1	SIMPLE	customer	eq_ref	PRIMARY	PRIMARY	4	sort-nest.o_custkey	1	
 SELECT
 c_acctbal, c_name, o_orderDate, l_extendedprice, l_orderkey
 FROM
@@ -458,7 +228,7 @@ AND
 l_orderkey between 5500 AND 6000
 AND
 l_shipDATE >= '1997-01-01' and l_shipDATE <=  '1998-08-10'
-            ORDER BY l_extendedprice DESC, c_acctbal DESC
+            ORDER BY l_extendedprice DESC, o_orderDATE DESC
 LIMIT 20;
 c_acctbal	c_name	o_orderDate	l_extendedprice	l_orderkey
 1842.49	Customer#000000124	1997-11-06	54759.5	5857
@@ -481,11 +251,9 @@ c_acctbal	c_name	o_orderDate	l_extendedprice	l_orderkey
 9904.28	Customer#000000043	1998-02-06	47339.52	5671
 8959.65	Customer#000000149	1996-11-12	47247.52	5606
 5744.59	Customer#000000046	1998-04-14	45589.72	5604
-########################################################################
 #
 # Sort-nest on table (customer, orders, lineitem)
 #
-set use_sort_nest=1;
 explain SELECT
 l_extendedprice, c_acctbal, o_totalprice, o_orderDate, l_orderkey
 FROM
@@ -547,65 +315,4 @@ l_extendedprice	c_acctbal	o_totalprice	o_orderDate	l_orderkey
 28948.59	6819.74	182966.39	1994-07-17	5572
 26732.43	9561.95	101429.61	1993-04-21	5670
 24590.68	9561.95	95312.81	1992-03-28	5953
-set use_sort_nest=0;
-explain SELECT
-l_extendedprice, c_acctbal, o_totalprice, o_orderDate, l_orderkey
-FROM
-orders, customer, lineitem, nation
-WHERE
-c_custkey= o_custkey
-AND
-l_orderkey = o_orderkey
-AND
-n_nationkey = c_nationkey
-AND
-l_orderkey between 5500 AND 6000
-AND
-c_custkey between 1 and 10
-ORDER BY
-l_extendedprice DESC, c_acctbal DESC, o_totalprice DESC
-LIMIT 20;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	orders	range	PRIMARY,i_o_custkey	PRIMARY	4	NULL	125	Using where; Using temporary; Using filesort
-1	SIMPLE	customer	eq_ref	PRIMARY,i_c_nationkey	PRIMARY	4	dbt3.orders.o_custkey	1	Using where
-1	SIMPLE	nation	eq_ref	PRIMARY	PRIMARY	4	dbt3.customer.c_nationkey	1	Using index
-1	SIMPLE	lineitem	ref	PRIMARY,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	dbt3.orders.o_orderkey	4	
-SELECT
-l_extendedprice, c_acctbal, o_totalprice, o_orderDate, l_orderkey
-FROM
-orders, customer, lineitem, nation
-WHERE
-c_custkey= o_custkey
-AND
-l_orderkey = o_orderkey
-AND
-n_nationkey = c_nationkey
-AND
-l_orderkey between 5500 AND 6000
-AND
-c_custkey between 1 and 10
-ORDER BY
-l_extendedprice DESC, c_acctbal DESC, o_totalprice DESC
-LIMIT 20;
-l_extendedprice	c_acctbal	o_totalprice	o_orderDate	l_orderkey
-53758.5	794.47	210643.96	1997-04-23	5859
-49830.24	121.65	140363.7	1998-05-28	5507
-49542.24	121.65	140363.7	1998-05-28	5507
-48745.11	6819.74	122823.78	1993-04-05	5794
-47992.64	6819.74	140838.11	1998-06-26	5763
-47615.98	6819.74	182966.39	1994-07-17	5572
-46705.74	9561.95	101429.61	1993-04-21	5670
-44467.59	121.65	44777.63	1992-07-08	5893
-44442.3	6819.74	122823.78	1993-04-05	5794
-39723.6	794.47	210643.96	1997-04-23	5859
-37048.32	9561.95	95312.81	1992-03-28	5953
-36860.25	794.47	210643.96	1997-04-23	5859
-32996.16	6819.74	140838.11	1998-06-26	5763
-31416.68	6819.74	182966.39	1994-07-17	5572
-31219.32	794.47	210643.96	1997-04-23	5859
-31042.34	9561.95	95312.81	1992-03-28	5953
-29462.13	794.47	210643.96	1997-04-23	5859
-28948.59	6819.74	182966.39	1994-07-17	5572
-26732.43	9561.95	101429.61	1993-04-21	5670
-24590.68	9561.95	95312.81	1992-03-28	5953
 drop database dbt3;
diff --git a/sql/sql_priv.h b/sql/sql_priv.h
index 7aca1d2b699..de649d79c1b 100644
--- a/sql/sql_priv.h
+++ b/sql/sql_priv.h
@@ -230,6 +230,7 @@
 #define OPTIMIZER_SWITCH_USE_ROWID_FILTER          (1ULL << 33)
 #define OPTIMIZER_SWITCH_COND_PUSHDOWN_FROM_HAVING (1ULL << 34)
 #define OPTIMIZER_SWITCH_NOT_NULL_RANGE_SCAN       (1ULL << 35)
+#define OPTIMIZER_SWITCH_COST_BASED_ORDER_BY_LIMIT (1ULL << 36)
 
 #define OPTIMIZER_SWITCH_DEFAULT   (OPTIMIZER_SWITCH_INDEX_MERGE | \
                                     OPTIMIZER_SWITCH_INDEX_MERGE_UNION | \
diff --git a/sql/sql_sort_nest.cc b/sql/sql_sort_nest.cc
index 878aca3eb7c..de0f6ea0f52 100644
--- a/sql/sql_sort_nest.cc
+++ b/sql/sql_sort_nest.cc
@@ -1407,7 +1407,8 @@ void JOIN::set_fraction_output_for_nest()
 
 bool JOIN::sort_nest_allowed()
 {
-  return thd->variables.use_sort_nest && order &&
+  return optimizer_flag(thd, OPTIMIZER_SWITCH_COST_BASED_ORDER_BY_LIMIT) &&
+         order &&
          !(const_tables == table_count ||
            (select_distinct || group_list) ||
            having  ||
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index 173e38dc63b..293f802e632 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -2587,6 +2587,7 @@ export const char *optimizer_switch_names[]=
   "rowid_filter",
   "condition_pushdown_from_having",
   "not_null_range_scan",
+  "cost_based_order_by_limit",
   "default", 
   NullS
 };
@@ -6409,9 +6410,3 @@ static Sys_var_ulonglong Sys_max_rowid_filter_size(
        SESSION_VAR(max_rowid_filter_size), CMD_LINE(REQUIRED_ARG),
        VALID_RANGE(1024, (ulonglong)~(intptr)0), DEFAULT(128*1024),
        BLOCK_SIZE(1));
-
-static Sys_var_mybool Sys_use_sort_nest(
-       "use_sort_nest",
-       "Enable the sort nest",
-       SESSION_VAR(use_sort_nest), CMD_LINE(OPT_ARG),
-       DEFAULT(FALSE));