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 # MySQL doesn't support them, but MariaDB does:
18 #
19 create or replace table t1 (b double generated always as (rand()) virtual);
20 create or replace table t1 (a datetime generated always as (curdate()) virtual);
21 create or replace table t1 (a datetime generated always as (current_date) virtual);
22 create or replace table t1 (a datetime generated always as (current_date()) virtual);
23 create or replace table t1 (a datetime generated always as (current_time) virtual);
24 create or replace table t1 (a datetime generated always as (current_time()) virtual);
25 create or replace table t1 (a datetime generated always as (current_timestamp()) virtual);
26 create or replace table t1 (a datetime generated always as (current_timestamp) virtual);
27 create or replace table t1 (a datetime generated always as (curtime()) virtual);
28 create or replace table t1 (a datetime, b varchar(10) generated always as (localtime()) virtual);
29 create or replace table t1 (a datetime, b varchar(10) generated always as (localtime) virtual);
30 create or replace table t1 (a datetime, b varchar(10) generated always as (localtimestamp()) virtual);
31 create or replace table t1 (a datetime, b varchar(10) generated always as (localtimestamp) virtual);
32 create or replace table t1 (a datetime, b varchar(10) generated always as (now()) virtual);
33 create or replace table t1 (a int, b varchar(10) generated always as (sysdate()) virtual);
34 create or replace table t1 (a datetime, b datetime generated always as (unix_timestamp()) virtual);
35 create or replace table t1 (a datetime, b datetime generated always as (utc_date()) virtual);
36 create or replace table t1 (a datetime, b datetime generated always as (utc_time()) virtual);
37 create or replace table t1 (a datetime, b datetime generated always as (utc_timestamp()) virtual);
38 create or replace table t1 (a int generated always as (connection_id()) virtual);
39 create or replace table t1 (a varchar(32) generated always as (current_user()) virtual);
40 create or replace table t1 (a varchar(32) generated always as (current_user) virtual);
41 create or replace table t1 (a varchar(1024), b varchar(1024) generated always as (database()) virtual);
42 create or replace table t1 (a varchar(32) generated always as (schema()) virtual);
43 create or replace table t1 (a varchar(32) generated always as (session_user()) virtual);
44 create or replace table t1 (a varchar(32) generated always as (system_user()) virtual);
45 create or replace table t1 (a varchar(1024), b varchar(1024) generated always as (user()) virtual);
46 create or replace table t1 (a varchar(1024) generated always as (uuid_short()) virtual);
47 create or replace table t1 (a varchar(1024) generated always as (uuid()) virtual);
48 create or replace table t1 (a varchar(1024), b varchar(1024) generated always as (version()) virtual);
49 create or replace table t1 (a varchar(1024), b varchar(1024) generated always as (encrypt(a)) virtual);
50 create or replace table t1 (a varchar(1024), b varchar(1024) generated always as (UpdateXML(a,'/a','<e>fff</e>')) virtual);
51 drop table t1;
52
53 #
54 # NOTE: All SQL functions below should be rejected, otherwise BUG.
55 #
56
57 --echo # LOAD_FILE()
58 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
59 create table t1 (a varchar(64), b varchar(1024) generated always as (load_file(a)) virtual);
60
61 --echo # MATCH()
62 if (!$skip_full_text_check)
63 {
64 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
65 create table t1 (a varchar(32), b bool generated always as (match a against ('sample text')) virtual);
66 }
67
68 --echo # BENCHMARK()
69 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
70 create table t1 (a varchar(1024), b varchar(1024) generated always as (benchmark(a,3)) virtual);
71
72 --echo # FOUND_ROWS()
73 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
74 create table t1 (a varchar(1024), b varchar(1024) generated always as (found_rows()) virtual);
75
76 --echo # GET_LOCK()
77 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
78 create table t1 (a varchar(1024), b varchar(1024) generated always as (get_lock(a,10)) virtual);
79
80 --echo # IS_FREE_LOCK()
81 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
82 create table t1 (a varchar(1024), b varchar(1024) generated always as (is_free_lock(a)) virtual);
83
84 --echo # IS_USED_LOCK()
85 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
86 create table t1 (a varchar(1024), b varchar(1024) generated always as (is_used_lock(a)) virtual);
87
88 --echo # LAST_INSERT_ID()
89 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
90 create table t1 (a int generated always as (last_insert_id()) virtual);
91
92 --echo # MASTER_POS_WAIT()
93 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
94 create table t1 (a varchar(32), b int generated always as (master_pos_wait(a,0,2)) virtual);
95
96 --echo # NAME_CONST()
97 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
98 create table t1 (a varchar(32) generated always as (name_const('test',1)) virtual);
99
100 --echo # RELEASE_LOCK()
101 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
102 create table t1 (a varchar(32), b int generated always as (release_lock(a)) virtual);
103
104 --echo # ROW_COUNT()
105 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
106 create table t1 (a int generated always as (row_count()) virtual);
107
108 --echo # SLEEP()
109 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
110 create table t1 (a int, b int generated always as (sleep(a)) virtual);
111
112 --echo # VALUES()
113 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
114 create table t1 (a varchar(1024), b varchar(1024) generated always as (value(a)) virtual);
115
116 --echo # Stored procedures
117
118 delimiter //;
119 create procedure p1()
120 begin
121 select current_user();
122 end //
123
124 create function f1()
125 returns int
126 begin
127 return 1;
128 end //
129
130 delimiter ;//
131
132 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
133 create table t1 (a int generated always as (p1()) virtual);
134 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
135 create table t1 (a int generated always as (f1()) virtual);
136
137 drop procedure p1;
138 drop function f1;
139
140 --echo # Unknown functions
141 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
142 create table t1 (a int generated always as (f1()) virtual);
143
144 --echo #
145 --echo # GROUP BY FUNCTIONS
146 --echo #
147
148 --echo # AVG()
149 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
150 create table t1 (a int, b int generated always as (avg(a)) virtual);
151
152 --echo # BIT_AND()
153 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
154 create table t1 (a int, b int generated always as (bit_and(a)) virtual);
155
156 --echo # BIT_OR()
157 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
158 create table t1 (a int, b int generated always as (bit_or(a)) virtual);
159
160 --echo # BIT_XOR()
161 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
162 create table t1 (a int, b int generated always as (bit_xor(a)) virtual);
163
164 --echo # COUNT(DISTINCT)
165 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
166 create table t1 (a int, b int generated always as (count(distinct a)) virtual);
167
168 --echo # COUNT()
169 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
170 create table t1 (a int, b int generated always as (count(a)) virtual);
171
172 --echo # GROUP_CONCAT()
173 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
174 create table t1 (a varchar(32), b int generated always as (group_concat(a,'')) virtual);
175
176 --echo # MAX()
177 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
178 create table t1 (a int, b int generated always as (max(a)) virtual);
179
180 --echo # MIN()
181 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
182 create table t1 (a int, b int generated always as (min(a)) virtual);
183
184 --echo # STD()
185 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
186 create table t1 (a int, b int generated always as (std(a)) virtual);
187
188 --echo # STDDEV_POP()
189 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
190 create table t1 (a int, b int generated always as (stddev_pop(a)) virtual);
191
192 --echo # STDDEV_SAMP()
193 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
194 create table t1 (a int, b int generated always as (stddev_samp(a)) virtual);
195
196 --echo # STDDEV()
197 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
198 create table t1 (a int, b int generated always as (stddev(a)) virtual);
199
200 --echo # SUM()
201 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
202 create table t1 (a int, b int generated always as (sum(a)) virtual);
203
204 --echo # VAR_POP()
205 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
206 create table t1 (a int, b int generated always as (var_pop(a)) virtual);
207
208 --echo # VAR_SAMP()
209 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
210 create table t1 (a int, b int generated always as (var_samp(a)) virtual);
211
212 --echo # VARIANCE()
213 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
214 create table t1 (a int, b int generated always as (variance(a)) virtual);
215
216 --echo #
217 --echo # Sub-selects
218 --echo #
219
220 create table t1 (a int);
221 -- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
222 create table t2 (a int, b int generated always as (select count(*) virtual from t1));
223 drop table t1;
224
225 --echo #
226 --echo # Long expression
227
228 let $tmp_long_string = `SELECT repeat('a',240)`;
229 eval create table t1 (a int, b varchar(300) generated always as (concat(a,'$tmp_long_string')) virtual);
230 drop table t1;
231 let $tmp_long_string = `SELECT repeat('a',243)`;
232 # Limit is lifted to 64K. TODO write a test for it.
233 # --error 1470
234 eval create table t1 (a int, b varchar(300) generated always as (concat(a,'$tmp_long_string')) virtual);
235 drop table t1;
236
237 --echo #
238 --echo # Constant expression
239 create table t1 (a int generated always as (PI()) virtual);
240 drop table t1;
241
242 --echo # bug#21098119: GCOL WITH MATCH/AGAINST -->
243 --echo # ASSERTION FAILED: TR && TR->TABLE->FILE
244 --echo #
245 create table t1 (a int);
246 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
247 alter table t1 add column r blob generated always
248 as (match(a) against ('' in boolean mode)) virtual;
249 drop table t1;
250
251 --echo #
252 --echo # End of 10.3 tests
253 --echo #
254
255 --echo #
256 --echo # MDEV-29029 Index corruption and/or assertion failure upon using RANDOM_BYTES for indexed virtual column
257 --echo #
258
259 create table t1 (a int);
260 insert t1 values (1);
261 alter table t1 add column b binary(32) generated always as (random_bytes(a));
262 select a,length(b) from t1;
263 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
264 alter table t1 add index (b);
265 alter table t1 add column c binary(32) default (random_bytes(a));
266 select a,length(b),length(c) from t1;
267 drop table t1;
268
269 --echo #
270 --echo #
271 --echo # End of 10.10 tests
272 --echo #