Comments - Generated (Virtual and Persistent/Stored) Columns

This article quotes some restrictions existing in other DBMSs but does not say that MariaDB has important limitations not existing in other products. For instances the SQL Server computed columns are described as:

A computed column is computed from an expression that can use other columns in the same table. The expression can be a noncomputed column name, constant, function, and any combination of these connected by one or more operators. The expression cannot be a subquery.

Which means that MS computed columns have not some major MariaDB virtual column restrictions:

1. Their value can be a constant.
2. Their value can be a not deterministic function, except if use in an index or a constraint.
3. There is apparently no limitation on the length of the expression.

All these limitation are bad and really limit the interest of virtual columns. Limitation on constant and expression length are not justifiable.

About the limitation on deterministic expressions, this is simply the necessity of this feature that is questionnable. Indeed, a feature is necessary if it enables to do things that cannot be done by other mean. For instance:

create table emp2 (
id int(6) NOT NULL ,
name char(12) NOT NULL,
birth date NOT NULL,
hired date NOT NULL,
agehired int(3) as (timestampdiff(year,birth,hired)) virtual);

select * from emp2;

id  name    birth       hired       agehired
 1  John    1986-01-25  2010-06-02        24
 2  Henry   1987-06-07  2008-04-01        20
 3  George  1981-08-10  2010-06-02        28
 4  Sam     1979-11-22  2007-10-10        27
 5  James   1992-05-13  2009-12-14        17
 6  Bill    1986-09-11  2008-02-10        21
 7  Mat     1975-07-17  2011-05-15        35

This is Ok, however the same result can be obtained with the table:

create table emp1 (
id int(6) NOT NULL ,
name char(12) NOT NULL,
birth date NOT NULL,
hired date NOT NULL);

with the query:

select *, timestampdiff(year,birth,hired) agehired from emp1;

Therefore, virtual columns appears as just a query wording facility without bringing a real new concept.

Things would be different if non deterministic expressions would be accepted. Firstly it would make possible to add useful things such as an AGE column returning the same result than saying:

select *, timestampdiff(year,birth,curdate()) age from emp1;

But even more, it would make possible to do things that are not possible by other means:

create table randx (
row int key auto_increment);
insert into randx values(default),(default),(default),(default);

A query such as:

select row, rand() from randx where rand() < 0.5;

can return result such as:

row rand()
1   0.3253647185166969000000000000000
2   0.5974436510330473100000000000000
3   0.4231471795804306900000000000000

This apparently strange result comes from the fact that the rand() function is called on each appearance in the query. However, if it were possible to do;

create table randx (
row int key auto_increment,
val double as (rand()) virtual);

Providing that a column value is evaluated only once per row, as many DBMSs do, the query:

select row, val from randx where val < 0.5;

would never return rows with val >= 0.5.

Of course restrictions such as not using this virtual column for indexing or constraint would apply.

"Their value can be a not deterministic function, except if use in an index or a constraint."

In 10.1.6 (at least), you can use non-deterministic functions, but apparently the 252 character limitation still exists.

I'd show you, but I'm frustrated with the UI here. How did you get the <pre></pre> to work in your comment?

Let's try some <code>:

ALTER TABLE `z_test`
    ADD `Last_name_bit_length` INT(5)
        AS (BIT_LENGTH(`Name_Last`)) PERSISTENT
        AFTER `Name_Last`;

MariaDB [EcoReality]> SELECT Name_Last, Last_name_bit_length from z_test;
+------------+----------------------+
| Name_Last  | Last_name_bit_length |
+------------+----------------------+
| Bartfast   |                   64 |
| Flintstone |                   80 |
| Huston     |                   48 |
| Bah        |                   24 |
+------------+----------------------+
4 rows in set (0.00 sec)

Ah, that's better. :-)

(You guys REALLY need an "Edit" button...)