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.