Debian Codegraph @

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

raw
1 SET @@session.default_storage_engine = 'MyISAM';
2 # AES_ENCRYPT() without the mode (4th argument)
3 create or replace table t1 (b blob as (aes_encrypt('abc', 'bcd')) PERSISTENT);
4 ERROR HY000: Function or expression 'aes_encrypt()' cannot be used in the GENERATED ALWAYS AS clause of `b`
5 create or replace table t1 (b blob as (aes_encrypt('abc', 'bcd','def')) PERSISTENT);
6 ERROR HY000: Function or expression 'aes_encrypt()' cannot be used in the GENERATED ALWAYS AS clause of `b`
7 create or replace table t1 (b blob as (aes_decrypt('abc', 'bcd')) PERSISTENT);
8 ERROR HY000: Function or expression 'aes_decrypt()' cannot be used in the GENERATED ALWAYS AS clause of `b`
9 create or replace table t1 (b blob as (aes_decrypt('abc', 'bcd','def')) PERSISTENT);
10 ERROR HY000: Function or expression 'aes_decrypt()' cannot be used in the GENERATED ALWAYS AS clause of `b`
11 # RAND()
12 create or replace table t1 (b double as (rand()));
13 create or replace table t1 (b double as (rand()) PERSISTENT);
14 ERROR HY000: Function or expression 'rand()' cannot be used in the GENERATED ALWAYS AS clause of `b`
15 # LOAD_FILE()
16 create or replace table t1 (a varchar(64), b varchar(1024) as (load_file(a)));
17 ERROR HY000: Function or expression 'load_file()' cannot be used in the GENERATED ALWAYS AS clause of `b`
18 # CURDATE()
19 create or replace table t1 (a datetime as (curdate()) PERSISTENT);
20 ERROR HY000: Function or expression 'curdate()' cannot be used in the GENERATED ALWAYS AS clause of `a`
21 # CURRENT_DATE(), CURRENT_DATE
22 create or replace table t1 (a datetime as (current_date) PERSISTENT);
23 ERROR HY000: Function or expression 'curdate()' cannot be used in the GENERATED ALWAYS AS clause of `a`
24 create or replace table t1 (a datetime as (current_date()) PERSISTENT);
25 ERROR HY000: Function or expression 'curdate()' cannot be used in the GENERATED ALWAYS AS clause of `a`
26 # CURRENT_TIME(), CURRENT_TIME
27 create or replace table t1 (a datetime as (current_time) PERSISTENT);
28 ERROR HY000: Function or expression 'curtime()' cannot be used in the GENERATED ALWAYS AS clause of `a`
29 create or replace table t1 (a datetime as (current_time()) PERSISTENT);
30 ERROR HY000: Function or expression 'curtime()' cannot be used in the GENERATED ALWAYS AS clause of `a`
31 # CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP
32 create or replace table t1 (a datetime as (current_timestamp()) PERSISTENT);
33 ERROR HY000: Function or expression 'current_timestamp()' cannot be used in the GENERATED ALWAYS AS clause of `a`
34 create or replace table t1 (a datetime as (current_timestamp) PERSISTENT);
35 ERROR HY000: Function or expression 'current_timestamp()' cannot be used in the GENERATED ALWAYS AS clause of `a`
36 # CURTIME()
37 create or replace table t1 (a datetime as (curtime()) PERSISTENT);
38 ERROR HY000: Function or expression 'curtime()' cannot be used in the GENERATED ALWAYS AS clause of `a`
39 # LOCALTIME(), LOCALTIME
40 create or replace table t1 (a datetime, b varchar(10) as (localtime()) PERSISTENT);
41 ERROR HY000: Function or expression 'curtime()' cannot be used in the GENERATED ALWAYS AS clause of `b`
42 create or replace table t1 (a datetime, b varchar(10) as (localtime) PERSISTENT);
43 ERROR HY000: Function or expression 'curtime()' cannot be used in the GENERATED ALWAYS AS clause of `b`
44 # LOCALTIMESTAMP, LOCALTIMESTAMP()(v4.0.6)
45 create or replace table t1 (a datetime, b varchar(10) as (localtimestamp()) PERSISTENT);
46 ERROR HY000: Function or expression 'localtimestamp()' cannot be used in the GENERATED ALWAYS AS clause of `b`
47 create or replace table t1 (a datetime, b varchar(10) as (localtimestamp) PERSISTENT);
48 ERROR HY000: Function or expression 'localtimestamp()' cannot be used in the GENERATED ALWAYS AS clause of `b`
49 # NOW()
50 create or replace table t1 (a datetime, b varchar(10) as (now()) PERSISTENT);
51 ERROR HY000: Function or expression 'current_timestamp()' cannot be used in the GENERATED ALWAYS AS clause of `b`
52 # SYSDATE()
53 create or replace table t1 (a int, b varchar(10) as (sysdate()) PERSISTENT);
54 ERROR HY000: Function or expression 'sysdate()' cannot be used in the GENERATED ALWAYS AS clause of `b`
55 # UNIX_TIMESTAMP()
56 create or replace table t1 (a datetime, b datetime as (unix_timestamp()) PERSISTENT);
57 ERROR HY000: Function or expression 'unix_timestamp()' cannot be used in the GENERATED ALWAYS AS clause of `b`
58 # UTC_DATE()
59 create or replace table t1 (a datetime, b datetime as (utc_date()) PERSISTENT);
60 ERROR HY000: Function or expression 'utc_date()' cannot be used in the GENERATED ALWAYS AS clause of `b`
61 # UTC_TIME()
62 create or replace table t1 (a datetime, b datetime as (utc_time()) PERSISTENT);
63 ERROR HY000: Function or expression 'utc_time()' cannot be used in the GENERATED ALWAYS AS clause of `b`
64 # UTC_TIMESTAMP()
65 create or replace table t1 (a datetime, b datetime as (utc_timestamp()) PERSISTENT);
66 ERROR HY000: Function or expression 'utc_timestamp()' cannot be used in the GENERATED ALWAYS AS clause of `b`
67 # WEEK() - one argument version
68 create or replace table t1 (a datetime, b datetime as (week(a)) PERSISTENT);
69 ERROR HY000: Function or expression 'week()' cannot be used in the GENERATED ALWAYS AS clause of `b`
70 # MATCH()
71 create or replace table t1 (a varchar(32), b bool as (match a against ('sample text')) PERSISTENT);
72 ERROR HY000: Function or expression 'match ... against()' cannot be used in the GENERATED ALWAYS AS clause of `b`
73 # BENCHMARK()
74 create or replace table t1 (a varchar(1024), b varchar(1024) as (benchmark(a,3)));
75 ERROR HY000: Function or expression 'benchmark()' cannot be used in the GENERATED ALWAYS AS clause of `b`
76 # CHARSET()
77 create or replace table t1 (a varchar(64), b varchar(64) as (charset(a)) PERSISTENT);
78 # COERCIBILITY()
79 create or replace table t1 (a varchar(64), b int as (coercibility(a)) PERSISTENT);
80 # COLLATION()
81 create or replace table t1 (a varchar(64), b varchar(64) as (collation(a)) PERSISTENT);
82 # CONNECTION_ID()
83 create or replace table t1 (a int as (connection_id()));
84 create or replace table t1 (a int as (connection_id()) PERSISTENT);
85 ERROR HY000: Function or expression 'connection_id()' cannot be used in the GENERATED ALWAYS AS clause of `a`
86 # DATABASE()
87 create or replace table t1 (a varchar(32) as (database()));
88 create or replace table t1 (a varchar(1024), b varchar(1024) as (database()) PERSISTENT);
89 ERROR HY000: Function or expression 'database()' cannot be used in the GENERATED ALWAYS AS clause of `b`
90 # FOUND_ROWS()
91 create or replace table t1 (a varchar(1024), b varchar(1024) as (found_rows()));
92 ERROR HY000: Function or expression 'found_rows()' cannot be used in the GENERATED ALWAYS AS clause of `b`
93 # GET_LOCK()
94 create or replace table t1 (a varchar(1024), b varchar(1024) as (get_lock(a,10)));
95 ERROR HY000: Function or expression 'get_lock()' cannot be used in the GENERATED ALWAYS AS clause of `b`
96 # IS_FREE_LOCK()
97 create or replace table t1 (a varchar(1024), b varchar(1024) as (is_free_lock(a)));
98 ERROR HY000: Function or expression 'is_free_lock()' cannot be used in the GENERATED ALWAYS AS clause of `b`
99 # IS_USED_LOCK()
100 create or replace table t1 (a varchar(1024), b varchar(1024) as (is_used_lock(a)));
101 ERROR HY000: Function or expression 'is_used_lock()' cannot be used in the GENERATED ALWAYS AS clause of `b`
102 # LAST_INSERT_ID()
103 create or replace table t1 (a int as (last_insert_id()));
104 ERROR HY000: Function or expression 'last_insert_id()' cannot be used in the GENERATED ALWAYS AS clause of `a`
105 # MASTER_POS_WAIT()
106 create or replace table t1 (a varchar(32), b int as (master_pos_wait(a,0,2)));
107 ERROR HY000: Function or expression 'master_pos_wait()' cannot be used in the GENERATED ALWAYS AS clause of `b`
108 # NAME_CONST()
109 create or replace table t1 (a varchar(32) as (name_const('test',1)));
110 ERROR HY000: Function or expression 'name_const()' cannot be used in the GENERATED ALWAYS AS clause of `a`
111 # RELEASE_LOCK()
112 create or replace table t1 (a varchar(32), b int as (release_lock(a)));
113 ERROR HY000: Function or expression 'release_lock()' cannot be used in the GENERATED ALWAYS AS clause of `b`
114 # ROW_COUNT()
115 create or replace table t1 (a int as (row_count()));
116 ERROR HY000: Function or expression 'row_count()' cannot be used in the GENERATED ALWAYS AS clause of `a`
117 # SCHEMA()
118 create or replace table t1 (a varchar(32) as (schema()) PERSISTENT);
119 ERROR HY000: Function or expression 'database()' cannot be used in the GENERATED ALWAYS AS clause of `a`
120 # SESSION_USER()
121 create or replace table t1 (a varchar(32) as (session_user()) PERSISTENT);
122 ERROR HY000: Function or expression 'session_user()' cannot be used in the GENERATED ALWAYS AS clause of `a`
123 # SLEEP()
124 create or replace table t1 (a int, b int as (sleep(a)));
125 ERROR HY000: Function or expression 'sleep()' cannot be used in the GENERATED ALWAYS AS clause of `b`
126 # SYSTEM_USER()
127 create or replace table t1 (a varchar(32) as (system_user()) PERSISTENT);
128 ERROR HY000: Function or expression 'user()' cannot be used in the GENERATED ALWAYS AS clause of `a`
129 # USER()
130 create or replace table t1 (a varchar(1024), b varchar(1024) as (user()) PERSISTENT);
131 ERROR HY000: Function or expression 'user()' cannot be used in the GENERATED ALWAYS AS clause of `b`
132 # UUID_SHORT()
133 create or replace table t1 (a varchar(1024) as (uuid_short()) PERSISTENT);
134 ERROR HY000: Function or expression 'uuid_short()' cannot be used in the GENERATED ALWAYS AS clause of `a`
135 # UUID()
136 create or replace table t1 (a varchar(1024) as (uuid()) PERSISTENT);
137 ERROR HY000: Function or expression 'uuid()' cannot be used in the GENERATED ALWAYS AS clause of `a`
138 # VALUES()
139 create or replace table t1 (a varchar(1024), b varchar(1024) as (value(a)));
140 ERROR HY000: Function or expression 'value()' cannot be used in the GENERATED ALWAYS AS clause of `b`
141 # VERSION()
142 create or replace table t1 (a varchar(1024), b varchar(1024) as (version()) PERSISTENT);
143 ERROR HY000: Function or expression 'version()' cannot be used in the GENERATED ALWAYS AS clause of `b`
144 # ENCRYPT()
145 create or replace table t1 (a varchar(1024), b varchar(1024) as (encrypt(a)) PERSISTENT);
146 # Stored procedures
147 create procedure p1()
148 begin
149 select current_user();
150 end //
151 create function f1()
152 returns int
153 begin
154 return 1;
155 end //
156 create or replace table t1 (a int as (p1()) PERSISTENT);
157 ERROR HY000: Function or expression '`p1`()' cannot be used in the GENERATED ALWAYS AS clause of `a`
158 create or replace table t1 (a int as (f1()) PERSISTENT);
159 ERROR HY000: Function or expression '`f1`()' cannot be used in the GENERATED ALWAYS AS clause of `a`
160 drop procedure p1;
161 drop function f1;
162 # Unknown functions
163 create or replace table t1 (a int as (f1()) PERSISTENT);
164 ERROR HY000: Function or expression '`f1`()' cannot be used in the GENERATED ALWAYS AS clause of `a`
165 #
166 # GROUP BY FUNCTIONS
167 #
168 # AVG()
169 create or replace table t1 (a int, b int as (avg(a)));
170 ERROR HY000: Function or expression 'avg()' cannot be used in the GENERATED ALWAYS AS clause of `b`
171 # BIT_AND()
172 create or replace table t1 (a int, b int as (bit_and(a)));
173 ERROR HY000: Function or expression 'bit_and()' cannot be used in the GENERATED ALWAYS AS clause of `b`
174 # BIT_OR()
175 create or replace table t1 (a int, b int as (bit_or(a)));
176 ERROR HY000: Function or expression 'bit_or()' cannot be used in the GENERATED ALWAYS AS clause of `b`
177 # BIT_XOR()
178 create or replace table t1 (a int, b int as (bit_xor(a)));
179 ERROR HY000: Function or expression 'bit_xor()' cannot be used in the GENERATED ALWAYS AS clause of `b`
180 # COUNT(DISTINCT)
181 create or replace table t1 (a int, b int as (count(distinct a)));
182 ERROR HY000: Function or expression 'count(distinct )' cannot be used in the GENERATED ALWAYS AS clause of `b`
183 # COUNT()
184 create or replace table t1 (a int, b int as (count(a)));
185 ERROR HY000: Function or expression 'count()' cannot be used in the GENERATED ALWAYS AS clause of `b`
186 # GROUP_CONCAT()
187 create or replace table t1 (a varchar(32), b int as (group_concat(a,'')));
188 ERROR HY000: Function or expression 'group_concat()' cannot be used in the GENERATED ALWAYS AS clause of `b`
189 # MAX()
190 create or replace table t1 (a int, b int as (max(a)));
191 ERROR HY000: Function or expression 'max()' cannot be used in the GENERATED ALWAYS AS clause of `b`
192 # MIN()
193 create or replace table t1 (a int, b int as (min(a)));
194 ERROR HY000: Function or expression 'min()' cannot be used in the GENERATED ALWAYS AS clause of `b`
195 # STD()
196 create or replace table t1 (a int, b int as (std(a)));
197 ERROR HY000: Function or expression 'std()' cannot be used in the GENERATED ALWAYS AS clause of `b`
198 # STDDEV_POP()
199 create or replace table t1 (a int, b int as (stddev_pop(a)));
200 ERROR HY000: Function or expression 'std()' cannot be used in the GENERATED ALWAYS AS clause of `b`
201 # STDDEV_SAMP()
202 create or replace table t1 (a int, b int as (stddev_samp(a)));
203 ERROR HY000: Function or expression 'stddev_samp()' cannot be used in the GENERATED ALWAYS AS clause of `b`
204 # STDDEV()
205 create or replace table t1 (a int, b int as (stddev(a)));
206 ERROR HY000: Function or expression 'std()' cannot be used in the GENERATED ALWAYS AS clause of `b`
207 # SUM()
208 create or replace table t1 (a int, b int as (sum(a)));
209 ERROR HY000: Function or expression 'sum()' cannot be used in the GENERATED ALWAYS AS clause of `b`
210 # VAR_POP()
211 create or replace table t1 (a int, b int as (var_pop(a)));
212 ERROR HY000: Function or expression 'variance()' cannot be used in the GENERATED ALWAYS AS clause of `b`
213 # VAR_SAMP()
214 create or replace table t1 (a int, b int as (var_samp(a)));
215 ERROR HY000: Function or expression 'var_samp()' cannot be used in the GENERATED ALWAYS AS clause of `b`
216 # VARIANCE()
217 create or replace table t1 (a int, b int as (variance(a)));
218 ERROR HY000: Function or expression 'variance()' cannot be used in the GENERATED ALWAYS AS clause of `b`
219 #
220 # XML FUNCTIONS
221 #
222 # ExtractValue()
223 create or replace table t1 (a varchar(1024), b varchar(1024) as (ExtractValue(a,'//b[$@j]')) PERSISTENT);
224 # UpdateXML()
225 create or replace table t1 (a varchar(1024), b varchar(1024) as (UpdateXML(a,'/a','<e>fff</e>')) PERSISTENT);
226 #
227 # Sub-selects
228 #
229 create or replace table t1 (a int);
230 create or replace table t2 (a int, b int as (select count(*) from t1));
231 ERROR HY000: Function or expression 'select ...' cannot be used in the GENERATED ALWAYS AS clause of `b`
232 drop table t1;
233 create or replace table t1 (a int, b int as ((select 1)));
234 ERROR HY000: Function or expression 'select ...' cannot be used in the GENERATED ALWAYS AS clause of `b`
235 create or replace table t1 (a int, b int as (a+(select 1)));
236 ERROR HY000: Function or expression 'select ...' cannot be used in the GENERATED ALWAYS AS clause of `b`
237 #
238 # SP functions
239 #
240 drop function if exists sub1;
241 create function sub1(i int) returns int deterministic
242 return i+1;
243 select sub1(1);
244 sub1(1)
245 2
246 create or replace table t1 (a int, b int as (a+sub3(1)));
247 ERROR HY000: Function or expression '`sub3`()' cannot be used in the GENERATED ALWAYS AS clause of `b`
248 drop function sub1;
249 #
250 # Long expression
251 create or replace table t1 (a int, b varchar(300) as (concat(a,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')));
252 drop table t1;
253 create or replace table t1 (a int, b varchar(16384) as (concat(a,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'))) charset=latin1;
254 ERROR HY000: Expression in the GENERATED ALWAYS AS clause is too big
255 #
256 # Constant expression
257 create or replace table t1 (a int as (PI()) PERSISTENT);
258 drop table if exists t1;
259 create table t1 (a timestamp, b varchar(255) as (date_format(a, '%w %a %m %b')) stored);
260 ERROR HY000: Function or expression 'date_format()' cannot be used in the GENERATED ALWAYS AS clause of `b`