Debian Codegraph @

mysql-test/suite/vcol/r/vcol_column_def_options_innodb.result

raw
1 SET @@session.default_storage_engine = 'InnoDB';
2 #
3 # Section 1. Wrong column definition options
4 # - NOT NULL
5 # - NULL
6 # - DEFAULT <value>
7 # - AUTO_INCREMENT
8 # - [PRIMARY] KEY
9 # NOT NULL
10 create table t1 (a int, b int as (a+1) not null);
11 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 'not null)' at line 1
12 create table t1 (a int);
13 alter table t1 add column b int as (a+1) not null;
14 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 'not null' at line 1
15 drop table t1;
16 # NULL
17 create table t1 (a int, b int as (a+1) null);
18 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 'null)' at line 1
19 create table t1 (a int);
20 alter table t1 add column b int as (a+1) null;
21 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 'null' at line 1
22 drop table t1;
23 # DEFAULT
24 create table t1 (a int, b int as (a+1) default 0);
25 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 'default 0)' at line 1
26 create table t1 (a int);
27 alter table t1 add column b int as (a+1) default 0;
28 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 'default 0' at line 1
29 drop table t1;
30 # AUTO_INCREMENT
31 create table t1 (a int, b int as (a+1) AUTO_INCREMENT);
32 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 'AUTO_INCREMENT)' at line 1
33 create table t1 (a int);
34 alter table t1 add column b int as (a+1) AUTO_INCREMENT;
35 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 'AUTO_INCREMENT' at line 1
36 drop table t1;
37 # [PRIMARY] KEY
38 create table t1 (a int, b int as (a+1) key);
39 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 'key)' at line 1
40 create table t1 (a int, b int as (a+1) primary key);
41 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 'primary key)' at line 1
42 create table t1 (a int);
43 alter table t1 add column b int as (a+1) key;
44 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 'key' at line 1
45 alter table t1 add column b int as (a+1) primary key;
46 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 'primary key' at line 1
47 drop table t1;
48 # Section 2. Other column definition options
49 # - COMMENT
50 # - REFERENCES (only syntax testing here)
51 # - STORED (only systax testing here)
52 create table t1 (a int, b int as (a % 2) comment 'my comment');
53 show create table t1;
54 Table Create Table
55 t1 CREATE TABLE `t1` (
56 `a` int(11) DEFAULT NULL,
57 `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) VIRTUAL COMMENT 'my comment'
58 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
59 describe t1;
60 Field Type Null Key Default Extra
61 a int(11) YES NULL
62 b int(11) YES NULL VIRTUAL GENERATED
63 drop table t1;
64 create table t1 (a int, b int as (a % 2));
65 alter table t1 modify b int as (a % 2) comment 'my comment';
66 show create table t1;
67 Table Create Table
68 t1 CREATE TABLE `t1` (
69 `a` int(11) DEFAULT NULL,
70 `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) VIRTUAL COMMENT 'my comment'
71 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
72 describe t1;
73 Field Type Null Key Default Extra
74 a int(11) YES NULL
75 b int(11) YES NULL VIRTUAL GENERATED
76 insert into t1 (a) values (1);
77 select * from t1;
78 a b
79 1 1
80 insert into t1 values (2,default);
81 select a,b from t1;
82 a b
83 1 1
84 2 0
85 create table t2 like t1;
86 show create table t2;
87 Table Create Table
88 t2 CREATE TABLE `t2` (
89 `a` int(11) DEFAULT NULL,
90 `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) VIRTUAL COMMENT 'my comment'
91 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
92 describe t2;
93 Field Type Null Key Default Extra
94 a int(11) YES NULL
95 b int(11) YES NULL VIRTUAL GENERATED
96 insert into t2 (a) values (1);
97 select * from t2;
98 a b
99 1 1
100 insert into t2 values (2,default);
101 select a,b from t2;
102 a b
103 1 1
104 2 0
105 drop table t2;
106 drop table t1;
107 create table t1 (a int, b int as (a % 2) persistent);
108 show create table t1;
109 Table Create Table
110 t1 CREATE TABLE `t1` (
111 `a` int(11) DEFAULT NULL,
112 `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) STORED
113 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
114 describe t1;
115 Field Type Null Key Default Extra
116 a int(11) YES NULL
117 b int(11) YES NULL STORED GENERATED
118 insert into t1 (a) values (1);
119 select * from t1;
120 a b
121 1 1
122 insert into t1 values (2,default);
123 select a,b from t1;
124 a b
125 1 1
126 2 0
127 drop table t1;
128 create table t2 (a int);
129 create table t1 (a int, b int as (a % 2) persistent);
130 show create table t1;
131 Table Create Table
132 t1 CREATE TABLE `t1` (
133 `a` int(11) DEFAULT NULL,
134 `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) STORED
135 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
136 drop table t1;
137 create table t1 (a int, b int as (a % 2));
138 alter table t1 modify b int as (a % 2) persistent;
139 ERROR HY000: This is not yet supported for generated columns
140 show create table t1;
141 Table Create Table
142 t1 CREATE TABLE `t1` (
143 `a` int(11) DEFAULT NULL,
144 `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) VIRTUAL
145 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
146 drop table t1;