[Maria-developers] Reqeust for SHOW FULL FIELDS FROM and virtual tables in MariaDB
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. Peter Webyog
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.
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.
Hi!
"Peter" == Peter Laursen <peter_laursen@webyog.com> writes:
Peter> I have a few more points to consider: Peter> CREATE TABLE table1 (as before); Peter> SET SQL_MODE = 'strict_all_tables'; Peter> INSERT INTO `test`.`table1`(`a`,`b`,`c`,`d`) VALUES ( '1','a',NULL,NULL); -- Peter> success Peter> SHOW WARNINGS -- empty set Peter> -- this is actually good, even a little inconsistent with what comes next. Peter> But GRID-type GUI clients could have difficulties working with virtual Peter> colums if it behaved differently. Peter> UPDATE `test`.`table1` SET `d`='b' WHERE `a`='1' AND `b`='a' AND `c`='1' AND Peter> `d`='a'; Peter> SHOW WARNINGS; -- 1647 The value specified for computed column 'd' in table Peter> 'table1' ignored Peter> -- In strict mode we should have an error and not a warning here, I think. Peter> INSERT INTO `test`.`table1`(`a`,`b`,`c`,`d`) VALUES ( '2','b','3','d'); Peter> -- again two warnings (not errors) here for 3rd and 4th column's INSERT Peter> -- In summary Peter> -- I like that it is not returning warnings or errors for values Peter> INSERTED/UPDATED to NULL. Peter> -- but in strict mode I think errors whoud be returned where warnings Peter> currently are. Virtual columns should be sql_mode-aware'. Agree. Will be fixed in 5.3. Regards, Monty
Hi!
"Peter" == Peter Laursen <peter_laursen@webyog.com> writes:
Peter> Refer http://kb.askmonty.org/v/virtual-columns - the example Peter> CREATE TABLE table1 ( Peter> a INT NOT NULL, Peter> b VARCHAR(32), Peter> c INT AS (a MOD 10) virtual, Peter> d VARCHAR(5) AS (LEFT(b,5)) persistent); Peter> now Peter> SHOW FULL FIELDS FROM table1; Peter> Field Type Collation Null Key Default Extra Privileges Comment Peter> ------ ----------- ----------------- ------ ------ ------- ------- Peter> ------------------------------- ------- Peter> a int(11) (NULL) NO (NULL) select,insert,update,references Peter> b varchar(32) latin1_swedish_ci YES (NULL) select,insert,update,references Peter> c int(11) (NULL) YES (NULL) VIRTUAL select,insert,update,references Peter> d varchar(5) latin1_swedish_ci YES (NULL) VIRTUAL Peter> select,insert,update,references Peter> I request that 'extra' column should return either "PERSISTENT" or " Peter> VIRTUAL, PERSISTENT" for column `d`. The reason is that the output from Peter> SHOW FULL FIELDS returns an *array* (that will not need to be parsed) as Peter> opposite to SHOW CREATE TABLE. Accordingly SHOW FULL FIELDS is much easier Peter> to use for an 'unintelligent client' as opposed to an 'intelligent (or Peter> ignorant for that sake :-) ) human user to decide on special properties for Peter> a column. Agree; I will fix this for 5.3 Regards, Monty
participants (3)
-
Arjen Lentz -
Michael Widenius -
Peter Laursen