Tracker: mdi-native as a WordPress PHPUnit backend — engine gaps vs. WP core + real plugin SQL (wp-codebox#2487)

[chubes4]

Goal

Make mdi-native a viable database-type for wordpress.phpunit in wp-codebox (Automattic/wp-codebox#2487) — a dependency-free, secret-free, no-process WordPress test backend — with generic SQL and wpdb parity for arbitrary WordPress plugins and workloads.

The following sections retain the original audit of a real MySQL-heavy plugin (Extra-Chill/data-machine-events: 109 raw $wpdb call sites, 4 dbDelta tables, multisite suite) and the WordPress core test lifecycle against inc/native/ at 8e2b21f (v0.13) as historical evidence. Grep-verified, not assumed; corrections welcome.

Historical Baseline: Already Supported

• Bounded equality JOIN, LEFT JOIN; GROUP BY, HAVING, DISTINCT, UNION [ALL], LIMIT/OFFSET
• IN (SELECT …), NOT IN (SELECT …), EXISTS (SELECT …) subquery predicates
• LIKE, REGEXP, CASE, COALESCE, GROUP_CONCAT, JSON_*, SQL_CALC_FOUND_ROWS/FOUND_ROWS
• SHOW TABLES [LIKE], SHOW [FULL] COLUMNS, DESCRIBE, SHOW INDEX/KEYS, SHOW VARIABLES/STATUS
• CREATE TABLE, ALTER TABLE (single-column), INSERT IGNORE, ON DUPLICATE KEY UPDATE, transactions + SAVEPOINT
• Deterministic markdown_db_native_unsupported_query errors with a structured reason — good; the PHPUnit integration will surface these as a distinct diagnosis cause.

Historical Baseline: Gaps

• native: support SELECT ... FOR UPDATE (accept-and-ignore under single-writer semantics) #371 — SELECT … FOR UPDATE (accept-and-ignore under single-writer semantics). Blocks: any lock-then-write pattern.
• native: expand the scalar-function table (DATE/time, GREATEST/LEAST, math incl. trig, string) to what WP core and common plugins issue #372 — scalar-function coverage: DATE(), DATE_ADD/SUB, NOW(), GREATEST/LEAST, IF/IFNULL, string, math incl. trig. Blocks: date bucketing, geo distance, most WP_Query date queries.
• native: relax JOIN shape — arbitrary ON predicates, aliased self-joins, derived tables in FROM #373 — JOIN shape: arbitrary ON predicates, aliased self-joins (WP_Meta_Query mt1/mt2), derived tables in FROM. Blocks: core meta/tax queries; UNION-in-FROM.
• native: route queries by table prefix for multisite (unsupported_table_prefix blocks every multisite boot and suite) #374 — table-prefix routing for multisite (unsupported_table_prefix). Blocks: every multisite boot and every wp_codebox_multisite: true suite.
• native: verify dbDelta() round-trips (DESCRIBE/SHOW INDEX shape, composite/unique/sub-part keys, ALTER diffs) as a no-op on rerun #375 — dbDelta() round-trip verification (rerun must be a no-op; composite/unique/sub-part keys; ALTER diffs). Blocks: plugin schema installs behaving like MariaDB.
• probes: run-native-mysql-coverage / lifecycle recipes fail schema validation against current wp-codebox; pin + commit the coverage report #376 — the coverage/lifecycle probes fail schema validation against current wp-codebox; pin and commit the coverage report so consumers can read the supported surface.

Historical Baseline: Suggested Order

#376 first (so every other item can be measured with the coverage report and shadow verifier instead of grep), then #374 (multisite is a hard wall for the target consumers), then #372 → #373 → #371 → #375.

Historical Baseline: Measuring Readiness

MARKDOWN_DB_NATIVE_SHADOW=true + tests/probe-native-shadow-report.php replays a real suite's reads through mdi-native without failing them and reports unsupported/mismatched templates. This was the proposed measurement path before #376 and the later paired consumer evidence; the current paired result is recorded below.

Historical Landed Engine Repairs

The six engine/probe gaps landed in #384 (commit d15708f8c8e80e650a7a7e023dce7e9f712a41bb). Source PRs #378 through #383 are closed as superseded. The integrated candidate passed 45/45 SQL corpus statements and 26/26 WordPress operations, the full smoke suite, SQLite differential, WooCommerce lifecycle, multisite cold reload, and native/MariaDB dbDelta parity. Scalar/subquery boolean composition and combined-result UNION ordering/limits are included.

This was the consumer-suite status when the repair landed. Later paired consumer evidence is recorded below. No release or deployment was performed.

AI Assistance

GPT-6 Astra (openai/gpt-6-astra) via OpenCode coordinated parallel OpenAI GPT-5.6 Terra (openai/gpt-5.6-terra) workers, reviewed and corrected candidates, checked Lab evidence, and merged the integrated repair under Chris Huber's direction.

Current Status and Priority (September 12, 2026)

This tracker now prioritizes generic SQL and wpdb parity for arbitrary WordPress plugins and workloads. The earlier corpus and consumer references are historical progress measures, not a limit on the engine contract.

Merged #403 provides the latest paired consumer evidence with the same companion adapter:

Backend	Passed	Failed	Skipped	Total
Native	1040	4	9	1053
MySQL	1046	0	7	1053

The four remaining native failures require physical mysqli connections/handles, which are outside the native-engine contract. Two smoke-native-table-replace.php failures predate this work and remain separate parity evidence. Earlier 942/70/32 counts are stale.

Merged #399, #400, #403, #404, #405, and #406 supply the current repair evidence. #370 merged on September 9, 2026; any earlier reference to #370 as current or draft is historical context only.

Performance profiling may proceed in parallel where it preserves or improves parity. The next performance objective, after sufficient parity evidence, is faster and more scalable operation than both SQLite and MySQL. Neither target has been achieved.

CLI-First Acceptance

CLI is the primary entrypoint. An acceptance CLI invocation must run in a fresh process and measure total time through boot, command setup, query or workload execution, shutdown, and durable write completion. Warm-process diagnostic loops are permitted separately and must be labeled as diagnostics. Results must establish correctness and durability, and separately report cold-cache and warm-cache measurements.

Acceptance evidence must use identical corpus, runtime, iteration, and concurrency settings for comparisons. It must report scaling, peak memory, concurrent-workload behavior, throughput, and tail latency as well as aggregate latency. Representative corpora demonstrate progress without narrowing arbitrary-plugin scope.

The epic's staged local-site rehearsal, cutover, SQLite-removal, and production gates remain intact.

Current Status AI Assistance

GPT-6 Astra (openai/gpt-6-astra) via OpenCode assisted refreshing status and priorities based on source and merged evidence under maintainer direction.