Debian Codegraph @

mysql-test/suite/vcol/t/vcol_blocked_sql_funcs_main.inc

raw
1 ################################################################################
2 # inc/vcol_blocked_sql_funcs_main.inc #
3 # #
4 # Purpose: #
5 # Tests around sql functions #
6 # #
7 # #
8 #------------------------------------------------------------------------------#
9 # Original Author: Andrey Zhakov #
10 # Original Date: 2008-08-31 #
11 # Change Author: Oleksandr Byelkin (Monty program Ab)
12 # Date: 2009-03-24
13 # Change: Syntax changed
14 ################################################################################
15
16 #
17 # NOTE: All SQL functions should be rejected, otherwise BUG.
18 # As PERSISTANT has higher level checks than VIRTUAL, we use VIRTUAL
19 # to check for things that should not work for either VIRTUAL or PERSISTENT
20 #
21
22 --echo # AES_ENCRYPT() without the mode (4th argument)
23 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
24 create or replace table t1 (b blob as (aes_encrypt('abc', 'bcd')) PERSISTENT);
25 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
26 create or replace table t1 (b blob as (aes_encrypt('abc', 'bcd','def')) PERSISTENT);
27 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
28 create or replace table t1 (b blob as (aes_decrypt('abc', 'bcd')) PERSISTENT);
29 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
30 create or replace table t1 (b blob as (aes_decrypt('abc', 'bcd','def')) PERSISTENT);
31
32 --echo # RAND()
33 create or replace table t1 (b double as (rand()));
34 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
35 create or replace table t1 (b double as (rand()) PERSISTENT);
36
37 --echo # LOAD_FILE()
38 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
39 create or replace table t1 (a varchar(64), b varchar(1024) as (load_file(a)));
40
41 --echo # CURDATE()
42 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
43 create or replace table t1 (a datetime as (curdate()) PERSISTENT);
44
45 --echo # CURRENT_DATE(), CURRENT_DATE
46 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
47 create or replace table t1 (a datetime as (current_date) PERSISTENT);
48 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
49 create or replace table t1 (a datetime as (current_date()) PERSISTENT);
50
51 --echo # CURRENT_TIME(), CURRENT_TIME
52 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
53 create or replace table t1 (a datetime as (current_time) PERSISTENT);
54 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
55 create or replace table t1 (a datetime as (current_time()) PERSISTENT);
56
57 --echo # CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP
58 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
59 create or replace table t1 (a datetime as (current_timestamp()) PERSISTENT);
60 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
61 create or replace table t1 (a datetime as (current_timestamp) PERSISTENT);
62
63 --echo # CURTIME()
64 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
65 create or replace table t1 (a datetime as (curtime()) PERSISTENT);
66
67 --echo # LOCALTIME(), LOCALTIME
68 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
69 create or replace table t1 (a datetime, b varchar(10) as (localtime()) PERSISTENT);
70 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
71 create or replace table t1 (a datetime, b varchar(10) as (localtime) PERSISTENT);
72
73 --echo # LOCALTIMESTAMP, LOCALTIMESTAMP()(v4.0.6)
74 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
75 create or replace table t1 (a datetime, b varchar(10) as (localtimestamp()) PERSISTENT);
76 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
77 create or replace table t1 (a datetime, b varchar(10) as (localtimestamp) PERSISTENT);
78
79 --echo # NOW()
80 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
81 create or replace table t1 (a datetime, b varchar(10) as (now()) PERSISTENT);
82
83 --echo # SYSDATE()
84 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
85 create or replace table t1 (a int, b varchar(10) as (sysdate()) PERSISTENT);
86
87 --echo # UNIX_TIMESTAMP()
88 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
89 create or replace table t1 (a datetime, b datetime as (unix_timestamp()) PERSISTENT);
90
91 --echo # UTC_DATE()
92 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
93 create or replace table t1 (a datetime, b datetime as (utc_date()) PERSISTENT);
94
95 --echo # UTC_TIME()
96 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
97 create or replace table t1 (a datetime, b datetime as (utc_time()) PERSISTENT);
98
99 --echo # UTC_TIMESTAMP()
100 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
101 create or replace table t1 (a datetime, b datetime as (utc_timestamp()) PERSISTENT);
102
103 --echo # WEEK() - one argument version
104 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
105 create or replace table t1 (a datetime, b datetime as (week(a)) PERSISTENT);
106
107 --echo # MATCH()
108 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
109 create or replace table t1 (a varchar(32), b bool as (match a against ('sample text')) PERSISTENT);
110
111 --echo # BENCHMARK()
112 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
113 create or replace table t1 (a varchar(1024), b varchar(1024) as (benchmark(a,3)));
114
115 --echo # CHARSET()
116 create or replace table t1 (a varchar(64), b varchar(64) as (charset(a)) PERSISTENT);
117
118 --echo # COERCIBILITY()
119 create or replace table t1 (a varchar(64), b int as (coercibility(a)) PERSISTENT);
120
121 --echo # COLLATION()
122 create or replace table t1 (a varchar(64), b varchar(64) as (collation(a)) PERSISTENT);
123
124 --echo # CONNECTION_ID()
125 create or replace table t1 (a int as (connection_id()));
126 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
127 create or replace table t1 (a int as (connection_id()) PERSISTENT);
128
129 --echo # DATABASE()
130 create or replace table t1 (a varchar(32) as (database()));
131 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
132 create or replace table t1 (a varchar(1024), b varchar(1024) as (database()) PERSISTENT);
133
134 --echo # FOUND_ROWS()
135 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
136 create or replace table t1 (a varchar(1024), b varchar(1024) as (found_rows()));
137
138 --echo # GET_LOCK()
139 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
140 create or replace table t1 (a varchar(1024), b varchar(1024) as (get_lock(a,10)));
141
142 --echo # IS_FREE_LOCK()
143 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
144 create or replace table t1 (a varchar(1024), b varchar(1024) as (is_free_lock(a)));
145
146 --echo # IS_USED_LOCK()
147 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
148 create or replace table t1 (a varchar(1024), b varchar(1024) as (is_used_lock(a)));
149
150 --echo # LAST_INSERT_ID()
151 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
152 create or replace table t1 (a int as (last_insert_id()));
153
154 --echo # MASTER_POS_WAIT()
155 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
156 create or replace table t1 (a varchar(32), b int as (master_pos_wait(a,0,2)));
157
158 --echo # NAME_CONST()
159 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
160 create or replace table t1 (a varchar(32) as (name_const('test',1)));
161
162 --echo # RELEASE_LOCK()
163 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
164 create or replace table t1 (a varchar(32), b int as (release_lock(a)));
165
166 --echo # ROW_COUNT()
167 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
168 create or replace table t1 (a int as (row_count()));
169
170 --echo # SCHEMA()
171 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
172 create or replace table t1 (a varchar(32) as (schema()) PERSISTENT);
173
174 --echo # SESSION_USER()
175 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
176 create or replace table t1 (a varchar(32) as (session_user()) PERSISTENT);
177
178 --echo # SLEEP()
179 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
180 create or replace table t1 (a int, b int as (sleep(a)));
181
182 --echo # SYSTEM_USER()
183 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
184 create or replace table t1 (a varchar(32) as (system_user()) PERSISTENT);
185
186 --echo # USER()
187 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
188 create or replace table t1 (a varchar(1024), b varchar(1024) as (user()) PERSISTENT);
189
190 --echo # UUID_SHORT()
191 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
192 create or replace table t1 (a varchar(1024) as (uuid_short()) PERSISTENT);
193
194 --echo # UUID()
195 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
196 create or replace table t1 (a varchar(1024) as (uuid()) PERSISTENT);
197
198 --echo # VALUES()
199 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
200 create or replace table t1 (a varchar(1024), b varchar(1024) as (value(a)));
201
202 --echo # VERSION()
203 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
204 create or replace table t1 (a varchar(1024), b varchar(1024) as (version()) PERSISTENT);
205
206 --echo # ENCRYPT()
207 create or replace table t1 (a varchar(1024), b varchar(1024) as (encrypt(a)) PERSISTENT);
208
209 --echo # Stored procedures
210
211 delimiter //;
212 create procedure p1()
213 begin
214 select current_user();
215 end //
216
217 create function f1()
218 returns int
219 begin
220 return 1;
221 end //
222
223 delimiter ;//
224
225 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
226 create or replace table t1 (a int as (p1()) PERSISTENT);
227 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
228 create or replace table t1 (a int as (f1()) PERSISTENT);
229
230 drop procedure p1;
231 drop function f1;
232
233 --echo # Unknown functions
234 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
235 create or replace table t1 (a int as (f1()) PERSISTENT);
236
237 --echo #
238 --echo # GROUP BY FUNCTIONS
239 --echo #
240
241 --echo # AVG()
242 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
243 create or replace table t1 (a int, b int as (avg(a)));
244
245 --echo # BIT_AND()
246 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
247 create or replace table t1 (a int, b int as (bit_and(a)));
248
249 --echo # BIT_OR()
250 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
251 create or replace table t1 (a int, b int as (bit_or(a)));
252
253 --echo # BIT_XOR()
254 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
255 create or replace table t1 (a int, b int as (bit_xor(a)));
256
257 --echo # COUNT(DISTINCT)
258 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
259 create or replace table t1 (a int, b int as (count(distinct a)));
260
261 --echo # COUNT()
262 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
263 create or replace table t1 (a int, b int as (count(a)));
264
265 --echo # GROUP_CONCAT()
266 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
267 create or replace table t1 (a varchar(32), b int as (group_concat(a,'')));
268
269 --echo # MAX()
270 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
271 create or replace table t1 (a int, b int as (max(a)));
272
273 --echo # MIN()
274 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
275 create or replace table t1 (a int, b int as (min(a)));
276
277 --echo # STD()
278 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
279 create or replace table t1 (a int, b int as (std(a)));
280
281 --echo # STDDEV_POP()
282 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
283 create or replace table t1 (a int, b int as (stddev_pop(a)));
284
285 --echo # STDDEV_SAMP()
286 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
287 create or replace table t1 (a int, b int as (stddev_samp(a)));
288
289 --echo # STDDEV()
290 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
291 create or replace table t1 (a int, b int as (stddev(a)));
292
293 --echo # SUM()
294 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
295 create or replace table t1 (a int, b int as (sum(a)));
296
297 --echo # VAR_POP()
298 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
299 create or replace table t1 (a int, b int as (var_pop(a)));
300
301 --echo # VAR_SAMP()
302 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
303 create or replace table t1 (a int, b int as (var_samp(a)));
304
305 --echo # VARIANCE()
306 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
307 create or replace table t1 (a int, b int as (variance(a)));
308
309 --echo #
310 --echo # XML FUNCTIONS
311 --echo #
312
313 --echo # ExtractValue()
314 create or replace table t1 (a varchar(1024), b varchar(1024) as (ExtractValue(a,'//b[$@j]')) PERSISTENT);
315
316 --echo # UpdateXML()
317 create or replace table t1 (a varchar(1024), b varchar(1024) as (UpdateXML(a,'/a','<e>fff</e>')) PERSISTENT);
318
319 --echo #
320 --echo # Sub-selects
321 --echo #
322
323 create or replace table t1 (a int);
324 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
325 create or replace table t2 (a int, b int as (select count(*) from t1));
326 drop table t1;
327
328 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
329 create or replace table t1 (a int, b int as ((select 1)));
330 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
331 create or replace table t1 (a int, b int as (a+(select 1)));
332
333 --echo #
334 --echo # SP functions
335 --echo #
336
337 --disable_warnings
338 drop function if exists sub1;
339 --enable_warnings
340 create function sub1(i int) returns int deterministic
341 return i+1;
342 select sub1(1);
343 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
344 create or replace table t1 (a int, b int as (a+sub3(1)));
345 drop function sub1;
346
347 --echo #
348 --echo # Long expression
349
350 let $tmp_long_string = `SELECT repeat('a',240)`;
351 eval create or replace table t1 (a int, b varchar(300) as (concat(a,'$tmp_long_string')));
352 drop table t1;
353 let $tmp_long_string = `SELECT repeat('a',243)`;
354 eval create or replace table t1 (a int, b varchar(16384) as (concat(a,'$tmp_long_string'))) charset=latin1;
355
356 --disable_query_log
357 let $tmp_long_string = `SELECT repeat('a',65535)`;
358 --error ER_EXPRESSION_IS_TOO_BIG
359 eval create or replace table t1 (a int, b varchar(16384) as (concat(a,'$tmp_long_string'))) charset=latin1;
360 --enable_query_log
361
362 --echo #
363 --echo # Constant expression
364 create or replace table t1 (a int as (PI()) PERSISTENT);
365
366 drop table if exists t1;
367
368 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
369 create table t1 (a timestamp, b varchar(255) as (date_format(a, '%w %a %m %b')) stored);