Debian Codegraph @

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

raw
1 set @OLD_SQL_MODE=@@SESSION.SQL_MODE;
2 create table t1 (a int, b int generated always as (a+1));
3 show create table t1;
4 Table Create Table
5 t1 CREATE TABLE `t1` (
6 `a` int(11) DEFAULT NULL,
7 `b` int(11) GENERATED ALWAYS AS (`a` + 1) VIRTUAL
8 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
9 drop table t1;
10 create table t1 (a int, b int as (a+1) virtual);
11 show create table t1;
12 Table Create Table
13 t1 CREATE TABLE `t1` (
14 `a` int(11) DEFAULT NULL,
15 `b` int(11) GENERATED ALWAYS AS (`a` + 1) VIRTUAL
16 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
17 drop table t1;
18 create table t1 (a int, b int generated always as (a+1) persistent);
19 show create table t1;
20 Table Create Table
21 t1 CREATE TABLE `t1` (
22 `a` int(11) DEFAULT NULL,
23 `b` int(11) GENERATED ALWAYS AS (`a` + 1) STORED
24 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
25 drop table t1;
26 set session sql_mode='ORACLE';
27 create table t1 (a int, b int as (a+1));
28 show create table t1;
29 Table Create Table
30 t1 CREATE TABLE "t1" (
31 "a" int(11) DEFAULT NULL,
32 "b" int(11) GENERATED ALWAYS AS ("a" + 1) VIRTUAL
33 )
34 drop table t1;
35 create table t1 (a int, b int generated always as (a+1) virtual);
36 show create table t1;
37 Table Create Table
38 t1 CREATE TABLE "t1" (
39 "a" int(11) DEFAULT NULL,
40 "b" int(11) GENERATED ALWAYS AS ("a" + 1) VIRTUAL
41 )
42 drop table t1;
43 create table t1 (a int, b int as (a+1) persistent);
44 show create table t1;
45 Table Create Table
46 t1 CREATE TABLE "t1" (
47 "a" int(11) DEFAULT NULL,
48 "b" int(11) GENERATED ALWAYS AS ("a" + 1) STORED
49 )
50 drop table t1;
51 set session sql_mode=@OLD_SQL_MODE;
52 #
53 # MDEV-25091 CREATE TABLE: field references qualified by a wrong table name succeed
54 #
55 create table t2 (x int);
56 create table t1 (x int, y int generated always as (t2.x));
57 ERROR HY000: Function or expression 't2.x' cannot be used in the GENERATED ALWAYS AS clause of `y`
58 create table t1 (x int, y int check (y > t2.x));
59 ERROR HY000: Function or expression 't2.x' cannot be used in the CHECK clause of `y`
60 create table t1 (x int, y int default t2.x);
61 ERROR HY000: Function or expression 't2.x' cannot be used in the DEFAULT clause of `y`
62 create table t1 (x int, check (t2.x > 0));
63 ERROR HY000: Function or expression 't2.x' cannot be used in the CHECK clause of `CONSTRAINT_1`
64 create table t1 (x int);
65 alter table t1 add column y int generated always as (t2.x);
66 ERROR HY000: Function or expression 't2.x' cannot be used in the GENERATED ALWAYS AS clause of `y`
67 alter table t1 add column y int check (z > t2.x);
68 ERROR HY000: Function or expression 't2.x' cannot be used in the CHECK clause of `y`
69 alter table t1 add column y int default t2.x;
70 ERROR HY000: Function or expression 't2.x' cannot be used in the DEFAULT clause of `y`
71 alter table t1 add constraint check (t2.x > 0);
72 ERROR HY000: Function or expression 't2.x' cannot be used in the CHECK clause of `CONSTRAINT_1`
73 create or replace table t1 (x int, y int generated always as (t1.x));
74 create or replace table t1 (x int, y int check (y > t1.x));
75 create or replace table t1 (x int, y int default t1.x);
76 create or replace table t1 (x int, check (t1.x > 0));
77 create or replace table t1 (x int, y int generated always as (test.t1.x));
78 create or replace table t1 (x int, y int check (y > test.t1.x));
79 create or replace table t1 (x int, y int default test.t1.x);
80 create or replace table t1 (x int, check (test.t1.x > 0));
81 drop tables t1, t2;
82 create table t1 (x int, y int generated always as (test2.t1.x));
83 ERROR HY000: Function or expression 'test2.t1.x' cannot be used in the GENERATED ALWAYS AS clause of `y`
84 create table t1 (x int, y int check (y > test2.t1.x));
85 ERROR HY000: Function or expression 'test2.t1.x' cannot be used in the CHECK clause of `y`
86 create table t1 (x int, y int default test2.t1.x);
87 ERROR HY000: Function or expression 'test2.t1.x' cannot be used in the DEFAULT clause of `y`
88 create table t1 (x int, check (test2.t1.x > 0));
89 ERROR HY000: Function or expression 'test2.t1.x' cannot be used in the CHECK clause of `CONSTRAINT_1`
90 #
91 # MDEV-25672 table alias from previous statement interferes later commands
92 #
93 create table t1 (a int, v_a int generated always as (a));
94 update t1 as x set a = 1;
95 alter table t1 force;
96 drop table t1;
97 create table t1 (
98 id int not null auto_increment primary key,
99 order_date_time datetime not null,
100 order_date date generated always as (convert(order_date_time, date)),
101 language_id binary(16) null
102 );
103 update t1 as tx set order_date= null;
104 alter table t1 modify column language_id binary(16) not null;
105 drop table t1;
106 #
107 # MDEV-24176 Server crashes after insert in the table with virtual column generated using date_format() and if()
108 #
109 create table t1 (d1 date not null, d2 date not null,
110 gd text as (concat(d1,if(d1 <> d2, date_format(d2, 'to %y-%m-%d '), ''))) );
111 insert into t1(d1,d2) values
112 ('2020-09-01','2020-09-01'),('2020-05-01','2020-09-01');
113 select * from t1;
114 d1 d2 gd
115 2020-09-01 2020-09-01 2020-09-01
116 2020-05-01 2020-09-01 2020-05-01to 20-09-01
117 drop table t1;
118 # MDEV-25772 (duplicate) and LOCK TABLES case
119 create table t1 (d1 datetime , v_d1 tinyint(1) as (d1 < curdate()));
120 insert into t1 (d1) values ('2021-09-11 08:38:23'), ('2021-09-01 08:38:23');
121 lock tables t1 write;
122 select * from t1 where v_d1=1;
123 d1 v_d1
124 2021-09-11 08:38:23 1
125 2021-09-01 08:38:23 1
126 select * from t1;
127 d1 v_d1
128 2021-09-11 08:38:23 1
129 2021-09-01 08:38:23 1
130 unlock tables;
131 drop table t1;
132 # MDEV-26432 (duplicate)
133 create table t1 (v2 int, v1 int as ((user() like 'x'))) ;
134 select 1 from t1 where v1=1 ;
135 1
136 select * from t1;
137 v2 v1
138 drop table t1;
139 create table t1 (v2 int as ( user () like 'x'));
140 select 1 from t1 order by v2 ;
141 1
142 alter table t1 add i int;
143 drop table t1;
144 # MDEV-26437 (duplicate)
145 create table v0 (v2 int not null,
146 v1 bigint as (case 'x' when current_user() then v2 end));
147 select v2 as v3 from v0 where v1 like 'x' escape 'x';
148 v3
149 insert into v0 (v2) values (-128);
150 drop table v0;
151 create table t1 (vi int as (case 'x' when current_user() then 1 end));
152 select 1 from t1 where vi=1;
153 1
154 show create table t1;
155 Table Create Table
156 t1 CREATE TABLE `t1` (
157 `vi` int(11) GENERATED ALWAYS AS (case 'x' when current_user() then 1 end) VIRTUAL
158 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
159 drop table t1;
160 create table t1 (vi int as (case 'x' when current_user() then 1 end));
161 select 1 from t1 where vi=1;
162 1
163 select 1 from t1 where vi=1;
164 1
165 drop table t1;
166 # MDEV-28092 (duplicate)
167 create table t1 (b timestamp, a int as (1 in (dayofmonth (b between 'x' and current_user) = b)));
168 insert into t1(b) values ('2022-03-17 14:55:37');
169 select 1 from t1 x natural join t1;
170 1
171 1
172 Warnings:
173 Warning 1292 Truncated incorrect datetime value: 'x'
174 Warning 1292 Truncated incorrect datetime value: 'root@localhost'
175 Warning 1292 Truncated incorrect datetime value: 'x'
176 Warning 1292 Truncated incorrect datetime value: 'root@localhost'
177 drop table t1;
178 # MDEV-28089 (duplicate)
179 create table t1 (a int , b date as (1 in ('x' ,(database () = 'x' is null) ))) ;
180 select b from t1;
181 b
182 select a from t1 order by 'x' = b;
183 a
184 drop table t1;
185 create table t1 (a int , b date as (1 in ('x' ,(database ()) ))) ;
186 Warnings:
187 Warning 1292 Truncated incorrect DECIMAL value: 'x'
188 Warning 1292 Truncated incorrect DECIMAL value: 'test'
189 select b from t1;
190 b
191 Warnings:
192 Warning 1292 Truncated incorrect DECIMAL value: 'x'
193 Warning 1292 Truncated incorrect DECIMAL value: 'test'
194 select a from t1 order by 'x' = b;
195 a
196 Warnings:
197 Warning 1292 Truncated incorrect DECIMAL value: 'x'
198 Warning 1292 Truncated incorrect DECIMAL value: 'test'
199 drop table t1;
200 #
201 # MDEV-31319 Assertion const_item_cache == true failed in Item_func::fix_fields
202 #
203 create table t (f1 int, f2 int, fv int generated always as (case user() when 'foo' or 'bar' then f1 else f2 end) virtual);
204 select * from t;
205 f1 f2 fv
206 insert into t (f1,f2) values(1,1);
207 select * from t;
208 f1 f2 fv
209 1 1 1
210 Warnings:
211 Warning 1292 Truncated incorrect DECIMAL value: 'root@localhost'
212 Warning 1292 Truncated incorrect DOUBLE value: 'foo'
213 Warning 1292 Truncated incorrect DOUBLE value: 'bar'
214 create table tmp as select * from information_schema.tables where table_name = 't';
215 select * from t;
216 f1 f2 fv
217 1 1 1
218 Warnings:
219 Warning 1292 Truncated incorrect DECIMAL value: 'root@localhost'
220 Warning 1292 Truncated incorrect DOUBLE value: 'foo'
221 Warning 1292 Truncated incorrect DOUBLE value: 'bar'
222 drop table t, tmp;
223 #
224 # MDEV-29357 Assertion (fixed) in Item_func_dayname on INSERT
225 #
226 set sql_mode='';
227 create table t (c1 blob ,c2 int,c3 char(10) as (dayname (c2)));
228 create trigger tr before insert on t for each row set new.c2=0;
229 insert into t values (0, 0, 0);
230 Warnings:
231 Warning 1906 The value specified for generated column 'c3' in table 't' has been ignored
232 Warning 1292 Incorrect datetime value: '0' for column `test`.`t`.`c2` at row 1
233 Warning 1292 Incorrect datetime value: '0' for column `test`.`t`.`c2` at row 1
234 insert into t values (1, 1, 1);
235 Warnings:
236 Warning 1906 The value specified for generated column 'c3' in table 't' has been ignored
237 Warning 1292 Incorrect datetime value: '1' for column `test`.`t`.`c2` at row 1
238 Warning 1292 Incorrect datetime value: '0' for column `test`.`t`.`c2` at row 1
239 drop trigger tr;
240 drop table t;
241 #
242 # MDEV-29932 Invalid expr in cleanup_session_expr() upon INSERT DELAYED
243 #
244 create table t (f timestamp default from_unixtime(1), g timestamp as (from_unixtime(2)));
245 insert delayed into t values ();
246 flush table t;
247 select unix_timestamp(f), unix_timestamp(g) from t;
248 unix_timestamp(f) unix_timestamp(g)
249 1 2
250 drop table t;