MDEV-8646: Re-engineer the code for post-join operations

- Make EXPLAIN code use the post-join operations
- Remove Sort_and_group_tracker that was used for that purpose

9 files changed, 56 insertions, 61 deletions

diff --git a/mysql-test/r/analyze_format_json.result b/mysql-test/r/analyze_format_json.result
index 2e106371c63..a66a3895008 100644
--- a/mysql-test/r/analyze_format_json.result
+++ b/mysql-test/r/analyze_format_json.result
@@ -685,13 +685,13 @@ ANALYZE
       "r_used_priority_queue": false,
       "r_output_rows": 0,
       "volatile parameter": "REPLACED",
-      "filesort": {
-        "r_loops": 1,
-        "volatile parameter": "REPLACED",
-        "r_used_priority_queue": false,
-        "r_output_rows": 0,
-        "volatile parameter": "REPLACED",
-        "temporary_table": {
+      "temporary_table": {
+        "filesort": {
+          "r_loops": 1,
+          "volatile parameter": "REPLACED",
+          "r_used_priority_queue": false,
+          "r_output_rows": 0,
+          "volatile parameter": "REPLACED",
           "temporary_table": {
             "table": {
               "table_name": "t2",
diff --git a/mysql-test/r/analyze_stmt_orderby.result b/mysql-test/r/analyze_stmt_orderby.result
index be1f01a2a52..244da2f596e 100644
--- a/mysql-test/r/analyze_stmt_orderby.result
+++ b/mysql-test/r/analyze_stmt_orderby.result
@@ -173,7 +173,6 @@ EXPLAIN
     "select_id": 1,
     "filesort": {
       "temporary_table": {
-        "function": "buffer",
         "table": {
           "table_name": "t0",
           "access_type": "ALL",
@@ -460,13 +459,13 @@ ANALYZE
       "r_limit": 1,
       "r_used_priority_queue": true,
       "r_output_rows": 2,
-      "filesort": {
-        "r_loops": 1,
-        "r_total_time_ms": "REPLACED",
-        "r_used_priority_queue": false,
-        "r_output_rows": 6,
-        "r_buffer_size": "REPLACED",
-        "temporary_table": {
+      "temporary_table": {
+        "filesort": {
+          "r_loops": 1,
+          "r_total_time_ms": "REPLACED",
+          "r_used_priority_queue": false,
+          "r_output_rows": 6,
+          "r_buffer_size": "REPLACED",
           "temporary_table": {
             "table": {
               "table_name": "t6",
@@ -512,25 +511,28 @@ EXPLAIN
     "select_id": 1,
     "filesort": {
       "temporary_table": {
-        "function": "buffer",
-        "table": {
-          "table_name": "t6",
-          "access_type": "ALL",
-          "rows": 5,
-          "filtered": 100,
-          "attached_condition": "((t6.b > 0) and (t6.a <= 5))"
-        },
-        "block-nl-join": {
-          "table": {
-            "table_name": "t5",
-            "access_type": "ALL",
-            "rows": 7,
-            "filtered": 100
-          },
-          "buffer_type": "flat",
-          "buffer_size": "256Kb",
-          "join_type": "BNL",
-          "attached_condition": "(t5.a = t6.a)"
+        "filesort": {
+          "temporary_table": {
+            "table": {
+              "table_name": "t6",
+              "access_type": "ALL",
+              "rows": 5,
+              "filtered": 100,
+              "attached_condition": "((t6.b > 0) and (t6.a <= 5))"
+            },
+            "block-nl-join": {
+              "table": {
+                "table_name": "t5",
+                "access_type": "ALL",
+                "rows": 7,
+                "filtered": 100
+              },
+              "buffer_type": "flat",
+              "buffer_size": "256Kb",
+              "join_type": "BNL",
+              "attached_condition": "(t5.a = t6.a)"
+            }
+          }
         }
       }
     }
diff --git a/mysql-test/r/derived_opt.result b/mysql-test/r/derived_opt.result
index 04a76c2cbc8..6e4ea1b5d36 100644
--- a/mysql-test/r/derived_opt.result
+++ b/mysql-test/r/derived_opt.result
@@ -231,8 +231,8 @@ CREATE VIEW v1 AS SELECT a, MIN(b) AS b FROM t2 GROUP BY a;
 EXPLAIN
 SELECT * FROM v1, t1 WHERE v1.b=t1.a  ORDER BY v1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	Using filesort
-1	PRIMARY	<derived2>	ref	key0	key0	5	const	1	Using where
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	<derived2>	ref	key0	key0	5	const	1	Using where; Using filesort
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	10	Using temporary; Using filesort
 SELECT * FROM v1, t1 WHERE v1.b=t1.a  ORDER BY v1.a;
 a	b	a
diff --git a/mysql-test/r/explain_json.result b/mysql-test/r/explain_json.result
index a42f5af114c..975d0fddf38 100644
--- a/mysql-test/r/explain_json.result
+++ b/mysql-test/r/explain_json.result
@@ -487,7 +487,6 @@ EXPLAIN
           "select_id": 2,
           "filesort": {
             "temporary_table": {
-              "function": "buffer",
               "table": {
                 "table_name": "t1",
                 "access_type": "ALL",
@@ -531,7 +530,6 @@ EXPLAIN
           "select_id": 2,
           "filesort": {
             "temporary_table": {
-              "function": "buffer",
               "table": {
                 "table_name": "t1",
                 "access_type": "ALL",
@@ -576,7 +574,6 @@ EXPLAIN
         "query_block": {
           "select_id": 2,
           "temporary_table": {
-            "function": "buffer",
             "table": {
               "table_name": "t1",
               "access_type": "ALL",
@@ -1133,7 +1130,6 @@ EXPLAIN
     "having_condition": "(TOP > t2.a)",
     "filesort": {
       "temporary_table": {
-        "function": "buffer",
         "table": {
           "table_name": "t2",
           "access_type": "ALL",
@@ -1152,7 +1148,6 @@ EXPLAIN
     "select_id": 1,
     "filesort": {
       "temporary_table": {
-        "function": "buffer",
         "table": {
           "table_name": "t2",
           "access_type": "ALL",
@@ -1182,7 +1177,6 @@ EXPLAIN
     "select_id": 1,
     "filesort": {
       "temporary_table": {
-        "function": "buffer",
         "table": {
           "table_name": "t2",
           "access_type": "ALL",
@@ -1380,7 +1374,6 @@ EXPLAIN
   "query_block": {
     "select_id": 1,
     "temporary_table": {
-      "function": "buffer",
       "table": {
         "table_name": "t1",
         "access_type": "ALL",
diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
index d816b1acf92..c15f96fc2e5 100644
--- a/mysql-test/r/join_cache.result
+++ b/mysql-test/r/join_cache.result
@@ -5412,9 +5412,9 @@ WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND
 t2.a BETWEEN 4 and 5
 ORDER BY t2.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	Using filesort
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t2	range	a,c	a	5	NULL	1	Using index condition; Using where
+1	PRIMARY	t2	range	a,c	a	5	NULL	1	Using index condition; Using where; Using filesort
 1	PRIMARY	t4	ref	c	c	5	test.t2.c	2	Using where; Start temporary; End temporary
 SELECT * FROM t1,t2
 WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 820d66b9264..ca544f40aa8 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -1289,8 +1289,8 @@ SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
 FROM t1 JOIN t2 USING( a )
 GROUP BY t1.a WITH ROLLUP;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	Using filesort
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	Using where; Using filesort
 SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
 FROM t1 JOIN t2 USING( a )
 GROUP BY t1.a WITH ROLLUP;
@@ -1429,8 +1429,8 @@ EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1
 WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL
 GROUP BY t2.f1, t2.f2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	Using filesort
-1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	const	1	Using where; Using index
+1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	const	1	Using where; Using index; Using filesort
 SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1
 WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL
 GROUP BY t2.f1, t2.f2;
@@ -1846,8 +1846,8 @@ EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1
 WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL
 GROUP BY t2.f1, t2.f2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	Using filesort
-1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	const	1	Using where; Using index
+1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	const	1	Using where; Using index; Using filesort
 SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1
 WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL
 GROUP BY t2.f1, t2.f2;
diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result
index 4412f8059dd..3616deaee9c 100644
--- a/mysql-test/r/join_outer_jcl6.result
+++ b/mysql-test/r/join_outer_jcl6.result
@@ -1300,8 +1300,8 @@ SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
 FROM t1 JOIN t2 USING( a )
 GROUP BY t1.a WITH ROLLUP;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	Using filesort
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	Using where; Using filesort
 SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
 FROM t1 JOIN t2 USING( a )
 GROUP BY t1.a WITH ROLLUP;
@@ -1440,8 +1440,8 @@ EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1
 WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL
 GROUP BY t2.f1, t2.f2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	Using filesort
-1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	const	1	Using where; Using index
+1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	const	1	Using where; Using index; Using filesort
 SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1
 WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL
 GROUP BY t2.f1, t2.f2;
@@ -1857,8 +1857,8 @@ EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1
 WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL
 GROUP BY t2.f1, t2.f2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	Using filesort
-1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	const	1	Using where; Using index
+1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	const	1	Using where; Using index; Using filesort
 SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1
 WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL
 GROUP BY t2.f1, t2.f2;
diff --git a/mysql-test/r/mrr_derived_crash_4610.result b/mysql-test/r/mrr_derived_crash_4610.result
index 8dcdfda9276..3e38a0d4218 100644
--- a/mysql-test/r/mrr_derived_crash_4610.result
+++ b/mysql-test/r/mrr_derived_crash_4610.result
@@ -7,8 +7,8 @@ explain select 1 from
 (select f2, f3, val, count(id) from t4 join t2 left join t3 on 0) top
 join t1 on f1 = f3 where f3 = 'aaaa' order by val;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	const	PRIMARY	PRIMARY	12	const	1	Using index; Using filesort
-1	PRIMARY	<derived2>	ref	key0	key0	13	const	0	Using where
+1	PRIMARY	t1	const	PRIMARY	PRIMARY	12	const	1	Using index
+1	PRIMARY	<derived2>	ref	key0	key0	13	const	0	Using where; Using filesort
 2	DERIVED	t4	ALL	NULL	NULL	NULL	NULL	1	
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	1	Using join buffer (flat, BNL join)
 2	DERIVED	t3	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index 4cd814468a4..7202a6238b0 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -2003,8 +2003,8 @@ FROM t2 JOIN t3 ON t3.f4 = t2.f4
 WHERE t3.f1 = 8
 GROUP BY 1, 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	Using filesort
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using filesort
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 3	MATERIALIZED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 PREPARE st1 FROM "