
 -- CHAR -> VARCHAR or CHAR -> TEXT can't be persistent or indexed:
 vtxt VARCHAR(5) AS (txt) PERSISTENT
);

CREATE TABLE bad_sub (
 num1 BIGINT UNSIGNED,
 num2 BIGINT UNSIGNED,
 -- The resulting value can vary for some large values
 vnum BIGINT AS (num1 - num2) VIRTUAL,
 KEY(vnum)
);

The warnings for the above tables look like this:

Warning (Code 1901): Function or expression '`txt`' cannot be used in the
GENERATED ALWAYS AS clause of `vtxt`
Warning (Code 1105): Expression depends on the @@sql_mode value
PAD_CHAR_TO_FULL_LENGTH

Warning (Code 1901): Function or expression '`num1` - `num2`' cannot be used
in the GENERATED ALWAYS AS clause of `vnum`
Warning (Code 1105): Expression depends on the @@sql_mode value
NO_UNSIGNED_SUBTRACTION

To work around the issue, force the padding or type to make the generated
column's expression return a consistent value. For example:

CREATE TABLE good_pad (
 txt CHAR(5),
 -- Using RTRIM() or RPAD() makes the value consistent:
 vtxt VARCHAR(5) AS (RTRIM(txt)) PERSISTENT,
 -- When not persistent or indexed, it is OK for the value to vary by mode:
 vtxt2 VARCHAR(5) AS (txt) VIRTUAL,
 -- CHAR -> CHAR is always OK:
 txt2 CHAR(5) AS (txt) PERSISTENT
);

CREATE TABLE good_sub (
 num1 BIGINT UNSIGNED,
 num2 BIGINT UNSIGNED,
 -- The indexed value will always be consistent in this expression:
 vnum BIGINT AS (CAST(num1 AS SIGNED) - CAST(num2 AS SIGNED)) VIRTUAL,
 KEY(vnum)
);

MySQL Compatibility Support
---------------------------

* The STORED keyword is supported as an alias for the PERSISTENT keyword.

* Tables created with MySQL 5.7 or later that contain MySQL's generated
columns can be imported into MariaDB without a dump and restore.

Implementation Differences
--------------------------

Generated columns are subject to various constraints in other DBMSs that are
not present in MariaDB's implementation. Generated columns may also be called
computed columns or virtual columns in different implementations. The various
details for a specific implementation can be found in the documentation for
each specific DBMS.

Implementation Differences Compared to Microsoft SQL Server
-----------------------------------------------------------

MariaDB's generated columns implementation does not enforce the following
restrictions that are present in Microsoft SQL Server's computed columns
implementation:

* MariaDB allows server variables in generated column expressions, including
those that change dynamically, such as warning_count.
* MariaDB allows the CONVERT_TZ() function to be called with a named time zone
as an argument, even though time zone names and time offsets are configurable.
* MariaDB allows the CAST() function to be used with non-unicode character
sets, even though character sets are configurable and differ between
binaries/versions.
* MariaDB allows FLOAT expressions to be used in generated columns. Microsoft
SQL Server considers these expressions to be "imprecise" due to potential
cross-platform differences in floating-point implementations and precision.
* Microsoft SQL Server requires the ARITHABORT mode to be set, so that
division by zero returns an error, and not a NULL.
* Microsoft SQL Server requires QUOTED_IDENTIFIER to be set in sql_mode. In
MariaDB, if data is inserted without ANSI_QUOTES set in sql_mode, then it will
be processed and stored differently in a generated column that contains quoted
identifiers.

Microsoft SQL Server enforces the above restrictions by doing one of the
following things:

* Refusing to create computed columns.
* Refusing to allow updates to a table containing them.
* Refusing to use an index over such a column if it can not be guaranteed that
the expression is fully deterministic.

In MariaDB, as long as the sql_mode, language, and other settings that were in
effect during the CREATE TABLE remain unchanged, the generated column
expression will always be evaluated the same. If any of these things change,
then please be aware that the generated column expression might not be
evaluated the same way as it previously was.

If you try to update a virtual column, you will get an error if the default
strict mode is enabled in sql_mode, or a warning otherwise.

Development History
-------------------

Generated columns was originally developed by Andrey Zhakov. It was then
modified by Sanja Byelkin and Igor Babaev at Monty Program for inclusion in
MariaDB. Monty did the work on MariaDB 10.2 to lift a some of the old
limitations.

Examples
--------

Here is an example table that uses both VIRTUAL and PERSISTENT virtual columns:

USE TEST;

CREATE TABLE table1 (
  a INT NOT NULL,
  b VARCHAR(32),
  c INT AS (a mod 10) VIRTUAL,
  d VARCHAR(5) AS (left(b,5)) PERSISTENT);

If you describe the table, you can easily see which columns are virtual by
looking in the "Extra" column:

DESCRIBE table1;
+-------+-------------+------+-----+---------+------------+
| Field | Type        | Null | Key | Default | Extra      |
+-------+-------------+------+-----+---------+------------+
| a     | int(11)     | NO   |     | NULL    |            |
| b     | varchar(32) | YES  |     | NULL    |            |
| c     | int(11)     | YES  |     | NULL    | VIRTUAL    |
| d     | varchar(5)  | YES  |     | NULL    | PERSISTENT |
+-------+-------------+------+-----+---------+------------+

To find out what function(s) generate the value of the virtual column you can
use SHOW CREATE TABLE:
