Debian Codegraph @

mysql-test/suite/perfschema/t/selects.test

raw
1 --source include/not_embedded.inc
2 --source include/have_perfschema.inc
3
4 #
5 # WL#4814, 4.1.2 STORAGE ENGINE, FSE8: Selects
6 #
7
8 # Make some data that we can work on:
9
10 UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES';
11
12 # Disable ALL table IO, to avoid generating events from the selects
13 # To be revised after WL#5342 PERFORMANCE SCHEMA SETUP OBJECTS
14
15 UPDATE performance_schema.setup_instruments SET enabled = 'NO', timed = 'NO'
16 where NAME='wait/io/table/sql/handler';
17
18 --disable_warnings
19 DROP TABLE IF EXISTS t1;
20 --enable_warnings
21 CREATE TABLE t1 (id INT PRIMARY KEY, b CHAR(100) DEFAULT 'initial value')
22 ENGINE=MyISAM;
23 INSERT INTO t1 (id) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
24
25 # ORDER BY, GROUP BY and HAVING
26
27 --replace_column 2 [NUM_BYTES]
28 SELECT OPERATION, SUM(NUMBER_OF_BYTES) AS TOTAL
29 FROM performance_schema.events_waits_history_long
30 GROUP BY OPERATION
31 HAVING TOTAL IS NOT NULL
32 ORDER BY OPERATION
33 LIMIT 1;
34
35 # Sub SELECT
36 --replace_column 1 [EVENT_ID]
37 SELECT EVENT_ID FROM performance_schema.events_waits_current
38 WHERE THREAD_ID IN
39 (SELECT THREAD_ID FROM performance_schema.threads)
40 AND EVENT_NAME IN
41 (SELECT NAME FROM performance_schema.setup_instruments
42 WHERE NAME LIKE "wait/synch/%")
43 LIMIT 1;
44
45 # JOIN
46
47 --replace_column 1 [EVENT_ID]
48 SELECT DISTINCT EVENT_ID
49 FROM performance_schema.events_waits_current
50 JOIN performance_schema.events_waits_history USING (EVENT_ID)
51 JOIN performance_schema.events_waits_history_long USING (EVENT_ID)
52 ORDER BY EVENT_ID
53 LIMIT 1;
54
55 # Self JOIN
56
57 --replace_column 1 [THREAD_ID] 2 [EVENT_ID] 3 [EVENT_NAME] 4 [TIMER_WAIT]
58 SELECT t1.THREAD_ID, t2.EVENT_ID, t3.EVENT_NAME, t4.TIMER_WAIT
59 FROM performance_schema.events_waits_history t1
60 JOIN performance_schema.events_waits_history t2 USING (EVENT_ID)
61 JOIN performance_schema.events_waits_history t3 ON (t2.THREAD_ID = t3.THREAD_ID)
62 JOIN performance_schema.events_waits_history t4 ON (t3.EVENT_NAME = t4.EVENT_NAME)
63 ORDER BY t1.EVENT_ID, t2.EVENT_ID
64 LIMIT 5;
65
66 # UNION
67 --replace_column 1 [THREAD_ID] 2 [EVENT_ID]
68 SELECT THREAD_ID, EVENT_ID FROM (
69 SELECT THREAD_ID, EVENT_ID FROM performance_schema.events_waits_current
70 UNION
71 SELECT THREAD_ID, EVENT_ID FROM performance_schema.events_waits_history
72 UNION
73 SELECT THREAD_ID, EVENT_ID FROM performance_schema.events_waits_history_long
74 ) t1 ORDER BY THREAD_ID, EVENT_ID
75 LIMIT 5;
76
77 # EVENT
78
79 # Check that the event_scheduler is really running
80 --source include/running_event_scheduler.inc
81
82 --disable_warnings
83 DROP TABLE IF EXISTS t_event;
84 DROP EVENT IF EXISTS t_ps_event;
85 --enable_warnings
86 CREATE TABLE t_event AS
87 SELECT EVENT_ID FROM performance_schema.events_waits_current
88 WHERE 1 = 2;
89 CREATE EVENT t_ps_event
90 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND
91 ON COMPLETION PRESERVE
92 DO INSERT INTO t_event
93 SELECT DISTINCT EVENT_ID
94 FROM performance_schema.events_waits_current
95 JOIN performance_schema.events_waits_history USING (EVENT_ID)
96 ORDER BY EVENT_ID
97 LIMIT 1;
98
99 # TRIGGER
100
101 ALTER TABLE t1 ADD COLUMN c INT;
102
103 --disable_warnings
104 DROP TRIGGER IF EXISTS t_ps_trigger;
105 --enable_warnings
106 delimiter |;
107
108 CREATE TRIGGER t_ps_trigger BEFORE INSERT ON t1
109 FOR EACH ROW BEGIN
110 SET NEW.c = (SELECT MAX(EVENT_ID)
111 FROM performance_schema.events_waits_current);
112 END;
113 |
114
115 delimiter ;|
116
117 INSERT INTO t1 (id) VALUES (11), (12), (13);
118
119 --replace_column 2 [EVENT_ID]
120 SELECT id, c FROM t1 WHERE id > 10 ORDER BY c;
121
122 DROP TRIGGER t_ps_trigger;
123
124 # PROCEDURE
125
126 --disable_warnings
127 DROP PROCEDURE IF EXISTS t_ps_proc;
128 --enable_warnings
129 --enable_prepare_warnings
130 delimiter |;
131
132 CREATE PROCEDURE t_ps_proc(IN conid INT, OUT pid INT)
133 BEGIN
134 SELECT thread_id FROM performance_schema.threads
135 WHERE PROCESSLIST_ID = conid INTO pid;
136 END;
137
138 |
139
140 delimiter ;|
141
142 CALL t_ps_proc(connection_id(), @p_id);
143
144 --disable_prepare_warnings
145 # FUNCTION
146
147 --disable_warnings
148 DROP FUNCTION IF EXISTS t_ps_proc;
149 --enable_warnings
150 delimiter |;
151
152 CREATE FUNCTION t_ps_func(conid INT) RETURNS int
153 BEGIN
154 return (SELECT thread_id FROM performance_schema.threads
155 WHERE PROCESSLIST_ID = conid);
156 END;
157
158 |
159
160 delimiter ;|
161
162 SELECT t_ps_func(connection_id()) = @p_id;
163
164 # We might reach this point too early which means the event scheduler has not
165 # executed our "t_ps_event". Therefore we poll till the record was inserted
166 # and run our test statement afterwards.
167 let $wait_timeout= 20;
168 let $wait_condition= SELECT COUNT(*) = 1 FROM t_event;
169 --source include/wait_condition.inc
170 --replace_column 1 [EVENT_ID]
171 SELECT * FROM t_event;
172
173 # Clean up
174 DROP PROCEDURE t_ps_proc;
175 DROP FUNCTION t_ps_func;
176 DROP EVENT t_ps_event;
177 DROP TABLE t1;
178 DROP TABLE t_event;
179
180 UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES';
181