Syntax
------

<type>  [GENERATED ALWAYS]  AS   ( <expression> )
[VIRTUAL | PERSISTENT | STORED]  [UNIQUE] [UNIQUE KEY] [COMMENT <text>]

MariaDB's generated columns syntax is designed to be similar to the syntax for
Microsoft SQL Server's computed columns and Oracle Database's virtual columns.
In MariaDB 10.2 and later, the syntax is also compatible with the syntax for
MySQL's generated columns.

Description
-----------

A generated column is a column in a table that cannot explicitly be set to a
specific value in a DML query. Instead, its value is automatically generated
based on an expression. This expression might generate the value based on the
values of other columns in the table, or it might generate the value by
calling built-in functions or user-defined functions (UDFs).

There are two types of generated columns:

* PERSISTENT (a.k.a. STORED): This type's value is actually stored in the
table.
* VIRTUAL: This type's value is not stored at all. Instead, the value is
generated dynamically when the table is queried. This type is the default.

Generated columns are also sometimes called computed columns or virtual
columns.

Supported Features
------------------

Storage Engine Support
----------------------

* Generated columns can only be used with storage engines which support them.
If you try to use a storage engine that does not support them, then you will
see an error similar to the following:

ERROR 1910 (HY000): TokuDB storage engine does not support computed columns

* InnoDB, Aria, MyISAM and CONNECT support generated columns.

* A column in a MERGE table can be built on a PERSISTENT generated column.
However, a column in a MERGE table can not be defined as a VIRTUAL and
PERSISTENT generated column.

Data Type Support
-----------------

* All data types are supported when defining generated columns.

* Using the ZEROFILL column option is supported when defining generated
columns.

* Using the AUTO_INCREMENT column option is not supported when defining
generated columns. Until MariaDB 10.2.25, it was supported, but this support
was removed, because it would not work correctly. See MDEV-11117.

Index Support
-------------

* Using a generated column as a table's primary key is not supported. See
MDEV-5590 for more information. If you try to use one as a primary key, then
you will see an error similar to the following:

ERROR 1903 (HY000): Primary key cannot be defined upon a computed column

* Using PERSISTENT generated columns as part of a foreign key is supported.

* Referencing PERSISTENT generated columns as part of a foreign key is also
supported.
However, using the ON UPDATE CASCADE, ON UPDATE SET NULL, or ON DELETE SET
NULL clauses is not supported. If you try to use an unsupported clause, then
you will see an error similar to the following:

ERROR 1905 (HY000): Cannot define foreign key with ON UPDATE SET NULL clause
on a computed column

* Defining indexes on both VIRTUAL and PERSISTENT generated columns is
supported.
If an index is defined on a generated column, then the optimizer considers
using it in the same way as indexes based on "real" columns.

Statement Support
-----------------

* Generated columns are used in DML queries just as if they were "real"
columns.
However, VIRTUAL and PERSISTENT generated columns differ in how their data is
stored.
Values for PERSISTENT generated columns are generated whenever a DML queries
inserts or updates the row with the special DEFAULT value. This generates the
columns value, and it is stored in the table like the other "real" columns.
This value can be read by other DML queries just like the other "real" columns.
Values for VIRTUAL generated columns are not stored in the table. Instead, the
value is generated dynamically whenever the column is queried. If other
columns in a row are queried, but the VIRTUAL generated column is not one of
the queried columns, then the column's value is not generated.

* The SELECT statement supports generated columns.

* Generated columns can be referenced in the INSERT, UPDATE, and DELETE
statements.
However, VIRTUAL or PERSISTENT generated columns cannot be explicitly set to
any other values than NULL or DEFAULT. If a generated column is explicitly set
to any other value, then the outcome depends on whether strict mode is enabled
in sql_mode. If it is not enabled, then a warning will be raised and the
default generated value will be used instead. If it is enabled, then an error
will be raised instead.

* The CREATE TABLE statement has limited support for generated columns.
It supports defining generated columns in a new table.
It supports using generated columns to partition tables.
It does not support using the versioning clauses with generated columns.

* The ALTER TABLE statement has limited support for generated columns.
It supports the MODIFY and CHANGE clauses for PERSISTENT generated columns.
It does not support the MODIFY clause for VIRTUAL generated columns if
ALGORITHM is not set to COPY. See MDEV-15476 for more information.
It does not support the  CHANGE clause for VIRTUAL generated columns if
ALGORITHM is not set to COPY. See MDEV-17035 for more information.
It does not support altering a table if ALGORITHM is not set to COPY if the
table has a VIRTUAL generated column that is indexed. See MDEV-14046 for more
information.
It does not support adding a VIRTUAL generated column with the ADD clause if