Debian Codegraph @

mysql-test/suite/funcs_1/r/myisam_storedproc_10.result

raw
1 SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION';
2
3 --source suite/funcs_1/storedproc/load_sp_tb.inc
4 --------------------------------------------------------------------------------
5
6 --source suite/funcs_1/storedproc/cleanup_sp_tb.inc
7 --------------------------------------------------------------------------------
8 DROP DATABASE IF EXISTS db_storedproc;
9 DROP DATABASE IF EXISTS db_storedproc_1;
10 CREATE DATABASE db_storedproc;
11 CREATE DATABASE db_storedproc_1;
12 USE db_storedproc;
13 create table t1(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
14 engine = <engine_to_be_tested>;
15 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t1;
16 create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
17 engine = <engine_to_be_tested>;
18 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t2;
19 create table t3(f1 char(20),f2 char(20),f3 integer) engine = <engine_to_be_tested>;
20 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t3.txt' into table t3;
21 create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
22 engine = <engine_to_be_tested>;
23 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t4;
24 USE db_storedproc_1;
25 create table t6(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
26 engine = <engine_to_be_tested>;
27 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6;
28 USE db_storedproc;
29 create table t7 (f1 char(20), f2 char(25), f3 date, f4 int)
30 engine = <engine_to_be_tested>;
31 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t7;
32 Warnings:
33 Warning 1265 Data truncated for column 'f3' at row 1
34 Warning 1265 Data truncated for column 'f3' at row 2
35 Warning 1265 Data truncated for column 'f3' at row 3
36 Warning 1265 Data truncated for column 'f3' at row 4
37 Warning 1265 Data truncated for column 'f3' at row 5
38 Warning 1265 Data truncated for column 'f3' at row 6
39 Warning 1265 Data truncated for column 'f3' at row 7
40 Warning 1265 Data truncated for column 'f3' at row 8
41 Warning 1265 Data truncated for column 'f3' at row 9
42 Warning 1265 Data truncated for column 'f3' at row 10
43 create table t8 (f1 char(20), f2 char(25), f3 date, f4 int)
44 engine = <engine_to_be_tested>;
45 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t8;
46 Warnings:
47 Warning 1265 Data truncated for column 'f3' at row 1
48 Warning 1265 Data truncated for column 'f3' at row 2
49 Warning 1265 Data truncated for column 'f3' at row 3
50 Warning 1265 Data truncated for column 'f3' at row 4
51 Warning 1265 Data truncated for column 'f3' at row 5
52 Warning 1265 Data truncated for column 'f3' at row 6
53 Warning 1265 Data truncated for column 'f3' at row 7
54 Warning 1265 Data truncated for column 'f3' at row 8
55 Warning 1265 Data truncated for column 'f3' at row 9
56 Warning 1265 Data truncated for column 'f3' at row 10
57 create table t9(f1 int, f2 char(25), f3 int) engine = <engine_to_be_tested>;
58 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t9.txt' into table t9;
59 create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
60 engine = <engine_to_be_tested>;
61 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t10;
62 create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
63 engine = <engine_to_be_tested>;
64 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t11;
65
66 Section 3.1.10 - CALL checks:
67 --------------------------------------------------------------------------------
68 USE db_storedproc;
69
70 Testcase 3.1.10.2 + 3.1.10.5:
71 -----------------------------
72
73 2. Ensure that a procedure cannot be called if the appropriate privileges do not
74 exist.
75 5. Ensure that a function cannot be executed if the appropriate privileges do
76 not exist.
77 --------------------------------------------------------------------------------
78 DROP PROCEDURE IF EXISTS sp31102;
79 DROP FUNCTION IF EXISTS fn31105;
80 create user 'user_1'@'localhost';
81 create user 'user_2'@'localhost';
82 GRANT CREATE ROUTINE ON db_storedproc.* TO 'user_1'@'localhost';
83 GRANT SELECT ON db_storedproc.* TO 'user_2'@'localhost';
84 FLUSH PRIVILEGES;
85 connect user2_1, localhost, user_1, , db_storedproc;
86
87 user_1@localhost db_storedproc
88 CREATE PROCEDURE sp31102 () SQL SECURITY INVOKER
89 BEGIN
90 SELECT * FROM db_storedproc.t1 WHERE f4=-5000 LIMIT 1;
91 END//
92 CREATE FUNCTION fn31105(n INT) RETURNS INT
93 BEGIN
94 DECLARE res INT;
95 SET res = n * n;
96 RETURN res;
97 END//
98 disconnect user2_1;
99 connect user2_2, localhost, user_2, , db_storedproc;
100
101 user_2@localhost db_storedproc
102 CALL sp31102();
103 ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc.sp31102'
104 SELECT fn31105( 9 );
105 ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc.fn31105'
106 connection default;
107 USE db_storedproc;
108
109 root@localhost db_storedproc
110 CALL sp31102();
111 f1 f2 f3 f4 f5 f6
112 a` a` 1000-01-01 -5000 a` -5000
113 SELECT fn31105( 9 );
114 fn31105( 9 )
115 81
116 GRANT EXECUTE ON db_storedproc.* TO 'user_2'@'localhost';
117 FLUSH PRIVILEGES;
118 disconnect user2_2;
119 connect user2_3, localhost, user_2, , db_storedproc;
120
121 user_2@localhost db_storedproc
122 CALL sp31102();
123 f1 f2 f3 f4 f5 f6
124 a` a` 1000-01-01 -5000 a` -5000
125 SELECT fn31105( 9 );
126 fn31105( 9 )
127 81
128 disconnect user2_3;
129 connection default;
130 USE db_storedproc;
131
132 root@localhost db_storedproc
133 REVOKE EXECUTE ON db_storedproc.* FROM 'user_2'@'localhost';
134 FLUSH PRIVILEGES;
135 CALL sp31102();
136 f1 f2 f3 f4 f5 f6
137 a` a` 1000-01-01 -5000 a` -5000
138 SELECT fn31105( 9 );
139 fn31105( 9 )
140 81
141 connect user2_4, localhost, user_2, , db_storedproc;
142
143 user_2@localhost db_storedproc
144 CALL sp31102();
145 ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc.sp31102'
146 SELECT fn31105( 9 );
147 ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc.fn31105'
148 disconnect user2_4;
149 connection default;
150 USE db_storedproc;
151
152 root@localhost db_storedproc
153 DROP PROCEDURE sp31102;
154 DROP FUNCTION fn31105;
155 DROP USER 'user_1'@'localhost';
156 DROP USER 'user_2'@'localhost';
157
158 Testcase 3.1.10.3:
159 ------------------
160
161 Ensure that a function can never be called.
162 --------------------------------------------------------------------------------
163 DROP FUNCTION IF EXISTS fn1;
164 CREATE FUNCTION fn1(a int) returns int
165 BEGIN
166 set @b = 0.9 * a;
167 return @b;
168 END//
169 CALL fn1();
170 ERROR 42000: PROCEDURE db_storedproc.fn1 does not exist
171 DROP FUNCTION fn1;
172
173 Testcase 3.1.10.6:
174 ------------------
175
176 Ensure that a procedure can never be executed.
177 --------------------------------------------------------------------------------
178 DROP PROCEDURE IF EXISTS sp1;
179 DROP FUNCTION IF EXISTS sp1;
180 CREATE PROCEDURE sp1()
181 BEGIN
182 SELECT * from t10;
183 END//
184 SELECT sp1();
185 ERROR 42000: FUNCTION db_storedproc.sp1 does not exist
186 DROP PROCEDURE sp1;
187
188 Testcase 3.1.10.7:
189 ------------------
190
191 Ensure that the ROW_COUNT() SQL function always returns the correct number of
192 rows affected by the execution of a stored procedure.
193 --------------------------------------------------------------------------------
194 DROP PROCEDURE IF EXISTS sp_ins_1;
195 DROP PROCEDURE IF EXISTS sp_ins_3;
196 DROP PROCEDURE IF EXISTS sp_upd;
197 DROP PROCEDURE IF EXISTS sp_ins_upd;
198 DROP PROCEDURE IF EXISTS sp_del;
199 DROP PROCEDURE IF EXISTS sp_with_rowcount;
200 CREATE TABLE temp(f1 CHAR(20),f2 CHAR(25),f3 DATE,f4 INT,f5 CHAR(25),f6 INT);
201 INSERT INTO temp SELECT * FROM t10;
202 CREATE PROCEDURE sp_ins_1()
203 BEGIN
204 INSERT INTO temp VALUES ('abc', 'abc', '20051003', 100, 'uvw', 1000);
205 END//
206 CREATE PROCEDURE sp_ins_3()
207 BEGIN
208 INSERT INTO temp VALUES ('abc', 'xyz', '19490523', 100, 'uvw', 1000);
209 INSERT INTO temp VALUES ('abc', 'xyz', '1989-11-09', 100, 'uvw', 1000);
210 INSERT INTO temp VALUES ('abc', 'xyz', '2005-10-24', 100, 'uvw', 1000);
211 END//
212 CREATE PROCEDURE sp_upd()
213 BEGIN
214 UPDATE temp SET temp.f1 = 'updated' WHERE temp.f1 ='abc';
215 END//
216 CREATE PROCEDURE sp_ins_upd()
217 BEGIN
218 BEGIN
219 INSERT INTO temp VALUES ('qwe', 'abc', '1989-11-09', 100, 'uvw', 1000);
220 INSERT INTO temp VALUES ('qwe', 'xyz', '1998-03-26', 100, 'uvw', 1000);
221 INSERT INTO temp VALUES ('qwe', 'abc', '2000-11-09', 100, 'uvw', 1000);
222 INSERT INTO temp VALUES ('qwe', 'abc', '2005-11-07', 100, 'uvw', 1000);
223 END;
224 SELECT COUNT( f1 ), f1 FROM temp GROUP BY f1;
225 UPDATE temp SET temp.f1 = 'updated_2' WHERE temp.f1 ='qwe' AND temp.f2 = 'abc';
226 END//
227 CREATE PROCEDURE sp_del()
228 BEGIN
229 DELETE FROM temp WHERE temp.f1 ='qwe' OR temp.f1 = 'updated_2';
230 END//
231 CREATE PROCEDURE sp_with_rowcount()
232 BEGIN
233 BEGIN
234 INSERT INTO temp VALUES ('qwe', 'abc', '1989-11-09', 100, 'uvw', 1000),
235 ('qwe', 'xyz', '1998-03-26', 100, 'uvw', 1000),
236 ('qwe', 'abc', '2000-11-09', 100, 'uvw', 1000),
237 ('qwe', 'xyz', '2005-11-07', 100, 'uvw', 1000);
238 END;
239 SELECT row_count() AS 'row_count() after insert';
240 SELECT row_count() AS 'row_count() after select row_count()';
241 SELECT f1,f2,f3 FROM temp ORDER BY f1,f2,f3;
242 UPDATE temp SET temp.f1 = 'updated_2' WHERE temp.f2 = 'abc';
243 SELECT row_count() AS 'row_count() after update';
244 SELECT f1,f2,f3 FROM temp ORDER BY f1,f2,f3;
245 DELETE FROM temp WHERE temp.f1 = 'updated_2';
246 SELECT row_count() AS 'row_count() after delete';
247 END//
248 CALL sp_ins_1();
249 SELECT row_count();
250 row_count()
251 1
252 SELECT * FROM temp;
253 f1 f2 f3 f4 f5 f6
254 a^aaaaaaaa a^aaaaaaaa 1000-01-09 -4992 a^aaaaaaaa -4992
255 a_aaaaaaaaa a_aaaaaaaaa 1000-01-10 -4991 a_aaaaaaaaa -4991
256 a` a` 1000-01-01 -5000 a` -5000
257 aaa aaa 1000-01-02 -4999 aaa -4999
258 abaa abaa 1000-01-03 -4998 abaa -4998
259 abc abc 2005-10-03 100 uvw 1000
260 acaaa acaaa 1000-01-04 -4997 acaaa -4997
261 adaaaa adaaaa 1000-01-05 -4996 adaaaa -4996
262 aeaaaaa aeaaaaa 1000-01-06 -4995 aeaaaaa -4995
263 afaaaaaa afaaaaaa 1000-01-07 -4994 afaaaaaa -4994
264 agaaaaaaa agaaaaaaa 1000-01-08 -4993 agaaaaaaa -4993
265 CALL sp_ins_3();
266 SELECT row_count();
267 row_count()
268 3
269 SELECT * FROM temp;
270 f1 f2 f3 f4 f5 f6
271 a^aaaaaaaa a^aaaaaaaa 1000-01-09 -4992 a^aaaaaaaa -4992
272 a_aaaaaaaaa a_aaaaaaaaa 1000-01-10 -4991 a_aaaaaaaaa -4991
273 a` a` 1000-01-01 -5000 a` -5000
274 aaa aaa 1000-01-02 -4999 aaa -4999
275 abaa abaa 1000-01-03 -4998 abaa -4998
276 abc abc 2005-10-03 100 uvw 1000
277 abc xyz 1949-05-23 100 uvw 1000
278 abc xyz 1989-11-09 100 uvw 1000
279 abc xyz 2005-10-24 100 uvw 1000
280 acaaa acaaa 1000-01-04 -4997 acaaa -4997
281 adaaaa adaaaa 1000-01-05 -4996 adaaaa -4996
282 aeaaaaa aeaaaaa 1000-01-06 -4995 aeaaaaa -4995
283 afaaaaaa afaaaaaa 1000-01-07 -4994 afaaaaaa -4994
284 agaaaaaaa agaaaaaaa 1000-01-08 -4993 agaaaaaaa -4993
285 CALL sp_upd();
286 SELECT row_count();
287 row_count()
288 4
289 SELECT * FROM temp;
290 f1 f2 f3 f4 f5 f6
291 a^aaaaaaaa a^aaaaaaaa 1000-01-09 -4992 a^aaaaaaaa -4992
292 a_aaaaaaaaa a_aaaaaaaaa 1000-01-10 -4991 a_aaaaaaaaa -4991
293 a` a` 1000-01-01 -5000 a` -5000
294 aaa aaa 1000-01-02 -4999 aaa -4999
295 abaa abaa 1000-01-03 -4998 abaa -4998
296 acaaa acaaa 1000-01-04 -4997 acaaa -4997
297 adaaaa adaaaa 1000-01-05 -4996 adaaaa -4996
298 aeaaaaa aeaaaaa 1000-01-06 -4995 aeaaaaa -4995
299 afaaaaaa afaaaaaa 1000-01-07 -4994 afaaaaaa -4994
300 agaaaaaaa agaaaaaaa 1000-01-08 -4993 agaaaaaaa -4993
301 updated abc 2005-10-03 100 uvw 1000
302 updated xyz 1949-05-23 100 uvw 1000
303 updated xyz 1989-11-09 100 uvw 1000
304 updated xyz 2005-10-24 100 uvw 1000
305 CALL sp_ins_upd();
306 COUNT( f1 ) f1
307 1 a_aaaaaaaaa
308 1 a`
309 1 a^aaaaaaaa
310 1 aaa
311 1 abaa
312 1 acaaa
313 1 adaaaa
314 1 aeaaaaa
315 1 afaaaaaa
316 1 agaaaaaaa
317 4 qwe
318 4 updated
319 SELECT row_count();
320 row_count()
321 7
322 SELECT * FROM temp;
323 f1 f2 f3 f4 f5 f6
324 a^aaaaaaaa a^aaaaaaaa 1000-01-09 -4992 a^aaaaaaaa -4992
325 a_aaaaaaaaa a_aaaaaaaaa 1000-01-10 -4991 a_aaaaaaaaa -4991
326 a` a` 1000-01-01 -5000 a` -5000
327 aaa aaa 1000-01-02 -4999 aaa -4999
328 abaa abaa 1000-01-03 -4998 abaa -4998
329 acaaa acaaa 1000-01-04 -4997 acaaa -4997
330 adaaaa adaaaa 1000-01-05 -4996 adaaaa -4996
331 aeaaaaa aeaaaaa 1000-01-06 -4995 aeaaaaa -4995
332 afaaaaaa afaaaaaa 1000-01-07 -4994 afaaaaaa -4994
333 agaaaaaaa agaaaaaaa 1000-01-08 -4993 agaaaaaaa -4993
334 qwe xyz 1998-03-26 100 uvw 1000
335 updated abc 2005-10-03 100 uvw 1000
336 updated xyz 1949-05-23 100 uvw 1000
337 updated xyz 1989-11-09 100 uvw 1000
338 updated xyz 2005-10-24 100 uvw 1000
339 updated_2 abc 1989-11-09 100 uvw 1000
340 updated_2 abc 2000-11-09 100 uvw 1000
341 updated_2 abc 2005-11-07 100 uvw 1000
342 CALL sp_del();
343 SELECT row_count();
344 row_count()
345 4
346 SELECT * FROM temp;
347 f1 f2 f3 f4 f5 f6
348 a^aaaaaaaa a^aaaaaaaa 1000-01-09 -4992 a^aaaaaaaa -4992
349 a_aaaaaaaaa a_aaaaaaaaa 1000-01-10 -4991 a_aaaaaaaaa -4991
350 a` a` 1000-01-01 -5000 a` -5000
351 aaa aaa 1000-01-02 -4999 aaa -4999
352 abaa abaa 1000-01-03 -4998 abaa -4998
353 acaaa acaaa 1000-01-04 -4997 acaaa -4997
354 adaaaa adaaaa 1000-01-05 -4996 adaaaa -4996
355 aeaaaaa aeaaaaa 1000-01-06 -4995 aeaaaaa -4995
356 afaaaaaa afaaaaaa 1000-01-07 -4994 afaaaaaa -4994
357 agaaaaaaa agaaaaaaa 1000-01-08 -4993 agaaaaaaa -4993
358 updated abc 2005-10-03 100 uvw 1000
359 updated xyz 1949-05-23 100 uvw 1000
360 updated xyz 1989-11-09 100 uvw 1000
361 updated xyz 2005-10-24 100 uvw 1000
362 DELETE FROM temp;
363 CALL sp_with_rowcount();
364 row_count() after insert
365 4
366 row_count() after select row_count()
367 -1
368 f1 f2 f3
369 qwe abc 1989-11-09
370 qwe abc 2000-11-09
371 qwe xyz 1998-03-26
372 qwe xyz 2005-11-07
373 row_count() after update
374 2
375 f1 f2 f3
376 qwe xyz 1998-03-26
377 qwe xyz 2005-11-07
378 updated_2 abc 1989-11-09
379 updated_2 abc 2000-11-09
380 row_count() after delete
381 2
382 SELECT row_count();
383 row_count()
384 8
385 SELECT * FROM temp;
386 f1 f2 f3 f4 f5 f6
387 qwe xyz 1998-03-26 100 uvw 1000
388 qwe xyz 2005-11-07 100 uvw 1000
389 DROP PROCEDURE sp_ins_1;
390 DROP PROCEDURE sp_ins_3;
391 DROP PROCEDURE sp_upd;
392 DROP PROCEDURE sp_ins_upd;
393 DROP PROCEDURE sp_del;
394 DROP PROCEDURE sp_with_rowcount;
395 DROP TABLE temp;
396
397 Testcase 3.1.10.8:
398 ------------------
399
400 Ensure that the mysql_affected_rows() C API function always returns the correct
401 number of rows affected by the execution of a stored procedure.
402 --------------------------------------------------------------------------------
403
404 --source suite/funcs_1/storedproc/cleanup_sp_tb.inc
405 --------------------------------------------------------------------------------
406 DROP DATABASE IF EXISTS db_storedproc;
407 DROP DATABASE IF EXISTS db_storedproc_1;
408
409 . +++ END OF SCRIPT +++
410 --------------------------------------------------------------------------------