More testcases.

1 files changed, 105 insertions, 1 deletions

diff --git a/mysql-test/r/analyze_stmt_orderby.result b/mysql-test/r/analyze_stmt_orderby.result
index 440a55060d4..0f5c7c8437b 100644
--- a/mysql-test/r/analyze_stmt_orderby.result
+++ b/mysql-test/r/analyze_stmt_orderby.result
@@ -381,7 +381,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_f
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	1	0.00	100.00	100.00	Using filesort
 drop table t3;
 #
-#
+# A test for duplicate_removal()
 #
 create table t3 (a int, b int);
 insert into t3 select a, 123 from t0;
@@ -426,5 +426,109 @@ ANALYZE
     }
   }
 }
+#
+# A query with two filesort calls:
+#   - first is needed to do group-by-group grouping to calculate COUNT(DISTINCT)
+#   - the second is need to produce ORDER BY.
+# (see MDEV-7836 for description of the query plan)
+create table t5 (a int , b int) ;
+create table t6 like t5 ;
+create table t7 like t5 ;
+insert into t5 values (0, 100), (1, 2), (1, 3), (2, 2), (2, 7),
+(2, -1), (3, 10);
+insert into t6 values (0, 0), (1, 1), (2, 1), (3, 1), (4, 1);
+insert into t7 values (3, 3), (2, 2), (1, 1);
+# TODO: This ANALYZE output doesn't make it clear what is used for what.
+analyze format=json
+select count(distinct t5.b) as sum from t5, t6
+where t5.a=t6.a and t6.b > 0 and t5.a <= 5
+group by t5.a order by sum limit 1;
+ANALYZE
+{
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "filesort": {
+      "r_loops": 1,
+      "r_limit": 1,
+      "r_used_priority_queue": true,
+      "r_output_rows": 2,
+      "filesort": {
+        "r_loops": 1,
+        "r_used_priority_queue": false,
+        "r_output_rows": 6,
+        "r_buffer_size": "REPLACED",
+        "temporary_table": {
+          "temporary_table": {
+            "table": {
+              "table_name": "t6",
+              "access_type": "ALL",
+              "r_loops": 1,
+              "rows": 5,
+              "r_rows": 5,
+              "r_total_time_ms": "REPLACED",
+              "filtered": 100,
+              "r_filtered": 80,
+              "attached_condition": "((t6.b > 0) and (t6.a <= 5))"
+            },
+            "block-nl-join": {
+              "table": {
+                "table_name": "t5",
+                "access_type": "ALL",
+                "r_loops": 1,
+                "rows": 7,
+                "r_rows": 7,
+                "r_total_time_ms": "REPLACED",
+                "filtered": 100,
+                "r_filtered": 100
+              },
+              "buffer_type": "flat",
+              "buffer_size": "128Kb",
+              "join_type": "BNL",
+              "attached_condition": "(t5.a = t6.a)",
+              "r_filtered": 21.429
+            }
+          }
+        }
+      }
+    }
+  }
+}
+explain format=json
+select count(distinct t5.b) as sum from t5, t6
+where t5.a=t6.a and t6.b > 0 and t5.a <= 5
+group by t5.a order by sum limit 1;
+EXPLAIN
+{
+  "query_block": {
+    "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": "128Kb",
+          "join_type": "BNL",
+          "attached_condition": "(t5.a = t6.a)"
+        }
+      }
+    }
+  }
+}
+drop table t5,t6,t7;
 drop table t3;
 drop table t0,t1;