1# This file is a collection of utility tests
2# for WL#4897: Add EXPLAIN INSERT/UPDATE/DELETE.
3#
4# Since MTR doesn't have functions, we use this file instead
5# including it many times.
6#
7# Parameters:
8#
9#  $query:   INSERT/REPLACE/UPDATE/DELETE query to explain
10#            NOTE: this file resets this variable
11#
12#  $select:  may be empty; the SELECT query similar to $query
13#            We use it to compare:
14#              1) table data before and after EXPLAIN $query evaluation;
15#              2) EXPLAIN $query and EXPLAIN $select output and
16#                 handler/filesort statistics
17#            NOTE: this file resets this variable
18#  $innodb:  take $no_rows parameter into account if not 0;
19#  $no_rows: filter out "rows" and "filtered" columns of EXPLAIN if not 0;
20#            it may be necessary for InnoDB tables since InnoDB's table row
21#            counter can't return precise and repeatable values;
22#	     NOTE: ANALYZE TABLE doesn't help
23#            NOTE: this file resets this variable
24
25--echo #
26--echo # query:  $query
27--echo # select: $select
28--echo #
29
30if ($select) {
31--disable_query_log
32--eval $select INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/before_explain.txt'
33--enable_query_log
34}
35
36if ($innodb) {
37  if ($no_rows) {
38--replace_column 9 X
39  }
40}
41--eval EXPLAIN $query
42if (`SELECT ROW_COUNT() > 0`) {
43--echo # Erroneous query: EXPLAIN $query
44--die Unexpected ROW_COUNT() <> 0
45}
46
47FLUSH STATUS;
48FLUSH TABLES;
49if ($innodb) {
50  if ($no_rows) {
51--replace_column 9 X 10 X
52  }
53}
54--eval EXPLAIN EXTENDED $query
55if (`SELECT ROW_COUNT() > 0`) {
56--echo # Erroneous query: EXPLAIN EXTENDED $query
57--die Unexpected ROW_COUNT() <> 0
58}
59--echo # Status of EXPLAIN EXTENDED query
60--disable_query_log
61SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
62                   Variable_name LIKE 'Handler_read_%' OR
63                   Variable_name = 'Handler_write' OR
64                   Variable_name = 'Handler_update' OR
65                   Variable_name = 'Handler_delete') AND Value <> 0;
66--enable_query_log
67
68if ($json) {
69if ($innodb) {
70  if ($no_rows) {
71--replace_regex /"rows": [0-9]+/"rows": "X"/ /"filtered": [0-9.]+/"filtered": "X"/
72  }
73}
74--eval EXPLAIN FORMAT=JSON $query;
75if ($validation) {
76--disable_query_log
77--replace_result $MASTER_MYSOCK MASTER_MYSOCK
78--exec $MYSQL -S $MASTER_MYSOCK -u root -r test -e "EXPLAIN FORMAT=JSON $query;" > $MYSQLTEST_VARDIR/tmp/explain.json
79--replace_regex  /[-]*// /FILE.[\/\\:_\.0-9A-Za-z]*/Validation:/
80--exec python $MYSQL_TEST_DIR/suite/opt_trace/validate_json.py $MYSQLTEST_VARDIR/tmp/explain.json
81--remove_file '$MYSQLTEST_VARDIR/tmp/explain.json'
82--enable_query_log
83}
84}
85
86if ($select) {
87FLUSH STATUS;
88FLUSH TABLES;
89if ($innodb) {
90  if ($no_rows) {
91--replace_column 9 X 10 X
92  }
93}
94--eval EXPLAIN EXTENDED $select
95--echo # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
96--disable_query_log
97SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
98                   Variable_name LIKE 'Handler_read_%' OR
99                   Variable_name = 'Handler_write' OR
100                   Variable_name = 'Handler_update' OR
101                   Variable_name = 'Handler_delete') AND Value <> 0;
102--enable_query_log
103if ($json) {
104if ($innodb) {
105  if ($no_rows) {
106--replace_regex /"rows": [0-9]+/"rows": "X"/ /"filtered": [0-9.]+/"filtered": "X"/
107  }
108}
109--eval EXPLAIN FORMAT=JSON $select;
110if ($validation) {
111--disable_query_log
112--replace_result $MASTER_MYSOCK MASTER_MYSOCK
113--exec $MYSQL -S $MASTER_MYSOCK -u root -r test -e "EXPLAIN FORMAT=JSON $select;" > $MYSQLTEST_VARDIR/tmp/explain.json
114--replace_regex  /[-]*// /FILE.[\/\\:_\.0-9A-Za-z]*/Validation:/
115--exec python $MYSQL_TEST_DIR/suite/opt_trace/validate_json.py $MYSQLTEST_VARDIR/tmp/explain.json
116--remove_file '$MYSQLTEST_VARDIR/tmp/explain.json'
117--enable_query_log
118}
119}
120}
121
122--disable_query_log
123
124if ($select) {
125--eval $select INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/after_explain.txt'
126--diff_files '$MYSQLTEST_VARDIR/tmp/before_explain.txt' '$MYSQLTEST_VARDIR/tmp/after_explain.txt'
127--remove_file '$MYSQLTEST_VARDIR/tmp/before_explain.txt'
128--remove_file '$MYSQLTEST_VARDIR/tmp/after_explain.txt'
129}
130
131FLUSH STATUS;
132FLUSH TABLES;
133if ($select) {
134--disable_result_log
135--eval $select
136--enable_result_log
137--echo # Status of "equivalent" SELECT query execution:
138SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
139                   Variable_name LIKE 'Handler_read_%' OR
140                   Variable_name = 'Handler_write' OR
141                   Variable_name = 'Handler_update' OR
142                   Variable_name = 'Handler_delete') AND Value <> 0;
143}
144
145FLUSH STATUS;
146FLUSH TABLES;
147--eval $query
148--echo # Status of testing query execution:
149SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
150                   Variable_name LIKE 'Handler_read_%' OR
151                   Variable_name = 'Handler_write' OR
152                   Variable_name = 'Handler_update' OR
153                   Variable_name = 'Handler_delete') AND Value <> 0;
154
155--let $query=
156--let $select=
157--let $no_rows=
158
159--enable_query_log
160
161--echo
162