Remotely targeted JOIN_FIXED_ORDER does not preserve the target query block's join order

MariaDB 12.3.3 applies JOIN_FIXED_ORDER differently when an outer query block targets a materialized derived query block by name. The local hint preserves the derived block's source order b, s, but the remotely targeted equivalent produces s, b.

This occurs when JOIN_FIXED_ORDER in an outer query block targets a materialized derived query block by name.

Setup and reproduction

Run this SQL in one session:

DROP DATABASE IF EXISTS hint_test;

CREATE DATABASE hint_test;

USE hint_test;

CREATE TABLE big_rows (id INT PRIMARY KEY);

CREATE TABLE small_rows (id INT PRIMARY KEY);

INSERT INTO big_rows SELECT seq FROM seq_1_to_1000;

INSERT INTO small_rows VALUES (1);

ANALYZE TABLE big_rows, small_rows;

SELECT 'LOCAL_CONTROL' AS marker;

EXPLAIN FORMAT=JSON

SELECT COUNT(*)

FROM

(

  SELECT /*+ JOIN_FIXED_ORDER() */ b.id AS b_id, s.id AS s_id

  FROM big_rows AS b

  JOIN small_rows AS s

  LIMIT 1000000

) AS dt;

SELECT 'REMOTE_TARGET' AS marker;

EXPLAIN FORMAT=JSON

SELECT /*+ JOIN_FIXED_ORDER(@dt) */ COUNT(*)

FROM

(

  SELECT b.id AS b_id, s.id AS s_id

  FROM big_rows AS b

  JOIN small_rows AS s

  LIMIT 1000000

) AS dt;

DROP DATABASE hint_test;

Decisive observation

The local control's materialized query block contains this inner order:

"nested_loop": [

  { "table": { "table_name": "b", ... } },

  { "block-nl-join": { "table": { "table_name": "s", ... } } }

]

The remotely targeted form instead contains:

"nested_loop": [

  { "table": { "table_name": "s", ... } },

  { "block-nl-join": { "table": { "table_name": "b", ... } } }

]

Both statements complete successfully and the server accepts both hints.

Semantic mismatch

The two hints express the same fixed-order requirement for the derived query block: the local form is written inside that block, while the outer form explicitly targets it as @dt. Both should therefore preserve its source order b, s. Instead, only the local form does so; the remote form allows the target block to be reordered as s, b.