maria-discuss team mailing list archive

[Peter Laursen <peter_laursen@xxxxxxxxxx>]

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@xxxxxxxxxxxxx> wrote:

> Hi Peter, all
>
> > From: "Peter Laursen" <peter_laursen@xxxxxxxxxx>
> >
> > 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.
>