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