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
11 CREATE TABLE test.v1 (a int, b int);
12 INSERT INTO test.v1 VALUES (1, 100), (2, 200), (3, 300);
13
14 CREATE TABLE test.t1 (a int, b int);
15 INSERT INTO test.t1 VALUES (1, 100), (2, 200), (3, 300);
16
17 ANALYZE TABLE v1, t1;
18
19 TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;
20
21 #
22 # test.v1 is a table.
23 # Every query here is different, and should have a different digest.
24 #
25
26 # The hypergraph optimizer skips EXPLAIN with the traditional format.
27 # Disable warnings so that the count of SHOW WARNINGS in
28 # performance_schema.events_statements_summary_by_digest is the same
29 # regardless of optimizer used.
30 --disable_warnings
31 EXPLAIN SELECT * from test.v1;
32 EXPLAIN SELECT * from test.v1 where a = 1;
33 EXPLAIN SELECT * from test.v1 where b > 100;
34 EXPLAIN SELECT a, b from test.v1;
35 EXPLAIN SELECT b, a from test.v1;
36 --enable_warnings
37
38 SELECT * from test.v1;
39 SELECT * from test.v1 where a = 1;
40 SELECT * from test.v1 where b > 100;
41 SELECT a, b from test.v1;
42 SELECT b, a from test.v1;
43
44 --echo #
45 --echo # DIGESTS SEEN ON TABLE
46 --echo #
47
48 SELECT SCHEMA_NAME, DIGEST, DIGEST_TEXT, COUNT_STAR, QUERY_SAMPLE_TEXT
49 FROM performance_schema.events_statements_summary_by_digest
50 ORDER BY DIGEST_TEXT;
51
52 DROP TABLE test.v1;
53 CREATE VIEW test.v1 AS SELECT * FROM test.t1;
54
55 #
56 # test.v1 is now a view.
57 # the query digests should be unchanged.
58 #
59
60 --disable_warnings
61 EXPLAIN SELECT * from test.v1;
62 EXPLAIN SELECT * from test.v1 where a = 1;
63 EXPLAIN SELECT * from test.v1 where b > 100;
64 EXPLAIN SELECT a, b from test.v1;
65 EXPLAIN SELECT b, a from test.v1;
66 --enable_warnings
67
68 SELECT * from test.v1;
69 SELECT * from test.v1 where a = 1;
70 SELECT * from test.v1 where b > 100;
71 SELECT a, b from test.v1;
72 SELECT b, a from test.v1;
73
74 --echo #
75 --echo # DIGESTS SEEN ON VIEW
76 --echo #
77
78 SELECT SCHEMA_NAME, DIGEST, DIGEST_TEXT, COUNT_STAR, QUERY_SAMPLE_TEXT
79 FROM performance_schema.events_statements_summary_by_digest
80 ORDER BY DIGEST_TEXT;
81
82 DROP VIEW test.v1;
83 DROP TABLE test.t1;
84