Debian Codegraph @

mysql-test/suite/sysschema/t/v_statements_with_full_table_scans.test

raw
1 -- source ../include/ps_truncate_all_tables.inc
2 # Performance schema tracks prepared statements separately, and does not
3 # yet have a summary view that we can use for this view.
4 # Until then, we disable this test with --ps-protocol
5 # Test requires: sp-protocol/ps-protocol/view-protocol/cursor-protocol disabled
6 -- source include/no_protocol.inc
7
8 # Tests for sys schema
9 # Verify the sys.statements_with_full_table_scans view
10
11 # Ensure structure changes don't slip in
12 DESC sys.statements_with_full_table_scans;
13
14 # Make sure view select does not error, but ignore results
15 --disable_result_log
16 SELECT * FROM sys.statements_with_full_table_scans;
17 --enable_result_log
18
19
20 # Ensure structure changes don't slip in
21 DESC sys.x$statements_with_full_table_scans;
22
23 # Make sure view select does not error, but ignore results
24 --disable_result_log
25 SELECT * FROM sys.x$statements_with_full_table_scans;
26 --enable_result_log
27
28 # Create a dummy table, force some full table scans, verify the results
29 CREATE DATABASE v_statements_with_full_table_scans;
30
31 CREATE TABLE v_statements_with_full_table_scans.t (i BIGINT AUTO_INCREMENT PRIMARY KEY, j BIGINT) ENGINE = innodb;
32
33 INSERT INTO v_statements_with_full_table_scans.t (j) VALUES (1), (2), (3);
34
35 INSERT INTO v_statements_with_full_table_scans.t (j) (SELECT j*2 FROM v_statements_with_full_table_scans.t);
36
37 INSERT INTO v_statements_with_full_table_scans.t (j) (SELECT j*2 FROM v_statements_with_full_table_scans.t);
38
39 INSERT INTO v_statements_with_full_table_scans.t (j) (SELECT j*2 FROM v_statements_with_full_table_scans.t);
40
41 INSERT INTO v_statements_with_full_table_scans.t (j) (SELECT j*2 FROM v_statements_with_full_table_scans.t);
42
43 INSERT INTO v_statements_with_full_table_scans.t (j) (SELECT j*2 FROM v_statements_with_full_table_scans.t);
44
45 INSERT INTO v_statements_with_full_table_scans.t (j) (SELECT j*2 FROM v_statements_with_full_table_scans.t);
46
47 INSERT INTO v_statements_with_full_table_scans.t (j) (SELECT j*2 FROM v_statements_with_full_table_scans.t);
48
49 INSERT INTO v_statements_with_full_table_scans.t (j) (SELECT j*2 FROM v_statements_with_full_table_scans.t);
50
51 # The table should now have 768 rows
52
53 # Now truncate the P_S tables, to get fresh results
54 CALL sys.ps_truncate_all_tables(false);
55
56 # Following returns 28 rows, but should full scan as j has no index
57 # Using RAND() to force no query caching, so ignore results, they are not important
58 --disable_result_log
59 SELECT i, j, RAND() FROM v_statements_with_full_table_scans.t WHERE j = 12;
60 --enable_result_log
61
62 # Now verify the statement shows up in the views with the right row count (should be 768)
63 SELECT db, query, rows_examined FROM sys.statements_with_full_table_scans WHERE query LIKE '%v_statements_with_full_table_scans%' ORDER BY rows_examined DESC;
64
65 SELECT db, query, rows_examined FROM sys.x$statements_with_full_table_scans WHERE query LIKE '%v_statements_with_full_table_scans%' ORDER BY rows_examined DESC;
66
67 # Scan again
68 --disable_result_log
69 SELECT i, j, RAND() FROM v_statements_with_full_table_scans.t WHERE j = 12;
70 --enable_result_log
71
72 # Now verify that double the amount of rows for the statement should be shown as scanned (should be 1536)
73 SELECT db, query, rows_examined FROM sys.statements_with_full_table_scans WHERE query LIKE '%v_statements_with_full_table_scans%' ORDER BY rows_examined DESC;
74
75 SELECT db, query, rows_examined FROM sys.x$statements_with_full_table_scans WHERE query LIKE '%v_statements_with_full_table_scans%' ORDER BY rows_examined DESC;
76
77 # Do a point lookup
78 --skip_if_hypergraph # Does not support indexes yet, so it would show up in the list.
79 SELECT * FROM v_statements_with_full_table_scans.t WHERE i = 10;
80
81 # The number of rows scanned should not have changed (should still be 1536)
82 SELECT db, query, rows_examined FROM sys.statements_with_full_table_scans WHERE query LIKE '%v_statements_with_full_table_scans%' ORDER BY rows_examined DESC;
83
84 SELECT db, query, rows_examined FROM sys.x$statements_with_full_table_scans WHERE query LIKE '%v_statements_with_full_table_scans%' ORDER BY rows_examined DESC;
85
86 # Cleanup
87 DROP DATABASE v_statements_with_full_table_scans;