Observations about MariaDB’s Virtual Columns

I have played a bit with MariaDB’s Virtual Columns. The documentation seems to be incomplete, especially it does not list all limitations. I edited the page, and I hope that what I written is correct. In this post, I want to list everything I’ve found: I didn’t write some of these things in the wiki, because I’m not sure if they are bugs, or because I’m not sure they are worth being written there.

If there are errors (or you know about other limitations) please add a comment, and I will fix this post ASAP.

Enjoy!

• Virtual Columns can only be used with Storage Engine which supports them. The only official Storage Engines which support Virtual Columns are: InnoDB, Aria, MyISAM.
• All datatypes can be used for Virtual Columns. They can be Zerofill. But you can’t specify NULL/NOT NULL. Of course, no DEFAULT value (the default value is the result of the SQL expression). Comments are ok.
• You can’t use Virtual Columns in Triggers because of this bug. Should be solved in the next version of each tree (see bug page).
• INSERT statements should use the SET clause or include all Virtual Columns – assign DEFAULT values explicitly. Of coure you can not modify a value with UPDATE.
• Virtual Columns expressions can only use non-Virtual columns from the same table, operators and built-in deterministic functions. Everything else is not allowed. Constant values (including not-null-column IS NULL) are not allowed.
• • Please, note that if you don’t write correctly a function’s name, you will see this error: `Function or expression is not allowed for column ‘v2’`. The fuction you tried to use may be ok, so check if you written correctly its name.
• MODIFY and CHANGE operations in ALTER TABLE are not supported for Virtual Columns. You need to DROP + ADD the column. This is always true, even if you are not changing anything.
• The only way to get a Virtual Column’s expression is perform a SHOW CREATE TABLE and parse its output (maybe not so hard, but not convenient).
• Administrative commands seem to work correctly. CHECKSUM counts stored columns, and does not count non-stored columns – this is correct. ALTER TABLE ORDER BY works.

Foreign Keys

Still I did not test exensively FOREIGN KEYs, but here’s what I’ve found out.

• All columns in FOREIGN KEYs, or referred by FOREIGN KEYs, can be Persistent. Non-stored columns can’t be used (this seems to me consistent, as referred columns need to be indexed, and a foreign key itself is an index).
• ON UPDATE CASCADE, ON UPDATE SET NULL and ON DELETE SET NULL are not allowed (you will receive a clear error message, so I’m sure this is not a bug; also, a computed value can not be modified).
  (I’ve noticed that SET DEFAULT fails with a generic error 150, but this is not related to Virtual Columns, and I tried this just because this clause was supported by PBXT.)
• In my tests, all contraints were enforced as expected. All error messages were ok.
• I combined virtual/non-virtual FOREIGN KEYs columns, and referred columns in several ways: everything worked good.