Supported SQL

Supported SQL

Verified against development commit c42e19eba on 27 August 2026. MemCP is Beta and does not claim complete MySQL or PostgreSQL compatibility. Unsupported shapes should fail explicitly; test a required workload before migration.

This page is a capability map for application evaluation. MemCP accepts a broad MySQL-oriented dialect through its MySQL protocol and HTTP endpoint, plus PostgreSQL-oriented syntax through a separate HTTP endpoint. Both compile into the same logical planner and storage runtime, but accepted surface syntax and returned metadata can differ by frontend.

Support is defined by tested semantics, not merely by whether the parser accepts a statement. Compatibility-only clauses may intentionally be no-ops, and uncommon combinations can remain unsupported even when their individual operators are available. Use this overview to select candidates, then verify every required query, constraint, transaction, prepared statement, and client behavior against the deployed release.

Query language

• SELECT, aliases, WHERE, DISTINCT, GROUP BY, HAVING, ORDER BY, LIMIT, and OFFSET;
• INNER, LEFT, RIGHT, and CROSS JOIN;
• UNION and UNION ALL, including ordered/limited set results;
• derived tables and logical SQL views;
• scalar, EXISTS, NOT EXISTS, IN, and NOT IN subqueries, including many correlated and grouped forms;
• aggregates COUNT, SUM, AVG, MIN, MAX, and GROUP_CONCAT;
• window functions including ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG, FIRST_VALUE, and LAST_VALUE with partition/order clauses.

Data modification and schema

• single- and multi-row INSERT, INSERT IGNORE, INSERT … SELECT, and MySQL or PostgreSQL upsert syntax;
• UPDATE and DELETE, including qualified and supported multi-table forms;
• CREATE, ALTER, RENAME, TRUNCATE, and DROP for databases and tables;
• CREATE VIEW, indexes, primary/unique keys, foreign keys, defaults, AUTO_INCREMENT, computed expressions, types, collations, and per-table ENGINE;
• persistent BEFORE/AFTER triggers for INSERT, UPDATE, and DELETE.

Foreign keys are enforced for the tested RESTRICT, CASCADE, and SET NULL paths. Some compatibility DDL is intentionally accepted as a no-op; for example, a client accepting ADD FULLTEXT does not imply a MySQL-compatible full-text index implementation.

Transactions, security, and metadata

• BEGIN, COMMIT, and ROLLBACK;
• users, passwords, grants, revokes, and database access policies;
• SHOW DATABASES, tables, columns, indexes, create-table metadata, variables, engines, processlist, and selected INFORMATION_SCHEMA relations;
• prepared statements over the MySQL protocol;
• KILL QUERY and KILL CONNECTION.

See Transactions and Isolation and Security and Authentication for the operational contract.

Expressions

MemCP implements SQL NULL/three-valued logic, CASE, COALESCE/NULLIF/IFNULL, arithmetic and bitwise operators, comparisons, BETWEEN, IN lists, LIKE/ILIKE, REGEXP/RLIKE, MATCH…AGAINST compatibility, casts, string functions, mathematical functions, hashes, date/time functions, intervals, and timezone conversion.

The exact parser and function catalog changes more often than this overview. Use the generated API reference and the SQL test taxonomy for detailed coverage.

JSON

MemCP stores declared JSON/JSONB values in a native tagged BSON representation. The MySQL dialect implements JSON validation, type inspection, path extraction, constructors, aggregates, mutation functions, containment/search operations, JSON_TABLE, and the ->/->> operators. The PostgreSQL dialect provides its corresponding json/jsonb operators, constructors, aggregates, path functions, and set-returning expansion forms. Deterministic path expressions can be materialized and indexed through the ordinary computed-column/index machinery. See JSON for the detailed compatibility contract and deviations.

Planner inspection

EXPLAIN SELECT ...; EXPLAIN IR SELECT ...; EXPLAIN REORDER SELECT ...; EXPLAIN PHYSICAL SELECT ...; EXPLAIN COMPILE SELECT ...;

See Query Planner and Physical Lowering.

Known limitations

MemCP is not a byte-for-byte MySQL server implementation. Metadata details, administrative commands, uncommon syntax, advanced locking modes, XA, stored procedures, and untested SQL-standard edge cases may be absent. Consult the current issue tracker and add a must-pass and must-fail test for every application-critical construct.