Bug #121291 EXPLAIN FORMAT=JSON v2 omits access_type for LooseScan/FirstMatch nodes

• Submitted: 16 Sep 7:46
• Reporter: QiFan Liu
• Status: Open
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: MySQL 26.7.0; MySQL 9.7.2
• OS: Any
• CPU Architecture: Any

[16 Sep 7:46] QiFan Liu

Description:
EXPLAIN FORMAT=JSON with explain_json_format_version=2 omits the access_type field from the access-path object representing a combined LooseScan/FirstMatch semijoin. The same object is reached and identifies itself through semijoin_strategy=firstmatch_with_loosescan and an operation containing LooseScan, so it is an explainable access-path node rather than descriptive text outside the plan structure. This violates the JSON v2 metadata contract that explainable access-path objects expose access_type. Consumers cannot classify this node consistently with adjacent access-path objects and must special-case descriptive fields.
Reproduced consistently on MySQL 26.7.0 and MySQL 9.7.2.

Actual result:
reached_target_node	operation_identifies_node	target_has_access_type
1	1	0

Expected result:
The target node should include an access_type field, producing 1, 1, 1. Its semijoin_strategy and LooseScan operation already establish that it is the relevant access-path object, so descriptive metadata is not a substitute for access_type.

How to repeat:
CREATE DATABASE explain_access_type_test;
USE explain_access_type_test;

SET explain_json_format_version = 2;
SET optimizer_switch = 'firstmatch=off,duplicateweedout=off,materialization=off';

CREATE TABLE loose_scan_test (
  a INTEGER,
  b INTEGER,
  KEY ab (a, b),
  KEY b (b)
);

INSERT INTO loose_scan_test VALUES (1, 2), (2, 3);
ANALYZE TABLE loose_scan_test;

EXPLAIN FORMAT=JSON INTO @plan
SELECT b
FROM loose_scan_test
WHERE a IN (
  SELECT t1.b
  FROM loose_scan_test AS t1
  JOIN loose_scan_test AS t2 ON t1.b
);

SET @strategy_path = JSON_UNQUOTE(
  JSON_SEARCH(@plan, 'one', 'firstmatch_with_loosescan', NULL,
              '$**.semijoin_strategy')
);
SET @node_path = LEFT(
  @strategy_path,
  LENGTH(@strategy_path) - LENGTH('.semijoin_strategy')
);

SELECT @strategy_path IS NOT NULL AS reached_target_node,
       JSON_UNQUOTE(JSON_EXTRACT(
         @plan, CONCAT(@node_path, '.operation')
       )) LIKE '%LooseScan%' AS operation_identifies_node,
       JSON_CONTAINS_PATH(
         @plan, 'one', CONCAT(@node_path, '.access_type')
       ) AS target_has_access_type;