diff options
Diffstat (limited to 'mysql-test/suite/vcol')
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; |