Range optimizer should ignore metadata-only indexes with no handler capabilities

Description

A storage engine may expose primary/unique key metadata to MariaDB while not implementing physical index access through the handler API.

Such an engine reports no index capabilities by returning zero from handler::index_flags(). However, SQL_SELECT::test_quick_select() still considers these keys during range optimization and can construct an index range plan that eventually calls ha_index_read_map().

This turns a query that could be executed with a table scan into an execution-time HA_ERR_WRONG_COMMAND / ER_ILLEGAL_HA failure.

The problem is visible with the DuckDB storage engine. DuckDB exposes primary-key metadata for constraints, row identity and replication, but does not implement MariaDB index access.

Reproduction

Start MariaDB with binary logging enabled:

[mysqld]

log-bin=mariadb-bin

server-id=1

binlog-format=MIXED

Run:

CREATE TABLE invoice (

  id UUID DEFAULT UUID() PRIMARY KEY,

  product VARCHAR(64),

  amount DECIMAL(12,2),

  sold_at TIMESTAMP

) ENGINE=DuckDB DEFAULT CHARSET=utf8mb4;

INSERT INTO invoice (product, amount, sold_at) VALUES

  ('MariaDB mug', 12.00, NOW()),

  ('DuckDB sticker', 2.50, NOW());

DELETE FROM invoice

WHERE id = '1ed15f77-b44f-11f1-ae30-6ee3d9fbb323';

The issue can also be reproduced directly with row logging:

SET SESSION binlog_format=ROW;

UPDATE invoice

SET product='Updated'

WHERE id='1ed15f77-b44f-11f1-ae30-6ee3d9fbb323';

Actual result

ERROR 1031 (HY000):

Storage engine DUCKDB of the table `test`.`invoice` doesn't have this option

The optimizer selects the declared primary key and execution reaches ha_duckdb::index_read_map(), which returns HA_ERR_WRONG_COMMAND because DuckDB does not implement physical MariaDB index access.

The issue is especially visible with log_bin=ON and binlog_format=MIXED: a nondeterministic default such as DEFAULT UUID() marks the statement unsafe for statement logging, so MIXED switches it to ROW. This disables the direct DML path and exposes the invalid index range plan. With log_bin=OFF, the same DELETE succeeds through the engine's direct DELETE path.

Expected result

The optimizer should not construct an index access plan using a key for which the storage engine reports no index capabilities. It should fall back to a table scan, allowing the handler to execute the UPDATE or DELETE row by row and generate the required row events.

Analysis

DuckDB declares SQL key metadata because MariaDB needs it for:

• primary-key and uniqueness definitions;
• row identity;
• replication before-images;
• HA_PRIMARY_KEY_REQUIRED_FOR_DELETE;
• HA_PRIMARY_KEY_REQUIRED_FOR_POSITION.

However, these keys are not physical indexes accessible through the handler index API. The handler explicitly reports this:

ulong index_flags(uint inx, uint part, bool all_parts) const override

{

  return 0;

}

The handler API documentation and the example storage engine describe zero as the value to return when indexes are not implemented:

/* If you do not implement indexes, just return zero here. */

SQL_SELECT::test_quick_select() currently intersects keys_to_use with keys_in_use_for_query, but does not remove keys for which the handler reports no usable index capabilities.

Proposed fix

Filter keys_to_use before constructing the range optimizer parameters:

keys_to_use.intersect(head->keys_in_use_for_query);

for (idx= 0; idx < head->s->keys; idx++)

{

  if (keys_to_use.is_set(idx) &&

      !file->index_flags(idx, 0, true))

    keys_to_use.clear_bit(idx);

}

Location:

SQL_SELECT::test_quick_select()

sql/opt_range.cc

This preserves key metadata while excluding keys that advertise no handler index operations from range analysis. Engines advertising any supported index capability are unaffected.

Regression testing

The DuckDB replication test uses indexed predicates:

UPDATE t_upd SET v='TWO' WHERE id=2;

UPDATE t_upd SET id=30, v='moved' WHERE id=3;

DELETE FROM t_upd WHERE id=1;

The test verifies that:

1. range optimization does not choose DuckDB metadata-only keys;
2. execution falls back to a table scan;
3. UPDATE and DELETE row events are generated on the master;
4. the events are applied correctly on the replica;
5. primary-key-changing UPDATE remains correct;
6. MIXED and ROW replication do not fail with ER_ILLEGAL_HA.

Relevant test:

storage/duckdb/mysql-test/duckdb/t/mdev_40957_rpl.test