summaryrefslogtreecommitdiff
path: root/mysql-test/suite/vcol
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite/vcol')
-rw-r--r--mysql-test/suite/vcol/r/vcol_blocked_sql_funcs.result6
-rw-r--r--mysql-test/suite/vcol/r/vcol_column_def_options_innodb.result12
-rw-r--r--mysql-test/suite/vcol/r/vcol_column_def_options_myisam.result12
-rw-r--r--mysql-test/suite/vcol/r/vcol_misc.result2
-rw-r--r--mysql-test/suite/vcol/r/vcol_non_stored_columns_innodb.result4
-rw-r--r--mysql-test/suite/vcol/r/vcol_non_stored_columns_myisam.result4
-rw-r--r--mysql-test/suite/vcol/r/vcol_sql_mode.result4
-rw-r--r--mysql-test/suite/vcol/r/vcol_supported_sql_funcs.result25
-rw-r--r--mysql-test/suite/vcol/r/vcol_view_innodb.result2
-rw-r--r--mysql-test/suite/vcol/r/vcol_view_myisam.result2
-rw-r--r--mysql-test/suite/vcol/t/vcol_blocked_sql_funcs_main.inc5
-rw-r--r--mysql-test/suite/vcol/t/vcol_supported_sql_funcs_main.inc6
12 files changed, 55 insertions, 29 deletions
diff --git a/mysql-test/suite/vcol/r/vcol_blocked_sql_funcs.result b/mysql-test/suite/vcol/r/vcol_blocked_sql_funcs.result
index 6605f7c99b4..19e8efb06ad 100644
--- a/mysql-test/suite/vcol/r/vcol_blocked_sql_funcs.result
+++ b/mysql-test/suite/vcol/r/vcol_blocked_sql_funcs.result
@@ -127,8 +127,8 @@ ERROR HY000: Function or expression 'uuid_short()' cannot be used in the GENERAT
create or replace table t1 (a varchar(1024) as (uuid()) PERSISTENT);
ERROR HY000: Function or expression 'uuid()' cannot be used in the GENERATED ALWAYS AS clause of `a`
# VALUES()
-create or replace table t1 (a varchar(1024), b varchar(1024) as (values(a)));
-ERROR HY000: Function or expression 'values()' cannot be used in the GENERATED ALWAYS AS clause of `b`
+create or replace table t1 (a varchar(1024), b varchar(1024) as (value(a)));
+ERROR HY000: Function or expression 'value()' cannot be used in the GENERATED ALWAYS AS clause of `b`
# VERSION()
create or replace table t1 (a varchar(1024), b varchar(1024) as (version()) PERSISTENT);
ERROR HY000: Function or expression 'version()' cannot be used in the GENERATED ALWAYS AS clause of `b`
@@ -247,3 +247,5 @@ ERROR HY000: Expression in the GENERATED ALWAYS AS clause is too big
# Constant expression
create or replace table t1 (a int as (PI()) PERSISTENT);
drop table if exists t1;
+create table t1 (a timestamp, b varchar(255) as (date_format(a, '%w %a %m %b')) stored);
+ERROR HY000: Function or expression 'date_format()' cannot be used in the GENERATED ALWAYS AS clause of `b`
diff --git a/mysql-test/suite/vcol/r/vcol_column_def_options_innodb.result b/mysql-test/suite/vcol/r/vcol_column_def_options_innodb.result
index 8bfb8f0429c..0f80365f6a7 100644
--- a/mysql-test/suite/vcol/r/vcol_column_def_options_innodb.result
+++ b/mysql-test/suite/vcol/r/vcol_column_def_options_innodb.result
@@ -54,7 +54,7 @@ show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
- `b` int(11) GENERATED ALWAYS AS (`a` % 2) VIRTUAL COMMENT 'my comment'
+ `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) VIRTUAL COMMENT 'my comment'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
describe t1;
Field Type Null Key Default Extra
@@ -67,7 +67,7 @@ show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
- `b` int(11) GENERATED ALWAYS AS (`a` % 2) VIRTUAL COMMENT 'my comment'
+ `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) VIRTUAL COMMENT 'my comment'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
describe t1;
Field Type Null Key Default Extra
@@ -87,7 +87,7 @@ show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`a` int(11) DEFAULT NULL,
- `b` int(11) GENERATED ALWAYS AS (`a` % 2) VIRTUAL COMMENT 'my comment'
+ `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) VIRTUAL COMMENT 'my comment'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
describe t2;
Field Type Null Key Default Extra
@@ -109,7 +109,7 @@ show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
- `b` int(11) GENERATED ALWAYS AS (`a` % 2) STORED
+ `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) STORED
) ENGINE=InnoDB DEFAULT CHARSET=latin1
describe t1;
Field Type Null Key Default Extra
@@ -131,7 +131,7 @@ show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
- `b` int(11) GENERATED ALWAYS AS (`a` % 2) STORED
+ `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) STORED
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
create table t1 (a int, b int as (a % 2));
@@ -141,6 +141,6 @@ show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
- `b` int(11) GENERATED ALWAYS AS (`a` % 2) VIRTUAL
+ `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) VIRTUAL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
diff --git a/mysql-test/suite/vcol/r/vcol_column_def_options_myisam.result b/mysql-test/suite/vcol/r/vcol_column_def_options_myisam.result
index 96eb2bdc02f..e53a7594663 100644
--- a/mysql-test/suite/vcol/r/vcol_column_def_options_myisam.result
+++ b/mysql-test/suite/vcol/r/vcol_column_def_options_myisam.result
@@ -54,7 +54,7 @@ show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
- `b` int(11) GENERATED ALWAYS AS (`a` % 2) VIRTUAL COMMENT 'my comment'
+ `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) VIRTUAL COMMENT 'my comment'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
describe t1;
Field Type Null Key Default Extra
@@ -67,7 +67,7 @@ show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
- `b` int(11) GENERATED ALWAYS AS (`a` % 2) VIRTUAL COMMENT 'my comment'
+ `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) VIRTUAL COMMENT 'my comment'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
describe t1;
Field Type Null Key Default Extra
@@ -87,7 +87,7 @@ show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`a` int(11) DEFAULT NULL,
- `b` int(11) GENERATED ALWAYS AS (`a` % 2) VIRTUAL COMMENT 'my comment'
+ `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) VIRTUAL COMMENT 'my comment'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
describe t2;
Field Type Null Key Default Extra
@@ -109,7 +109,7 @@ show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
- `b` int(11) GENERATED ALWAYS AS (`a` % 2) STORED
+ `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) STORED
) ENGINE=MyISAM DEFAULT CHARSET=latin1
describe t1;
Field Type Null Key Default Extra
@@ -131,7 +131,7 @@ show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
- `b` int(11) GENERATED ALWAYS AS (`a` % 2) STORED
+ `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) STORED
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 (a int, b int as (a % 2));
@@ -141,6 +141,6 @@ show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
- `b` int(11) GENERATED ALWAYS AS (`a` % 2) VIRTUAL
+ `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
diff --git a/mysql-test/suite/vcol/r/vcol_misc.result b/mysql-test/suite/vcol/r/vcol_misc.result
index a9b5bc29018..90dee4a43dc 100644
--- a/mysql-test/suite/vcol/r/vcol_misc.result
+++ b/mysql-test/suite/vcol/r/vcol_misc.result
@@ -284,7 +284,7 @@ Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) NOT NULL,
`b` varchar(32) DEFAULT NULL,
- `c` int(11) GENERATED ALWAYS AS (`a` % 10) VIRTUAL,
+ `c` int(11) GENERATED ALWAYS AS (`a` MOD 10) VIRTUAL,
`d` varchar(5) GENERATED ALWAYS AS (left(`b`,5)) STORED
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show columns from t1;
diff --git a/mysql-test/suite/vcol/r/vcol_non_stored_columns_innodb.result b/mysql-test/suite/vcol/r/vcol_non_stored_columns_innodb.result
index f4cbb5bd662..84817469fa2 100644
--- a/mysql-test/suite/vcol/r/vcol_non_stored_columns_innodb.result
+++ b/mysql-test/suite/vcol/r/vcol_non_stored_columns_innodb.result
@@ -81,7 +81,7 @@ show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
- `b` int(11) GENERATED ALWAYS AS (`a` % 2) STORED
+ `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) STORED
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
# Case 8. ALTER. Modify virtual non-stored -> virtual stored
@@ -92,7 +92,7 @@ show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
- `b` int(11) GENERATED ALWAYS AS (`a` % 2) VIRTUAL
+ `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) VIRTUAL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
# Case 9. CREATE LIKE
diff --git a/mysql-test/suite/vcol/r/vcol_non_stored_columns_myisam.result b/mysql-test/suite/vcol/r/vcol_non_stored_columns_myisam.result
index 87bd1bcf530..b4b41635110 100644
--- a/mysql-test/suite/vcol/r/vcol_non_stored_columns_myisam.result
+++ b/mysql-test/suite/vcol/r/vcol_non_stored_columns_myisam.result
@@ -81,7 +81,7 @@ show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
- `b` int(11) GENERATED ALWAYS AS (`a` % 2) STORED
+ `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) STORED
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
# Case 8. ALTER. Modify virtual non-stored -> virtual stored
@@ -92,7 +92,7 @@ show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
- `b` int(11) GENERATED ALWAYS AS (`a` % 2) VIRTUAL
+ `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
# Case 9. CREATE LIKE
diff --git a/mysql-test/suite/vcol/r/vcol_sql_mode.result b/mysql-test/suite/vcol/r/vcol_sql_mode.result
index d78a290a7c2..385939fd037 100644
--- a/mysql-test/suite/vcol/r/vcol_sql_mode.result
+++ b/mysql-test/suite/vcol/r/vcol_sql_mode.result
@@ -340,11 +340,11 @@ DROP TABLE t1;
# PAD_CHAR_TO_FULL_LENGTH + LENGTH(char_column) = hard dependency
CREATE TABLE t1 (a CHAR(5), v INT AS (LENGTH(a)) VIRTUAL, KEY(v));
Warnings:
-Warning 1901 Function or expression 'length(`a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
+Warning 1901 Function or expression 'octet_length(`a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
SHOW WARNINGS;
Level Code Message
-Warning 1901 Function or expression 'length(`a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
+Warning 1901 Function or expression 'octet_length(`a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
DROP TABLE t1;
#
diff --git a/mysql-test/suite/vcol/r/vcol_supported_sql_funcs.result b/mysql-test/suite/vcol/r/vcol_supported_sql_funcs.result
index d4104c2c57e..10ab32f9486 100644
--- a/mysql-test/suite/vcol/r/vcol_supported_sql_funcs.result
+++ b/mysql-test/suite/vcol/r/vcol_supported_sql_funcs.result
@@ -393,7 +393,7 @@ show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
- `b` int(11) GENERATED ALWAYS AS (`a` % 10) VIRTUAL
+ `b` int(11) GENERATED ALWAYS AS (`a` MOD 10) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (1,default);
insert ignore into t1 values (11,default);
@@ -410,7 +410,7 @@ show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
- `b` int(11) GENERATED ALWAYS AS (`a` % 10) VIRTUAL
+ `b` int(11) GENERATED ALWAYS AS (`a` MOD 10) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (1,default);
insert ignore into t1 values (11,default);
@@ -915,7 +915,7 @@ Table Create Table
t1 CREATE TABLE `t1` (
`a` varchar(10) DEFAULT NULL,
`b` varchar(10) DEFAULT NULL,
- `c` varchar(20) GENERATED ALWAYS AS (insert(`a`,length(`a`),length(`b`),`b`)) VIRTUAL
+ `c` varchar(20) GENERATED ALWAYS AS (insert(`a`,octet_length(`a`),octet_length(`b`),`b`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('start,','end',default);
select * from t1;
@@ -978,7 +978,7 @@ show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` varchar(10) DEFAULT NULL,
- `b` int(11) GENERATED ALWAYS AS (length(`a`)) VIRTUAL
+ `b` int(11) GENERATED ALWAYS AS (octet_length(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('text',default);
select * from t1;
@@ -1142,7 +1142,7 @@ show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` varchar(10) DEFAULT NULL,
- `b` int(11) GENERATED ALWAYS AS (length(`a`)) VIRTUAL
+ `b` int(11) GENERATED ALWAYS AS (octet_length(`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('text',default);
select * from t1;
@@ -2894,6 +2894,21 @@ a b
2012-12-12 Wednesday Wed December Dec
drop table t1;
set sql_warnings = 0;
+# DATE_FORMAT() STORED
+set sql_warnings = 1;
+create table t1 (a date, b varchar(100) as (date_format(a, '%W %a %M %b', 'de_DE')) STORED);
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` date DEFAULT NULL,
+ `b` varchar(100) GENERATED ALWAYS AS (date_format(`a`,'%W %a %M %b','de_DE')) STORED
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert into t1 values ('2012-12-12',default);
+select * from t1;
+a b
+2012-12-12 Mittwoch Mi Dezember Dez
+drop table t1;
+set sql_warnings = 0;
# CURRENT_USER()
set sql_warnings = 1;
create table t1 (a char, b varchar(32) as (current_user()));
diff --git a/mysql-test/suite/vcol/r/vcol_view_innodb.result b/mysql-test/suite/vcol/r/vcol_view_innodb.result
index 091cdc02fb3..b5d722f4d58 100644
--- a/mysql-test/suite/vcol/r/vcol_view_innodb.result
+++ b/mysql-test/suite/vcol/r/vcol_view_innodb.result
@@ -35,7 +35,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 100.00
2 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00
Warnings:
-Note 1003 select `v2`.`d` AS `d`,`v2`.`e` AS `e` from `test`.`v2`
+Note 1003 /* select#1 */ select `v2`.`d` AS `d`,`v2`.`e` AS `e` from `test`.`v2`
create view v3 (d,e) as select d*2, e*2 from v1;
select * from v3;
d e
diff --git a/mysql-test/suite/vcol/r/vcol_view_myisam.result b/mysql-test/suite/vcol/r/vcol_view_myisam.result
index 8ad1853faa4..8f86774c418 100644
--- a/mysql-test/suite/vcol/r/vcol_view_myisam.result
+++ b/mysql-test/suite/vcol/r/vcol_view_myisam.result
@@ -35,7 +35,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 100.00
2 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00
Warnings:
-Note 1003 select `v2`.`d` AS `d`,`v2`.`e` AS `e` from `test`.`v2`
+Note 1003 /* select#1 */ select `v2`.`d` AS `d`,`v2`.`e` AS `e` from `test`.`v2`
create view v3 (d,e) as select d*2, e*2 from v1;
select * from v3;
d e
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
index 766d0c7410c..52324176694 100644
--- a/mysql-test/suite/vcol/t/vcol_blocked_sql_funcs_main.inc
+++ b/mysql-test/suite/vcol/t/vcol_blocked_sql_funcs_main.inc
@@ -187,7 +187,7 @@ create or replace table t1 (a varchar(1024) as (uuid()) PERSISTENT);
--echo # VALUES()
-- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create or replace table t1 (a varchar(1024), b varchar(1024) as (values(a)));
+create or replace table t1 (a varchar(1024), b varchar(1024) as (value(a)));
--echo # VERSION()
-- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
@@ -354,3 +354,6 @@ eval create or replace table t1 (a int, b varchar(16384) as (concat(a,'$tmp_long
create or replace table t1 (a int as (PI()) PERSISTENT);
drop table if exists t1;
+
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create table t1 (a timestamp, b varchar(255) as (date_format(a, '%w %a %m %b')) stored);
diff --git a/mysql-test/suite/vcol/t/vcol_supported_sql_funcs_main.inc b/mysql-test/suite/vcol/t/vcol_supported_sql_funcs_main.inc
index dafc42098dd..fa2162e5981 100644
--- a/mysql-test/suite/vcol/t/vcol_supported_sql_funcs_main.inc
+++ b/mysql-test/suite/vcol/t/vcol_supported_sql_funcs_main.inc
@@ -1197,6 +1197,12 @@ let $values1 = '2012-12-12',default;
let $rows = 1;
--source suite/vcol/inc/vcol_supported_sql_funcs.inc
+--echo # DATE_FORMAT() STORED
+let $cols = a date, b varchar(100) as (date_format(a, '%W %a %M %b', 'de_DE')) STORED;
+let $values1 = '2012-12-12',default;
+let $rows = 1;
+--source suite/vcol/inc/vcol_supported_sql_funcs.inc
+
--echo # CURRENT_USER()
let $cols = a char, b varchar(32) as (current_user());
let $values1 = 'a', default;
View Lorry Controller Status