MariaDB ColumnStore Limitations

ColumnSore has several limitations that make some MariaDB features unavailable. Some of these limitations are design choices, others might be unimplemented features or bugs. If a limitation is a problem for you, you can open a feature request in MariaDB JIRA, in the ColumnStore (MCOL) project.

• Indexes are not supported or necessary for performance. See Query Execution.
• Primary keys, UNIQUE keys and foreign keys are also not supported. This means that unicity constraints and referential integrity are not supported.
• Features based on indexes are not supportedd:
  • FULLTEXT;
  • SPATIAL;
  • Foreign keys.
• Table partitioning is not supported.
• Generated columns are not supported.
• ColumnStore tables cannot be temporal tables.
• ColumnStore only supports the utf8mb3 character set with the default collation.
• Invisible columns are not supported.
• A column DEFAULT value can only be a constant value. See MCOL-5665.
• The FIRST and AFTER syntaxes, to indicate the physical position of a column, does not make sense for a columnar engine and is not support by ColumnStore.

The following MariaDB types are not supported by ColumnStore:

• JSON
• Geometry types
• UUID
• INET4
• INET6

All other MariaDB types are supported.

INTEGER types modifiers have these limitations:

• Numeric columns can be signed or unsigned, but the SIGNED keyword is not understood. (MCOL-5690)
• ZEROFILL is not supported.

• CREATE OR REPLACE TABLE drops the existing table, and won't recreate it if we're trying to recreate it as a ColumnStore table. See MCOL-5674.
• Online ALTER TABLE. Clauses ALGORITHM, LOCK and ONLINE cannot be specified.
• ALTER TABLE cannot perform multiple changes. For example you can't add two columns or drop two columns with a single statement.
• ALTER TABLE does not support any modification to a column, including the CHANGE, MODIFY and RENAME operations.
• ALTER TABLE clauses don't support IF EXISTS and IF NOT EXISTS with ColumnStore, i.e: ALTER TABLE … ADD COLUMN IF NOT EXISTS.
• CHECK TABLE and REPAIR TABLE.
• ALTER TABLE FORCE and OPTIMIZE TABLE, though the latter won't return an error.
• WAIT and NOWAIT, see MCOL-5662.
• RENAME TABLE cannot be used to move a table across databases.

• INSERT DELAYED.
• INSERT IGNORE. This is relevant because CHECK constraints are supported.
• While RETURNING generally works, DELETE RETURNING currently doesn't return the deleted rows. See MCOL-5664.
• DELETE and UPDATE don't support the ORDER BY clause.

Non-standard behaviour:

• Any write acquires a lock on the target table.
• SELECTs with LOCK IN SHARE MODE or FOR UPDATE don't acquire any locks.
• Isolation levels are irrelevant. A ColumnStore transaction always behaves as a REPEATABLE READ transaction.

Unsupported features:

• Savepoints.
• XA transactions;
• LOCK TABLES.

• EXPLAIN will always return an empty row for ColumnStore tables, except that the select_type column is set to PUSHED SELECT. However ColumnStore has other ways to show how a query is executed, see Query Processing.
• Window functions on a sufficiently big set of rows produce an error.
• Regardless the sql_mode, ColumnStore behaves as if the ONLY_FULL_GROUP_BY flag was specified. The error message is different.
• The WITH TIES clause is accepted, but it has no effect.
• <=>, the NULL-safe equal operator, is not supported. See MCOL-5675.
• Row subqueries, AKA row constructors, are supported but only with the =, <>, IN and NOT IN operators.
• UNION, INTERSECT and EXCEPT followed by VALUES currently crash MariaDB. See MCOL-5703.
• Some functions are not supported. This includes CHR(), CHAR() and ORD().

Note that version 23.10 of the storage engine is not compatible with MariaDB 11.10.

• WITH ROLLUP. Where unsupported, it causes this error message, but the mentioned bug is deleted or not visible:
  ERROR 1178 (42000): The storage engine for the table doesn't support MCS-1014: Rollup is currently not supported.
• Queries against ColumnStore tables always behaved as is the sql_mode flag EMPTY_STRING_IS_NULL was enabled. This had some unexpected consequences, for example when a column was compared to an empty string.

If you hit a ColumnStore limitations, you will probably see an error similar to this (copied from the mariadb client):

  ERROR 1178 (42000): The storage engine for the table doesn't support MCS-2029: Lock table command is currently not supported in Columnstore.

It's important to be able to understand errors. The elements of this error are:

• Error code: a number that identifies a MariaDB error, in this case 1178.
• SQLstate: a standard alphanumerical code to identify errors regardless the technology in use. “42000” is used for custom errors, not defined by MariaDB itself (but possibly defined by ColumnStore or another storage engine).
• An error message.

Note that error messages could be composed in this way:

• A first message generated by MariaDB.
• A ColumnStore error code, starting with “MCS”.
• An error message generated by ColumnStore.