MySQL Features Not Supported by MatrixOne
This page lists MySQL features and SQL syntax that MatrixOne either does not support at all (Completely Missing) or supports with documented differences (Partial Support).
- Completely Missing entries are curated manually for MySQL features that have no corresponding MatrixOne documentation page.
- Partial Support entries are extracted from
differs_from_mysqlfrontmatter onmysql_compat: partialpages underdocs/MatrixOne/Reference/**.
Summary
| Source | Count |
|---|---|
| Completely Missing (curated) | 131 |
| Partial Support | 209 |
| Total | 340 |
Completely Missing
These MySQL features have no MatrixOne counterpart and are not available in any form.
DDL — Data Definition Language
| Feature | Note |
|---|---|
| ALTER DATABASE | No ALTER DATABASE support |
| CREATE / DROP TRIGGER | Triggers are not supported |
| CREATE / DROP EVENT | Event scheduler is not supported |
| CREATE / DROP PROCEDURE | Stored procedures are not supported |
| CREATE FUNCTION (SQL body) | Only Python UDFs and simple SQL functions are supported; MySQL-style compound-statement function bodies are not |
| ALTER TABLE ... PARTITION | Partition management via ALTER TABLE is not supported |
| ALTER TABLE ... ALGORITHM / LOCK | ALTER TABLE algorithm/lock hints are not supported |
| ENGINE= clause in CREATE TABLE | ENGINE= clause is syntactically accepted but ignored; MatrixOne uses TAE exclusively |
| CREATE TABLE ... TABLESPACE | Tablespace assignment is not supported |
| CREATE TABLE with GENERATED columns | Generated (computed) columns are not supported |
| CREATE TABLE with CHECK constraints | CHECK constraints are not enforced |
| CREATE VIEW ... WITH CHECK OPTION | WITH CHECK OPTION is syntactically accepted but not enforced for views |
| CREATE VIEW ... DEFINER / SQL SECURITY | DEFINER and SQL SECURITY clauses are not supported |
| Materialized Views | CREATE MATERIALIZED VIEW is not supported |
| Character sets and collations beyond utf8mb4/utf8mb4_bin | Only utf8mb4/utf8mb4_bin are functional; other charsets/collations (latin1, gbk, utf8, utf8mb3) are syntactically accepted but have no effect |
| ALTER EVENT | Event body/schedule/status modification not supported (no event scheduler) |
| ALTER FUNCTION (MySQL stored function) | Modifying MySQL-style stored function characteristics is not supported; ALTER FUNCTION in MatrixOne serves a different purpose |
| ALTER INSTANCE | MySQL 8.0 instance reconfiguration (e.g. InnoDB redo log rotation) is not supported |
| ALTER PROCEDURE | Modifying stored procedure characteristics is not supported (no stored procedures) |
| ALTER RESOURCE GROUP | Resource group VCPU/thread priority management is not supported |
| ALTER SERVER | FEDERATED engine server connection options are not supported |
| ALTER TABLESPACE | Tablespace data file add/drop/rename is not supported |
| CREATE RESOURCE GROUP | Resource group creation (VCPU affinity, thread priority) is not supported |
| CREATE SERVER | FEDERATED engine remote server definitions are not supported |
| CREATE SPATIAL REFERENCE SYSTEM | Custom spatial reference systems for GIS are not supported |
| CREATE TABLESPACE | General/undo tablespace creation is not supported; MatrixOne manages storage automatically |
| DROP RESOURCE GROUP | Resource group removal is not supported |
| DROP SERVER | FEDERATED server definition removal is not supported |
| DROP SPATIAL REFERENCE SYSTEM | SRS definition removal is not supported |
| DROP TABLESPACE | Tablespace removal is not supported; MatrixOne manages storage automatically |
DML — Data Manipulation Language
| Feature | Note |
|---|---|
| HANDLER statements | HANDLER OPEN / READ / CLOSE are not supported |
| LOAD XML | LOAD XML INFILE is not supported; use LOAD DATA for CSV/JSONL |
| CALL procedure_name() | Stored procedure execution is not supported |
| DO statement | DO expr [, expr] ... is not supported |
| IMPORT TABLE | MySQL 8.0 IMPORT TABLE (import InnoDB .ibd tablespace files) is not supported |
| Parenthesized Query Expressions | MySQL 8.0.19+ parenthesized query blocks with per-block ORDER BY / LIMIT are not supported |
| TABLE statement | MySQL 8.0.19+ TABLE tablename (equivalent to SELECT * FROM) is not supported |
| VALUES statement (DML) | MySQL 8.0.19+ VALUES row_constructor_list as standalone DML is not supported |
DCL — Data Control Language
| Feature | Note |
|---|---|
| GRANT with PROXY | PROXY user grants are not supported |
| RENAME USER | RENAME USER is not supported; use ALTER USER instead |
| SET PASSWORD | SET PASSWORD is syntactically accepted but does not change the password; use ALTER USER instead |
| SQL modes beyond ONLY_FULL_GROUP_BY | Only ONLY_FULL_GROUP_BY has actual effect; other SQL modes (STRICT_TRANS_TABLES, NO_ZERO_DATE, NO_ENGINE_SUBSTITUTION, etc.) are syntax-only with no behavioral impact |
| Connection limit clauses (MAX_USER_CONNECTIONS, MAX_QUERIES_PER_HOUR, etc.) | ALTER USER account resource limit clauses are not honored |
| IP whitelisting for user accounts | Connection IP whitelisting / host-based access control is not supported |
Data Types
| Feature | Note |
|---|---|
| Spatial types (GEOMETRY, POINT, LINESTRING, POLYGON, etc.) | Spatial type names are syntactically accepted but non-functional (no spatial operations or GIS functions work) |
| MEDIUMINT | MEDIUMINT is syntactically accepted but treated as INT; prefer explicit INT or SMALLINT for clarity |
| Year type (with 2-digit format) | YEAR(2) is not supported; only YEAR(4) / YEAR is available |
| BIT(M) with M > 64 | BIT type is supported but length limits may differ |
| ENUM sorting and filtering | ENUM values can only be compared with strings in WHERE conditions; ENUM filtering and sorting are not supported |
| DECIMAL precision limits | DECIMAL(P, D) accepts P up to at least 65 but precision enforcement may differ from MySQL; verify exact behavior for financial applications requiring high precision |
Indexes & Constraints
| Feature | Note |
|---|---|
| SPATIAL INDEX | Spatial indexes are not supported |
| FULLTEXT INDEX (MySQL native syntax) | MatrixOne has its own full-text index syntax (CREATE FULLTEXT INDEX) that differs from MySQL |
| FOREIGN KEY ... ON DELETE SET DEFAULT / ON UPDATE SET DEFAULT | SET DEFAULT referential action is not supported (CASCADE and SET NULL work) |
| Index hints (USE INDEX, FORCE INDEX, IGNORE INDEX) | Index hints in SELECT statements are not supported |
| Descending indexes | DESC in index column definitions is not supported |
| Functional / expression indexes | Indexes on expressions are not supported |
| Invisible indexes | ALTER INDEX ... INVISIBLE is not supported |
Storage Engine
| Feature | Note |
|---|---|
| InnoDB storage engine | MatrixOne uses TAE (Transactional Analytical Engine) instead of InnoDB; ENGINE= clause is ignored |
| MyISAM / MEMORY / ARCHIVE / CSV engines | Only the TAE engine is available; alternative storage engines are not supported |
Partitioning
| Feature | Note |
|---|---|
| Subpartitioning | Subpartitioning is not supported |
| Partition management (REORGANIZE, COALESCE, EXCHANGE, etc.) | MySQL partition management operations are not supported |
Transactions
| Feature | Note |
|---|---|
| SAVEPOINT / ROLLBACK TO SAVEPOINT | Savepoints within transactions are not supported |
| RELEASE SAVEPOINT | Releasing a transaction savepoint is not supported (no savepoints) |
| XA transactions (distributed transactions) | XA START / XA END / XA PREPARE / XA COMMIT are not supported; MatrixOne uses its own distributed transaction model |
| LOCK TABLES / UNLOCK TABLES | LOCK TABLES / UNLOCK TABLES is syntactically accepted but does not enforce actual table-level locks |
| LOCK INSTANCE FOR BACKUP / UNLOCK INSTANCE | MySQL 8.0 backup-oriented global instance locks are not supported |
| FLUSH TABLES WITH READ LOCK | Global read locks are not supported |
| SET operations within transactions | SET variable assignments are not allowed within an active transaction block |
Replication & Binary Log
| Feature | Note |
|---|---|
| Binary log (binlog) | MySQL binary log and related statements are not supported; MatrixOne uses its own CDC mechanism |
| SHOW BINARY LOGS / SHOW MASTER LOGS | Listing binary log files on the server is not supported |
| SHOW BINLOG EVENTS | Displaying events in a binary log is not supported |
| SHOW MASTER STATUS | Showing source server binary log position is not supported |
| PURGE BINARY LOGS | Deleting binary log files is not supported |
| SHOW REPLICA STATUS / SHOW SLAVE STATUS | Showing replica server status is not supported |
| SHOW REPLICAS / SHOW SLAVE HOSTS | Listing registered replicas is not supported |
| SHOW RELAYLOG EVENTS | Displaying relay log events is not supported |
| CHANGE MASTER TO / START SLAVE / STOP SLAVE | MySQL replication protocol (source/replica management) is not supported; MatrixOne has mo_cdc and pub/sub instead |
| RESET MASTER / RESET SLAVE | MySQL replication management commands are not supported |
| CLONE LOCAL DATA DIRECTORY | MySQL 8.0 clone plugin local cloning is not supported |
| CLONE INSTANCE | MySQL 8.0 clone plugin remote cloning is not supported |
SHOW Statements
| Feature | Note |
|---|---|
| SHOW TRIGGER | Not supported (no triggers in MatrixOne) |
| SHOW EVENTS | Not supported (no event scheduler) |
| SHOW PROCEDURE STATUS | Not supported (no stored procedures) |
| SHOW ENGINE | Not supported; MatrixOne does not expose storage engine internals |
| SHOW ENGINES | Accepted syntactically but produces empty result set; MatrixOne uses TAE exclusively |
| SHOW STATUS | Accepted syntactically but produces empty output |
| SHOW PRIVILEGES | Accepted syntactically but produces empty output |
| SHOW CHARACTER SET | Accepted syntactically but returns empty result set; only utf8mb4 charset is available |
| SHOW PROFILE / SHOW PROFILES | Query profiling via SHOW PROFILE is not supported |
| SHOW OPEN TABLES | Not supported |
| SHOW PLUGINS | Not supported |
| SHOW ERRORS / SHOW WARNINGS | Results differ significantly from MySQL due to different implementation |
| SHOW CREATE USER | Not supported; returns a parser error |
| SHOW CREATE EVENT | Not supported (no event scheduler) |
| SHOW CREATE PROCEDURE | Not supported (no stored procedures) |
| SHOW CREATE TRIGGER | Not supported (no triggers) |
| SHOW FUNCTION CODE | Not supported (no stored function internals to display) |
| SHOW PROCEDURE CODE | Not supported (no stored procedure internals to display) |
System & Administration
| Feature | Note |
|---|---|
| FLUSH statements (FLUSH LOGS, FLUSH TABLES, FLUSH PRIVILEGES, etc.) | FLUSH operations are not supported; privilege changes take effect without FLUSH PRIVILEGES |
| CACHE INDEX / LOAD INDEX INTO CACHE | Key cache management is not supported |
| CHECKSUM TABLE | Not supported |
| OPTIMIZE TABLE | Not supported; MatrixOne handles storage optimization automatically |
| REPAIR TABLE | Not supported |
| CHECK TABLE | Not supported |
| mysql.* system database | The mysql system database is not accessible; MatrixOne has its own system metadata tables (mo_catalog) |
| INFORMATION_SCHEMA (full) | INFORMATION_SCHEMA tables are present but most return empty result sets |
| PERFORMANCE_SCHEMA | Performance Schema is not available |
| INSTALL PLUGIN / UNINSTALL PLUGIN | Plugin system is not supported |
| INSTALL COMPONENT / UNINSTALL COMPONENT | Component system is not supported |
| RESET / RESET PERSIST | System variable persistence management is not supported |
| RESTART | RESTART server statement is not supported |
| SHUTDOWN | Server shutdown statement is not supported |
| BINLOG statement | The BINLOG statement for executing events decoded from a binary log is not supported |
| HELP statement | HELP command for SQL syntax reference is not supported |
Functions & Operators
| Feature | Note |
|---|---|
| MySQL native full-text search functions (MATCH ... AGAINST) | MatrixOne full-text search uses different syntax; MySQL MATCH AGAINST is not available |
| Window functions: NTILE, FIRST_VALUE, LAST_VALUE, NTH_VALUE, LEAD, LAG | Some MySQL window functions are not yet supported in MatrixOne |
| GIS / spatial functions | ST_* spatial functions are not supported (no spatial data types) |
| XML functions (ExtractValue, UpdateXML) | XML processing functions are not supported |
| Performance Schema functions | FORMAT_BYTES, FORMAT_PICO_TIME, PS_THREAD_ID, etc. are not available |
| GROUPING() | GROUPING function for ROLLUP identification may behave differently |
| JSON functions (bulk missing) | Only ~8 JSON functions supported (JSON_UNQUOTE, JSON_QUOTE, JSON_EXTRACT, JSON_SET, JSON_ROW, etc.) vs MySQL 30+. Missing: JSON_OBJECT, JSON_ARRAY, JSON_MERGE, JSON_SEARCH, JSON_CONTAINS, JSON_KEYS, JSON_LENGTH, JSON_TYPE, JSON_VALID, JSON_TABLE, JSON_ARRAYAGG, JSON_OBJECTAGG, and more |
| GET_LOCK() / RELEASE_LOCK() | Advisory (user-level) locks are not supported |
| BENCHMARK() | BENCHMARK(count, expr) function for measuring SQL execution speed is not supported |
Peripheral Tools
| Feature | Note |
|---|---|
| mysql_upgrade | Database upgrade tool is not available; MatrixOne has its own upgrade procedure |
| mysqlcheck | Table checking/repair tool is not available |
| mysqlbinlog | Binary log utility is not available |
| mysqlpump / mysqldump | Use mo_dump for MatrixOne logical backups |
| mysqlslap | Load testing client is not available |
| mysql_config_editor | Login path configuration is not available |
| xtrabackup | Physical backup uses mo_br instead; xtrabackup / mariabackup are not compatible |
DQL
| Feature | Note |
|---|---|
| FULL JOIN / FULL OUTER JOIN | FULL JOIN is not supported; emulate with LEFT JOIN UNION RIGHT JOIN |
Partial Support
These MySQL features are partially supported by MatrixOne with documented differences. Each row links to the relevant MatrixOne documentation page for full details.
DDL — Data Definition Language
| Statement | Difference from MySQL |
|---|---|
| ALTER TABLE | Multiple ALTER TABLE operations can be combined in one statement, with limitation: DROP PRIMARY KEY cannot be combined with RENAME COLUMN, CHANGE COLUMN, or DROP COLUMN (causes server panic); DROP PK + ADD COLUMN and DROP PK + MODIFY COLUMN work correctly |
| ALTER TABLE | Temporary tables cannot be altered |
| ALTER TABLE | ALTER TABLE does not support PARTITION operations |
| ALTER VIEW | WITH CHECK OPTION is accepted in CREATE VIEW (syntax only, views are read-only) but rejected as a syntax error in ALTER VIEW |
| CREATE DATABASE | Only utf8mb4 / utf8mb4_bin are functional; other charsets/collations are syntactically accepted but have no effect |
| CREATE DATABASE | ENCRYPTION clause accepted but inert |
| Create Fulltext Index | MatrixOne full-text index is implemented on TAE storage with CJK/English optimizations; MySQL implements it on InnoDB/MyISAM with different stopword and parser semantics. |
| CREATE FUNCTION...LANGUAGE SQL AS | Only LANGUAGE SQL and LANGUAGE PYTHON are supported; usage differs significantly from MySQL stored functions |
| CREATE FUNCTION...LANGUAGE SQL AS | CREATE OR REPLACE FUNCTION is supported; MySQL 8.0 does not support OR REPLACE for functions (only IF NOT EXISTS since 8.0.29) |
| CREATE INDEX | Secondary indexes are supported and participate in query optimization (as of MO 3.0.12, EXPLAIN shows Index Table Scan for secondary index queries). Does not support index hints (USE INDEX, FORCE INDEX, IGNORE INDEX), function-based indexes, or FULLTEXT index via CREATE INDEX syntax (use CREATE FULLTEXT INDEX instead). |
| CREATE TABLE | ENGINE= clause is syntactically accepted but ignored; MatrixOne uses TAE exclusively |
| CREATE TABLE | Spatial type names (GEOMETRY, POINT, etc.) are syntactically accepted but non-functional; MEDIUMINT is syntactically accepted but treated as INT |
| CREATE TABLE | BOOL is a native boolean type, not an INT alias as in MySQL |
| CREATE TABLE | AUTO_INCREMENT step is always 1; @@auto_increment_increment is syntactically accepted but inert |
| CREATE TABLE | Partitioning accepts syntax but only HASH and KEY participate in partition pruning (RANGE/LIST/RANGE COLUMNS/LIST COLUMNS are syntax-only); subpartitioning causes an internal error; ADD/DROP/TRUNCATE PARTITION not supported |
| CREATE TABLE | CHECK constraints are syntactically accepted but not enforced; MySQL 8.0.16+ enforces them |
| CREATE VIEW | WITH CHECK OPTION is syntactically accepted but not enforced |
| CREATE VIEW | Views are read-only; MySQL 8.0 supports INSERT/UPDATE/DELETE through views that meet updatability criteria |
| DROP FUNCTION | Drops MatrixOne-style SQL / Python functions, not MySQL stored procedures/functions |
| DROP FUNCTION | Requires argument type list on DROP (e.g. DROP FUNCTION py_add(int, int)); MySQL 8.0 accepts only the function name |
| DROP INDEX | MO accepts DROP INDEX IF EXISTS syntax (MySQL 8.0 does not), but IF EXISTS does not suppress errors for missing indexes; it returns internal error 20101 instead of a silent skip |
| DROP VIEW | MO does not support dropping multiple views in a single statement; only a single view per DROP VIEW. MySQL 8.0 supports dropping multiple views (e.g., DROP VIEW v1, v2). |
| Rename Table | MO does not support RENAME TABLE across databases; when given cross-database syntax, MO renames the table within its current database instead of raising an error. MySQL 8.0 supports cross-database RENAME TABLE. |
DML — Data Manipulation Language
| Statement | Difference from MySQL |
|---|---|
| CURRENT_ROLE() | Returns a single active role name; MySQL 8.0 can return multiple comma-separated active roles or 'NONE'. |
| DELETE | LOW_PRIORITY, QUICK, IGNORE modifiers are syntactically accepted but have no effect |
| DELETE | PARTITION clause not supported |
| INSERT | Modifiers LOW_PRIORITY / DELAYED / HIGH_PRIORITY not supported |
| INSERT | PARTITION clause not supported |
| INSERT ... ON DUPLICATE KEY UPDATE | ON DUPLICATE KEY UPDATE only triggers on PRIMARY KEY conflicts; UNIQUE index conflicts are detected but result in errors (ERROR 1062 or ERROR 20102) rather than triggering ON DUPLICATE KEY UPDATE |
| INSERT IGNORE | LOW_PRIORITY / DELAYED / HIGH_PRIORITY modifiers not supported |
| INSERT IGNORE | Duplicates are silently ignored; MySQL emits a warning for each skipped row. |
| INSERT IGNORE | Does not ignore NULL-into-NOT-NULL, type-conversion, or partition-mismatch errors as MySQL does. |
| INSERT IGNORE | PARTITION clause not supported |
| LAST_INSERT_ID() | Multi-row INSERT returns the last inserted auto-increment value; MySQL returns the first inserted value. |
| LOAD DATA | SET clause only accepts columns_name = nullif(expr1, expr2) |
| LOAD DATA | JSONLines import uses MatrixOne-specific syntax |
| LOAD DATA | Object-storage import (S3/URL) uses MatrixOne-specific syntax |
| LOAD DATA | LOW_PRIORITY and CONCURRENT modifiers not supported |
| LOAD DATA | REPLACE and IGNORE modifiers not supported |
| REPLACE | node-sql-parser rejects REPLACE … WHERE (parser bug, not MatrixOne) |
| REPLACE | PARTITION clause not supported |
| REPLACE | LOW_PRIORITY and DELAYED modifiers not supported |
| REPLACE | REPLACE only detects conflicts on PRIMARY KEY; secondary UNIQUE index conflicts throw ERROR 1062 (MySQL 8.0 handles both). Constraints section incorrectly states UNIQUE index can also trigger REPLACE; this is wrong per actual MO behavior. |
| REPLACE | node-sql-parser rejects REPLACE … WHERE (parser bug, not MatrixOne) |
| REPLACE | PARTITION clause not supported |
| REPLACE | LOW_PRIORITY and DELAYED modifiers not supported |
| REPLACE | REPLACE only detects conflicts on PRIMARY KEY; secondary UNIQUE index conflicts throw ERROR 1062 (MySQL 8.0 handles both). Constraints section incorrectly states UNIQUE index can also trigger REPLACE; this is wrong per actual MO behavior. |
| UPDATE | LOW_PRIORITY and IGNORE modifiers are syntactically accepted but have no effect |
| UPDATE | PARTITION clause not supported |
| UPSERT | INSERT IGNORE does not suppress NOT NULL or type-conversion errors (MySQL 8.0 does) |
| UPSERT | INSERT ON DUPLICATE KEY UPDATE only triggers on PRIMARY KEY conflicts; UNIQUE index conflicts are detected but result in errors (ERROR 1062 or ERROR 20102) rather than triggering ON DUPLICATE KEY UPDATE |
| UPSERT | REPLACE does not support REPLACE ... WHERE (parser bug) |
DQL
| Statement | Difference from MySQL |
|---|---|
| Combining Queries (UNION, INTERSECT, MINUS) | MINUS keyword is MO-specific; MySQL 8.0.31+ uses EXCEPT for the same set-difference semantics. MINUS ALL is not yet implemented in MO while MySQL 8.0.31+ supports EXCEPT ALL. |
| Combining Queries (UNION, INTERSECT, MINUS) | INTERSECT was added in MySQL 8.0.31; both MO and MySQL support INTERSECT and INTERSECT ALL with matching semantics. |
| Combining Queries (UNION, INTERSECT, MINUS) | UNION is standard across both, but MO's type coercion in UNION columns is stricter (errors on incompatible types where MySQL silently coerces). |
| Derived Tables | LATERAL derived tables are not supported in MO (MySQL 8.0.14+ supports LATERAL for correlated subqueries in FROM clause) |
| FULL JOIN | FULL JOIN with ON clause produces different errors on MO (missing FROM-clause entry) vs MySQL 8.0 (Unknown column in ON clause). FULL JOIN with USING returns INNER JOIN results on both (neither returns unmatched rows). FULL OUTER JOIN produces a syntax error on both. MySQL 8.0 does not natively support either FULL JOIN or FULL OUTER JOIN. |
| INTERSECT | INTERSECT was added in MySQL 8.0.31; MO INTERSECT and INTERSECT ALL semantics match MySQL 8.0 (both return identical results for common test cases including duplicate handling) |
| JOIN | FULL JOIN and FULL OUTER JOIN are not fully supported (FULL JOIN with ON produces errors, FULL JOIN with USING returns INNER JOIN results, FULL OUTER JOIN is a syntax error); MySQL 8.0 also does not support FULL JOIN/OUTER JOIN natively |
| OUTER JOIN | Overview page that includes FULL OUTER JOIN; neither MO nor MySQL 8.0 natively support FULL OUTER JOIN (MO produces syntax error, same as MySQL) |
| SELECT | SELECT … FOR UPDATE only supports single-table queries |
| SELECT | SELECT INTO OUTFILE is only partially supported |
| SELECT | AS OF TIMESTAMP time-travel queries require PITR/snapshot to be enabled on the database; without PITR the syntax produces an error |
| SELECT | SELECT ... FOR SHARE is not supported |
| SELECT | FOR UPDATE NOWAIT and SKIP LOCKED modifiers are not supported |
| SELECT | GROUP BY ... WITH ROLLUP row ordering differs: MO places rollup summary rows at the top of the result set, while MySQL 8.0 places them at the bottom (standard MySQL grouping order) |
| SUBQUERY | Multi-column scalar subquery comparisons (e.g., WHERE (a,b) = (SELECT ...)) are not supported; use multi-column IN instead |
| UNION | UNION type coercion is strict: MO errors on incompatible types in UNION columns (e.g., INT vs VARCHAR), while MySQL 8.0 silently coerces (e.g., varchar to int converts to 0) |
| UNION | UNION ALL type coercion is similarly strict compared to MySQL 8.0's lenient coercion |
| WITH (Common Table Expressions) | Outer joins (LEFT JOIN, RIGHT JOIN, OUTER JOIN) are not allowed in recursive CTE members; MySQL 8.0 permits them except when the recursive CTE is on the right side of a LEFT JOIN (MySQL allows LEFT JOIN with CTE on the left side; MO rejects all outer joins in recursive CTEs regardless of position) |
DCL — Data Control Language
| Statement | Difference from MySQL |
|---|---|
| ALTER USER | Only ALTER USER can change passwords; account-limit clauses not honoured |
| ALTER USER | Password management options (PASSWORD EXPIRE, PASSWORD HISTORY, PASSWORD REUSE INTERVAL, PASSWORD REQUIRE CURRENT, FAILED_LOGIN_ATTEMPTS, PASSWORD_LOCK_TIME) not supported |
| ALTER USER | Account locking (ACCOUNT LOCK/UNLOCK) not supported |
| ALTER USER | REQUIRE clause (TLS/SSL enforcement) not supported |
| ALTER USER | COMMENT and ATTRIBUTE modification not supported |
| ALTER USER | Multiple users per statement not supported (MySQL 8.0 allows user [, user] ...) |
| CREATE ROLE | Role exists inside MatrixOne's multi-account model; roles are account-scoped, not server-global as in MySQL. |
| CREATE USER | IDENTIFIED BY is the only supported password form; IDENTIFIED WITH plugins not supported |
| CREATE USER | Connection-IP whitelists and connection-limit clauses not supported |
| CREATE USER | COMMENT and ATTRIBUTE clauses not supported |
| CREATE USER | 'user'@'host' syntax is accepted and host is stored in mo_catalog.mo_user.user_host but may not restrict connections; users are scoped to the current account, not server-global as in MySQL |
| CREATE USER | Password management options (PASSWORD EXPIRE, PASSWORD HISTORY, PASSWORD REUSE INTERVAL, PASSWORD REQUIRE CURRENT) not supported |
| CREATE USER | Account locking (ACCOUNT LOCK/UNLOCK) not supported |
| CREATE USER | REQUIRE clause (TLS/SSL enforcement) not supported |
| DROP ROLE | Role exists inside MatrixOne's multi-account model; roles are account-scoped, not server-global as in MySQL. |
| DROP USER | User identifier is a bare username scoped to the current account; MySQL uses 'user'@'host' tuples. |
| GRANT | Authorization logic differs from MySQL — MatrixOne evaluates via its role/account model |
| GRANT | User identifier is a bare username scoped to the current account; MySQL uses 'user'@'host' tuples |
| GRANT | AS user [WITH ROLE ...] clause (MySQL 8.0 privilege restriction) not supported |
| GRANT | GRANT privilege ... TO only accepts roles; users receive privileges indirectly through role membership (GRANT role TO user) |
| GRANT | WITH ADMIN OPTION for role grants is not supported |
| REVOKE | Recovery logic differs from MySQL — privileges return to the role/account graph |
| REVOKE | User identifier is a bare username scoped to the current account; MySQL uses 'user'@'host' tuples |
| REVOKE | IGNORE UNKNOWN USER clause (MySQL 8.0.30+) not supported |
Other
| Statement | Difference from MySQL |
|---|---|
| DEALLOCATE PREPARE | DEALLOCATE PREPARE on a non-existent statement silently succeeds; MySQL returns ERROR 1243 (Unknown prepared statement handler). |
| DESCRIBE / DESC | DESCRIBE/DESC output includes an extra Comment column (7 columns total vs MySQL's 6). |
| DESCRIBE / DESC | Type names are displayed in uppercase with display widths (e.g., INT(32), FLOAT(0), TIMESTAMP(0)) instead of MySQL's lowercase without widths (e.g., int, float, timestamp). |
| DESCRIBE / DESC | Column name filter (DESC tbl_name col_name) is non-functional; all columns are returned. |
| DESCRIBE / DESC | Wild pattern (DESC tbl_name 'pattern') not supported; produces syntax error. |
| DESCRIBE / DESC | For TIMESTAMP columns with DEFAULT CURRENT_TIMESTAMP, MO does not show DEFAULT_GENERATED in the Extra column as MySQL does. |
| EXPLAIN | Output format is a single QUERY PLAN column with tree-structured text (PostgreSQL-style); MySQL uses a multi-column tabular format with id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra |
| EXPLAIN | JSON output (FORMAT=JSON) not supported; MO returns syntax error |
| EXPLAIN | FORMAT=TREE and FORMAT=TRADITIONAL not supported; FORMAT=TEXT bare keyword also errors; only bare keyword forms (EXPLAIN, EXPLAIN ANALYZE, EXPLAIN VERBOSE) work |
| EXPLAIN | EXPLAIN FOR CONNECTION not supported (returns internal error) |
| EXPLAIN | EXPLAIN (ANALYZE TRUE/FALSE) and EXPLAIN (VERBOSE TRUE/FALSE) parenthesized boolean syntax WORKS in MO 3.0.12, contrary to doc claims; only parenthesized FORMAT syntax is unsupported |
| EXPLAIN Output Format | Output is a single QUERY PLAN column with tree-structured text; MySQL uses multi-column tabular EXPLAIN format |
| EXPLAIN Output Format | JSON output not supported |
| EXPLAIN Output Format | Node types (Sink, Sink Scan, PreInsert, Fuzzy Filter, etc.) are MO-specific and have no MySQL equivalent |
| Get information with EXPLAIN ANALYZE | Output format mirrors PostgreSQL (QUERY PLAN tree with Analyze sub-lines showing timeConsumed, waitTime, inputRows, outputRows, InputSize, OutputSize, MemorySize); MySQL 8.0 EXPLAIN ANALYZE uses TREE format with cost estimation and actual time in a different structure |
| Get information with EXPLAIN ANALYZE | JSON output not supported |
| Get information with EXPLAIN ANALYZE | MO EXPLAIN ANALYZE produces one output row per plan tree line; MySQL produces a single row with the full plan |
| PREPARE | MatrixOne cannot PREPARE SET, DO, or other TCL/DCL statements |
| PREPARE | Repreparation on parameter type change may throw a cast error instead of silently converting the value (e.g., passing a string to an integer parameter). |
| SET ROLE | Accepts a single role name only; MySQL 8.0 also supports NONE, DEFAULT, ALL, ALL EXCEPT role_list, and role lists. |
| SHOW COLLATION | Only utf8mb4_bin is effective; other collations appear but are inert |
| SHOW COLLATION | MO 3.0.12 returns 11 collations (with Default and Pad_attribute columns); older doc examples show only 1 row with 5 columns |
| SHOW COLLATION | Output columns (Collation, Charset, Id, Default, Compiled, Sortlen, Pad_attribute) differ slightly from MySQL which also includes Pad_attribute |
| SHOW COLUMNS | MO SHOW COLUMNS (without FULL) already includes the Comment column; MySQL only shows Comment with FULL |
| SHOW COLUMNS | MO SHOW FULL COLUMNS returns Collation and Privileges columns but Collation is always NULL |
| SHOW COLUMNS | MO accepts EXTENDED keyword (SHOW EXTENDED COLUMNS) and FIELDS synonym (SHOW FIELDS), both returning same columns as SHOW COLUMNS |
| SHOW COLUMNS | MO Type column includes display width (e.g. INT(32)) while MySQL shows just int |
| SHOW CREATE DATABASE | Output omits CHARACTER SET, COLLATE, and ENCRYPTION clauses present in MySQL 8.0 SHOW CREATE DATABASE output |
| SHOW CREATE TABLE | Output reflects MatrixOne-specific extensions (CLUSTER BY, USING IVFFLAT/HNSW, etc.) |
| SHOW CREATE TABLE | MO output omits ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci appended by MySQL |
| SHOW CREATE VIEW | DEFINER = user clause absent from output; SQL SECURITY {DEFINER|INVOKER} is emitted |
| SHOW CREATE VIEW | MO output lacks ALGORITHM=UNDEFINED clause that MySQL always includes |
| SHOW CREATE VIEW | MO does not fully qualify column references (MySQL outputs db.table.col AS alias) |
| SHOW CREATE VIEW | MO uses unquoted identifiers; MySQL backtick-quotes database, table, and column names |
| SHOW CREATE VIEW | The rendered Create View output shows CREATE SQL SECURITY DEFINER VIEW (not CREATE ALGORITHM=UNDEFINED DEFINER=user SQL SECURITY DEFINER VIEW as MySQL does) |
| SHOW FUNCTION STATUS | Lists MatrixOne SQL/Python functions; MySQL shows stored routines AND built-in sys schema functions (e.g. extract_schema_from_file_name, format_bytes) |
| SHOW FUNCTION STATUS | MO only shows user-defined functions; MySQL shows all functions including built-in ones |
| SHOW GRANTS | Grant syntax output is completely different: MO uses MO-specific format (GRANT create account ON account, GRANT table all ON table) instead of MySQL standard format (GRANT SELECT, INSERT, UPDATE, DELETE ON .) |
| SHOW GRANTS | MO output includes backtick-quoted user@host inside grant statements; MySQL uses quoted user@host format with TO clause |
| SHOW GRANTS | USING role_list clause not supported |
| SHOW GRANTS | MO does not support SHOW GRANTS FOR CURRENT_USER (or CURRENT_USER()) as a shorthand for the current user |
| SHOW INDEX | Reflects MatrixOne index model — secondary index rows appear but may not accelerate queries |
| SHOW INDEX | Index_type may be empty (MySQL typically shows BTREE) |
| SHOW INDEX | Index_comment column is present (MySQL 8.0 also has Index_comment; difference is minor) |
| SHOW INDEX | Index_params column is present (MySQL does not have this column) |
| SHOW INDEX | Expression column shows the column name for non-functional key parts; MySQL shows NULL for non-functional key parts |
| SHOW INDEX | MO SHOW INDEX returns 16 columns vs MySQL 15 columns (MO adds Index_params, lacks the extra Collation behavior) |
| SHOW PROCESSLIST | MO returns 19 columns (node_id, conn_id, session_id, account, user, host, db, session_start, command, info, txn_id, statement_id, statement_type, query_type, sql_source_type, query_start, client_host, role, proxy_host) vs MySQL 8 columns (Id, User, Host, db, Command, Time, State, Info) |
| SHOW PROCESSLIST | MO column names differ completely: conn_id vs Id, session_start vs Time, no State column, MO adds txn_id/statement_id/statement_type/query_type/sql_source_type/query_start/client_host/role/proxy_host |
| SHOW PROCESSLIST | SHOW FULL PROCESSLIST is accepted by MO but returns same columns as SHOW PROCESSLIST (no behavioral difference) |
| SHOW TABLE STATUS | Result columns differ from MySQL: MO has 19 cols (adds Role_id, Role_name; omits Version); MySQL has 18 cols (includes Version; no Role_id/Role_name) |
| SHOW TABLE STATUS | Engine column always shows Tae instead of InnoDB |
| SHOW TABLE STATUS | MO's Auto_increment defaults to 0 (MySQL shows NULL for tables without auto-increment) |
| SHOW TABLE STATUS | MO shows views in SHOW TABLE STATUS with Engine=NULL and Comment=VIEW (same as MySQL behavior) |
| SHOW TABLES | Output column header uses lowercase database name (Tables_in_ |
| SHOW TABLES | MO does not display a parenthesized LIKE pattern in the column header unlike MySQL |
| SHOW VARIABLES | System variables are mostly syntactic stubs; actual behaviour differs from MySQL |
| SHOW VARIABLES | GLOBAL and SESSION scope modifiers are syntactically accepted for both SET and SHOW; SHOW GLOBAL vs SHOW SESSION return different values when SESSION has been overridden, same as MySQL |
| SHOW VARIABLES | MO has a completely different set of variable names (e.g. testbotchvar_nodyn, testbothvar_dyn) alongside MySQL-compatible ones (autocommit, sql_mode) |
| SHOW VARIABLES | Variable values use lowercase ('on'/'off') while MySQL uses uppercase ('ON'/'OFF') |
Data Types
| Statement | Difference from MySQL |
|---|---|
| Data Type Conversion | BOOLEAN → DECIMAL cast is not supported; other common conversions are supported |
| Data Types Overview | TIMESTAMP range is 0001-01-01 to 9999-12-31 (MySQL: 1970-01-01 to 2038-01-19) |
| Data Types Overview | DATETIME lower bound is 0001-01-01 (MySQL: 1000-01-01) |
| Data Types Overview | Non-standard type names FLOAT32/FLOAT64 in addition to MySQL's FLOAT/DOUBLE |
| Fixed-Point Types (Exact Value) - DECIMAL | DECIMAL precision supports up to 65 digits via DECIMAL256 |
| TIMESTAMP Initialization | TIMESTAMP range is 0001-9999 (MySQL 8.0: 1970-2038); auto-initialization behavior near range boundaries may differ |
| TIMESTAMP Initialization | DATETIME DEFAULT 0 is not supported (MySQL 8.0 supports it) |
| TIMESTAMP Initialization | TIMESTAMP ON UPDATE CURRENT_TIMESTAMP without explicit DEFAULT defaults to NULL (MySQL 8.0 defaults to 0) |
| TIMESTAMP Initialization | DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP without explicit DEFAULT rejects NULL insert (MySQL 8.0 defaults to 0) |
Language Structure
| Statement | Difference from MySQL |
|---|---|
| Comments | Supports // single-line comments (C++ style); MySQL 8.0 does not support // comments |
| Comments | Does not support /!.../ conditional/executable comments (MySQL 8.0 does) |
| Keywords | MatrixOne-specific keywords marked with (M) in the keyword list |
Limitations
| Statement | Difference from MySQL |
|---|---|
| Partition Support | All partition types (KEY, HASH, RANGE, LIST) are accepted syntactically but not enforced at storage or plan level; tables are created without actual partitioning |
Operators
| Statement | Difference from MySQL |
|---|---|
| CAST | CAST('non-numeric' AS SIGNED) raises an error instead of returning 0 or NULL (MySQL 8.0 returns 0 with a warning) |
| CAST | CAST(datetime_typed_value AS CHAR) may fail in some cases (MySQL 8.0 supports it universally) |
| CONVERT | CONVERT('non-numeric', SIGNED) raises an error instead of returning 0 or NULL |
| CONVERT | CONVERT(datetime_typed_value, CHAR) may fail in some cases (MySQL 8.0 supports it universally) |
| IF() | IF(NULL, expr2, expr3) raises an error instead of returning expr3 (MySQL 8.0 returns expr3) |
| INTERVAL | INTERVAL is internally implemented as a two-argument function rather than as a true SQL keyword; documented syntax INTERVAL(expr,unit) differs from MySQL's INTERVAL expr unit keyword-style notation |
| INTERVAL | Malformed dates in DATE_ADD/DATE_SUB raise errors rather than returning NULL (MySQL 8.0 returns NULL) |
Functions & Operators
| Statement | Difference from MySQL |
|---|---|
| AES_DECRYPT() | MatrixOne supports only aes-128-ecb and aes-256-cbc block modes; MySQL 8.0 also supports the full set of ECB/CBC/CFB/OFB variants at multiple key sizes. |
| AES_DECRYPT() | MatrixOne AES_DECRYPT does not accept the optional kdf_name / salt / info KDF arguments present in MySQL 8.0. |
| AES_ENCRYPT() | MatrixOne supports only aes-128-ecb and aes-256-cbc block modes; MySQL 8.0 also supports the full set of ECB/CBC/CFB/OFB variants at multiple key sizes. |
| AES_ENCRYPT() | MatrixOne AES_ENCRYPT does not accept the optional kdf_name / salt / info KDF arguments present in MySQL 8.0. |
| AVG | AVG() returns DOUBLE for all input types (MySQL returns DECIMAL for exact-value types) |
| CURDATE() | curdate()+int returns days since 1970-01-01 rather than coercing both sides to integer and adding like MySQL. |
| CURRENT_ROLE() | Returns a single active role name; MySQL 8.0 can return multiple comma-separated active roles or 'NONE'. |
| CURRENT_USER, CURRENT_USER() | The host part may be returned as 'localhost' or the resolved client host rather than MySQL's explicit 'username@host' format. |
| DATE_ADD() | Date literals accept only 'yyyy-mm-dd' and 'yyyymmdd' formats; MySQL accepts wider variants. |
| DATE_FORMAT() | Date literals accept only 'yyyy-mm-dd' and 'yyyymmdd' formats; MySQL accepts wider variants. |
| DATE_SUB() | Date literals accept only 'yyyy-mm-dd' and 'yyyymmdd' formats; MySQL accepts wider variants. |
| DATE() | Date literals accept only 'yyyy-mm-dd' and 'yyyymmdd' formats; MySQL accepts wider variants (yy-mm-dd, yy/mm/dd, yymmdd, etc.). |
| EXTRACT() | Date literals accept only 'yyyy-mm-dd' and 'yyyymmdd' formats; MySQL accepts wider variants. |
| FLOOR() | MatrixOne supports an optional second decimals argument (FLOOR(number, decimals)) to specify decimal places; MySQL 8.0 only supports the single-argument form FLOOR(X) |
| FROM_BASE64() | FROM_BASE64() may include trailing null bytes in decoded output; MySQL strips them (e.g., FROM_BASE64('YQ==') returns 'a\0\0' instead of 'a') |
| FROM_UNIXTIME() | Date literals accept only 'yyyy-mm-dd' and 'yyyymmdd' formats; MySQL accepts wider variants. |
| LOAD_FILE() | LOAD_FILE() takes a DATALINK value (file:// or stage:// URL) rather than MySQL's plain filesystem path argument |
| OCT(N) | OCT(N) returns a numeric value rather than MySQL's plain string representation |
| RAND() | RAND(seed) is not supported; calling RAND(N) with an integer argument produces ERROR 20203 |
| REGEXP_INSTR() | match_type parameter not yet supported; passing it causes ERROR 20203 |
| REGEXP_SUBSTR() | match_type parameter not yet supported; passing it causes ERROR 20203 |
| SUM | SUM() returns the input integer type rather than DECIMAL for exact-value arguments (MySQL returns DECIMAL) |
| TIMESTAMP() | MatrixOne TIMESTAMP range is '0001-01-01'–'9999-12-31' vs MySQL '1970-01-01'–'2038-01-19' (compat doc: Data Types). |
| TIMESTAMP() | Two-argument form TIMESTAMP(expr1, expr2) is not supported; MO only supports single-argument TIMESTAMP(expr) |
| TO_DAYS() | Two-digit year handling differs: MatrixOne completes '08-10-07' to year 0008; MySQL interprets it as 2008. |
| TO_DAYS() | Dates '0000-00-00' and '0000-01-01' raise an error in MatrixOne rather than being accepted as MySQL does. |
| TO_SECONDS() | Two-digit year handling differs: MatrixOne completes '08-10-07' to year 0008; MySQL interprets it as 2008. |
| TO_SECONDS() | Dates '0000-00-00' and '0000-01-01' raise an error in MatrixOne rather than being accepted as MySQL does. |
| UNIX_TIMESTAMP() | Date literals accept only 'yyyy-mm-dd' and 'yyyymmdd' formats; MySQL accepts wider variants. |
| YEAR() | Date literals accept only 'yyyy-mm-dd' and 'yyyymmdd' formats; MySQL accepts wider variants. |
System Variables
| Statement | Difference from MySQL |
|---|---|
| Foreign Key Checks | With foreign_key_checks=0, dropping a parent table deletes and re-establishes the foreign key relationship when the parent table is rebuilt; MySQL 8.0 preserves FK metadata |
| Illegal Login Restrictions | connection_control_failed_connections_threshold and connection_control_max_connection_delay require the Connection-Control plugin in MySQL 8.0 but are built-in in MatrixOne |
| Server System Variables | Many system variables are syntactic stubs that do not change actual behavior; only a subset of MySQL system variables are functional |
| SQL Mode | Only ONLY_FULL_GROUP_BY mode is functional; all other SQL modes are accepted syntactically but have no effect |
| Time Zone Support | Named time zones (e.g., 'America/New_York', 'UTC') are not supported; only (+/-)HH:MM offset format is accepted for time_zone |