EXPLAIN FORMAT=JSON: Full scan on NULL key (join case)

2 files changed, 73 insertions, 0 deletions

diff --git a/mysql-test/r/explain_json.result b/mysql-test/r/explain_json.result
index c9b65282047..0824132de05 100644
--- a/mysql-test/r/explain_json.result
+++ b/mysql-test/r/explain_json.result
@@ -719,3 +719,62 @@ EXPLAIN
 }
 drop table t1;
 drop table t0;
+#
+# MDEV-7265: "Full scan on NULL key", the join case
+#
+CREATE TABLE t1 (a INT, KEY(a));
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (3),(4);
+EXPLAIN FORMAT=JSON SELECT * FROM t1 AS outer_t1 WHERE a <> ALL ( SELECT a FROM t1, t2 WHERE b <> outer_t1.a );
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "outer_t1",
+      "access_type": "index",
+      "key": "a",
+      "key_length": "5",
+      "used_key_parts": ["a"],
+      "rows": 2,
+      "filtered": 100,
+      "attached_condition": "(not(<in_optimizer>(outer_t1.a,<exists>(subquery#2))))",
+      "using_index": true
+    },
+    "subqueries": [
+      {
+        "query_block": {
+          "select_id": 2,
+          "full-scan-on-null_key": {
+            "table": {
+              "table_name": "t1",
+              "access_type": "ref_or_null",
+              "possible_keys": ["a"],
+              "key": "a",
+              "key_length": "5",
+              "used_key_parts": ["a"],
+              "ref": ["func"],
+              "rows": 2,
+              "filtered": 100,
+              "attached_condition": "trigcond(((<cache>(outer_t1.a) = t1.a) or isnull(t1.a)))",
+              "using_index": true
+            }
+          },
+          "block-nl-join": {
+            "table": {
+              "table_name": "t2",
+              "access_type": "ALL",
+              "rows": 2,
+              "filtered": 100
+            },
+            "buffer_type": "flat",
+            "join_type": "BNL",
+            "attached_condition": "((t2.b <> outer_t1.a) and trigcond(((<cache>(outer_t1.a) = t1.a) or isnull(t1.a))))"
+          }
+        }
+      }
+    ]
+  }
+}
+DROP TABLE t1,t2;
diff --git a/mysql-test/t/explain_json.test b/mysql-test/t/explain_json.test
index 0ecc09691e0..476cb0d3df6 100644
--- a/mysql-test/t/explain_json.test
+++ b/mysql-test/t/explain_json.test
@@ -153,3 +153,17 @@ select * from t1 tbl1, t1 tbl2 where tbl2.a < tbl1.b;
 drop table t1;
 drop table t0;
 
+--echo #
+--echo # MDEV-7265: "Full scan on NULL key", the join case
+--echo #
+
+CREATE TABLE t1 (a INT, KEY(a));
+INSERT INTO t1 VALUES (1),(2);
+
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (3),(4);
+
+EXPLAIN FORMAT=JSON SELECT * FROM t1 AS outer_t1 WHERE a <> ALL ( SELECT a FROM t1, t2 WHERE b <> outer_t1.a );
+
+DROP TABLE t1,t2;
+