Debian Codegraph @

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

raw
1 SET @@session.default_storage_engine = 'MyISAM';
2 create or replace table t1 (b double generated always as (rand()) virtual);
3 create or replace table t1 (a datetime generated always as (curdate()) virtual);
4 create or replace table t1 (a datetime generated always as (current_date) virtual);
5 create or replace table t1 (a datetime generated always as (current_date()) virtual);
6 create or replace table t1 (a datetime generated always as (current_time) virtual);
7 create or replace table t1 (a datetime generated always as (current_time()) virtual);
8 create or replace table t1 (a datetime generated always as (current_timestamp()) virtual);
9 create or replace table t1 (a datetime generated always as (current_timestamp) virtual);
10 create or replace table t1 (a datetime generated always as (curtime()) virtual);
11 create or replace table t1 (a datetime, b varchar(10) generated always as (localtime()) virtual);
12 create or replace table t1 (a datetime, b varchar(10) generated always as (localtime) virtual);
13 create or replace table t1 (a datetime, b varchar(10) generated always as (localtimestamp()) virtual);
14 create or replace table t1 (a datetime, b varchar(10) generated always as (localtimestamp) virtual);
15 create or replace table t1 (a datetime, b varchar(10) generated always as (now()) virtual);
16 create or replace table t1 (a int, b varchar(10) generated always as (sysdate()) virtual);
17 create or replace table t1 (a datetime, b datetime generated always as (unix_timestamp()) virtual);
18 create or replace table t1 (a datetime, b datetime generated always as (utc_date()) virtual);
19 create or replace table t1 (a datetime, b datetime generated always as (utc_time()) virtual);
20 create or replace table t1 (a datetime, b datetime generated always as (utc_timestamp()) virtual);
21 create or replace table t1 (a int generated always as (connection_id()) virtual);
22 create or replace table t1 (a varchar(32) generated always as (current_user()) virtual);
23 create or replace table t1 (a varchar(32) generated always as (current_user) virtual);
24 create or replace table t1 (a varchar(1024), b varchar(1024) generated always as (database()) virtual);
25 create or replace table t1 (a varchar(32) generated always as (schema()) virtual);
26 create or replace table t1 (a varchar(32) generated always as (session_user()) virtual);
27 create or replace table t1 (a varchar(32) generated always as (system_user()) virtual);
28 create or replace table t1 (a varchar(1024), b varchar(1024) generated always as (user()) virtual);
29 create or replace table t1 (a varchar(1024) generated always as (uuid_short()) virtual);
30 create or replace table t1 (a varchar(1024) generated always as (uuid()) virtual);
31 create or replace table t1 (a varchar(1024), b varchar(1024) generated always as (version()) virtual);
32 create or replace table t1 (a varchar(1024), b varchar(1024) generated always as (encrypt(a)) virtual);
33 create or replace table t1 (a varchar(1024), b varchar(1024) generated always as (UpdateXML(a,'/a','<e>fff</e>')) virtual);
34 drop table t1;
35 # LOAD_FILE()
36 create table t1 (a varchar(64), b varchar(1024) generated always as (load_file(a)) virtual);
37 ERROR HY000: Function or expression 'load_file()' cannot be used in the GENERATED ALWAYS AS clause of `b`
38 # MATCH()
39 create table t1 (a varchar(32), b bool generated always as (match a against ('sample text')) virtual);
40 ERROR HY000: Function or expression 'match ... against()' cannot be used in the GENERATED ALWAYS AS clause of `b`
41 # BENCHMARK()
42 create table t1 (a varchar(1024), b varchar(1024) generated always as (benchmark(a,3)) virtual);
43 ERROR HY000: Function or expression 'benchmark()' cannot be used in the GENERATED ALWAYS AS clause of `b`
44 # FOUND_ROWS()
45 create table t1 (a varchar(1024), b varchar(1024) generated always as (found_rows()) virtual);
46 ERROR HY000: Function or expression 'found_rows()' cannot be used in the GENERATED ALWAYS AS clause of `b`
47 # GET_LOCK()
48 create table t1 (a varchar(1024), b varchar(1024) generated always as (get_lock(a,10)) virtual);
49 ERROR HY000: Function or expression 'get_lock()' cannot be used in the GENERATED ALWAYS AS clause of `b`
50 # IS_FREE_LOCK()
51 create table t1 (a varchar(1024), b varchar(1024) generated always as (is_free_lock(a)) virtual);
52 ERROR HY000: Function or expression 'is_free_lock()' cannot be used in the GENERATED ALWAYS AS clause of `b`
53 # IS_USED_LOCK()
54 create table t1 (a varchar(1024), b varchar(1024) generated always as (is_used_lock(a)) virtual);
55 ERROR HY000: Function or expression 'is_used_lock()' cannot be used in the GENERATED ALWAYS AS clause of `b`
56 # LAST_INSERT_ID()
57 create table t1 (a int generated always as (last_insert_id()) virtual);
58 ERROR HY000: Function or expression 'last_insert_id()' cannot be used in the GENERATED ALWAYS AS clause of `a`
59 # MASTER_POS_WAIT()
60 create table t1 (a varchar(32), b int generated always as (master_pos_wait(a,0,2)) virtual);
61 ERROR HY000: Function or expression 'master_pos_wait()' cannot be used in the GENERATED ALWAYS AS clause of `b`
62 # NAME_CONST()
63 create table t1 (a varchar(32) generated always as (name_const('test',1)) virtual);
64 ERROR HY000: Function or expression 'name_const()' cannot be used in the GENERATED ALWAYS AS clause of `a`
65 # RELEASE_LOCK()
66 create table t1 (a varchar(32), b int generated always as (release_lock(a)) virtual);
67 ERROR HY000: Function or expression 'release_lock()' cannot be used in the GENERATED ALWAYS AS clause of `b`
68 # ROW_COUNT()
69 create table t1 (a int generated always as (row_count()) virtual);
70 ERROR HY000: Function or expression 'row_count()' cannot be used in the GENERATED ALWAYS AS clause of `a`
71 # SLEEP()
72 create table t1 (a int, b int generated always as (sleep(a)) virtual);
73 ERROR HY000: Function or expression 'sleep()' cannot be used in the GENERATED ALWAYS AS clause of `b`
74 # VALUES()
75 create table t1 (a varchar(1024), b varchar(1024) generated always as (value(a)) virtual);
76 ERROR HY000: Function or expression 'value()' cannot be used in the GENERATED ALWAYS AS clause of `b`
77 # Stored procedures
78 create procedure p1()
79 begin
80 select current_user();
81 end //
82 create function f1()
83 returns int
84 begin
85 return 1;
86 end //
87 create table t1 (a int generated always as (p1()) virtual);
88 ERROR HY000: Function or expression '`p1`()' cannot be used in the GENERATED ALWAYS AS clause of `a`
89 create table t1 (a int generated always as (f1()) virtual);
90 ERROR HY000: Function or expression '`f1`()' cannot be used in the GENERATED ALWAYS AS clause of `a`
91 drop procedure p1;
92 drop function f1;
93 # Unknown functions
94 create table t1 (a int generated always as (f1()) virtual);
95 ERROR HY000: Function or expression '`f1`()' cannot be used in the GENERATED ALWAYS AS clause of `a`
96 #
97 # GROUP BY FUNCTIONS
98 #
99 # AVG()
100 create table t1 (a int, b int generated always as (avg(a)) virtual);
101 ERROR HY000: Function or expression 'avg()' cannot be used in the GENERATED ALWAYS AS clause of `b`
102 # BIT_AND()
103 create table t1 (a int, b int generated always as (bit_and(a)) virtual);
104 ERROR HY000: Function or expression 'bit_and()' cannot be used in the GENERATED ALWAYS AS clause of `b`
105 # BIT_OR()
106 create table t1 (a int, b int generated always as (bit_or(a)) virtual);
107 ERROR HY000: Function or expression 'bit_or()' cannot be used in the GENERATED ALWAYS AS clause of `b`
108 # BIT_XOR()
109 create table t1 (a int, b int generated always as (bit_xor(a)) virtual);
110 ERROR HY000: Function or expression 'bit_xor()' cannot be used in the GENERATED ALWAYS AS clause of `b`
111 # COUNT(DISTINCT)
112 create table t1 (a int, b int generated always as (count(distinct a)) virtual);
113 ERROR HY000: Function or expression 'count(distinct )' cannot be used in the GENERATED ALWAYS AS clause of `b`
114 # COUNT()
115 create table t1 (a int, b int generated always as (count(a)) virtual);
116 ERROR HY000: Function or expression 'count()' cannot be used in the GENERATED ALWAYS AS clause of `b`
117 # GROUP_CONCAT()
118 create table t1 (a varchar(32), b int generated always as (group_concat(a,'')) virtual);
119 ERROR HY000: Function or expression 'group_concat()' cannot be used in the GENERATED ALWAYS AS clause of `b`
120 # MAX()
121 create table t1 (a int, b int generated always as (max(a)) virtual);
122 ERROR HY000: Function or expression 'max()' cannot be used in the GENERATED ALWAYS AS clause of `b`
123 # MIN()
124 create table t1 (a int, b int generated always as (min(a)) virtual);
125 ERROR HY000: Function or expression 'min()' cannot be used in the GENERATED ALWAYS AS clause of `b`
126 # STD()
127 create table t1 (a int, b int generated always as (std(a)) virtual);
128 ERROR HY000: Function or expression 'std()' cannot be used in the GENERATED ALWAYS AS clause of `b`
129 # STDDEV_POP()
130 create table t1 (a int, b int generated always as (stddev_pop(a)) virtual);
131 ERROR HY000: Function or expression 'std()' cannot be used in the GENERATED ALWAYS AS clause of `b`
132 # STDDEV_SAMP()
133 create table t1 (a int, b int generated always as (stddev_samp(a)) virtual);
134 ERROR HY000: Function or expression 'stddev_samp()' cannot be used in the GENERATED ALWAYS AS clause of `b`
135 # STDDEV()
136 create table t1 (a int, b int generated always as (stddev(a)) virtual);
137 ERROR HY000: Function or expression 'std()' cannot be used in the GENERATED ALWAYS AS clause of `b`
138 # SUM()
139 create table t1 (a int, b int generated always as (sum(a)) virtual);
140 ERROR HY000: Function or expression 'sum()' cannot be used in the GENERATED ALWAYS AS clause of `b`
141 # VAR_POP()
142 create table t1 (a int, b int generated always as (var_pop(a)) virtual);
143 ERROR HY000: Function or expression 'variance()' cannot be used in the GENERATED ALWAYS AS clause of `b`
144 # VAR_SAMP()
145 create table t1 (a int, b int generated always as (var_samp(a)) virtual);
146 ERROR HY000: Function or expression 'var_samp()' cannot be used in the GENERATED ALWAYS AS clause of `b`
147 # VARIANCE()
148 create table t1 (a int, b int generated always as (variance(a)) virtual);
149 ERROR HY000: Function or expression 'variance()' cannot be used in the GENERATED ALWAYS AS clause of `b`
150 #
151 # Sub-selects
152 #
153 create table t1 (a int);
154 create table t2 (a int, b int generated always as (select count(*) virtual from t1));
155 ERROR HY000: Function or expression 'select ...' cannot be used in the GENERATED ALWAYS AS clause of `b`
156 drop table t1;
157 #
158 # Long expression
159 create table t1 (a int, b varchar(300) generated always as (concat(a,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')) virtual);
160 drop table t1;
161 create table t1 (a int, b varchar(300) generated always as (concat(a,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')) virtual);
162 drop table t1;
163 #
164 # Constant expression
165 create table t1 (a int generated always as (PI()) virtual);
166 drop table t1;
167 # bug#21098119: GCOL WITH MATCH/AGAINST -->
168 # ASSERTION FAILED: TR && TR->TABLE->FILE
169 #
170 create table t1 (a int);
171 alter table t1 add column r blob generated always
172 as (match(a) against ('' in boolean mode)) virtual;
173 ERROR HY000: Function or expression 'match ... against()' cannot be used in the GENERATED ALWAYS AS clause of `r`
174 drop table t1;
175 #
176 # End of 10.3 tests
177 #
178 #
179 # MDEV-29029 Index corruption and/or assertion failure upon using RANDOM_BYTES for indexed virtual column
180 #
181 create table t1 (a int);
182 insert t1 values (1);
183 alter table t1 add column b binary(32) generated always as (random_bytes(a));
184 select a,length(b) from t1;
185 a length(b)
186 1 32
187 alter table t1 add index (b);
188 ERROR HY000: Function or expression 'random_bytes()' cannot be used in the GENERATED ALWAYS AS clause of `b`
189 alter table t1 add column c binary(32) default (random_bytes(a));
190 select a,length(b),length(c) from t1;
191 a length(b) length(c)
192 1 32 32
193 drop table t1;
194 #
195 #
196 # End of 10.10 tests
197 #
198 DROP VIEW IF EXISTS v1,v2;
199 DROP TABLE IF EXISTS t1,t2,t3;
200 DROP PROCEDURE IF EXISTS p1;
201 DROP FUNCTION IF EXISTS f1;
202 DROP TRIGGER IF EXISTS trg1;
203 DROP TRIGGER IF EXISTS trg2;
204 set sql_warnings = 0;