Debian Codegraph @

mysql-test/suite/gcol/r/gcol_blocked_sql_funcs_innodb.result

raw
1 SET @@session.default_storage_engine = 'InnoDB';
2 # RAND()
3 create table t1 (b double generated always as (rand()) virtual);
4 ERROR HY000: Expression of generated column 'b' contains a disallowed function: rand.
5 # LOAD_FILE()
6 create table t1 (a varchar(64), b varchar(1024) generated always as (load_file(a)) virtual);
7 ERROR HY000: Expression of generated column 'b' contains a disallowed function: load_file.
8 # CURDATE()
9 create table t1 (a datetime generated always as (curdate()) virtual);
10 ERROR HY000: Expression of generated column 'a' contains a disallowed function: curdate.
11 # CURRENT_DATE(), CURRENT_DATE
12 create table t1 (a datetime generated always as (current_date) virtual);
13 ERROR HY000: Expression of generated column 'a' contains a disallowed function: curdate.
14 create table t1 (a datetime generated always as (current_date()) virtual);
15 ERROR HY000: Expression of generated column 'a' contains a disallowed function: curdate.
16 # CURRENT_TIME(), CURRENT_TIME
17 create table t1 (a datetime generated always as (current_time) virtual);
18 ERROR HY000: Expression of generated column 'a' contains a disallowed function: curtime.
19 create table t1 (a datetime generated always as (current_time()) virtual);
20 ERROR HY000: Expression of generated column 'a' contains a disallowed function: curtime.
21 # CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP
22 create table t1 (a datetime generated always as (current_timestamp()) virtual);
23 ERROR HY000: Expression of generated column 'a' contains a disallowed function: now.
24 create table t1 (a datetime generated always as (current_timestamp) virtual);
25 ERROR HY000: Expression of generated column 'a' contains a disallowed function: now.
26 # CURTIME()
27 create table t1 (a datetime generated always as (curtime()) virtual);
28 ERROR HY000: Expression of generated column 'a' contains a disallowed function: curtime.
29 # LOCALTIME(), LOCALTIME
30 create table t1 (a datetime, b varchar(10) generated always as (localtime()) virtual);
31 ERROR HY000: Expression of generated column 'b' contains a disallowed function: now.
32 create table t1 (a datetime, b varchar(10) generated always as (localtime) virtual);
33 ERROR HY000: Expression of generated column 'b' contains a disallowed function: now.
34 # LOCALTIMESTAMP, LOCALTIMESTAMP()(v4.0.6)
35 create table t1 (a datetime, b varchar(10) generated always as (localtimestamp()) virtual);
36 ERROR HY000: Expression of generated column 'b' contains a disallowed function: now.
37 create table t1 (a datetime, b varchar(10) generated always as (localtimestamp) virtual);
38 ERROR HY000: Expression of generated column 'b' contains a disallowed function: now.
39 # NOW()
40 create table t1 (a datetime, b varchar(10) generated always as (now()) virtual);
41 ERROR HY000: Expression of generated column 'b' contains a disallowed function: now.
42 # STATEMENT_DIGEST
43 create table t1 (a int, b int generated always as (statement_digest(a)) virtual);
44 ERROR HY000: Expression of generated column 'b' contains a disallowed function: statement_digest.
45 # STATEMENT_DIGEST_TEXT
46 create table t1 (a int, b int generated always as (statement_digest_text(a)) virtual);
47 ERROR HY000: Expression of generated column 'b' contains a disallowed function: statement_digest_text.
48 # SYSDATE()
49 create table t1 (a int, b varchar(10) generated always as (sysdate()) virtual);
50 ERROR HY000: Expression of generated column 'b' contains a disallowed function: sysdate.
51 # UNIX_TIMESTAMP()
52 create table t1 (a datetime, b datetime generated always as (unix_timestamp()) virtual);
53 ERROR HY000: Expression of generated column 'b' contains a disallowed function: unix_timestamp.
54 # UTC_DATE()
55 create table t1 (a datetime, b datetime generated always as (utc_date()) virtual);
56 ERROR HY000: Expression of generated column 'b' contains a disallowed function: utc_date.
57 # UTC_TIME()
58 create table t1 (a datetime, b datetime generated always as (utc_time()) virtual);
59 ERROR HY000: Expression of generated column 'b' contains a disallowed function: utc_time.
60 # UTC_TIMESTAMP()
61 create table t1 (a datetime, b datetime generated always as (utc_timestamp()) virtual);
62 ERROR HY000: Expression of generated column 'b' contains a disallowed function: utc_timestamp.
63 # MATCH()
64 # BENCHMARK()
65 create table t1 (a varchar(1024), b varchar(1024) generated always as (benchmark(a,3)) virtual);
66 ERROR HY000: Expression of generated column 'b' contains a disallowed function: benchmark.
67 # CONNECTION_ID()
68 create table t1 (a int generated always as (connection_id()) virtual);
69 ERROR HY000: Expression of generated column 'a' contains a disallowed function: connection_id.
70 # CURRENT_USER(), CURRENT_USER
71 create table t1 (a varchar(32) generated always as (current_user()) virtual);
72 ERROR HY000: Expression of generated column 'a' contains a disallowed function: current_user.
73 create table t1 (a varchar(32) generated always as (current_user) virtual);
74 ERROR HY000: Expression of generated column 'a' contains a disallowed function: current_user.
75 # DATABASE()
76 create table t1 (a varchar(1024), b varchar(1024) generated always as (database()) virtual);
77 ERROR HY000: Expression of generated column 'b' contains a disallowed function: database.
78 # FOUND_ROWS()
79 create table t1 (a varchar(1024), b varchar(1024) generated always as (found_rows()) virtual);
80 ERROR HY000: Expression of generated column 'b' contains a disallowed function: found_rows.
81 # GET_LOCK()
82 create table t1 (a varchar(1024), b varchar(1024) generated always as (get_lock(a,10)) virtual);
83 ERROR HY000: Expression of generated column 'b' contains a disallowed function: get_lock.
84 # IS_FREE_LOCK()
85 create table t1 (a varchar(1024), b varchar(1024) generated always as (is_free_lock(a)) virtual);
86 ERROR HY000: Expression of generated column 'b' contains a disallowed function: is_free_lock.
87 # IS_USED_LOCK()
88 create table t1 (a varchar(1024), b varchar(1024) generated always as (is_used_lock(a)) virtual);
89 ERROR HY000: Expression of generated column 'b' contains a disallowed function: is_used_lock.
90 # LAST_INSERT_ID()
91 create table t1 (a int generated always as (last_insert_id()) virtual);
92 ERROR HY000: Expression of generated column 'a' contains a disallowed function: last_insert_id.
93 # SOURCE_POS_WAIT()
94 create table t1 (a varchar(32), b int generated always as (source_pos_wait(a,0,2)) virtual);
95 ERROR HY000: Expression of generated column 'b' contains a disallowed function: source_pos_wait.
96 # NAME_CONST()
97 create table t1 (a varchar(32) generated always as (name_const('test',1)) virtual);
98 ERROR HY000: Expression of generated column 'a' contains a disallowed function.
99 # RELEASE_LOCK()
100 create table t1 (a varchar(32), b int generated always as (release_lock(a)) virtual);
101 ERROR HY000: Expression of generated column 'b' contains a disallowed function: release_lock.
102 # ROW_COUNT()
103 create table t1 (a int generated always as (row_count()) virtual);
104 ERROR HY000: Expression of generated column 'a' contains a disallowed function: row_count.
105 # SCHEMA()
106 create table t1 (a varchar(32) generated always as (schema()) virtual);
107 ERROR HY000: Expression of generated column 'a' contains a disallowed function: database.
108 # SESSION_USER()
109 create table t1 (a varchar(32) generated always as (session_user()) virtual);
110 ERROR HY000: Expression of generated column 'a' contains a disallowed function: user.
111 # SLEEP()
112 create table t1 (a int, b int generated always as (sleep(a)) virtual);
113 ERROR HY000: Expression of generated column 'b' contains a disallowed function: sleep.
114 # SYSTEM_USER()
115 create table t1 (a varchar(32) generated always as (system_user()) virtual);
116 ERROR HY000: Expression of generated column 'a' contains a disallowed function: user.
117 # USER()
118 create table t1 (a varchar(1024), b varchar(1024) generated always as (user()) virtual);
119 ERROR HY000: Expression of generated column 'b' contains a disallowed function: user.
120 # UUID_SHORT()
121 create table t1 (a varchar(1024) generated always as (uuid_short()) virtual);
122 ERROR HY000: Expression of generated column 'a' contains a disallowed function: uuid_short.
123 # UUID()
124 create table t1 (a varchar(1024) generated always as (uuid()) virtual);
125 ERROR HY000: Expression of generated column 'a' contains a disallowed function: uuid.
126 # VALUES()
127 create table t1 (a varchar(1024), b varchar(1024) generated always as (values(a)) virtual);
128 ERROR HY000: Expression of generated column 'b' contains a disallowed function: values.
129 # VERSION()
130 create table t1 (a varchar(1024), b varchar(1024) generated always as (version()) virtual);
131 ERROR HY000: Expression of generated column 'b' contains a disallowed function: version().
132 # ENCRYPT()
133 create table t1 (a varchar(1024), b varchar(1024) generated always as (encrypt(a)) virtual);
134 ERROR HY000: Expression of generated column 'b' contains a disallowed function: `encrypt`.
135 # Stored procedures
136 create procedure p1()
137 begin
138 select current_user();
139 end //
140 create function f1()
141 returns int
142 begin
143 return 1;
144 end //
145 create table t1 (a int generated always as (p1()) virtual);
146 ERROR HY000: Expression of generated column 'a' contains a disallowed function: `p1`.
147 create table t1 (a int generated always as (f1()) virtual);
148 ERROR HY000: Expression of generated column 'a' contains a disallowed function: `f1`.
149 drop procedure p1;
150 drop function f1;
151 # Unknown functions
152 create table t1 (a int generated always as (f1()) virtual);
153 ERROR HY000: Expression of generated column 'a' contains a disallowed function: `f1`.
154 #
155 # GROUP BY FUNCTIONS
156 #
157 # AVG()
158 create table t1 (a int, b int generated always as (avg(a)) virtual);
159 ERROR HY000: Invalid use of group function
160 # BIT_AND()
161 create table t1 (a int, b int generated always as (bit_and(a)) virtual);
162 ERROR HY000: Invalid use of group function
163 # BIT_OR()
164 create table t1 (a int, b int generated always as (bit_or(a)) virtual);
165 ERROR HY000: Invalid use of group function
166 # BIT_XOR()
167 create table t1 (a int, b int generated always as (bit_xor(a)) virtual);
168 ERROR HY000: Invalid use of group function
169 # COUNT(DISTINCT)
170 create table t1 (a int, b int generated always as (count(distinct a)) virtual);
171 ERROR HY000: Invalid use of group function
172 # COUNT()
173 create table t1 (a int, b int generated always as (count(a)) virtual);
174 ERROR HY000: Invalid use of group function
175 # GROUP_CONCAT()
176 create table t1 (a varchar(32), b int generated always as (group_concat(a,'')) virtual);
177 ERROR HY000: Invalid use of group function
178 # MAX()
179 create table t1 (a int, b int generated always as (max(a)) virtual);
180 ERROR HY000: Invalid use of group function
181 # MIN()
182 create table t1 (a int, b int generated always as (min(a)) virtual);
183 ERROR HY000: Invalid use of group function
184 # STD()
185 create table t1 (a int, b int generated always as (std(a)) virtual);
186 ERROR HY000: Invalid use of group function
187 # STDDEV_POP()
188 create table t1 (a int, b int generated always as (stddev_pop(a)) virtual);
189 ERROR HY000: Invalid use of group function
190 # STDDEV_SAMP()
191 create table t1 (a int, b int generated always as (stddev_samp(a)) virtual);
192 ERROR HY000: Invalid use of group function
193 # STDDEV()
194 create table t1 (a int, b int generated always as (stddev(a)) virtual);
195 ERROR HY000: Invalid use of group function
196 # SUM()
197 create table t1 (a int, b int generated always as (sum(a)) virtual);
198 ERROR HY000: Invalid use of group function
199 # VAR_POP()
200 create table t1 (a int, b int generated always as (var_pop(a)) virtual);
201 ERROR HY000: Invalid use of group function
202 # VAR_SAMP()
203 create table t1 (a int, b int generated always as (var_samp(a)) virtual);
204 ERROR HY000: Invalid use of group function
205 # VARIANCE()
206 create table t1 (a int, b int generated always as (variance(a)) virtual);
207 ERROR HY000: Invalid use of group function
208 #
209 # XML FUNCTIONS
210 #
211 # UpdateXML()
212 create table t1 (a varchar(1024), b varchar(1024) generated always as (UpdateXML(a,'/a','<e>fff</e>')) virtual);
213 ERROR HY000: Expression of generated column 'b' contains a disallowed function: updatexml.
214 #
215 # Sub-selects
216 #
217 create table t1 (a int);
218 create table t2 (a int, b int generated always as (select count(*) virtual from t1));
219 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select count(*) virtual from t1))' at line 1
220 drop table t1;
221 #
222 # Long expression
223 create table t1 (a int, b varchar(300) generated always as (concat(a,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')) virtual);
224 drop table t1;
225 create table t1 (a int, b varchar(300) generated always as (concat(a,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')) virtual);
226 drop table t1;
227 #
228 # Constant expression
229 create table t1 (a int generated always as (PI()) virtual);
230 drop table t1;
231 # bug#21098119: GCOL WITH MATCH/AGAINST -->
232 # ASSERTION FAILED: TR && TR->TABLE->FILE
233 #
234 create table t1 (a int);
235 alter table t1 add column r blob generated always
236 as (match(a) against ('' in boolean mode)) virtual;
237 ERROR HY000: Expression of generated column 'r' contains a disallowed function: match.
238 drop table t1;
239 #
240 # Bug#26279884 : VIRTUAL COLUMNS ALLOW DEPRECATED FUNCTION TO BE USED
241 #
242 # JSON_MERGE()
243 CREATE TABLE t2(a JSON, b JSON, c JSON GENERATED ALWAYS AS (JSON_MERGE(a,b)));
244 ERROR HY000: Expression of generated column 'c' contains a disallowed function: json_merge_preserve.
245 #
246 # Bug#29342245 ASSERTION WHEN CREATING A FUNCTIONAL INDEX ON A SUBQUERY WITH UNION
247 #
248 CREATE TABLE t1 (x INT);
249 CREATE TABLE t2 (y INT, z INT AS ((SELECT 1 FROM t1 UNION SELECT 1 FROM t1)));
250 ERROR HY000: Expression of generated column 'z' contains a disallowed function.
251 DROP TABLE t1;
252 DROP VIEW IF EXISTS v1,v2;
253 DROP TABLE IF EXISTS t1,t2,t3;
254 DROP PROCEDURE IF EXISTS p1;
255 DROP FUNCTION IF EXISTS f1;
256 DROP TRIGGER IF EXISTS trg1;
257 DROP TRIGGER IF EXISTS trg2;
258 set sql_warnings = 0;