Debian Codegraph @

mysql-test/suite/compat/oracle/t/ps.test

raw
1 SET sql_mode=ORACLE;
2
3 --echo #
4 --echo # MDEV-10801 sql_mode: dynamic SQL placeholders
5 --echo #
6
7 SET @a=10, @b=20;
8 PREPARE stmt FROM 'SELECT ?,?';
9 EXECUTE stmt USING @a, @b;
10 PREPARE stmt FROM 'SELECT :a,:b';
11 EXECUTE stmt USING @a, @b;
12 PREPARE stmt FROM 'SELECT :aaa,:bbb';
13 EXECUTE stmt USING @a, @b;
14 PREPARE stmt FROM 'SELECT :"a",:"b"';
15 EXECUTE stmt USING @a, @b;
16 PREPARE stmt FROM 'SELECT :"aaa",:"bbb"';
17 EXECUTE stmt USING @a, @b;
18 PREPARE stmt FROM 'SELECT :1,:2';
19 EXECUTE stmt USING @a, @b;
20 PREPARE stmt FROM 'SELECT :222,:111';
21 EXECUTE stmt USING @a, @b;
22 PREPARE stmt FROM 'SELECT :0,:65535';
23 EXECUTE stmt USING @a, @b;
24 PREPARE stmt FROM 'SELECT :65535,:0';
25 EXECUTE stmt USING @a, @b;
26
27 --echo #
28 --echo # MDEV-10709 Expressions as parameters to Dynamic SQL
29 --echo #
30
31 --echo #
32 --echo # Testing disallowed expressions in USING
33 --echo #
34
35 PREPARE stmt FROM 'SELECT :1 FROM DUAL';
36 --error ER_SUBQUERIES_NOT_SUPPORTED
37 EXECUTE stmt USING (SELECT 1);
38 DEALLOCATE PREPARE stmt;
39
40 DELIMITER $$;
41 CREATE FUNCTION f1() RETURN VARCHAR
42 AS
43 BEGIN
44 RETURN 'test';
45 END;
46 $$
47 DELIMITER ;$$
48 PREPARE stmt FROM 'SELECT ? FROM DUAL';
49 --error ER_SUBQUERIES_NOT_SUPPORTED
50 EXECUTE stmt USING f1();
51 DEALLOCATE PREPARE stmt;
52 DROP FUNCTION f1;
53
54 --echo #
55 --echo # Using a user variable as a EXECUTE..USING out parameter
56 --echo #
57
58 DELIMITER /;
59 CREATE PROCEDURE p1(a OUT INT)
60 AS
61 BEGIN
62 a:= 10;
63 END;
64 /
65 DELIMITER ;/
66 SET @a=1;
67 CALL p1(@a);
68 SELECT @a;
69 SET @a=2;
70 PREPARE stmt FROM 'CALL p1(?)';
71 EXECUTE stmt USING @a;
72 SELECT @a;
73 DROP PROCEDURE p1;
74
75
76 --echo #
77 --echo # Using an SP variable as a EXECUTE..USING out parameter
78 --echo #
79
80 DELIMITER /;
81 CREATE PROCEDURE p1 (a OUT INT)
82 AS
83 BEGIN
84 a:=10;
85 END;
86 /
87 CREATE PROCEDURE p2 (a OUT INT)
88 AS
89 BEGIN
90 PREPARE stmt FROM 'CALL p1(?)';
91 EXECUTE stmt USING a;
92 END;
93 /
94 DELIMITER ;/
95 SET @a= 1;
96 CALL p2(@a);
97 SELECT @a;
98 DROP PROCEDURE p2;
99 DROP PROCEDURE p1;
100
101
102 --echo #
103 --echo # Using a trigger field as a EXECUTE..USING out parameter
104 --echo #
105 DELIMITER /;
106 CREATE PROCEDURE p1 (a OUT INT)
107 AS
108 BEGIN
109 a:= 10;
110 END;
111 /
112 DELIMITER ;/
113 CREATE TABLE t1 (a INT);
114 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW CALL p1(:NEW.a);
115 INSERT INTO t1 VALUES (1);
116 SELECT * FROM t1;
117 DROP TABLE t1;
118 DROP PROCEDURE p1;
119
120
121 --echo #
122 --echo # Testing re-prepare on a table metadata update between PREPARE and EXECUTE
123 --echo #
124
125 CREATE TABLE t1 (a INT);
126 DELIMITER /;
127 CREATE PROCEDURE p1(a IN INT)
128 AS
129 BEGIN
130 INSERT INTO t1 VALUES (a);
131 END;
132 /
133 DELIMITER ;/
134 PREPARE stmt FROM 'CALL p1(?)';
135 EXECUTE stmt USING 10;
136 SELECT * FROM t1;
137 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW NEW.a:=NEW.a+1;
138 EXECUTE stmt USING 20;
139 SELECT * FROM t1;
140 DEALLOCATE PREPARE stmt;
141 DROP PROCEDURE p1;
142 DROP TABLE t1;
143
144 --echo #
145 --echo # End of MDEV-10709 Expressions as parameters to Dynamic SQL
146 --echo #
147
148 --echo #
149 --echo # MDEV-10585 EXECUTE IMMEDIATE statement
150 --echo #
151
152 --echo #
153 --echo # Testing disallowed expressions in USING
154 --echo #
155
156 --error ER_SUBQUERIES_NOT_SUPPORTED
157 EXECUTE IMMEDIATE 'SELECT :1 FROM DUAL' USING (SELECT 1);
158
159 DELIMITER $$;
160 CREATE FUNCTION f1() RETURN VARCHAR
161 AS
162 BEGIN
163 RETURN 'test';
164 END;
165 $$
166 DELIMITER ;$$
167 --error ER_SUBQUERIES_NOT_SUPPORTED
168 EXECUTE IMMEDIATE 'SELECT ? FROM DUAL' USING f1();
169 DROP FUNCTION f1;
170
171
172 --echo #
173 --echo # Testing simple expressions
174 --echo #
175
176 EXECUTE IMMEDIATE 'SELECT :1 FROM DUAL' USING 10;
177
178
179 --echo #
180 --echo # MDEV-10866 Extend PREPARE and EXECUTE IMMEDIATE to understand expressions
181 --echo #
182
183 --echo #
184 --echo # Testing erroneous and diallowed prepare source
185 --echo #
186
187 --error ER_CANT_AGGREGATE_2COLLATIONS
188 EXECUTE IMMEDIATE _latin1'SELECT 1 AS c FROM ' || _latin2 'DUAL';
189 --error ER_CANT_AGGREGATE_2COLLATIONS
190 PREPARE stmt FROM _latin1'SELECT 1 AS c FROM ' || _latin2 'DUAL';
191
192 --error ER_SUBQUERIES_NOT_SUPPORTED
193 EXECUTE IMMEDIATE (SELECT 'SELECT 1');
194 --error ER_SUBQUERIES_NOT_SUPPORTED
195 PREPARE stmt FROM (SELECT 'SELECT 1');
196
197 --error ER_BAD_FIELD_ERROR
198 EXECUTE IMMEDIATE a;
199 --error ER_BAD_FIELD_ERROR
200 PREPARE stmt FROM a;
201
202 --error ER_PARSE_ERROR
203 EXECUTE IMMEDIATE NULL;
204 --error ER_PARSE_ERROR
205 PREPARE stmt FROM NULL;
206
207 --error ER_PARSE_ERROR
208 EXECUTE IMMEDIATE COALESCE(NULL);
209 --error ER_PARSE_ERROR
210 PREPARE stmt FROM COALESCE(NULL);
211
212 DELIMITER $$;
213 CREATE FUNCTION f1() RETURN VARCHAR
214 AS
215 BEGIN
216 RETURN 't1';
217 END;
218 $$
219 DELIMITER ;$$
220 --error ER_SUBQUERIES_NOT_SUPPORTED
221 EXECUTE IMMEDIATE f1();
222 --error ER_SUBQUERIES_NOT_SUPPORTED
223 PREPARE stmt FROM f1();
224 DROP FUNCTION f1;
225
226 --echo #
227 --echo # Testing user variables in prepare source
228 --echo #
229
230 SET @table_name='DUAL';
231 EXECUTE IMMEDIATE 'SELECT 1 AS a FROM ' || @table_name;
232 PREPARE stmt FROM 'SELECT 1 AS a FROM ' || @table_name;
233 EXECUTE stmt;
234 DEALLOCATE PREPARE stmt;
235
236 --echo #
237 --echo # Testing SP parameters and variables in prepare source
238 --echo #
239
240 DELIMITER $$;
241 CREATE PROCEDURE p1(table_name VARCHAR)
242 AS
243 BEGIN
244 EXECUTE IMMEDIATE 'SELECT 1 AS c FROM '|| table_name;
245 END;
246 $$
247 DELIMITER ;$$
248 CALL p1('DUAL');
249 DROP PROCEDURE p1;
250
251 DELIMITER $$;
252 CREATE PROCEDURE p1()
253 AS
254 table_name VARCHAR(64):='DUAL';
255 BEGIN
256 EXECUTE IMMEDIATE 'SELECT 1 AS c FROM ' || table_name;
257 END;
258 $$
259 DELIMITER ;$$
260 CALL p1();
261 DROP PROCEDURE p1;
262
263
264 --echo #
265 --echo # End of MDEV-10866 Extend PREPARE and EXECUTE IMMEDIATE to understand expressions
266 --echo #
267
268
269 --echo #
270 --echo # MDEV-12846 sql_mode=ORACLE: using Oracle-style placeholders in direct query execution makes the server crash
271 --echo #
272
273 # When running with --ps, the below queries return
274 # CR_PARAMS_NOT_BOUND instead of ER_PARSE_ERROR
275
276 --disable_ps_protocol
277 --error ER_PARSE_ERROR
278 SELECT ? FROM DUAL;
279 --error ER_PARSE_ERROR
280 SELECT :a FROM DUAL;
281 --error ER_PARSE_ERROR
282 SELECT :1 FROM DUAL;
283
284 --error ER_PARSE_ERROR
285 SELECT 1+? FROM DUAL;
286 --error ER_PARSE_ERROR
287 SELECT 1+:a FROM DUAL;
288 --error ER_PARSE_ERROR
289 SELECT 1+:1 FROM DUAL;
290 --enable_ps_protocol