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 <[email protected]> wrote: > Hi Peter, all > > > From: "Peter Laursen" <[email protected]> > > > > 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. >
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
