Debian Codegraph @

mysql-test/suite/vcol/inc/vcol_column_def_options.inc

raw
1 ################################################################################
2 # inc/vcol_column_def_options.inc #
3 # #
4 # Purpose: #
5 # Testing different optional parameters specified when defining #
6 # a virtual column. #
7 # #
8 # #
9 #------------------------------------------------------------------------------#
10 # Original Author: Andrey Zhakov #
11 # Original Date: 2008-09-02 #
12 # Change Author: Oleksandr Byelkin (Monty program Ab)
13 # Date: 2009-03-24
14 # Change: Syntax changed
15 ################################################################################
16
17 --echo #
18 --echo # Section 1. Wrong column definition options
19 --echo # - NOT NULL
20 --echo # - NULL
21 --echo # - DEFAULT <value>
22 --echo # - AUTO_INCREMENT
23 --echo # - [PRIMARY] KEY
24
25 --echo # NOT NULL
26 --error ER_PARSE_ERROR
27 create table t1 (a int, b int as (a+1) not null);
28 create table t1 (a int);
29 --error ER_PARSE_ERROR
30 alter table t1 add column b int as (a+1) not null;
31 drop table t1;
32
33 --echo # NULL
34 --error ER_PARSE_ERROR
35 create table t1 (a int, b int as (a+1) null);
36 create table t1 (a int);
37 --error ER_PARSE_ERROR
38 alter table t1 add column b int as (a+1) null;
39 drop table t1;
40
41 --echo # DEFAULT
42 --error ER_PARSE_ERROR
43 create table t1 (a int, b int as (a+1) default 0);
44 create table t1 (a int);
45 --error ER_PARSE_ERROR
46 alter table t1 add column b int as (a+1) default 0;
47 drop table t1;
48
49 --echo # AUTO_INCREMENT
50 --error ER_PARSE_ERROR
51 create table t1 (a int, b int as (a+1) AUTO_INCREMENT);
52 create table t1 (a int);
53 --error ER_PARSE_ERROR
54 alter table t1 add column b int as (a+1) AUTO_INCREMENT;
55 drop table t1;
56
57 --echo # [PRIMARY] KEY
58 --error ER_PARSE_ERROR
59 create table t1 (a int, b int as (a+1) key);
60 --error ER_PARSE_ERROR
61 create table t1 (a int, b int as (a+1) primary key);
62 create table t1 (a int);
63 --error ER_PARSE_ERROR
64 alter table t1 add column b int as (a+1) key;
65 --error ER_PARSE_ERROR
66 alter table t1 add column b int as (a+1) primary key;
67 drop table t1;
68
69 --echo # Section 2. Other column definition options
70 --echo # - COMMENT
71 --echo # - REFERENCES (only syntax testing here)
72 --echo # - STORED (only systax testing here)
73 create table t1 (a int, b int as (a % 2) comment 'my comment');
74 show create table t1;
75 describe t1;
76 drop table t1;
77 create table t1 (a int, b int as (a % 2));
78 alter table t1 modify b int as (a % 2) comment 'my comment';
79 show create table t1;
80 describe t1;
81 insert into t1 (a) values (1);
82 select * from t1;
83 insert into t1 values (2,default);
84 select a,b from t1;
85 create table t2 like t1;
86 show create table t2;
87 describe t2;
88 insert into t2 (a) values (1);
89 select * from t2;
90 insert into t2 values (2,default);
91 select a,b from t2;
92 drop table t2;
93 drop table t1;
94
95 create table t1 (a int, b int as (a % 2) persistent);
96 show create table t1;
97 describe t1;
98 insert into t1 (a) values (1);
99 select * from t1;
100 insert into t1 values (2,default);
101 select a,b from t1;
102 drop table t1;
103
104
105 create table t2 (a int);
106 create table t1 (a int, b int as (a % 2) persistent);
107 show create table t1;
108 drop table t1;
109 create table t1 (a int, b int as (a % 2));
110 --error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN
111 alter table t1 modify b int as (a % 2) persistent;
112 show create table t1;
113 drop table t1;