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 — for plugins that issue ordinary WordPress-core-shaped SQL, and eventually for raw-SQL-heavy ones.

This tracks the engine gaps found by auditing 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). Grep-verified, not assumed; corrections welcome.

Already supported (verified in source, no issue needed)

• 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.

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.

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.

Measuring readiness for a given suite

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. Once #376 lands I'll run it against data-machine-events' PHPUnit run and attach the report here as the ground-truth gap list.

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 tracker remains open for consumer-suite readiness: the specific downstream PHPUnit suites and shadow report described above have not yet been verified here. 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.