Debian Codegraph @

mysql-test/suite/perfschema/r/statement_program_non_nested.result

raw
1 TRUNCATE TABLE performance_schema.events_statements_summary_by_program;
2 TRUNCATE TABLE performance_schema.events_statements_history_long;
3 ################################################
4 # Quering PS statement summary and history_long#
5 ################################################
6 # SET-UP
7 CREATE DATABASE stored_programs;
8 USE stored_programs;
9 CREATE TABLE t1(
10 i INT NOT NULL,
11 j INT
12 );
13 CREATE TABLE t2(
14 name CHAR(16) NOT NULL DEFAULT '',
15 id INT NOT NULL
16 );
17 CREATE TABLE t3(
18 d DATE,
19 n INT,
20 f DOUBLE,
21 s VARCHAR(32)
22 );
23 CREATE TABLE t4(
24 `k` int(10) unsigned NOT NULL AUTO_INCREMENT,
25 `word` varchar(100) NOT NULL,
26 `mean` varchar(300) NOT NULL,
27 PRIMARY KEY (`k`)
28 );
29 Warnings:
30 Warning 1681 Integer display width is deprecated and will be removed in a future release.
31 ############################
32 # Creating Stored Programs #
33 ############################
34 # Stored Routine ( Procedure & Function )
35 CREATE PROCEDURE SampleProc1(x1 INT, x2 INT, y INT)
36 BEGIN
37 INSERT INTO t1 VALUES (x1, y);
38 INSERT INTO t1 VALUES (x2, y);
39 END|
40 CREATE PROCEDURE SampleProc2(x CHAR(16), y INT)
41 BEGIN
42 DECLARE z1, z2 INT;
43 SET z1 = y;
44 SET z2 = z1+2;
45 INSERT INTO t2 VALUES (x, z2);
46 END|
47 CREATE PROCEDURE SampleProc3()
48 BEGIN
49 DECLARE ld DATE;
50 DECLARE li INT;
51 DECLARE lf DOUBLE;
52 DECLARE ls VARCHAR(32);
53 SET ld = NULL, li = NULL, lf = NULL, ls = NULL;
54 INSERT INTO t3 VALUES (ld, li, lf, ls);
55 INSERT INTO t3 (n, f, s) VALUES ((ld IS NULL), 1, "ld is null"),
56 ((li IS NULL), 1, "li is null"),
57 ((li = 0), NULL, "li = 0"),
58 ((lf IS NULL), 1, "lf is null"),
59 ((lf = 0), NULL, "lf = 0"),
60 ((ls IS NULL), 1, "ls is null");
61 END|
62 CREATE PROCEDURE SampleProc4()
63 BEGIN
64 DECLARE x INT;
65 SET x = 1;
66 WHILE x <= 2 DO
67 INSERT INTO t4(word, mean) VALUES('a','a mean');
68 SET x = x + 1;
69 END WHILE;
70 END|
71 CREATE FUNCTION append(s1 CHAR(8), s2 CHAR(8)) RETURNS CHAR(16)
72 RETURN concat(s1, s2)|
73 CREATE FUNCTION wt_avg(n1 INT, n2 INT, n3 INT, n4 INT)
74 RETURNS INT
75 DETERMINISTIC
76 BEGIN
77 DECLARE avg INT;
78 SET avg = (n1+n2+n3*2+n4*4)/8;
79 RETURN avg;
80 END|
81 CREATE FUNCTION fac(n INT UNSIGNED) RETURNS BIGINT UNSIGNED
82 BEGIN
83 DECLARE f BIGINT UNSIGNED DEFAULT 1;
84 WHILE n > 1 DO
85 SET f = f * n;
86 SET n = n - 1;
87 END WHILE;
88 RETURN f;
89 END|
90 # Triggers
91 CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW
92 BEGIN
93 if isnull(new.j) then
94 SET new.j:= new.i * 10;
95 END if;
96 END|
97 CREATE TRIGGER trg2 AFTER INSERT ON t2 FOR EACH ROW
98 BEGIN
99 UPDATE t1 SET i=new.id+i ;
100 END|
101 CREATE TRIGGER trg3 AFTER UPDATE ON t2 FOR EACH ROW
102 SET @change:= @change + new.id - old.id|
103 CREATE TRIGGER trg4 BEFORE DELETE ON t1 FOR EACH ROW
104 SET @del:= @del + 1|
105 CREATE TRIGGER trg5 AFTER DELETE ON t1 FOR EACH ROW
106 SET @del:= @del + 8 + old.j|
107 #####################
108 # Executing queries #
109 #####################
110 INSERT INTO t1 VALUES (10,20);
111 CALL SampleProc1(30,40,50);
112 SET @a=1;
113 SELECT @a;
114 @a
115 1
116 CALL SampleProc2("Jwalamukhi",34);
117 SELECT @a;
118 @a
119 1
120 CALL SampleProc3();
121 CALL SampleProc4();
122 SET @change=1;
123 SELECT @change;
124 @change
125 1
126 UPDATE t2 SET id=22 WHERE name="Jwalamukhi";
127 SELECT @change;
128 @change
129 -13
130 SET @del=1;
131 SELECT @del;
132 @del
133 1
134 DELETE FROM t1 WHERE i=76;
135 SELECT @del;
136 @del
137 60
138 SELECT wt_avg(1, 12, 1990, 1121990);
139 wt_avg(1, 12, 1990, 1121990)
140 561494
141 SELECT fac(5);
142 fac(5)
143 120
144 SELECT append("Bolly", "wood");
145 append("Bolly", "wood")
146 Bollywood
147 TRUNCATE TABLE performance_schema.events_statements_history_long;
148 # Event
149 SELECT COUNT(*) = 1 FROM information_schema.processlist
150 WHERE user = 'event_scheduler' AND command = 'Daemon';
151 COUNT(*) = 1
152 1
153 Warnings:
154 Warning 1287 'INFORMATION_SCHEMA.PROCESSLIST' is deprecated and will be removed in a future release. Please use performance_schema.processlist instead
155 CREATE TABLE table_t(a INT);
156 CREATE EVENT e1 ON SCHEDULE EVERY 2 SECOND DO
157 BEGIN
158 INSERT INTO table_t VALUES(1);
159 END|
160 SELECT * FROM table_t;
161 a
162 1
163 SET GLOBAL event_scheduler=OFF;
164 SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_STATEMENTS
165 FROM performance_schema.events_statements_summary_by_program
166 WHERE OBJECT_SCHEMA='stored_programs' ORDER BY OBJECT_NAME;
167 OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME COUNT_STAR COUNT_STATEMENTS
168 FUNCTION stored_programs append 1 1
169 EVENT stored_programs e1 1 1
170 FUNCTION stored_programs fac 1 19
171 PROCEDURE stored_programs SampleProc1 1 2
172 PROCEDURE stored_programs SampleProc2 1 5
173 PROCEDURE stored_programs SampleProc3 1 10
174 PROCEDURE stored_programs SampleProc4 1 11
175 TRIGGER stored_programs trg1 3 3
176 TRIGGER stored_programs trg2 1 1
177 TRIGGER stored_programs trg3 1 1
178 TRIGGER stored_programs trg4 1 1
179 TRIGGER stored_programs trg5 1 1
180 FUNCTION stored_programs wt_avg 1 3
181 SELECT EVENT_NAME, SQL_TEXT, CURRENT_SCHEMA, OBJECT_TYPE, OBJECT_SCHEMA,
182 OBJECT_NAME, NESTING_EVENT_TYPE, NESTING_EVENT_LEVEL FROM
183 performance_schema.events_statements_history_long WHERE
184 CURRENT_SCHEMA='stored_programs' AND
185 (SQL_TEXT not like '%count(*) = 1%' OR SQL_TEXT IS NULL)
186 ORDER BY OBJECT_NAME, NESTING_EVENT_LEVEL, SQL_TEXT, EVENT_NAME;
187 EVENT_NAME SQL_TEXT CURRENT_SCHEMA OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME NESTING_EVENT_TYPE NESTING_EVENT_LEVEL
188 statement/scheduler/event NULL stored_programs NULL NULL NULL NULL 0
189 statement/sql/create_event CREATE EVENT e1 ON SCHEDULE EVERY 2 SECOND DO
190 BEGIN
191 INSERT INTO table_t VALUES(1);
192 END stored_programs NULL NULL NULL NULL 0
193 statement/sql/create_table CREATE TABLE table_t(a INT) stored_programs NULL NULL NULL NULL 0
194 statement/sql/select SELECT * FROM table_t stored_programs NULL NULL NULL NULL 0
195 statement/sql/select SELECT 5 * 10 stored_programs NULL NULL NULL NULL 0
196 statement/sql/select SELECT COUNT(*) = 0 FROM information_schema.processlist
197 WHERE user = 'event_scheduler' stored_programs NULL NULL NULL NULL 0
198 statement/sql/select SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_STATEMENTS
199 FROM performance_schema.events_statements_summary_by_program
200 WHERE OBJECT_SCHEMA='stored_programs' ORDER BY OBJECT_NAME stored_programs NULL NULL NULL NULL 0
201 statement/sql/set_option SET GLOBAL event_scheduler=OFF stored_programs NULL NULL NULL NULL 0
202 statement/sql/show_warnings SHOW WARNINGS stored_programs NULL NULL NULL NULL 0
203 statement/sql/truncate TRUNCATE TABLE performance_schema.events_statements_history_long stored_programs NULL NULL NULL NULL 0
204 statement/sp/stmt INSERT INTO table_t VALUES(1) stored_programs EVENT stored_programs e1 STATEMENT 1
205 # clean -up
206 TRUNCATE TABLE performance_schema.events_statements_summary_by_program;
207 TRUNCATE TABLE performance_schema.events_statements_history_long;
208 SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_STATEMENTS
209 FROM performance_schema.events_statements_summary_by_program
210 WHERE OBJECT_SCHEMA='stored_programs' ORDER BY OBJECT_NAME;
211 OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME COUNT_STAR COUNT_STATEMENTS
212 FUNCTION stored_programs append 0 0
213 EVENT stored_programs e1 0 0
214 FUNCTION stored_programs fac 0 0
215 PROCEDURE stored_programs SampleProc1 0 0
216 PROCEDURE stored_programs SampleProc2 0 0
217 PROCEDURE stored_programs SampleProc3 0 0
218 PROCEDURE stored_programs SampleProc4 0 0
219 TRIGGER stored_programs trg1 0 0
220 TRIGGER stored_programs trg2 0 0
221 TRIGGER stored_programs trg3 0 0
222 TRIGGER stored_programs trg4 0 0
223 TRIGGER stored_programs trg5 0 0
224 FUNCTION stored_programs wt_avg 0 0
225 DROP PROCEDURE SampleProc1;
226 DROP PROCEDURE SampleProc2;
227 DROP PROCEDURE SampleProc3;
228 DROP PROCEDURE SampleProc4;
229 DROP FUNCTION wt_avg;
230 DROP FUNCTION fac;
231 DROP FUNCTION append;
232 DROP TRIGGER trg1;
233 DROP TRIGGER trg2;
234 DROP TRIGGER trg3;
235 DROP TRIGGER trg4;
236 DROP TRIGGER trg5;
237 DROP EVENT IF EXISTS e1;
238 DROP TABLE t1;
239 DROP TABLE t2;
240 DROP TABLE t3;
241 DROP TABLE t4;
242 DROP TABLE table_t;
243 DROP DATABASE stored_programs;
244 SET GLOBAL event_scheduler=ON;
245 SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_STATEMENTS
246 FROM performance_schema.events_statements_summary_by_program
247 WHERE OBJECT_SCHEMA='stored_programs' ORDER BY OBJECT_NAME;
248 OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME COUNT_STAR COUNT_STATEMENTS
249 # SET-UP
250 CREATE DATABASE stored_programs;
251 USE stored_programs;
252 CREATE TABLE t1(
253 i INT NOT NULL,
254 j INT
255 );
256 CREATE TABLE t2(
257 name CHAR(16) NOT NULL DEFAULT '',
258 id INT NOT NULL
259 );
260 CREATE TABLE t3(
261 d DATE,
262 n INT,
263 f DOUBLE,
264 s VARCHAR(32)
265 );
266 CREATE TABLE t4(
267 `k` int(10) unsigned NOT NULL AUTO_INCREMENT,
268 `word` varchar(100) NOT NULL,
269 `mean` varchar(300) NOT NULL,
270 PRIMARY KEY (`k`)
271 );
272 Warnings:
273 Warning 1681 Integer display width is deprecated and will be removed in a future release.
274 ############################
275 # Creating Stored Programs #
276 ############################
277 # Stored Routine ( Procedure & Function )
278 CREATE PROCEDURE SampleProc1(x1 INT, x2 INT, y INT)
279 BEGIN
280 INSERT INTO t1 VALUES (x1, y);
281 INSERT INTO t1 VALUES (x2, y);
282 END|
283 CREATE PROCEDURE SampleProc2(x CHAR(16), y INT)
284 BEGIN
285 DECLARE z1, z2 INT;
286 SET z1 = y;
287 SET z2 = z1+2;
288 INSERT INTO t2 VALUES (x, z2);
289 END|
290 CREATE PROCEDURE SampleProc3()
291 BEGIN
292 DECLARE ld DATE;
293 DECLARE li INT;
294 DECLARE lf DOUBLE;
295 DECLARE ls VARCHAR(32);
296 SET ld = NULL, li = NULL, lf = NULL, ls = NULL;
297 INSERT INTO t3 VALUES (ld, li, lf, ls);
298 INSERT INTO t3 (n, f, s) VALUES ((ld IS NULL), 1, "ld is null"),
299 ((li IS NULL), 1, "li is null"),
300 ((li = 0), NULL, "li = 0"),
301 ((lf IS NULL), 1, "lf is null"),
302 ((lf = 0), NULL, "lf = 0"),
303 ((ls IS NULL), 1, "ls is null");
304 END|
305 CREATE PROCEDURE SampleProc4()
306 BEGIN
307 DECLARE x INT;
308 SET x = 1;
309 WHILE x <= 2 DO
310 INSERT INTO t4(word, mean) VALUES('a','a mean');
311 SET x = x + 1;
312 END WHILE;
313 END|
314 CREATE FUNCTION append(s1 CHAR(8), s2 CHAR(8)) RETURNS CHAR(16)
315 RETURN concat(s1, s2)|
316 CREATE FUNCTION wt_avg(n1 INT, n2 INT, n3 INT, n4 INT)
317 RETURNS INT
318 DETERMINISTIC
319 BEGIN
320 DECLARE avg INT;
321 SET avg = (n1+n2+n3*2+n4*4)/8;
322 RETURN avg;
323 END|
324 CREATE FUNCTION fac(n INT UNSIGNED) RETURNS BIGINT UNSIGNED
325 BEGIN
326 DECLARE f BIGINT UNSIGNED DEFAULT 1;
327 WHILE n > 1 DO
328 SET f = f * n;
329 SET n = n - 1;
330 END WHILE;
331 RETURN f;
332 END|
333 # Triggers
334 CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW
335 BEGIN
336 if isnull(new.j) then
337 SET new.j:= new.i * 10;
338 END if;
339 END|
340 CREATE TRIGGER trg2 AFTER INSERT ON t2 FOR EACH ROW
341 BEGIN
342 UPDATE t1 SET i=new.id+i ;
343 END|
344 CREATE TRIGGER trg3 AFTER UPDATE ON t2 FOR EACH ROW
345 SET @change:= @change + new.id - old.id|
346 CREATE TRIGGER trg4 BEFORE DELETE ON t1 FOR EACH ROW
347 SET @del:= @del + 1|
348 CREATE TRIGGER trg5 AFTER DELETE ON t1 FOR EACH ROW
349 SET @del:= @del + 8 + old.j|
350 update performance_schema.setup_instruments set enabled='YES', timed='NO'
351 where name like "statement/sp/%" order by name;
352 TRUNCATE TABLE performance_schema.events_statements_summary_by_program;
353 TRUNCATE TABLE performance_schema.events_statements_history_long;
354 #####################
355 # Executing queries #
356 #####################
357 INSERT INTO t1 VALUES (10,20);
358 CALL SampleProc1(30,40,50);
359 SET @a=1;
360 SELECT @a;
361 @a
362 1
363 CALL SampleProc2("Jwalamukhi",34);
364 SELECT @a;
365 @a
366 1
367 CALL SampleProc3();
368 CALL SampleProc4();
369 SET @change=1;
370 SELECT @change;
371 @change
372 1
373 UPDATE t2 SET id=22 WHERE name="Jwalamukhi";
374 SELECT @change;
375 @change
376 -13
377 SET @del=1;
378 SELECT @del;
379 @del
380 1
381 DELETE FROM t1 WHERE i=76;
382 SELECT @del;
383 @del
384 60
385 SELECT wt_avg(1, 12, 1990, 1121990);
386 wt_avg(1, 12, 1990, 1121990)
387 561494
388 SELECT fac(5);
389 fac(5)
390 120
391 SELECT append("Bolly", "wood");
392 append("Bolly", "wood")
393 Bollywood
394 TRUNCATE TABLE performance_schema.events_statements_history_long;
395 # Event
396 SELECT COUNT(*) = 1 FROM information_schema.processlist
397 WHERE user = 'event_scheduler' AND command = 'Daemon';
398 COUNT(*) = 1
399 1
400 Warnings:
401 Warning 1287 'INFORMATION_SCHEMA.PROCESSLIST' is deprecated and will be removed in a future release. Please use performance_schema.processlist instead
402 CREATE TABLE table_t(a INT);
403 CREATE EVENT e1 ON SCHEDULE EVERY 2 SECOND DO
404 BEGIN
405 INSERT INTO table_t VALUES(1);
406 END|
407 SELECT * FROM table_t;
408 a
409 1
410 SET GLOBAL event_scheduler=OFF;
411 SELECT EVENT_NAME, TIMER_START, TIMER_END, TIMER_WAIT FROM
412 performance_schema.events_statements_history_long WHERE
413 CURRENT_SCHEMA='stored_programs' AND EVENT_NAME like "statement/sp/%";
414 EVENT_NAME TIMER_START TIMER_END TIMER_WAIT
415 statement/sp/stmt NULL NULL NULL
416 update performance_schema.setup_instruments set enabled='YES', timed='YES'
417 where name like "statement/sp/%" order by name;
418 TRUNCATE TABLE performance_schema.events_statements_summary_by_program;
419 TRUNCATE TABLE performance_schema.events_statements_history_long;
420 DROP PROCEDURE SampleProc1;
421 DROP PROCEDURE SampleProc2;
422 DROP PROCEDURE SampleProc3;
423 DROP PROCEDURE SampleProc4;
424 DROP FUNCTION wt_avg;
425 DROP FUNCTION fac;
426 DROP FUNCTION append;
427 DROP TRIGGER trg1;
428 DROP TRIGGER trg2;
429 DROP TRIGGER trg3;
430 DROP TRIGGER trg4;
431 DROP TRIGGER trg5;
432 DROP EVENT IF EXISTS e1;
433 DROP TABLE t1;
434 DROP TABLE t2;
435 DROP TABLE t3;
436 DROP TABLE t4;
437 DROP TABLE table_t;
438 DROP DATABASE stored_programs;
439 SET GLOBAL event_scheduler=ON;
440 # set-up
441 CREATE DATABASE sp;
442 USE sp;
443 CREATE TABLE t1(
444 a INT,
445 b INT
446 );
447 # let the creation of the following stored programs fail
448 CREATE PROCEDURE fail1(IN a INT OUT x CHAR(16))
449 SET a=1;
450 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 'OUT x CHAR(16))
451 SET a=1' at line 1
452 CREATE FUNCTION fail2(a INT , b INT) RETURNS INT
453 x=SELECT COUNT(*) FROM t;
454 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(*) FROM t' at line 2
455 CREATE EVENT fail3 SCHEDULE EVERY MICROSECOND DO
456 DROP TABLE t;
457 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 'SCHEDULE EVERY MICROSECOND DO
458 DROP TABLE t' at line 1
459 # the below query on PS table doesn't show any rows
460 # as the creation of stored programs failed
461 SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_STATEMENTS
462 FROM performance_schema.events_statements_summary_by_program
463 WHERE OBJECT_SCHEMA='sp';
464 OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME COUNT_STAR COUNT_STATEMENTS
465 # create few stored programs
466 CREATE PROCEDURE p(x1 INT, x2 INT)
467 BEGIN
468 INSERT INTO t1 VALUES (x1, x2);
469 END|
470 CREATE FUNCTION f(y1 INT, y2 INT) RETURNS INT
471 RETURN y1+y2;
472 CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW
473 SET @neg=-1;
474 # execute the created stored programs such that they fail.
475 CALL p(7);
476 ERROR 42000: Incorrect number of arguments for PROCEDURE sp.p; expected 2, got 1
477 SELECT f("add",1,3);
478 ERROR 42000: Incorrect number of arguments for FUNCTION sp.f; expected 2, got 3
479 INSERT INTO t1;
480 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 '' at line 1
481 # the below query on PS table doesn't expose any statistics as
482 # execution of the created stored porgrams failed.
483 SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_STATEMENTS
484 FROM performance_schema.events_statements_summary_by_program
485 WHERE OBJECT_SCHEMA='sp';
486 OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME COUNT_STAR COUNT_STATEMENTS
487 PROCEDURE sp p 0 0
488 FUNCTION sp f 0 0
489 #clean-up
490 DROP PROCEDURE p;
491 DROP FUNCTION f;
492 DROP TRIGGER trg;
493 DROP TABLE t1;
494 DROP DATABASE sp;