MariaDB ColumnStore SQL limitations

MariaDB ColumnStore is an extremely fast and scalable solution for analytics built on MariaDB, which Vettabase supports. MariaDB ColumnStore inherits the MariaDB SQL dialect, and many MariaDB features. However, numerous MariaDB features are not available for the ColumnStore storage engine. In some cases the reason is that they don’t apply to a columnar storage. Some features were not yet implemented, and probably the team decided to leave out some other features.

In this article I want to highlight the MariaDB features that you shouldn’t expect to find in ColumnStore. This is not necessarily a problem, but correct expectations need to be set when we approach a new technology.

Table and column features

The following table characteristics are supported by regular MariaDB, but not for ColumnStore tables.

Indexes

Indexes are not supported, and not necessary to make ColumnStore queries fast.

There is no PRIMARY KEY and there are no UNIQUE indexes.

Since indexes are not used, ColumnStore has no FULLTEXT or SPATIAL capabilities, including geometric types.

Other table features

The following table and column features are also not supported:

• Table partitioning is not supported, though every column is stored separately and partitioned.
• Temporal tables.
• Generated columns.
• Character sets. The only supported character set is utf8mb3.
• Invisible columns.
• DEFAULT clauses can only specify constant values (MCOL-5665). For example, DEFAULT col * 2 is not admitted.
• FIRST and AFTER column specifiers. They are not meaningful for columnar storage.

Compression is supported globally. InnoDB compression syntax won’t work.

Types

These data types are not supported with ColumnStore.

JSON

Use TEXT instead. Not well-formed JSON will be accepted, so make sure you validate your JSON documents before inserting them if necessary.

Geospatial types

GEOMETRY and its subtypes are not accepted. You can use the standard GeoJSON format, MariaDB WBT format, or MariaDB WKB format. For text formats you can use the TEXT type, and for binary formats you can use BLOB.

You can also create a table of coordinates, stored as FLOATs. In this way you will be able to make any search. However, ColumnStore won’t be able to run spatial queries efficiently.

UUID

You can store UUIDs as BINARY strings. A similar operation is described in MariaDB/MySQL: Working with MD5 or other hashes.

INET4 and INET6

IPs version 4 can be stored as BINARY(4), and IPs version 6 can be stored as BINARY(16).

INTEGER limitations

INTEGER types are supported with the following minor limitations:

• The SIGNED keyword is not supported. However, integers are signed by default.
• ZEROFILL is not supported.

DDL statements: CREATE, ALTER, DROP

Important: CREATE OR REPLACE TABLE drops the existing table, and won’t recreate it if we’re trying to recreate it as a ColumnStore table. I reported this bug as MCOL-5674.

Also, DDL statements have the following limitations:

• Online ALTER TABLE. The ALGORITHM, LOCK, and ONLINE clauses result in an error.
• Instant column creation or deletion is not supported.
• ALTER TABLE cannot perform multiple changes. For example, if you want to add two columns, you need to run two separate ALTER TABLEs.
• 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. For example, these won’t work:
  • CREATE TABLE IF NOT EXISTS ...;
  • DROP TABLE IF EXISTS ...;
  • ALTER TABLE … ADD COLUMN IF NOT EXISTS.
• ALTER TABLE FORCE and OPTIMIZE TABLE, though the latter won’t return an error. If really needed, you can backup, drop, and restore a table.
• WAIT and NOWAIT. Interestingly, MariaDB waits until a lock is released if required, but then it responds with an error. I reported this bug as MCOL-5662.
• RENAME TABLE works but cannot be used to move a table across databases.
• CHECK TABLE and REPAIR TABLE.

DML (INSERT, DELETE, UPDATE)

The following features are not supported:

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

Transactions and Locks

Unsupported features:

• Transaction savepoints;
• XA transactions;
• LOCK TABLES.

Differences between ColumnStore and InnoDB:

• 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 not enforced. A ColumnStore transaction always behaves as a REPEATABLE READ transaction.

SELECT

Missing features and differences that affect the SELECT statements:

• The EXPLAIN output is designed to show a typical MariaDB query execution. ColumnStore uses different strategies. EXPLAIN will always return an empty row for ColumnStore tables, except that the select_type column is set to PUSHED SELECT.
• 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 has no effect.
• <=>, the NULL-safe equal operator, is not supported. I reported this bug as 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. I reported this bug as MCOL-5703.
• Some functions are not supported. This includes CHR(), CHAR() and ORD().

Implemented in ColumnStore 23.10

ColumnStore 23.10 is included in MariaDB 11.4, but it is not compatible with MariaDB 11.10. Therefore, the following features are not available with MariaDB versions older than 11.4.

• GROUP BY ... 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.
  You can use SUM() as a window function instead.
• Queries against ColumnStore tables always behaved as if 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.

ColumnStore errors for unsupported features

If you hit a ColumnStore limitation, you will probably see an error similar to this:

  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.

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

Overall, MariaDB Columnstore is a fantastic technology for the right use cases. It’s built on MariaDB, but it’s important to know that some MariaDB features are not available, because they don’t apply to a columnar store, because they’re not relevant for ColumnStore use cases, or for other reasons. If you are using MariaDB for your project, you can benefit greatly from attending my 1-day training on MariaDB ColumnStore for Data Engineers and DBAs with 30-day chat support. The training is available at just 100 GBP if you apply the promo code FEDERICO while registering on Eventbrite.

Federico Razzoli