MDEV-7563 Support CHECK constraint as in (or close to) SQL Standard

MDEV-10134 Add full support for DEFAULT

- Added support for using tables with MySQL 5.7 virtual fields,
  including MySQL 5.7 syntax
- Better error messages also for old cases
- CREATE ... SELECT now also updates timestamp columns
- Blob can now have default values
- Added new system variable "check_constraint_checks", to turn of
  CHECK constraint checking if needed.
- Removed some engine independent tests in suite vcol to only test myisam
- Moved some tests from 'include' to 't'. Should some day be done for all tests.
- FRM version increased to 11 if one uses virtual fields or constraints
- Changed to use a bitmap to check if a field has got a value, instead of
  setting HAS_EXPLICIT_VALUE bit in field flags
- Expressions can now be up to 65K in total
- Ensure we are not refering to uninitialized fields when handling virtual fields or defaults
- Changed check_vcol_func_processor() to return a bitmap of used types
- Had to change some functions that calculated cached value in fix_fields to do
  this in val() or getdate() instead.
- store_now_in_TIME() now takes a THD argument
- fill_record() now updates default values
- Add a lookahead for NOT NULL, to be able to handle DEFAULT 1+1 NOT NULL
- Automatically generate a name for constraints that doesn't have a name
- Added support for ALTER TABLE DROP CONSTRAINT
- Ensure that partition functions register virtual fields used. This fixes
  some bugs when using virtual fields in a partitioning function

21 files changed, 700 insertions, 4057 deletions

diff --git a/mysql-test/suite/vcol/inc/vcol_blocked_sql_funcs_main.inc b/mysql-test/suite/vcol/inc/vcol_blocked_sql_funcs_main.inc
deleted file mode 100644
index 571fe75560b..00000000000
--- a/mysql-test/suite/vcol/inc/vcol_blocked_sql_funcs_main.inc
+++ /dev/null
@@ -1,360 +0,0 @@
-################################################################################
-# inc/vcol_blocked_sql_funcs_main.inc                                          #
-#                                                                              #
-# Purpose:                                                                     #
-#  Tests around sql functions                                                  #
-#                                                                              #
-#                                                                              #
-#------------------------------------------------------------------------------#
-# Original Author: Andrey Zhakov                                               #
-# Original Date: 2008-08-31                                                    #
-# Change Author: Oleksandr Byelkin (Monty program Ab)
-# Date: 2009-03-24 
-# Change: Syntax changed
-################################################################################
-
-#
-# NOTE:  All SQL functions should be rejected, otherwise BUG.
-#
-
---echo # RAND()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (b double as (rand()));
-
---echo # LOAD_FILE()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a varchar(64), b varchar(1024) as (load_file(a)));
-
---echo # CURDATE()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a datetime as (curdate()));
-
---echo # CURRENT_DATE(), CURRENT_DATE
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a datetime as (current_date));
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a datetime as (current_date()));
-
---echo # CURRENT_TIME(), CURRENT_TIME
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a datetime as (current_time));
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a datetime as (current_time()));
-
---echo # CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a datetime as (current_timestamp()));
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a datetime as (current_timestamp));
-
---echo # CURTIME()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a datetime as (curtime()));
-
---echo # LOCALTIME(), LOCALTIME
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a datetime, b varchar(10) as (localtime())); 
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a datetime, b varchar(10) as (localtime)); 
-
---echo # LOCALTIMESTAMP, LOCALTIMESTAMP()(v4.0.6)
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a datetime, b varchar(10) as (localtimestamp()));
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a datetime, b varchar(10) as (localtimestamp));
-
---echo # NOW()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a datetime, b varchar(10) as (now()));
-
---echo # SYSDATE()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a int, b varchar(10) as (sysdate()));
-
---echo # UNIX_TIMESTAMP()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a datetime, b datetime as (unix_timestamp()));
-
---echo # UTC_DATE()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a datetime, b datetime as (utc_date()));
-
---echo # UTC_TIME()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a datetime, b datetime as (utc_time()));
-
---echo # UTC_TIMESTAMP()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a datetime, b datetime as (utc_timestamp()));
-
---echo # WEEK() - one argument version
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a datetime, b datetime as (week(a)));
-
---echo # MATCH()
-if (!$skip_full_text_checks)
-{
-  -- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-  create table t1 (a varchar(32), b bool as (match a against ('sample text')));
-}
-
---echo # BENCHMARK()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a varchar(1024), b varchar(1024) as (benchmark(a,3)));
-
---echo # CHARSET()
--- error ER_CONST_EXPR_IN_VCOL
-create table t1 (a varchar(64), b varchar(64) as (charset(a)));
-
---echo # COERCIBILITY()
--- error ER_CONST_EXPR_IN_VCOL
-create table t1 (a varchar(64), b int as (coercibility(a)));
-
---echo # COLLATION()
--- error ER_CONST_EXPR_IN_VCOL
-create table t1 (a varchar(64), b varchar(64) as (collation(a)));
-
---echo # CONNECTION_ID()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a int as (connection_id()));
-
---echo # CURRENT_USER(), CURRENT_USER
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a varchar(32) as (current_user()));
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a varchar(32) as (current_user));
-
---echo # DATABASE()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a varchar(1024), b varchar(1024) as (database()));
-
---echo # FOUND_ROWS()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a varchar(1024), b varchar(1024) as (found_rows()));
-
---echo # GET_LOCK()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a varchar(1024), b varchar(1024) as (get_lock(a,10)));
-
---echo # IS_FREE_LOCK()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a varchar(1024), b varchar(1024) as (is_free_lock(a)));
-
---echo # IS_USED_LOCK()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a varchar(1024), b varchar(1024) as (is_used_lock(a)));
-
---echo # LAST_INSERT_ID()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a int as (last_insert_id()));
-
---echo # MASTER_POS_WAIT()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a varchar(32), b int as (master_pos_wait(a,0,2)));
-
---echo # NAME_CONST()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a varchar(32) as (name_const('test',1)));
-
---echo # RELEASE_LOCK()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a varchar(32), b int as (release_lock(a)));
-
---echo # ROW_COUNT()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a int as (row_count()));
-
---echo # SCHEMA()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a varchar(32) as (schema()));
-
---echo # SESSION_USER()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a varchar(32) as (session_user()));
-
---echo # SLEEP()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a int, b int as (sleep(a)));
-
---echo # SYSTEM_USER()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a varchar(32) as (system_user()));
-
---echo # USER()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a varchar(1024), b varchar(1024) as (user()));
-
---echo # UUID_SHORT()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a varchar(1024) as (uuid_short()));
-
---echo # UUID()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a varchar(1024) as (uuid()));
-
---echo # VALUES()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a varchar(1024), b varchar(1024) as (values(a)));
-
---echo # VERSION()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a varchar(1024), b varchar(1024) as (version())); 
-
---echo # ENCRYPT()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a varchar(1024), b varchar(1024) as (encrypt(a)));
-
---echo # Stored procedures
-
-delimiter //;
-create procedure p1()
-begin
-  select current_user();
-end //
-
-create function f1()
-returns int
-begin
- return 1;
-end //
-
-delimiter ;//
-
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a int as (p1()));
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a int as (f1()));
-
-drop procedure p1;
-drop function f1;
-
---echo # Unknown functions
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a int as (f1()));
-
---echo #
---echo # GROUP BY FUNCTIONS
---echo #
-
---echo # AVG()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a int, b int as (avg(a)));
-
---echo # BIT_AND()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a int, b int as (bit_and(a)));
-
---echo # BIT_OR()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a int, b int as (bit_or(a)));
-
---echo # BIT_XOR()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a int, b int as (bit_xor(a)));
-
---echo # COUNT(DISTINCT)
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a int, b int as (count(distinct a)));
-
---echo # COUNT()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a int, b int as (count(a)));
-
---echo # GROUP_CONCAT()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a varchar(32), b int as (group_concat(a,'')));
-
---echo # MAX()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a int, b int as (max(a)));
-
---echo # MIN()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a int, b int as (min(a)));
-
---echo # STD()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a int, b int as (std(a)));
-
---echo # STDDEV_POP()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a int, b int as (stddev_pop(a)));
-
---echo # STDDEV_SAMP()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a int, b int as (stddev_samp(a)));
-
---echo # STDDEV()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a int, b int as (stddev(a)));
-
---echo # SUM()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a int, b int as (sum(a)));
-
---echo # VAR_POP()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a int, b int as (var_pop(a)));
-
---echo # VAR_SAMP()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a int, b int as (var_samp(a)));
-
---echo # VARIANCE()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a int, b int as (variance(a)));
-
---echo #
---echo # XML FUNCTIONS
---echo #
-
---echo # ExtractValue()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a varchar(1024), b varchar(1024) as (ExtractValue(a,'//b[$@j]')));
-
---echo # UpdateXML()
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a varchar(1024), b varchar(1024) as (UpdateXML(a,'/a','<e>fff</e>')));
-
---echo #
---echo # Sub-selects
---echo #
-
-create table t1 (a int);
--- error ER_PARSE_ERROR
-create table t2 (a int, b int as (select count(*) from t1));
-drop table t1;
-
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a int, b int as ((select 1)));
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a int, b int as (a+(select 1)));
-
---echo #
---echo # SP functions
---echo #
-
---disable_warnings
-drop function if exists sub1;
---enable_warnings
-create function sub1(i int) returns int deterministic
-  return i+1;
-select sub1(1);
--- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t1 (a int, b int as (a+sub3(1)));
-drop function sub1;
-
---echo #
---echo # Long expression
-
-let $tmp_long_string = `SELECT repeat('a',240)`;
-eval create table t1 (a int, b varchar(300) as (concat(a,'$tmp_long_string')));
-drop table t1;
-let $tmp_long_string = `SELECT repeat('a',243)`;
---error ER_WRONG_STRING_LENGTH
-eval create table t1 (a int, b varchar(300) as (concat(a,'$tmp_long_string')));
-
---echo #
---echo # Constant expression
---error ER_CONST_EXPR_IN_VCOL
-create table t1 (a int as (PI()));
diff --git a/mysql-test/suite/vcol/inc/vcol_partition.inc b/mysql-test/suite/vcol/inc/vcol_partition.inc
index c20dfaaa2a4..b2c0c90ff69 100644
--- a/mysql-test/suite/vcol/inc/vcol_partition.inc
+++ b/mysql-test/suite/vcol/inc/vcol_partition.inc
@@ -34,6 +34,7 @@ PARTITION BY RANGE( b ) (
 insert into t1 values ('2006-01-01',default);
 insert into t1 values ('2007-01-01',default);
 insert into t1 values ('2005-01-01',default);
+insert into t1 (a) values ('2007-01-02');
 select * from t1;
 
 select partition_name,table_rows,data_length from information_schema.partitions where table_name = 't1';
diff --git a/mysql-test/suite/vcol/r/not_supported.result b/mysql-test/suite/vcol/r/not_supported.result
index 251fdaad101..eecc7d63c17 100644
--- a/mysql-test/suite/vcol/r/not_supported.result
+++ b/mysql-test/suite/vcol/r/not_supported.result
@@ -4,32 +4,28 @@ set time_zone='+10:00';
 set div_precision_increment=20;
 create table t1 (a int, b int, v decimal(20,19) as (a/3));
 create table t2 (a int, b int, v int as (a+@a));
-ERROR HY000: Function or expression is not allowed for column 'v'
-create table t3 (a int, b int, v int as (a+@@error_count));
-ERROR HY000: Function or expression is not allowed for column 'v'
+ERROR HY000: Function or expression 'user_var' is not allowed for 'VIRTUAL' of column/constraint 'v'
+create table t2 (a int, b int, v int as (a+@a) PERSISTENT);
+ERROR HY000: Function or expression 'user_var' is not allowed for 'VIRTUAL' of column/constraint 'v'
+create table t3_ok (a int, b int, v int as (a+@@error_count));
+create table t3 (a int, b int, v int as (a+@@error_count) PERSISTENT);
+ERROR HY000: Function or expression 'get_system_var' is not allowed for 'VIRTUAL' of column/constraint 'v'
 create table t4 (a int, b int, v int as (@a:=a));
-ERROR HY000: Function or expression is not allowed for column 'v'
+ERROR HY000: Function or expression 'user_var' is not allowed for 'VIRTUAL' of column/constraint 'v'
+create table t4 (a int, b int, v int as (@a:=a) PERSISTENT);
+ERROR HY000: Function or expression 'user_var' is not allowed for 'VIRTUAL' of column/constraint 'v'
 create table t5 (a int, b int, v varchar(100) as (monthname(a)));
+ERROR HY000: Function or expression 'monthname' is not allowed for 'VIRTUAL' of column/constraint 'v'
 create table t6 (a int, b int, v varchar(100) as (dayname(a)));
+ERROR HY000: Function or expression 'dayname' is not allowed for 'VIRTUAL' of column/constraint 'v'
 create table t7 (a int, b int, v varchar(100) as (date_format(a, '%W %a %M %b')));
+ERROR HY000: Function or expression 'date_format' is not allowed for 'VIRTUAL' of column/constraint 'v'
 create table t8 (a int, b int, v varchar(100) as (from_unixtime(a)));
 insert t1 (a,b) values (1,2);
-insert t5 (a,b) values (20141010,2);
-insert t6 (a,b) values (20141010,2);
-insert t7 (a,b) values (20141010,2);
 insert t8 (a,b) values (1234567890,2);
 select * from t1;
 a	b	v
 1	2	0.3333333333333333333
-select * from t5;
-a	b	v
-20141010	2	octubre
-select * from t6;
-a	b	v
-20141010	2	viernes
-select * from t7;
-a	b	v
-20141010	2	viernes vie octubre oct
 select * from t8;
 a	b	v
 1234567890	2	2009-02-14 09:31:30
@@ -39,15 +35,6 @@ set time_zone='+1:00';
 select * from t1;
 a	b	v
 1	2	0.3333333333333333333
-select * from t5;
-a	b	v
-20141010	2	octubre
-select * from t6;
-a	b	v
-20141010	2	viernes
-select * from t7;
-a	b	v
-20141010	2	viernes vie octubre oct
 select * from t8;
 a	b	v
 1234567890	2	2009-02-14 09:31:30
@@ -55,16 +42,7 @@ flush tables;
 select * from t1;
 a	b	v
 1	2	0.3333333330000000000
-select * from t5;
-a	b	v
-20141010	2	October
-select * from t6;
-a	b	v
-20141010	2	Friday
-select * from t7;
-a	b	v
-20141010	2	Friday Fri October Oct
 select * from t8;
 a	b	v
 1234567890	2	2009-02-14 00:31:30
-drop table t1, t5, t6, t7, t8;
+drop table t1, t3_ok, t8;
diff --git a/mysql-test/suite/vcol/r/vcol_blocked_sql_funcs.result b/mysql-test/suite/vcol/r/vcol_blocked_sql_funcs.result
new file mode 100644
index 00000000000..09d220f9eae
--- /dev/null
+++ b/mysql-test/suite/vcol/r/vcol_blocked_sql_funcs.result
@@ -0,0 +1,268 @@
+SET @@session.storage_engine = 'MyISAM';
+# RAND()
+create or replace table t1 (b double as (rand()));
+create or replace table t1 (b double as (rand()) PERSISTENT);
+ERROR HY000: Function or expression 'rand' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# LOAD_FILE()
+create or replace table t1 (a varchar(64), b varchar(1024) as (load_file(a)));
+ERROR HY000: Function or expression 'load_file' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# CURDATE()
+create or replace table t1 (a datetime as (curdate()) PERSISTENT);
+ERROR HY000: Function or expression 'curdate' is not allowed for 'VIRTUAL' of column/constraint 'a'
+# CURRENT_DATE(), CURRENT_DATE
+create or replace table t1 (a datetime as (current_date) PERSISTENT);
+ERROR HY000: Function or expression 'curdate' is not allowed for 'VIRTUAL' of column/constraint 'a'
+create or replace table t1 (a datetime as (current_date()) PERSISTENT);
+ERROR HY000: Function or expression 'curdate' is not allowed for 'VIRTUAL' of column/constraint 'a'
+# CURRENT_TIME(), CURRENT_TIME
+create or replace table t1 (a datetime as (current_time) PERSISTENT);
+ERROR HY000: Function or expression 'curtime' is not allowed for 'VIRTUAL' of column/constraint 'a'
+create or replace table t1 (a datetime as (current_time()) PERSISTENT);
+ERROR HY000: Function or expression 'curtime' is not allowed for 'VIRTUAL' of column/constraint 'a'
+# CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP
+create or replace table t1 (a datetime as (current_timestamp()) PERSISTENT);
+ERROR HY000: Function or expression 'now' is not allowed for 'VIRTUAL' of column/constraint 'a'
+create or replace table t1 (a datetime as (current_timestamp) PERSISTENT);
+ERROR HY000: Function or expression 'now' is not allowed for 'VIRTUAL' of column/constraint 'a'
+# CURTIME()
+create or replace table t1 (a datetime as (curtime()) PERSISTENT);
+ERROR HY000: Function or expression 'curtime' is not allowed for 'VIRTUAL' of column/constraint 'a'
+# LOCALTIME(), LOCALTIME
+create or replace table t1 (a datetime, b varchar(10) as (localtime()) PERSISTENT);
+ERROR HY000: Function or expression 'now' is not allowed for 'VIRTUAL' of column/constraint 'b'
+create or replace table t1 (a datetime, b varchar(10) as (localtime) PERSISTENT);
+ERROR HY000: Function or expression 'now' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# LOCALTIMESTAMP, LOCALTIMESTAMP()(v4.0.6)
+create or replace table t1 (a datetime, b varchar(10) as (localtimestamp()) PERSISTENT);
+ERROR HY000: Function or expression 'now' is not allowed for 'VIRTUAL' of column/constraint 'b'
+create or replace table t1 (a datetime, b varchar(10) as (localtimestamp) PERSISTENT);
+ERROR HY000: Function or expression 'now' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# NOW()
+create or replace table t1 (a datetime, b varchar(10) as (now()) PERSISTENT);
+ERROR HY000: Function or expression 'now' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# SYSDATE()
+create or replace table t1 (a int, b varchar(10) as (sysdate()) PERSISTENT);
+ERROR HY000: Function or expression 'sysdate' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# UNIX_TIMESTAMP()
+create or replace table t1 (a datetime, b datetime as (unix_timestamp()) PERSISTENT);
+ERROR HY000: Function or expression 'unix_timestamp' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# UTC_DATE()
+create or replace table t1 (a datetime, b datetime as (utc_date()) PERSISTENT);
+ERROR HY000: Function or expression 'utc_date' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# UTC_TIME()
+create or replace table t1 (a datetime, b datetime as (utc_time()) PERSISTENT);
+ERROR HY000: Function or expression 'utc_time' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# UTC_TIMESTAMP()
+create or replace table t1 (a datetime, b datetime as (utc_timestamp()) PERSISTENT);
+ERROR HY000: Function or expression 'utc_timestamp' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# WEEK() - one argument version
+create or replace table t1 (a datetime, b datetime as (week(a)) PERSISTENT);
+ERROR HY000: Function or expression 'get_system_var' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# MATCH()
+create or replace table t1 (a varchar(32), b bool as (match a against ('sample text')) PERSISTENT);
+ERROR HY000: Function or expression 'match' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# BENCHMARK()
+create or replace table t1 (a varchar(1024), b varchar(1024) as (benchmark(a,3)));
+ERROR HY000: Function or expression 'benchmark' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# CHARSET()
+create or replace table t1 (a varchar(64), b varchar(64) as (charset(a)) PERSISTENT);
+# COERCIBILITY()
+create or replace table t1 (a varchar(64), b int as (coercibility(a)) PERSISTENT);
+# COLLATION()
+create or replace table t1 (a varchar(64), b varchar(64) as (collation(a)) PERSISTENT);
+# CONNECTION_ID()
+create or replace table t1 (a int as (connection_id()));
+create or replace table t1 (a int as (connection_id()) PERSISTENT);
+ERROR HY000: Function or expression 'connection_id' is not allowed for 'VIRTUAL' of column/constraint 'a'
+# CURRENT_USER(), CURRENT_USER
+create or replace table t1 (a varchar(32) as (current_user()));
+ERROR HY000: Function or expression 'current_user()' is not allowed for 'VIRTUAL' of column/constraint 'a'
+create or replace table t1 (a varchar(32) as (current_user()) PERSISTENT);
+ERROR HY000: Function or expression 'current_user()' is not allowed for 'VIRTUAL' of column/constraint 'a'
+create or replace table t1 (a varchar(32) as (current_user) PERSISTENT);
+ERROR HY000: Function or expression 'current_user()' is not allowed for 'VIRTUAL' of column/constraint 'a'
+# DATABASE()
+create or replace table t1 (a varchar(32) as (database()));
+create or replace table t1 (a varchar(1024), b varchar(1024) as (database()) PERSISTENT);
+ERROR HY000: Function or expression 'database()' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# FOUND_ROWS()
+create or replace table t1 (a varchar(1024), b varchar(1024) as (found_rows()));
+ERROR HY000: Function or expression 'found_rows' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# GET_LOCK()
+create or replace table t1 (a varchar(1024), b varchar(1024) as (get_lock(a,10)));
+ERROR HY000: Function or expression 'get_lock' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# IS_FREE_LOCK()
+create or replace table t1 (a varchar(1024), b varchar(1024) as (is_free_lock(a)));
+ERROR HY000: Function or expression 'is_free_lock' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# IS_USED_LOCK()
+create or replace table t1 (a varchar(1024), b varchar(1024) as (is_used_lock(a)));
+ERROR HY000: Function or expression 'is_used_lock' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# LAST_INSERT_ID()
+create or replace table t1 (a int as (last_insert_id()));
+ERROR HY000: Function or expression 'last_insert_id' is not allowed for 'VIRTUAL' of column/constraint 'a'
+# MASTER_POS_WAIT()
+create or replace table t1 (a varchar(32), b int as (master_pos_wait(a,0,2)));
+ERROR HY000: Function or expression 'master_pos_wait' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# NAME_CONST()
+create or replace table t1 (a varchar(32) as (name_const('test',1)));
+ERROR HY000: Function or expression 'name_const' is not allowed for 'VIRTUAL' of column/constraint 'a'
+# RELEASE_LOCK()
+create or replace table t1 (a varchar(32), b int as (release_lock(a)));
+ERROR HY000: Function or expression 'release_lock' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# ROW_COUNT()
+create or replace table t1 (a int as (row_count()));
+ERROR HY000: Function or expression 'row_count' is not allowed for 'VIRTUAL' of column/constraint 'a'
+# SCHEMA()
+create or replace table t1 (a varchar(32) as (schema()) PERSISTENT);
+ERROR HY000: Function or expression 'database()' is not allowed for 'VIRTUAL' of column/constraint 'a'
+# SESSION_USER()
+create or replace table t1 (a varchar(32) as (session_user()) PERSISTENT);
+ERROR HY000: Function or expression 'user()' is not allowed for 'VIRTUAL' of column/constraint 'a'
+# SLEEP()
+create or replace table t1 (a int, b int as (sleep(a)));
+ERROR HY000: Function or expression 'sleep' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# SYSTEM_USER()
+create or replace table t1 (a varchar(32) as (system_user()) PERSISTENT);
+ERROR HY000: Function or expression 'user()' is not allowed for 'VIRTUAL' of column/constraint 'a'
+# USER()
+create or replace table t1 (a varchar(1024), b varchar(1024) as (user()) PERSISTENT);
+ERROR HY000: Function or expression 'user()' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# UUID_SHORT()
+create or replace table t1 (a varchar(1024) as (uuid_short()) PERSISTENT);
+ERROR HY000: Function or expression 'uuid_short' is not allowed for 'VIRTUAL' of column/constraint 'a'
+# UUID()
+create or replace table t1 (a varchar(1024) as (uuid()) PERSISTENT);
+ERROR HY000: Function or expression 'uuid' is not allowed for 'VIRTUAL' of column/constraint 'a'
+# VALUES()
+create or replace table t1 (a varchar(1024), b varchar(1024) as (values(a)));
+ERROR HY000: Function or expression 'values' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# VERSION()
+create or replace table t1 (a varchar(1024), b varchar(1024) as (version()) PERSISTENT);
+ERROR HY000: Function or expression 'version()' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# ENCRYPT()
+create or replace table t1 (a varchar(1024), b varchar(1024) as (encrypt(a)) PERSISTENT);
+# DATE_FORMAT()
+create or replace table t1 (a datetime, b varchar(64) as (date_format(a,'%W %M %D'));
+ERROR HY000: Function or expression 'date_format' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# Stored procedures
+create procedure p1()
+begin
+select current_user();
+end //
+create function f1()
+returns int
+begin
+return 1;
+end //
+create or replace table t1 (a int as (p1()) PERSISTENT);
+ERROR HY000: Function or expression '`p1`' is not allowed for 'VIRTUAL' of column/constraint 'a'
+create or replace table t1 (a int as (f1()) PERSISTENT);
+ERROR HY000: Function or expression '`f1`' is not allowed for 'VIRTUAL' of column/constraint 'a'
+drop procedure p1;
+drop function f1;
+# Unknown functions
+create or replace table t1 (a int as (f1()) PERSISTENT);
+ERROR HY000: Function or expression '`f1`' is not allowed for 'VIRTUAL' of column/constraint 'a'
+#
+# GROUP BY FUNCTIONS
+#
+# AVG()
+create or replace table t1 (a int, b int as (avg(a)));
+ERROR HY000: Function or expression 'avg(' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# BIT_AND()
+create or replace table t1 (a int, b int as (bit_and(a)));
+ERROR HY000: Function or expression 'bit_and(' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# BIT_OR()
+create or replace table t1 (a int, b int as (bit_or(a)));
+ERROR HY000: Function or expression 'bit_or(' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# BIT_XOR()
+create or replace table t1 (a int, b int as (bit_xor(a)));
+ERROR HY000: Function or expression 'bit_xor(' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# COUNT(DISTINCT)
+create or replace table t1 (a int, b int as (count(distinct a)));
+ERROR HY000: Function or expression 'count(distinct ' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# COUNT()
+create or replace table t1 (a int, b int as (count(a)));
+ERROR HY000: Function or expression 'count(' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# GROUP_CONCAT()
+create or replace table t1 (a varchar(32), b int as (group_concat(a,'')));
+ERROR HY000: Function or expression 'group_concat' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# MAX()
+create or replace table t1 (a int, b int as (max(a)));
+ERROR HY000: Function or expression 'max(' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# MIN()
+create or replace table t1 (a int, b int as (min(a)));
+ERROR HY000: Function or expression 'min(' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# STD()
+create or replace table t1 (a int, b int as (std(a)));
+ERROR HY000: Function or expression 'std(' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# STDDEV_POP()
+create or replace table t1 (a int, b int as (stddev_pop(a)));
+ERROR HY000: Function or expression 'std(' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# STDDEV_SAMP()
+create or replace table t1 (a int, b int as (stddev_samp(a)));
+ERROR HY000: Function or expression 'std(' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# STDDEV()
+create or replace table t1 (a int, b int as (stddev(a)));
+ERROR HY000: Function or expression 'std(' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# SUM()
+create or replace table t1 (a int, b int as (sum(a)));
+ERROR HY000: Function or expression 'sum(' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# VAR_POP()
+create or replace table t1 (a int, b int as (var_pop(a)));
+ERROR HY000: Function or expression 'variance(' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# VAR_SAMP()
+create or replace table t1 (a int, b int as (var_samp(a)));
+ERROR HY000: Function or expression 'var_samp(' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# VARIANCE()
+create or replace table t1 (a int, b int as (variance(a)));
+ERROR HY000: Function or expression 'variance(' is not allowed for 'VIRTUAL' of column/constraint 'b'
+# DAYNAME()
+create or replace table t1 (a int, b varchar(10) as (dayname(a)));
+ERROR HY000: Function or expression 'dayname' is not allowed for 'VIRTUAL' of column/constraint 'b'
+create or replace table t1 (a int, b varchar(10) as (monthname(a)));
+ERROR HY000: Function or expression 'monthname' is not allowed for 'VIRTUAL' of column/constraint 'b'
+create or replace table t1 (a int, b varchar(10) as (date_format("1963-01-01","%d.%m.%Y")));
+ERROR HY000: Function or expression 'date_format' is not allowed for 'VIRTUAL' of column/constraint 'b'
+create or replace table t1 (a int, b varchar(10) as (time_format(now(),"%d.%m.%Y")));
+ERROR HY000: Function or expression 'time_format' is not allowed for 'VIRTUAL' of column/constraint 'b'
+#
+# XML FUNCTIONS
+#
+# ExtractValue()
+create or replace table t1 (a varchar(1024), b varchar(1024) as (ExtractValue(a,'//b[$@j]')) PERSISTENT);
+# UpdateXML()
+create or replace table t1 (a varchar(1024), b varchar(1024) as (UpdateXML(a,'/a','<e>fff</e>')) PERSISTENT);
+#
+# Sub-selects
+#
+create or replace table t1 (a int);
+create or replace table t2 (a int, b int as (select count(*) from t1));
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'select count(*) from t1))' at line 1
+drop table t1;
+create or replace table t1 (a int, b int as ((select 1)));
+ERROR HY000: Function or expression 'subselect' is not allowed for 'VIRTUAL' of column/constraint 'b'
+create or replace table t1 (a int, b int as (a+(select 1)));
+ERROR HY000: Function or expression 'subselect' is not allowed for 'VIRTUAL' of column/constraint 'b'
+#
+# SP functions
+#
+drop function if exists sub1;
+create function sub1(i int) returns int deterministic
+return i+1;
+select sub1(1);
+sub1(1)
+2
+create or replace table t1 (a int, b int as (a+sub3(1)));
+ERROR HY000: Function or expression '`sub3`' is not allowed for 'VIRTUAL' of column/constraint 'b'
+drop function sub1;
+#
+# Long expression
+create or replace table t1 (a int, b varchar(300) as (concat(a,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')));
+drop table t1;
+create or replace table t1 (a int, b varchar(16384) as (concat(a,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')));
+ERROR HY000: Too many columns
+#
+# Constant expression
+create or replace table t1 (a int as (PI()) PERSISTENT);
+drop table if exists t1;
diff --git a/mysql-test/suite/vcol/r/vcol_blocked_sql_funcs_innodb.result b/mysql-test/suite/vcol/r/vcol_blocked_sql_funcs_innodb.result
deleted file mode 100644
index 7f805459264..00000000000
--- a/mysql-test/suite/vcol/r/vcol_blocked_sql_funcs_innodb.result
+++ /dev/null
@@ -1,255 +0,0 @@
-SET @@session.storage_engine = 'InnoDB';
-# RAND()
-create table t1 (b double as (rand()));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# LOAD_FILE()
-create table t1 (a varchar(64), b varchar(1024) as (load_file(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# CURDATE()
-create table t1 (a datetime as (curdate()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-# CURRENT_DATE(), CURRENT_DATE
-create table t1 (a datetime as (current_date));
-ERROR HY000: Function or expression is not allowed for column 'a'
-create table t1 (a datetime as (current_date()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-# CURRENT_TIME(), CURRENT_TIME
-create table t1 (a datetime as (current_time));
-ERROR HY000: Function or expression is not allowed for column 'a'
-create table t1 (a datetime as (current_time()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-# CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP
-create table t1 (a datetime as (current_timestamp()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-create table t1 (a datetime as (current_timestamp));
-ERROR HY000: Function or expression is not allowed for column 'a'
-# CURTIME()
-create table t1 (a datetime as (curtime()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-# LOCALTIME(), LOCALTIME
-create table t1 (a datetime, b varchar(10) as (localtime()));
-ERROR HY000: Function or expression is not allowed for column 'b'
-create table t1 (a datetime, b varchar(10) as (localtime));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# LOCALTIMESTAMP, LOCALTIMESTAMP()(v4.0.6)
-create table t1 (a datetime, b varchar(10) as (localtimestamp()));
-ERROR HY000: Function or expression is not allowed for column 'b'
-create table t1 (a datetime, b varchar(10) as (localtimestamp));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# NOW()
-create table t1 (a datetime, b varchar(10) as (now()));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# SYSDATE()
-create table t1 (a int, b varchar(10) as (sysdate()));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# UNIX_TIMESTAMP()
-create table t1 (a datetime, b datetime as (unix_timestamp()));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# UTC_DATE()
-create table t1 (a datetime, b datetime as (utc_date()));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# UTC_TIME()
-create table t1 (a datetime, b datetime as (utc_time()));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# UTC_TIMESTAMP()
-create table t1 (a datetime, b datetime as (utc_timestamp()));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# WEEK() - one argument version
-create table t1 (a datetime, b datetime as (week(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# MATCH()
-# BENCHMARK()
-create table t1 (a varchar(1024), b varchar(1024) as (benchmark(a,3)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# CHARSET()
-create table t1 (a varchar(64), b varchar(64) as (charset(a)));
-ERROR HY000: Constant expression in computed column function is not allowed
-# COERCIBILITY()
-create table t1 (a varchar(64), b int as (coercibility(a)));
-ERROR HY000: Constant expression in computed column function is not allowed
-# COLLATION()
-create table t1 (a varchar(64), b varchar(64) as (collation(a)));
-ERROR HY000: Constant expression in computed column function is not allowed
-# CONNECTION_ID()
-create table t1 (a int as (connection_id()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-# CURRENT_USER(), CURRENT_USER
-create table t1 (a varchar(32) as (current_user()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-create table t1 (a varchar(32) as (current_user));
-ERROR HY000: Function or expression is not allowed for column 'a'
-# DATABASE()
-create table t1 (a varchar(1024), b varchar(1024) as (database()));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# FOUND_ROWS()
-create table t1 (a varchar(1024), b varchar(1024) as (found_rows()));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# GET_LOCK()
-create table t1 (a varchar(1024), b varchar(1024) as (get_lock(a,10)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# IS_FREE_LOCK()
-create table t1 (a varchar(1024), b varchar(1024) as (is_free_lock(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# IS_USED_LOCK()
-create table t1 (a varchar(1024), b varchar(1024) as (is_used_lock(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# LAST_INSERT_ID()
-create table t1 (a int as (last_insert_id()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-# MASTER_POS_WAIT()
-create table t1 (a varchar(32), b int as (master_pos_wait(a,0,2)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# NAME_CONST()
-create table t1 (a varchar(32) as (name_const('test',1)));
-ERROR HY000: Function or expression is not allowed for column 'a'
-# RELEASE_LOCK()
-create table t1 (a varchar(32), b int as (release_lock(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# ROW_COUNT()
-create table t1 (a int as (row_count()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-# SCHEMA()
-create table t1 (a varchar(32) as (schema()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-# SESSION_USER()
-create table t1 (a varchar(32) as (session_user()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-# SLEEP()
-create table t1 (a int, b int as (sleep(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# SYSTEM_USER()
-create table t1 (a varchar(32) as (system_user()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-# USER()
-create table t1 (a varchar(1024), b varchar(1024) as (user()));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# UUID_SHORT()
-create table t1 (a varchar(1024) as (uuid_short()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-# UUID()
-create table t1 (a varchar(1024) as (uuid()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-# VALUES()
-create table t1 (a varchar(1024), b varchar(1024) as (values(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# VERSION()
-create table t1 (a varchar(1024), b varchar(1024) as (version()));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# ENCRYPT()
-create table t1 (a varchar(1024), b varchar(1024) as (encrypt(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# Stored procedures
-create procedure p1()
-begin
-select current_user();
-end //
-create function f1()
-returns int
-begin
-return 1;
-end //
-create table t1 (a int as (p1()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-create table t1 (a int as (f1()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-drop procedure p1;
-drop function f1;
-# Unknown functions
-create table t1 (a int as (f1()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-#
-# GROUP BY FUNCTIONS
-#
-# AVG()
-create table t1 (a int, b int as (avg(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# BIT_AND()
-create table t1 (a int, b int as (bit_and(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# BIT_OR()
-create table t1 (a int, b int as (bit_or(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# BIT_XOR()
-create table t1 (a int, b int as (bit_xor(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# COUNT(DISTINCT)
-create table t1 (a int, b int as (count(distinct a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# COUNT()
-create table t1 (a int, b int as (count(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# GROUP_CONCAT()
-create table t1 (a varchar(32), b int as (group_concat(a,'')));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# MAX()
-create table t1 (a int, b int as (max(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# MIN()
-create table t1 (a int, b int as (min(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# STD()
-create table t1 (a int, b int as (std(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# STDDEV_POP()
-create table t1 (a int, b int as (stddev_pop(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# STDDEV_SAMP()
-create table t1 (a int, b int as (stddev_samp(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# STDDEV()
-create table t1 (a int, b int as (stddev(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# SUM()
-create table t1 (a int, b int as (sum(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# VAR_POP()
-create table t1 (a int, b int as (var_pop(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# VAR_SAMP()
-create table t1 (a int, b int as (var_samp(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# VARIANCE()
-create table t1 (a int, b int as (variance(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-#
-# XML FUNCTIONS
-#
-# ExtractValue()
-create table t1 (a varchar(1024), b varchar(1024) as (ExtractValue(a,'//b[$@j]')));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# UpdateXML()
-create table t1 (a varchar(1024), b varchar(1024) as (UpdateXML(a,'/a','<e>fff</e>')));
-ERROR HY000: Function or expression is not allowed for column 'b'
-#
-# Sub-selects
-#
-create table t1 (a int);
-create table t2 (a int, b int as (select count(*) from t1));
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'select count(*) from t1))' at line 1
-drop table t1;
-create table t1 (a int, b int as ((select 1)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-create table t1 (a int, b int as (a+(select 1)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-#
-# SP functions
-#
-drop function if exists sub1;
-create function sub1(i int) returns int deterministic
-return i+1;
-select sub1(1);
-sub1(1)
-2
-create table t1 (a int, b int as (a+sub3(1)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-drop function sub1;
-#
-# Long expression
-create table t1 (a int, b varchar(300) as (concat(a,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')));
-drop table t1;
-create table t1 (a int, b varchar(300) as (concat(a,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')));
-ERROR HY000: String 'concat(a,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' is too long for VIRTUAL COLUMN EXPRESSION (should be no longer than 252)
-#
-# Constant expression
-create table t1 (a int as (PI()));
-ERROR HY000: Constant expression in computed column function is not allowed
diff --git a/mysql-test/suite/vcol/r/vcol_blocked_sql_funcs_myisam.result b/mysql-test/suite/vcol/r/vcol_blocked_sql_funcs_myisam.result
deleted file mode 100644
index 1e1e6d6466e..00000000000
--- a/mysql-test/suite/vcol/r/vcol_blocked_sql_funcs_myisam.result
+++ /dev/null
@@ -1,257 +0,0 @@
-SET @@session.storage_engine = 'MyISAM';
-# RAND()
-create table t1 (b double as (rand()));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# LOAD_FILE()
-create table t1 (a varchar(64), b varchar(1024) as (load_file(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# CURDATE()
-create table t1 (a datetime as (curdate()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-# CURRENT_DATE(), CURRENT_DATE
-create table t1 (a datetime as (current_date));
-ERROR HY000: Function or expression is not allowed for column 'a'
-create table t1 (a datetime as (current_date()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-# CURRENT_TIME(), CURRENT_TIME
-create table t1 (a datetime as (current_time));
-ERROR HY000: Function or expression is not allowed for column 'a'
-create table t1 (a datetime as (current_time()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-# CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP
-create table t1 (a datetime as (current_timestamp()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-create table t1 (a datetime as (current_timestamp));
-ERROR HY000: Function or expression is not allowed for column 'a'
-# CURTIME()
-create table t1 (a datetime as (curtime()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-# LOCALTIME(), LOCALTIME
-create table t1 (a datetime, b varchar(10) as (localtime()));
-ERROR HY000: Function or expression is not allowed for column 'b'
-create table t1 (a datetime, b varchar(10) as (localtime));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# LOCALTIMESTAMP, LOCALTIMESTAMP()(v4.0.6)
-create table t1 (a datetime, b varchar(10) as (localtimestamp()));
-ERROR HY000: Function or expression is not allowed for column 'b'
-create table t1 (a datetime, b varchar(10) as (localtimestamp));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# NOW()
-create table t1 (a datetime, b varchar(10) as (now()));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# SYSDATE()
-create table t1 (a int, b varchar(10) as (sysdate()));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# UNIX_TIMESTAMP()
-create table t1 (a datetime, b datetime as (unix_timestamp()));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# UTC_DATE()
-create table t1 (a datetime, b datetime as (utc_date()));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# UTC_TIME()
-create table t1 (a datetime, b datetime as (utc_time()));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# UTC_TIMESTAMP()
-create table t1 (a datetime, b datetime as (utc_timestamp()));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# WEEK() - one argument version
-create table t1 (a datetime, b datetime as (week(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# MATCH()
-create table t1 (a varchar(32), b bool as (match a against ('sample text')));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# BENCHMARK()
-create table t1 (a varchar(1024), b varchar(1024) as (benchmark(a,3)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# CHARSET()
-create table t1 (a varchar(64), b varchar(64) as (charset(a)));
-ERROR HY000: Constant expression in computed column function is not allowed
-# COERCIBILITY()
-create table t1 (a varchar(64), b int as (coercibility(a)));
-ERROR HY000: Constant expression in computed column function is not allowed
-# COLLATION()
-create table t1 (a varchar(64), b varchar(64) as (collation(a)));
-ERROR HY000: Constant expression in computed column function is not allowed
-# CONNECTION_ID()
-create table t1 (a int as (connection_id()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-# CURRENT_USER(), CURRENT_USER
-create table t1 (a varchar(32) as (current_user()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-create table t1 (a varchar(32) as (current_user));
-ERROR HY000: Function or expression is not allowed for column 'a'
-# DATABASE()
-create table t1 (a varchar(1024), b varchar(1024) as (database()));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# FOUND_ROWS()
-create table t1 (a varchar(1024), b varchar(1024) as (found_rows()));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# GET_LOCK()
-create table t1 (a varchar(1024), b varchar(1024) as (get_lock(a,10)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# IS_FREE_LOCK()
-create table t1 (a varchar(1024), b varchar(1024) as (is_free_lock(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# IS_USED_LOCK()
-create table t1 (a varchar(1024), b varchar(1024) as (is_used_lock(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# LAST_INSERT_ID()
-create table t1 (a int as (last_insert_id()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-# MASTER_POS_WAIT()
-create table t1 (a varchar(32), b int as (master_pos_wait(a,0,2)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# NAME_CONST()
-create table t1 (a varchar(32) as (name_const('test',1)));
-ERROR HY000: Function or expression is not allowed for column 'a'
-# RELEASE_LOCK()
-create table t1 (a varchar(32), b int as (release_lock(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# ROW_COUNT()
-create table t1 (a int as (row_count()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-# SCHEMA()
-create table t1 (a varchar(32) as (schema()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-# SESSION_USER()
-create table t1 (a varchar(32) as (session_user()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-# SLEEP()
-create table t1 (a int, b int as (sleep(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# SYSTEM_USER()
-create table t1 (a varchar(32) as (system_user()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-# USER()
-create table t1 (a varchar(1024), b varchar(1024) as (user()));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# UUID_SHORT()
-create table t1 (a varchar(1024) as (uuid_short()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-# UUID()
-create table t1 (a varchar(1024) as (uuid()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-# VALUES()
-create table t1 (a varchar(1024), b varchar(1024) as (values(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# VERSION()
-create table t1 (a varchar(1024), b varchar(1024) as (version()));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# ENCRYPT()
-create table t1 (a varchar(1024), b varchar(1024) as (encrypt(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# Stored procedures
-create procedure p1()
-begin
-select current_user();
-end //
-create function f1()
-returns int
-begin
-return 1;
-end //
-create table t1 (a int as (p1()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-create table t1 (a int as (f1()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-drop procedure p1;
-drop function f1;
-# Unknown functions
-create table t1 (a int as (f1()));
-ERROR HY000: Function or expression is not allowed for column 'a'
-#
-# GROUP BY FUNCTIONS
-#
-# AVG()
-create table t1 (a int, b int as (avg(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# BIT_AND()
-create table t1 (a int, b int as (bit_and(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# BIT_OR()
-create table t1 (a int, b int as (bit_or(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# BIT_XOR()
-create table t1 (a int, b int as (bit_xor(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# COUNT(DISTINCT)
-create table t1 (a int, b int as (count(distinct a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# COUNT()
-create table t1 (a int, b int as (count(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# GROUP_CONCAT()
-create table t1 (a varchar(32), b int as (group_concat(a,'')));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# MAX()
-create table t1 (a int, b int as (max(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# MIN()
-create table t1 (a int, b int as (min(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# STD()
-create table t1 (a int, b int as (std(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# STDDEV_POP()
-create table t1 (a int, b int as (stddev_pop(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# STDDEV_SAMP()
-create table t1 (a int, b int as (stddev_samp(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# STDDEV()
-create table t1 (a int, b int as (stddev(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# SUM()
-create table t1 (a int, b int as (sum(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# VAR_POP()
-create table t1 (a int, b int as (var_pop(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# VAR_SAMP()
-create table t1 (a int, b int as (var_samp(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# VARIANCE()
-create table t1 (a int, b int as (variance(a)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-#
-# XML FUNCTIONS
-#
-# ExtractValue()
-create table t1 (a varchar(1024), b varchar(1024) as (ExtractValue(a,'//b[$@j]')));
-ERROR HY000: Function or expression is not allowed for column 'b'
-# UpdateXML()
-create table t1 (a varchar(1024), b varchar(1024) as (UpdateXML(a,'/a','<e>fff</e>')));
-ERROR HY000: Function or expression is not allowed for column 'b'
-#
-# Sub-selects
-#
-create table t1 (a int);
-create table t2 (a int, b int as (select count(*) from t1));
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'select count(*) from t1))' at line 1
-drop table t1;
-create table t1 (a int, b int as ((select 1)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-create table t1 (a int, b int as (a+(select 1)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-#
-# SP functions
-#
-drop function if exists sub1;
-create function sub1(i int) returns int deterministic
-return i+1;
-select sub1(1);
-sub1(1)
-2
-create table t1 (a int, b int as (a+sub3(1)));
-ERROR HY000: Function or expression is not allowed for column 'b'
-drop function sub1;
-#
-# Long expression
-create table t1 (a int, b varchar(300) as (concat(a,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')));
-drop table t1;
-create table t1 (a int, b varchar(300) as (concat(a,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')));
-ERROR HY000: String 'concat(a,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' is too long for VIRTUAL COLUMN EXPRESSION (should be no longer than 252)
-#
-# Constant expression
-create table t1 (a int as (PI()));
-ERROR HY000: Constant expression in computed column function is not allowed
diff --git a/mysql-test/suite/vcol/r/vcol_misc.result b/mysql-test/suite/vcol/r/vcol_misc.result
index 451898aa972..d4a583c34b2 100644
--- a/mysql-test/suite/vcol/r/vcol_misc.result
+++ b/mysql-test/suite/vcol/r/vcol_misc.result
@@ -34,7 +34,7 @@ CREATE TABLE t1 (
 a int NOT NULL DEFAULT '0',
 v double AS ((1, a)) VIRTUAL
 );
-ERROR HY000: Expression for computed column cannot return a row
+ERROR 21000: Operand should contain 1 column(s)
 CREATE TABLE t1 (
 a CHAR(255) BINARY NOT NULL DEFAULT 0,
 b CHAR(255) BINARY NOT NULL DEFAULT 0,
@@ -187,11 +187,7 @@ ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 tsv TIMESTAMP AS (ADDDATE(ts, INTERVAL 1 DAY)) VIRTUAL
 ) ENGINE=MyISAM;
 INSERT INTO t1 (tsv) VALUES (DEFAULT);
-Warnings:
-Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00'
 INSERT DELAYED INTO t1 (tsv) VALUES (DEFAULT);
-Warnings:
-Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00'
 FLUSH TABLES;
 SELECT COUNT(*) FROM t1;
 COUNT(*)
@@ -320,7 +316,9 @@ drop table t1;
 # MDEV-5611: self-referencing virtual column
 #
 create table t1 (a int, b int as (b is null) virtual);
-ERROR HY000: A computed column cannot be based on a computed column
+ERROR 01000: Expression for field `b` is refering to uninitialized field `b`
+create table t1 (a int as (1+1), b int as (a is null) virtual);
+drop table t1;
 # end of 5.3 tests
 create table t1 (v1 varchar(255) as (c1) persistent, c1 varchar(50)) collate=latin1_general_ci;
 show create table t1;
diff --git a/mysql-test/suite/vcol/r/vcol_partition_innodb.result b/mysql-test/suite/vcol/r/vcol_partition_innodb.result
index 466c1851800..6a7978a8bf4 100644
--- a/mysql-test/suite/vcol/r/vcol_partition_innodb.result
+++ b/mysql-test/suite/vcol/r/vcol_partition_innodb.result
@@ -12,15 +12,17 @@ PARTITION p2 VALUES LESS THAN (2008)
 insert into t1 values ('2006-01-01',default);
 insert into t1 values ('2007-01-01',default);
 insert into t1 values ('2005-01-01',default);
+insert into t1 (a) values ('2007-01-02');
 select * from t1;
 a	b
 2005-01-01	2005
 2006-01-01	2006
 2007-01-01	2007
+2007-01-02	2007
 select partition_name,table_rows,data_length from information_schema.partitions where table_name = 't1';
 partition_name	table_rows	data_length
 p0	1	16384
-p2	2	16384
+p2	3	16384
 # Modify the expression of virtual column b
 ALTER TABLE t1 modify b int as (year(a)-1);
 select * from t1;
@@ -28,10 +30,11 @@ a	b
 2005-01-01	2004
 2006-01-01	2005
 2007-01-01	2006
+2007-01-02	2006
 select partition_name,table_rows,data_length from information_schema.partitions where table_name = 't1';
 partition_name	table_rows	data_length
 p0	2	16384
-p2	1	16384
+p2	2	16384
 drop table t1;
 # Case 2. Partitioning by LIST based on a stored virtual column.
 CREATE TABLE t1 (a int, b int as (a % 3 ) persistent)
diff --git a/mysql-test/suite/vcol/r/vcol_partition_myisam.result b/mysql-test/suite/vcol/r/vcol_partition_myisam.result
index 9a92f308c45..cb6f7fe1eca 100644
--- a/mysql-test/suite/vcol/r/vcol_partition_myisam.result
+++ b/mysql-test/suite/vcol/r/vcol_partition_myisam.result
@@ -12,15 +12,17 @@ PARTITION p2 VALUES LESS THAN (2008)
 insert into t1 values ('2006-01-01',default);
 insert into t1 values ('2007-01-01',default);
 insert into t1 values ('2005-01-01',default);
+insert into t1 (a) values ('2007-01-02');
 select * from t1;
 a	b
 2005-01-01	2005
 2006-01-01	2006
 2007-01-01	2007
+2007-01-02	2007
 select partition_name,table_rows,data_length from information_schema.partitions where table_name = 't1';
 partition_name	table_rows	data_length
 p0	1	7
-p2	2	14
+p2	3	21
 # Modify the expression of virtual column b
 ALTER TABLE t1 modify b int as (year(a)-1);
 select * from t1;
@@ -28,10 +30,11 @@ a	b
 2005-01-01	2004
 2006-01-01	2005
 2007-01-01	2006
+2007-01-02	2006
 select partition_name,table_rows,data_length from information_schema.partitions where table_name = 't1';
 partition_name	table_rows	data_length
 p0	2	14
-p2	1	7
+p2	2	14
 drop table t1;
 # Case 2. Partitioning by LIST based on a stored virtual column.
 CREATE TABLE t1 (a int, b int as (a % 3 ) persistent)
diff --git a/mysql-test/suite/vcol/r/vcol_supported_sql_funcs_myisam.result b/mysql-test/suite/vcol/r/vcol_supported_sql_funcs.result
index 1c6af88f9d7..7ce09a3a967 100644
--- a/mysql-test/suite/vcol/r/vcol_supported_sql_funcs_myisam.result
+++ b/mysql-test/suite/vcol/r/vcol_supported_sql_funcs.result
@@ -2028,21 +2028,6 @@ a	b
 2008-08-31 00:00:00	2008-09-30 00:00:00
 drop table t1;
 set sql_warnings = 0;
-# DATE_FORMAT()
-set sql_warnings = 1;
-create table t1 (a datetime, b varchar(64) as (date_format(a,'%W %M %D')));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` varchar(64) AS (date_format(a,'%W %M %D')) VIRTUAL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
-insert into t1 values ('2008-08-31',default);
-select * from t1;
-a	b
-2008-08-31 00:00:00	Sunday August 31st
-drop table t1;
-set sql_warnings = 0;
 # DATE_SUB()
 set sql_warnings = 1;
 create table t1 (a datetime, b datetime as (date_sub(a,interval 1 month)));
@@ -2103,21 +2088,6 @@ a	b
 2008-08-31 00:00:00	31
 drop table t1;
 set sql_warnings = 0;
-# DAYNAME()
-set sql_warnings = 1;
-create table t1 (a datetime, b varchar(10) as (dayname(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` varchar(10) AS (dayname(a)) VIRTUAL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
-insert into t1 values ('2008-08-31',default);
-select * from t1;
-a	b
-2008-08-31 00:00:00	Sunday
-drop table t1;
-set sql_warnings = 0;
 # DAYOFMONTH()
 set sql_warnings = 1;
 create table t1 (a datetime, b int as (dayofmonth(a)));
@@ -2209,21 +2179,6 @@ a	b
 1196440219	2007-11-30 16:30:19
 drop table t1;
 set sql_warnings = 0;
-# GET_FORMAT()
-set sql_warnings = 1;
-create table t1 (a datetime, b varchar(32) as (date_format(a,get_format(DATE,'EUR'))));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` varchar(32) AS (date_format(a,get_format(DATE,'EUR'))) VIRTUAL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
-insert into t1 values ('2008-08-31',default);
-select * from t1;
-a	b
-2008-08-31 00:00:00	31.08.2008
-drop table t1;
-set sql_warnings = 0;
 # HOUR()
 set sql_warnings = 1;
 create table t1 (a time, b long as (hour(a)));
@@ -2335,21 +2290,6 @@ a	b
 2009-12-31 23:59:59	12
 drop table t1;
 set sql_warnings = 0;
-# MONTHNAME()
-set sql_warnings = 1;
-create table t1 (a datetime, b varchar(16) as (monthname(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` varchar(16) AS (monthname(a)) VIRTUAL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
-insert into t1 values ('2009-12-31 23:59:59.000010',default);
-select * from t1;
-a	b
-2009-12-31 23:59:59	December
-drop table t1;
-set sql_warnings = 0;
 # PERIOD_ADD()
 set sql_warnings = 1;
 create table t1 (a int, b int as (period_add(a,2)));
@@ -2471,21 +2411,6 @@ a	b
 2008-08-31 00:00:00	2008-08-30 22:00:00
 drop table t1;
 set sql_warnings = 0;
-# TIME_FORMAT()
-set sql_warnings = 1;
-create table t1 (a datetime, b varchar(32) as (time_format(a,'%r')));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` varchar(32) AS (time_format(a,'%r')) VIRTUAL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
-insert into t1 values ('2008-08-31 02:03:04',default);
-select * from t1;
-a	b
-2008-08-31 02:03:04	02:03:04 AM
-drop table t1;
-set sql_warnings = 0;
 # TIME_TO_SEC()
 set sql_warnings = 1;
 create table t1 (a time, b long as (time_to_sec(a)));
diff --git a/mysql-test/suite/vcol/r/vcol_supported_sql_funcs_innodb.result b/mysql-test/suite/vcol/r/vcol_supported_sql_funcs_innodb.result
deleted file mode 100644
index 772c47aac0d..00000000000
--- a/mysql-test/suite/vcol/r/vcol_supported_sql_funcs_innodb.result
+++ /dev/null
@@ -1,2908 +0,0 @@
-SET @@session.storage_engine = 'InnoDB';
-#
-# NUMERIC FUNCTIONS
-#
-# ABS()
-set sql_warnings = 1;
-create table t1 (a int, b int as (abs(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` int(11) DEFAULT NULL,
-  `b` int(11) AS (abs(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (-1, default);
-select * from t1;
-a	b
--1	1
-drop table t1;
-set sql_warnings = 0;
-# ACOS()
-set sql_warnings = 1;
-create table t1 (a double, b double as (format(acos(a),6)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` double DEFAULT NULL,
-  `b` double AS (format(acos(a),6)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (1, default);
-insert into t1 values (1.0001,default);
-insert into t1 values (0,default);
-select * from t1;
-a	b
-1	0
-1.0001	NULL
-0	1.570796
-drop table t1;
-set sql_warnings = 0;
-# ASIN()
-set sql_warnings = 1;
-create table t1 (a double, b double as (format(asin(a),6)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` double DEFAULT NULL,
-  `b` double AS (format(asin(a),6)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (0.2, default);
-insert into t1 values (1.0001,default);
-select * from t1;
-a	b
-0.2	0.201358
-1.0001	NULL
-drop table t1;
-set sql_warnings = 0;
-#ATAN
-set sql_warnings = 1;
-create table t1 (a double, b double, c double as (format(atan(a,b),6)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` double DEFAULT NULL,
-  `b` double DEFAULT NULL,
-  `c` double AS (format(atan(a,b),6)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (-2,2,default);
-insert into t1 values (format(PI(),6),0,default);
-select * from t1;
-a	b	c
--2	2	-0.785398
-3.141593	0	1.570796
-drop table t1;
-set sql_warnings = 0;
-set sql_warnings = 1;
-create table t1 (a double, c double as (format(atan(a),6)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` double DEFAULT NULL,
-  `c` double AS (format(atan(a),6)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (-2,default);
-insert into t1 values (format(PI(),6),default);
-select * from t1;
-a	c
--2	-1.107149
-3.141593	1.262627
-drop table t1;
-set sql_warnings = 0;
-# ATAN2
-set sql_warnings = 1;
-create table t1 (a double, b double, c double as (format(atan2(a,b),6)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` double DEFAULT NULL,
-  `b` double DEFAULT NULL,
-  `c` double AS (format(atan2(a,b),6)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (-2,2,default);
-insert into t1 values (format(PI(),6),0,default);
-select * from t1;
-a	b	c
--2	2	-0.785398
-3.141593	0	1.570796
-drop table t1;
-set sql_warnings = 0;
-# CEIL()
-set sql_warnings = 1;
-create table t1 (a double, b int as (ceil(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` double DEFAULT NULL,
-  `b` int(11) AS (ceil(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (1.23,default);
-insert into t1 values (-1.23,default);
-select * from t1;
-a	b
-1.23	2
--1.23	-1
-drop table t1;
-set sql_warnings = 0;
-# CONV()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b int, c int, d varchar(10) as (conv(a,b,c)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` int(11) DEFAULT NULL,
-  `c` int(11) DEFAULT NULL,
-  `d` varchar(10) AS (conv(a,b,c)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('a',16,2,default);
-insert into t1 values ('6e',18,8,default);
-insert into t1 values (-17,10,-18,default);
-insert into t1 values (10+'10'+'10'+0xa,10,10,default);
-select * from t1;
-a	b	c	d
-a	16	2	1010
-6e	18	8	172
--17	10	-18	-H
-40	10	10	40
-drop table t1;
-set sql_warnings = 0;
-# COS()
-set sql_warnings = 1;
-create table t1 (a double, b double as (format(cos(a),6)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` double DEFAULT NULL,
-  `b` double AS (format(cos(a),6)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (format(PI(),6),default);
-select * from t1;
-a	b
-3.141593	-1
-drop table t1;
-set sql_warnings = 0;
-# COT()
-set sql_warnings = 1;
-create table t1 (a double, b double as (format(cot(a),6)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` double DEFAULT NULL,
-  `b` double AS (format(cot(a),6)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (12,default);
-insert into t1 values (1,default);
-select * from t1;
-a	b
-12	-1.572673
-1	0.642093
-drop table t1;
-set sql_warnings = 0;
-# CRC32()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b long as (crc32(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` mediumtext AS (crc32(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('MySQL',default);
-insert into t1 values ('mysql',default);
-select * from t1;
-a	b
-MySQL	3259397556
-mysql	2501908538
-drop table t1;
-set sql_warnings = 0;
-# DEGREES()
-set sql_warnings = 1;
-create table t1 (a double, b double as (format(degrees(a),6)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` double DEFAULT NULL,
-  `b` double AS (format(degrees(a),6)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (format(PI(),6),default);
-insert into t1 values (format(PI()/2,6),default);
-select * from t1;
-a	b
-3.141593	180.00002
-1.570796	89.999981
-drop table t1;
-set sql_warnings = 0;
-# /
-set sql_warnings = 1;
-create table t1 (a double, b double as (a/2));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` double DEFAULT NULL,
-  `b` double AS (a/2) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (2,default);
-select * from t1;
-a	b
-2	1
-drop table t1;
-set sql_warnings = 0;
-# EXP()
-set sql_warnings = 1;
-create table t1 (a double, b double as (format(exp(a),6)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` double DEFAULT NULL,
-  `b` double AS (format(exp(a),6)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (2,default);
-insert into t1 values (-2,default);
-insert into t1 values (0,default);
-select * from t1;
-a	b
-2	7.389056
--2	0.135335
-0	1
-drop table t1;
-set sql_warnings = 0;
-# FLOOR()
-set sql_warnings = 1;
-create table t1 (a double, b long as (floor(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` double DEFAULT NULL,
-  `b` mediumtext AS (floor(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (1.23,default);
-insert into t1 values (-1.23,default);
-select * from t1;
-a	b
-1.23	1
--1.23	-2
-drop table t1;
-set sql_warnings = 0;
-# LN()
-set sql_warnings = 1;
-create table t1 (a double, b double as (format(ln(a),6)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` double DEFAULT NULL,
-  `b` double AS (format(ln(a),6)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (2,default);
-insert into t1 values (-2,default);
-select * from t1;
-a	b
-2	0.693147
--2	NULL
-drop table t1;
-set sql_warnings = 0;
-# LOG()
-set sql_warnings = 1;
-create table t1 (a double, b double, c double as (format(log(a,b),6)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` double DEFAULT NULL,
-  `b` double DEFAULT NULL,
-  `c` double AS (format(log(a,b),6)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (2,65536,default);
-insert into t1 values (10,100,default);
-insert into t1 values (1,100,default);
-select * from t1;
-a	b	c
-2	65536	16
-10	100	2
-1	100	NULL
-drop table t1;
-set sql_warnings = 0;
-set sql_warnings = 1;
-create table t1 (a double, b double as (format(log(a),6)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` double DEFAULT NULL,
-  `b` double AS (format(log(a),6)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (2,default);
-insert into t1 values (-2,default);
-select * from t1;
-a	b
-2	0.693147
--2	NULL
-drop table t1;
-set sql_warnings = 0;
-# LOG2()
-set sql_warnings = 1;
-create table t1 (a double, b double as (format(log2(a),6)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` double DEFAULT NULL,
-  `b` double AS (format(log2(a),6)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (65536,default);
-insert into t1 values (-100,default);
-select * from t1;
-a	b
-65536	16
--100	NULL
-drop table t1;
-set sql_warnings = 0;
-# LOG10()
-set sql_warnings = 1;
-create table t1 (a double, b double as (format(log10(a),6)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` double DEFAULT NULL,
-  `b` double AS (format(log10(a),6)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (2,default);
-insert into t1 values (100,default);
-insert into t1 values (-100,default);
-select * from t1;
-a	b
-2	0.30103
-100	2
--100	NULL
-drop table t1;
-set sql_warnings = 0;
-# -
-set sql_warnings = 1;
-create table t1 (a double, b double as (a-1));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` double DEFAULT NULL,
-  `b` double AS (a-1) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (2,default);
-select * from t1;
-a	b
-2	1
-drop table t1;
-set sql_warnings = 0;
-# MOD()
-set sql_warnings = 1;
-create table t1 (a int, b int as (mod(a,10)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` int(11) DEFAULT NULL,
-  `b` int(11) AS (mod(a,10)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (1,default);
-insert into t1 values (11,default);
-select * from t1;
-a	b
-1	1
-11	1
-drop table t1;
-set sql_warnings = 0;
-# %
-set sql_warnings = 1;
-create table t1 (a int, b int as (a % 10));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` int(11) DEFAULT NULL,
-  `b` int(11) AS (a % 10) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (1,default);
-insert into t1 values (11,default);
-select * from t1;
-a	b
-1	1
-11	1
-drop table t1;
-set sql_warnings = 0;
-# OCT()
-set sql_warnings = 1;
-create table t1 (a double, b varchar(10) as (oct(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` double DEFAULT NULL,
-  `b` varchar(10) AS (oct(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (12,default);
-select * from t1;
-a	b
-12	14
-drop table t1;
-set sql_warnings = 0;
-# PI()
-set sql_warnings = 1;
-create table t1 (a double, b double as (format(PI()*a*a,6)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` double DEFAULT NULL,
-  `b` double AS (format(PI()*a*a,6)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (1,default);
-select * from t1;
-a	b
-1	3.141593
-drop table t1;
-set sql_warnings = 0;
-# +
-set sql_warnings = 1;
-create table t1 (a int, b int as (a+1));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` int(11) DEFAULT NULL,
-  `b` int(11) AS (a+1) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (1,default);
-select * from t1;
-a	b
-1	2
-drop table t1;
-set sql_warnings = 0;
-# POW, POWER
-set sql_warnings = 1;
-create table t1 (a int, b int as (pow(a,2)), c int as (power(a,2)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` int(11) DEFAULT NULL,
-  `b` int(11) AS (pow(a,2)) VIRTUAL,
-  `c` int(11) AS (power(a,2)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (1,default,default);
-insert into t1 values (2,default,default);
-select * from t1;
-a	b	c
-1	1	1
-2	4	4
-drop table t1;
-set sql_warnings = 0;
-# RADIANS()
-set sql_warnings = 1;
-create table t1 (a double, b double as (format(radians(a),6)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` double DEFAULT NULL,
-  `b` double AS (format(radians(a),6)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (90,default);
-select * from t1;
-a	b
-90	1.570796
-drop table t1;
-set sql_warnings = 0;
-# ROUND()
-set sql_warnings = 1;
-create table t1 (a double, b int as (round(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` double DEFAULT NULL,
-  `b` int(11) AS (round(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (-1.23,default);
-insert into t1 values (-1.58,default);
-insert into t1 values (1.58,default);
-select * from t1;
-a	b
--1.23	-1
--1.58	-2
-1.58	2
-drop table t1;
-set sql_warnings = 0;
-set sql_warnings = 1;
-create table t1 (a double, b double, c int as (round(a,b)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` double DEFAULT NULL,
-  `b` double DEFAULT NULL,
-  `c` int(11) AS (round(a,b)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (1.298,1,default);
-insert into t1 values (1.298,0,default);
-insert into t1 values (23.298,-1,default);
-select * from t1;
-a	b	c
-1.298	1	1
-1.298	0	1
-23.298	-1	20
-drop table t1;
-set sql_warnings = 0;
-# SIGN()
-set sql_warnings = 1;
-create table t1 (a double, b int as (sign(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` double DEFAULT NULL,
-  `b` int(11) AS (sign(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (-32,default);
-insert into t1 values (0,default);
-insert into t1 values (234,default);
-select * from t1;
-a	b
--32	-1
-0	0
-234	1
-drop table t1;
-set sql_warnings = 0;
-# SIN()
-set sql_warnings = 1;
-create table t1 (a double, b double as (format(sin(a),6)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` double DEFAULT NULL,
-  `b` double AS (format(sin(a),6)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (format(PI()/2,6),default);
-select * from t1;
-a	b
-1.570796	1
-drop table t1;
-set sql_warnings = 0;
-# SQRT()
-set sql_warnings = 1;
-create table t1 (a double, b double as (format(sqrt(a),6)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` double DEFAULT NULL,
-  `b` double AS (format(sqrt(a),6)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (4,default);
-insert into t1 values (20,default);
-insert into t1 values (-16,default);
-select * from t1;
-a	b
-4	2
-20	4.472136
--16	NULL
-drop table t1;
-set sql_warnings = 0;
-# TAN()
-set sql_warnings = 1;
-create table t1 (a double, b double as (format(tan(a),6)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` double DEFAULT NULL,
-  `b` double AS (format(tan(a),6)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (format(PI(),6),default);
-insert into t1 values (format(PI()+1,6),default);
-select * from t1;
-a	b
-3.141593	0
-4.141593	1.557409
-drop table t1;
-set sql_warnings = 0;
-# *
-set sql_warnings = 1;
-create table t1 (a double, b double as (a*3));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` double DEFAULT NULL,
-  `b` double AS (a*3) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (0,default);
-insert into t1 values (1,default);
-insert into t1 values (2,default);
-select * from t1;
-a	b
-0	0
-1	3
-2	6
-drop table t1;
-set sql_warnings = 0;
-# TRUNCATE()
-set sql_warnings = 1;
-create table t1 (a double, b double as (truncate(a,4)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` double DEFAULT NULL,
-  `b` double AS (truncate(a,4)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (1.223,default);
-insert into t1 values (1.999,default);
-insert into t1 values (1.999,default);
-insert into t1 values (122,default);
-select * from t1;
-a	b
-1.223	1.223
-1.999	1.999
-1.999	1.999
-122	122
-drop table t1;
-set sql_warnings = 0;
-# Unary -
-set sql_warnings = 1;
-create table t1 (a double, b double as (-a));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` double DEFAULT NULL,
-  `b` double AS (-a) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (1,default);
-insert into t1 values (-1,default);
-select * from t1;
-a	b
-1	-1
--1	1
-drop table t1;
-set sql_warnings = 0;
-#
-# STRING FUNCTIONS
-#
-# ASCII()
-set sql_warnings = 1;
-create table t1 (a char(2), b int as (ascii(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` char(2) DEFAULT NULL,
-  `b` int(11) AS (ascii(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2',default);
-insert into t1 values (2,default);
-insert into t1 values ('dx',default);
-select * from t1;
-a	b
-2	50
-2	50
-dx	100
-drop table t1;
-set sql_warnings = 0;
-# BIN()
-set sql_warnings = 1;
-create table t1 (a int, b varchar(10) as (bin(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` int(11) DEFAULT NULL,
-  `b` varchar(10) AS (bin(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (12,default);
-select * from t1;
-a	b
-12	1100
-drop table t1;
-set sql_warnings = 0;
-# BIT_LENGTH()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b long as (bit_length(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` mediumtext AS (bit_length(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('text',default);
-select * from t1;
-a	b
-text	32
-drop table t1;
-set sql_warnings = 0;
-# CHAR_LENGTH()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b long as (char_length(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` mediumtext AS (char_length(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('text',default);
-select * from t1;
-a	b
-text	4
-drop table t1;
-set sql_warnings = 0;
-# CHAR()
-set sql_warnings = 1;
-create table t1 (a int, b int, c varbinary(10) as (char(a,b)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` int(11) DEFAULT NULL,
-  `b` int(11) DEFAULT NULL,
-  `c` varbinary(10) AS (char(a,b)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (77,121,default);
-select * from t1;
-a	b	c
-77	121	My
-drop table t1;
-set sql_warnings = 0;
-# CHARACTER_LENGTH()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b long as (character_length(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` mediumtext AS (character_length(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('text',default);
-select * from t1;
-a	b
-text	4
-drop table t1;
-set sql_warnings = 0;
-# CONCAT_WS()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(10), c varchar(20) as (concat_ws(',',a,b)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(10) DEFAULT NULL,
-  `c` varchar(20) AS (concat_ws(',',a,b)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('value1','value2',default);
-select * from t1;
-a	b	c
-value1	value2	value1,value2
-drop table t1;
-set sql_warnings = 0;
-# CONCAT()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(10), c varchar(20) as (concat(a,',',b)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(10) DEFAULT NULL,
-  `c` varchar(20) AS (concat(a,',',b)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('value1','value2',default);
-select * from t1;
-a	b	c
-value1	value2	value1,value2
-drop table t1;
-set sql_warnings = 0;
-# ELT()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(10), c int, d varchar(10) as (elt(c,a,b)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(10) DEFAULT NULL,
-  `c` int(11) DEFAULT NULL,
-  `d` varchar(10) AS (elt(c,a,b)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('value1','value2',1,default);
-insert into t1 values ('value1','value2',2,default);
-select * from t1;
-a	b	c	d
-value1	value2	1	value1
-value1	value2	2	value2
-drop table t1;
-set sql_warnings = 0;
-# EXPORT_SET()
-set sql_warnings = 1;
-create table t1 (a int, b varchar(10) as (export_set(a,'1','0','',10)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` int(11) DEFAULT NULL,
-  `b` varchar(10) AS (export_set(a,'1','0','',10)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (6,default);
-select * from t1;
-a	b
-6	0110000000
-drop table t1;
-set sql_warnings = 0;
-# FIELD()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(10), c int as (field('aa',a,b)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(10) DEFAULT NULL,
-  `c` int(11) AS (field('aa',a,b)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('aa','bb',default);
-insert into t1 values ('bb','aa',default);
-select * from t1;
-a	b	c
-aa	bb	1
-bb	aa	2
-drop table t1;
-set sql_warnings = 0;
-# FIND_IN_SET()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(10), c int as (find_in_set(a,b)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(10) DEFAULT NULL,
-  `c` int(11) AS (find_in_set(a,b)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('aa','aa,bb,cc',default);
-insert into t1 values ('aa','bb,aa,cc',default);
-select * from t1;
-a	b	c
-aa	aa,bb,cc	1
-aa	bb,aa,cc	2
-drop table t1;
-set sql_warnings = 0;
-# FORMAT()
-set sql_warnings = 1;
-create table t1 (a double, b varchar(20) as (format(a,2)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` double DEFAULT NULL,
-  `b` varchar(20) AS (format(a,2)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (12332.123456,default);
-select * from t1;
-a	b
-12332.123456	12,332.12
-drop table t1;
-set sql_warnings = 0;
-# HEX()
-set sql_warnings = 1;
-create table t1 (a int, b varchar(10) as (hex(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` int(11) DEFAULT NULL,
-  `b` varchar(10) AS (hex(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (17,default);
-select * from t1;
-a	b
-17	11
-drop table t1;
-set sql_warnings = 0;
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(10) as (hex(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(10) AS (hex(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('abc',default);
-select * from t1;
-a	b
-abc	616263
-drop table t1;
-set sql_warnings = 0;
-# INSERT()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(10), c varchar(20) as (insert(a,length(a),length(b),b)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(10) DEFAULT NULL,
-  `c` varchar(20) AS (insert(a,length(a),length(b),b)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('start,','end',default);
-select * from t1;
-a	b	c
-start,	end	startend
-drop table t1;
-set sql_warnings = 0;
-# INSTR()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(10), c int as (instr(a,b)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(10) DEFAULT NULL,
-  `c` int(11) AS (instr(a,b)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('foobarbar,','bar',default);
-insert into t1 values ('xbar,','foobar',default);
-select * from t1;
-a	b	c
-foobarbar,	bar	4
-xbar,	foobar	0
-drop table t1;
-set sql_warnings = 0;
-# LCASE()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(10) as (lcase(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(10) AS (lcase(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('MySQL',default);
-select * from t1;
-a	b
-MySQL	mysql
-drop table t1;
-set sql_warnings = 0;
-# LEFT()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(5) as (left(a,5)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(5) AS (left(a,5)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('foobarbar',default);
-select * from t1;
-a	b
-foobarbar	fooba
-drop table t1;
-set sql_warnings = 0;
-# LENGTH()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b int as (length(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` int(11) AS (length(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('text',default);
-select * from t1;
-a	b
-text	4
-drop table t1;
-set sql_warnings = 0;
-# LIKE
-set sql_warnings = 1;
-create table t1 (a varchar(10), b bool as (a like 'H%!o' escape '!'));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` tinyint(1) AS (a like 'H%!o' escape '!') VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('Hello',default);
-insert into t1 values ('MySQL',default);
-select * from t1;
-a	b
-Hello	1
-MySQL	0
-drop table t1;
-set sql_warnings = 0;
-# LOCATE()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(10) as (locate('bar',a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(10) AS (locate('bar',a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('foobarbar',default);
-select * from t1;
-a	b
-foobarbar	4
-drop table t1;
-set sql_warnings = 0;
-# LOWER()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(10) as (lower(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(10) AS (lower(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('MySQL',default);
-select * from t1;
-a	b
-MySQL	mysql
-drop table t1;
-set sql_warnings = 0;
-# LPAD()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(10) as (lpad(a,4,' ')));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(10) AS (lpad(a,4,' ')) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('MySQL',default);
-insert into t1 values ('M',default);
-select * from t1;
-a	b
-MySQL	MySQ
-M	   M
-drop table t1;
-set sql_warnings = 0;
-# LTRIM()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(10) as (ltrim(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(10) AS (ltrim(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('  MySQL',default);
-insert into t1 values ('MySQL',default);
-select * from t1;
-a	b
-  MySQL	MySQL
-MySQL	MySQL
-drop table t1;
-set sql_warnings = 0;
-# MAKE_SET()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(10), c int, d varchar(30) as (make_set(c,a,b)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(10) DEFAULT NULL,
-  `c` int(11) DEFAULT NULL,
-  `d` varchar(30) AS (make_set(c,a,b)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('a','b',1,default);
-insert into t1 values ('a','b',3,default);
-select * from t1;
-a	b	c	d
-a	b	1	a
-a	b	3	a,b
-drop table t1;
-set sql_warnings = 0;
-# MID()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(10) as (mid(a,1,2)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(10) AS (mid(a,1,2)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('foobarbar',default);
-select * from t1;
-a	b
-foobarbar	fo
-drop table t1;
-set sql_warnings = 0;
-# NOT LIKE
-set sql_warnings = 1;
-create table t1 (a varchar(10), b bool as (a not like 'H%o'));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` tinyint(1) AS (a not like 'H%o') VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('Hello',default);
-insert into t1 values ('MySQL',default);
-select * from t1;
-a	b
-Hello	0
-MySQL	1
-drop table t1;
-set sql_warnings = 0;
-# NOT REGEXP
-set sql_warnings = 1;
-create table t1 (a varchar(10), b bool as (a not regexp 'H.+o'));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` tinyint(1) AS (a not regexp 'H.+o') VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('Hello',default);
-insert into t1 values ('hello',default);
-select * from t1;
-a	b
-Hello	0
-hello	0
-drop table t1;
-set sql_warnings = 0;
-# OCTET_LENGTH()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b int as (octet_length(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` int(11) AS (octet_length(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('text',default);
-select * from t1;
-a	b
-text	4
-drop table t1;
-set sql_warnings = 0;
-# ORD()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b long as (ord(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` mediumtext AS (ord(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2',default);
-select * from t1;
-a	b
-2	50
-drop table t1;
-set sql_warnings = 0;
-# POSITION()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(10) as (position('bar' in a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(10) AS (position('bar' in a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('foobarbar',default);
-select * from t1;
-a	b
-foobarbar	4
-drop table t1;
-set sql_warnings = 0;
-# QUOTE()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(10) as (quote(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(10) AS (quote(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('Don\'t',default);
-select * from t1;
-a	b
-Don't	'Don\'t'
-drop table t1;
-set sql_warnings = 0;
-# REGEXP()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b bool as (a regexp 'H.+o'));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` tinyint(1) AS (a regexp 'H.+o') VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('Hello',default);
-insert into t1 values ('hello',default);
-select * from t1;
-a	b
-Hello	1
-hello	1
-drop table t1;
-set sql_warnings = 0;
-# REPEAT()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(30) as (repeat(a,3)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(30) AS (repeat(a,3)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('MySQL',default);
-select * from t1;
-a	b
-MySQL	MySQLMySQLMySQL
-drop table t1;
-set sql_warnings = 0;
-# REPLACE()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(30) as (replace(a,'aa','bb')));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(30) AS (replace(a,'aa','bb')) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('maa',default);
-select * from t1;
-a	b
-maa	mbb
-drop table t1;
-set sql_warnings = 0;
-# REVERSE()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(30) as (reverse(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(30) AS (reverse(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('maa',default);
-select * from t1;
-a	b
-maa	aam
-drop table t1;
-set sql_warnings = 0;
-# RIGHT()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(10) as (right(a,4)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(10) AS (right(a,4)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('foobarbar',default);
-select * from t1;
-a	b
-foobarbar	rbar
-drop table t1;
-set sql_warnings = 0;
-# RLIKE()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b bool as (a rlike 'H.+o'));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` tinyint(1) AS (a rlike 'H.+o') VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('Hello',default);
-insert into t1 values ('MySQL',default);
-select * from t1;
-a	b
-Hello	1
-MySQL	0
-drop table t1;
-set sql_warnings = 0;
-# RPAD()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(10) as (rpad(a,4,'??')));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(10) AS (rpad(a,4,'??')) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('He',default);
-select * from t1;
-a	b
-He	He??
-drop table t1;
-set sql_warnings = 0;
-# RTRIM();
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(10) as (rtrim(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(10) AS (rtrim(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('Hello  ',default);
-select * from t1;
-a	b
-Hello  	Hello
-drop table t1;
-set sql_warnings = 0;
-# SOUNDEX()
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(20) as (soundex(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(20) AS (soundex(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('Hello',default);
-select * from t1;
-a	b
-Hello	H400
-drop table t1;
-set sql_warnings = 0;
-# SOUNDS LIKE
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(10), c bool as (a sounds like b));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(10) DEFAULT NULL,
-  `c` tinyint(1) AS (a sounds like b) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('Hello','Hello',default);
-insert into t1 values ('Hello','MySQL',default);
-insert into t1 values ('Hello','hello',default);
-select * from t1;
-a	b	c
-Hello	Hello	1
-Hello	MySQL	0
-Hello	hello	1
-drop table t1;
-set sql_warnings = 0;
-# SPACE()
-set sql_warnings = 1;
-create table t1 (a varchar(5), b varchar(10) as (concat(a,space(5))));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(5) DEFAULT NULL,
-  `b` varchar(10) AS (concat(a,space(5))) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('Hello', default);
-select * from t1;
-a	b
-Hello	Hello     
-drop table t1;
-set sql_warnings = 0;
-# STRCMP()
-set sql_warnings = 1;
-create table t1 (a varchar(9), b varchar(9), c tinyint(1) as (strcmp(a,b)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(9) DEFAULT NULL,
-  `b` varchar(9) DEFAULT NULL,
-  `c` tinyint(1) AS (strcmp(a,b)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('Hello','Hello', default);
-insert into t1 values ('Hello','Hello1', default);
-select * from t1;
-a	b	c
-Hello	Hello	0
-Hello	Hello1	-1
-drop table t1;
-set sql_warnings = 0;
-# SUBSTR()
-set sql_warnings = 1;
-create table t1 (a varchar(5), b varchar(10) as (substr(a,2)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(5) DEFAULT NULL,
-  `b` varchar(10) AS (substr(a,2)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('Hello',default);
-select * from t1;
-a	b
-Hello	ello
-drop table t1;
-set sql_warnings = 0;
-# SUBSTRING_INDEX()
-set sql_warnings = 1;
-create table t1 (a varchar(15), b varchar(10) as (substring_index(a,'.',2)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(15) DEFAULT NULL,
-  `b` varchar(10) AS (substring_index(a,'.',2)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('www.mysql.com',default);
-select * from t1;
-a	b
-www.mysql.com	www.mysql
-drop table t1;
-set sql_warnings = 0;
-# SUBSTRING()
-set sql_warnings = 1;
-create table t1 (a varchar(5), b varchar(10) as (substring(a from 2 for 2)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(5) DEFAULT NULL,
-  `b` varchar(10) AS (substring(a from 2 for 2)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('Hello',default);
-select * from t1;
-a	b
-Hello	el
-drop table t1;
-set sql_warnings = 0;
-# TRIM()
-set sql_warnings = 1;
-create table t1 (a varchar(15), b varchar(10) as (trim(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(15) DEFAULT NULL,
-  `b` varchar(10) AS (trim(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (' aa ',default);
-select * from t1;
-a	b
- aa 	aa
-drop table t1;
-set sql_warnings = 0;
-# UCASE()
-set sql_warnings = 1;
-create table t1 (a varchar(5), b varchar(10) as (ucase(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(5) DEFAULT NULL,
-  `b` varchar(10) AS (ucase(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('MySQL',default);
-select * from t1;
-a	b
-MySQL	MYSQL
-drop table t1;
-set sql_warnings = 0;
-# UNHEX()
-set sql_warnings = 1;
-create table t1 (a varchar(15), b varchar(10) as (unhex(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(15) DEFAULT NULL,
-  `b` varchar(10) AS (unhex(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('4D7953514C',default);
-select * from t1;
-a	b
-4D7953514C	MySQL
-drop table t1;
-set sql_warnings = 0;
-# UPPER()
-set sql_warnings = 1;
-create table t1 (a varchar(5), b varchar(10) as (upper(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(5) DEFAULT NULL,
-  `b` varchar(10) AS (upper(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('MySQL',default);
-select * from t1;
-a	b
-MySQL	MYSQL
-drop table t1;
-set sql_warnings = 0;
-#
-# CONTROL FLOW FUNCTIONS
-#
-# CASE
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(16) as (case a when NULL then 'asd' when 'b' then 'B' else a end));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(16) AS (case a when NULL then 'asd' when 'b' then 'B' else a end) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (NULL,default);
-insert into t1 values ('b',default);
-insert into t1 values ('c',default);
-select * from t1;
-a	b
-NULL	NULL
-b	B
-c	c
-drop table t1;
-set sql_warnings = 0;
-# IF
-set sql_warnings = 1;
-create table t1 (a int, b int, c int as (if(a=1,a,b)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` int(11) DEFAULT NULL,
-  `b` int(11) DEFAULT NULL,
-  `c` int(11) AS (if(a=1,a,b)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (1,2,default);
-insert into t1 values (3,4,default);
-select * from t1;
-a	b	c
-1	2	1
-3	4	4
-drop table t1;
-set sql_warnings = 0;
-# IFNULL
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(10), c varchar(10) as (ifnull(a,'DEFAULT')));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(10) DEFAULT NULL,
-  `c` varchar(10) AS (ifnull(a,'DEFAULT')) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (NULL,'adf',default);
-insert into t1 values ('a','adf',default);
-select * from t1;
-a	b	c
-NULL	adf	DEFAULT
-a	adf	a
-drop table t1;
-set sql_warnings = 0;
-# NULLIF
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(10) as (nullif(a,'DEFAULT')));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(10) AS (nullif(a,'DEFAULT')) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('DEFAULT',default);
-insert into t1 values ('a',default);
-select * from t1;
-a	b
-DEFAULT	NULL
-a	a
-drop table t1;
-set sql_warnings = 0;
-#
-# OPERATORS
-#
-# AND, && 
-set sql_warnings = 1;
-create table t1 (a int, b bool as (a>0 && a<2));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` int(11) DEFAULT NULL,
-  `b` tinyint(1) AS (a>0 && a<2) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (-1,default);
-insert into t1 values (1,default);
-select * from t1;
-a	b
--1	0
-1	1
-drop table t1;
-set sql_warnings = 0;
-# BETWEEN ... AND ... 
-set sql_warnings = 1;
-create table t1 (a int, b bool as (a between 0 and 2));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` int(11) DEFAULT NULL,
-  `b` tinyint(1) AS (a between 0 and 2) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (-1,default);
-insert into t1 values (1,default);
-select * from t1;
-a	b
--1	0
-1	1
-drop table t1;
-set sql_warnings = 0;
-# BINARY
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varbinary(10) as (binary a));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varbinary(10) AS (binary a) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('11',default);
-insert into t1 values (1,default);
-select * from t1;
-a	b
-11	11
-1	1
-drop table t1;
-set sql_warnings = 0;
-# &
-set sql_warnings = 1;
-create table t1 (a int, b int as (a & 5));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` int(11) DEFAULT NULL,
-  `b` int(11) AS (a & 5) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (1,default);
-insert into t1 values (0,default);
-select * from t1;
-a	b
-1	1
-0	0
-drop table t1;
-set sql_warnings = 0;
-# ~
-set sql_warnings = 1;
-create table t1 (a int, b int as (~a));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` int(11) DEFAULT NULL,
-  `b` int(11) AS (~a) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (1,default);
-Warnings:
-Warning	1264	Out of range value for column 'b' at row 1
-select * from t1;
-a	b
-1	2147483647
-drop table t1;
-set sql_warnings = 0;
-# |
-set sql_warnings = 1;
-create table t1 (a int, b int as (a | 5));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` int(11) DEFAULT NULL,
-  `b` int(11) AS (a | 5) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (1,default);
-insert into t1 values (0,default);
-insert into t1 values (2,default);
-select * from t1;
-a	b
-1	5
-0	5
-2	7
-drop table t1;
-set sql_warnings = 0;
-# ^
-set sql_warnings = 1;
-create table t1 (a int, b int as (a ^ 5));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` int(11) DEFAULT NULL,
-  `b` int(11) AS (a ^ 5) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (1,default);
-insert into t1 values (0,default);
-insert into t1 values (2,default);
-select * from t1;
-a	b
-1	4
-0	5
-2	7
-drop table t1;
-set sql_warnings = 0;
-# DIV
-set sql_warnings = 1;
-create table t1 (a int, b int as (a div 5));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` int(11) DEFAULT NULL,
-  `b` int(11) AS (a div 5) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (1,default);
-insert into t1 values (7,default);
-select * from t1;
-a	b
-1	0
-7	1
-drop table t1;
-set sql_warnings = 0;
-# <=>
-set sql_warnings = 1;
-create table t1 (a int, b int, c bool as (a <=> b));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` int(11) DEFAULT NULL,
-  `b` int(11) DEFAULT NULL,
-  `c` tinyint(1) AS (a <=> b) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (1,1,default);
-insert into t1 values (NULL,NULL,default);
-insert into t1 values (1,NULL,default);
-select * from t1;
-a	b	c
-1	1	1
-NULL	NULL	1
-1	NULL	0
-drop table t1;
-set sql_warnings = 0;
-# =
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(10), c bool as (a=b));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(10) DEFAULT NULL,
-  `c` tinyint(1) AS (a=b) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('a','b',default);
-insert into t1 values ('a','a',default);
-select * from t1;
-a	b	c
-a	b	0
-a	a	1
-drop table t1;
-set sql_warnings = 0;
-# >=
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(10), c bool as (a >= b));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(10) DEFAULT NULL,
-  `c` tinyint(1) AS (a >= b) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('a','b',default);
-insert into t1 values ('a','a',default);
-select * from t1;
-a	b	c
-a	b	0
-a	a	1
-drop table t1;
-set sql_warnings = 0;
-# >
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(10), c bool as (a > b));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(10) DEFAULT NULL,
-  `c` tinyint(1) AS (a > b) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('a','b',default);
-insert into t1 values ('a','a',default);
-select * from t1;
-a	b	c
-a	b	0
-a	a	0
-drop table t1;
-set sql_warnings = 0;
-# IS NOT NULL
-set sql_warnings = 1;
-create table t1 (a int, b bool as (a is not null));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` int(11) DEFAULT NULL,
-  `b` tinyint(1) AS (a is not null) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (1,default);
-insert into t1 values (NULL,default);
-select * from t1;
-a	b
-1	1
-NULL	0
-drop table t1;
-set sql_warnings = 0;
-# IS NULL
-set sql_warnings = 1;
-create table t1 (a int, b bool as (a is null));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` int(11) DEFAULT NULL,
-  `b` tinyint(1) AS (a is null) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (1,default);
-insert into t1 values (NULL,default);
-select * from t1;
-a	b
-1	0
-NULL	1
-drop table t1;
-set sql_warnings = 0;
-# <<
-set sql_warnings = 1;
-create table t1 (a int, b int as (a << 2));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` int(11) DEFAULT NULL,
-  `b` int(11) AS (a << 2) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (1,default);
-insert into t1 values (3,default);
-select * from t1;
-a	b
-1	4
-3	12
-drop table t1;
-set sql_warnings = 0;
-# <=
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(10), c bool as (a <= b));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(10) DEFAULT NULL,
-  `c` tinyint(1) AS (a <= b) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('b','a',default);
-insert into t1 values ('b','b',default);
-insert into t1 values ('b','c',default);
-select * from t1;
-a	b	c
-b	a	0
-b	b	1
-b	c	1
-drop table t1;
-set sql_warnings = 0;
-# <
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(10), c bool as (a < b));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(10) DEFAULT NULL,
-  `c` tinyint(1) AS (a < b) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('b','a',default);
-insert into t1 values ('b','b',default);
-insert into t1 values ('b','c',default);
-select * from t1;
-a	b	c
-b	a	0
-b	b	0
-b	c	1
-drop table t1;
-set sql_warnings = 0;
-# NOT BETWEEN ... AND ...
-set sql_warnings = 1;
-create table t1 (a int, b bool as (a not between 0 and 2));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` int(11) DEFAULT NULL,
-  `b` tinyint(1) AS (a not between 0 and 2) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (-1,default);
-insert into t1 values (1,default);
-select * from t1;
-a	b
--1	1
-1	0
-drop table t1;
-set sql_warnings = 0;
-# <>
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(10), c bool as (a <> b));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(10) DEFAULT NULL,
-  `c` tinyint(1) AS (a <> b) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('b','a',default);
-insert into t1 values ('b','b',default);
-insert into t1 values ('b','c',default);
-select * from t1;
-a	b	c
-b	a	1
-b	b	0
-b	c	1
-drop table t1;
-set sql_warnings = 0;
-# !=
-set sql_warnings = 1;
-create table t1 (a varchar(10), b varchar(10), c bool as (a != b));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(10) DEFAULT NULL,
-  `b` varchar(10) DEFAULT NULL,
-  `c` tinyint(1) AS (a != b) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('b','a',default);
-insert into t1 values ('b','b',default);
-insert into t1 values ('b','c',default);
-select * from t1;
-a	b	c
-b	a	1
-b	b	0
-b	c	1
-drop table t1;
-set sql_warnings = 0;
-# ||, OR
-set sql_warnings = 1;
-create table t1 (a int, b int as (a>5 || a<3));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` int(11) DEFAULT NULL,
-  `b` int(11) AS (a>5 || a<3) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (1,default);
-insert into t1 values (4,default);
-select * from t1;
-a	b
-1	1
-4	0
-drop table t1;
-set sql_warnings = 0;
-# >>
-set sql_warnings = 1;
-create table t1 (a int, b int as (a >> 2));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` int(11) DEFAULT NULL,
-  `b` int(11) AS (a >> 2) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (8,default);
-insert into t1 values (3,default);
-select * from t1;
-a	b
-8	2
-3	0
-drop table t1;
-set sql_warnings = 0;
-# XOR
-set sql_warnings = 1;
-create table t1 (a int, b int as (a xor 5));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` int(11) DEFAULT NULL,
-  `b` int(11) AS (a xor 5) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (0,default);
-insert into t1 values (1,default);
-insert into t1 values (2,default);
-select * from t1;
-a	b
-0	1
-1	0
-2	0
-drop table t1;
-set sql_warnings = 0;
-#
-# DATE AND TIME FUNCTIONS
-#
-# ADDDATE()
-set sql_warnings = 1;
-create table t1 (a datetime, b datetime as (adddate(a,interval 1 month)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` datetime AS (adddate(a,interval 1 month)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2008-08-31',default);
-select * from t1;
-a	b
-2008-08-31 00:00:00	2008-09-30 00:00:00
-drop table t1;
-set sql_warnings = 0;
-# ADDTIME()
-set sql_warnings = 1;
-create table t1 (a datetime, b datetime as (addtime(a,'02:00:00')));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` datetime AS (addtime(a,'02:00:00')) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2008-08-31',default);
-select * from t1;
-a	b
-2008-08-31 00:00:00	2008-08-31 02:00:00
-drop table t1;
-set sql_warnings = 0;
-# CONVERT_TZ()
-set sql_warnings = 1;
-create table t1 (a datetime, b datetime as (convert_tz(a,'MET','UTC')));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` datetime AS (convert_tz(a,'MET','UTC')) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2008-08-31',default);
-select * from t1;
-a	b
-2008-08-31 00:00:00	2008-08-30 22:00:00
-drop table t1;
-set sql_warnings = 0;
-# DATE_ADD()
-set sql_warnings = 1;
-create table t1 (a datetime, b datetime as (date_add(a,interval 1 month)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` datetime AS (date_add(a,interval 1 month)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2008-08-31',default);
-select * from t1;
-a	b
-2008-08-31 00:00:00	2008-09-30 00:00:00
-drop table t1;
-set sql_warnings = 0;
-# DATE_FORMAT()
-set sql_warnings = 1;
-create table t1 (a datetime, b varchar(64) as (date_format(a,'%W %M %D')));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` varchar(64) AS (date_format(a,'%W %M %D')) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2008-08-31',default);
-select * from t1;
-a	b
-2008-08-31 00:00:00	Sunday August 31st
-drop table t1;
-set sql_warnings = 0;
-# DATE_SUB()
-set sql_warnings = 1;
-create table t1 (a datetime, b datetime as (date_sub(a,interval 1 month)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` datetime AS (date_sub(a,interval 1 month)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2008-08-31',default);
-select * from t1;
-a	b
-2008-08-31 00:00:00	2008-07-31 00:00:00
-drop table t1;
-set sql_warnings = 0;
-# DATE()
-set sql_warnings = 1;
-create table t1 (a datetime, b datetime as (date(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` datetime AS (date(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2008-08-31 02:00:00',default);
-select * from t1;
-a	b
-2008-08-31 02:00:00	2008-08-31 00:00:00
-drop table t1;
-set sql_warnings = 0;
-# DATEDIFF()
-set sql_warnings = 1;
-create table t1 (a datetime, b long as (datediff(a,'2000-01-01')));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` mediumtext AS (datediff(a,'2000-01-01')) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2008-08-31',default);
-select * from t1;
-a	b
-2008-08-31 00:00:00	3165
-drop table t1;
-set sql_warnings = 0;
-# DAY()
-set sql_warnings = 1;
-create table t1 (a datetime, b int as (day(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` int(11) AS (day(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2008-08-31',default);
-select * from t1;
-a	b
-2008-08-31 00:00:00	31
-drop table t1;
-set sql_warnings = 0;
-# DAYNAME()
-set sql_warnings = 1;
-create table t1 (a datetime, b varchar(10) as (dayname(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` varchar(10) AS (dayname(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2008-08-31',default);
-select * from t1;
-a	b
-2008-08-31 00:00:00	Sunday
-drop table t1;
-set sql_warnings = 0;
-# DAYOFMONTH()
-set sql_warnings = 1;
-create table t1 (a datetime, b int as (dayofmonth(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` int(11) AS (dayofmonth(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2008-08-31',default);
-select * from t1;
-a	b
-2008-08-31 00:00:00	31
-drop table t1;
-set sql_warnings = 0;
-# DAYOFWEEK()
-set sql_warnings = 1;
-create table t1 (a datetime, b int as (dayofweek(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` int(11) AS (dayofweek(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2008-08-31',default);
-select * from t1;
-a	b
-2008-08-31 00:00:00	1
-drop table t1;
-set sql_warnings = 0;
-# DAYOFYEAR()
-set sql_warnings = 1;
-create table t1 (a datetime, b int as (dayofyear(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` int(11) AS (dayofyear(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2008-08-31',default);
-select * from t1;
-a	b
-2008-08-31 00:00:00	244
-drop table t1;
-set sql_warnings = 0;
-# EXTRACT
-set sql_warnings = 1;
-create table t1 (a datetime, b int as (extract(year from a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` int(11) AS (extract(year from a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2008-08-31',default);
-select * from t1;
-a	b
-2008-08-31 00:00:00	2008
-drop table t1;
-set sql_warnings = 0;
-# FROM_DAYS()
-set sql_warnings = 1;
-create table t1 (a long, b datetime as (from_days(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` mediumtext DEFAULT NULL,
-  `b` datetime AS (from_days(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (730669,default);
-select * from t1;
-a	b
-730669	2000-07-03 00:00:00
-drop table t1;
-set sql_warnings = 0;
-# FROM_UNIXTIME()
-set time_zone='UTC';
-set sql_warnings = 1;
-create table t1 (a long, b datetime as (from_unixtime(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` mediumtext DEFAULT NULL,
-  `b` datetime AS (from_unixtime(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (1196440219,default);
-select * from t1;
-a	b
-1196440219	2007-11-30 16:30:19
-drop table t1;
-set sql_warnings = 0;
-# GET_FORMAT()
-set sql_warnings = 1;
-create table t1 (a datetime, b varchar(32) as (date_format(a,get_format(DATE,'EUR'))));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` varchar(32) AS (date_format(a,get_format(DATE,'EUR'))) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2008-08-31',default);
-select * from t1;
-a	b
-2008-08-31 00:00:00	31.08.2008
-drop table t1;
-set sql_warnings = 0;
-# HOUR()
-set sql_warnings = 1;
-create table t1 (a time, b long as (hour(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` time DEFAULT NULL,
-  `b` mediumtext AS (hour(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('10:05:03',default);
-select * from t1;
-a	b
-10:05:03	10
-drop table t1;
-set sql_warnings = 0;
-# LAST_DAY()
-set sql_warnings = 1;
-create table t1 (a datetime, b datetime as (last_day(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` datetime AS (last_day(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2003-02-05',default);
-insert into t1 values ('2003-02-32',default);
-Warnings:
-Warning	1265	Data truncated for column 'a' at row 1
-select * from t1;
-a	b
-2003-02-05 00:00:00	2003-02-28 00:00:00
-0000-00-00 00:00:00	NULL
-drop table t1;
-set sql_warnings = 0;
-# MAKEDATE()
-set sql_warnings = 1;
-create table t1 (a int, b datetime as (makedate(a,1)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` int(11) DEFAULT NULL,
-  `b` datetime AS (makedate(a,1)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (2001,default);
-select * from t1;
-a	b
-2001	2001-01-01 00:00:00
-drop table t1;
-set sql_warnings = 0;
-# MAKETIME()
-set sql_warnings = 1;
-create table t1 (a int, b time as (maketime(a,1,3)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` int(11) DEFAULT NULL,
-  `b` time AS (maketime(a,1,3)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (12,default);
-select * from t1;
-a	b
-12	12:01:03
-drop table t1;
-set sql_warnings = 0;
-# MICROSECOND()
-set sql_warnings = 1;
-create table t1 (a datetime, b long as (microsecond(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` mediumtext AS (microsecond(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2009-12-31 12:00:00.123456',default);
-insert into t1 values ('2009-12-31 23:59:59.000010',default);
-select * from t1;
-a	b
-2009-12-31 12:00:00	0
-2009-12-31 23:59:59	0
-drop table t1;
-set sql_warnings = 0;
-# MINUTE()
-set sql_warnings = 1;
-create table t1 (a datetime, b int as (minute(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` int(11) AS (minute(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2009-12-31 23:59:59.000010',default);
-select * from t1;
-a	b
-2009-12-31 23:59:59	59
-drop table t1;
-set sql_warnings = 0;
-# MONTH()
-set sql_warnings = 1;
-create table t1 (a datetime, b int as (month(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` int(11) AS (month(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2009-12-31 23:59:59.000010',default);
-select * from t1;
-a	b
-2009-12-31 23:59:59	12
-drop table t1;
-set sql_warnings = 0;
-# MONTHNAME()
-set sql_warnings = 1;
-create table t1 (a datetime, b varchar(16) as (monthname(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` varchar(16) AS (monthname(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2009-12-31 23:59:59.000010',default);
-select * from t1;
-a	b
-2009-12-31 23:59:59	December
-drop table t1;
-set sql_warnings = 0;
-# PERIOD_ADD()
-set sql_warnings = 1;
-create table t1 (a int, b int as (period_add(a,2)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` int(11) DEFAULT NULL,
-  `b` int(11) AS (period_add(a,2)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (200801,default);
-select * from t1;
-a	b
-200801	200803
-drop table t1;
-set sql_warnings = 0;
-# PERIOD_DIFF()
-set sql_warnings = 1;
-create table t1 (a int, b int, c int as (period_diff(a,b)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` int(11) DEFAULT NULL,
-  `b` int(11) DEFAULT NULL,
-  `c` int(11) AS (period_diff(a,b)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (200802,200703,default);
-select * from t1;
-a	b	c
-200802	200703	11
-drop table t1;
-set sql_warnings = 0;
-# QUARTER()
-set sql_warnings = 1;
-create table t1 (a datetime, b int as (quarter(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` int(11) AS (quarter(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2008-08-31',default);
-select * from t1;
-a	b
-2008-08-31 00:00:00	3
-drop table t1;
-set sql_warnings = 0;
-# SEC_TO_TIME()
-set sql_warnings = 1;
-create table t1 (a long, b time as (sec_to_time(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` mediumtext DEFAULT NULL,
-  `b` time AS (sec_to_time(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (2378,default);
-select * from t1;
-a	b
-2378	00:39:38
-drop table t1;
-set sql_warnings = 0;
-# SECOND()
-set sql_warnings = 1;
-create table t1 (a datetime, b int as (second(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` int(11) AS (second(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('10:05:03',default);
-select * from t1;
-a	b
-2010-05-03 00:00:00	0
-drop table t1;
-set sql_warnings = 0;
-# STR_TO_DATE()
-set sql_warnings = 1;
-create table t1 (a varchar(64), b datetime as (str_to_date(a,'%m/%d/%Y')));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(64) DEFAULT NULL,
-  `b` datetime AS (str_to_date(a,'%m/%d/%Y')) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('04/30/2004',default);
-select * from t1;
-a	b
-04/30/2004	2004-04-30 00:00:00
-drop table t1;
-set sql_warnings = 0;
-# SUBDATE()
-set sql_warnings = 1;
-create table t1 (a datetime, b datetime as (subdate(a,interval 1 month)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` datetime AS (subdate(a,interval 1 month)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2008-08-31',default);
-select * from t1;
-a	b
-2008-08-31 00:00:00	2008-07-31 00:00:00
-drop table t1;
-set sql_warnings = 0;
-# SUBTIME()
-set sql_warnings = 1;
-create table t1 (a datetime, b datetime as (subtime(a,'02:00:00')));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` datetime AS (subtime(a,'02:00:00')) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2008-08-31',default);
-select * from t1;
-a	b
-2008-08-31 00:00:00	2008-08-30 22:00:00
-drop table t1;
-set sql_warnings = 0;
-# TIME_FORMAT()
-set sql_warnings = 1;
-create table t1 (a datetime, b varchar(32) as (time_format(a,'%r')));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` varchar(32) AS (time_format(a,'%r')) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2008-08-31 02:03:04',default);
-select * from t1;
-a	b
-2008-08-31 02:03:04	02:03:04 AM
-drop table t1;
-set sql_warnings = 0;
-# TIME_TO_SEC()
-set sql_warnings = 1;
-create table t1 (a time, b long as (time_to_sec(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` time DEFAULT NULL,
-  `b` mediumtext AS (time_to_sec(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('22:23:00',default);
-select * from t1;
-a	b
-22:23:00	80580
-drop table t1;
-set sql_warnings = 0;
-# TIME()
-set sql_warnings = 1;
-create table t1 (a datetime, b time as (time(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` time AS (time(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2008-08-31 02:03:04',default);
-select * from t1;
-a	b
-2008-08-31 02:03:04	02:03:04
-drop table t1;
-set sql_warnings = 0;
-# TIMEDIFF()
-set sql_warnings = 1;
-create table t1 (a datetime, b datetime, c long as (timediff(a,b)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` datetime DEFAULT NULL,
-  `c` mediumtext AS (timediff(a,b)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2008-12-31 23:59:59.000001','2008-12-30 01:01:01.000002',default);
-select * from t1;
-a	b	c
-2008-12-31 23:59:59	2008-12-30 01:01:01	46:58:58
-drop table t1;
-set sql_warnings = 0;
-# TIMESTAMP()
-set sql_warnings = 1;
-create table t1 (a datetime, b timestamp as (timestamp(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` timestamp AS (timestamp(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2008-12-31',default);
-select * from t1;
-a	b
-2008-12-31 00:00:00	2008-12-31 00:00:00
-drop table t1;
-set sql_warnings = 0;
-# TIMESTAMPADD()
-set sql_warnings = 1;
-create table t1 (a datetime, b timestamp as (timestampadd(minute,1,a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` timestamp AS (timestampadd(minute,1,a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2003-01-02',default);
-select * from t1;
-a	b
-2003-01-02 00:00:00	2003-01-02 00:01:00
-drop table t1;
-set sql_warnings = 0;
-# TIMESTAMPDIFF()
-set sql_warnings = 1;
-create table t1 (a timestamp, b timestamp, c long as (timestampdiff(MONTH, a,b)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-  `b` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
-  `c` mediumtext AS (timestampdiff(MONTH, a,b)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2003-02-01','2003-05-01',default);
-select * from t1;
-a	b	c
-2003-02-01 00:00:00	2003-05-01 00:00:00	3
-drop table t1;
-set sql_warnings = 0;
-# TO_DAYS()
-set sql_warnings = 1;
-create table t1 (a datetime, b long as (to_days(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` mediumtext AS (to_days(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2007-10-07',default);
-select * from t1;
-a	b
-2007-10-07 00:00:00	733321
-drop table t1;
-set sql_warnings = 0;
-# WEEK()
-set sql_warnings = 1;
-create table t1 (a datetime, b int as (week(a,0)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` int(11) AS (week(a,0)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2008-09-01',default);
-select * from t1;
-a	b
-2008-09-01 00:00:00	35
-drop table t1;
-set sql_warnings = 0;
-# WEEKDAY()
-set sql_warnings = 1;
-create table t1 (a datetime, b int as (weekday(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` int(11) AS (weekday(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2008-09-01',default);
-select * from t1;
-a	b
-2008-09-01 00:00:00	0
-drop table t1;
-set sql_warnings = 0;
-# WEEKOFYEAR()
-set sql_warnings = 1;
-create table t1 (a datetime, b int as (weekofyear(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` int(11) AS (weekofyear(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2008-09-01',default);
-select * from t1;
-a	b
-2008-09-01 00:00:00	36
-drop table t1;
-set sql_warnings = 0;
-# YEAR()
-set sql_warnings = 1;
-create table t1 (a datetime, b int as (year(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` int(11) AS (year(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2008-09-01',default);
-select * from t1;
-a	b
-2008-09-01 00:00:00	2008
-drop table t1;
-set sql_warnings = 0;
-# YEARWEEK()
-set sql_warnings = 1;
-create table t1 (a datetime, b int as (yearweek(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` datetime DEFAULT NULL,
-  `b` int(11) AS (yearweek(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('2008-09-01',default);
-select * from t1;
-a	b
-2008-09-01 00:00:00	200835
-drop table t1;
-set sql_warnings = 0;
-#
-# FULL TEXT SEARCH FUNCTIONS
-#
-# None.
-#
-# CAST FUNCTIONS AND OPERATORS
-#
-# CAST()
-set sql_warnings = 1;
-create table t1 (a int, b long as (cast(a as unsigned)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` int(11) DEFAULT NULL,
-  `b` mediumtext AS (cast(a as unsigned)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (1,default);
-insert into t1 values (-1,default);
-Warnings:
-Note	1105	Cast to unsigned converted negative integer to it's positive complement
-select * from t1;
-a	b
-1	1
--1	18446744073709551615
-Warnings:
-Note	1105	Cast to unsigned converted negative integer to it's positive complement
-Note	1105	Cast to unsigned converted negative integer to it's positive complement
-drop table t1;
-set sql_warnings = 0;
-# Convert()
-set sql_warnings = 1;
-create table t1 (a int, b long as (convert(a,unsigned)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` int(11) DEFAULT NULL,
-  `b` mediumtext AS (convert(a,unsigned)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (1,default);
-insert into t1 values (-1,default);
-Warnings:
-Note	1105	Cast to unsigned converted negative integer to it's positive complement
-select * from t1;
-a	b
-1	1
--1	18446744073709551615
-Warnings:
-Note	1105	Cast to unsigned converted negative integer to it's positive complement
-Note	1105	Cast to unsigned converted negative integer to it's positive complement
-drop table t1;
-set sql_warnings = 0;
-#
-# XML FUNCTIONS
-#
-# None.
-#
-# OTHER FUNCTIONS
-#
-# AES_DECRYPT(), AES_ENCRYPT()
-set sql_warnings = 1;
-create table t1 (a varchar(1024), b varchar(1024) as (aes_encrypt(aes_decrypt(a,'adf'),'adf')));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(1024) DEFAULT NULL,
-  `b` varchar(1024) AS (aes_encrypt(aes_decrypt(a,'adf'),'adf')) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('MySQL',default);
-select * from t1;
-a	b
-MySQL	NULL
-drop table t1;
-set sql_warnings = 0;
-# BIT_COUNT()
-set sql_warnings = 1;
-create table t1 (a int, b int as (bit_count(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` int(11) DEFAULT NULL,
-  `b` int(11) AS (bit_count(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values (5,default);
-select * from t1;
-a	b
-5	2
-drop table t1;
-set sql_warnings = 0;
-# COMPRESS(), UNCOMPRESS()
-set sql_warnings = 1;
-create table t1 (a varchar(1024), b varchar(1024) as (uncompress(compress(a))));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(1024) DEFAULT NULL,
-  `b` varchar(1024) AS (uncompress(compress(a))) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('MySQL',default);
-select * from t1;
-a	b
-MySQL	MySQL
-drop table t1;
-set sql_warnings = 0;
-# ENCODE(), DECODE()
-set sql_warnings = 1;
-create table t1 (a varchar(1024), b varchar(1024) as (decode(encode(a,'abc'),'abc')));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(1024) DEFAULT NULL,
-  `b` varchar(1024) AS (decode(encode(a,'abc'),'abc')) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('MySQL',default);
-select * from t1;
-a	b
-MySQL	MySQL
-drop table t1;
-set sql_warnings = 0;
-# DEFAULT()
-set sql_warnings = 1;
-create table t1 (a varchar(1024) default 'aaa', b varchar(1024) as (ifnull(a,default(a))));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(1024) DEFAULT 'aaa',
-  `b` varchar(1024) AS (ifnull(a,default(a))) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('any value',default);
-select * from t1;
-a	b
-any value	any value
-drop table t1;
-set sql_warnings = 0;
-# INET_ATON(), INET_NTOA()
-set sql_warnings = 1;
-create table t1 (a varchar(1024), b varchar(1024) as (inet_ntoa(inet_aton(a))));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(1024) DEFAULT NULL,
-  `b` varchar(1024) AS (inet_ntoa(inet_aton(a))) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('127.0.0.1',default);
-select * from t1;
-a	b
-127.0.0.1	127.0.0.1
-drop table t1;
-set sql_warnings = 0;
-# MD5()
-set sql_warnings = 1;
-create table t1 (a varchar(1024), b varbinary(32) as (md5(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(1024) DEFAULT NULL,
-  `b` varbinary(32) AS (md5(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('testing',default);
-select * from t1;
-a	b
-testing	ae2b1fca515949e5d54fb22b8ed95575
-drop table t1;
-set sql_warnings = 0;
-# OLD_PASSWORD()
-set sql_warnings = 1;
-create table t1 (a varchar(1024), b varchar(1024) as (old_password(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(1024) DEFAULT NULL,
-  `b` varchar(1024) AS (old_password(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('badpwd',default);
-select * from t1;
-a	b
-badpwd	7f84554057dd964b
-drop table t1;
-set sql_warnings = 0;
-# PASSWORD()
-set sql_warnings = 1;
-create table t1 (a varchar(1024), b varchar(1024) as (password(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(1024) DEFAULT NULL,
-  `b` varchar(1024) AS (password(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('badpwd',default);
-select * from t1;
-a	b
-badpwd	*AAB3E285149C0135D51A520E1940DD3263DC008C
-drop table t1;
-set sql_warnings = 0;
-# SHA1()
-set sql_warnings = 1;
-create table t1 (a varchar(1024), b varchar(1024) as (sha1(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(1024) DEFAULT NULL,
-  `b` varchar(1024) AS (sha1(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('abc',default);
-select * from t1;
-a	b
-abc	a9993e364706816aba3e25717850c26c9cd0d89d
-drop table t1;
-set sql_warnings = 0;
-# SHA()
-set sql_warnings = 1;
-create table t1 (a varchar(1024), b varchar(1024) as (sha(a)));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` varchar(1024) DEFAULT NULL,
-  `b` varchar(1024) AS (sha(a)) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('abc',default);
-select * from t1;
-a	b
-abc	a9993e364706816aba3e25717850c26c9cd0d89d
-drop table t1;
-set sql_warnings = 0;
-# UNCOMPRESSED_LENGTH()
-set sql_warnings = 1;
-create table t1 (a char, b varchar(1024) as (uncompressed_length(compress(repeat(a,30)))));
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `a` char(1) DEFAULT NULL,
-  `b` varchar(1024) AS (uncompressed_length(compress(repeat(a,30)))) VIRTUAL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-insert into t1 values ('a',default);
-select * from t1;
-a	b
-a	30
-drop table t1;
-set sql_warnings = 0;
diff --git a/mysql-test/suite/vcol/r/vcol_view_innodb.result b/mysql-test/suite/vcol/r/vcol_view_innodb.result
index c9a4897e230..43ade0dddaf 100644
--- a/mysql-test/suite/vcol/r/vcol_view_innodb.result
+++ b/mysql-test/suite/vcol/r/vcol_view_innodb.result
@@ -270,11 +270,11 @@ c int as (-a) persistent);
 create view v1 as select * from t1 where b > -2 && c >-2 with check option;
 insert into v1 (a) values (1);
 insert into v1 (a) values (3);
-ERROR HY000: CHECK OPTION failed 'test.v1'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 insert ignore into v1 (a) values (2),(3),(0);
 Warnings:
-Warning	1369	CHECK OPTION failed 'test.v1'
-Warning	1369	CHECK OPTION failed 'test.v1'
+Warning	1369	CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
+Warning	1369	CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 select * from t1;
 a	b	c
 1	-1	-1
diff --git a/mysql-test/suite/vcol/r/vcol_view_myisam.result b/mysql-test/suite/vcol/r/vcol_view_myisam.result
index e3fb2ec61ca..acd3389e170 100644
--- a/mysql-test/suite/vcol/r/vcol_view_myisam.result
+++ b/mysql-test/suite/vcol/r/vcol_view_myisam.result
@@ -270,11 +270,11 @@ c int as (-a) persistent);
 create view v1 as select * from t1 where b > -2 && c >-2 with check option;
 insert into v1 (a) values (1);
 insert into v1 (a) values (3);
-ERROR HY000: CHECK OPTION failed 'test.v1'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 insert ignore into v1 (a) values (2),(3),(0);
 Warnings:
-Warning	1369	CHECK OPTION failed 'test.v1'
-Warning	1369	CHECK OPTION failed 'test.v1'
+Warning	1369	CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
+Warning	1369	CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 select * from t1;
 a	b	c
 1	-1	-1
diff --git a/mysql-test/suite/vcol/t/not_supported.test b/mysql-test/suite/vcol/t/not_supported.test
index 70b9dea69fd..4cfc97a1be1 100644
--- a/mysql-test/suite/vcol/t/not_supported.test
+++ b/mysql-test/suite/vcol/t/not_supported.test
@@ -16,24 +16,26 @@ create table t1 (a int, b int, v decimal(20,19) as (a/3));
 --error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
 create table t2 (a int, b int, v int as (a+@a));
 --error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t3 (a int, b int, v int as (a+@@error_count));
+create table t2 (a int, b int, v int as (a+@a) PERSISTENT);
+create table t3_ok (a int, b int, v int as (a+@@error_count));
+--error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create table t3 (a int, b int, v int as (a+@@error_count) PERSISTENT);
 --error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
 create table t4 (a int, b int, v int as (@a:=a));
+--error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create table t4 (a int, b int, v int as (@a:=a) PERSISTENT);
+--error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
 create table t5 (a int, b int, v varchar(100) as (monthname(a)));
+--error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
 create table t6 (a int, b int, v varchar(100) as (dayname(a)));
+--error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
 create table t7 (a int, b int, v varchar(100) as (date_format(a, '%W %a %M %b')));
 create table t8 (a int, b int, v varchar(100) as (from_unixtime(a)));
 
 insert t1 (a,b) values (1,2);
-insert t5 (a,b) values (20141010,2);
-insert t6 (a,b) values (20141010,2);
-insert t7 (a,b) values (20141010,2);
 insert t8 (a,b) values (1234567890,2);
 
 select * from t1;
-select * from t5;
-select * from t6;
-select * from t7;
 select * from t8;
 
 disconnect con1;
@@ -41,18 +43,11 @@ connection default;
 set time_zone='+1:00';
 
 select * from t1;
-select * from t5;
-select * from t6;
-select * from t7;
 select * from t8;
 
 flush tables;
 
 select * from t1;
-select * from t5;
-select * from t6;
-select * from t7;
 select * from t8;
 
-drop table t1, t5, t6, t7, t8;
-
+drop table t1, t3_ok, t8;
diff --git a/mysql-test/suite/vcol/t/vcol_blocked_sql_funcs_myisam.test b/mysql-test/suite/vcol/t/vcol_blocked_sql_funcs.test
index d8eddb4ef15..3e9e5936476 100644
--- a/mysql-test/suite/vcol/t/vcol_blocked_sql_funcs_myisam.test
+++ b/mysql-test/suite/vcol/t/vcol_blocked_sql_funcs.test
@@ -39,7 +39,7 @@ eval SET @@session.storage_engine = 'MyISAM';
 
 #------------------------------------------------------------------------------#
 # Execute the tests to be applied to all storage engines
---source suite/vcol/inc/vcol_blocked_sql_funcs_main.inc
+--source vcol_blocked_sql_funcs_main.inc
 
 #------------------------------------------------------------------------------#
 # Execute storage engine specific tests
diff --git a/mysql-test/suite/vcol/t/vcol_blocked_sql_funcs_innodb.test b/mysql-test/suite/vcol/t/vcol_blocked_sql_funcs_innodb.test
deleted file mode 100644
index 516e121a2aa..00000000000
--- a/mysql-test/suite/vcol/t/vcol_blocked_sql_funcs_innodb.test
+++ /dev/null
@@ -1,52 +0,0 @@
-################################################################################
-# t/vcol_supported_sql_funcs.test                                              #
-#                                                                              #
-# Purpose:                                                                     #
-#  Test SQL functions not allowed for virtual columns                          #
-#        InnoDB branch                                                         #
-#                                                                              #
-#------------------------------------------------------------------------------#
-# Original Author: Andrey Zhakov                                               #
-# Original Date: 2008-08-31                                                    #
-# Change Author:                                                               #
-# Change Date:                                                                 #
-# Change:                                                                      #
-################################################################################
-
-#
-# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE !
-#       TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN
-#       THE SOURCED FILES ONLY.
-#
-
-#------------------------------------------------------------------------------#
-# General not engine specific settings and requirements
---source suite/vcol/inc/vcol_init_vars.pre
-
-#------------------------------------------------------------------------------#
-# Cleanup
---source suite/vcol/inc/vcol_cleanup.inc
-
-#------------------------------------------------------------------------------#
-# Engine specific settings and requirements
-
-##### Storage engine to be tested
-# Set the session storage engine
---source include/have_innodb.inc
-eval SET @@session.storage_engine = 'InnoDB';
-
-let $skip_full_text_checks = 1;
-
-##### Workarounds for known open engine specific bugs
-#     none
-
-#------------------------------------------------------------------------------#
-# Execute the tests to be applied to all storage engines
---source suite/vcol/inc/vcol_blocked_sql_funcs_main.inc
-
-#------------------------------------------------------------------------------#
-# Execute storage engine specific tests
-
-#------------------------------------------------------------------------------#
-# Cleanup
---source suite/vcol/inc/vcol_cleanup.inc
diff --git a/mysql-test/suite/vcol/t/vcol_blocked_sql_funcs_main.inc b/mysql-test/suite/vcol/t/vcol_blocked_sql_funcs_main.inc
new file mode 100644
index 00000000000..625a40aca54
--- /dev/null
+++ b/mysql-test/suite/vcol/t/vcol_blocked_sql_funcs_main.inc
@@ -0,0 +1,381 @@
+################################################################################
+# inc/vcol_blocked_sql_funcs_main.inc                                          #
+#                                                                              #
+# Purpose:                                                                     #
+#  Tests around sql functions                                                  #
+#                                                                              #
+#                                                                              #
+#------------------------------------------------------------------------------#
+# Original Author: Andrey Zhakov                                               #
+# Original Date: 2008-08-31                                                    #
+# Change Author: Oleksandr Byelkin (Monty program Ab)
+# Date: 2009-03-24 
+# Change: Syntax changed
+################################################################################
+
+#
+# NOTE:  All SQL functions should be rejected, otherwise BUG.
+# As PERSISTANT has higher level checks than VIRTUAL, we use VIRTUAL
+# to check for things that should not work for either VIRTUAL or PERSISTENT
+#
+
+--echo # RAND()
+create or replace table t1 (b double as (rand()));
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (b double as (rand()) PERSISTENT);
+
+--echo # LOAD_FILE()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a varchar(64), b varchar(1024) as (load_file(a)));
+
+--echo # CURDATE()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a datetime as (curdate()) PERSISTENT);
+
+--echo # CURRENT_DATE(), CURRENT_DATE
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a datetime as (current_date) PERSISTENT);
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a datetime as (current_date()) PERSISTENT);
+
+--echo # CURRENT_TIME(), CURRENT_TIME
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a datetime as (current_time) PERSISTENT);
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a datetime as (current_time()) PERSISTENT);
+
+--echo # CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a datetime as (current_timestamp()) PERSISTENT);
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a datetime as (current_timestamp) PERSISTENT);
+
+--echo # CURTIME()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a datetime as (curtime()) PERSISTENT);
+
+--echo # LOCALTIME(), LOCALTIME
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a datetime, b varchar(10) as (localtime()) PERSISTENT); 
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a datetime, b varchar(10) as (localtime) PERSISTENT); 
+
+--echo # LOCALTIMESTAMP, LOCALTIMESTAMP()(v4.0.6)
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a datetime, b varchar(10) as (localtimestamp()) PERSISTENT);
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a datetime, b varchar(10) as (localtimestamp) PERSISTENT);
+
+--echo # NOW()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a datetime, b varchar(10) as (now()) PERSISTENT);
+
+--echo # SYSDATE()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a int, b varchar(10) as (sysdate()) PERSISTENT);
+
+--echo # UNIX_TIMESTAMP()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a datetime, b datetime as (unix_timestamp()) PERSISTENT);
+
+--echo # UTC_DATE()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a datetime, b datetime as (utc_date()) PERSISTENT);
+
+--echo # UTC_TIME()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a datetime, b datetime as (utc_time()) PERSISTENT);
+
+--echo # UTC_TIMESTAMP()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a datetime, b datetime as (utc_timestamp()) PERSISTENT);
+
+--echo # WEEK() - one argument version
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a datetime, b datetime as (week(a)) PERSISTENT);
+
+--echo # MATCH()
+--error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+  create or replace table t1 (a varchar(32), b bool as (match a against ('sample text')) PERSISTENT);
+
+--echo # BENCHMARK()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a varchar(1024), b varchar(1024) as (benchmark(a,3)));
+
+--echo # CHARSET()
+create or replace table t1 (a varchar(64), b varchar(64) as (charset(a)) PERSISTENT);
+
+--echo # COERCIBILITY()
+create or replace table t1 (a varchar(64), b int as (coercibility(a)) PERSISTENT);
+
+--echo # COLLATION()
+create or replace table t1 (a varchar(64), b varchar(64) as (collation(a)) PERSISTENT);
+
+--echo # CONNECTION_ID()
+create or replace table t1 (a int as (connection_id()));
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a int as (connection_id()) PERSISTENT);
+
+--echo # CURRENT_USER(), CURRENT_USER
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a varchar(32) as (current_user()));
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a varchar(32) as (current_user()) PERSISTENT);
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a varchar(32) as (current_user) PERSISTENT);
+
+--echo # DATABASE()
+create or replace table t1 (a varchar(32) as (database()));
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a varchar(1024), b varchar(1024) as (database()) PERSISTENT);
+
+--echo # FOUND_ROWS()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a varchar(1024), b varchar(1024) as (found_rows()));
+
+--echo # GET_LOCK()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a varchar(1024), b varchar(1024) as (get_lock(a,10)));
+
+--echo # IS_FREE_LOCK()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a varchar(1024), b varchar(1024) as (is_free_lock(a)));
+
+--echo # IS_USED_LOCK()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a varchar(1024), b varchar(1024) as (is_used_lock(a)));
+
+--echo # LAST_INSERT_ID()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a int as (last_insert_id()));
+
+--echo # MASTER_POS_WAIT()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a varchar(32), b int as (master_pos_wait(a,0,2)));
+
+--echo # NAME_CONST()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a varchar(32) as (name_const('test',1)));
+
+--echo # RELEASE_LOCK()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a varchar(32), b int as (release_lock(a)));
+
+--echo # ROW_COUNT()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a int as (row_count()));
+
+--echo # SCHEMA()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a varchar(32) as (schema()) PERSISTENT);
+
+--echo # SESSION_USER()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a varchar(32) as (session_user()) PERSISTENT);
+
+--echo # SLEEP()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a int, b int as (sleep(a)));
+
+--echo # SYSTEM_USER()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a varchar(32) as (system_user()) PERSISTENT);
+
+--echo # USER()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a varchar(1024), b varchar(1024) as (user()) PERSISTENT);
+
+--echo # UUID_SHORT()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a varchar(1024) as (uuid_short()) PERSISTENT);
+
+--echo # UUID()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a varchar(1024) as (uuid()) PERSISTENT);
+
+--echo # VALUES()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a varchar(1024), b varchar(1024) as (values(a)));
+
+--echo # VERSION()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a varchar(1024), b varchar(1024) as (version()) PERSISTENT); 
+
+--echo # ENCRYPT()
+create or replace table t1 (a varchar(1024), b varchar(1024) as (encrypt(a)) PERSISTENT);
+
+--echo # DATE_FORMAT()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a datetime, b varchar(64) as (date_format(a,'%W %M %D'));
+
+--echo # Stored procedures
+
+delimiter //;
+create procedure p1()
+begin
+  select current_user();
+end //
+
+create function f1()
+returns int
+begin
+ return 1;
+end //
+
+delimiter ;//
+
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a int as (p1()) PERSISTENT);
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a int as (f1()) PERSISTENT);
+
+drop procedure p1;
+drop function f1;
+
+--echo # Unknown functions
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a int as (f1()) PERSISTENT);
+
+--echo #
+--echo # GROUP BY FUNCTIONS
+--echo #
+
+--echo # AVG()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a int, b int as (avg(a)));
+
+--echo # BIT_AND()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a int, b int as (bit_and(a)));
+
+--echo # BIT_OR()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a int, b int as (bit_or(a)));
+
+--echo # BIT_XOR()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a int, b int as (bit_xor(a)));
+
+--echo # COUNT(DISTINCT)
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a int, b int as (count(distinct a)));
+
+--echo # COUNT()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a int, b int as (count(a)));
+
+--echo # GROUP_CONCAT()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a varchar(32), b int as (group_concat(a,'')));
+
+--echo # MAX()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a int, b int as (max(a)));
+
+--echo # MIN()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a int, b int as (min(a)));
+
+--echo # STD()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a int, b int as (std(a)));
+
+--echo # STDDEV_POP()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a int, b int as (stddev_pop(a)));
+
+--echo # STDDEV_SAMP()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a int, b int as (stddev_samp(a)));
+
+--echo # STDDEV()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a int, b int as (stddev(a)));
+
+--echo # SUM()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a int, b int as (sum(a)));
+
+--echo # VAR_POP()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a int, b int as (var_pop(a)));
+
+--echo # VAR_SAMP()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a int, b int as (var_samp(a)));
+
+--echo # VARIANCE()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a int, b int as (variance(a)));
+
+--echo # DAYNAME()
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a int, b varchar(10) as (dayname(a)));
+
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a int, b varchar(10) as (monthname(a)));
+
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a int, b varchar(10) as (date_format("1963-01-01","%d.%m.%Y")));
+
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a int, b varchar(10) as (time_format(now(),"%d.%m.%Y")));
+
+--echo #
+--echo # XML FUNCTIONS
+--echo #
+
+--echo # ExtractValue()
+create or replace table t1 (a varchar(1024), b varchar(1024) as (ExtractValue(a,'//b[$@j]')) PERSISTENT);
+
+--echo # UpdateXML()
+create or replace table t1 (a varchar(1024), b varchar(1024) as (UpdateXML(a,'/a','<e>fff</e>')) PERSISTENT);
+
+--echo #
+--echo # Sub-selects
+--echo #
+
+create or replace table t1 (a int);
+-- error ER_PARSE_ERROR
+create or replace table t2 (a int, b int as (select count(*) from t1));
+drop table t1;
+
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a int, b int as ((select 1)));
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a int, b int as (a+(select 1)));
+
+--echo #
+--echo # SP functions
+--echo #
+
+--disable_warnings
+drop function if exists sub1;
+--enable_warnings
+create function sub1(i int) returns int deterministic
+  return i+1;
+select sub1(1);
+-- error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create or replace table t1 (a int, b int as (a+sub3(1)));
+drop function sub1;
+
+--echo #
+--echo # Long expression
+
+let $tmp_long_string = `SELECT repeat('a',240)`;
+eval create or replace table t1 (a int, b varchar(300) as (concat(a,'$tmp_long_string')));
+drop table t1;
+let $tmp_long_string = `SELECT repeat('a',243)`;
+eval create or replace table t1 (a int, b varchar(16384) as (concat(a,'$tmp_long_string')));
+
+--disable_query_log
+let $tmp_long_string = `SELECT repeat('a',65535)`;
+--error ER_TOO_MANY_FIELDS
+eval create or replace table t1 (a int, b varchar(16384) as (concat(a,'$tmp_long_string')));
+--enable_query_log
+
+--echo #
+--echo # Constant expression
+create or replace table t1 (a int as (PI()) PERSISTENT);
+
+drop table if exists t1;
diff --git a/mysql-test/suite/vcol/t/vcol_misc.test b/mysql-test/suite/vcol/t/vcol_misc.test
index c78093bce0f..4ca9562221c 100644
--- a/mysql-test/suite/vcol/t/vcol_misc.test
+++ b/mysql-test/suite/vcol/t/vcol_misc.test
@@ -26,7 +26,7 @@ drop table t1;
 # Bug#604549: Expression for virtual column returns row
 #             
 
--- error ER_ROW_EXPR_FOR_VCOL 
+-- error ER_OPERAND_COLUMNS
 CREATE TABLE t1 (
   a int NOT NULL DEFAULT '0',
   v double AS ((1, a)) VIRTUAL
@@ -279,9 +279,12 @@ drop table t1;
 --echo # MDEV-5611: self-referencing virtual column
 --echo #
 
---error ER_VCOL_BASED_ON_VCOL
+--error ER_EXPRESSION_REFERS_TO_UNINIT_FIELD
 create table t1 (a int, b int as (b is null) virtual);
 
+create table t1 (a int as (1+1), b int as (a is null) virtual);
+drop table t1;
+
 --echo # end of 5.3 tests
 
 #
diff --git a/mysql-test/suite/vcol/t/vcol_supported_sql_funcs_myisam.test b/mysql-test/suite/vcol/t/vcol_supported_sql_funcs.test
index aa5833451c6..d1e21af059b 100644
--- a/mysql-test/suite/vcol/t/vcol_supported_sql_funcs_myisam.test
+++ b/mysql-test/suite/vcol/t/vcol_supported_sql_funcs.test
@@ -39,7 +39,7 @@ eval SET @@session.storage_engine = 'MyISAM';
 
 #------------------------------------------------------------------------------#
 # Execute the tests to be applied to all storage engines
---source suite/vcol/inc/vcol_supported_sql_funcs_main.inc
+--source vcol_supported_sql_funcs_main.inc
 
 #------------------------------------------------------------------------------#
 # Execute storage engine specific tests
diff --git a/mysql-test/suite/vcol/t/vcol_supported_sql_funcs_innodb.test b/mysql-test/suite/vcol/t/vcol_supported_sql_funcs_innodb.test
deleted file mode 100644
index 53826a460a7..00000000000
--- a/mysql-test/suite/vcol/t/vcol_supported_sql_funcs_innodb.test
+++ /dev/null
@@ -1,50 +0,0 @@
-################################################################################
-# t/vcol_supported_sql_funcs.test                                              #
-#                                                                              #
-# Purpose:                                                                     #
-#  Test SQL functions allowed for virtual columns                              #
-#        InnoDB branch                                                         #
-#                                                                              #
-#------------------------------------------------------------------------------#
-# Original Author: Andrey Zhakov                                               #
-# Original Date: 2008-08-31                                                    #
-# Change Author:                                                               #
-# Change Date:                                                                 #
-# Change:                                                                      #
-################################################################################
-
-#
-# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE !
-#       TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN
-#       THE SOURCED FILES ONLY.
-#
-
-#------------------------------------------------------------------------------#
-# General not engine specific settings and requirements
---source suite/vcol/inc/vcol_init_vars.pre
-
-#------------------------------------------------------------------------------#
-# Cleanup
---source suite/vcol/inc/vcol_cleanup.inc
-
-#------------------------------------------------------------------------------#
-# Engine specific settings and requirements
-
-##### Storage engine to be tested
-# Set the session storage engine
---source include/have_innodb.inc
-SET @@session.storage_engine = 'InnoDB';
-
-##### Workarounds for known open engine specific bugs
-#     none
-
-#------------------------------------------------------------------------------#
-# Execute the tests to be applied to all storage engines
---source suite/vcol/inc/vcol_supported_sql_funcs_main.inc
-
-#------------------------------------------------------------------------------#
-# Execute storage engine specific tests
-
-#------------------------------------------------------------------------------#
-# Cleanup
---source suite/vcol/inc/vcol_cleanup.inc
diff --git a/mysql-test/suite/vcol/inc/vcol_supported_sql_funcs_main.inc b/mysql-test/suite/vcol/t/vcol_supported_sql_funcs_main.inc
index 38f2b00a1a0..cf3183d979d 100644
--- a/mysql-test/suite/vcol/inc/vcol_supported_sql_funcs_main.inc
+++ b/mysql-test/suite/vcol/t/vcol_supported_sql_funcs_main.inc
@@ -842,12 +842,6 @@ let $values1 = '2008-08-31',default;
 let $rows = 1;
 --source suite/vcol/inc/vcol_supported_sql_funcs.inc
 
---echo # DATE_FORMAT()
-let $cols = a datetime, b varchar(64) as (date_format(a,'%W %M %D'));
-let $values1 = '2008-08-31',default;
-let $rows = 1;
---source suite/vcol/inc/vcol_supported_sql_funcs.inc
-
 --echo # DATE_SUB()
 let $cols = a datetime, b datetime as (date_sub(a,interval 1 month));
 let $values1 = '2008-08-31',default;
@@ -872,12 +866,6 @@ let $values1 = '2008-08-31',default;
 let $rows = 1;
 --source suite/vcol/inc/vcol_supported_sql_funcs.inc
 
---echo # DAYNAME()
-let $cols = a datetime, b varchar(10) as (dayname(a));
-let $values1 = '2008-08-31',default;
-let $rows = 1;
---source suite/vcol/inc/vcol_supported_sql_funcs.inc
-
 --echo # DAYOFMONTH()
 let $cols = a datetime, b int as (dayofmonth(a));
 let $values1 = '2008-08-31',default;
@@ -915,12 +903,6 @@ let $rows = 1;
 set time_zone='UTC';
 --source suite/vcol/inc/vcol_supported_sql_funcs.inc
 
---echo # GET_FORMAT()
-let $cols = a datetime, b varchar(32) as (date_format(a,get_format(DATE,'EUR')));
-let $values1 = '2008-08-31',default;
-let $rows = 1;
---source suite/vcol/inc/vcol_supported_sql_funcs.inc
-
 --echo # HOUR()
 let $cols = a time, b long as (hour(a));
 let $values1 = '10:05:03',default;
@@ -965,12 +947,6 @@ let $values1 = '2009-12-31 23:59:59.000010',default;
 let $rows = 1;
 --source suite/vcol/inc/vcol_supported_sql_funcs.inc
 
---echo # MONTHNAME()
-let $cols = a datetime, b varchar(16) as (monthname(a));
-let $values1 = '2009-12-31 23:59:59.000010',default;
-let $rows = 1;
---source suite/vcol/inc/vcol_supported_sql_funcs.inc
-
 --echo # PERIOD_ADD()
 let $cols = a int, b int as (period_add(a,2));
 let $values1 = 200801,default;
@@ -1019,12 +995,6 @@ let $values1 = '2008-08-31',default;
 let $rows = 1;
 --source suite/vcol/inc/vcol_supported_sql_funcs.inc
 
---echo # TIME_FORMAT()
-let $cols = a datetime, b varchar(32) as (time_format(a,'%r'));
-let $values1 = '2008-08-31 02:03:04',default;
-let $rows = 1;
---source suite/vcol/inc/vcol_supported_sql_funcs.inc
-
 --echo # TIME_TO_SEC()
 let $cols = a time, b long as (time_to_sec(a));
 let $values1 = '22:23:00',default;