Debian Codegraph @

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

raw
1 # ----------------------------------------------------
2 # Tests for the performance schema statement Digests.
3 # ----------------------------------------------------
4
5 # Test case to show behavior of statements digest when
6 # using a view
7
8 # Test requires: sp-protocol/ps-protocol/view-protocol/cursor-protocol disabled
9 --source include/no_protocol.inc
10 --source include/not_embedded.inc
11 --source include/have_perfschema.inc
12 --source include/check_digest.inc
13
14 CREATE TABLE test.v1 (a int, b int);
15 INSERT INTO test.v1 VALUES (1, 100), (2, 200), (3, 300);
16
17 CREATE TABLE test.t1 (a int, b int);
18 INSERT INTO test.t1 VALUES (1, 100), (2, 200), (3, 300);
19
20
21 TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;
22
23 #
24 # test.v1 is a table.
25 # Every query here is different, and should have a different digest.
26 #
27
28 EXPLAIN EXTENDED SELECT * from test.v1;
29 EXPLAIN EXTENDED SELECT * from test.v1 where a = 1;
30 EXPLAIN EXTENDED SELECT * from test.v1 where b > 100;
31 EXPLAIN EXTENDED SELECT a, b from test.v1;
32 EXPLAIN EXTENDED SELECT b, a from test.v1;
33
34 SELECT * from test.v1;
35 SELECT * from test.v1 where a = 1;
36 SELECT * from test.v1 where b > 100;
37 SELECT a, b from test.v1;
38 SELECT b, a from test.v1;
39
40 --echo #
41 --echo # DIGESTS SEEN ON TABLE
42 --echo #
43
44 SELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR
45 FROM performance_schema.events_statements_summary_by_digest
46 ORDER BY DIGEST_TEXT;
47
48 DROP TABLE test.v1;
49 CREATE VIEW test.v1 AS SELECT * FROM test.t1;
50
51 #
52 # test.v1 is now a view.
53 # the query digests should be unchanged.
54 #
55
56 EXPLAIN EXTENDED SELECT * from test.v1;
57 EXPLAIN EXTENDED SELECT * from test.v1 where a = 1;
58 EXPLAIN EXTENDED SELECT * from test.v1 where b > 100;
59 EXPLAIN EXTENDED SELECT a, b from test.v1;
60 EXPLAIN EXTENDED SELECT b, a from test.v1;
61
62 SELECT * from test.v1;
63 SELECT * from test.v1 where a = 1;
64 SELECT * from test.v1 where b > 100;
65 SELECT a, b from test.v1;
66 SELECT b, a from test.v1;
67
68 --echo #
69 --echo # DIGESTS SEEN ON VIEW
70 --echo #
71
72 SELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR
73 FROM performance_schema.events_statements_summary_by_digest
74 ORDER BY DIGEST_TEXT;
75
76 DROP VIEW test.v1;
77 DROP TABLE test.t1;
78
79 # ----------------------------------------------------
80 # Tests for the performance schema statement Digests.
81 # ----------------------------------------------------
82
83 # Test case to show behavior of statements digest when
84 # using a view
85
86 --source include/not_embedded.inc
87 --source include/have_perfschema.inc
88 --source include/no_protocol.inc
89
90 CREATE TABLE test.v1 (a int, b int);
91 INSERT INTO test.v1 VALUES (1, 100), (2, 200), (3, 300);
92
93 CREATE TABLE test.t1 (a int, b int);
94 INSERT INTO test.t1 VALUES (1, 100), (2, 200), (3, 300);
95
96
97 TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;
98
99 #
100 # test.v1 is a table.
101 # Every query here is different, and should have a different digest.
102 #
103
104 EXPLAIN SELECT * from test.v1;
105 EXPLAIN SELECT * from test.v1 where a = 1;
106 EXPLAIN SELECT * from test.v1 where b > 100;
107 EXPLAIN SELECT a, b from test.v1;
108 EXPLAIN SELECT b, a from test.v1;
109
110 SELECT * from test.v1;
111 SELECT * from test.v1 where a = 1;
112 SELECT * from test.v1 where b > 100;
113 SELECT a, b from test.v1;
114 SELECT b, a from test.v1;
115
116 --echo #
117 --echo # DIGESTS SEEN ON TABLE
118 --echo #
119
120 SELECT SCHEMA_NAME, check_digest(DIGEST), DIGEST_TEXT, COUNT_STAR
121 FROM performance_schema.events_statements_summary_by_digest
122 ORDER BY DIGEST_TEXT;
123
124 DROP TABLE test.v1;
125 CREATE VIEW test.v1 AS SELECT * FROM test.t1;
126
127 #
128 # test.v1 is now a view.
129 # the query digests should be unchanged.
130 #
131
132 EXPLAIN SELECT * from test.v1;
133 EXPLAIN SELECT * from test.v1 where a = 1;
134 EXPLAIN SELECT * from test.v1 where b > 100;
135 EXPLAIN SELECT a, b from test.v1;
136 EXPLAIN SELECT b, a from test.v1;
137
138 SELECT * from test.v1;
139 SELECT * from test.v1 where a = 1;
140 SELECT * from test.v1 where b > 100;
141 SELECT a, b from test.v1;
142 SELECT b, a from test.v1;
143
144 --echo #
145 --echo # DIGESTS SEEN ON VIEW
146 --echo #
147
148 call reset_digests;
149
150 SELECT SCHEMA_NAME, check_digest(DIGEST), DIGEST_TEXT, COUNT_STAR
151 FROM performance_schema.events_statements_summary_by_digest
152 ORDER BY DIGEST_TEXT;
153
154 DROP VIEW test.v1;
155 DROP TABLE test.t1;
156
157 --source include/check_digest_end.inc