Virtual column expressions in Maria are either more relaxed or more restrictive compared to other db products

[Philip Stoev]

Computed/Virtual columns in Microsoft SQL Server are subject to numerous constraints that are treated at length in the documentation

http://msdn.microsoft.com/en-us/library/ms189292.aspx#BKMK_persisted

The virtual column implementation in Maria does not enforce the following restrictions that are present in MS SQL Server:

- Maria allows server variables in virtual column expressions , including @@warning_count and others that change dynamically;
- Maria allows user variables in virtual column expressions
- Maria allows CONVERT_TZ() with a named time zone as an argument, even though time zone names and time offsets are configurable;
- Maria allows DATE_FORMAT() is allowed even though month names are language-dependent;
- Maria allows CAST() to non-unicode character sets, even though character sets are configurable and differ between binaries/versions;
- Maria allows FLOAT expressions in virtual columns, which SQL servers considers "imprecise" due to potential cross-platform differences in floating-point implementation and precision;
- SQL Server requires ARITHABORT mode to be set, so that division by zero returns an error, and not a NULL;
- SQL Server requires QUOTED_IDENTIFIER SQL mode to be set. In MariaDB, data inserted under different settings of ANSI_QUOTES will be processed and stored differently in a virtual column that contains quoted identifiers;
- Maria does not allow user-defined functions, even those flagged as DETERMINISTIC;

Microsoft SQL Server enforces the restrictions by refusing to create virtual columns, refusing to allow updates to a table containing them, and, finally, refusing to use an index over such a column if it can not be guaranteed that the virtual expression is fully deterministic.

What Maria can do is store the SQL mode, language, etc. that was in effect during the CREATE TABLE so that the virtual column expression is always be evaluated under that original SQL mode regardless of future changes to the mode.

[Philip Stoev (pstoev-askmonty)]

Henrik Ingo also reports that MariaDB does not allow ExtractValue() to be used in virtual column expressions.