Debian Codegraph @

mysql-test/suite/vcol/t/vcol_syntax.test

raw
1 #
2 # test syntax
3 #
4 set @OLD_SQL_MODE=@@SESSION.SQL_MODE;
5 create table t1 (a int, b int generated always as (a+1));
6 show create table t1;
7 drop table t1;
8 create table t1 (a int, b int as (a+1) virtual);
9 show create table t1;
10 drop table t1;
11 create table t1 (a int, b int generated always as (a+1) persistent);
12 show create table t1;
13 drop table t1;
14
15 set session sql_mode='ORACLE';
16 create table t1 (a int, b int as (a+1));
17 show create table t1;
18 drop table t1;
19 create table t1 (a int, b int generated always as (a+1) virtual);
20 show create table t1;
21 drop table t1;
22 create table t1 (a int, b int as (a+1) persistent);
23 show create table t1;
24 drop table t1;
25 set session sql_mode=@OLD_SQL_MODE;
26
27 --echo #
28 --echo # MDEV-25091 CREATE TABLE: field references qualified by a wrong table name succeed
29 --echo #
30 create table t2 (x int);
31
32 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
33 create table t1 (x int, y int generated always as (t2.x));
34 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
35 create table t1 (x int, y int check (y > t2.x));
36 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
37 create table t1 (x int, y int default t2.x);
38 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
39 create table t1 (x int, check (t2.x > 0));
40
41 create table t1 (x int);
42 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
43 alter table t1 add column y int generated always as (t2.x);
44 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
45 alter table t1 add column y int check (z > t2.x);
46 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
47 alter table t1 add column y int default t2.x;
48 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
49 alter table t1 add constraint check (t2.x > 0);
50
51 create or replace table t1 (x int, y int generated always as (t1.x));
52 create or replace table t1 (x int, y int check (y > t1.x));
53 create or replace table t1 (x int, y int default t1.x);
54 create or replace table t1 (x int, check (t1.x > 0));
55
56 create or replace table t1 (x int, y int generated always as (test.t1.x));
57 create or replace table t1 (x int, y int check (y > test.t1.x));
58 create or replace table t1 (x int, y int default test.t1.x);
59 create or replace table t1 (x int, check (test.t1.x > 0));
60
61 drop tables t1, t2;
62
63 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
64 create table t1 (x int, y int generated always as (test2.t1.x));
65 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
66 create table t1 (x int, y int check (y > test2.t1.x));
67 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
68 create table t1 (x int, y int default test2.t1.x);
69 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
70 create table t1 (x int, check (test2.t1.x > 0));
71
72 --echo #
73 --echo # MDEV-25672 table alias from previous statement interferes later commands
74 --echo #
75 create table t1 (a int, v_a int generated always as (a));
76 update t1 as x set a = 1;
77 alter table t1 force;
78 drop table t1;
79
80 create table t1 (
81 id int not null auto_increment primary key,
82 order_date_time datetime not null,
83 order_date date generated always as (convert(order_date_time, date)),
84 language_id binary(16) null
85 );
86
87 update t1 as tx set order_date= null;
88 alter table t1 modify column language_id binary(16) not null;
89 # Cleanup
90 drop table t1;
91
92 --echo #
93 --echo # MDEV-24176 Server crashes after insert in the table with virtual column generated using date_format() and if()
94 --echo #
95 create table t1 (d1 date not null, d2 date not null,
96 gd text as (concat(d1,if(d1 <> d2, date_format(d2, 'to %y-%m-%d '), ''))) );
97
98 insert into t1(d1,d2) values
99 ('2020-09-01','2020-09-01'),('2020-05-01','2020-09-01');
100 select * from t1;
101
102 drop table t1;
103
104 --echo # MDEV-25772 (duplicate) and LOCK TABLES case
105 create table t1 (d1 datetime , v_d1 tinyint(1) as (d1 < curdate()));
106 insert into t1 (d1) values ('2021-09-11 08:38:23'), ('2021-09-01 08:38:23');
107
108 lock tables t1 write;
109 select * from t1 where v_d1=1;
110 select * from t1;
111 unlock tables;
112
113 drop table t1;
114
115 --echo # MDEV-26432 (duplicate)
116 create table t1 (v2 int, v1 int as ((user() like 'x'))) ;
117 select 1 from t1 where v1=1 ;
118 select * from t1;
119
120 drop table t1;
121
122 create table t1 (v2 int as ( user () like 'x'));
123 select 1 from t1 order by v2 ;
124 alter table t1 add i int;
125 drop table t1;
126
127 --echo # MDEV-26437 (duplicate)
128 create table v0 (v2 int not null,
129 v1 bigint as (case 'x' when current_user() then v2 end));
130
131 select v2 as v3 from v0 where v1 like 'x' escape 'x';
132 insert into v0 (v2) values (-128);
133
134 drop table v0;
135
136 create table t1 (vi int as (case 'x' when current_user() then 1 end));
137 select 1 from t1 where vi=1;
138 show create table t1;
139
140 drop table t1;
141
142 create table t1 (vi int as (case 'x' when current_user() then 1 end));
143 select 1 from t1 where vi=1;
144 select 1 from t1 where vi=1;
145
146 drop table t1;
147
148 --echo # MDEV-28092 (duplicate)
149 create table t1 (b timestamp, a int as (1 in (dayofmonth (b between 'x' and current_user) = b)));
150 insert into t1(b) values ('2022-03-17 14:55:37');
151
152 select 1 from t1 x natural join t1;
153 drop table t1;
154
155 --echo # MDEV-28089 (duplicate)
156 create table t1 (a int , b date as (1 in ('x' ,(database () = 'x' is null) ))) ;
157 select b from t1;
158 select a from t1 order by 'x' = b;
159 drop table t1;
160
161 create table t1 (a int , b date as (1 in ('x' ,(database ()) ))) ;
162 select b from t1;
163 select a from t1 order by 'x' = b;
164 drop table t1;
165
166 --echo #
167 --echo # MDEV-31319 Assertion const_item_cache == true failed in Item_func::fix_fields
168 --echo #
169 create table t (f1 int, f2 int, fv int generated always as (case user() when 'foo' or 'bar' then f1 else f2 end) virtual);
170 select * from t;
171 insert into t (f1,f2) values(1,1);
172 select * from t;
173 create table tmp as select * from information_schema.tables where table_name = 't';
174 select * from t;
175
176 # cleanup
177 drop table t, tmp;
178
179 --echo #
180 --echo # MDEV-29357 Assertion (fixed) in Item_func_dayname on INSERT
181 --echo #
182 set sql_mode='';
183 create table t (c1 blob ,c2 int,c3 char(10) as (dayname (c2)));
184 create trigger tr before insert on t for each row set new.c2=0;
185 insert into t values (0, 0, 0);
186 insert into t values (1, 1, 1);
187
188 drop trigger tr;
189 drop table t;
190
191 --echo #
192 --echo # MDEV-29932 Invalid expr in cleanup_session_expr() upon INSERT DELAYED
193 --echo #
194 create table t (f timestamp default from_unixtime(1), g timestamp as (from_unixtime(2)));
195 insert delayed into t values ();
196 flush table t;
197 select unix_timestamp(f), unix_timestamp(g) from t;
198 # Cleanup
199 drop table t;