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
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
|
set @OLD_SQL_MODE=@@SESSION.SQL_MODE;
create table t1 (a int, b int generated always as (a+1));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) GENERATED ALWAYS AS (`a` + 1) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
drop table t1;
create table t1 (a int, b int as (a+1) virtual);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) GENERATED ALWAYS AS (`a` + 1) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
drop table t1;
create table t1 (a int, b int generated always as (a+1) persistent);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) GENERATED ALWAYS AS (`a` + 1) STORED
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
drop table t1;
set session sql_mode='ORACLE';
create table t1 (a int, b int as (a+1));
show create table t1;
Table Create Table
t1 CREATE TABLE "t1" (
"a" int(11) DEFAULT NULL,
"b" int(11) GENERATED ALWAYS AS ("a" + 1) VIRTUAL
)
drop table t1;
create table t1 (a int, b int generated always as (a+1) virtual);
show create table t1;
Table Create Table
t1 CREATE TABLE "t1" (
"a" int(11) DEFAULT NULL,
"b" int(11) GENERATED ALWAYS AS ("a" + 1) VIRTUAL
)
drop table t1;
create table t1 (a int, b int as (a+1) persistent);
show create table t1;
Table Create Table
t1 CREATE TABLE "t1" (
"a" int(11) DEFAULT NULL,
"b" int(11) GENERATED ALWAYS AS ("a" + 1) STORED
)
drop table t1;
set session sql_mode=@OLD_SQL_MODE;
#
# MDEV-25091 CREATE TABLE: field references qualified by a wrong table name succeed
#
create table t2 (x int);
create table t1 (x int, y int generated always as (t2.x));
ERROR HY000: Function or expression 't2.x' cannot be used in the GENERATED ALWAYS AS clause of `y`
create table t1 (x int, y int check (y > t2.x));
ERROR HY000: Function or expression 't2.x' cannot be used in the CHECK clause of `y`
create table t1 (x int, y int default t2.x);
ERROR HY000: Function or expression 't2.x' cannot be used in the DEFAULT clause of `y`
create table t1 (x int, check (t2.x > 0));
ERROR HY000: Function or expression 't2.x' cannot be used in the CHECK clause of `CONSTRAINT_1`
create table t1 (x int);
alter table t1 add column y int generated always as (t2.x);
ERROR HY000: Function or expression 't2.x' cannot be used in the GENERATED ALWAYS AS clause of `y`
alter table t1 add column y int check (z > t2.x);
ERROR HY000: Function or expression 't2.x' cannot be used in the CHECK clause of `y`
alter table t1 add column y int default t2.x;
ERROR HY000: Function or expression 't2.x' cannot be used in the DEFAULT clause of `y`
alter table t1 add constraint check (t2.x > 0);
ERROR HY000: Function or expression 't2.x' cannot be used in the CHECK clause of `CONSTRAINT_1`
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;
create table t1 (x int, y int generated always as (test2.t1.x));
ERROR HY000: Function or expression 'test2.t1.x' cannot be used in the GENERATED ALWAYS AS clause of `y`
create table t1 (x int, y int check (y > test2.t1.x));
ERROR HY000: Function or expression 'test2.t1.x' cannot be used in the CHECK clause of `y`
create table t1 (x int, y int default test2.t1.x);
ERROR HY000: Function or expression 'test2.t1.x' cannot be used in the DEFAULT clause of `y`
create table t1 (x int, check (test2.t1.x > 0));
ERROR HY000: Function or expression 'test2.t1.x' cannot be used in the CHECK clause of `CONSTRAINT_1`
#
# MDEV-25672 table alias from previous statement interferes later commands
#
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;
drop table t1;
#
# MDEV-24176 Server crashes after insert in the table with virtual column generated using date_format() and if()
#
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;
d1 d2 gd
2020-09-01 2020-09-01 2020-09-01
2020-05-01 2020-09-01 2020-05-01to 20-09-01
drop table t1;
# 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;
d1 v_d1
2021-09-11 08:38:23 1
2021-09-01 08:38:23 1
select * from t1;
d1 v_d1
2021-09-11 08:38:23 1
2021-09-01 08:38:23 1
unlock tables;
drop table t1;
# MDEV-26432 (duplicate)
create table t1 (v2 int, v1 int as ((user() like 'x'))) ;
select 1 from t1 where v1=1 ;
1
select * from t1;
v2 v1
drop table t1;
create table t1 (v2 int as ( user () like 'x'));
select 1 from t1 order by v2 ;
1
alter table t1 add i int;
drop table t1;
# 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';
v3
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;
1
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`vi` int(11) GENERATED ALWAYS AS (case 'x' when current_user() then 1 end) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
drop table t1;
create table t1 (vi int as (case 'x' when current_user() then 1 end));
select 1 from t1 where vi=1;
1
select 1 from t1 where vi=1;
1
drop table t1;
# 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;
1
1
Warnings:
Warning 1292 Truncated incorrect datetime value: 'x'
Warning 1292 Truncated incorrect datetime value: 'root@localhost'
Warning 1292 Truncated incorrect datetime value: 'x'
Warning 1292 Truncated incorrect datetime value: 'root@localhost'
drop table t1;
# MDEV-28089 (duplicate)
create table t1 (a int , b date as (1 in ('x' ,(database () = 'x' is null) ))) ;
select b from t1;
b
select a from t1 order by 'x' = b;
a
drop table t1;
create table t1 (a int , b date as (1 in ('x' ,(database ()) ))) ;
Warnings:
Warning 1292 Truncated incorrect DECIMAL value: 'x'
Warning 1292 Truncated incorrect DECIMAL value: 'test'
select b from t1;
b
Warnings:
Warning 1292 Truncated incorrect DECIMAL value: 'x'
Warning 1292 Truncated incorrect DECIMAL value: 'test'
select a from t1 order by 'x' = b;
a
Warnings:
Warning 1292 Truncated incorrect DECIMAL value: 'x'
Warning 1292 Truncated incorrect DECIMAL value: 'test'
drop table t1;
#
# MDEV-31319 Assertion const_item_cache == true failed in Item_func::fix_fields
#
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;
f1 f2 fv
insert into t (f1,f2) values(1,1);
select * from t;
f1 f2 fv
1 1 1
Warnings:
Warning 1292 Truncated incorrect DECIMAL value: 'root@localhost'
Warning 1292 Truncated incorrect DOUBLE value: 'foo'
Warning 1292 Truncated incorrect DOUBLE value: 'bar'
create table tmp as select * from information_schema.tables where table_name = 't';
select * from t;
f1 f2 fv
1 1 1
Warnings:
Warning 1292 Truncated incorrect DECIMAL value: 'root@localhost'
Warning 1292 Truncated incorrect DOUBLE value: 'foo'
Warning 1292 Truncated incorrect DOUBLE value: 'bar'
drop table t, tmp;
#
# MDEV-29357 Assertion (fixed) in Item_func_dayname on INSERT
#
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);
Warnings:
Warning 1906 The value specified for generated column 'c3' in table 't' has been ignored
Warning 1292 Incorrect datetime value: '0' for column `test`.`t`.`c2` at row 1
Warning 1292 Incorrect datetime value: '0' for column `test`.`t`.`c2` at row 1
insert into t values (1, 1, 1);
Warnings:
Warning 1906 The value specified for generated column 'c3' in table 't' has been ignored
Warning 1292 Incorrect datetime value: '1' for column `test`.`t`.`c2` at row 1
Warning 1292 Incorrect datetime value: '0' for column `test`.`t`.`c2` at row 1
drop trigger tr;
drop table t;
#
# MDEV-29932 Invalid expr in cleanup_session_expr() upon INSERT DELAYED
#
create table t (f timestamp default from_unixtime(1), g timestamp as (from_unixtime(2)));
insert delayed into t values ();
flush table t;
select unix_timestamp(f), unix_timestamp(g) from t;
unix_timestamp(f) unix_timestamp(g)
1 2
drop table t;
|
