I have a few more points to consider: 

CREATE TABLE table1 (as before);

SET SQL_MODE = 'strict_all_tables';

INSERT INTO `test`.`table1`(`a`,`b`,`c`,`d`) VALUES ( '1','a',NULL,NULL); -- success
SHOW WARNINGS -- empty set
-- this is actually good, even a little inconsistent with what comes next. But GRID-type GUI clients could have difficulties working with virtual colums if it behaved differently.

UPDATE `test`.`table1` SET `d`='b' WHERE `a`='1' AND `b`='a' AND `c`='1' AND `d`='a';
SHOW WARNINGS; -- 1647 The value specified for computed column 'd' in table 'table1' ignored
-- In strict mode we should have an error and not a warning here, I think.

INSERT INTO `test`.`table1`(`a`,`b`,`c`,`d`) VALUES ( '2','b','3','d');
-- again two warnings (not errors) here for 3rd and 4th column's INSERT

-- In summary
-- I like that it is not returning warnings or errors for values INSERTED/UPDATED to NULL.
-- but in strict mode I think errors whoud be returned where warnings currently are. Virtual columns should be sql_mode-aware'. 

-- Peter


On Fri, Jun 3, 2011 at 05:24, Arjen Lentz <arjen@openquery.com> wrote:
Hi Peter, all

> From: "Peter Laursen" <peter_laursen@webyog.com>
>
> Refer http://kb.askmonty.org/v/virtual-columns - the example
>
> 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);
>
> now
>
> SHOW FULL FIELDS FROM table1;
>
> Field Type Collation Null Key Default Extra Privileges Comment
> ------ ----------- ----------------- ------ ------ ------- -------
> ------------------------------- -------
> a int(11) (NULL) NO (NULL) select,insert,update,references
> b varchar(32) latin1_swedish_ci YES (NULL)
> select,insert,update,references
> c int(11) (NULL) YES (NULL) VIRTUAL select,insert,update,references
> d varchar(5) latin1_swedish_ci YES (NULL) VIRTUAL
> select,insert,update,references
>
> I request that 'extra' column should return either "PERSISTENT" or "
> VIRTUAL, PERSISTENT" for column `d`. The reason is that the output
> from SHOW FULL FIELDS returns an *array* (that will not need to be
> parsed) as opposite to SHOW CREATE TABLE. Accordingly SHOW FULL
> FIELDS is much easier to use for an 'unintelligent client' as
> opposed to an 'intelligent (or ignorant for that sake :-) ) human
> user to decide on special properties for a column.

I'm for this.
Perhaps use BASE rather than PERSISTENT, in line with views & base tables - since it's similar stuff.
And of course SHOW FULL TABLES will show "BASE TABLE" and "VIEW" already.

Cheers,
Arjen.