Fixed that SHOW COLUMNS for a virtual persistent column shows 'PERSISTENT' instead of 'VIRTUAL'

Strict mode now gives error if one tries to update a virtual column.

mysql-test/suite/vcol/r/vcol_column_def_options_innodb.result:
  Updated test results
mysql-test/suite/vcol/r/vcol_column_def_options_myisam.result:
  Updated test results
mysql-test/suite/vcol/r/vcol_keys_innodb.result:
  Updated test results
mysql-test/suite/vcol/r/vcol_keys_myisam.result:
  Updated test results
mysql-test/suite/vcol/r/vcol_misc.result:
  Added new test for 'show columns' and error handling when trying to update a virtual column.
mysql-test/suite/vcol/t/vcol_misc.test:
  Added new test for 'show columns' and error handling when trying to update a virtual column.
sql/sql_base.cc:
  Strict mode now gives error if one tries to update a virtual column.
sql/sql_show.cc:
  Show PERSISTENT instead of VIRTUAL for persistent columns.

6 files changed, 72 insertions, 10 deletions

diff --git a/mysql-test/suite/vcol/r/vcol_column_def_options_innodb.result b/mysql-test/suite/vcol/r/vcol_column_def_options_innodb.result
index b1f96f8f4d9..db16d25000a 100644
--- a/mysql-test/suite/vcol/r/vcol_column_def_options_innodb.result
+++ b/mysql-test/suite/vcol/r/vcol_column_def_options_innodb.result
@@ -114,7 +114,7 @@ t1	CREATE TABLE `t1` (
 describe t1;
 Field	Type	Null	Key	Default	Extra
 a	int(11)	YES		NULL	
-b	int(11)	YES		NULL	VIRTUAL
+b	int(11)	YES		NULL	PERSISTENT
 insert into t1 (a) values (1);
 select * from t1;
 a	b
diff --git a/mysql-test/suite/vcol/r/vcol_column_def_options_myisam.result b/mysql-test/suite/vcol/r/vcol_column_def_options_myisam.result
index 9fde339cb06..1b4a5060e40 100644
--- a/mysql-test/suite/vcol/r/vcol_column_def_options_myisam.result
+++ b/mysql-test/suite/vcol/r/vcol_column_def_options_myisam.result
@@ -114,7 +114,7 @@ t1	CREATE TABLE `t1` (
 describe t1;
 Field	Type	Null	Key	Default	Extra
 a	int(11)	YES		NULL	
-b	int(11)	YES		NULL	VIRTUAL
+b	int(11)	YES		NULL	PERSISTENT
 insert into t1 (a) values (1);
 select * from t1;
 a	b
diff --git a/mysql-test/suite/vcol/r/vcol_keys_innodb.result b/mysql-test/suite/vcol/r/vcol_keys_innodb.result
index 857dcb8423f..5070981f08f 100644
--- a/mysql-test/suite/vcol/r/vcol_keys_innodb.result
+++ b/mysql-test/suite/vcol/r/vcol_keys_innodb.result
@@ -19,7 +19,7 @@ t1	CREATE TABLE `t1` (
 describe t1;
 Field	Type	Null	Key	Default	Extra
 a	int(11)	YES		NULL	
-b	int(11)	YES	UNI	NULL	VIRTUAL
+b	int(11)	YES	UNI	NULL	PERSISTENT
 drop table t1;
 create table t1 (a int, b int as (a*2), unique key (b));
 ERROR HY000: Key/Index cannot be defined on a non-stored computed column
@@ -34,7 +34,7 @@ t1	CREATE TABLE `t1` (
 describe t1;
 Field	Type	Null	Key	Default	Extra
 a	int(11)	YES		NULL	
-b	int(11)	YES	UNI	NULL	VIRTUAL
+b	int(11)	YES	UNI	NULL	PERSISTENT
 drop table t1;
 create table t1 (a int, b int as (a*2));
 alter table t1 add unique key (b);
@@ -64,7 +64,7 @@ t1	CREATE TABLE `t1` (
 describe t1;
 Field	Type	Null	Key	Default	Extra
 a	int(11)	YES		NULL	
-b	int(11)	YES	MUL	NULL	VIRTUAL
+b	int(11)	YES	MUL	NULL	PERSISTENT
 drop table t1;
 create table t1 (a int, b int as (a*2) persistent, index (a,b));
 show create table t1;
@@ -77,7 +77,7 @@ t1	CREATE TABLE `t1` (
 describe t1;
 Field	Type	Null	Key	Default	Extra
 a	int(11)	YES	MUL	NULL	
-b	int(11)	YES		NULL	VIRTUAL
+b	int(11)	YES		NULL	PERSISTENT
 drop table t1;
 create table t1 (a int, b int as (a*2));
 alter table t1 add index (b);
diff --git a/mysql-test/suite/vcol/r/vcol_keys_myisam.result b/mysql-test/suite/vcol/r/vcol_keys_myisam.result
index af0935affbc..71d74a6c977 100644
--- a/mysql-test/suite/vcol/r/vcol_keys_myisam.result
+++ b/mysql-test/suite/vcol/r/vcol_keys_myisam.result
@@ -19,7 +19,7 @@ t1	CREATE TABLE `t1` (
 describe t1;
 Field	Type	Null	Key	Default	Extra
 a	int(11)	YES		NULL	
-b	int(11)	YES	UNI	NULL	VIRTUAL
+b	int(11)	YES	UNI	NULL	PERSISTENT
 drop table t1;
 create table t1 (a int, b int as (a*2), unique key (b));
 ERROR HY000: Key/Index cannot be defined on a non-stored computed column
@@ -34,7 +34,7 @@ t1	CREATE TABLE `t1` (
 describe t1;
 Field	Type	Null	Key	Default	Extra
 a	int(11)	YES		NULL	
-b	int(11)	YES	UNI	NULL	VIRTUAL
+b	int(11)	YES	UNI	NULL	PERSISTENT
 drop table t1;
 create table t1 (a int, b int as (a*2));
 alter table t1 add unique key (b);
@@ -64,7 +64,7 @@ t1	CREATE TABLE `t1` (
 describe t1;
 Field	Type	Null	Key	Default	Extra
 a	int(11)	YES		NULL	
-b	int(11)	YES	MUL	NULL	VIRTUAL
+b	int(11)	YES	MUL	NULL	PERSISTENT
 drop table t1;
 create table t1 (a int, b int as (a*2) persistent, index (a,b));
 show create table t1;
@@ -77,7 +77,7 @@ t1	CREATE TABLE `t1` (
 describe t1;
 Field	Type	Null	Key	Default	Extra
 a	int(11)	YES	MUL	NULL	
-b	int(11)	YES		NULL	VIRTUAL
+b	int(11)	YES		NULL	PERSISTENT
 drop table t1;
 create table t1 (a int, b int as (a*2));
 alter table t1 add index (b);
diff --git a/mysql-test/suite/vcol/r/vcol_misc.result b/mysql-test/suite/vcol/r/vcol_misc.result
index da4306e8088..6ae10d805db 100644
--- a/mysql-test/suite/vcol/r/vcol_misc.result
+++ b/mysql-test/suite/vcol/r/vcol_misc.result
@@ -203,3 +203,42 @@ a	b
 1	1
 3	0
 drop table t1;
+CREATE TABLE `t1` (
+`a` int(11) NOT NULL,
+`b` varchar(32) DEFAULT NULL,
+`c` int(11) AS (a MOD 10) VIRTUAL,
+`d` varchar(5) AS (LEFT(b,5)) PERSISTENT
+) ENGINE=MyISAM;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL,
+  `b` varchar(32) DEFAULT NULL,
+  `c` int(11) AS (a MOD 10) VIRTUAL,
+  `d` varchar(5) AS (LEFT(b,5)) PERSISTENT
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+show columns from t1;
+Field	Type	Null	Key	Default	Extra
+a	int(11)	NO		NULL	
+b	varchar(32)	YES		NULL	
+c	int(11)	YES		NULL	VIRTUAL
+d	varchar(5)	YES		NULL	PERSISTENT
+show full columns from t1;
+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	PERSISTENT	select,insert,update,references	
+INSERT INTO `test`.`t1`(`a`,`b`,`c`,`d`) VALUES ( '1','a',NULL,NULL);
+UPDATE `test`.`t1` SET `d`='b' WHERE `a`='1' AND `b`='a' AND `c`='1' AND  `d`='a';
+Warnings:
+Warning	1906	The value specified for computed column 'd' in table 't1' ignored
+INSERT INTO `test`.`t1`(`a`,`b`,`c`,`d`) VALUES ( '1','a',NULL,'a');
+Warnings:
+Warning	1906	The value specified for computed column 'd' in table 't1' ignored
+set sql_mode='strict_all_tables';
+UPDATE `test`.`t1` SET `d`='b' WHERE `a`='1' AND `b`='a' AND `c`='1' AND  `d`='a';
+ERROR HY000: The value specified for computed column 'd' in table 't1' ignored
+INSERT INTO `test`.`t1`(`a`,`b`,`c`,`d`) VALUES ( '1','a',NULL,'a');
+ERROR HY000: The value specified for computed column 'd' in table 't1' ignored
+drop table t1;
diff --git a/mysql-test/suite/vcol/t/vcol_misc.test b/mysql-test/suite/vcol/t/vcol_misc.test
index eb8c5791f54..68cfca32904 100644
--- a/mysql-test/suite/vcol/t/vcol_misc.test
+++ b/mysql-test/suite/vcol/t/vcol_misc.test
@@ -182,3 +182,26 @@ insert into t1 (a) values (1),(3);
 select * from t1;
 select * from t1;
 drop table t1;
+
+#
+# Test output of show columns
+#
+
+CREATE TABLE `t1` (
+  `a` int(11) NOT NULL,
+  `b` varchar(32) DEFAULT NULL,
+  `c` int(11) AS (a MOD 10) VIRTUAL,
+  `d` varchar(5) AS (LEFT(b,5)) PERSISTENT
+) ENGINE=MyISAM;
+show create table t1;
+show columns from t1;
+show full columns from t1;
+INSERT INTO `test`.`t1`(`a`,`b`,`c`,`d`) VALUES ( '1','a',NULL,NULL);
+UPDATE `test`.`t1` SET `d`='b' WHERE `a`='1' AND `b`='a' AND `c`='1' AND  `d`='a';
+INSERT INTO `test`.`t1`(`a`,`b`,`c`,`d`) VALUES ( '1','a',NULL,'a');
+set sql_mode='strict_all_tables';
+--error ER_WARNING_NON_DEFAULT_VALUE_FOR_VIRTUAL_COLUMN
+UPDATE `test`.`t1` SET `d`='b' WHERE `a`='1' AND `b`='a' AND `c`='1' AND  `d`='a';
+--error ER_WARNING_NON_DEFAULT_VALUE_FOR_VIRTUAL_COLUMN
+INSERT INTO `test`.`t1`(`a`,`b`,`c`,`d`) VALUES ( '1','a',NULL,'a');
+drop table t1;