Debian Codegraph @

mysql-test/suite/gcol/inc/gcol_blocked_sql_funcs_main.inc

raw
1 ################################################################################
2 # inc/gcol_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: #
12 # Change Date: #
13 # Change: #
14 ################################################################################
15
16 #
17 # NOTE: All SQL functions should be rejected, otherwise BUG.
18 #
19
20 --echo # RAND()
21 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
22 create table t1 (b double generated always as (rand()) virtual);
23
24 --echo # LOAD_FILE()
25 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
26 create table t1 (a varchar(64), b varchar(1024) generated always as (load_file(a)) virtual);
27
28 --echo # CURDATE()
29 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
30 create table t1 (a datetime generated always as (curdate()) virtual);
31
32 --echo # CURRENT_DATE(), CURRENT_DATE
33 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
34 create table t1 (a datetime generated always as (current_date) virtual);
35 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
36 create table t1 (a datetime generated always as (current_date()) virtual);
37
38 --echo # CURRENT_TIME(), CURRENT_TIME
39 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
40 create table t1 (a datetime generated always as (current_time) virtual);
41 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
42 create table t1 (a datetime generated always as (current_time()) virtual);
43
44 --echo # CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP
45 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
46 create table t1 (a datetime generated always as (current_timestamp()) virtual);
47 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
48 create table t1 (a datetime generated always as (current_timestamp) virtual);
49
50 --echo # CURTIME()
51 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
52 create table t1 (a datetime generated always as (curtime()) virtual);
53
54 --echo # LOCALTIME(), LOCALTIME
55 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
56 create table t1 (a datetime, b varchar(10) generated always as (localtime()) virtual);
57 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
58 create table t1 (a datetime, b varchar(10) generated always as (localtime) virtual);
59
60 --echo # LOCALTIMESTAMP, LOCALTIMESTAMP()(v4.0.6)
61 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
62 create table t1 (a datetime, b varchar(10) generated always as (localtimestamp()) virtual);
63 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
64 create table t1 (a datetime, b varchar(10) generated always as (localtimestamp) virtual);
65
66 --echo # NOW()
67 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
68 create table t1 (a datetime, b varchar(10) generated always as (now()) virtual);
69
70 --echo # STATEMENT_DIGEST
71 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
72 create table t1 (a int, b int generated always as (statement_digest(a)) virtual);
73
74 --echo # STATEMENT_DIGEST_TEXT
75 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
76 create table t1 (a int, b int generated always as (statement_digest_text(a)) virtual);
77
78 --echo # SYSDATE()
79 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
80 create table t1 (a int, b varchar(10) generated always as (sysdate()) virtual);
81
82 --echo # UNIX_TIMESTAMP()
83 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
84 create table t1 (a datetime, b datetime generated always as (unix_timestamp()) virtual);
85
86 --echo # UTC_DATE()
87 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
88 create table t1 (a datetime, b datetime generated always as (utc_date()) virtual);
89
90 --echo # UTC_TIME()
91 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
92 create table t1 (a datetime, b datetime generated always as (utc_time()) virtual);
93
94 --echo # UTC_TIMESTAMP()
95 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
96 create table t1 (a datetime, b datetime generated always as (utc_timestamp()) virtual);
97
98 --echo # MATCH()
99 if (!$skip_full_text_check)
100 {
101 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
102 create table t1 (a varchar(32), b bool generated always as (match a against ('sample text')) virtual);
103 }
104
105 --echo # BENCHMARK()
106 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
107 create table t1 (a varchar(1024), b varchar(1024) generated always as (benchmark(a,3)) virtual);
108
109 --echo # CONNECTION_ID()
110 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
111 create table t1 (a int generated always as (connection_id()) virtual);
112
113 --echo # CURRENT_USER(), CURRENT_USER
114 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
115 create table t1 (a varchar(32) generated always as (current_user()) virtual);
116 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
117 create table t1 (a varchar(32) generated always as (current_user) virtual);
118
119 --echo # DATABASE()
120 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
121 create table t1 (a varchar(1024), b varchar(1024) generated always as (database()) virtual);
122
123 --echo # FOUND_ROWS()
124 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
125 create table t1 (a varchar(1024), b varchar(1024) generated always as (found_rows()) virtual);
126
127 --echo # GET_LOCK()
128 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
129 create table t1 (a varchar(1024), b varchar(1024) generated always as (get_lock(a,10)) virtual);
130
131 --echo # IS_FREE_LOCK()
132 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
133 create table t1 (a varchar(1024), b varchar(1024) generated always as (is_free_lock(a)) virtual);
134
135 --echo # IS_USED_LOCK()
136 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
137 create table t1 (a varchar(1024), b varchar(1024) generated always as (is_used_lock(a)) virtual);
138
139 --echo # LAST_INSERT_ID()
140 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
141 create table t1 (a int generated always as (last_insert_id()) virtual);
142
143 --echo # SOURCE_POS_WAIT()
144 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
145 create table t1 (a varchar(32), b int generated always as (source_pos_wait(a,0,2)) virtual);
146
147 --echo # NAME_CONST()
148 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
149 create table t1 (a varchar(32) generated always as (name_const('test',1)) virtual);
150
151 --echo # RELEASE_LOCK()
152 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
153 create table t1 (a varchar(32), b int generated always as (release_lock(a)) virtual);
154
155 --echo # ROW_COUNT()
156 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
157 create table t1 (a int generated always as (row_count()) virtual);
158
159 --echo # SCHEMA()
160 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
161 create table t1 (a varchar(32) generated always as (schema()) virtual);
162
163 --echo # SESSION_USER()
164 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
165 create table t1 (a varchar(32) generated always as (session_user()) virtual);
166
167 --echo # SLEEP()
168 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
169 create table t1 (a int, b int generated always as (sleep(a)) virtual);
170
171 --echo # SYSTEM_USER()
172 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
173 create table t1 (a varchar(32) generated always as (system_user()) virtual);
174
175 --echo # USER()
176 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
177 create table t1 (a varchar(1024), b varchar(1024) generated always as (user()) virtual);
178
179 --echo # UUID_SHORT()
180 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
181 create table t1 (a varchar(1024) generated always as (uuid_short()) virtual);
182
183 --echo # UUID()
184 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
185 create table t1 (a varchar(1024) generated always as (uuid()) virtual);
186
187 --echo # VALUES()
188 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
189 create table t1 (a varchar(1024), b varchar(1024) generated always as (values(a)) virtual);
190
191 --echo # VERSION()
192 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
193 create table t1 (a varchar(1024), b varchar(1024) generated always as (version()) virtual);
194
195 --echo # ENCRYPT()
196 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
197 create table t1 (a varchar(1024), b varchar(1024) generated always as (encrypt(a)) virtual);
198
199 --echo # Stored procedures
200
201 delimiter //;
202 create procedure p1()
203 begin
204 select current_user();
205 end //
206
207 create function f1()
208 returns int
209 begin
210 return 1;
211 end //
212
213 delimiter ;//
214
215 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
216 create table t1 (a int generated always as (p1()) virtual);
217 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
218 create table t1 (a int generated always as (f1()) virtual);
219
220 drop procedure p1;
221 drop function f1;
222
223 --echo # Unknown functions
224 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
225 create table t1 (a int generated always as (f1()) virtual);
226
227 --echo #
228 --echo # GROUP BY FUNCTIONS
229 --echo #
230
231 --echo # AVG()
232 -- error 1111
233 create table t1 (a int, b int generated always as (avg(a)) virtual);
234
235 --echo # BIT_AND()
236 -- error 1111
237 create table t1 (a int, b int generated always as (bit_and(a)) virtual);
238
239 --echo # BIT_OR()
240 -- error 1111
241 create table t1 (a int, b int generated always as (bit_or(a)) virtual);
242
243 --echo # BIT_XOR()
244 -- error 1111
245 create table t1 (a int, b int generated always as (bit_xor(a)) virtual);
246
247 --echo # COUNT(DISTINCT)
248 -- error 1111
249 create table t1 (a int, b int generated always as (count(distinct a)) virtual);
250
251 --echo # COUNT()
252 -- error 1111
253 create table t1 (a int, b int generated always as (count(a)) virtual);
254
255 --echo # GROUP_CONCAT()
256 -- error 1111
257 create table t1 (a varchar(32), b int generated always as (group_concat(a,'')) virtual);
258
259 --echo # MAX()
260 -- error 1111
261 create table t1 (a int, b int generated always as (max(a)) virtual);
262
263 --echo # MIN()
264 -- error 1111
265 create table t1 (a int, b int generated always as (min(a)) virtual);
266
267 --echo # STD()
268 -- error 1111
269 create table t1 (a int, b int generated always as (std(a)) virtual);
270
271 --echo # STDDEV_POP()
272 -- error 1111
273 create table t1 (a int, b int generated always as (stddev_pop(a)) virtual);
274
275 --echo # STDDEV_SAMP()
276 -- error 1111
277 create table t1 (a int, b int generated always as (stddev_samp(a)) virtual);
278
279 --echo # STDDEV()
280 -- error 1111
281 create table t1 (a int, b int generated always as (stddev(a)) virtual);
282
283 --echo # SUM()
284 -- error 1111
285 create table t1 (a int, b int generated always as (sum(a)) virtual);
286
287 --echo # VAR_POP()
288 -- error 1111
289 create table t1 (a int, b int generated always as (var_pop(a)) virtual);
290
291 --echo # VAR_SAMP()
292 -- error 1111
293 create table t1 (a int, b int generated always as (var_samp(a)) virtual);
294
295 --echo # VARIANCE()
296 -- error 1111
297 create table t1 (a int, b int generated always as (variance(a)) virtual);
298
299 --echo #
300 --echo # XML FUNCTIONS
301 --echo #
302
303 --echo # UpdateXML()
304 -- error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
305 create table t1 (a varchar(1024), b varchar(1024) generated always as (UpdateXML(a,'/a','<e>fff</e>')) virtual);
306
307 --echo #
308 --echo # Sub-selects
309 --echo #
310
311 create table t1 (a int);
312 -- error 1064
313 create table t2 (a int, b int generated always as (select count(*) virtual from t1));
314 drop table t1;
315
316 --echo #
317 --echo # Long expression
318
319 let $tmp_long_string = `SELECT repeat('a',240)`;
320 eval create table t1 (a int, b varchar(300) generated always as (concat(a,'$tmp_long_string')) virtual);
321 drop table t1;
322 let $tmp_long_string = `SELECT repeat('a',243)`;
323 # Limit is lifted to 64K. TODO write a test for it.
324 # --error 1470
325 eval create table t1 (a int, b varchar(300) generated always as (concat(a,'$tmp_long_string')) virtual);
326 drop table t1;
327
328 --echo #
329 --echo # Constant expression
330 create table t1 (a int generated always as (PI()) virtual);
331 drop table t1;
332
333 if(! $testing_ndb) {
334 --echo # bug#21098119: GCOL WITH MATCH/AGAINST -->
335 --echo # ASSERTION FAILED: TR && TR->TABLE->FILE
336 --echo #
337 create table t1 (a int);
338 --error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
339 alter table t1 add column r blob generated always
340 as (match(a) against ('' in boolean mode)) virtual;
341 drop table t1;
342 }
343
344 # Deprecated functions are not allowed in generation expressions
345 --echo #
346 --echo # Bug#26279884 : VIRTUAL COLUMNS ALLOW DEPRECATED FUNCTION TO BE USED
347 --echo #
348
349 --echo # JSON_MERGE()
350 --error ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED
351 CREATE TABLE t2(a JSON, b JSON, c JSON GENERATED ALWAYS AS (JSON_MERGE(a,b)));
352
353 --echo #
354 --echo # Bug#29342245 ASSERTION WHEN CREATING A FUNCTIONAL INDEX ON A SUBQUERY WITH UNION
355 --echo #
356 CREATE TABLE t1 (x INT);
357 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
358 CREATE TABLE t2 (y INT, z INT AS ((SELECT 1 FROM t1 UNION SELECT 1 FROM t1)));
359 DROP TABLE t1;