Join order chosen based on range access that is lost after substitute_best_equal -> full partition scan with BNL join (regression from 10.6)

Environment

MariaDB 11.8.9-MariaDB-deb12 (upgraded from 10.6; the problem never happened on 10.6).
Tables: ENGINE=Aria, ROW_FORMAT=PAGE. The big table is PARTITION BY LIST COLUMNS(type).
Optimizer settings are defaults: optimizer_use_condition_selectivity=4, join_cache_level=2, optimizer_where_cost=0.032

Schema (simplified, names anonymized)

CREATE TABLE t_item (

  id BIGINT UNSIGNED NOT NULL PRIMARY KEY

) ENGINE=Aria;

CREATE TABLE t_event (

  id      INT UNSIGNED NOT NULL AUTO_INCREMENT,

  type    VARCHAR(25)  NOT NULL,

  item_id INT UNSIGNED DEFAULT NULL,

  d       DATE DEFAULT NULL,

  ref_d   DATE DEFAULT NULL,

  PRIMARY KEY (id, type),

  KEY k_item_d (item_id, d),

  KEY k_ref_d (ref_d),

  KEY k_d (d),

  KEY k_type_item_d (type, item_id, d)

) ENGINE=Aria ROW_FORMAT=PAGE

PARTITION BY LIST COLUMNS(type) (

  PARTITION pA VALUES IN ('A'),

  PARTITION pB VALUES IN ('B'),

  PARTITION pC VALUES IN ('C')

);

The real table has more columns and indexes. Partition sizes in production: pA ~4.2M rows, pB ~40M rows (~53M rows total).

Query

SELECT p.id

FROM t_item a

LEFT JOIN t_event p ON p.type = 'A' AND p.item_id = a.id

JOIN t_event c ON c.type = 'B' AND c.item_id = p.item_id

              AND c.ref_d = p.d AND c.d > p.d

WHERE p.type = 'A' AND a.id = 1001

ORDER BY p.d DESC

LIMIT 1;

In production the query runs inside a stored procedure (a.id = NAME_CONST('P_ID', ...)).

Expected

Join order a -> p -> c: p accessed via ref/range with item_id = const (~2 rows), then c via index.
That is what 10.6 always did, and what 11.8 does with STRAIGHT_JOIN.

Actual

id | table | type  | key           | ref         | rows    | Extra

1  | a     | const | PRIMARY       | const       | 1       | Using index; Using temporary; Using filesort

1  | c     | ref   | k_type_item_d | const,const | 60      | Using index condition; Using where

1  | p     | ALL   | NULL          | NULL        | 4168116 | Using where; Using join buffer (flat, BNL join)

The whole pA partition is scanned for a query that should read ~2 rows.

Analysis from optimizer_trace

1. Both join orders are considered and are close in cost:

   [a, p, c] cost_for_plan = 1.1667

   [a, c, p] cost_for_plan = 0.6123 + cost_for_sorting 0.4309 = 1.0431 <- chosen

2. For [a, c, p] the chosen access for p relies on the constant item_id ("scan" for p was rejected on cost, table_scan cost = 645.45):

   "best_access_path": { "table": "p",

   "chosen_access_method": { "type": "range", "index": "k_item_d",

   "rows_read": 2, "rows_out": 1, "cost": 0.017615138,

   "uses_join_buffering": true } }

3. During condition processing the constant is kept outside the p/c equality group:

   p.item_id = 1001 and multiple equal(1001, a.id)

   and multiple equal(c.item_id, p.item_id) and multiple equal(c.ref_d, p.d)

4. After the join order is fixed, substitute_best_equal attaches the constant to c only:

   p.item_id = c.item_id and p.d = c.ref_d and p.type = 'A' ... and c.item_id = 1001

5. In attaching_conditions_to_tables, range analysis for p is redone without the constant. The k_item_d range used to cost the plan can no longer be built; only this is found:

   "index": "k_type_item_d", "ranges": ["(A) <= (type) <= (A)"],

   "rows": 4168118, "cost": 1513.198144, "chosen": false

6. Execution falls back to a full scan of partition pA with a BNL join buffer instead of the 2-row range access that was costed.

So the plan is costed with an access method that is not available at execution time, and the join order is not re-evaluated.

Workarounds (both confirmed)

• SELECT STRAIGHT_JOIN ... (forces a -> p -> c)
• SET STATEMENT join_cache_level=0 FOR SELECT ...

Results are correct; this is a performance issue only, but on a ~53M-row Aria table (table-level locks) it blocks writers for the whole scan.

The full optimizer trace (anonymized) can be provided on request.